AdventGuru

I have been approached many times over the years regarding portfolio accounting system conversions and projects brought about by startups, breakaway advisors, mergers of firms that use Advent Software products, and impending migrations away from Advent products. Each of these types of projects share similar requirements and know-how to extract, transform and load data from the source system into the destination system, but the requisite integration of portfolio accounting records from two different firms and datasets into a single dataset post-merger or acquisition is, by definition, at another level of complexity.

In initial conversations, it is not uncommon for firms to ask me if they can just do it themselves and the answer is … maybe.  If they have the necessary knowledge and skillset, they could, but the experience of doing it multiple times breeds competence, confidence, tools, and valuable insights, making future projects more turnkey.  At most of the firms I work with, the person asking me this question already has a job, and this isn’t it.

Unifying Portfolio Accounting Systems and Records

In portfolio accounting terms, merging companies together is about putting like with like, identifying both common and unique asset classes, security types and securities.   Reviewing this data carefully and making decisions about how you want things to appear in the merged environment creates the foundation for the work that needs to be done.  This process is much easier if one party can clearly be identified as the primary firm that the secondary party’s data is being merged into.

The most fundamental data to the project are the asset class and security type settings.  The differences in data here determine the overall complexity of the work required.  Assuming these data were identical, there would be little to do.  You could simply review the security master, find any ticker naming inconsistencies, and rename those securities.  You would still need to merge the prices, splits, groups, portfolios, performance, indexes, and composites, but the process would be relatively easy.

Unfortunately, it is usually not that simple.  You basically need to get the two systems to speak the same language through the process of reorganizing and renaming securities while maintaining the integrity of the portfolio accounting systems.  You may add, remove, or modify asset classes and security types in the merged environment, but you need to do it with the knowledge of what can be done and what you may be giving up in the new environment.  Most obviously, removing an asset class means that you won’t be able to update performance for that asset class any longer in the merged environment.  That may be okay if asset class performance isn’t in use.

Specific care must be taken not to invalidate performance records or transactions based on security type parameters.  Reclassing a security type as another asset class impacts historic performance.  Renaming a security and its security type can also invalidate performance history for the asset classes involved, unless performance history is regenerated with the new configuration afterwards.

Most of this article is written in reference to work that I have performed merging Axys datasets, but the work required for APX is very similar.  I skirt over certain areas of the process during my description in an effort to keep this blog under 2,500 words, since the purpose of this blog to shed light on the process involved and not necessarily to give readers an A-to-Z guide on how to do their own Portfolio Accounting merge project – though I suspect some may use it as such.

How to Merge Portfolio Accounting Records With Another Firm
I categorize the work required into the following phases:

• Preparation: Backup, Profiles and Initial Assessment
• Reorganization and Renaming
• Merge
• Validation

Preparation
There is no better way to get started on a project like this than making a backup of the systems involved prior to any work that you perform.  There are typically other backups being run on these systems, but I want to make sure I can restore systems to their original state prior to any work I do, and you should, too.  In an Axys environment, you should be able to simply zip the entire folder (e.g. f:\axys3) from Windows Explorer or run a PowerShell script command like this:

compress-archive -LiteralPath ‘f:\axys3\’ -DestinationPath ‘f:\axys3\backup.zip’ -Force

As part of the preparation process, I typically create multiple partitioned workspaces.  In a recent job where there were already two Axys profiles for separate business lines, I created two additional profiles for my client during the merge project: one as the environment that I would transform to be like or compatible with the firm it would be merged into, and the other environment as the destination for the final merged portfolio accounting system.

As a result of this approach, the pre-merge profiles are all accessible after the merge is completed, and the client can easily test the results of the merge and verify that everything has been correctly merged before cutting over to the new environment.

I also export the INF files for each of the Axys profiles to be merged and do some automated comparisons between the files to help me determine how much work is involved in merging the environments.

Reorganization and Renaming
Reorganization is the crux of any merger project.  It is the reorganization of asset classes and security types itself that leads to much of the renaming, but some of the renaming takes place because it is required to eliminate security duplicates.  The phases are intended to be separate and distinct where you would finish one and not repeat it, but in practice the process within and between phases tends to be more iterative.

Asset Classes
There are no shortcuts here.  Ideally, my preference would be to leave it alone if possible. However, I realize that isn’t possible in all situations.  You may be able to avoid changing the asset class definitions of the firm you are merging data into, and you probably should, but you will likely need to change some of the asset class definitions in either the source or destination to accommodate the merge.

Security Types
As far as I know, you cannot import security types through IMEX, and that is probably a good thing.  I suspect I could figure out a way to force this, but it would be a bad idea.  Along with asset classes, the security type table is the heart of how your portfolio accounting system is organized. The definition of security types determines how each security type is treated in your portfolio accounting system.  Edits must be made manually, and some edits are not allowed.  If you are creating a security type that is a lot like another, you can speed the process by inserting a row and adding a copy of a previously defined security type.

Industry Groups and Industry Sectors
In order to merge security masters, you need to have already merged the industry group and industry sector files, because Advent won’t let you import a security with an invalid industry group or industry sector.  Merging isn’t quite the word for it, because you are unlikely to merge these tables like you would splits.  A more apt description of this merge process would be standardizing and/or reclassifying one set of securities to match the industry groups and industry sectors definitions defined by another profile.

Any changes made to the industry sectors in the target environment could impact the integrity of performance history by industry sector (if that data was generated in either of the source environments), and necessitate regenerating that performance history with the new industry sector definitions.

Renaming Securities
You will find that the reclassification of security types (e.g. efus fmagx versus mfus fmagx) forces you to rename the impacted securities.  Differences in the way firms may name symbols (e.g. swvxx versus swvx.x, ibm versus IBM, 34393t401 versus 34393T401) are also likely to create a slew of necessary renames.

Once again, there is no reason to create something to do this, because it already exists.  Renaming securities can be accomplished by running the process manually. That process works and is fine for a handful of securities, but when you want to rename dozens or hundreds of securities – never mind thousands, it just won’t do.  The real job here is to utilize the existing renaming capabilities through the use of a script, leveraging Advent’s chgsym command to do multiple security renames on each line of the script.

Merge

This is where it all comes together.  Once more, you can utilize basic script functions built into Axys and APX to efficiently merge certain files with minimal effort. Much of what is required can be automated. However, in some cases, it may be simpler to just do the work manually.

Prices
The exported price file formats for Axys and APX are simple enough that you could easily write something to merge price files, but you shouldn’t because that functionality already exists in Advent’s mergepri script command.  Instead, you create code to make a script to merge the necessary price files.  The mergepri command allows you to specify a destination and multiple sources.  The first source is the primary.  Prices in the first source file will not be overwritten by prices found in the secondary source files.

Splits
You could do this manually, but I have included some sample code to do it so you don’t have to.  The program works with exported CSV copies of firms split.inf files and creates a CSV file that must be imported into the merged Advent environment.

' The MergeSplits subroutine and the functions it calls (IsRecordInFile
' & TruncateZeros) are used to merge two exported Advent Axys split.inf
' files into a single split file ready for import into a merged Axys
' environment.

' written in VBA by Kevin Shea (aka AdventGuru) & updated 02/24/2024

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

Sub MergeSplits(SourceFile1 As String, SourceFile2 As String, DestinationFile As String)

Dim sf1 As Integer
Dim sf2 As Integer
Dim df As Integer
Dim Record As String

df = FreeFile
Open DestinationFile For Output As #df

sf1 = FreeFile
Open SourceFile1 For Input As #sf1

Do While Not EOF(sf1)

  Line Input #sf1, Record
  Print #df, TruncateZeros(Record, ",")

Loop

Close #sf1

sf2 = FreeFile
Open SourceFile2 For Input As #sf2

Do While Not EOF(sf2)

  Line Input #sf2, Record
  Record = TruncateZeros(Record, ",")
  If Not IsRecordInFile(SourceFile1, Record) Then Print #df, Record

Loop

Close #sf2
Close #df

Debug.Print "done."

End Sub

Function IsRecordInFile(SourceFile As String, RecordPassed As String) As Boolean

Dim ff As Integer
Dim RecordToCompare As String
Dim tempIsRecordInFile As Boolean
tempIsRecordInFile = False

ff = FreeFile

Open SourceFile For Input As #ff

Do While Not EOF(ff)

  Line Input #ff, RecordToCompare
  If TruncateZeros(RecordToCompare, ",") = RecordPassed Then
    tempIsRecordInFile = True
    Exit Do
  End If
  
Loop

Close #ff

IsRecordInFile = tempIsRecordInFile

End Function

Function TruncateZeros(SplitRecord As String, FieldSeparator As String)

Dim tempTruncateZeros As String
Dim ZeroEnd As Integer
Dim Cursor As Integer
Dim DecimalFound As Boolean
Dim SplitFields() As String

tempTruncateZeros = SplitRecord
DecimalFound = False
ZeroEnd = 0

SplitFields() = Split(SplitRecord, FieldSeparator)
'to standardize split records for comparison this routine gets rid of extra zeros that can exist in split records

If InStr(SplitFields(2), ".") > 0 Then DecimalFound = True
If DecimalFound Then

  For Cursor = Len(SplitFields(2)) To 1 Step -1
    If Mid$(SplitFields(2), Cursor, 1) <> "0" Then
      ZeroEnd = Cursor
      Exit For
    End If
  Next Cursor
  tempTruncateZeros = SplitFields(0) + FieldSeparator + SplitFields(1) + FieldSeparator + Left(SplitFields(2), ZeroEnd)
End If

If Right$(tempTruncateZeros, 1) = "." Then tempTruncateZeros = Left(tempTruncateZeros, Len(tempTruncateZeros) - 1)
TruncateZeros = tempTruncateZeros

End Function

The code above does a little more than just combine two files and remove the duplicates; it also truncates any trailing zeros in the split quantity to reduce the likelihood of duplicate split records. The same end goal could be achieved using one the most basic SQL queries – if the data for the split files was already loaded into tables as illustrated below.

SELECT SplitDate, SplitSymbol, SplitFactor from SplitSource1
UNION SELECT SplitDate, SplitSymbol, SplitFactor from SplitSource2
ORDER BY SplitDate;

This approach certainly looks more direct, but you would need to define the database tables properly. You would also need extract the data to bring it into the database, and store the results of the query file in one of the accepted file formats (TSV or CSV) to import it back into the system.

Dataport

Any redundant symbol (??sym.inf) and account (??act.inf) translation tables need to be merged (e.g. vsact.inf and vssym.inf); a similar approach to the merging of the splits can be used here, but these files are already in fixed text format, so they don’t need to be exported.

After merging the translation tables you may find that you need to update selected interface account number labels. For example, if you needed to create Schwab $vsact labels for newly merged portfolios using the values from existing $csact portfolio labels, and retain the $csact label, you could accomplish that in a few minutes by using the following REPLANG code to produce a script to perform the label updates.

outfile f:\axys3\auto\addvslab.scr n
load cli
.addlabel -files $:file.cli -labelrec \$\vsact,$csact\n
$csact ?
next cli
fclose

Please note, this is something that works in Axys that would not work in APX since the addlabel script command is not a valid APX script command. In APX, you would post the new label through the trade blotter.

Security Masters

You need to update the security master by merging the unique records from the secondary firm into the primary firm, and then import the new security master into the merged profile.  If the security master imports without errors, you are ready to move on to the simpler aspects of the merge. I do this through code I have written for this specific to merging security records, and do an import with a full replace, but it could potentially be done manually or through the use of IMEX’s optional import of unique records.

Portfolios, Groups, Performance, Indexes and Composites

It is worth mentioning that Advent has a script command mergecli that merges portfolios, but that’s not applicable here.  That command only merges portfolios from the same database, but it can be a useful tool to aggregate portfolios for other purposes.

If you have performed the previous steps, the now-standardized data for portfolios, groups, performance, indexes, and composites can be copied to the container with the other merged records, but you may need to rename some of these objects and any objects with dependencies if any of the names are redundant with the environment you are merging them into.  For example, if a portfolio (code) was already in use, you would need to rename the portfolio itself, its performance records, and the occurrence of that portfolio in any groups.

Validation

When the previous phases are complete, you are finally ready to verify and eventually validate the results.  Initially, this can be done quickly by spot-checking various reports and portfolios.  If you find issues, you may need to revisit the previous phases, make fixes, and rerun processes that you have already created to merge the files again.  When you reach a higher level of confidence about the merged set of data, you should reconcile consolidated appraisals from each of the systems.  If you have made significant changes to asset classes and/or their underlying securities, you may need to regenerate performance history and validate that, too.

How long does it take?

Surprisingly, these projects can go very quickly if key personnel make themselves available to do their part.  Those folks need to be able to tell you how they want things organized and be ready to participate in the validation process.  So long as key personnel motivated, the process can go as fast as they want it to go.

In the past few months, I have done a couple of these projects.  The jobs themselves were very similar; one was done over three months – not by any means a rush job.  I did the other one in less than two weeks, but I probably could have completed the work in less than a week if we needed to.

In my opinion, the relative size of the databases doesn’t significantly add to or subtract from the amount of time or work required.  Either way, you are performing the same processes.  In other words, it isn’t about how much data there is; it is about the processes you run to make one set of data ready to be merged into the other.

How much does it cost?

Prices for this service are all over the map.  When firms merge, they tend to be somewhat price-insensitive regarding the cost of certain things they deem critical to the merger.  One firm I talked with a few years back told me they paid 100k to merge their portfolio records after a recent merger, and they didn’t bat an eye at it. Another firm I am familiar with was given a quote for 40k by a competitor.  To me, these quotes sound like someone throwing spaghetti at the wall to see if it sticks.  In the latter case, I charged a justifiable premium for my expertise and was still able to do the work easily for less than half of what they were originally quoted.

In closing, there are many other data types that may need to be merged depending on how your firm uses Advent, such as extended data, FXs, FFXs, and factors, but the purpose of the blog is to explain what is involved in a typical portfolio accounting system merger. While some may be tempted to do this on their own, I don’t think anyone that has asked me to assist them with the process of merging portfolio accounting records has ever regretted it.

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.