---
title: "Reducing a cash balance by the cost of broker commission"
slug: "reducing-the-cash-balance-by-the-cost-of-broker-commission"
updated: 2024-11-28T19:15:47Z
published: 2024-11-28T19:15:47Z
canonical: "support.lusid.com/reducing-the-cash-balance-by-the-cost-of-broker-commission"
---

> ## 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.

# Reducing a cash balance by the cost of broker commission

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](https://github.com/finbourne/finbourne-notebooks/blob/main/V1/university/T01006-Introduction-to-transaction-configuration/T01006-Reducing-the-cash-balance-by-the-cost-of-broker-commission.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 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:

![](https://cdn.document360.io/d575ad81-c0ed-4980-bbd1-d59ac5c3de82/Images/Documentation/image(24).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 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:

1. 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.
2. Create a custom `BuyWithCommission` 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 GBP cash holding by the total consideration (in the same way as the built-in `Buy` transaction type).
  - Additionally decrease the GBP cash holding 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 | `BuyWith` `Commission` | Defines the name of the transaction type. |
| Movement #1 | Type | `Stock` `Movement` | 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 | `Cash` `Commitment` | 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 | `Cash` `Commitment` | Updates a cash holding. |
| Direction | `-1` | Decreases the number of units. |
| Side | `ReduceCash` `BalanceBy` `Commission` | Defines the name of the custom side. |
|  | Security | `Txn:SettleCcy` | Updates the same cash holding as `Side2`. |
|  | Currency | `Txn:` `Settlement` `Currency` | Updates the same cash holding as `Side2`. |
|  | Rate | `SettledToPort` `folioRate` | 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](https://www.lusid.com/docs/api#operation/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 `Transaction` domain, since the property belongs to transactions. The scope and code stages can be any intuitive strings, so for example `Transaction/FBNUniversity/Commission`.
- The data type of the property should be 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-Reducing-the-cash-balance-by-the-cost-of-broker-commission.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:

![](https://cdn.document360.io/d575ad81-c0ed-4980-bbd1-d59ac5c3de82/Images/Documentation/image(25).png)

## Creating the custom BuyWithCommission transaction type

### Creating the custom ReduceCashBalanceByCommission side

The first task is to call the [SetSideDefinition](https://www.lusid.com/docs/api#operation/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](https://github.com/finbourne/finbourne-notebooks/blob/main/V1/university/T01006-Introduction-to-transaction-configuration/T01006-Reducing-the-cash-balance-by-the-cost-of-broker-commission.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(26).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-Reducing-the-cash-balance-by-the-cost-of-broker-commission.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:

## Loading transactions into a portfolio and storing the commission

We can now 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-Reducing-the-cash-balance-by-the-cost-of-broker-commission.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(27).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-Reducing-the-cash-balance-by-the-cost-of-broker-commission.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 and £319.75:

![](https://cdn.document360.io/d575ad81-c0ed-4980-bbd1-d59ac5c3de82/Images/Documentation/image(28).png)