Category: Automation


I have said it before, and I’ll say it again: if Advent does well, it follows that someone like me should do well, too.  I profit directly from Advent customers when they hire me, and indirectly when companies that provide services to Advent customers hire me.  That said, there is a certain amount of Advent’s dysfunction that helps my business.  In short, I offer what Advent cannot and/or does not want to provide to their customers, and many of my customers hire me specifically because I do not work for Advent.

 

Steeplechase race in 1912, Celtic Park, N.Y., through water

On the other hand, there is a limit to what is reasonable.  If Advent proverbially lights their hair on fire and jumps off a tall building, that is not good for me or my business. With thirty-five years of experience dealing with Advent, acting as an advocate to clients, I have seen some absurd things and have a high tolerance for some of it, but even I will occasionally find myself flummoxed.  It pains me to say so, since I really want Advent to succeed… at least enough that they aren’t losing business to competitors without good reason.  That is bad for their business and mine.

Advent Options

If you are to believe the natural progression of things as they are presented to the masses, it follows that some Axys customers who are frustrated by its limitations can be better served by Black Diamond or APX.  From what I have seen personally, Advent hasn’t had great success moving Axys customers to Black Diamond.  I have seen a few good Axys clients go Orion and Tamarac, but I have yet to see one of my clients go to Black Diamond successfully. This is not meant as a criticism of Black Diamond – it’s just an observation that my typical clients haven’t found the complete solution they are looking for in Black Diamond.

For those firms where Axys is no longer the answer and Black Diamond cannot meet their expectations, APX may offer a viable upgrade path.  Aside from its cost, APX has always been a relatively easy upgrade choice for Axys users to make because it is an Advent option that supports the legacy of Axys, which includes knowledge of portfolio management and performance fundamentals, transactions, processes, reporting, and scripts.  That means that when those customers move to APX, much of the reporting, infrastructure and established workflows can remain the same.

In short, APX offers everything that Axys does, plus the benefits of an Enterprise/SQL server platform.  The incremental learning required for operations staff to go from Axys to APX is very manageable, and things pretty much work like they did in Axys.

Among APX offerings, I know of at least five possible permutations:

  1. APX Self-Hosted on Premise
  2. APX Self-Hosted in the Cloud by a Third Party
  3. APX Dedicated with AOS
  4. APX Dedicated without AOS
  5. APX Multi-Tenant (hosted by Advent)

I have listed these APX environment options in order of my personal preference, based on specific experience with all of the options and the ease with which one can effectively manage, integrate, automate, and enhance systems. From my perspective, the first two options clearly give you the greatest degree of control and autonomy over your own systems. Choosing one of the other three options puts you in a place where Advent is enforcing various controls over your system – good and bad. Firms that have always had complete control over their systems and want to continue to do so should bristle at the very idea of this.

Advent’s Dedicated Hosting Service for APX Users

One that is used to hosting their own APX system on premise might think that hosting via Advent’s dedicated environment would be nothing but a boon, but reality quickly shatters that dream for savvy, hands-on APX users. Advent’s value add here is clearly AOS, but if that is the case, why would they ever sell someone dedicated hosting without the AOS service?  When they do that, the Dedicated Hosting service provided is arguably no better than what a third-party vendor can deliver.  Oh, wait, that’s not true.  It is potentially worse, because the system will be locked down in such a way that you won’t be able to do the things you would be able to do if your APX hosted environment was provided by a third-party resource that needed to make sure you were satisfied with their service.

This is because, without an AOS resource, some things that a firm would want to do to automate and enhance their systems simply cannot be done because they fall under the responsibility of the AOS silo.  You can call Advent support all you want, but they cannot resolve your problem, because only AOS can do these things.

Want to schedule a process to run at a certain time?  You can’t do that.  Do you want to install a third-party product?  You can’t do that.  Do you want to log into the server directly?  You can’t do that either. All of these things are only possible with the cooperation of an assigned AOS resource.   And even if you have an AOS resource, you still cannot do those things, but instead must ask your AOS resource. 

A fitting analogy for comparing the work required in their locked-down environment to what one might otherwise do in a self-hosted environment could be comparing the 100-yard dash to steeplechase.  As a result, the automation that you may create is more likely to resemble a Rube Goldberg machine than a typical streamlined process due to Advent’s forced assistance and rules regarding what can and cannot be done. 

 

Unfortunately, as you invite a higher degree of involvement from Advent vis-à-vis Advent’s dedicated (a.k.a. “managed”) hosting model, you lose control of the systems you are entrusted to manage and improve unless you had the foresight to have Advent agree upfront to the access rights required or are willing to spend countless hours dickering with Advent about the rights, which may ultimately end in frustration anyway.  This comment is not based on my direct experience with these systems alone, but also what I have heard amongst my peers.

Advent basically has the keys to the kingdom in this scenario, and the users are at the mercy of Advent. It’s almost plausible that they cannot allow you access to certain areas of the system that you usually have, but at the end of the day, when you cannot easily perform work that you were able to do in the past when you self-hosted APX, it feels much more like a ruse intended to ensure that Advent gets not just what clients have agreed to pay them, but any other work you might want to perform in the environment related to automation.

However, the problem is that they cannot necessarily perform the same work of a contractor with specific experience Advent may lack.  From my perspective, Advent’s focus in their Dedicated Hosting seems to be maintaining the status quo, not constantly striving to build a better mousetrap to service your business processes.  That is the directive I am looking for from my clients.

Anyone that can’t envision how Advent could consume their money, time, and resources while providing this service to them may lack experience working with Advent, or the imagination necessary to take their own client experiences with Advent and extrapolate the possibilities once Advent has a greater degree of control over their systems.  The frustration this arrangement creates can be amplified if the firms facing this entanglement are committed to long-term, bajillion-dollar contracts.  These large, multi-year contracts could be part of the reason Advent feels comfortable repeatedly saying the one word my clients never want to hear—no.

Over the years, Tamarac, Orion, Addepar and Ridgeline have all made inroads to capture market share from what was once predominantly Advent’s business to keep or lose, and they will continue to do so until Advent makes improving its rapport with clients a priority.  You may have already guessed, but Advent’s worst enemy and biggest threat to the future of their business may be Advent’s hubris, and winning the WatersTechnology Buy-Side technology award for the Best Portfolio Accounting Provider two years in a row is unlikely to change that.  Even so, if you have deep pockets and are truly ready to hand the reins over to Advent, you may be happy with the results.

 


Kevin Shea Impact 2010

About the Author: Kevin Shea is the Founder and Principal Consultant 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.

After Labor Day, summer is effectively over for investment professionals. Most executives and senior staff of financial services firms return to the office from vacations recharged and ready to go. Next week, they may need to tap that extra energy if they have to cope with the conversion of TD Ameritrade accounts to Schwab accounts. Schwab Advisor Center will be temporarily unavailable from September 2nd at 2am ET through September 5th at 5am ET while they complete the integration between Schwab and TD Ameritrade.

Earlier this summer, Schwab stopped generating transactions and positions, as well as other files that were scheduled to be sunset. Schwab gave customers over a year of warning that this change was coming. However, I cannot help wondering how difficult it would have been to keep generating those files, or better yet, refer those customers to me directly for assistance. Schwab wasn’t interested in doing either. As a result, customers that were impacted may still need to update or change systems that were dependent on those data feeds for daily workflows in order to process the transactions and positions data received after 07/11/23.

 

 

Creating Price Files Compatible with Advent Products

When I blogged about Schwab ending support for legacy price and transaction files around this same time last year, I didn’t know if I would be willing to create a translation utility to take Schwab CRS transaction files and convert them to Advent’s CS transaction files.  Writing the utility for prices was relatively simple by comparison.  I collected a one-time service fee from customers, and in turn facilitated their ability to keep their pricing workflow intact through the use of that utility. The utility processes CRS security files (CRSyyyymmdd.SEC) to create CS price files (CSmmddyy.PRI). Once the CS files have been created, they show up in Dataport as they always have in the past.

I felt good about the fact that I was able to help those customers in very tangible way with little effort on my part.  If you haven’t read the blog, you can find it here.  In short, the blog details the issue at the time, some possible solutions, and warns users that the conversion of transactions will be a larger and more costly issue to address.  At that time, I was not committed to writing a transaction conversion utility.

I have included a rudimentary VB code sample below to translate Schwab’s newer CRS SEC file to the legacy CS PRI Advent format required by some users:

 

VB
Sub CreatePriceFile(Folder As String, FileDate As Date)

'This subroutine converts Schwab CRS files to Advent's
'naming convention (CSmmddyy.PRI) and file format to be
'compatible with Axys and Dataport.  This is the same format
'that was provided via the Schwab Point-to-Point interface.

'Once this routine has been run on a CRSyyyymmdd.SEC file, 
'Dataport will recognize and be able to convert these files
'as it did prior to Schwab turning off the feed. 

' written in VBA by Kevin Shea (aka AdventGuru) & updated 08/30/2023

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

On Error GoTo CPErrorHandler

dim Fields() as string
Dim Spaces, OutfileFH, IngestFH As Integer
Dim Record, Price, RawPrice, CUSIP, SType, Ticker, AssetIs As String
Dim SourceFilename, DestinationFilename As String

SourceFilename = "CRS" + Format(FileDate, "YYYYMMDD") + ".SEC"
DestinationFilename = "CS" + Format(FileDate, "MMDDYY") + ".PRI"

OutfileFH = FreeFile
Open Folder + DestinationFilename For Output As #OutfileFH

IngestFH = FreeFile
Open Folder + SourceFilename For Input As #IngestFH

Do While Not EOF(IngestFH)

  Line Input #IngestFH, Record

  If Left$(Record, 2) = "D1" Then
  'Only process the detail records.
  'Ignore header "H1" and summary "T1" records.
 
    Fields = Split(Record, "|")
    'This is a great VB command that splits the contents of the record and puts
    'it into an array.  For example, fields(0) contains the value of first field
    'in the record, fields(1) contains the value of the second field and so on.
     
    'Assign the fields to named variables we need to build the price file, which
    'makes the code easier to read later.

    Ticker = Trim$(Fields(9))
    AssetIs = Trim$(Fields(6))
    CUSIP = Trim$(Fields(11))
    SType = Trim(Fields(8))
    Spaces = 9 - (Len(SType) + Len(Ticker))

    'Remove the leading zeros from the price field value.
    'May not be absolutely necessary, but we do it anyway.
    'You might be tempted to use the replace statement here
    'instead, but that would have unattended consequences.
    'We are only removing the leading zeros.

    For x = 2 To Len(Fields(34))
      If Mid$(Fields(34), x, 1) <> "0" Then
        Price = Right$(Fields(34), Len(Fields(34)) - (x - 1))
        Exit For
      End If
    Next x
  
    'Ignore securities if they are derivatives.
    'If a ticker exists use that.
    'Otherwise, assume we need to use the CUSIP.

    If AssetIs <> "DERV" Then
      If Trim$(Ticker) = "" Then
        Spaces = 12 - (Len(SType) + Len(CUSIP))
        Print #OutfileFH, SType + Left$(CUSIP, 8) + Space(Spaces) + Price
      Else
        Spaces = 11 - (Len(SType) + Len(Ticker))
        Print #OutfileFH, SType + Trim$(Ticker) + Space(Spaces) + Price
      End If
    End If
  
  End If
Loop

Close #IngestFH
Close #OutfileFH

Debug.Print "Price file " + DestinationFilename + " built from " + SourceFilename + "."
Log ("Price file " + DestinationFilename + " built from " + SourceFilename + ".")

Exit Sub

CPErrorHandler:

'Nothing happens here, but some logging.
Log "An error occurred in the sub (CreatePriceFile)"

End Sub

Sub Log(LogMessage As String)

Dim LF As Integer
LF = FreeFile
Open Application.ActiveWorkbook.Path + "\SPTP_Price_File_Translator_Log_" + Format(Date$, "MMDDYYYY") + ".txt" For Append As #LF
Print #LF, Format(Date$, "MM/DD/YYYY") + " " + Format(Time$, "HH:MM:SS") + " " + LogMessage

Close #LF

End Sub

Expand

 

CRS Transaction File Translation

As the July 2023 deadline imposed by Schwab approached, some of the users I assisted with the CRS Price File Translator reached out to me to see if I was going to create a tool to address it. Eventually, I agreed to do it in July.  Working from samples of the first customer’s historic CS transaction files (CSmmddyy.TRN) and the newer Schwab CRS transaction files (CRSyyyyddmm.TRN), I was able to map over fifty different types of transactions and build a tool to convert the CRS files provided by Schwab into the format compatible with Advent and Dataport. 

There is some redundancy in Schwab’s transaction mappings.  Schwab seems to create a distinct transaction code and mapping for more transactions than necessary. For example, there are at least six different types of dividend mappings and similarly at least three different ways that they categorize a check that was written.  My goal in writing the translator was to preserve the information and create a file nearly identical to what Schwab has been generating for several years.

 

The CRS Position File

When I agreed to create the conversion utility for transaction files, I failed to realize that I would also have convert the position files so that users can continue to utilize the Schwab Reconciliation Report in Advent. So I created the position translator gratis.  While analyzing the file I found that Schwab has two different record types encoded in their CRS RPS files. The first block of records appears to be non-cash assets.  The second block of records are cash-only. Those records start with “D1” and “D2” respectively.

Those familiar with Schwab’s cash types may already know that they have nearly twenty different types of “cash” that get baked into the position files.  The CRS RPS file has the asset value for these various cash types for each account stored in a single record, which means that we needed to read the cash records from the CRS file and create multiple records in the CS file.  Conversely, the non-cash records are translated into a single record in the CS file we created.

The tool has been used to convert the transactions and positions from 07/12/23 forward.  It is now being used in day-to-day operations at that firm.  There have been a small number of mapping issues we needed to fix, but overall, the CRS Translator – which now creates CS PRI, TRN and RPS files – is working well.  In the past week, I signed up a few additional customers for the service and expect to hear from more potential customers due to the upcoming Schwab/TD Ameritrade (TDA) work scheduled for Labor Day weekend.

 

What is going to happen to AD files currently generated by TD Ameritrade?

Apparently, Schwab will stop providing similarly constructed legacy files to the TDA advisors.  My understanding is that those Schwab customers will receive CRS files populated with their data for the first time on 09/05/23.  They have been receiving empty files with headers alone to date. If their workflows have any dependencies on the old file formats, they will need to convert those files ASAP or make other changes to their systems to implement new workflows, such as ACD, so they can continue to download prices and transactions and reconcile positions in a timely manner.

 


About the Author: Kevin Shea is the Founder and Principal Consultant 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_000000129907XSmallIn my experience working with investors across the nation both small and large, there is at least one recurring theme.  Their sophisticated workflows hinge on semi-automatic processes that rely in part or completely on people.  I suppose that is a good thing when operator oversight is a plus, but when I get involved most of these firms have realized that they literally want to push a button and have things done as simply, quickly and reliably as possible.

Ten years ago, one of my clients experienced organic growth so rapid that it drove their firm from one that managed hundreds of millions to billions in assets in less than two years. While investment managers are known to consistently take pains to build processes that allow their businesses to scale efficiently, explosive growth can strain or disrupt established workflows.

In this case, the effect of the growth was dramatic. It demanded that certain processes be automated. One of the most important processes was related to trading foreign stocks in local currencies.  The influx of trades from new business was overwhelming.  Back-office employees that were already working hard needed to work even longer hours to keep pace with the incoming business.

More specifically, the firm’s back office needed to manually enter both transactions to trade currencies and the associated equity trades. In the multi-currency version of Axys, purchases and sales of foreign stocks in local currency require three transactions: two to exchange the currency and one to buy or sell the security.  As a result, every purchase or sale required two additional transactions related to foreign currency exchange.  They were entering three manual transactions in the trade blotter for every trade they made – even partial fills of an outstanding order required these transactions.  The entries to the trade blotter were tedious, time-consuming, and a potential source of operator error; the firm knew they needed to automate them.


Money concept

Understanding the Required Workflows

As part of their process, the firm waited until they got the executed FX rates from an assortment of brokers before they could enter the corresponding trades and post them in their portfolio accounting system.  This backlog of trades created a slew of manual trade blotter work that ultimately had to be done after hours to make sure the firm was ready to trade the next day.

A homegrown Order Management System (OMS) populated the Axys trade blotters of the traders at the company with open trades. Those blotters were utilized to track open trades and never actually posted.  Once the execution info was reported, our automation needed to create the additional transactions that back-office staff was entering manually. The workflow also required that the trade blotters for individual traders be updated to reflect that the executed trades were no longer part of the open trades. In effect, our automation needed to rewrite the traders’ blotters as well as the trade blotter of the operations employee running the app and post the latter.


Building the Prototype/v1

In a relatively quick timeframe (40 hours), we built a working prototype using Visual Basic that:

  1. loaded the outstanding trades from each trader’s blotter into an Access database
  2. created a process where outstanding trades could be selected and associated with trade execution info to generate the required trade blotter entries
  3. imported executed trades to the trade blotter for review and posting
  4. updated the open positions in the trader’s blotters
  5. produced reports (via Crystal Reports) detailing trades pending and actual execution info

In short, the app pulled pending transactions from their homegrown OMS and allowed users to associate foreign currency execution rates and other specifics of trade execution with specific orders to produce the necessary Axys trade blotter transactions automatically.  Once the execution info was recorded, the user would exit the app, which in turn automatically updated the user’s trade blotter and the traders’ blotters.

Version 2

The next version of the app was under development for over six months and cost nearly 40k, but it met the needs of the firm. The core functionality of v1 with respect to workflow was preserved, but we added many features.  In terms of the initial cost, v2 was expensive, but over a period of nearly ten years it required almost no maintenance. The multi-currency trade automation solved an immediate and urgent need when it was originally developed, and continued to save the firm hours of back office processing work every week for nearly ten years, until it was finally decommissioned in 2015 as a byproduct of the firm’s transition to Moxy and Geneva.

Though the application wasn’t cheap to build or maintain initially, it paid for itself many times over during its tenure as an integral part of the daily workflow at firm that knows the value of automation.


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.