Category: APX


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.

Bull_20140121-winter-SamiraBouaou-2329-676x450A relatively predictable bull market doesn’t pose significant challenges to investment managers short of making the best possible investments.  Assuming one has embraced a decent platform and mission-critical systems are in order, investment managers don’t need to think too hard about reporting or much else during a boom.  Investment management firms are money-making machines, and in a bull market, most tend to do that well.

When markets and returns are kind, client reporting doesn’t get much scrutiny.  This is a point exemplified by the fact that during these times some clients don’t even bother to open up their statements.  In more challenging times, client reporting gets a level of attention that has the potential to be bad for business.  And yes, I know – even during those times – there are still clients that don’t look at their statements.

Over the years, report designers like myself have created a number of dazzling client reports that can look … well … not so dazzling when returns approach zero, or worse, become negative.  In the design process, most of the time is spent looking at accounts that paint a pretty picture.  Those are the accounts that get used in sample reports, so it shouldn’t be surprising when investment managers see how ugly an account with poor performance can appear.

A survivor bias naturally minimizes any attention these accounts might receive, but in the meantime, those reporting on accounts with sub-zero performance need to make decisions like “Should we show the floor of the graph when the account is negative?” and other presentation details that most investors would rather not contemplate.

No one I have worked with says, “We want our bad performance to look good.”  They just don’t want it to look any worse than it actually is.  Though some of the changes we make to reports are purely cosmetic, most of the report enhancements we implement are designed with one thing in mind: presenting performance fairly.

Some specific examples of modifications we have made include:

  1. creating a truly representative custom index for each account
  2. producing comparative index returns and risk
  3. isolating managed asset class and sector returns to produce select time period performance
  4. providing comprehensive performance summary reports that help the clients of investment firms put occasional anomalies in perspective

 

Flashback to Q1 2016 

Coming off a relatively flat 2015, traditional investors were likely dismayed to see double-digit negative returns just two weeks into 2016.  Since many of these same investors were drafting their investment commentaries explaining the past quarter, calendar year 2015, and the outlook for 2016 (ahem), these market conditions likely spurred some very focused thought about existing client reports and how they might look next quarter based on January’s performance to date.

2016-Q1

As a result of this come-to-Jesus moment, my phone was ringing more than usual.  In January of 2016, I fielded a number of calls from firms in a few different countries and across the US.  In almost every case, decision-makers were ready to pull the trigger.  They wanted new reports and they wanted them done quickly.

Investors planning on replacing their platform with a higher-cost alternative that ultimately might address the shortcomings of existing client reports may have reconsidered those decisions for the immediate future due to concerns over time to implement, predictability and increased costs.  I have made the point in the past and I’ll make it again here: it is typically much easier to replace your reports than your portfolio accounting system.

In simple terms, it generally takes hours, days or weeks to create new reports for Axys and APX users regardless of the content.  In contrast, the time required to change your portfolio accounting system in order to leverage pre-existing reports or make new reports on a different platform is more likely three to six months.  In some cases, investors that switch to another portfolio accounting system with new reports in mind find that they still don’t have those reports a year later.

These fundamental truths along with market conditions no doubt led investors that use Advent Software products to seek out and retain the expertise of replang and SSRS report designers like me to create and implement a variety of new performance reports designed to address concerns about their existing performance reports.

In my own experience, one prospect provided me with the specifications for their new performance summary report and requested that the report be done in a week.  In another instance, I was tasked with facilitating a reclassification of securities, regeneration of performance history, and creation of a new performance report based on the reclassification before the end of January.  In each of these engagements, working as a team with responsive and motivated clients, we were able to start and complete large-scale, high-impact projects in an accelerated time frame.

As a seasoned professional services consultant, I do my best to address the needs of my retainer clients – that keep me in business – first and any new prospects second, so these non-retainer clients were fortunate that I was able and willing to commit to projects that required fast tracking on short notice.  Though I regularly take on new projects, the most interesting thing about that quarter’s new business was the timeline imposed.

There was definitely a sense of urgency associated with these reporting projects that usually isn’t there, and though consultants like me appreciate new business opportunities, investment managers cannot usually expect those results if they haven’t forged a relationship with a consultant ahead of time.

With an established, highly-motivated expert on retainer, you can call that person on impulse, have a high-level conversation in minutes with someone that is familiar with your systems, get a responsive quote for additional work if necessary, and potentially have a new report or automation created before you might be able to effectively start a dialog or get an adequate response from an alternative source.

When the financial markets take a turn for the worse, one might assume that folks like myself who make a living off of providing software products, custom reporting solutions and consulting services aimed at automating and integrating investment management systems would suffer a downturn in business.

While that is ultimately true, should the market ever become so difficult that my customers become distressed or go out of business, it is not true of “corrections” that occur in the market.  These events force the hand of investors – making them scramble to take stock of reporting, trading and any other systems that their businesses rely on with an aim to enhance, automate and improve.

The best run investment firms are constantly striving to improve their mission-critical systems and willing to retain the talent that empowers them to make changes to those systems whenever it becomes necessary or advantageous.

Investors dodged a bullet in Q1 of 2016, but doing so in the future may require changes to your client reports. I am hopeful that 2019 will be another banner year for investors, but sooner or later something is bound to change.


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.

iStock_000021815840XSmallAll things change – even things at the SEC.   Previously, investment managers could upload text (aka ASCII) files detailing their holdings to Edgar.  This quarter, a change was made, requiring the file to be formatted in XML.  Investors have 45 days from the end of the quarter to file their 13F reports, so Q2 reports are due today.

Some users that attempted to get these reports done earlier in Q3 expressed frustration with the XML issue and their ability to get more proactive assistance from Advent to address it.  Last week, as the filing deadline approached, Advent reached out to clients, alerting them of the change and directing them to an ASCII to XML conversion tool to facilitate the process.  In my own experience with Advent’s support team, I found them both helpful and knowledgeable in regard to the 13F reporting issues.

Though Advent’s documentation states that the 13F report and conversion tool requires Axys 3.8.5 or higher, the report from Axys 3.8.5 worked fine when we used it on Axys 3.7 with a client.  APX users can use the same utility.  The utility was simple to use and worked well;  the biggest challenge for users is finding the file they need to convert.

The 13F reporting mechanism is functional, but the setup seems cryptic and disjointed.  First-time users expecting a turn-key, intuitive solution will be disappointed.  Fortunately, the details of what is required to produce 13F reports are well-documented in Advent’s help file.

How 13F Reporting Works…

By default, the 13F report only includes the equity asset class.   It is possible to exclude individual securities through the use of the 13F.est file, but it is not possible to include individual securities.  Additional asset classes may be added.  Report-specific labels must be added to the 13F portfolio file to make the report work properly.

When the supporting files are properly configured, the report produces detailed holdings and simultaneously generates an inftable.txt file with the same information.  This file is placed in the specific user folder (i.g. f:\axys3\users\amy) of the person running the report on the network version of Axys or the root folder of Axys on the single-user version of Axys.  When users have generated a 13F report without missing data or error messages, they are ready run to the conversion utility to produce the inftable.xml file and upload the information to the Edgar site.

This quarter, running and filing 13F reports was more challenging than it has been in the past, since users were forced to correctly implement the 13F report in order to successfully generate an XML file.  Based on my experience with users, this was something they had not been doing in the past.  Most users would run the report to get something close to what they needed and then manually modify the text file, rather than keep all of the information updated in the 13F portfolio and 13F.est files.  Going forward, the process will still require that new securities and relevant asset classes be classified specifically for the 13F report, but future report runs should be simpler.

For more info on 13F reporting, refer to the SEC’s document detailing Frequently Asked Questions (FAQ).

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_000011255731XSmallWhen Advent first introduced The Professional Portfolio (aka Proport) 25 years ago, one feature that contributed  largely to its long-term success was the ability to get data in and out of the program easily.  Advent software has continued to make this feature a priority throughout every iteration of their original portfolio management product line.

In contrast competing products like Centerpiece, which would eventually be purchased by Schwab and rebranded Portfolio Center, appeared to be a black box.  You could see what was being calculated, but could not see the components of the calculation.

Proport files were stored in an open text format and could be easily read and written as necessary.  Axys v1.x, Advent’s premier Windows product at the time, maintained a similar open file structure.  Axys v2.x was the first version to implement a binary file format.  At the time, some users were concerned that the format change would complicate maintenance of existing customized solutions and inhibit their ability to continue to create solutions that exchanged data with Advent’s portfolio management system.

Firms were slow to embrace Axys v2.x and some never upgraded to it.  Perhaps it was concern over the new file formats.  Instead, most firms eventually upgraded to Axys v3.x. Concerns about the format change were moot since Advent also introduced IMEX, which allowed users to import and export files in CSV, tab, and fixed formats.

Exceptions apply, but the IMEX tool facilitates the ability to move data in and out of Axys with relative ease.  The features of IMEX combined with the ability to import transaction and label data through the trade blotter provide a comprehensive means to get fundemental data in and out of Advent Axys and APX.  Additional methods of importing and exporting data follow:

Axys users may write or read data directly to data files if they have knowledge of the underlying data format.  However, this is not a best practice due to changing file formats between versions.  For example, upgrading from Axys v3.7 to v3.8 requires a file conversion process.  Some of the resulting Axys v3.8 files have a different file format, so any process directly writing or reading these files would need to be updated to take the new file formats into account.

APX users may

  1. query the APX database via Excel (and other software programs).
  2. write SSRS or Crystal reports to extract data.
  3. use many other SQL based tools to export and import selected data.

Axys and APX users can

  1. export reports directly to Excel with the push of a button or create a macro that stores report output in XLS and other file formats.
  2. create custom reports via Report Writer Pro, which can easily be changed to CSV format.
  3. modify existing replang reports to build CSV, other text formats, and various Advent file formats.
  4. use third-party Extract, Transform and Load (ETL) products like xPort.

APX (v1.x to v4.x) maintains the functionality of IMEX, but the ability to generate files in a fixed format has been eliminated.  In addition, you can export data to an Axys v3 format.

Due to these capabilities and a host of other Advent features that facilitate automation and integration, Axys and APX users as well as third-party vendors like ISITC.com have developed many custom solutions utilizing Advent Software’s infrastructure to address day-to-day investment operations workflow and reporting requirements.  The building blocks of these solutions faciliate subsequent projects and allow investment firms to further enhance Advent’s portfolio management systems to meet their evolving needs with less effort and cost.

Here is a sample of some VB code we use to integrate and automate data handling of exported Axys and APX data.  The code can be used in Excel with VBA and User-Defined Functions (UDFs) to pull data from Axys and APX like Bloomberg BLP functions are used in Excel. This function is just one of the routines in our library of code that enable us to seemlessly integrate our solutions with Advent’s infrastructure.  I wrote the original source code for this routine twenty years ago and have updated it as necessary to support later releases of Axys and APX.

Using similar resources, integrators can move quickly from prototype to production when developing solutions for Advent users.  In fact, Advent’s most recent annual report continues to cite customers building their own solutions as one of their largest sources of competition.  For instance, a number of firms have created their own Order Management Systems – not that I’d recommend it.

Advisors abhor inefficiency and are typically willing to make a reasonable investment to reduce it.  Automation not only increases efficiency, but lowers risk by eliminating manual processes that may rely on individuals and their exclusive knowledge of manual or semi-automatic procedures.  Some financial services firms have customized their systems to a degree that makes staying on the Advent platform for twenty plus years possible and the thought of switching to another platform regrettable.

Thanks to the way Advent handles getting data in and out of their systems, users can continue using Axys to meet their ever-changing system requirements and leverage most solutions created for Axys on the APX platform.   Similar and potentially better tech options may exist on other competing platforms, but most of those systems lack the maturity, depth of resources, third-party relationships, and corresponding reliability of Advent’s platform choices.  Knowledge and acceptance of these competing products among advisors, employees, and third-party solution providers won’t match Advent for a long time.

As a result, even though technologically superior portfolio management platforms may emerge, many firms will continue using Advent’s best known portfolio management systems for the foreseeable 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.