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:
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.
