Category: Custom Reporting


Account vs InflationReporting a portfolio’s comparative performance against representative benchmarks over various periods of time has been an widely implemented technique of investment advisors for decades.  Firms do this in a variety of different ways, and some methods are more meaningful than others.

Advisors may show the CPI as a baseline index against the portfolio and other indexes in an effort to illustrate how their investments have performed against inflation and hopefully point out the most basic benefit of investing with their firm versus keeping it under the mattress.

If a firm manages equity they may show the the Dow Jones or the S&P 500 indexes.  If they manage international equities, or have a bond component they will likely show other representative indexes.  They may further isolate performance by asset class and show it with the corresponding indexes to facilitate a client’s ability to draw a direct comparison between the performance of like asset classes and underscore the value they add to the investing process of specific asset classes.

PROBLEMS WITH TOTAL PERFORMANCE BLENDS

Firms typically show total performance too, but many run afoul when trying to show a representative index to match total portfolio performance.  Portfolio management systems like Advent Axys and APX have long supported the ability to create blended indexes, but the actual implementation of the feature falls short of real-world requirements.  In the past, many advisors were content to create a blended index with static weights, but firms soon recognized that these ballpark blends weren’t good enough for their requirements because they didn’t accurately reflect the asset mix of their portfolios over time.

Advent Software initially supported the creation of blended indexes in their portfolio management systems (Proport and Axys) through a report that was engineered to create simple index blends.  This method of creating indexes was somewhat limited because index weights couldn’t be changed over time, but it allowed advisors to manage many indexes centrally through the use of scripts and macros, and it also created separate index (DEX) files.  Having separate files allowed clients to track and show a number of blends if they chose to.

Advent improved the feature to some degree by creating the synthetic index, which allows users to change index weights over time on an individual portfolio basis.  The synthetic index feature works best when used to create ad hoc indexes for selected portfolios, but when users try to scale the feature to be used for a large number of portfolios its usefulness tested.  The ability to change the index weights over time is a plus, but updating the info in an automated fashion is not a feature of the system.

AUTOMATED BLENDING SOLUTIONS

In the early 90s, we started working with advisors to create custom blends with dynamic index weights in order to match the ever changing asset mix of portfolios.  This was done by categorizing assets as fundamental asset types, and assigning those asset types to like indexes.  Once the infrastructure was created we created an extract containing the historic month end asset type weights for all portfolios and used those weights along their corresponding index returns over time to create a true blended index.  The process was automated with a combination of Axys reports, macros, scripts and Visual Basic.

Years ago, we  helped firms address the some of the short-comings of the synthetic index feature by creating a product dedicated to creating blended indexes and managing them.  This allowed users to manage the indexes centrally rather than at the portfolio level.

Last year, we revisited the problem of effectively managing custom index blends for Axys and APX users again.  This time we automated the process of refreshing the index weights stored in each portfolio’s corresponding performance file through a process that updates the synthetic weights by stripping the performance files of their synthetic weight values and rewriting them based on each portfolio’s asset mix and correspondingly assigned indexes.  The process builds monthly synthetic index weights for the inception-to-date period.

This most recent update to our index blending automation tool leverages Advent’s synthetic index capability, and adds the much needed automation that users require to implement the existing synthetic index feature in a way that makes more sense.  Unfortunately, there are still limits to the built-in blending functions of Axys and APX.  For example, synthetic indexes create a blend called simply “Blend”, and users trying to show multiple blends need to create those other blends somewhere else and then generate index files, so that they can be shown alongside synthetic blended indexes.

Thankfully, Advent’s import/export functions, scripting and macros allow savvy users to workaround these limitations as long as they are willing to roll up their sleeves and do a little work or know someone who can help.

About the Author: Kevin Shea is President of InfoSystems Integrated, Inc. (ISI); ISI provides a wide variety of outsourced IT solutions to investment advisors nationwide.

For details, please visit isitc.com, contact Kevin Shea via phone at 617-720-3400 x202 or e-mail at kshea@isitc.com.

iStock_000007871357XSmallYour firm has just completed its implementation of APX. All systems are go including a small collection of SSRS reports, which meet some but probably not all of your firm’s requirements. You have new reporting capabilities, and now the question is “will your firm ever use these new features?” SSRS is also known as Microsoft Reporting Services, which sounds a little less complicated. No matter what the name is, SSRS is a beast – the following issues will challenge your firm’s ability to embrace and leverage SSRS technology for the foreseeable future.

1. TOOL INSTALLATION – SSRS tools like Report Builder and Business Intelligence Development Studio (BIDS) will not be installed on most of your PCs. They are kept at an arms-length from most users, and rightfully so. Though SQL Server Manager and SSRS reporting tools can be accessed on the database server, it currently isn’t Advent Software’s policy to install the applications that give users access to these tools on all users’ PCs. Assuming you have someone at your office with relevant report-writing experience, getting their system configured to make SSRS reports and/or modifications is special request. I have worked with many APX users. By default, most of them do not have access to the tools, so they could not use them or even see them. Some firms using APX 3.x do not even have access to SSRS reports because they have not been installed.

2. ENVIRONMENTAL COMPLEXITY – Once the tools have been installed, the collection of SSRS reports is open to users’ review and modification, but the infrastructure and understanding it requires is cumbersome. Most APX users do not have the skills necessary to create SSRS reports, and very few of those who do are interested in doing it. For those unfamiliar with SSRS and other similar report-writing tools, seemingly simple reporting modifications can be a pain if datasets aren’t designed with your specific reporting needs in mind. Those writing reports need to make frequent backups. Occasionally, reports can become corrupted and cause their writers to lose hours of work.

3. TIME – Compared to creating compound reports and building reports using Advent Report Writer Pro, developing reports using SSRS and other similar report writers like Crystal takes much more time. This is the norm, but not the rule. There are specific report-writing tasks that SSRS is more efficient at performing, but overall report-writing with SSRS is exponentially more complex than using Advent’s standard report-writing tools. This is due to the fact that SSRS development and modifications are the domain of Business Intelligence (BI) professionals and other system integrators who do it for a living. Report Writer Pro and compound reporting were developed by Advent to be used by investment operations end-users with limited technical know-how. SSRS was created by Microsoft, and is not designed with these same users in mind. Some simple SSRS reports take minutes to create, but it is much more likely for users to spend hours, weeks or even months working on reports.

4. COST – Since your firm is unlikely to have BI report developer resources internally, you will need to hire outside resources to develop your reports. That sounds familiar, right? Assuming that you, like many Advent APX clients, spend somewhere around 100k to 200k annually on APX, you can expect to pay at least another 15k to 30k annually to get the reports you want and keep them maintained by qualified third-party resources. You may be able to get the work done cheaper, but anyone delivering reporting services on a platform this complex at a significantly lower price will not be in business for long.

5. AVAILABILITY OF QUALIFIED RESOURCES – Since SSRS is still relatively new to Advent users, there are very few BI resources available with specific experience working for APX users. The learning curve is steep. Significant integration and reporting work needs to be done for individual firms to fully embrace SSRS as their reporting platform, and short-term that leads to a smaller pool of available resources to do the work.

RUNNING WITH SSRS
Due to the complexity inherent in combining various data elements via SSRS and workflow automation, some APX users may still be better off using the REPLANG and compound report functionality first introduced in Axys. Standardizing your firm’s reports using SSRS on Advent’s APX platform could be tough. For many users, standardizing will mean trying to make standard (REPLANG) reports look like they were created in SSRS, or worse, completely reengineering those reports in SSRS.

Advent deserves credit for implementing SSRS. It is a progressive move aimed at satisfying the enterprise users for which APX was designed, but some firms using APX should ask themselves whether they truly are an “enterprise” before they start implementing tools designed for enterprises. (In the near future, I will be blogging on the issue of firm identity and the role it plays in the success or failure of technology implementations.)

Long-term, there is good news for many APX users. Though creating reports can be very complex, the format of SSRS reports is extremely portable, which should eventually lead to more report sharing among APX users. Unfortunately, while this may be good news for APX users, BI developers and integrators like ISITC have to be more concerned with the portability of their end product.

One could literally spend hundreds of hours developing a report and have someone walk away with it. In other stickier environments, reports might be developed at a discount, but an integrator’s sunk costs could easily be recouped through a nearly guaranteed long-term maintenance agreement. Given concerns regarding portability, you should expect to pay a premium to have SSRS reports developed for your firm.

Firms making a significant investment to develop distinctive reports in APX now should be equally concerned with maintaining those reports in the future. Advent and third parties that create reporting solutions regularly make updates to address bugs and/or add functionality to reports. Though APX users may not realize it, this environment is still fairly sticky. Those unfamiliar with specific reports can easily perform the simplest modifications, but firms will do well to retain those that write their SSRS reports to address more complex modifications in the future.

About the Author: Kevin Shea is President of InfoSystems Integrated, Inc. (ISI); ISI provides a wide variety of outsourced IT solutions to investment advisors nationwide. For details, please visit isitc.com, contact Kevin Shea via phone at 617-720-3400 x202 or e-mail at kshea@isitc.com.

As a provider of technology solutions for financial services firms small and large nationwide, I frequently come in contact with investment firms of diverse dynamics and decision-making processes.  I am, of course, familiar with the process and discipline of getting

three separate quotes for goods and services, but even after decades of bidding on projects, it is still unclear to me what investment firms actually do with this information.

In some cases, it seems like the decision has already been made and prospects are just going through the motions to fulfill the expectation to follow a procedure and process established by their firm.  Gut decisions sometimes overrule common sense.

One of my clients actually adheres to this discipline for everything and, if the rumors are true, even gets three prices for paper clips.  In my own experience with them, they did, in fact, get three quotes for a single piece of computer equipment that cost about $75.  Considering current wage and consulting rates this arguably may not be a good use of time or money.  Perhaps it’s a more altruistic goal of keeping our economy competitive that drives their policy.

 

Opportunity                          

Recently, I was contacted by a firm looking for assistance with some Axys report modifications.  One of our competitors provided them with a quote for the work they needed.  The prospect felt that the price was too high and they solicited my opinion.  I never saw the quote from my competitor, but heard from the prospect that they wanted 3-4k up front and expected it would cost 7-8k.  In another conversation, I was told that there was also a local company bidding on the work.  That made sense to me – three bids.

I was provided with a detailed specification of what needed to be done and asked to provide them with a quote.  The firm was looking to make some modifications to the Axys report that generates Advent’s performance history data and stores it as Net of Fees (PRF) and Gross of Fees (PBF) data.  Though the requirements seemed complicated initially, it eventually became clear to me that the job simply required filtering of a couple REPLANG routines, and some minor additions.

I shared my impression with the prospect and ball-parked our bid at 3k (a 12 hour block of time) less than half of our known competitor’s bid.   I explained that the actual work was likely to take three to four hours, and rest of the time would be spent on testing, support and maintenance.  My expectation was that we would get the work done in a half day to a day at most and the remainder of our time could be used for any required maintenance or modification later in the year.

 

Follow-Up

After about a week, I called to follow up and found out that the firm was strongly considering having the work done by their local vendor, who told them it could be done for seven to ten days.  “Excuse me,” I said.  “Don’t you mean seven to ten hours?”

“No,” he replied.  He further explained that they really like using the local vendor and would probably use them for the job, which I fully understand.  I have, no doubt, benefited from this sentiment in Boston for years.  At that point in the call, I was thinking that it was more like seven to ten lines of code, but thankfully I didn’t start laughing.  I waited until the call ended.

 

No Risk, No Reward

In the end, your firm’s decision to select one bid over another is a personal one, similar in some respects to the one that dictates an investment adviser’s success attracting new clients and retaining them.  It’s about trust, performance, and the ability to continually communicate that you are worthy of one and capable of the other.  To succeed long-term in the financial services business, you need both.  Through good performance, we gain a measure of trust.  However, without a measure of initial trust or risk, there is no opportunity to perform.

About the Author: Kevin Shea is President of InfoSystems Integrated, Inc. (ISI); ISI provides a wide variety of outsourced IT solutions to investment advisors nationwide. For details, please visit isitc.com or contact Kevin Shea via phone at 617-720-3400 x202 or e-mail at kshea@isitc.com.

In an earlier blog I emphasized the importance of mainstream client reporting.  As investment professionals once again turn to the dreaded task of busily cranking out their quarterly reports, it is relevant to share the process we have established to help many of them transition from tired, stale reports to a new generation of client reports. 

In this article, I’ll take you through our process for overhauling client reporting drawing upon specific references to a recent project. Whether you opt to utilize a third-party service provider like us, request Report Writing services from Advent, or produce your next generation client reports internally, you should find the following information useful.  Those that want to implement new reports for Q3 or Q4 of 2011 need to start the process now.

Our Process for Improving Client Reporting

Our fundamental approach addresses the most difficult reporting issues first, identifying any show-stopping problems as early in the process as possible.  We are able to create reports using a number of techniques.  If one way doesn’t work, we can always fall back on another, but our goal is to select the right method from the get-go.

1. Review

In the review stage, advisors need to appraise their current client reporting packages with a critical eye in order to identify what is good and bad about them.  In a nutshell, investors must preserve what is highly valued by clients and remove what is superfluous. The ultimate goal should be to create concise, comprehensive reports that are easily understood, allowing clients to view as little or as much detail as they desire.  Many advisors want to create visually crisp and professional reporting packages.  We understand the importance of this;  however, in the area of client reporting, meaningful content should trump form.

Though we are available and qualified to review client reports and make recommendations for new ones, most firms prefer to do this internally. 

2. Mock-up

A new report always starts with an idea.  Oftentimes, this is shown with a mock-up expressing the look of the desired end product.  In some cases, our customers produce mockups in Excel, but others cut and paste pictures together, or simply sketch them freehand.  Any of these options are fine.  As they say, a picture says a thousand words:  the more detailed the pictures, the less you will have to explain to those writing the reports. 

Most clients have a strong preference as to whether reports appear in landscape or portrait. This aspect of your reports will be more expensive to change as you progress further into the project.  We understand that this decision may have more to do with aesthetic presentation issues, but some report layouts simply require more vertical space or horizontal space than others.  If you are dead-set on a certain orientation, you may need to be more flexible about report content.

Over the years, we have created a wide variety of quarterly reporting packages for clients. Some samples of our work that may help you with your mockup appear on our website under the menu titled “Custom Reports for Axys/APX.”  They fall into three categories:

1 – samples of reports produced by extracting data from Axys/APX and generating reports through traditional report writers like Crystal Reports and SSRS

2 – samples generated directly from Axys/APX through the use of compound report macros

3 – older samples of reports that were generated through a variety of methods

While browsing these above samples, click on any report to view it in larger size.

After viewing all of our online samples and PDF documents, our client produced the following mock-ups for us:

3. Draft

The draft process, as we define it, is one where the reports’ framework is established in the chosen environment.  Roughing out the reports helps determine their feasibility. In the attached example, we started by spending a day onsite, drafting the four account summary-type reports that were requested.   We used a combination of REPLANG, Report Writer Pro, and compound report macros. During this phase of the process, we are not overly concerned about individual details. Instead, we focus on the big picture. Is it possible to create the reports requested? What type of challenges will we face? What tools will be required? What resources, including time, will be required?

There are two possible outcomes to this stage:

  • Validation that the reports can be produced in the selected environment, as well as a better understanding of what they will look like and how much time they will take
  • A recommendation for another methodology, such as SSRS or Crystal Reports, based on the difficulties encountered in attempts to draft the basic report framework

After drafting the four requested summary reports, we were in a better position to estimate the amount of time necessary for development, knew what features would be difficult to implement, and were confident that we could deliver the reports on time.  Our client was also included in the process.  As we drafted each report, we sought their feedback to determine whether things were taking shape as intended.

4. Design

A significant amount of time needs to be spent in the design phase, selecting fonts, styles, colors, chart details, and other elements of presentation related to the reports.   Our client preferred to use the traditional Times New Roman font, but this font choice is one of the reasons most Axys and APX reports look so similar. We selected title bars rather than title boxes to give added flexibility regarding the placement and size of report elements.  Colors are very important. In the past, I have seen clients struggle to pick a palette of colors for charts and graphs. Our client picked vibrant colors that complemented their logo. If you are not already familiar with it, Adobe has a very useful and free resource that you can use to select a color scheme for your reports:

 http://kuler.adobe.com

As a general rule, one should complete design of the master page or default style for all reports before moving on to the next phase.  Report writers and developers are not necessarily graphic designers.  You can save your staff or vendor a lot of grief by having your color schemes selected and logos produced by professional designers.  In particular, your designer should produce images of the proper size, format and quality required.

5. Build & Test

We minimize formatting and style changes by beginning work in this phase only when a client has committed to a design specification.  Ideally, we wouldn’t make any changes to design once we have begun the build phase, but some customers change their minds between the design and build phases.  We also occasionally run into difficulties with pieces of the implementation process or come up with a better way to design something in the process of its implementation.

No matter how a report is created, the formatting of the first in a series of client reports to match the design layout is the most difficult.  However, once the initial report is completed, the rest of the reports come together much faster. The bulk of the time on your project will likely be spent on implementation.  This time depends on the number and complexity of reports you plan to produce, and the resources available.  It will likely take days, if not weeks.

In initial testing, we run reports for a small control group that represents the client’s various types of accounts. We also do a number of report runs for the full group of reports that will be run at quarter end. We find that doing full-scale tests is the best way to identify exceptions and deal with them proactively. As we find issues with individual reports, we apply fixes to address them, and must test again to validate the fixes.

In the example below, our client did a great job illustrating exactly what they wanted and let us focus on producing the report.

Account Summary Mock-up

The final report that we created based on our client’s mock-up shown above follows:

To see other samples of the final reports click here.

It took roughly 30 days to produce the final versions of our customer’s four account summary reports.  The customer was very engaged in the process and highly motivated, facilitating progress by providing quick responses to our questions.   Similar projects take 4-6 weeks, but could take significantly less time if you are working from established reports that just need modification.  You may remember that our initial draft took a day; we spent the rest of the time working on the more difficult aspects of the project.  Our summary reports brought data that needed verification into focus.  Based on exception testing and other issues, we made our final modifications to the reports’ appearance.

The project referenced in this article included new custom reports, quarterly packaging automation, integration of new custom reports, and conversion of legacy packaging to our new report packaging environment.  The project’s total cost was about 20k, nearly double what it would have been with our preexisting custom reports, but the only recurring cost is maintenance.  Many of today’s alternatives feature a sizable implementation cost and significant monthly fees.

Improving your client reporting is one of the most important things you can do to communicate more effectively.  Your next generation of quarterly statements should make it clear to your clients that you are investing in a process that directly benefits them.  If you do it right, you are bound to receive positive feedback from your clients once they have your new reports in hand.

There is no time like the present to start working on your next generation of client reports.

About the Author:
Kevin Shea is President of InfoSystems Integrated, Inc. (ISI); ISI provides a wide variety of outsourced IT solutions to investment advisors nationwide. For details, please visit isitc.com or contact Kevin Shea via phone at 617-720-3400 x202 or e-mail at kshea@isitc.com.