Tag Archive: conversion


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

 

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.

 

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.

In 2005, Advent released the first version of Advent Portfolio Exchange (APX). This paved the way for enterprise users to take Advent more seriously, while reassuring rapidly growing firms that APX would service their future needs and provide support for legacy requirements. Initially, this change was fine with many of the Axys users that have historically comprised Advent’s established userbase, but after years of baseline Axys updates and Advent’s predominant emphasis on APX, the patience of some Axys users has worn thin.

Today Axys users likely fit into one of four camps:

  1. They are planning to move to APX in the near future.
  2. They understand their options well enough, but don’t think the benefits of moving to APX outweigh the costs.
  3. They simply don’t care about APX or competing products – just as long as Axys keeps doing what they need, everything is fine.
  4. They are frustrated by Advent’s perceived abandonment of their business segment and are either actively seeking a replacement to Axys or in the process of converting to a new system.

I have repeatedly been told that owning a self-hosted version of APX is 2-3 times more expensive than Axys, but don’t take my word for it.  Advent’s pricing changes regularly.  Call Advent and get a quote.   Early on, APX conversions were very expensive, and some firms were quoted six-figure conversion costs.  Although these costs have been reduced substantially, APX is still significantly more expensive than Axys.

In the past, conversions were much more complex and time-consuming.  The primary issue seemed to be the normalization of a wide variety of Axys data.  As APX has evolved, Advent and the conversion utility within APX have created efficiencies in the conversion process.  In a recent conversation with a client, who is now considering the move from Axys to APX, I learned that Advent took copies of their Axys files and was able to demo APX 4.x with representative data from their firm in about a week.

In addition to the difference in the software cost, Advent recommends that APX users host the app in a traditional database server and application server configuration.  Some users may opt to host IIS on a separate server as well.  Currently, many small and medium businesses (SMBs) simply host Axys on their primary file server.

Why would a firm running Axys want to pay the premium for APX?

The answer is improved security, infrastructure, and functionality that meets the expectations of those with higher technological standards – historically enterprise users, not SMBs.  APX promised this from day one, but APX v1 was, well, version 1.  I sat in on a couple dog and pony shows for APX when it was first introduced.  In one, the presenter abruptly but politely disconnected a conference call with one of their early “testimonial” users when the conversation went in an unexpected direction.  At Advent’s conference in Orlando, more time than Advent would have liked was spent on the topic of APX latency, but these types of issues can be experienced with any v1 product covering as much ground as APX.

One of the most valuable benefits of Advent’s portfolio accounting systems is the maturity of their products.  This maturity is the primary reason why so many things in Axys and APX work the way they should.  Though much has changed at the core of Axys and APX, both of these systems can potentially run a report created on The Professional Portfolio (the precursor to Axys and APX) 25 years ago.  Due to the continuity of Advent’s portfolio management systems, users of The Professional Portfolio and Axys have been able to jump into APX without a lot of training.

Last year, when I attended the Advent conference in Boston, a panelist from the Advent Users Group touched upon the issue of APX owners using APX like Axys.  Her point was that you should use the newer features of APX v3, but as she mentioned it, I couldn’t help thinking how much the earlier versions of APX were like Axys.  Aside from the SQL backend and other related platform benefits, it felt like you were still using Axys, only it was more complicated and clunky.

Even now, we see that the heart of Axys continues to beat inside APX, playing a critical role with respect to backward compatibility and legacy reporting.  Over the course of its first five years, APX has matured significantly.  That initial awkward period is behind Advent APX.

In the past 18 months, Advent has made significant strides towards fulfilling the promise of APX, introducing additional SSRS reports in APX 3.x and the ability to create dashboards in APX 4.x.  I have finally heard mention of an API.  Yes, APX is more complex than Axys, but now that more of the infrastructure has been built out, you can feel better about it.  With these improvements, APX should make sense for a larger number of investment firms.

APX is a logical upgrade for Axys clients who:

  1. Want to minimize the need to retrain staff on a new portfolio accounting system.
  2. Understand that additional features, such as SSRS reporting and dashboards, come hand in hand with incremental complexity and the costs of an enterprise solution.

Those that don’t want to take on as much overhead may find solace in moving to APX on Demand (a SAAS offering), but in doing so they will have to sacrifice some of the flexibility and functionality available to self-hosted users of APX.

 

Final Score: APX 4, Axys 0

Looking at version releases of APX and Axys over the past seven years, it is easy to understand the focus of Advent’s primary resources.  Though four minor releases of Axys have been made since APX came out, there have been no major releases.  A major release implies a major change to the software, and at this point it doesn’t appear that a major Axys release is coming from Advent.

Last year’s acquisition of Black Diamond provides Axys users with another choice under the Advent umbrella, but I haven’t seen many users go from Axys to Black Diamond. While Axys improvements have stalled out, Advent’s full-throttle APX development has many of its Axys users feeling disenfranchised.  From my own perspective, Advent appears to be losing some valuable clients through a failure to more actively communicate with their SMB client base.

If Advent wants to keep Axys clients as Advent clients, they should connect with their users and reassure them that they want to work with them. Still, Advent should also understand that for some, it may make more sense to move on.

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.

Last year, I helped one of our smaller clients get a new phone system – not because they needed one, but because the owner decided it was time for something NEW. There was nothing wrong with the old system, and I am fairly sure that this particular client will get no additional benefit due to their limited use of the phone system. I appreciate this approach when applied to the purchase of new computer equipment, which naturally obsoletes itself as new software updates are implemented. However, the NEW logic that was used to send the old phone system to the landfill was lost on me.

More recently, I experienced a similar issue with a client of ours in regard to their portfolio management system. After many years of using Axys, they decided they were ready for something NEW. They didn’t spend much time considering APX. They wanted something really NEW (i.e., not Advent). I was concerned, and not about our own meal ticket. Though we do a lot of specialty work for Advent Software users, we do our best to remain impartial and support RIAs that use other portfolio management systems. If we see a better product that makes both technological and financial sense for a specific client’s requirements, we will let them know.

There are usually pros and cons to switching from one system to another. When making a decision to move from one portfolio management system to another you are effectively betting that the benefits will justify the cost. I was concerned that my client was making a bad decision, but I also didn’t want to be the naysayer. We spent a limited amount of time discussing the potential switch: I did my best to explain what it was that made their existing system so valuable, however underappreciated it currently was. I’ll blog on the virtues of Advent Software’s portfolio management systems in the near future, but in short, my opinion is summarized below:

Advent Software is a market leader with portfolio management systems that are mature and malleable. At its most fundamental level, the portfolio management system your firm uses is a foundation that can be built upon by your firm, third-parties, and your primary software vendor. A more mature infrastructure gives your firm fewer surprises and a wider variety of solutions to choose from. By selecting a leading portfolio management system your firm benefits from the logical motivation of third-party vendors to make their products available on that platform first ensuring that your firm has the ability to embrace emerging technology at a competitive pace.

At some point, we all want something NEW. It is an opportunity to get rid of the problems with which we have been dealing and get a new lease on life. And while getting a new whatchamacallit is a good thing when we are talking about your ten-year old car, it is not necessarily the right solution for problems with your spouse, or your ten-year old portfolio management system. Our client is now in the process of converting to INDATA, but we probably won’t know how happy they are with their decision until next year.

Unfortunately, the decision to replace your existing portfolio system with another less mature but newer-looking system is likely to result in disappointment. Conversions typically leave some of your data behind in order to get you into the new system in a timely and cost-effective manner. Firms that adopt new systems without sufficient research frequently find out that they have traded one set of problems for another. Those who champion the switch to a new portfolio management system want a successful outcome that validates their decision. As such, those who are most apt to recognize shortcomings early on may opt to sweep the inadequacies under the rug and stick to their guns rather than question their initial decision.

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.