In this tutorial we'll see how to use LUSID to perform the following task:
“As a portfolio manager, I want to generate a holdings report that reduces my cash balance by the money paid out in broker commissions, as well as by the cost of equities purchased.”
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 add a custom property to transactions to store commission amounts, and create a custom transaction type to reduce our cash balance by the total cost of the commission, as well as by the total cost of the equities purchased.
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 custom transaction type to reduce our GBP holding by the total cost of the commission as well as the total cost of the shares (£325 - £3.75 - £1.50 = £319.75). To do this, we need to:
- Store the commission data with each transaction. We can do this by creating a custom
Commissionproperty that we assign to each transaction with the appropriate value on upsert. - Create a custom
BuyWithCommissiontransaction 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
Buytransaction type). - Decrease the GBP cash holding by the total consideration (in the same way as the built-in
Buytransaction type). - Additionally decrease the GBP cash holding by the amount of the
Commissionproperty.
- Increase the number of BP shares held by the units purchased (in the same way as the built-in
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.
| Transaction type component | Value | Explanation | ||
| Alias | Type | BuyWithCommission | Defines the name of the transaction type. | |
| Movement #1 | Type | StockMovement | Updates an instrument (non-cash) holding. | |
| Direction | 1 | Increases the number of units. | ||
| Side | Side1 | Uses the built-in Side1 designed for instrument (non-cash) holdings. | ||
| Movement #2 | Type | CashCommitment | Updates a cash holding. | |
| Direction | -1 | Decreases the number of units. | ||
| Side | Side2 | Uses the built-in Side2 designed for cash holdings. | ||
| Movement #3 | Type | CashCommitment | Updates a cash holding. | |
| Direction | -1 | Decreases the number of units. | ||
| Side | ReduceCashBalanceByCommission | Defines the name of the custom side. | ||
| Security | Txn:SettleCcy | Updates the same cash holding as Side2. | ||
| Currency | Txn:SettlementCurrency | Updates the same cash holding as Side2. | ||
| Rate | SettledToPortfolioRate | Uses the same exchange rate calculation as Side2. | ||
| Units | Transaction/FBNUniversity/Commission | Decreases the number of units on the holding by the number of units stored by the Commission property on a transaction (for a currency, the number of units is equal to the amount). | ||
| Amount | Transaction/FBNUniversity/Commission | Decreases the amount of the holding by the amount of the Commission property on a transaction. | ||
In summary, for each transaction upserted to LUSID with the custom BuyWithCommission 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 additionally decreases the main GBP cash holding by the broker's commission.
Creating a property type for the Commission property
The first task is to call the CreatePropertyDefinition API to create a property type for the Commission property, in order to establish the unique 3-stage property key that we'll need both to create the custom transaction type and upsert transactions with properties:
- The first stage of the 3-stage property key must be in the
Transactiondomain, since the property belongs to transactions. The scope and code stages can be any intuitive strings, so for exampleTransaction/FBNUniversity/Commission. - The data type of the property should be 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 BuyWithCommission transaction type
Creating the custom ReduceCashBalanceByCommission side
The first task is to call the SetSideDefinition API to create the custom ReduceCashBalanceByCommission 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 now 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 and £319.75: