Reconciling end-of-day positions with an external system

In this tutorial we'll see how to use LUSID to perform the following task:

“As a portfolio manager, I want to load start-of-day positions, intra-day transactions, and finally end-of-day positions from an external system, and then reconcile LUSID's holdings calculation with that of the external system.”

We'll see how to:

Set positions explicitly in a portfolio using cut labels to represent timestamps in a meaningful way.
Load transactions, and request LUSID to calculate holdings, at precise times.
Reconcile LUSID's holdings calculations with the end-of-day positions from the external system.

You can examine and run the complete code sample for this exercise from this Jupyter Notebook, providing you have suitable access control permissions. This can most easily be achieved by assigning your LUSID user the built-in lusid-administrator role, which should already be the case if you are the domain owner. If you are informed you do not have a license to perform a particular operation, please contact support.

Examining the source files

Let's assume we have a file from an external system that records start-of-day positions for cash, BP and Unilever on 7 March 2022:

At 4pm we receive another file with a stream of the day's transactions:

At 4:30pm, after market close, we receive a third file with the external system's view of our end-of-day positions:

We can see that:

We start the day with a quantity of GBP, BP and Unilever.
During the day we buy BP and sell Unilever.
We are likely to see breaks when reconciling the external system's end-of-day positions with LUSID's holdings calculation.

Creating a portfolio

We can call the LUSID CreatePortfolio API to create a portfolio in which to store transactions and calculate holdings, passing in a suitable scope, code, name and base currency. For a general introduction to portfolios in LUSID, read our portfolio documentation.

Step 3 of the accompanying Jupyter Notebook demonstrates how to do this using the LUSID Python SDK. You can execute the Notebook in Jupyterhub for your LUSID domain and then sign in to the web app to see the portfolio on the Portfolios dashboard, available from the left-hand Data Management menu.

Setting start-of-day positions

There are two ways to set positions explicitly in a portfolio:

The LUSID SetHoldings API states positions for a set of instruments, replacing (zeroing out) any existing instrument positions in a portfolio.
The LUSID AdjustHoldings API states positions for a set of instruments in a portfolio without affecting existing instrument positions.

Since our portfolio is currently empty, it does not matter which API we use to load the start-of-day file. Note that both APIs set settled positions.

Step 4 of the accompanying Jupyter Notebook demonstrates how to call the LUSID SetHoldings API using the LUSID Python SDK, passing in the LDN_Open cut label. LUSID creates an ‘adjustment transaction’ with a timestamp of 8:00am UTC on 7 March 2022 for each instrument in the start-of-day file, recording the number of units held and the cost but without subtracting the latter from the portfolio's cash balance. Note LUSID stores all datetimes in UTC.

You can execute the Notebook in Jupyterhub for your LUSID domain and then sign in to the web app to see the results on the Dashboard > Holdings. Use the Effective datetime picker (highlighted top right) to see LUSID's holdings calculation at precisely 8:00am on 7 March 2022:

Loading intra-day transactions

We can call the LUSID UpsertTransactions API to load the intra-day transactions using the built-in Buy and Sell transaction types, both of which (unlike the initial SetHoldings call) impact the portfolio's cash balance.

Step 5 of the accompanying Jupyter Notebook demonstrates how to do this using the LUSID Python SDK. You can execute the Notebook in Jupyterhub for your LUSID domain and then sign in to the web app to see the results on the Dashboard > Holdings, using the Effective datetime picker again to see LUSID's holdings calculation at precisely 4:29:59pm. Note since the settlement date is T+2, Units and Settled Units differ for equities, and cash holdings have a Holding Type of Cash Commitment to reflect future transfers in and out of the portfolio:

Setting end-of-day positions

We can call the LUSID SetHoldings API once more to set the external system's view of our end-of-day positions, this time using the LDN_Close cut label to set the time of each adjustment transaction to 4:30pm on 7 March 2022.

Step 6 of the accompanying Jupyter Notebook demonstrates how to do this using the LUSID Python SDK. You can execute the Notebook in Jupyterhub for your LUSID domain and then sign in to the web app to see the results on the Dashboard > Holdings, using the Effective datetime picker again to see LUSID's holdings calculation at precisely 4:30pm:

Reconciling LUSID's holdings calculation with the external system

LUSID automatically calculates a holding for each instrument in a portfolio on-the-fly by decomposing all the transactions affecting that instrument into their underlying economic movements, as determined by their transaction type.

We can request LUSID to calculate holdings for the portfolio at 4:29:59pm on 7 March 2022, to encompass the start-of-day positions and intra-day transactions, but not the external system's view of the end-of-day positions set at 4:30pm. We can then compare the two, effectively reconciling the portfolio with itself.

To do this, call the LUSID ReconcileGeneric API, passing in a left-hand side which constitutes LUSID's holdings calculations at 4:29:59pm, and a right-hand side which constitutes the external system's view at 4:30pm:

Step 7 of the accompanying Jupyter Notebook demonstrates how to do this using the LUSID Python SDK.
In the web app, navigate to the Reconciliations > Holdings dashboard, specify the same portfolio and the same date for both left-hand and right-hand sides, but different effective times for each (highlighted in red in the picture below).
You'll also need to select a recipe for each side, for example FinbourneExamplesRecipeMidThenBid, though note it actually has no effect on this simple reconciliation. If you don't have any example recipes in your environment, contact Technical Support.
Note also that to see the dashboard as it is below you will also need to change the default mapping configuration (highlighted in green). Instructions for this are in the appendix.

Note that:

LUSID assumes equity positions are settled and calculates our position in BP as 15,000, while the external system believes it to be 14,900, giving a break of 100.
LUSID calculates our position in Unilever as 1,000, while the external system believes it to be 1,010, giving a break of -10.
LUSID calculates our cash position as £39,000, which is £11,000 less than our initial position of £50,000. This is because -£20,000 is due out for BP and £9,000 is due in for Unilever. However, because we restated our cash position to that of the external system at 4:30pm, LUSID has subtracted the £11,000 in unsettled cash from the external system's end-of-day position of £38,000, giving a total of £27,000 and hence a break of £12,000:

Summary

In this tutorial, we've seen how to:

Set positions explicitly in a portfolio using one of two available APIs
Use cut labels to represent the time portion of a timestamp in an intuitive way
Roll back LUSID's effective at timeline to compare two views of the same data stored in LUSID.
Interpret LUSID's holdings reconciliation functionality to understand equity and cash position breaks.

Appendix: Changing the default mapping configuration in the LUSID web app

The default mapping configuration in the Reconciliations > Holdings dashboard works for settled but not unsettled transactions. Since our transactions are unsettled when we perform the holdings reconciliation on 7 March 2022, GBP cash breaks are not shown.

To show GBP cash breaks:

Click the Mapping Configuration dropdown, top right:
On the Select mapping screen of the Select mapping configuration dialog, click the Add new mapping button:
On the Map the properties screen, deselect the Holding/default/Units (Value) row and then click the Add properties button:
On the Queryable Key tab, search for Holding/default/Units in the Address Key column, and click on Value in the Operation column:
Select Sum and deselect Value in the picker, then select the whole row and click the Submit button:
Click the Done button and then save the configuration mapping with a new name, for example SumUnits: