Reconciling Orion and your Custodian: Part 2 Holdings

In part 1 of this series, I talked about why it is necessary to perform your own recon because you are responsible for ensuring that your client’s data is correct and available in a timely manner. In this post, I am going to demonstrate how to reconcile holdings from custodians, such as SEI with Orion. These concepts should apply to holdings from any custodian, not just SEI.

The elements that we will validate include:

  • Account
    • Match the accounts from the custodian with the accounts in Orion. There should be an exact match of accounts with no additions.
  • Position 
    • Once a position is matched, the following checks can be executed:
    • Match the account and the ticker and/or cusip from the custodian with Orion. There should be an exact match of positions with no additions.
  • Shares
    • Compare the share quantity values for the matched positions.
  • Price
    • Compare the price of the matched position. You may need specialized logic for certain security types.
  • Cash
    • Compare account cash amounts. This could involve specialized logic depending on how cash is allocated.

Now that we have defined what we are going to do the next step is to do it. 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 if the process should continue.

The custodian minimum data requirement is as follows:

  • Account ID         
  • CUSIP   
  • Ticker Symbol
  • Shares                 
  • Price

For my implementation, I took in a tab delimiter file of positions from the custodian and parsed them into the appropriate fields and data types. I am not going to go into detail on how to parse a tab delimited file as there are plenty of tutorials online.   Also, for your implementation you may choose to do direct database access or utilize an api. Once your have the custodian holdings in a list you can now get the Orion holdings.

 

First, we need get all accounts because you will need this data to supplement the holdings data. Below is the api call to get all accounts:

 

GET v1/Portfolio/Accounts/Simple

Gets a simple list of accounts that the logged in user has access to. The return is limited to pages of 50000. Use $top and $skip in the query string to page through the data.

 

Response

     [

{   "id": 1,   "name": "sample string 2" }, {   "id": 1,   "name": "sample string 2" }]

Below is the endpoint to retrieve the positions utilizing the appropriate as of date. There are a few different ways to capture the as of date. Include in file, as part of the file name, or user input.

GET v1/Portfolio/Assets/Value/{asOfDate}

Gets a simple list of assets (including aum for the end of the date specified)

 

Arguments

               asOfDate: The aum returned is as of the end of this date.

 

Response

[

{

   "shares": 1.1,

   "price": 1.1,

   "value": 1.1,

   "productId": 1,

   "assetClass": "sample string 2",

   "ticker": "sample string 3",

   "isCustodialCash": true,

   "accountId": 5,

   "id": 6,

   "name": "sample string 7"

},

]


Once we have the accounts and position we can build the collection that will be used for the recon as shown below:

  

   public class OrionHolding

   {

       public int ClientId { get; set; }

       public int AccountId { get; set; }

       public string AccountNumber { get; set; }

       public string AccountName { get; set; }

       public bool isActive { get; set; }

       public decimal shares { get; set; }

       public decimal price { get; set; }

       public decimal value { get; set; }

       public int productId { get; set; }

       public string productName { get; set; }

       public string ticker { get; set; }

       public string cusip { get; set; }

       public string assetClass { get; set; }

           public int MatchCount { get; set; }

   }


Looping through the position, we can add the additional account data needed for the recon from the account collection.

 

// Loop through the positions

foreach (var item in _positions)

{

   // Create the _holdings object

   HoldingRecon orionHolding = new HoldingRecon();

  

   // Use the account id to query the accounts collection for the supplement data

   var _account = _accounts.Where(x => x.id == item.accountId).FirstOrDefault();

   if (_account != null)

   {

   orionHolding.AccountId = _account.id;

   orionHolding.AccountName = _account.name;

   orionHolding.AccountNumber = _account.number;

   orionHolding.ClientId = _account.clientId;

   orionHolding.isActive = _account.isActive;

   }

 

   // Update Asset Value Fields

   orionHolding.shares = item.shares;

   orionHolding.price = Math.Round(item.price,4);

   orionHolding.value = item.value;

   orionHolding.productId = item.productId;

   orionHolding.productName = item.name;

   orionHolding.ticker = item.ticker;

   orionHolding.cusip = item.ticker;

   orionHolding.assetClass = item.assetClass;

  

   // Add holdings item to the collection

   orionHoldings.Add(orionHolding);

}

 

Now that we have loaded the custodian and Orion holdings into collections, we can begin the actual recon.

We first need a distinct list of accounts from the custodian. I am using the custodian data as the driver for the process but keep in mind we will still need to check the Orion holdings to make sure a position exists in Orion but not at the custodian.

The statement below loads a collection of distinct account ids:

var accounts = custodianHoldings.Select(x => x.Account_ID).Distinct();

 

With this account list, we can loop through each account and reconcile on an account-by-account basis. This is safer because we are only checking positions within the designated account and there would be no chance of matching on positions in two different accounts.

By Looping through the accounts, we can load the positions just for the account for Orion and the custodian as shown below:

foreach (string account in accounts)

{

  var custodianAcctHoldings = custodianHoldings.Where(x => x.Account_ID == account);

  var orionAcctHoldings = orionHoldings.Where(x => x.AccountNumber == account);

   .

   .

   .

 

The Orion API end point used to get the positions did not include the cusip, so I had to retrieve the cusip in an additional call. I had a little trouble finding an endpoint that returned the cusip. The endpoint that I used to get the cusip is GET v1/Global/Products/Verbose/{key}. I loaded the found cusip into a collection for efficiency so that I would not need to make the same call for an existing security.

As we loop through the custodian’s positions in the account, we attempt to retrieve positions from Orion for the account. If none are found we insert the account not found exception into the exception collection.

// Loop through custodian positions for the account

foreach (custodianReconHolding custodianItem in custodianAcctHoldings)

{

// Get orion’s positions for the account

  orionActHoldings =

     orionHoldings.Where(x => x.AccountNumber == custodianItem.Account_ID);

if (_orionActHoldings.Count() == 0)

{

      // If no orion position are found write an exception and move on

custodianItem.MatchError = "ACCOUNT NOT FOUND";

custodianNotMatched.Add(custodianItem);                      

continue;

}          

 

Once we have a collection of positions for the custodian and Orion, we now can try to find a match by position.

 

// Match on cusip or ticker

var orionItem = _orionAcctHoldings.Where(x =>

   (x.ticker == custodianItem.Ticker_Symbol_ID || x.cusip == custodianItem.CUSIP_ID));

if (orionItem != null)

{

   // Mark found in the custodian collection

   custodianItem.matchCount = orionItem.Count();

   foreach (var oitem in orionItem)

   {

       // Mark found in the Orion Collection

     oitem.MatchCount = 1;

   }

}

else

{

   // If no orion position are found write an exception and move on

custodianItem.MatchError = "POSITIONS NOT FOUND";

custodianNotMatched.Add(custodianItem);                      

continue;

}          

 

 

 

Now that we have a matched position, we can now validate that the shares and price match as well.

 

var orionShares = orionItem.Where(x => x.shares == custodianItem.Shares)

         .FirstOrDefault();

if (orionShares == null)

{

// If shares do not match write an exception and move on

   custionItem.matchCount = 0;

   decimal oShares = orionItem.FirstOrDefault().shares;

   // calculate the variance

   decimal variance = (decimal) Math.Abs(oShares - custodianItem.Shares);

    custodianItem.Varance = variance;

    custodianItem.MatchError = "SHARES MIS-MATCH:" + " Orion: " +

       orionItem.FirstOrDefault().shares + " Custodian: " +

       custodianItem.Shares;

   custodianNotMatched.Add(custodianItem);

   continue

}

 

The price validation is pretty much identical to the share validation.

 

var orionPrice = orionItem.Where(x => x.Price == custodianItem.Price)

         .FirstOrDefault();

if (orionPrice == null)

{

// If Prices do not match write an exception and move on

   custionItem.matchCount = 0;

   decimal oPrice = orionItem.FirstOrDefault().Price;

   // calculate the variance

   decimal variance = (decimal) Math.Abs(oPrice - custodianItem.Price);

    custodianItem.Varance = variance;

    custodianItem.MatchError = "PRICE MIS-MATCH:" + " Orion: " +

       orionItem.FirstOrDefault().Price + " Custodian: " +

       custodianItem.Price;

   custodianNotMatched.Add(custodianItem);

   continue;

}

 

You may need to add additional logic due to when certain securities are priced or if the variance is deemed insignificant.

Below is the logic to validate cash positions. You may have you cash positions in a different feed, so I broke it out separately:

foreach (var cashItem in custodioanCash)

{

// Cash positions in orion have a ticker of "CASH:SWEEP"

var orionCash = orionHoldings.Where(x => x.ticker.ToUpper().Trim() == "CASH:SWEEP" &&

     x.AccountNumber == cashItem.Account_ID && x.shares ==

                         citem.Account_Cash_Amt).FirstOrDefault();

if (orionCash == null)

{

   // if the cash position is not found write an exception and move on

   cashItem.matchCount = 0;

   cashItem.MatchError = "CASH: NOT FOUND";

   custodianNotMatched.Add(cashItem);

    continue;

}

}

 

 

At this point we have a collection of custodian recon exceptions in custodianNotMatched, but what about the orion recon exception?

 

Well, remember when we were making the position as matched by updating the MatchCount item on the position record? We can now make use of that by the following:

 

var orionNotMatch = orionHoldings.Where(x=>x.MatchCount == 0).OrderBy(x =>

                   x.AccountNumber).ThenBy(x => x.ticker).ToList();

 

 

You now have two collections custodianNotMatched and orionNotMatched of recon exception where you can produce a report, write to a database, etc.

 

In this post, I tried to give you the general concept of how you would recon custodian data with data that is contained in Orion. There are certainly more efficient ways to program the recon, but it was easier for me to explain the steps broken out. I hope this was helpful.

Let's discuss your project .... Give us a call or drop us a line