This is the final installment to the series Reconciling Orion and your Custodian. In this post, we are going to discuss reconciling transactions. As I mentioned in the previous posts, I am reconciling SEI with Orion and some of the issues I ran into may not apply to your implementations. I think this is especially true for transactions. So, I am going to talk about the steps and techniques in general terms that I used to reconcile transactions with Orion.
As we did with the holdings reconcile, I utilized ASP.NET C# console program and Orion Advisor’s API to create the recon process. Before we do any processing, we should check if the Orion recon is complete for the current day. A previous post Orion: Accessing Daily Reconciliation Data through the API shows how to get this information through the api. I display the Orion recon status and the as of date and time. If the Orion reconciliation is not complete, prompt the user whether the process should continue.
For my implementation, I took in a tab delimiter file of transactions from the custodian and parsed them into the appropriate fields and data types.
The custodian minimum data requirement is as follows:
Transaction Type Code
To retrieve the Orion transactions for the reconcile period, we need the start date, end date, and the create date.
Start date is the earliest transaction trade date in the custodian data.
End date is the latest transaction entry date in the custodian data.
Create date is the current date.
GET v1/Portfolio/Transactions/Verbose?expand=2&expand=16&$top=10000&startDate= startDate &endDate= endDate &$filter=Portfolio/CreatedDate gt datetime' createDate '
The return is limited to pages of 10000. Use $top and $skip in the query string to page through the data. We also used $filter to bring back only transaction created today regardless of the trade date. We are also expanding the transaction to bring back portfolio and user defined fields in the response.
Depending on your implementation, you may need to do additional filtering due to the way Orion implements offsets.
The minimum transaction data from Orion is detailed below:
The first challenge I had to overcome was that there is no direct tie back to the custodian transaction in Orion, so the only way to match the transaction is through the characteristics of the transactions. I am now going to list the steps I took to match a transaction to a transaction in Orion.
The first field I matched is the account.
The notes field in Orion has the SEI transaction code embedded in it labeled "File Transaction Code". I parse out this field and match with the transaction type code in SEI.
The next criteria are matching on the security. In Orion cash transactions the ticker will be “CASH:SWEEP” utilized for bill pay among others. If the transaction involves a security, we need to match on the ticker and the CUSIP. I had to make a separate API call in Orion to get the CUSIP.
GET v1/Global/Products/Verbose /productId
Depending on your implementation, cash transaction can match on distribution code, contribution code, explanation, paidTo, and paidFor.
Matching on quantity and amount was a little tricky because there are cases where in Orion only the amount would match and depending if it was a reversal, you may have to use the absolute value of the amount and quantity.
Following these steps, I was able to generate a matching and exception report. This report is not fool-proof and will sometimes report false positives and duplicates due to Orion’s use of remark transactions, but it has become a valuable tool for the ops group.
In this series I showed that you can reconcile data with Orion and some techniques that you could utilize in your implementation of a reconcile process. As I mentioned, you are responsible for the cleanliness and timeliness of your client’s data so whatever tool that you can provide to assist in that goal is worth looking into.