--- title: "Breaking out broker commission as a separate cash holding" slug: "breaking-out-broker-commission-as-a-separate-cash-holding" updated: 2024-11-28T18:07:39Z published: 2024-11-28T18:07:39Z canonical: "support.lusid.com/breaking-out-broker-commission-as-a-separate-cash-holding" --- > ## Documentation Index > Fetch the complete documentation index at: https://support.lusid.com/llms.txt > Use this file to discover all available pages before exploring further. # 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](https://github.com/finbourne/finbourne-notebooks/blob/main/V1/university/T01006-Introduction-to-transaction-configuration/T01006-Breaking-out-broker-commission-as-a-separate-cash-holding.ipynb), 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](https://www.finbourne.com/contact). 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](/v1/docs/what-is-a-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: ![](https://cdn.document360.io/d575ad81-c0ed-4980-bbd1-d59ac5c3de82/Images/Documentation/image(30).png) 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](/v1/docs/understanding-how-lusid-uses-transaction-types-to-generate-holdings#examining-the-builtin-lusid-transaction-types). | **Transaction type component** | **Value** | **Explanation** | | --- | --- | --- | | Alias | Type | `BuyWithSeparateCommission` | 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 | `BreakOutCommissionSeparately` | Defines the name of the custom side. | | | Security | `Txn:SettleCcy` | Updates a cash holding in the same currency as `Side2`. | | | Currency | `Txn:SettlementCurrency` | Updates a cash holding in the same currency as `Side2`. | | | Rate | `SettledToPortfolioRate` | Uses the same exchange rate calculation as `Side2`. | | | Units | `Transaction/FBNUniversity/Commission` | Decreases the number of units on a cash 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 a cash holding by the amount of the `Commission` property on a transaction. | | Mapping | `Transaction/FBNUniversity/TrackCommission` | Maps the result of the movement not to the main GBP cash holding but to a separate GBP cash holding registered with the portfolio by the `TrackCommission` SHK. | 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](https://www.lusid.com/docs/api#operation/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](https://www.lusid.com/docs/api#operation/CreatePortfolio) API, specifying a suitable scope, code, name and base currency, and registering the SHK. Step 3 of the [accompanying Jupyter Notebook](https://github.com/finbourne/finbourne-notebooks/blob/main/V1/university/T01006-Introduction-to-transaction-configuration/T01006-Breaking-out-broker-commission-as-a-separate-cash-holding.ipynb) 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: ![](https://cdn.document360.io/d575ad81-c0ed-4980-bbd1-d59ac5c3de82/Images/Documentation/image(31).png) ## Creating a property type for the Commission property We need to call the [CreatePropertyDefinition](https://www.lusid.com/docs/api#operation/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](https://github.com/finbourne/finbourne-notebooks/blob/main/V1/university/T01006-Introduction-to-transaction-configuration/T01006-Breaking-out-broker-commission-as-a-separate-cash-holding.ipynb) 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](https://www.lusid.com/docs/api#operation/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](https://github.com/finbourne/finbourne-notebooks/blob/main/V1/university/T01006-Introduction-to-transaction-configuration/T01006-Breaking-out-broker-commission-as-a-separate-cash-holding.ipynb) 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): ![](https://cdn.document360.io/d575ad81-c0ed-4980-bbd1-d59ac5c3de82/Images/Documentation/image(32).png) ### Including the custom side in the transaction type definition We can now call the [SetTransactionType](https://www.lusid.com/docs/api#operation/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](https://github.com/finbourne/finbourne-notebooks/blob/main/V1/university/T01006-Introduction-to-transaction-configuration/T01006-Breaking-out-broker-commission-as-a-separate-cash-holding.ipynb) 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: ![](https://cdn.document360.io/d575ad81-c0ed-4980-bbd1-d59ac5c3de82/Images/Documentation/image(33).png) ## Loading transactions into a portfolio and storing the commission We can call the [UpsertTransactions](https://www.lusid.com/docs/api#operation/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](https://github.com/finbourne/finbourne-notebooks/blob/main/V1/university/T01006-Introduction-to-transaction-configuration/T01006-Breaking-out-broker-commission-as-a-separate-cash-holding.ipynb) 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: ![](https://cdn.document360.io/d575ad81-c0ed-4980-bbd1-d59ac5c3de82/Images/Documentation/image(34).png) ## Generating a holdings report We can now call the [GetHoldings](https://www.lusid.com/docs/api#operation/GetHoldings) API to generate a holdings report. Step 7 of the [accompanying Jupyter Notebook](https://github.com/finbourne/finbourne-notebooks/blob/main/V1/university/T01006-Introduction-to-transaction-configuration/T01006-Breaking-out-broker-commission-as-a-separate-cash-holding.ipynb) 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: ![](https://cdn.document360.io/d575ad81-c0ed-4980-bbd1-d59ac5c3de82/Images/Documentation/image(35).png)