I like to think that the greater purpose of much of the work I do with investment professionals is to simply show them what is possible. If you understand what can be done and how, I trust that you will have no problem coming up with many things that you want to do. Process automation, workflow improvements, integration, custom apps, and enhanced reporting are all easier to imagine and implement in your own environment when you have the benefit of the insights and perspective of relevant and significant user experience.
Like many before it, this article was written with that idea in mind. There is a very practical component (the source code below) which a small number of users may be able to take advantage of; the larger audience of managers, investment operations and technology professionals will hopefully come away with the idea of what can be done and roughly how it can be done, without necessarily planning to do this particular thing. More likely, readers will file away the knowledge of this article and revisit it later.
Most readers of this blog will not read it on the day I post it, or any time soon. Some day in the future, when the information in this particular blog becomes relevant to them, they will run a search and eventually find this article.
Audit Trail Usage
The Audit Trail (didpost.aud) file, a critical component of Advent APX and Axys, empowers users to review any transactions posted to portfolios in the system. It can also facilitate small- and large-scale removal of transactions that are posted in error. Among some Advent users, proactive management of the audit file is almost nonexistent until they hit the wall by letting the file grow to an unmanageable size or find it useful and perhaps even necessary to create subsets of the file as part of an audit.
One approach to proactively managing the file in Axys is to have a routine process whereby the file is copied quarterly or monthly and the current file is deleted. In this scenario, new transactions posted create a new Audit Trail file. And if you need to reference older transactions, you can temporarily copy the current file to another file and restore the file you need to access as the didpost.aud. When you are done accessing the old data, you revert things back by copying the original Audit Trail back to didpost.aud. Though the process is a bit clunky, somewhat risky, and manual, there are probably many firms using this approach. You could easily automate the processes I have described to create a more elegant solution.
In the past, I created some automation to routinely export the audit trail to a CSV file and create separate audit files of those exported CSVs for pre-selected periods. The code to do this is not complex, so I doubt I am the only one who has implemented this approach. This worked fine for many years, but it stopped working at some point. I am not sure when it happened, but I found that the ability to export the audit trail file in Axys 3.x via IMEX no longer worked reliably. Any attempt to export the Audit Trail would abend, leaving you with only part of the exported file you needed. Initially, I wasn’t sure whether the issue was limited to one of my customers, but after reviewing a few sites I work with, I found that the ability to export large audit trail files using IMEX no longer worked across the board. The issue is an Axys issue and not necessarily an APX issue, but I haven’t tried to export the audit trail in APX recently.
This failure to export Audit Trails posed quite a problem. Though the Audit Trail could be reviewed, and copy functions within the review windows worked, the size of the files made any kind of manual selection process onerous and prone to error. Besides, what would you do if you did select the data? I guess you could copy it into an empty trade blotter and save the blotter to a different file, but the trade blotter isn’t an Audit Trail file.
My assumption was that the integrity of the Audit Trail file was intact, but the function to export a larger file was broken. Usually, I frown upon working with Advent files directly. I prefer to work with exported files and consider that a best practice to avoid issues that can occur when the format of Advent files ultimately changes in later versions. In this particular instance, I was forced to read and write AUD files directly since the export mechanism wasn’t working properly, but I was happy with the end result and thought the source code might be useful to others.
Source Code to Fix Axys Audit Trail Export Issues
The code below reads the Audit Trail (didpost.aud) file in its native format, creates a time-stamped backup of the original file, and creates an annual didpost file for each year in a backup folder within the f:\axys3\aud folder. As some of you consider using this code, you may be concerned. After all, the integrity of the Audit Trail is not really something you want to mess around with lightly. You may even be thinking, Just because you can doesn’t mean you should. Nevertheless, I created, carefully tested, and successfully implemented the code to fix the audit trail for some Axys users that realized they had this issue.
Imports System.IO
Module Module1
Sub Main()
' This subroutine was created to address a problem in Axys 3.x.
' The routine serves two purposes. First of all, it addresses
' a problem where the Audit Trail can no longer be exported in
' its entirety. The secondary purpose of the routine is building
' annual Audit Trail files for record-keeping purposes.
' This process is intended to be run annually. When run it creates
' a backup of the existing Audit Trail and then create separate
' Audit Trail files for each of the previous years. However,
' given critical nature of the Audit Trail I recommend that you
' make sure you have additional backups of the Audit Trail to
' ensure that you can return the to original if necessary. When
' the process is completed the current Audit Trail will only have
' the records that were posted in the current year. Please note
' this code should be used carefully by users that completely
' understand its purpose.
' written in Microsoft Visual Studio's Visual Basic by
' Kevin Shea (aka AdventGuru) & updated 06/27/2019
' Disclaimer: This routine works fine for the specific instance
' it was created for, but could need additional modifications
' for different circumstances.
Dim idate As String
Dim d As Date
Dim i As Long = 0
Dim c As Integer
Dim x As Integer
Dim bufferpointer As Long = 0
Dim ByteCount As Long = 0
Dim CurrentYear As String = "0000"
Dim NewYear As String = "1111"
Dim ByteStop As Long = 0
Dim ByteStart As Long = 0
Dim size As Long
Dim posts As Integer
Dim InitialPostDate As Date
Dim semicolon As String = ";"
Dim CommentHeader() As Integer = {254, 250, 251, 252, 89, 0, 48, 0, 59, 0} '10 bytes
Dim FileHeader() As Integer = {97, 117, 100, 49, 46, 48, 49, 48} '8 bytes
Dim AuditTrailFolder As String
Dim AuditTrailBackupFolder As String
Dim AuditTrailFile As String
Dim time As DateTime = Date.Now
Dim format As String = "M_d_yyyy_HHmm"
AuditTrailFolder = "f:\axys3\aud"
AuditTrailBackupFolder = "f:\axys3\aud\backup"
AuditTrailFile = "didpost.aud"
System.IO.Directory.CreateDirectory(AuditTrailBackupFolder)
MsgBox("Please make sure no users are posting blotters before continuing ...")
My.Computer.FileSystem.CopyFile(AuditTrailFolder + AuditTrailFile,
AuditTrailBackupFolder + time.ToString(format) + ".aud")
MsgBox("Audit Trail Backup complete. Click okay to continue and analyze file.")
Dim sample As String
sample = Space(11)
Dim bytes() As Byte
bytes = File.ReadAllBytes(AuditTrailFolder + AuditTrailFile)
size = My.Computer.FileSystem.GetFileInfo(AuditTrailFolder + AuditTrailFile).Length
MsgBox("File is " + size.ToString + " bytes long.")
'put the file header in the buffer
Dim buffer(size) As Byte
For i = 0 To 7
buffer(i) = FileHeader(i)
Next i
bufferpointer = 8
ByteCount = 0
ByteStart = 8
Maxloop:
Do While ByteCount < size
ByteCount = ByteCount + 1
c = 0
For x = 0 To 9
If ByteCount + x = size Then Exit Do
If bytes(ByteCount + x) = CommentHeader(x) Then c = c + 1
Next x
If c = 10 Then
If Strings.Chr(bytes(ByteCount + 84)) = semicolon And Strings.Chr(bytes(ByteCount + 32)) = "a" And Strings.Chr(bytes(ByteCount + 33)) = "t" Then
idate = (Strings.Chr(bytes(ByteCount + 23)) + Strings.Chr(bytes(ByteCount + 24)) + "/" + Strings.Chr(bytes(ByteCount + 26)) + Strings.Chr(bytes(ByteCount + 27)) + "/" + Strings.Chr(bytes(ByteCount + 29)) + Strings.Chr(bytes(ByteCount + 30)))
End If
d = Convert.ToDateTime(idate)
posts = posts + 1
'if post date is greater than threshold of collection save buffer as distinct audit trail and reset buffer file
NewYear = d.ToString("yyyy")
If posts > 1 And (NewYear <> CurrentYear) Then
'a change in the current year to a new year triggers audit trail creation for the current year
ByteStop = ByteCount - 1
For i = ByteStart To ByteStop
buffer(bufferpointer) = bytes(i)
bufferpointer = bufferpointer + 1
Next
Dim s As New System.IO.FileStream(AuditTrailFolder + CurrentYear + ".aud", System.IO.FileMode.Append, System.IO.FileAccess.Write, System.IO.FileShare.ReadWrite)
s.Write(buffer, 0, bufferpointer)
s.Close()
ByteStart = ByteCount
CurrentYear = NewYear
bufferpointer = 8
End If
End If
If posts = 1 Then
InitialPostDate = d
CurrentYear = InitialPostDate.ToString("yyyy")
End If
End If
Loop
'We did everything, but the current year. Now let's do that too.
For i = ByteStart To size - 1
buffer(bufferpointer) = bytes(i)
bufferpointer = bufferpointer + 1
Next
buffer(bufferpointer) = 0
Dim f As New System.IO.FileStream(AuditTrailFolder + CurrentYear + ".aud", System.IO.FileMode.Append, System.IO.FileAccess.Write, System.IO.FileShare.ReadWrite)
f.Write(buffer, 0, bufferpointer)
f.Close()
MsgBox(posts.ToString + " posts analyzed. The first post was " + InitialPostDate.ToString("MM/dd/yyyy") + ". The last post was " + d.ToString("MM/dd/yyyy") + ".")
MsgBox("File copy with binary read all bytes and write all bytes complete.")
End Sub
End Module
Everyone has a different level of interest in the tech end of things. Some people want to understand it completely; others don’t want to know anything about it. I suspect that most of my readers fit somewhere in between those two extremes. On any given day, depending on what else is on your plate, you may lean towards one end or the other. In some cases, it simply makes more sense just to have someone like me take care of these things. If you have a problem with your Audit Trail that needs to be resolved, it may be one of those things.
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.
