Tag Archive: replang


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.

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.

Though an increasing number of firms pride themselves on their ability to fire out reports within the first week of the quarter, it seems that most firms still produce and mail out their statements in the second or third week.  Yes, I said “mail out.”  Even firms that have invested in the ability to post their reports to a web portal still mail most of their reports out due to low adoption rates by their clients.   Investment advisors that don’t get their reports out within the first three weeks of quarter end are operating outside of the norm.

There are 13 weeks in a quarter.  Given that most firms send quarter end reports during week two, operations folks aren’t thinking about doing an upgrade in week three.  They’re busy catching up on what they didn’t do in weeks one and two, while they were managing the client reporting process.  That leaves ten possible weeks for a system upgrade.  Weeks four through six are ideal, giving your firm adequate time to test your systems and apply fixes as necessary.

Weeks seven to 13 become increasingly unappealing; lucky number 13 is the worst possible time to perform a system upgrade.  Most people in the investment business know this.  With 25 years of experience installing Advent products, I consider the time approaching quarter end an obvious no-fly zone for in-place system upgrades, no matter how competent you are.  I was stunned yesterday when I received a call from a customer related to a system upgrade.

Apparently, someone working with Advent talked them into upgrading to Axys 3.8.5 last week, telling them they wouldn’t have any problems with the upgrade.  When you are unfamiliar with a client site, broad-sweeping statements like this are all too easy to make.  After the upgrade, their billing reports didn’t work.  The representative doing the upgrade was able to fix the standard billing report, but could not fix our compound billing report, which is used to generate client invoices.

Billing Report (created via compound reporting macros and replang)

Due to this issue, our customer’s billing process was on hold this week until the issue was resolved.  We received their call yesterday afternoon, and called them back before close of business, but didn’t hear from them until today.  We promptly connected to their system, reviewed their issue and resolved it; however, this incident certainly had the potential to end in technical tragedy.

I recently blogged on the different versions of Axys 3.x we see in use working with Advent clients.  The blog indicated that Axys 3.8.5 is a solid product release and should be an easy upgrade for users, but also underscored the need for users with customizations to anticipate difficulties.

Advent typically shows good sense in planning.  For example, in Axys to APX conversions, systems are run in parallel for months.  I am disappointed to hear about this incident, which I can only hope is an oversight, not the modus operandi for Axys upgrades.

Some people feel the need to push ahead no matter how close they are to quarter end.  Perhaps they make a bit more progress in doing so. Still the question for me is what benefit this upgrade had last week versus a couple weeks from now when quarter end reports have been produced.  If there is a benefit that offsets the risk, I am all for it.  In this case, I just don’t see it – not for my client.

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.

Unless your computer systems have been severely neglected, the time required to run Axys reports on any given day is unlikely to disappoint most users. Axys taps data and produces most de facto reports promptly. Axys users with less than a hundred portfolios are unlikely to care about processing speed issues, but for those of you that have several hundred or thousands of portfolios, the following performance enhancing tips for Axys can shave hours or perhaps days of processing time at quarter end:

1. Reign in Overzealous Virus Scanning
Norton Antivirus and other Antivirus products are engineered to perform real-time virus checks on all files every time they are accessed. While this functionality makes a lot of sense for office documents that could harbor viruses and the bulk of your company files, repetitively checking Axys file types is unlikely to uncover any viruses. In order to speed processing, disable real-time Antivirus checks on either selected folders like Axys3 or specific file types — like CLI files. It is still prudent to scan all files for viruses in a regular evening process. It just doesn’t make sense to check them each and every time they are accessed.

2. Optimize Network Configuration
In this day and age we still occasionally run into companies that have obviously spent plenty of money on technology, but overlooked the network needs of their investment operations area. IT consultants and/or in-house staff need to understand that Axys is not a client server application. As such, processing takes place on a user’s local machine, but is also dependent on server speed since files effectively need to be transferred to the local workstation so they can be processed. All workstations should be configured with gigabit network cards and connected to the server hosting the Axys application via gigabit switches. This sounds relatively straightforward, but “the devil is in the details.” If a multiple switches exist between the server and the workstations all of the switches involved need to be gigabit switches. The server should have a gigabit card. Most importantly the network cards on the workstations and server should be configured to connect at gigabit speeds.

3. Process Data Directly from the Server
If improving the network speed can enhance Axys performance, removing the network factor from the equation could further boost processing speed. In most circumstances, the operations team does not have the ability to connect to the server that houses Axys and run applications at their whim. Processing from the server can make sense in some smaller installations or in situations where the operations staff has been blessed with their own dedicated server that they have authority to use as they wish. However, processing directly from the server does not guarantee improved performance over processing from a networked workstation. It is possible that a server with poor processing power would under perform when compared with a faster computer connected to the server via a gigabit network connection. As IT professionals ourselves, we have reservations regarding this approach to resolving speed issues and do not currently believe it is a best practice.

4. Distribute Processing Across Multiple Workstations
Establishing the framework to be able to do report production in parallel empowers firms to scale their production as their business grows. If proper planning is done up front to organize the work required by various business units at a company, report production can be parceled off and done simultaneously or asynchronously as various business units demand. Proper planning entails creating the reports, scripts and macros to support parallel report production. Without the necessary infrastructure design and corresponding development, scripts and macros architected for a single user environment can’t be run autonomously as needed.

5. Buy a Faster Workstation(s) and/or Server
Buying a faster workstation should increase your processing speed, but it is conceivable that a bottleneck could exist at the server that limits the positive effect of purchasing a faster workstation. Likewise, buying a brand new server that is connected to older workstations will only do so much. When considering new hardware purchases, make sure you examine all possible bottlenecks. In our experience assisting clients with the development and integration necessary to automate their reporting systems, we found individual user’s Axys processing performance varied greatly. Recognizing the need to benchmark Axys processing speed, we created a simple report in REPLANG that allows us to quickly evaluate Axys performance from any workstation. This utility and other Axys tools are available from our website.

Feel free to download our basic speed test report (speed.rep) at www.isitc.com\aug or recreate it from the code listed below:

format 0
.\n\n\n\n
.Axys Processing Speed Benchmark Test\n
.v1.00 Last Modified 01-12-2007\n
.Copyright 2007 InfoSystems Integrated Inc.\n
.All rights reserved.\n
.\n
.www.isitc.com\n\n\n
#limit 10000
#accounts 0
ask #limit Enter the number of records to read?
#limit #limit 1-
.Start time $:now\n
label cc
load cli
#accounts #accounts 1+
if #accounts #limit >
goto ee
next cli
goto cc
label ee
. End time $:now\n
.#accounts read.\n\n
end

As you review and potentially implement the tips from this article, perform follow up speed checks to measure the effectiveness of the methods detailed. Test processing with our “speed report” from each individual workstation and your Axys server if possible. If all of your equipment is standardized you will likely see very little difference in the benchmarks of various workstations. In my own tests, the processing time of 10,000 records went from 46 seconds to 3 seconds, making me wonder why I didn’t optimize my environment sooner.

This article was originally published in the Advent User Group newsletter in 2007.

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 more information, please visit isitc.com, contact Kevin Shea via phone at 617-720-3400 x202, or e-mail at kshea@isitc.com.