Breaking out broker commission as a separate cash holding

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

“As a fund accountant, I want to create a bespoke holdings report that breaks out the money paid in broker commissions on equity purchases into a separate cash holding.”

Note: 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, contact support.

Imagine we have a data provider that includes broker commission as a data point in a stream of equity purchase transactions.

We cannot model broker commission out-of-the-box in LUSID. There is no field on the transaction in which to store this data, and the built-in Buy transaction type does not define the economic impact.

We can create a portfolio with a sub-holding key (SHK), which enables us to configure the default way in which LUSID generates and groups holdings.

We can then add a custom property to transactions to store commission amounts, and create a custom transaction type that maps commission to the SHK, in order to break it out as a separate cash holding line item in a holdings report.

Examining the source file

Let's assume we have transactions in a CSV file ready for upsert to LUSID:

We can see that:

• The first transaction deposits £500 in the portfolio.

• The second transaction purchases 25 BP shares at a cost of £125; the broker's commission is £3.75.

• The third transaction purchases 10 BP shares at a cost of £50; the broker's commission is £1.50.

By default, LUSID cannot store or process the commission data. When all trades have settled, LUSID generates a holdings report with 35 BP shares and £325.

We can create a SHK to break out a separate holding, and a custom transaction type to map the commission to the SHK, such that the holdings report contains three line items: 35 BP shares, £325 in the main GBP cash holding, and -£3.75 + -£1.50 = -£5.25 in a separate GBP cash holding. To do this, we need to:

1. Create a TrackCommission SHK (which is a standard property) and register it with a portfolio.

2. Store the commission data with each transaction. We can do this by creating a custom Commission property that we assign to each transaction with the appropriate value on upsert.

3. Create a custom BuyWithSeparateCommission transaction type to model the following behavior for each equity transaction:  

   • Increase the number of BP shares held by the units purchased (in the same way as the built-in Buy transaction type).

   • Decrease the main GBP cash holding by the total consideration (in the same way as the built-in Buy transaction type).

   • Decrease a separate GBP cash holding maintained by the SHK by the amount of the Commission property.

Modelling the custom transaction type

We can re-use the first two movements from the built-in Buy transaction type, and create a third movement to process the commission. See the full definition of this transaction type.

In summary, for each transaction upserted to LUSID with the custom BuyWithSeparateCommission transaction type:

• Movement #1 increases the number of units in the equity holding by the number purchased.

• Movement #2 decreases the main GBP cash holding by the total consideration.

• Movement #3 decreases a separate GBP cash holding by the broker's commission.

Creating a portfolio with a TrackCommission SHK

Since a SHK is just a custom property, the first step is to create a property type. To do this, we call the LUSID CreatePropertyDefinition API and establish the unique 3-stage property key that we'll need when registering the SHK with the portfolio:

• The first stage of the 3-stage property key must be in the Transaction domain, since the property belongs to transactions. The scope and code stages can be any intuitive strings, so for example Transaction/FBNUniversity/TrackCommission.

• The data type of the property should be string.

We can then call the LUSID CreatePortfolio API, specifying a suitable scope, code, name and base currency, and registering the SHK.

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 Data Management > Portfolios dashboard:

Creating a property type for the Commission property

We need to call the CreatePropertyDefinition API again to create a property type for the Commission property, establishing a unique 3-stage property key in the Transaction domain, for example Transaction/FBNUniversity/Commission. The data type should be a number, since we're storing cash amounts.

Step 4 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 property type on the Data Management > Properties dashboard:

Creating the custom BuyWithSeparateCommission transaction type

Creating the custom BreakOutCommissionSeparately side

The first task is to call the SetSideDefinition API to create the custom BreakOutCommissionSeparately side as per the definition in the table above, with the units and amount fields mapped to the Transaction/FBNUniversity/Commission property.

Note: The PUT SetSideDefinition API operates differently to the standard PUSH Upsert* model used elsewhere in LUSID. A side is replaced rather than updated, so it's important to specify the entire definition each time.

Step 5.1 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 custom side on the System Settings > Transaction Types dashboard (navigate to the Sides tab):

Including the custom side in the transaction type definition

We can now call the SetTransactionType API with the alias and movements in the table above, and the custom side as the third movement.

Note: The PUT SetTransactionType API operates differently to the standard PUSH Upsert* model used elsewhere in LUSID. A transaction type is replaced rather than updated, so it's important to specify the entire definition each time.

Step 5.2 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 custom transaction type on the System Settings > Transaction Types dashboard:

Loading transactions into a portfolio and storing the commission

We can call the UpsertTransactions API to load our transactions into a suitable portfolio, assigning the Transaction/FBNUniversity/Commission property to each with the appropriate value from the commission column in the source file.

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 examine the Transactions dashboard.

To show the Broker commission property column in the LUSID web app, click the cog icon (highlighted in green), choose Add column, and then select the property to display from the Property tab. Note also the transaction Type of each equity purchase:

Generating a holdings report

We can now call the GetHoldings API to generate a holdings report.

Step 7 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 examine the Holdings dashboard.

As expected, after all trades have settled, LUSID generates a holdings report with 35 BP shares, £325 in the main GBP cash holding and -£5.25 in a separate GBP cash holding reflecting the total cost of broker commission: