Category: Custom Reporting


grayscale photo of computer laptop near white notebook and ceramic mug on table

In previous articles, I have written about the ability to take an Advent Report Writer report and make changes to it in the underlying REPLANG code. Though the program is actually called Report Writer Pro, I won’t be referring to it by that name since the “Pro” part seems a bit much.

In this article, I will take you through the basics of this process and also share some code I wrote recently to deal with a troublesome Report Writer exception. Please note, there are many modifications that can easily be done within the Report Writer interface, but this article is not about that.

You should have a good reason for attempting to modify Report Writer reports outside of the app.  For me, two fundamental issues drive this decision.  The modification either cannot be handled due to limitations of Report Writer or it is simply much more expedient to hack and slash REPLANG than it is to work within the confines of the Report Writer environment.

Okay.  Let’s assume that like me you think you have a good reason to do this. There are a few things you should know before you modify a report created by Report Writer outside of Report Writer. First of all, any report created in the Report Writer has a RPW extension, but the underlying format is REPLANG.  In most cases, any report with a REP extension was not created by Report Writer – except of course for reports like the ones we are talking about creating.

Second, your report containing code not created by Report Writer can no longer be modified by Report Writer in the future, so make a backup of the RPW file before any changes, and save the file you modify as a REP file.  That way if you need to make a change to the report later – that Report Writer would be better suited to make – you can.  In that scenario, once you have updated the report you can reapply your manual updates to the newer RPW file and then save it as REP report again.

The reason that your new REP report cannot be modified in Report Writer is not only due to the fact that the extension isn’t an RPW, but more specifically that a checksum created by Report Writer when it was last modified by the software will no longer match.  The logic behind why this is done is understandable.  Report Writer only works with certain predefined templates and does some really impressive things, but it is not designed to interpret code changes that were not created by Report Writer.

Lastly, if you haven’t waded into REPLANG code created by Report Writer it will take some getting used to.  By nature of the fact that RPW files are created to be extensible the REPLANG code is more abstract. In other words, the code generated by Report Writer appears far less direct with regard to its purpose than code that an individual might write for a singular and well-defined purpose.  If you are not familiar with REPLANG coding, I wouldn’t recommend trying to modify REPLANG reports created by Report Writer.

Some examples of the types of things I find myself doing when I modify Report Writer reports from REPLANG directly follow:

  1. I need to do a calculation that I find difficult or impossible within the Report Writer interface.  If you have any experience using Report Writer to create user-defined formulas, this really isn’t that hard to imagine.
  2. Adding a piece of data that isn’t readily available from the Report Writer.
  3. For expediency sake, I know how to do something in REPLANG in a minute, but doing it in Report Writer might take an unreasonably long time.

Dealing with Report Writer Exceptions

Recently, while working on some reporting extracts for a client, I attempted to take what appeared to be a relatively simple Report Writer report and change the format to a CSV file.  Doing so is a basic function of the product and only requires a few mouse clicks.  It is something I tend to do on a regular basis and it usually works well.  However, this time Report Writer went haywire.

Instead of taking the twenty-two thousand line report and making another similar sized report as I expected, it created a ridiculously long report (hundreds of thousands of lines of code) that Report Writer could not test or run.  Even a twenty-two thousand line report sounds big and it is.  By comparison, the longest standard report(persave.rep), which updates performance files,  is under four thousand lines. Most of the standard reports are significantly less than a thousand lines of code.

It is not the first time I have seen this problem, but this time I decided to do something more proactive to deal with this issue in the future.   The report that I was attempting to change is fairly simple.  There are no summary records.  So, the output is just a table of detail records and that helps to simplify the coding requirements of what I want to do.  To deal with this issue in the past, I have simply renamed the RPW to a REP file and modified 10 to 20 lines specific to placing the output on the screen and reformatted them to be CSV friendly.

There are alternative ways to do this. You could create the Excel file output manually by exporting the report output to Excel once the report has been generated and saving it, or by writing a script to generate an Excel file, but in this particular instance I preferred to create a more flexible CSV report.  We could also do a standard search and replace function within a text editor, but having seen this issue more than a few times I wanted to create a utility I could use going forward.

So I quickly wrote the code in the section below to change the original report, which was sending output to the report view screen, to send all the output directly to a file.  I find it very easy to code things like this using Visual Basic (VB) or Visual Basic for Applications (VBA). Since most users also have access to VBA via Microsoft Office, it is a programming option you can find and use on almost any PC. As such, I frequently use VBA when I am doing spontaneous and simple coding tasks or projects that don’t require a more complex development environment. I am not a huge fan of verbose comments, but I thought they might be useful here.

Sub MAIN()

' This routine takes a REPORT WRITER report which sends output to the
' screen and creates a report that sends output to a file. The routine
' was created to address an exception where the normal function to make
' a change in a RPW file created an obscenely large Report Writer file
' that couldn't be tested or run.

' written in VBA by Kevin Shea (aka AdventGuru) & published 06/26/2019

' Disclaimer: This routine works fine for the specific instance it was
' created for, but could need additional modifications for different
' circumstances.

'Initialize variables
Dim InputFileHandler As Integer
Dim OutputFileHandler As Integer
Dim Filename$
Dim Record$
Dim IgnoreRecords As Boolean
Dim ReportLocation$
Dim OutputFolder$

ReportLocation$ = "f:\axys3\rep\"
OutputFolder$ = "f:\axys3\exp\"

' Axys users will probably need to change the two locations above to
' match their actual folder locations of Axys.

' APX users, depending on the APX server name, the preceding statements
' might need to change to ReportLocation$="\\apxappserver\APX$\rep\"
' and OutputFolder$ = \\apxappserver\APX$\exp\"

InputFileHandler = FreeFile 'fetch an unused file handler
IgnoreRecords = False
Filename$ = InputBox("Report file name?")

Open ReportLocation$ + Filename$ For Input As #InputFileHandler

OutputFileHandler = FreeFile 'fetch an unused file handler

Open ReportLocation$ + Left$(Filename$, Len(Filename$) - 4) + ".out" For Output As #OutputFileHandler

Do While Not EOF(InputFileHandler)

  Line Input #InputFileHandler, Record$ 'read a record from the report

  If Left$(Record$, 4) = "head" Then IgnoreRecords = Not (IgnoreRecords)

  ' REPLANG marks the beginning of the header definition with the
  ' statement "head" and ends the header definition with the same
  ' statement. We don't want to write the header to our output file,
  ' so we are going to ignore all of the code between the two head
  ' lines of code.

  If IgnoreRecords = False Then

    If InStr(Record$, ".") > 0 Then

      ' The "." in REPLANG is the syntax usually associated with
      ' sending output to the screen most, but not all, "." lines
      ' end with "\n", which marks the end of the output line. By
      ' making the "." a requirement of this logic we will only
      ' process REPLANG statements that create output.

      For i = 1 To Len(Record$)

        'ignore any code lines that are less than two characters
        If i > 1 Then

          ' We may not need to be this specific, but I want to err
          ' on the side of caution. We will only process lines that
          ' start with a period. This prevents us from processing
          ' other lines that may have periods in the remarks or other
          ' areas of the REPLANG report.

          If Trim$(Left$(Record$, i - 1)) = "" And Mid$(Record$, i, 1) = "." Then

            'Somewhat self-explantory...
            Record$ = Replace(Record$, "~", ",")
            Record$ = Replace(Record$, "#,", ",#")
            Record$ = Replace(Record$, " ,", ",")
            Record$ = Replace(Record$, "?", "")

            Print #OutputFileHandler, Record$ 'write the record we modified

            Exit For
            'Exit logic early if we are done processing the record.

          End If

        End If

      Next i

    Else

      ' This bit of code inserts the code that writes all records
      ' that weren't modified, but contains some minor code insertion
      ' (outfile) make the output go to a file. The proper place to
      ' insert the outfile statement is before any accounts are
      ' processed, which is immediately before the load cli statement.
      ' Writing to a file requires that we close (fclose) the file
      ' or the result will not get written properly. The right place
      ' to insert the fclose statement is when all of the processing
      ' has been completed, which is immediately after the next cli
      ' statement.

      If Left$(record, 8) = "load cli" Then Print #OutputFileHandler, "outfile "+OutputFolder$+"outfile.csv n"
      Print #OutputFileHandler, Record$
      If record = "next cli" Then Print #OutputFileHandler, "fclose"
 
    End If

  End If

Loop

Close #OutputFileHandler
Close #InputFileHandler

End Sub

As to why Report Writer erroneously attempts and fails to create such a long report, it likely has something to do with user-defined calculations and parsing of strings. However, for the purpose of this article we are not trying to fix that issue. It may just be a limitation of the Report Writer.


About the Author: Kevin Shea is the Founder and Principal Kevin Shea Impact 2010Consultant of Quartare; Quartare provides a wide variety of technology solutions to investment advisors nationwide. For details, please visit Quartare.com, contact Kevin Shea via phone at 617-720-3400 x202 or e-mail at kshea@quartare.com.

Earlier this year, Advent sent an alert to Axys users about Windows 8 issues and how to deal with them, as an interim solution to problems that Windows 8 users can face.  It is good that Advent is proactively alerting users, but I am not recommending that any of my clients move to Windows 8 just yet. Upgrading your office to Windows 8 is premature, unless you are willing to pay the premium and deal with the frustrations typically associated with being an early adopter of the latest Windows operating system.

iStock_000014515167XSmall

If your firm uses Axys, you may be wondering whether a new release is in the works. Though Advent hasn’t publicly set a release date for the next version yet, I expect they soon will. Based on what Advent has done in the past, users should expect a 3.9 release in the near future. That release will likely support Office 2013, and Adobe Acrobat 11, and may also feature improved Windows 8 compatibility.

Though these types of updates seem minimal, they have more substance than you might think. Axys remains a very functional and cost-efficient option for advisors. Compound reports generated in Axys 3.8.5 using Excel 2010 graphs rival output from APX at a fraction of the cost. If your compound reports look dated, find out what version of Excel you are using. Using the latest version of Excel in conjunction with a version of Axys that supports it can give your reports a newer look and feel.

Axys 4?

I would like to think that Axys 4 is in the works, but a major revision would probably mean a name change – perhaps “Cloud Axys?” Longer-term, expect Axys to undergo a technology transformation if Advent wants to keep the platform alive and decides to commit greater resources to future updates that keep pace with technology trends. While the number of APX, Geneva and Black Diamond users have continued to grow, Axys users still account for a considerable number of Advent’s clients.

Historically, Axys was the lynchpin of Advent Software’s success and center of their hub of solutions for their customers. Replacing the PMS of an investment advisor is more complicated than it seems.  It impacts many of the systems at an advisor’s office, as well as the people you need to support your business, the skills they need, and what third-party solutions are available.

It would be ideal for Advent if Axys customers moved to another Advent product in the future. Those conversions and newer software licensing agreements would generate more income, while eventually allowing Advent to phase out Axys without major renovations.  However, Axys users looking at APX, Black Diamond and Geneva don’t always see a clear path.

In the past two years, Tamarac/Envestnet and other competitors have won over some Axys customers. My firsthand knowledge of a couple of advisors who made the move to Tamarac leads me to believe that Advent didn’t need to lose these customers. Through better communication, negotiation or product positioning, they could have kept the business.  On that note, I spoke with an Axys user last week that requested an APX quote after seeing a demo in Q2 and never got one.

Perception is Reality

sun-tzo5

“Be where your enemy is not.” -Sun Tzu

Current Axys users represent a ground of contention that will not be ignored by Advent’s competitors and should not be ignored by Advent. At stake is the perception of who provides the very best PMS platforms for investment advisors.  Advent may be willing to let some of their Axys clients go quietly, but in doing so they risk losing those relationships long-term, if not permanently, as well as other advisors in their sphere of influence.  Axys users represent a critical mass that could fuel the growth of  Advent’s competition in the near future.  Left unchecked, Advent competitors garnering Axys users now could ultimately vie for current APX, Geneva, and Black Diamond users down the road.

Obviously, Advent cannot be all things to all customers, but they can make a better effort to keep existing Axys clients in the fold.  In order to do so, Advent must improve communications with Axys users, affirm their commitment to Axys, and continue to add technology enhancements to Axys on a regular basis.

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.

black coffee,glasses and newspaper on business fileI have been talking about the evolution of investment reporting for years and telling anyone who would listen that their clients will soon have other investment reporting options.  My dream or vision of the future includes me (of course) providing the interface to facilitate getting data from financial services firms to a secure data warehouse via xPort where their clients could download the data for analysis on an open reporting platform.

As I discussed this with one of my clients at a recent Schwab conference, they shared their concerns with me.   I was told, “It’s basically a problem of apples and oranges.”

My long-time client and friend explained to me that they would have concerns that data they reviewed and corrected (“apples”) might be reported as uncorrected data (“oranges”).

Though data aggregators exist and have much of the data required, they won’t have it all unless advisors participate and cooperate in the process.  Reconciliation needs to be performed and maintained on an ongoing basis with respect to assets under management, inception-to-date performance, and tax cost.  No one is more motivated and qualified to maintain that data integrity than the advisors whose decisions, service, and bottom line are impacted by the quality of that data.

Big Brother will have access to this data too – that’s not part of my plan, but just a given eventuality and perhaps already a reality.  Regulatory powers will employ predictive analytics to proactively search for potential fraud.  For example, an advisor reporting the same exact composite return two years in a row is possible, but highly unlikely and worth investigating.  When more scrutiny is applied to this data, one can only hope that the benefits of additional regulation will outweigh the compliance headaches.

Enter SigFig

SigFigAccording to their web site, SigFig was born out of the noble desire to serve the millions of investors that don’t meet typical portfolio minimums and cannot afford quality investment advice.  Your clients may be using SigFig already.  If you haven’t seen it, SigFig is to investment reporting what Mint is to personal financial reporting.  Unfortunately for investment advisors, SigFig has a similar business model, meaning that investors do not pay for the service, but instead get solicited with offers that appear relevant to their investments; for example, “this fund is outperforming your fund “or “your investment advisor is overcharging you.”

Using SigFig, investors can view a dashboard summary of investment reporting information that looks better than what many investment advisors currently provide to their clients.  However, as one familiar the details of performance calculations, client billing, and reconciliation, I am naturally concerned about possible data quality issues.  The idea of replacing the sound advice of an investment professional with algorithms designed to place ads – even though those ads are intended to be unbiased – seems inherently flawed.

To learn more, you can check out SigFig here:

www.sigfig.com

In my preferred vision, advisors would pay an interface fee and their participating clients would purchase SAAS reporting or a Droid/iOS app.  Idyllic as it might seem, this version of the future would allow investment advisors and their clients to share views of reports created by impartial third-party reporting sources.

SigFig is a step in the right direction, and should serve as warning to investment advisors that more robust investment reporting information will be delivered to their clients whether they participate in the process themselves or allow their clients to find it on their own.

The Best Investment Reports

It makes perfect sense that your firm should want to provide the best reports possible to your clients, without incurring an unreasonable expense or maintaining an unmanageable reporting process.  Unfortunately, what’s best for your firm and what’s best your client may be two different things. You want to validate your investment methodology and highlight the value continued use of your firm offers, but you also need to keep your client’s best interests in mind.  More than one advisor I have worked with in the past has chosen to shy away from slick, eye-popping reports, instead favoring black-and-white reports where simple numbers alone underscore performance.  In the opinion of these advisors, the relationship with a client is more important than fancy reporting and such reports can distract investors.

Call modern reports a prudent best practice or self-serving marketing effort designed to ensure your firm’s survival.  The truth is that they are a little of both.  Clients expect decent reporting, so substandard reports are now passé.  Quarterly report packages like those I have helped clients create for twenty years are also known as presentations, and perhaps that is a better name for them.  It describes what investors are really trying to do at quarter end.

Sample Client Reporting Presentation

Sample Client Reporting Presentation

Every quarter, advisors have an obligation and opportunity to make a presentation of how their clients’ investments are doing.  Most advisors also write a quarterly letter in which they address the near-term market conditions and reasonable expectations for the future while trying to impart some relevant wisdom to their investors.  Advisors are, in fact, presenting and remarketing to their clients on a quarterly basis.  Good presentations typically illustrate an advisor’s general knowledge of the markets, educate clients, and show how the advisor adds value.  The reports included in these presentations also present holdings analyses that provide clients with additional insight into their investments, but, most importantly, these reports provide the client with performance figures and comparative benchmarks for various time periods.

Report Development or Adoption

For some firms, proprietary custom report writing is required to meet the needs outlined above.  With this requirement comes the necessity to employ staff or contract with vendors to produce and maintain the reports.  The effort to produce high-quality reports can be daunting whether the project is handled internally or outsourced.  Many custom reports, by definition, are in flux.  In a typical quarter, custom reports may undergo additional feature enhancements and require maintenance modifications or bug fixes.  In order to maintain custom reporting systems, an ongoing commitment of time and money is necessary.

Advisors may want to create distinct custom reports that are part of their brand, but given the potential complexity and cost of creating those reports – the best investment reports for those with limited funds are the ones that already exist.

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_000011903357XSmallAs quarter end approaches once more, investment advisors gather their resolve and strive to implement new system enhancements to client statements before Q2 arrives. We all wish it could be different, but it is simply the nature of the business. Even firms with good-looking statements may rush to enhance them by incorporating new features such as blended indexes or minor report edits prior to quarter end.

Most of the systems implemented at investment advisories are run on a daily basis, but client statements are typically generated on a quarterly basis. Some investment managers produce monthly statements, but they are in the minority. In the near future, these types of periodic statements will become less relevant than an investor’s ability to generate meaningful client statements on demand.

Amending the quarterly reporting process and report packages requires a disciplined process of setting manageable goals, gaining approval, implementing changes, testing systems and resolving potential problems. Those responsible for creating the reports require approval from management, and until they get that final approval, the time necessary to comfortably implement the changes continues to slip away.

“THE ONLY CONSTANT IS CHANGE” -Heraclitus

As quarter end gets closer, it is not unusual for those involved in the project to get mixed messages about what needs to be done for this quarter. Operations staff juggle various responsibilities. Ad hoc requests, changing priorities and management decision delays inevitably create the need to do last-minute work or postpone the project until the next month, quarter, or even year. With these types of delays, it is a wonder that any progress towards creating better client statements is ever made.

At most larger firms, management is comprised of a number of voices that have a say in whether a firm adapts new systems and reports. The decision to change quarterly reports isn’t made in a vacuum. It is a decision that may depend on a firm’s dedication to their current portfolio management system or a need to allocate limited budget resources to other high-priority issues. Good quarterly reports also underscore a firm’s philosophy, so deep thought and considerable discourse can play a part in determining the content and appearance of client statements.

SOME AXYS USERS DO NOTHING WHILE OTHERS THINK ABOUT MAKING A DECISION.

Before you summarily dismiss all Axys users as being hopelessly outdated and technically inept because they haven’t upgraded to APX or moved to another more recently released Portfolio Management System (PMS), you should understand that there are many decent-sized firms out there that are still using Axys to manage hundreds of millions or billions in assets.  Though a small group of firms really do fit the preceding description, most firms do not. The majority are firms that understand the current shortcomings of Axys and the potential benefits of APX, but also value the simplicity, efficiency, and reliability of Axys.  These firms may have considered Advent’s other offerings: Black Diamond and Geneva, but haven’t managed to find a solution that works for their firm.

As a growing number of these Axys users may be questioning their commitment to Advent, it is only natural that they vacillate to some degree between putting more money into their existing platform or changing the platform altogether. I suspect that many of Advent’s Axys clients now fit into two categories: those who are indifferent and those facing decision paralysis.

Those who are indifferent use Axys like a calculator and will continue to do so until it becomes apparent that they cannot use it any more. Those facing decision paralysis will also continue to use Axys – albeit with some reservations – until another vendor steals them away or Advent makes a bold announcement to continue to support these lost souls with renewed vigor.

In the end, a firm’s need to keep pace with client expectations and technology by providing innovative reporting should win out whether that is achieved by spending money on their existing platform or embracing another. For now, like many in the industry, I continue to hurry up and wait for investment advisors and Advent Software to do something.

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.