V1/product/ibor/Output Transactions.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "    <form action=\"javascript:code_toggle()\"><input type=\"submit\" id=\"toggleButton\" value=\"Hide docstring\"></form>\n",
       "    \n",
       "         <script>\n",
       "         function code_toggle() {\n",
       "             if ($('div.cell.code_cell.rendered.selected div.input').css('display')!='none'){\n",
       "                 $('div.cell.code_cell.rendered.selected div.input').hide();\n",
       "             } else {\n",
       "                 $('div.cell.code_cell.rendered.selected div.input').show();\n",
       "             }\n",
       "         }\n",
       "         </script>\n",
       "\n",
       "     "
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "from lusidtools.jupyter_tools import toggle_code\n",
    "\n",
    "\"\"\"Output Transactions\n",
    "\n",
    "This notebook shows how LUSID uses synthetic transactions to fill in the gaps between user-instructed transactions and corporate actions.\n",
    "\n",
    "Attributes\n",
    "----------\n",
    "instruments\n",
    "transactions\n",
    "portfolios\n",
    "adjust holdings\n",
    "output transactions\n",
    "corporate actions\n",
    "stock split\n",
    "build transactions\n",
    "sub-holding keys\n",
    "\"\"\"\n",
    "\n",
    "toggle_code(\"Hide docstring\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Output Transactions Notebook\n",
    "\n",
    "\n",
    "When LUSID generates a portfolio, set of holdings or a valuation it bases all calculations on the entire timeline of transactions within a portfolio. \n",
    "\n",
    "This is straightforward when a portfolio contains just user-added transactions, but there are many other ways that portfolios can be updated within LUSID.\n",
    "\n",
    "This notebook shows how LUSID creates synthetic transactions within portfolios to represent non-transaction events such as Corporate Actions (e.g. bonus issue, dividend, stock split) or Holdings Adjustments. \n",
    "\n",
    "These synthetic transactions can be seen in the Output Transactions at the end of the notebook.\n",
    "\n",
    "We will also explore how LUSID handles corporate actions on instruments that are being held under multiple strategies.\n",
    "\n",
    "\n",
    "## Part 1: Setting up LUSID\n",
    "\n",
    "To initialise our LUSID environment run the cell below\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import general purpose packages\n",
    "import json\n",
    "import os\n",
    "import pandas as pd\n",
    "from datetime import datetime\n",
    "\n",
    "# Import lusid specific packages\n",
    "# These are the core lusid packages for interacting with the API via Python\n",
    "import lusid\n",
    "import lusid.models as models\n",
    "from lusid import ApiException\n",
    "from lusid.utilities import ApiClientFactory\n",
    "from lusidjam.refreshing_token import RefreshingToken\n",
    "from lusidtools.cocoon.cocoon import load_from_data_frame\n",
    "from lusidtools.cocoon.cocoon_printer import (\n",
    "    format_instruments_response,\n",
    "    format_portfolios_response,\n",
    "    format_transactions_response,\n",
    ")\n",
    "from lusidtools.pandas_utils.lusid_pandas import lusid_response_to_data_frame\n",
    "from lusidtools.cocoon.utilities import create_scope_id\n",
    "\n",
    "# Set pandas options\n",
    "pd.set_option('display.max_columns', None)\n",
    "\n",
    "# Authenticate our user and create our API client\n",
    "secrets_path = os.getenv(\"FBN_SECRETS_PATH\")\n",
    "\n",
    "# Initiate an API Factory which is the client side object for interacting with LUSID APIs\n",
    "api_factory = lusid.utilities.ApiClientFactory(\n",
    "    token=RefreshingToken(),\n",
    "    api_secrets_filename = secrets_path,\n",
    "    app_name=\"LusidJupyterNotebook\")\n",
    "\n",
    "# Create a scope and portfolio code\n",
    "scope = create_scope_id()\n",
    "portfolio_code = \"EQUITY_UK\"\n",
    "strategy_shk = \"strategy\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Load source transactions\n",
    "\n",
    "We have a csv of transactions representing initial holdings of 4 FTSE100 stocks that we can use to initialise our portfolio. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>portfolio_code</th>\n",
       "      <th>portfolio_name</th>\n",
       "      <th>portfolio_base_currency</th>\n",
       "      <th>ticker</th>\n",
       "      <th>sedol</th>\n",
       "      <th>instrument_type</th>\n",
       "      <th>instrument_id</th>\n",
       "      <th>name</th>\n",
       "      <th>txn_id</th>\n",
       "      <th>txn_type</th>\n",
       "      <th>txn_trade_date</th>\n",
       "      <th>txn_settle_date</th>\n",
       "      <th>txn_units</th>\n",
       "      <th>txn_price</th>\n",
       "      <th>txn_consideration</th>\n",
       "      <th>currency</th>\n",
       "      <th>strategy</th>\n",
       "      <th>cash_transactions</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>EQUITY_UK</td>\n",
       "      <td>LUSID's top 10 FTSE stock portfolio</td>\n",
       "      <td>GBP</td>\n",
       "      <td>GB0002162385</td>\n",
       "      <td>SEDOL1</td>\n",
       "      <td>equity</td>\n",
       "      <td>EQ_1234</td>\n",
       "      <td>Aviva</td>\n",
       "      <td>trd_0001</td>\n",
       "      <td>StockIn</td>\n",
       "      <td>02/01/2020</td>\n",
       "      <td>04/01/2020</td>\n",
       "      <td>120000</td>\n",
       "      <td>5</td>\n",
       "      <td>600000</td>\n",
       "      <td>GBP</td>\n",
       "      <td>balanced</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>EQUITY_UK</td>\n",
       "      <td>LUSID's top 10 FTSE stock portfolio</td>\n",
       "      <td>GBP</td>\n",
       "      <td>GB0002162385</td>\n",
       "      <td>SEDOL1</td>\n",
       "      <td>equity</td>\n",
       "      <td>EQ_1234</td>\n",
       "      <td>Aviva</td>\n",
       "      <td>trd_0002</td>\n",
       "      <td>StockIn</td>\n",
       "      <td>02/01/2020</td>\n",
       "      <td>04/01/2020</td>\n",
       "      <td>12000</td>\n",
       "      <td>5</td>\n",
       "      <td>60000</td>\n",
       "      <td>GBP</td>\n",
       "      <td>balanced</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>EQUITY_UK</td>\n",
       "      <td>LUSID's top 10 FTSE stock portfolio</td>\n",
       "      <td>GBP</td>\n",
       "      <td>GB0031348658</td>\n",
       "      <td>SEDOL3</td>\n",
       "      <td>equity</td>\n",
       "      <td>EQ_1236</td>\n",
       "      <td>Barclays</td>\n",
       "      <td>trd_0003</td>\n",
       "      <td>StockIn</td>\n",
       "      <td>02/01/2020</td>\n",
       "      <td>04/01/2020</td>\n",
       "      <td>150000</td>\n",
       "      <td>2</td>\n",
       "      <td>300000</td>\n",
       "      <td>GBP</td>\n",
       "      <td>balanced</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>EQUITY_UK</td>\n",
       "      <td>LUSID's top 10 FTSE stock portfolio</td>\n",
       "      <td>GBP</td>\n",
       "      <td>GB0031348658</td>\n",
       "      <td>SEDOL3</td>\n",
       "      <td>equity</td>\n",
       "      <td>EQ_1236</td>\n",
       "      <td>Barclays</td>\n",
       "      <td>trd_0004</td>\n",
       "      <td>StockIn</td>\n",
       "      <td>02/01/2020</td>\n",
       "      <td>04/01/2020</td>\n",
       "      <td>100000</td>\n",
       "      <td>2</td>\n",
       "      <td>300000</td>\n",
       "      <td>GBP</td>\n",
       "      <td>banking</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>EQUITY_UK</td>\n",
       "      <td>LUSID's top 10 FTSE stock portfolio</td>\n",
       "      <td>GBP</td>\n",
       "      <td>GB0005405286</td>\n",
       "      <td>SEDOL5</td>\n",
       "      <td>equity</td>\n",
       "      <td>EQ_1238</td>\n",
       "      <td>HSBC</td>\n",
       "      <td>trd_0005</td>\n",
       "      <td>StockIn</td>\n",
       "      <td>02/01/2020</td>\n",
       "      <td>04/01/2020</td>\n",
       "      <td>20000</td>\n",
       "      <td>6</td>\n",
       "      <td>120000</td>\n",
       "      <td>GBP</td>\n",
       "      <td>banking</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>EQUITY_UK</td>\n",
       "      <td>LUSID's top 10 FTSE stock portfolio</td>\n",
       "      <td>GBP</td>\n",
       "      <td>GB0008847096</td>\n",
       "      <td>SEDOL7</td>\n",
       "      <td>equity</td>\n",
       "      <td>EQ_1240</td>\n",
       "      <td>Tesco</td>\n",
       "      <td>trd_0006</td>\n",
       "      <td>StockIn</td>\n",
       "      <td>16/01/2020</td>\n",
       "      <td>18/01/2020</td>\n",
       "      <td>12000</td>\n",
       "      <td>9</td>\n",
       "      <td>108000</td>\n",
       "      <td>GBP</td>\n",
       "      <td>balanced</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  portfolio_code                       portfolio_name portfolio_base_currency  \\\n",
       "0      EQUITY_UK  LUSID's top 10 FTSE stock portfolio                     GBP   \n",
       "1      EQUITY_UK  LUSID's top 10 FTSE stock portfolio                     GBP   \n",
       "2      EQUITY_UK  LUSID's top 10 FTSE stock portfolio                     GBP   \n",
       "3      EQUITY_UK  LUSID's top 10 FTSE stock portfolio                     GBP   \n",
       "4      EQUITY_UK  LUSID's top 10 FTSE stock portfolio                     GBP   \n",
       "5      EQUITY_UK  LUSID's top 10 FTSE stock portfolio                     GBP   \n",
       "\n",
       "         ticker   sedol instrument_type instrument_id      name    txn_id  \\\n",
       "0  GB0002162385  SEDOL1          equity       EQ_1234     Aviva  trd_0001   \n",
       "1  GB0002162385  SEDOL1          equity       EQ_1234     Aviva  trd_0002   \n",
       "2  GB0031348658  SEDOL3          equity       EQ_1236  Barclays  trd_0003   \n",
       "3  GB0031348658  SEDOL3          equity       EQ_1236  Barclays  trd_0004   \n",
       "4  GB0005405286  SEDOL5          equity       EQ_1238      HSBC  trd_0005   \n",
       "5  GB0008847096  SEDOL7          equity       EQ_1240     Tesco  trd_0006   \n",
       "\n",
       "  txn_type txn_trade_date txn_settle_date  txn_units  txn_price  \\\n",
       "0  StockIn     02/01/2020      04/01/2020     120000          5   \n",
       "1  StockIn     02/01/2020      04/01/2020      12000          5   \n",
       "2  StockIn     02/01/2020      04/01/2020     150000          2   \n",
       "3  StockIn     02/01/2020      04/01/2020     100000          2   \n",
       "4  StockIn     02/01/2020      04/01/2020      20000          6   \n",
       "5  StockIn     16/01/2020      18/01/2020      12000          9   \n",
       "\n",
       "   txn_consideration currency  strategy  cash_transactions  \n",
       "0             600000      GBP  balanced                NaN  \n",
       "1              60000      GBP  balanced                NaN  \n",
       "2             300000      GBP  balanced                NaN  \n",
       "3             300000      GBP   banking                NaN  \n",
       "4             120000      GBP   banking                NaN  \n",
       "5             108000      GBP  balanced                NaN  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv(\"data/equity_transactions_for_output.csv\")\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Load instruments\n",
    "\n",
    "Using the csv of setup transactions we can create instruments in LUSID for each of the equities referenced. When uploading the instruments we need to map certain properties that LUSID expects to columns provided in the csv.\n",
    "\n",
    "For example, we need to map the 'ticker' column to the 'Isin' identifier and then 'sedol' to 'Sedol'."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "instrument_mapping = {\n",
    "    \"identifier_mapping\": {\n",
    "        \"ClientInternal\": \"instrument_id\",\n",
    "        \"Isin\": \"ticker\",\n",
    "        \"Sedol\": \"sedol\",\n",
    "    },\n",
    "    \"required\": {\n",
    "        \"name\": \"name\"\n",
    "    },\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>success</th>\n",
       "      <th>failed</th>\n",
       "      <th>errors</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   success  failed  errors\n",
       "0        4       0       0"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result = load_from_data_frame(\n",
    "    api_factory=api_factory,\n",
    "    scope=scope,\n",
    "    data_frame=df,\n",
    "    mapping_required=instrument_mapping[\"required\"],\n",
    "    mapping_optional={},\n",
    "    file_type=\"instruments\",\n",
    "    identifier_mapping=instrument_mapping[\"identifier_mapping\"],\n",
    ")\n",
    "\n",
    "# Check the success rate of the upload\n",
    "succ, failed, errors = format_instruments_response(result)\n",
    "pd.DataFrame(data=[{\"success\": len(succ), \"failed\": len(failed), \"errors\": len(errors)}])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create LUSID properties\n",
    "\n",
    "Create the properties for the sub-holding keys to hold strategy information in our portfolio."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "properties_api = api_factory.build(lusid.api.PropertyDefinitionsApi)\n",
    "\n",
    "try:\n",
    "    properties_api.create_property_definition(\n",
    "        create_property_definition_request=lusid.models.CreatePropertyDefinitionRequest(\n",
    "            domain=\"Transaction\",\n",
    "            scope=scope,\n",
    "            code=strategy_shk,\n",
    "            display_name=strategy_shk,\n",
    "            life_time=\"Perpetual\",\n",
    "            value_required=False,\n",
    "            data_type_id=lusid.models.resource_id.ResourceId(scope=\"system\", code=\"string\")\n",
    "        )\n",
    "    )\n",
    "except ApiException as e:\n",
    "    detail = json.loads(e.body)\n",
    "    if detail[\"code\"] != 124: # 'PropertyAlreadyExists'\n",
    "        raise e"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create portfolio\n",
    "\n",
    "To create the portfolio we will use the transaction csv as we did when creating instruments, mapping LUSID's required properties to columns in the dataframe, also providing a name, base currency and (optionally) setting the date at which the portfolio should be effective from (created)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "portfolio_mapping = {\n",
    "    \"required\": {\n",
    "        \"code\": \"portfolio_code\",\n",
    "        \"display_name\": \"portfolio_name\",\n",
    "        \"base_currency\": \"$GBP\",\n",
    "    },\n",
    "    \"optional\": {\"created\": \"$2020-01-01T00:00:00+00:00\"},\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>success</th>\n",
       "      <th>failed</th>\n",
       "      <th>errors</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   success  failed  errors\n",
       "0        1       0       0"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result = load_from_data_frame(\n",
    "    api_factory=api_factory,\n",
    "    scope=scope,\n",
    "    data_frame=df,\n",
    "    mapping_required=portfolio_mapping[\"required\"],\n",
    "    mapping_optional=portfolio_mapping[\"optional\"],\n",
    "    file_type=\"portfolios\",\n",
    "    sub_holding_keys=[strategy_shk],\n",
    ")\n",
    "\n",
    "# Check the success rate of the upload\n",
    "succ, failed = format_portfolios_response(result)\n",
    "pd.DataFrame(data=[{\"success\": len(succ), \"failed\": len(failed), \"errors\": len(errors)}])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Load in transactions\n",
    "\n",
    "Now that we have a list of instruments and a portfolio setup in LUSID, we can complete the initialisation by uploading the transactions.\n",
    "\n",
    "The approach is similar to the instruments and portfolio."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "transaction_mapping = {\n",
    "    \"identifier_mapping\": {\n",
    "        \"ClientInternal\": \"instrument_id\",\n",
    "    },\n",
    "    \"required\": {\n",
    "        \"code\": \"portfolio_code\",\n",
    "        \"transaction_id\": \"txn_id\",\n",
    "        \"type\": \"txn_type\",\n",
    "        \"transaction_price.price\": \"txn_price\",\n",
    "        \"transaction_price.type\": \"$Price\",\n",
    "        \"total_consideration.amount\": \"txn_consideration\",\n",
    "        \"units\": \"txn_units\",\n",
    "        \"transaction_date\": \"txn_trade_date\",\n",
    "        \"total_consideration.currency\": \"portfolio_base_currency\",\n",
    "        \"settlement_date\": \"txn_settle_date\",        \n",
    "    },\n",
    "    \"optional\": {},\n",
    "    \"properties\": [\"strategy\"],\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>success</th>\n",
       "      <th>failed</th>\n",
       "      <th>errors</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   success  failed  errors\n",
       "0        1       0       0"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result = load_from_data_frame(\n",
    "    api_factory=api_factory,\n",
    "    scope=scope,\n",
    "    data_frame=df,\n",
    "    mapping_required=transaction_mapping[\"required\"],\n",
    "    mapping_optional=transaction_mapping[\"optional\"],\n",
    "    sub_holding_keys=[strategy_shk],\n",
    "    file_type=\"transactions\",\n",
    "    identifier_mapping=transaction_mapping[\"identifier_mapping\"],\n",
    "    property_columns=transaction_mapping[\"properties\"],\n",
    "    properties_scope=scope,\n",
    ")\n",
    "\n",
    "# Check the success rate of the upload\n",
    "succ, failed = format_transactions_response(result)\n",
    "pd.DataFrame(data=[{\"success\": len(succ), \"failed\": len(failed), \"errors\": len(failed)}])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Check holdings\n",
    "\n",
    "Now that we have initialised the portfolio, we can query LUSID to check our holdings.\n",
    "\n",
    "The first thing we need to do is set up and call the transaction portfolio api."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "txn_portfolio_api = api_factory.build(lusid.api.TransactionPortfoliosApi)\n",
    "\n",
    "response = txn_portfolio_api.get_holdings(\n",
    "    scope=scope,\n",
    "    code=portfolio_code,\n",
    "    property_keys=[\"Instrument/default/Name\"],\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "LUSID python tools helps us transform the response into a dataframe where we can see a summary of our portfolio holdings.\n",
    "\n",
    "We have:\n",
    "- 1 portfolio\n",
    "- 5 distinct holdings\n",
    "    - across 4 unique instruments\n",
    "    - assigned to 2 strategies \n",
    "    \n",
    "The dataframe shows just one record for our Aviva holdings despite uploading multiple transactions involving Aviva equity. LUSID aggregated these into one holding as the transactions were assigned the same strategy ('balanced').\n",
    "\n",
    "This is contrasted by the way LUSID handles the Barclays transactions. Our holdings show two records for Barclays, one for the 'balanced' strategy and another for the 'banking' one. \n",
    "\n",
    "The strategy property we see here is just one example of how LUSID can handle custom tags to transactions or holdings which makes it easy to view different slices of the portfolio depending on the user and their use case.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>LusidInstrumentId</th>\n",
       "      <th>strategy(38b8-6dfa-6617-5c-SubHoldingKeys)</th>\n",
       "      <th>InstrumentName</th>\n",
       "      <th>SourcePortfolioId</th>\n",
       "      <th>SourcePortfolioScope(default-Properties)</th>\n",
       "      <th>HoldingType</th>\n",
       "      <th>Units</th>\n",
       "      <th>SettledUnits</th>\n",
       "      <th>Amount-Cost</th>\n",
       "      <th>Currency-Cost</th>\n",
       "      <th>Amount-CostPortfolioCcy</th>\n",
       "      <th>Currenct-CostPortfolioCcy</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>LUID_KR3A1NMI</td>\n",
       "      <td>balanced</td>\n",
       "      <td>Aviva</td>\n",
       "      <td>EQUITY_UK</td>\n",
       "      <td>38b8-6dfa-6617-5c</td>\n",
       "      <td>P</td>\n",
       "      <td>132000.0</td>\n",
       "      <td>132000.0</td>\n",
       "      <td>660000.0</td>\n",
       "      <td>GBP</td>\n",
       "      <td>0.0</td>\n",
       "      <td>GBP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>LUID_SIMWQCNR</td>\n",
       "      <td>balanced</td>\n",
       "      <td>Barclays</td>\n",
       "      <td>EQUITY_UK</td>\n",
       "      <td>38b8-6dfa-6617-5c</td>\n",
       "      <td>P</td>\n",
       "      <td>150000.0</td>\n",
       "      <td>150000.0</td>\n",
       "      <td>300000.0</td>\n",
       "      <td>GBP</td>\n",
       "      <td>0.0</td>\n",
       "      <td>GBP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>LUID_SIMWQCNR</td>\n",
       "      <td>banking</td>\n",
       "      <td>Barclays</td>\n",
       "      <td>EQUITY_UK</td>\n",
       "      <td>38b8-6dfa-6617-5c</td>\n",
       "      <td>P</td>\n",
       "      <td>100000.0</td>\n",
       "      <td>100000.0</td>\n",
       "      <td>300000.0</td>\n",
       "      <td>GBP</td>\n",
       "      <td>0.0</td>\n",
       "      <td>GBP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>LUID_S1MNV9OQ</td>\n",
       "      <td>banking</td>\n",
       "      <td>HSBC</td>\n",
       "      <td>EQUITY_UK</td>\n",
       "      <td>38b8-6dfa-6617-5c</td>\n",
       "      <td>P</td>\n",
       "      <td>20000.0</td>\n",
       "      <td>20000.0</td>\n",
       "      <td>120000.0</td>\n",
       "      <td>GBP</td>\n",
       "      <td>0.0</td>\n",
       "      <td>GBP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>LUID_AU5UQIVK</td>\n",
       "      <td>balanced</td>\n",
       "      <td>Tesco</td>\n",
       "      <td>EQUITY_UK</td>\n",
       "      <td>38b8-6dfa-6617-5c</td>\n",
       "      <td>P</td>\n",
       "      <td>12000.0</td>\n",
       "      <td>12000.0</td>\n",
       "      <td>108000.0</td>\n",
       "      <td>GBP</td>\n",
       "      <td>0.0</td>\n",
       "      <td>GBP</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  LusidInstrumentId strategy(38b8-6dfa-6617-5c-SubHoldingKeys) InstrumentName  \\\n",
       "0     LUID_KR3A1NMI                                   balanced          Aviva   \n",
       "1     LUID_SIMWQCNR                                   balanced       Barclays   \n",
       "2     LUID_SIMWQCNR                                    banking       Barclays   \n",
       "3     LUID_S1MNV9OQ                                    banking           HSBC   \n",
       "4     LUID_AU5UQIVK                                   balanced          Tesco   \n",
       "\n",
       "  SourcePortfolioId SourcePortfolioScope(default-Properties) HoldingType  \\\n",
       "0         EQUITY_UK                        38b8-6dfa-6617-5c           P   \n",
       "1         EQUITY_UK                        38b8-6dfa-6617-5c           P   \n",
       "2         EQUITY_UK                        38b8-6dfa-6617-5c           P   \n",
       "3         EQUITY_UK                        38b8-6dfa-6617-5c           P   \n",
       "4         EQUITY_UK                        38b8-6dfa-6617-5c           P   \n",
       "\n",
       "      Units  SettledUnits  Amount-Cost Currency-Cost  Amount-CostPortfolioCcy  \\\n",
       "0  132000.0      132000.0     660000.0           GBP                      0.0   \n",
       "1  150000.0      150000.0     300000.0           GBP                      0.0   \n",
       "2  100000.0      100000.0     300000.0           GBP                      0.0   \n",
       "3   20000.0       20000.0     120000.0           GBP                      0.0   \n",
       "4   12000.0       12000.0     108000.0           GBP                      0.0   \n",
       "\n",
       "  Currenct-CostPortfolioCcy  \n",
       "0                       GBP  \n",
       "1                       GBP  \n",
       "2                       GBP  \n",
       "3                       GBP  \n",
       "4                       GBP  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "with open(r\"config/get_holdings_mapping.json\") as mappings_file:\n",
    "    get_holdings_json_mapping = json.load(mappings_file)\n",
    "\n",
    "holdings_df = lusid_response_to_data_frame(\n",
    "    response,\n",
    "    rename_properties=True,\n",
    "    column_name_mapping=get_holdings_json_mapping,\n",
    ")\n",
    "\n",
    "holdings_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Part 2: Adding Corporate Actions\n",
    "\n",
    "We now want to process a selection of corporate actions that relate to instruments that we hold in our portfolio. \n",
    "\n",
    "Instead of having to calculate the results of the corporate action outside LUSID, and apply them individually to each affected portfolio, LUSID allows use of a corporate actions source to automatically apply the corporate actions to holdings across all portfolios.\n",
    "\n",
    "We want to record the corporate actions in one place and have the option to apply them to every portfolio that holds the respective instruments. \n",
    "\n",
    "### Create a corporate actions source\n",
    "\n",
    "The corporate actions source is a container for holding corporate actions that we can use to apply to any portfolio. \n",
    "\n",
    "You may want to use a different corporate action source for each of your data vendors."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "ca_source_code = \"ca_vendor1\"\n",
    "\n",
    "try:\n",
    "    source_request = models.CreateCorporateActionSourceRequest(\n",
    "        scope=scope,\n",
    "        code=ca_source_code,\n",
    "        display_name=ca_source_code,\n",
    "        description=\"Corporate Actions source for Output Transactions notebook\",\n",
    "    )\n",
    "    \n",
    "    source_result = api_factory.build(\n",
    "        lusid.api.CorporateActionSourcesApi\n",
    "    ).create_corporate_action_source(create_corporate_action_source_request=source_request)\n",
    "    \n",
    "except:\n",
    "    pass"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To attach the corporate action source to the portfolio you can use the TransactionPortfolioApi to upsert the details."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Corporate actions source of ca_vendor1 assigned to portfolio EQUITY_UK\n"
     ]
    }
   ],
   "source": [
    "# Assign corporate actions source to our portfolio\n",
    "\n",
    "api_factory.build(lusid.api.TransactionPortfoliosApi).upsert_portfolio_details(\n",
    "    scope=scope,\n",
    "    code=portfolio_code,\n",
    "    effective_at=\"2020-01-01T00:00:00+00:00\",\n",
    "    create_portfolio_details=lusid.models.CreatePortfolioDetails(\n",
    "      corporate_action_source_id=lusid.ResourceId(\n",
    "          scope=scope,\n",
    "          code=ca_source_code,\n",
    "      ),  \n",
    "    ),\n",
    ")\n",
    "\n",
    "print(\n",
    "    f\"Corporate actions source of {ca_source_code} assigned to portfolio {portfolio_code}\"\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Load transitions from an external source\n",
    "\n",
    "Corporate Action Sources provide a list of Corporate Actions.  Corporate Actions contain a set of Transitions, that determine the instruments that are taking part in a corporate action, and what the effect of the corporate action is on holdings in that instrument.\n",
    "\n",
    "In this example we are going to add transitions from a csv file for the following corporate actions:\n",
    "\n",
    "* A bonus issue of 13,200 units from Aviva (1 bonus share per 10 held)\n",
    "* A cash dividend of £425,000 from Barclays (£1.70 GBP per share held)\n",
    "* A 2 for 1 stock split from Tesco (each 1 share held is split into 2 shares held)\n",
    "\n",
    "These transitions are posted with a payment date of 31 March 2020."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>code</th>\n",
       "      <th>action_description</th>\n",
       "      <th>description</th>\n",
       "      <th>announcement_date</th>\n",
       "      <th>ex_date</th>\n",
       "      <th>record_date</th>\n",
       "      <th>payment_date</th>\n",
       "      <th>client_id</th>\n",
       "      <th>input_instrument_name</th>\n",
       "      <th>input_units_factor</th>\n",
       "      <th>input_cost_factor</th>\n",
       "      <th>output_instrument_internal</th>\n",
       "      <th>output_units_factor</th>\n",
       "      <th>output_cost_factor</th>\n",
       "      <th>dividend_yield</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5943592342</td>\n",
       "      <td>dividend-cash</td>\n",
       "      <td>Barclays</td>\n",
       "      <td>2020-03-31T00:00:00Z</td>\n",
       "      <td>2020-03-31T00:00:00Z</td>\n",
       "      <td>2020-03-31T00:00:00Z</td>\n",
       "      <td>2020-03-31T00:00:00Z</td>\n",
       "      <td>EQ_1236</td>\n",
       "      <td>Barclays</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>CCY_GBP</td>\n",
       "      <td>1.7</td>\n",
       "      <td>1</td>\n",
       "      <td>1.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5943592343</td>\n",
       "      <td>bonus-issue</td>\n",
       "      <td>Aviva</td>\n",
       "      <td>2020-03-31T00:00:00Z</td>\n",
       "      <td>2020-03-31T00:00:00Z</td>\n",
       "      <td>2020-03-31T00:00:00Z</td>\n",
       "      <td>2020-03-31T00:00:00Z</td>\n",
       "      <td>EQ_1234</td>\n",
       "      <td>Aviva</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>EQ_1234</td>\n",
       "      <td>1.1</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5943592344</td>\n",
       "      <td>stock-split</td>\n",
       "      <td>Tesco</td>\n",
       "      <td>2020-03-31T00:00:00Z</td>\n",
       "      <td>2020-03-31T00:00:00Z</td>\n",
       "      <td>2020-03-31T00:00:00Z</td>\n",
       "      <td>2020-03-31T00:00:00Z</td>\n",
       "      <td>EQ_1240</td>\n",
       "      <td>Tesco</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>EQ_1240</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         code action_description description     announcement_date  \\\n",
       "0  5943592342      dividend-cash    Barclays  2020-03-31T00:00:00Z   \n",
       "1  5943592343        bonus-issue       Aviva  2020-03-31T00:00:00Z   \n",
       "2  5943592344        stock-split       Tesco  2020-03-31T00:00:00Z   \n",
       "\n",
       "                ex_date           record_date          payment_date client_id  \\\n",
       "0  2020-03-31T00:00:00Z  2020-03-31T00:00:00Z  2020-03-31T00:00:00Z   EQ_1236   \n",
       "1  2020-03-31T00:00:00Z  2020-03-31T00:00:00Z  2020-03-31T00:00:00Z   EQ_1234   \n",
       "2  2020-03-31T00:00:00Z  2020-03-31T00:00:00Z  2020-03-31T00:00:00Z   EQ_1240   \n",
       "\n",
       "  input_instrument_name  input_units_factor  input_cost_factor  \\\n",
       "0              Barclays                   1                  1   \n",
       "1                 Aviva                   1                  1   \n",
       "2                 Tesco                   1                  1   \n",
       "\n",
       "  output_instrument_internal  output_units_factor  output_cost_factor  \\\n",
       "0                    CCY_GBP                  1.7                   1   \n",
       "1                    EQ_1234                  1.1                   1   \n",
       "2                    EQ_1240                  2.0                   1   \n",
       "\n",
       "   dividend_yield  \n",
       "0             1.7  \n",
       "1             0.0  \n",
       "2             0.0  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "corporate_action_df = pd.read_csv(\"data/corp-acts/corpact_transitions_split.csv\")\n",
    "\n",
    "# Format the datetimes into ISO strings\n",
    "for col in [\"announcement_date\", \"ex_date\", \"payment_date\", \"record_date\"]:\n",
    "    corporate_action_df[col] = corporate_action_df[col].apply(\n",
    "        lambda x: datetime.strptime(x, \"%d/%m/%Y\").strftime(format=\"%Y-%m-%dT00:00:00Z\")\n",
    "    )\n",
    "\n",
    "corporate_action_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For more information on corporate actions and transitions please see https://support.finbourne.com/how-are-corporate-actions-represented."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create the LUSID requests from the source data\n",
    "\n",
    "We want to take the transitions from the dataframe and break each up into their component input and output objects in preparation for uploading into LUSID."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "# This code produces a dictionary of transitions where:\n",
    "# The dict key is the corporate action code and the dict value is a list\n",
    "# of input and output CorporateActionTransitionComponentRequest objects\n",
    "\n",
    "transitions = {}\n",
    "CLIENT_INSTRUMENT_IDENTIFIER = \"Instrument/default/ClientInternal\"\n",
    "\n",
    "for index, ca in corporate_action_df.iterrows():\n",
    "    \n",
    "    # Create transition components\n",
    "    cat_in = models.CorporateActionTransitionComponentRequest(\n",
    "        instrument_identifiers={\n",
    "            CLIENT_INSTRUMENT_IDENTIFIER: ca[\"client_id\"]\n",
    "        },\n",
    "        units_factor=ca[\"input_units_factor\"],\n",
    "        cost_factor=ca[\"input_cost_factor\"],\n",
    "    )\n",
    "    \n",
    "    # Determine if the output is Cash or Stock, and create the \n",
    "    # appropriate transition component\n",
    "    if \"CCY\" in str(ca[\"output_instrument_internal\"]):\n",
    "        cat_out = models.CorporateActionTransitionComponentRequest(\n",
    "            instrument_identifiers={\n",
    "                \"Instrument/default/Currency\": ca[\"output_instrument_internal\"][4:]\n",
    "            },\n",
    "            units_factor=ca[\"output_units_factor\"],\n",
    "            cost_factor=ca[\"output_cost_factor\"],\n",
    "        )\n",
    "    else:\n",
    "        cat_out = models.CorporateActionTransitionComponentRequest(\n",
    "            instrument_identifiers={\n",
    "                CLIENT_INSTRUMENT_IDENTIFIER: ca[\"output_instrument_internal\"]\n",
    "            },\n",
    "            units_factor=ca[\"output_units_factor\"],\n",
    "            cost_factor=ca[\"output_cost_factor\"],\n",
    "        )\n",
    "        \n",
    "    key = ca[\"code\"]\n",
    "    transitions.setdefault(key, [])\n",
    "    transitions[key].append(ca[\"action_description\"])\n",
    "    transitions[key].append(cat_in)\n",
    "    transitions[key].append(cat_out)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Using the broken-down transitions we want to create an [UpsertCorporateActionRequest](https://www.lusid.com/docs/api/#operation/BatchUpsertCorporateActions) for each corporate action."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Iterate through the transitions, turning them into LUSID Corporate Action Requests\n",
    "\n",
    "for key, values in transitions.items():\n",
    "    transition_code = key\n",
    "    transition_type = values[0]\n",
    "    transition_in = values[1]\n",
    "    transitions_out = values[2:]\n",
    "    \n",
    "    for x in transitions_out:\n",
    "        if isinstance(x, str):\n",
    "            transitions_out.remove(x)\n",
    "            \n",
    "    temp_transition = models.CorporateActionTransition(\n",
    "        input_transition=transition_in, output_transitions=transitions_out\n",
    "    )\n",
    "    \n",
    "    # Extract the data for the corporate action from the LUID corporate actions dataframe\n",
    "    data = corporate_action_df[corporate_action_df[\"code\"] == transition_code]\n",
    "    \n",
    "    def upsert_car():\n",
    "        upsert_car = models.UpsertCorporateActionRequest(\n",
    "                    corporate_action_code=str(item[\"code\"]),\n",
    "                    announcement_date=item[\"announcement_date\"],\n",
    "                    ex_date=item[\"ex_date\"],\n",
    "                    record_date=item[\"record_date\"],\n",
    "                    payment_date=item[\"payment_date\"],\n",
    "                    transitions=[temp_transition])\n",
    "        return upsert_car\n",
    "    \n",
    "    # Iterate through each row of data and create the Corporate Action\n",
    "    # Request for the appropriate type of action.\n",
    "    for row, item in data.iterrows():\n",
    "        if transition_type == \"dividend-cash\":\n",
    "            div_ca = upsert_car()\n",
    "        if transition_type == \"bonus-issue\":\n",
    "            bonus_ca = upsert_car()\n",
    "        if transition_type == \"stock-split\":\n",
    "            split_ca = upsert_car()\n",
    "        \n",
    "            "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Upsert the corporate actions into LUSID's movements engine\n",
    "\n",
    "We now have 3 requests ready to upload to LUSID:\n",
    "* div_ca\n",
    "* bonus_ca\n",
    "* split_ca\n",
    "\n",
    "These are fed into the CorporateActionSourcesApi as a batch upsert of corporate actions."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "result = api_factory.build(\n",
    "    lusid.api.CorporateActionSourcesApi\n",
    ").batch_upsert_corporate_actions(\n",
    "    scope=scope, \n",
    "    code=ca_source_code, \n",
    "    upsert_corporate_action_request=[div_ca, bonus_ca, split_ca],\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### View the updated holdings\n",
    "\n",
    "\n",
    "The portfolio should now have:\n",
    "* 145,200 units of Aviva as we received 1 bonus share for every 10 we held, resulting in an increase of 13,200 units\n",
    "* £425,000 GBP cash as Barlcays paid out a dividend of £1.70 GBP for each of the 250,000 units in our portfolio\n",
    "* 24,000 units of Tesco as each of our 12,000 units were split in two"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>LusidInstrumentId</th>\n",
       "      <th>strategy(38b8-6dfa-6617-5c-SubHoldingKeys)</th>\n",
       "      <th>InstrumentName</th>\n",
       "      <th>SourcePortfolioId</th>\n",
       "      <th>SourcePortfolioScope(default-Properties)</th>\n",
       "      <th>HoldingType</th>\n",
       "      <th>Units</th>\n",
       "      <th>SettledUnits</th>\n",
       "      <th>Amount-Cost</th>\n",
       "      <th>Currency-Cost</th>\n",
       "      <th>Amount-CostPortfolioCcy</th>\n",
       "      <th>Currenct-CostPortfolioCcy</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>LUID_KR3A1NMI</td>\n",
       "      <td>balanced</td>\n",
       "      <td>Aviva</td>\n",
       "      <td>EQUITY_UK</td>\n",
       "      <td>38b8-6dfa-6617-5c</td>\n",
       "      <td>P</td>\n",
       "      <td>145200.0</td>\n",
       "      <td>145200.0</td>\n",
       "      <td>660000.0</td>\n",
       "      <td>GBP</td>\n",
       "      <td>0.0</td>\n",
       "      <td>GBP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>LUID_SIMWQCNR</td>\n",
       "      <td>balanced</td>\n",
       "      <td>Barclays</td>\n",
       "      <td>EQUITY_UK</td>\n",
       "      <td>38b8-6dfa-6617-5c</td>\n",
       "      <td>P</td>\n",
       "      <td>150000.0</td>\n",
       "      <td>150000.0</td>\n",
       "      <td>300000.0</td>\n",
       "      <td>GBP</td>\n",
       "      <td>0.0</td>\n",
       "      <td>GBP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>LUID_SIMWQCNR</td>\n",
       "      <td>banking</td>\n",
       "      <td>Barclays</td>\n",
       "      <td>EQUITY_UK</td>\n",
       "      <td>38b8-6dfa-6617-5c</td>\n",
       "      <td>P</td>\n",
       "      <td>100000.0</td>\n",
       "      <td>100000.0</td>\n",
       "      <td>300000.0</td>\n",
       "      <td>GBP</td>\n",
       "      <td>0.0</td>\n",
       "      <td>GBP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>LUID_S1MNV9OQ</td>\n",
       "      <td>banking</td>\n",
       "      <td>HSBC</td>\n",
       "      <td>EQUITY_UK</td>\n",
       "      <td>38b8-6dfa-6617-5c</td>\n",
       "      <td>P</td>\n",
       "      <td>20000.0</td>\n",
       "      <td>20000.0</td>\n",
       "      <td>120000.0</td>\n",
       "      <td>GBP</td>\n",
       "      <td>0.0</td>\n",
       "      <td>GBP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>LUID_AU5UQIVK</td>\n",
       "      <td>balanced</td>\n",
       "      <td>Tesco</td>\n",
       "      <td>EQUITY_UK</td>\n",
       "      <td>38b8-6dfa-6617-5c</td>\n",
       "      <td>P</td>\n",
       "      <td>24000.0</td>\n",
       "      <td>24000.0</td>\n",
       "      <td>108000.0</td>\n",
       "      <td>GBP</td>\n",
       "      <td>0.0</td>\n",
       "      <td>GBP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>CCY_GBP</td>\n",
       "      <td>balanced</td>\n",
       "      <td>CCY_GBP</td>\n",
       "      <td>EQUITY_UK</td>\n",
       "      <td>38b8-6dfa-6617-5c</td>\n",
       "      <td>B</td>\n",
       "      <td>255000.0</td>\n",
       "      <td>255000.0</td>\n",
       "      <td>255000.0</td>\n",
       "      <td>GBP</td>\n",
       "      <td>0.0</td>\n",
       "      <td>GBP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>CCY_GBP</td>\n",
       "      <td>banking</td>\n",
       "      <td>CCY_GBP</td>\n",
       "      <td>EQUITY_UK</td>\n",
       "      <td>38b8-6dfa-6617-5c</td>\n",
       "      <td>B</td>\n",
       "      <td>170000.0</td>\n",
       "      <td>170000.0</td>\n",
       "      <td>170000.0</td>\n",
       "      <td>GBP</td>\n",
       "      <td>0.0</td>\n",
       "      <td>GBP</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  LusidInstrumentId strategy(38b8-6dfa-6617-5c-SubHoldingKeys) InstrumentName  \\\n",
       "0     LUID_KR3A1NMI                                   balanced          Aviva   \n",
       "1     LUID_SIMWQCNR                                   balanced       Barclays   \n",
       "2     LUID_SIMWQCNR                                    banking       Barclays   \n",
       "3     LUID_S1MNV9OQ                                    banking           HSBC   \n",
       "4     LUID_AU5UQIVK                                   balanced          Tesco   \n",
       "5           CCY_GBP                                   balanced        CCY_GBP   \n",
       "6           CCY_GBP                                    banking        CCY_GBP   \n",
       "\n",
       "  SourcePortfolioId SourcePortfolioScope(default-Properties) HoldingType  \\\n",
       "0         EQUITY_UK                        38b8-6dfa-6617-5c           P   \n",
       "1         EQUITY_UK                        38b8-6dfa-6617-5c           P   \n",
       "2         EQUITY_UK                        38b8-6dfa-6617-5c           P   \n",
       "3         EQUITY_UK                        38b8-6dfa-6617-5c           P   \n",
       "4         EQUITY_UK                        38b8-6dfa-6617-5c           P   \n",
       "5         EQUITY_UK                        38b8-6dfa-6617-5c           B   \n",
       "6         EQUITY_UK                        38b8-6dfa-6617-5c           B   \n",
       "\n",
       "      Units  SettledUnits  Amount-Cost Currency-Cost  Amount-CostPortfolioCcy  \\\n",
       "0  145200.0      145200.0     660000.0           GBP                      0.0   \n",
       "1  150000.0      150000.0     300000.0           GBP                      0.0   \n",
       "2  100000.0      100000.0     300000.0           GBP                      0.0   \n",
       "3   20000.0       20000.0     120000.0           GBP                      0.0   \n",
       "4   24000.0       24000.0     108000.0           GBP                      0.0   \n",
       "5  255000.0      255000.0     255000.0           GBP                      0.0   \n",
       "6  170000.0      170000.0     170000.0           GBP                      0.0   \n",
       "\n",
       "  Currenct-CostPortfolioCcy  \n",
       "0                       GBP  \n",
       "1                       GBP  \n",
       "2                       GBP  \n",
       "3                       GBP  \n",
       "4                       GBP  \n",
       "5                       GBP  \n",
       "6                       GBP  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "response = txn_portfolio_api.get_holdings(\n",
    "    scope=scope,\n",
    "    code=portfolio_code,\n",
    "    property_keys=[\"Instrument/default/Name\"],\n",
    ")\n",
    "\n",
    "holdings_df = lusid_response_to_data_frame(\n",
    "    response,\n",
    "    rename_properties=True,\n",
    "    column_name_mapping=get_holdings_json_mapping,\n",
    ")\n",
    "\n",
    "holdings_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You'll notice that the cash dividend is not being shown as one amount. As our Barclays equities are held across two strategies, the cash dividend is also assigned to the two strategies in the same ratio."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Part 3: Manual holding adjustment\n",
    "\n",
    "You can make manual adjustments to holdings in LUSID. For example, you may wish to do this if you know that there is a correction required between your source data and the data in LUSID e.g. transaction types not yet configured.\n",
    "\n",
    "In time you would look to properly account for any mismatch.\n",
    "\n",
    "### Adjust holdings to add an initial cash balance\n",
    "\n",
    "Seeing the cash increase in the portfolio has highlighted the fact that there was no initial cash position on record. \n",
    "\n",
    "We can manually set the holdings of our portfolio to add in a starting cash balance of £500,000 GBP in our balanced strategy. We will make this effective from the same time the portfolio was created."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "# Set the date from which the cash balance will apply to be the start of the portfolio\n",
    "response = txn_portfolio_api.get_details(\n",
    "    scope=scope,\n",
    "    code=portfolio_code,\n",
    ")\n",
    "\n",
    "holdings_effective_date = response.version.effective_from\n",
    "\n",
    "# Define the actual initial cash balance\n",
    "initial_cash_balance = 500000\n",
    "\n",
    "# Create a holding adjustment to set our initial cash balance\n",
    "holding_adjustment = [\n",
    "    models.AdjustHoldingRequest( \n",
    "        instrument_identifiers={\n",
    "            \"Instrument/default/Currency\": \"GBP\",\n",
    "        },\n",
    "        tax_lots=[\n",
    "            models.TargetTaxLotRequest(\n",
    "                units=initial_cash_balance,\n",
    "            )\n",
    "        ],\n",
    "        sub_holding_keys={\n",
    "            f\"Transaction/{scope}/{strategy_shk}\" : models.PerpetualProperty(\n",
    "                key=f\"Transaction/{scope}/{strategy_shk}\",\n",
    "                value=models.PropertyValue(label_value=\"balanced\")),\n",
    "            },\n",
    "    )\n",
    "] "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Once we have the adjustment set up, we can use the TransactionPortfoliosApi to update the portfolio in LUSID."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "set_holdings_response = api_factory.build(lusid.api.TransactionPortfoliosApi).set_holdings(\n",
    "    scope=scope,\n",
    "    code=portfolio_code,\n",
    "    effective_at=holdings_effective_date,\n",
    "    adjust_holding_request=holding_adjustment,\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Check holdings post-adjustment\n",
    "\n",
    "The portfolio should now have £925,000 GBP cash as we manually increased the cash position from £425,000 by £500,000\n",
    "\n",
    "As we added the £500,000 to the 'balanced' strategy the cash balance for 'banking' has remained unchanged at £170,000."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>LusidInstrumentId</th>\n",
       "      <th>strategy(38b8-6dfa-6617-5c-SubHoldingKeys)</th>\n",
       "      <th>InstrumentName</th>\n",
       "      <th>SourcePortfolioId</th>\n",
       "      <th>SourcePortfolioScope(default-Properties)</th>\n",
       "      <th>HoldingType</th>\n",
       "      <th>Units</th>\n",
       "      <th>SettledUnits</th>\n",
       "      <th>Amount-Cost</th>\n",
       "      <th>Currency-Cost</th>\n",
       "      <th>Amount-CostPortfolioCcy</th>\n",
       "      <th>Currenct-CostPortfolioCcy</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>LUID_KR3A1NMI</td>\n",
       "      <td>balanced</td>\n",
       "      <td>Aviva</td>\n",
       "      <td>EQUITY_UK</td>\n",
       "      <td>38b8-6dfa-6617-5c</td>\n",
       "      <td>P</td>\n",
       "      <td>145200.0</td>\n",
       "      <td>145200.0</td>\n",
       "      <td>660000.0</td>\n",
       "      <td>GBP</td>\n",
       "      <td>0.0</td>\n",
       "      <td>GBP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>LUID_SIMWQCNR</td>\n",
       "      <td>balanced</td>\n",
       "      <td>Barclays</td>\n",
       "      <td>EQUITY_UK</td>\n",
       "      <td>38b8-6dfa-6617-5c</td>\n",
       "      <td>P</td>\n",
       "      <td>150000.0</td>\n",
       "      <td>150000.0</td>\n",
       "      <td>300000.0</td>\n",
       "      <td>GBP</td>\n",
       "      <td>0.0</td>\n",
       "      <td>GBP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>LUID_SIMWQCNR</td>\n",
       "      <td>banking</td>\n",
       "      <td>Barclays</td>\n",
       "      <td>EQUITY_UK</td>\n",
       "      <td>38b8-6dfa-6617-5c</td>\n",
       "      <td>P</td>\n",
       "      <td>100000.0</td>\n",
       "      <td>100000.0</td>\n",
       "      <td>300000.0</td>\n",
       "      <td>GBP</td>\n",
       "      <td>0.0</td>\n",
       "      <td>GBP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>LUID_S1MNV9OQ</td>\n",
       "      <td>banking</td>\n",
       "      <td>HSBC</td>\n",
       "      <td>EQUITY_UK</td>\n",
       "      <td>38b8-6dfa-6617-5c</td>\n",
       "      <td>P</td>\n",
       "      <td>20000.0</td>\n",
       "      <td>20000.0</td>\n",
       "      <td>120000.0</td>\n",
       "      <td>GBP</td>\n",
       "      <td>0.0</td>\n",
       "      <td>GBP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>LUID_AU5UQIVK</td>\n",
       "      <td>balanced</td>\n",
       "      <td>Tesco</td>\n",
       "      <td>EQUITY_UK</td>\n",
       "      <td>38b8-6dfa-6617-5c</td>\n",
       "      <td>P</td>\n",
       "      <td>24000.0</td>\n",
       "      <td>24000.0</td>\n",
       "      <td>108000.0</td>\n",
       "      <td>GBP</td>\n",
       "      <td>0.0</td>\n",
       "      <td>GBP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>CCY_GBP</td>\n",
       "      <td>balanced</td>\n",
       "      <td>CCY_GBP</td>\n",
       "      <td>EQUITY_UK</td>\n",
       "      <td>38b8-6dfa-6617-5c</td>\n",
       "      <td>B</td>\n",
       "      <td>755000.0</td>\n",
       "      <td>755000.0</td>\n",
       "      <td>255000.0</td>\n",
       "      <td>GBP</td>\n",
       "      <td>0.0</td>\n",
       "      <td>GBP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>CCY_GBP</td>\n",
       "      <td>banking</td>\n",
       "      <td>CCY_GBP</td>\n",
       "      <td>EQUITY_UK</td>\n",
       "      <td>38b8-6dfa-6617-5c</td>\n",
       "      <td>B</td>\n",
       "      <td>170000.0</td>\n",
       "      <td>170000.0</td>\n",
       "      <td>170000.0</td>\n",
       "      <td>GBP</td>\n",
       "      <td>0.0</td>\n",
       "      <td>GBP</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  LusidInstrumentId strategy(38b8-6dfa-6617-5c-SubHoldingKeys) InstrumentName  \\\n",
       "0     LUID_KR3A1NMI                                   balanced          Aviva   \n",
       "1     LUID_SIMWQCNR                                   balanced       Barclays   \n",
       "2     LUID_SIMWQCNR                                    banking       Barclays   \n",
       "3     LUID_S1MNV9OQ                                    banking           HSBC   \n",
       "4     LUID_AU5UQIVK                                   balanced          Tesco   \n",
       "5           CCY_GBP                                   balanced        CCY_GBP   \n",
       "6           CCY_GBP                                    banking        CCY_GBP   \n",
       "\n",
       "  SourcePortfolioId SourcePortfolioScope(default-Properties) HoldingType  \\\n",
       "0         EQUITY_UK                        38b8-6dfa-6617-5c           P   \n",
       "1         EQUITY_UK                        38b8-6dfa-6617-5c           P   \n",
       "2         EQUITY_UK                        38b8-6dfa-6617-5c           P   \n",
       "3         EQUITY_UK                        38b8-6dfa-6617-5c           P   \n",
       "4         EQUITY_UK                        38b8-6dfa-6617-5c           P   \n",
       "5         EQUITY_UK                        38b8-6dfa-6617-5c           B   \n",
       "6         EQUITY_UK                        38b8-6dfa-6617-5c           B   \n",
       "\n",
       "      Units  SettledUnits  Amount-Cost Currency-Cost  Amount-CostPortfolioCcy  \\\n",
       "0  145200.0      145200.0     660000.0           GBP                      0.0   \n",
       "1  150000.0      150000.0     300000.0           GBP                      0.0   \n",
       "2  100000.0      100000.0     300000.0           GBP                      0.0   \n",
       "3   20000.0       20000.0     120000.0           GBP                      0.0   \n",
       "4   24000.0       24000.0     108000.0           GBP                      0.0   \n",
       "5  755000.0      755000.0     255000.0           GBP                      0.0   \n",
       "6  170000.0      170000.0     170000.0           GBP                      0.0   \n",
       "\n",
       "  Currenct-CostPortfolioCcy  \n",
       "0                       GBP  \n",
       "1                       GBP  \n",
       "2                       GBP  \n",
       "3                       GBP  \n",
       "4                       GBP  \n",
       "5                       GBP  \n",
       "6                       GBP  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "response = txn_portfolio_api.get_holdings(\n",
    "    scope=scope,\n",
    "    code=portfolio_code,\n",
    "    property_keys=[\"Instrument/default/Name\"],\n",
    ")\n",
    "\n",
    "holdings_df = lusid_response_to_data_frame(\n",
    "    response,\n",
    "    rename_properties=True,\n",
    "    column_name_mapping=get_holdings_json_mapping,\n",
    ")\n",
    "\n",
    "holdings_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Part 4: What transactions make up our holdings?\n",
    "\n",
    "Now we have the portfolio holdings looking correct thanks to a combination of initial transactions, corporate actions and a manual holding adjustment. \n",
    "\n",
    "LUSID calculates holdings using the Movements Engine and a set of input and output transactions, so what has it done to include the corporate actions and the adjustment?\n",
    "\n",
    "Lets extract the list of transactions from LUSID to examine them a little closer.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>TransactionId</th>\n",
       "      <th>TransactionType</th>\n",
       "      <th>TransactionTypeDesc</th>\n",
       "      <th>ClientId</th>\n",
       "      <th>LusidInstrumentId</th>\n",
       "      <th>TransactionDate</th>\n",
       "      <th>SettlementDate</th>\n",
       "      <th>Units</th>\n",
       "      <th>TransactionAmount</th>\n",
       "      <th>Price</th>\n",
       "      <th>PriceType</th>\n",
       "      <th>TotalConsideration</th>\n",
       "      <th>TotalConsiderationCurrency</th>\n",
       "      <th>ExchangeRate</th>\n",
       "      <th>TransactionToPortfolioRate</th>\n",
       "      <th>TransactionCurrency</th>\n",
       "      <th>strategy(38b8-6dfa-6617-5c-Properties)</th>\n",
       "      <th>SourcePortfolioId(default-Properties)</th>\n",
       "      <th>SourcePortfolioScope(default-Properties)</th>\n",
       "      <th>ResultantHolding</th>\n",
       "      <th>InstrumentName</th>\n",
       "      <th>source</th>\n",
       "      <th>TransactionStatus</th>\n",
       "      <th>EntryDateTime</th>\n",
       "      <th>RealisedGainLoss</th>\n",
       "      <th>instrument_identifiers.Instrument/default/LusidInstrumentId</th>\n",
       "      <th>OriginalTradeDate(default-Properties)</th>\n",
       "      <th>OriginalSettlementDate(default-Properties)</th>\n",
       "      <th>InstrumentCurrency</th>\n",
       "      <th>TradeToPortfolioRate(default-Properties)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>trd_0001</td>\n",
       "      <td>StockIn</td>\n",
       "      <td>Transfer In</td>\n",
       "      <td>EQ_1234</td>\n",
       "      <td>LUID_KR3A1NMI</td>\n",
       "      <td>2020-01-02 00:00:00+00:00</td>\n",
       "      <td>2020-01-04 00:00:00+00:00</td>\n",
       "      <td>120000.0</td>\n",
       "      <td>600000.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>Price</td>\n",
       "      <td>600000.0</td>\n",
       "      <td>GBP</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>GBP</td>\n",
       "      <td>balanced</td>\n",
       "      <td>EQUITY_UK</td>\n",
       "      <td>38b8-6dfa-6617-5c</td>\n",
       "      <td>120000.0</td>\n",
       "      <td>Aviva</td>\n",
       "      <td>None</td>\n",
       "      <td>Active</td>\n",
       "      <td>2020-08-04 10:48:44.254160+00:00</td>\n",
       "      <td>[]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>trd_0002</td>\n",
       "      <td>StockIn</td>\n",
       "      <td>Transfer In</td>\n",
       "      <td>EQ_1234</td>\n",
       "      <td>LUID_KR3A1NMI</td>\n",
       "      <td>2020-01-02 00:00:00+00:00</td>\n",
       "      <td>2020-01-04 00:00:00+00:00</td>\n",
       "      <td>12000.0</td>\n",
       "      <td>60000.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>Price</td>\n",
       "      <td>60000.0</td>\n",
       "      <td>GBP</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>GBP</td>\n",
       "      <td>balanced</td>\n",
       "      <td>EQUITY_UK</td>\n",
       "      <td>38b8-6dfa-6617-5c</td>\n",
       "      <td>132000.0</td>\n",
       "      <td>Aviva</td>\n",
       "      <td>None</td>\n",
       "      <td>Active</td>\n",
       "      <td>2020-08-04 10:48:44.254160+00:00</td>\n",
       "      <td>[]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>trd_0003</td>\n",
       "      <td>StockIn</td>\n",
       "      <td>Transfer In</td>\n",
       "      <td>EQ_1236</td>\n",
       "      <td>LUID_SIMWQCNR</td>\n",
       "      <td>2020-01-02 00:00:00+00:00</td>\n",
       "      <td>2020-01-04 00:00:00+00:00</td>\n",
       "      <td>150000.0</td>\n",
       "      <td>300000.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>Price</td>\n",
       "      <td>300000.0</td>\n",
       "      <td>GBP</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>GBP</td>\n",
       "      <td>balanced</td>\n",
       "      <td>EQUITY_UK</td>\n",
       "      <td>38b8-6dfa-6617-5c</td>\n",
       "      <td>150000.0</td>\n",
       "      <td>Barclays</td>\n",
       "      <td>None</td>\n",
       "      <td>Active</td>\n",
       "      <td>2020-08-04 10:48:44.254160+00:00</td>\n",
       "      <td>[]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>trd_0004</td>\n",
       "      <td>StockIn</td>\n",
       "      <td>Transfer In</td>\n",
       "      <td>EQ_1236</td>\n",
       "      <td>LUID_SIMWQCNR</td>\n",
       "      <td>2020-01-02 00:00:00+00:00</td>\n",
       "      <td>2020-01-04 00:00:00+00:00</td>\n",
       "      <td>100000.0</td>\n",
       "      <td>300000.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>Price</td>\n",
       "      <td>300000.0</td>\n",
       "      <td>GBP</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>GBP</td>\n",
       "      <td>banking</td>\n",
       "      <td>EQUITY_UK</td>\n",
       "      <td>38b8-6dfa-6617-5c</td>\n",
       "      <td>100000.0</td>\n",
       "      <td>Barclays</td>\n",
       "      <td>None</td>\n",
       "      <td>Active</td>\n",
       "      <td>2020-08-04 10:48:44.254160+00:00</td>\n",
       "      <td>[]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>trd_0005</td>\n",
       "      <td>StockIn</td>\n",
       "      <td>Transfer In</td>\n",
       "      <td>EQ_1238</td>\n",
       "      <td>LUID_S1MNV9OQ</td>\n",
       "      <td>2020-01-02 00:00:00+00:00</td>\n",
       "      <td>2020-01-04 00:00:00+00:00</td>\n",
       "      <td>20000.0</td>\n",
       "      <td>120000.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>Price</td>\n",
       "      <td>120000.0</td>\n",
       "      <td>GBP</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>GBP</td>\n",
       "      <td>banking</td>\n",
       "      <td>EQUITY_UK</td>\n",
       "      <td>38b8-6dfa-6617-5c</td>\n",
       "      <td>20000.0</td>\n",
       "      <td>HSBC</td>\n",
       "      <td>None</td>\n",
       "      <td>Active</td>\n",
       "      <td>2020-08-04 10:48:44.254160+00:00</td>\n",
       "      <td>[]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>trd_0006</td>\n",
       "      <td>StockIn</td>\n",
       "      <td>Transfer In</td>\n",
       "      <td>EQ_1240</td>\n",
       "      <td>LUID_AU5UQIVK</td>\n",
       "      <td>2020-01-16 00:00:00+00:00</td>\n",
       "      <td>2020-01-18 00:00:00+00:00</td>\n",
       "      <td>12000.0</td>\n",
       "      <td>108000.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>Price</td>\n",
       "      <td>108000.0</td>\n",
       "      <td>GBP</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>GBP</td>\n",
       "      <td>balanced</td>\n",
       "      <td>EQUITY_UK</td>\n",
       "      <td>38b8-6dfa-6617-5c</td>\n",
       "      <td>12000.0</td>\n",
       "      <td>Tesco</td>\n",
       "      <td>None</td>\n",
       "      <td>Active</td>\n",
       "      <td>2020-08-04 10:48:44.254160+00:00</td>\n",
       "      <td>[]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>5943592343</td>\n",
       "      <td>CorpActIncrease</td>\n",
       "      <td>Corporate Action Increase</td>\n",
       "      <td>NaN</td>\n",
       "      <td>LUID_KR3A1NMI</td>\n",
       "      <td>2020-03-31 00:00:00+00:00</td>\n",
       "      <td>2020-03-31 00:00:00+00:00</td>\n",
       "      <td>13200.0</td>\n",
       "      <td>66000.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>Price</td>\n",
       "      <td>0.0</td>\n",
       "      <td>GBP</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>GBP</td>\n",
       "      <td>balanced</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>145200.0</td>\n",
       "      <td>Aviva</td>\n",
       "      <td></td>\n",
       "      <td>Active</td>\n",
       "      <td>2020-08-04 10:48:49.961555+00:00</td>\n",
       "      <td>[]</td>\n",
       "      <td>LUID_KR3A1NMI</td>\n",
       "      <td>2020-01-02T00:00:00.0000000+00:00</td>\n",
       "      <td>2020-01-04T00:00:00.0000000+00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>5943592344</td>\n",
       "      <td>CorpActIncrease</td>\n",
       "      <td>Corporate Action Increase</td>\n",
       "      <td>NaN</td>\n",
       "      <td>LUID_AU5UQIVK</td>\n",
       "      <td>2020-03-31 00:00:00+00:00</td>\n",
       "      <td>2020-03-31 00:00:00+00:00</td>\n",
       "      <td>12000.0</td>\n",
       "      <td>108000.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>Price</td>\n",
       "      <td>0.0</td>\n",
       "      <td>GBP</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>GBP</td>\n",
       "      <td>balanced</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>24000.0</td>\n",
       "      <td>Tesco</td>\n",
       "      <td></td>\n",
       "      <td>Active</td>\n",
       "      <td>2020-08-04 10:48:49.961555+00:00</td>\n",
       "      <td>[]</td>\n",
       "      <td>LUID_AU5UQIVK</td>\n",
       "      <td>2020-01-16T00:00:00.0000000+00:00</td>\n",
       "      <td>2020-01-18T00:00:00.0000000+00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2020-01-01T00:00:00.0000000+00:00</td>\n",
       "      <td>AdjustmentIncrease</td>\n",
       "      <td>Increase Adjustment</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CCY_GBP</td>\n",
       "      <td>2020-01-01 00:00:00+00:00</td>\n",
       "      <td>2020-01-01 00:00:00+00:00</td>\n",
       "      <td>500000.0</td>\n",
       "      <td>500000.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Price</td>\n",
       "      <td>0.0</td>\n",
       "      <td>GBP</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>GBP</td>\n",
       "      <td>balanced</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>500000.0</td>\n",
       "      <td>CCY_GBP</td>\n",
       "      <td></td>\n",
       "      <td>Active</td>\n",
       "      <td>2020-08-04 10:48:51.449733+00:00</td>\n",
       "      <td>[]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CCY_GBP</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>5943592342</td>\n",
       "      <td>Dividend</td>\n",
       "      <td>Dividend Payment</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CCY_GBP</td>\n",
       "      <td>2020-03-31 00:00:00+00:00</td>\n",
       "      <td>2020-03-31 00:00:00+00:00</td>\n",
       "      <td>255000.0</td>\n",
       "      <td>255000.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Price</td>\n",
       "      <td>0.0</td>\n",
       "      <td>GBP</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>GBP</td>\n",
       "      <td>balanced</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CCY_GBP</td>\n",
       "      <td></td>\n",
       "      <td>Active</td>\n",
       "      <td>2020-08-04 10:48:49.961555+00:00</td>\n",
       "      <td>[]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CCY_GBP</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>5943592342</td>\n",
       "      <td>Dividend</td>\n",
       "      <td>Dividend Payment</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CCY_GBP</td>\n",
       "      <td>2020-03-31 00:00:00+00:00</td>\n",
       "      <td>2020-03-31 00:00:00+00:00</td>\n",
       "      <td>170000.0</td>\n",
       "      <td>170000.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Price</td>\n",
       "      <td>0.0</td>\n",
       "      <td>GBP</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>GBP</td>\n",
       "      <td>banking</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CCY_GBP</td>\n",
       "      <td></td>\n",
       "      <td>Active</td>\n",
       "      <td>2020-08-04 10:48:49.961555+00:00</td>\n",
       "      <td>[]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CCY_GBP</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        TransactionId     TransactionType  \\\n",
       "0                            trd_0001             StockIn   \n",
       "1                            trd_0002             StockIn   \n",
       "2                            trd_0003             StockIn   \n",
       "3                            trd_0004             StockIn   \n",
       "4                            trd_0005             StockIn   \n",
       "5                            trd_0006             StockIn   \n",
       "6                          5943592343     CorpActIncrease   \n",
       "7                          5943592344     CorpActIncrease   \n",
       "8   2020-01-01T00:00:00.0000000+00:00  AdjustmentIncrease   \n",
       "9                          5943592342            Dividend   \n",
       "10                         5943592342            Dividend   \n",
       "\n",
       "          TransactionTypeDesc ClientId LusidInstrumentId  \\\n",
       "0                 Transfer In  EQ_1234     LUID_KR3A1NMI   \n",
       "1                 Transfer In  EQ_1234     LUID_KR3A1NMI   \n",
       "2                 Transfer In  EQ_1236     LUID_SIMWQCNR   \n",
       "3                 Transfer In  EQ_1236     LUID_SIMWQCNR   \n",
       "4                 Transfer In  EQ_1238     LUID_S1MNV9OQ   \n",
       "5                 Transfer In  EQ_1240     LUID_AU5UQIVK   \n",
       "6   Corporate Action Increase      NaN     LUID_KR3A1NMI   \n",
       "7   Corporate Action Increase      NaN     LUID_AU5UQIVK   \n",
       "8         Increase Adjustment      NaN           CCY_GBP   \n",
       "9            Dividend Payment      NaN           CCY_GBP   \n",
       "10           Dividend Payment      NaN           CCY_GBP   \n",
       "\n",
       "             TransactionDate            SettlementDate     Units  \\\n",
       "0  2020-01-02 00:00:00+00:00 2020-01-04 00:00:00+00:00  120000.0   \n",
       "1  2020-01-02 00:00:00+00:00 2020-01-04 00:00:00+00:00   12000.0   \n",
       "2  2020-01-02 00:00:00+00:00 2020-01-04 00:00:00+00:00  150000.0   \n",
       "3  2020-01-02 00:00:00+00:00 2020-01-04 00:00:00+00:00  100000.0   \n",
       "4  2020-01-02 00:00:00+00:00 2020-01-04 00:00:00+00:00   20000.0   \n",
       "5  2020-01-16 00:00:00+00:00 2020-01-18 00:00:00+00:00   12000.0   \n",
       "6  2020-03-31 00:00:00+00:00 2020-03-31 00:00:00+00:00   13200.0   \n",
       "7  2020-03-31 00:00:00+00:00 2020-03-31 00:00:00+00:00   12000.0   \n",
       "8  2020-01-01 00:00:00+00:00 2020-01-01 00:00:00+00:00  500000.0   \n",
       "9  2020-03-31 00:00:00+00:00 2020-03-31 00:00:00+00:00  255000.0   \n",
       "10 2020-03-31 00:00:00+00:00 2020-03-31 00:00:00+00:00  170000.0   \n",
       "\n",
       "    TransactionAmount  Price PriceType  TotalConsideration  \\\n",
       "0            600000.0    5.0     Price            600000.0   \n",
       "1             60000.0    5.0     Price             60000.0   \n",
       "2            300000.0    2.0     Price            300000.0   \n",
       "3            300000.0    2.0     Price            300000.0   \n",
       "4            120000.0    6.0     Price            120000.0   \n",
       "5            108000.0    9.0     Price            108000.0   \n",
       "6             66000.0    5.0     Price                 0.0   \n",
       "7            108000.0    9.0     Price                 0.0   \n",
       "8            500000.0    0.0     Price                 0.0   \n",
       "9            255000.0    1.0     Price                 0.0   \n",
       "10           170000.0    1.0     Price                 0.0   \n",
       "\n",
       "   TotalConsiderationCurrency  ExchangeRate  TransactionToPortfolioRate  \\\n",
       "0                         GBP           1.0                         0.0   \n",
       "1                         GBP           1.0                         0.0   \n",
       "2                         GBP           1.0                         0.0   \n",
       "3                         GBP           1.0                         0.0   \n",
       "4                         GBP           1.0                         0.0   \n",
       "5                         GBP           1.0                         0.0   \n",
       "6                         GBP           1.0                         0.0   \n",
       "7                         GBP           1.0                         0.0   \n",
       "8                         GBP           1.0                         1.0   \n",
       "9                         GBP           1.0                         0.0   \n",
       "10                        GBP           1.0                         0.0   \n",
       "\n",
       "   TransactionCurrency strategy(38b8-6dfa-6617-5c-Properties)  \\\n",
       "0                  GBP                               balanced   \n",
       "1                  GBP                               balanced   \n",
       "2                  GBP                               balanced   \n",
       "3                  GBP                                banking   \n",
       "4                  GBP                                banking   \n",
       "5                  GBP                               balanced   \n",
       "6                  GBP                               balanced   \n",
       "7                  GBP                               balanced   \n",
       "8                  GBP                               balanced   \n",
       "9                  GBP                               balanced   \n",
       "10                 GBP                                banking   \n",
       "\n",
       "   SourcePortfolioId(default-Properties)  \\\n",
       "0                              EQUITY_UK   \n",
       "1                              EQUITY_UK   \n",
       "2                              EQUITY_UK   \n",
       "3                              EQUITY_UK   \n",
       "4                              EQUITY_UK   \n",
       "5                              EQUITY_UK   \n",
       "6                                    NaN   \n",
       "7                                    NaN   \n",
       "8                                    NaN   \n",
       "9                                    NaN   \n",
       "10                                   NaN   \n",
       "\n",
       "   SourcePortfolioScope(default-Properties)  ResultantHolding InstrumentName  \\\n",
       "0                         38b8-6dfa-6617-5c          120000.0          Aviva   \n",
       "1                         38b8-6dfa-6617-5c          132000.0          Aviva   \n",
       "2                         38b8-6dfa-6617-5c          150000.0       Barclays   \n",
       "3                         38b8-6dfa-6617-5c          100000.0       Barclays   \n",
       "4                         38b8-6dfa-6617-5c           20000.0           HSBC   \n",
       "5                         38b8-6dfa-6617-5c           12000.0          Tesco   \n",
       "6                                       NaN          145200.0          Aviva   \n",
       "7                                       NaN           24000.0          Tesco   \n",
       "8                                       NaN          500000.0        CCY_GBP   \n",
       "9                                       NaN               NaN        CCY_GBP   \n",
       "10                                      NaN               NaN        CCY_GBP   \n",
       "\n",
       "   source TransactionStatus                    EntryDateTime RealisedGainLoss  \\\n",
       "0    None            Active 2020-08-04 10:48:44.254160+00:00               []   \n",
       "1    None            Active 2020-08-04 10:48:44.254160+00:00               []   \n",
       "2    None            Active 2020-08-04 10:48:44.254160+00:00               []   \n",
       "3    None            Active 2020-08-04 10:48:44.254160+00:00               []   \n",
       "4    None            Active 2020-08-04 10:48:44.254160+00:00               []   \n",
       "5    None            Active 2020-08-04 10:48:44.254160+00:00               []   \n",
       "6                    Active 2020-08-04 10:48:49.961555+00:00               []   \n",
       "7                    Active 2020-08-04 10:48:49.961555+00:00               []   \n",
       "8                    Active 2020-08-04 10:48:51.449733+00:00               []   \n",
       "9                    Active 2020-08-04 10:48:49.961555+00:00               []   \n",
       "10                   Active 2020-08-04 10:48:49.961555+00:00               []   \n",
       "\n",
       "   instrument_identifiers.Instrument/default/LusidInstrumentId  \\\n",
       "0                                                 NaN            \n",
       "1                                                 NaN            \n",
       "2                                                 NaN            \n",
       "3                                                 NaN            \n",
       "4                                                 NaN            \n",
       "5                                                 NaN            \n",
       "6                                       LUID_KR3A1NMI            \n",
       "7                                       LUID_AU5UQIVK            \n",
       "8                                                 NaN            \n",
       "9                                                 NaN            \n",
       "10                                                NaN            \n",
       "\n",
       "   OriginalTradeDate(default-Properties)  \\\n",
       "0                                    NaN   \n",
       "1                                    NaN   \n",
       "2                                    NaN   \n",
       "3                                    NaN   \n",
       "4                                    NaN   \n",
       "5                                    NaN   \n",
       "6      2020-01-02T00:00:00.0000000+00:00   \n",
       "7      2020-01-16T00:00:00.0000000+00:00   \n",
       "8                                    NaN   \n",
       "9                                    NaN   \n",
       "10                                   NaN   \n",
       "\n",
       "   OriginalSettlementDate(default-Properties) InstrumentCurrency  \\\n",
       "0                                         NaN                NaN   \n",
       "1                                         NaN                NaN   \n",
       "2                                         NaN                NaN   \n",
       "3                                         NaN                NaN   \n",
       "4                                         NaN                NaN   \n",
       "5                                         NaN                NaN   \n",
       "6           2020-01-04T00:00:00.0000000+00:00                NaN   \n",
       "7           2020-01-18T00:00:00.0000000+00:00                NaN   \n",
       "8                                         NaN            CCY_GBP   \n",
       "9                                         NaN            CCY_GBP   \n",
       "10                                        NaN            CCY_GBP   \n",
       "\n",
       "    TradeToPortfolioRate(default-Properties)  \n",
       "0                                        NaN  \n",
       "1                                        NaN  \n",
       "2                                        NaN  \n",
       "3                                        NaN  \n",
       "4                                        NaN  \n",
       "5                                        NaN  \n",
       "6                                        NaN  \n",
       "7                                        NaN  \n",
       "8                                        1.0  \n",
       "9                                        NaN  \n",
       "10                                       NaN  "
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Load a mapping file for DataFrame headers for the build transaction response\n",
    "with open(r\"config/build_transactions_mapping.json\") as mappings_file:\n",
    "    build_transactions_json_mapping = json.load(mappings_file)\n",
    "\n",
    "# Query LUSID for the transactions that form our portfolio\n",
    "build_transactions_response = txn_portfolio_api.build_transactions(\n",
    "    scope=scope,\n",
    "    code=portfolio_code,\n",
    "    transaction_query_parameters=models.TransactionQueryParameters(\n",
    "        start_date=\"2020-01-01\", end_date=\"2020-12-31\"\n",
    "    ),\n",
    "    property_keys=[\"Instrument/default/Name\"],\n",
    ")\n",
    "\n",
    "build_transactions_df = lusid_response_to_data_frame(\n",
    "    build_transactions_response,\n",
    "    rename_properties=True,\n",
    "    column_name_mapping=build_transactions_json_mapping,\n",
    ")\n",
    "build_transactions_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We initialised the portfolio with 6 'StockIn' transactions which are visible as the first 6 items in the Output Transactions list. \n",
    "\n",
    "The remaining 5 transactions that we can see in the output are 'synthetic transactions' that LUSID creates in our portfolio as a result of the actions and adjustments that we have instructed. \n",
    "\n",
    "LUSID is built to ensure that anything that impacts a portfolio is represented, even if it has not been directly applied.\n",
    "\n",
    "### Some things to note with transaction IDs\n",
    "\n",
    "LUSID creates transaction IDs for synthetic transactions and rather than creating unique identifiers each time, it is designed to use IDs based on the source transaction or corporate action transition to retain the relationships and traceability. \n",
    "\n",
    "#### Bonus issue and stock split\n",
    "We can see this in our notebook by looking at the transaction IDs for the bonus issue and stock split corporate actions (first dataframe). The IDs here match the 'code' or ID of the transitions that they are based on (second dataframe). "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>TransactionId</th>\n",
       "      <th>TransactionType</th>\n",
       "      <th>TransactionTypeDesc</th>\n",
       "      <th>ClientId</th>\n",
       "      <th>LusidInstrumentId</th>\n",
       "      <th>TransactionDate</th>\n",
       "      <th>SettlementDate</th>\n",
       "      <th>Units</th>\n",
       "      <th>TransactionAmount</th>\n",
       "      <th>Price</th>\n",
       "      <th>PriceType</th>\n",
       "      <th>TotalConsideration</th>\n",
       "      <th>TotalConsiderationCurrency</th>\n",
       "      <th>ExchangeRate</th>\n",
       "      <th>TransactionToPortfolioRate</th>\n",
       "      <th>TransactionCurrency</th>\n",
       "      <th>strategy(38b8-6dfa-6617-5c-Properties)</th>\n",
       "      <th>SourcePortfolioId(default-Properties)</th>\n",
       "      <th>SourcePortfolioScope(default-Properties)</th>\n",
       "      <th>ResultantHolding</th>\n",
       "      <th>InstrumentName</th>\n",
       "      <th>source</th>\n",
       "      <th>TransactionStatus</th>\n",
       "      <th>EntryDateTime</th>\n",
       "      <th>RealisedGainLoss</th>\n",
       "      <th>instrument_identifiers.Instrument/default/LusidInstrumentId</th>\n",
       "      <th>OriginalTradeDate(default-Properties)</th>\n",
       "      <th>OriginalSettlementDate(default-Properties)</th>\n",
       "      <th>InstrumentCurrency</th>\n",
       "      <th>TradeToPortfolioRate(default-Properties)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>5943592343</td>\n",
       "      <td>CorpActIncrease</td>\n",
       "      <td>Corporate Action Increase</td>\n",
       "      <td>NaN</td>\n",
       "      <td>LUID_KR3A1NMI</td>\n",
       "      <td>2020-03-31 00:00:00+00:00</td>\n",
       "      <td>2020-03-31 00:00:00+00:00</td>\n",
       "      <td>13200.0</td>\n",
       "      <td>66000.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>Price</td>\n",
       "      <td>0.0</td>\n",
       "      <td>GBP</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>GBP</td>\n",
       "      <td>balanced</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>145200.0</td>\n",
       "      <td>Aviva</td>\n",
       "      <td></td>\n",
       "      <td>Active</td>\n",
       "      <td>2020-08-04 10:48:49.961555+00:00</td>\n",
       "      <td>[]</td>\n",
       "      <td>LUID_KR3A1NMI</td>\n",
       "      <td>2020-01-02T00:00:00.0000000+00:00</td>\n",
       "      <td>2020-01-04T00:00:00.0000000+00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>5943592344</td>\n",
       "      <td>CorpActIncrease</td>\n",
       "      <td>Corporate Action Increase</td>\n",
       "      <td>NaN</td>\n",
       "      <td>LUID_AU5UQIVK</td>\n",
       "      <td>2020-03-31 00:00:00+00:00</td>\n",
       "      <td>2020-03-31 00:00:00+00:00</td>\n",
       "      <td>12000.0</td>\n",
       "      <td>108000.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>Price</td>\n",
       "      <td>0.0</td>\n",
       "      <td>GBP</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>GBP</td>\n",
       "      <td>balanced</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>24000.0</td>\n",
       "      <td>Tesco</td>\n",
       "      <td></td>\n",
       "      <td>Active</td>\n",
       "      <td>2020-08-04 10:48:49.961555+00:00</td>\n",
       "      <td>[]</td>\n",
       "      <td>LUID_AU5UQIVK</td>\n",
       "      <td>2020-01-16T00:00:00.0000000+00:00</td>\n",
       "      <td>2020-01-18T00:00:00.0000000+00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  TransactionId  TransactionType        TransactionTypeDesc ClientId  \\\n",
       "6    5943592343  CorpActIncrease  Corporate Action Increase      NaN   \n",
       "7    5943592344  CorpActIncrease  Corporate Action Increase      NaN   \n",
       "\n",
       "  LusidInstrumentId           TransactionDate            SettlementDate  \\\n",
       "6     LUID_KR3A1NMI 2020-03-31 00:00:00+00:00 2020-03-31 00:00:00+00:00   \n",
       "7     LUID_AU5UQIVK 2020-03-31 00:00:00+00:00 2020-03-31 00:00:00+00:00   \n",
       "\n",
       "     Units  TransactionAmount  Price PriceType  TotalConsideration  \\\n",
       "6  13200.0            66000.0    5.0     Price                 0.0   \n",
       "7  12000.0           108000.0    9.0     Price                 0.0   \n",
       "\n",
       "  TotalConsiderationCurrency  ExchangeRate  TransactionToPortfolioRate  \\\n",
       "6                        GBP           1.0                         0.0   \n",
       "7                        GBP           1.0                         0.0   \n",
       "\n",
       "  TransactionCurrency strategy(38b8-6dfa-6617-5c-Properties)  \\\n",
       "6                 GBP                               balanced   \n",
       "7                 GBP                               balanced   \n",
       "\n",
       "  SourcePortfolioId(default-Properties)  \\\n",
       "6                                   NaN   \n",
       "7                                   NaN   \n",
       "\n",
       "  SourcePortfolioScope(default-Properties)  ResultantHolding InstrumentName  \\\n",
       "6                                      NaN          145200.0          Aviva   \n",
       "7                                      NaN           24000.0          Tesco   \n",
       "\n",
       "  source TransactionStatus                    EntryDateTime RealisedGainLoss  \\\n",
       "6                   Active 2020-08-04 10:48:49.961555+00:00               []   \n",
       "7                   Active 2020-08-04 10:48:49.961555+00:00               []   \n",
       "\n",
       "  instrument_identifiers.Instrument/default/LusidInstrumentId  \\\n",
       "6                                      LUID_KR3A1NMI            \n",
       "7                                      LUID_AU5UQIVK            \n",
       "\n",
       "  OriginalTradeDate(default-Properties)  \\\n",
       "6     2020-01-02T00:00:00.0000000+00:00   \n",
       "7     2020-01-16T00:00:00.0000000+00:00   \n",
       "\n",
       "  OriginalSettlementDate(default-Properties) InstrumentCurrency  \\\n",
       "6          2020-01-04T00:00:00.0000000+00:00                NaN   \n",
       "7          2020-01-18T00:00:00.0000000+00:00                NaN   \n",
       "\n",
       "   TradeToPortfolioRate(default-Properties)  \n",
       "6                                       NaN  \n",
       "7                                       NaN  "
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "build_transactions_df.loc[build_transactions_df[\"TransactionType\"] == \"CorpActIncrease\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>code</th>\n",
       "      <th>action_description</th>\n",
       "      <th>description</th>\n",
       "      <th>announcement_date</th>\n",
       "      <th>ex_date</th>\n",
       "      <th>record_date</th>\n",
       "      <th>payment_date</th>\n",
       "      <th>client_id</th>\n",
       "      <th>input_instrument_name</th>\n",
       "      <th>input_units_factor</th>\n",
       "      <th>input_cost_factor</th>\n",
       "      <th>output_instrument_internal</th>\n",
       "      <th>output_units_factor</th>\n",
       "      <th>output_cost_factor</th>\n",
       "      <th>dividend_yield</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5943592343</td>\n",
       "      <td>bonus-issue</td>\n",
       "      <td>Aviva</td>\n",
       "      <td>2020-03-31T00:00:00Z</td>\n",
       "      <td>2020-03-31T00:00:00Z</td>\n",
       "      <td>2020-03-31T00:00:00Z</td>\n",
       "      <td>2020-03-31T00:00:00Z</td>\n",
       "      <td>EQ_1234</td>\n",
       "      <td>Aviva</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>EQ_1234</td>\n",
       "      <td>1.1</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5943592344</td>\n",
       "      <td>stock-split</td>\n",
       "      <td>Tesco</td>\n",
       "      <td>2020-03-31T00:00:00Z</td>\n",
       "      <td>2020-03-31T00:00:00Z</td>\n",
       "      <td>2020-03-31T00:00:00Z</td>\n",
       "      <td>2020-03-31T00:00:00Z</td>\n",
       "      <td>EQ_1240</td>\n",
       "      <td>Tesco</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>EQ_1240</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         code action_description description     announcement_date  \\\n",
       "1  5943592343        bonus-issue       Aviva  2020-03-31T00:00:00Z   \n",
       "2  5943592344        stock-split       Tesco  2020-03-31T00:00:00Z   \n",
       "\n",
       "                ex_date           record_date          payment_date client_id  \\\n",
       "1  2020-03-31T00:00:00Z  2020-03-31T00:00:00Z  2020-03-31T00:00:00Z   EQ_1234   \n",
       "2  2020-03-31T00:00:00Z  2020-03-31T00:00:00Z  2020-03-31T00:00:00Z   EQ_1240   \n",
       "\n",
       "  input_instrument_name  input_units_factor  input_cost_factor  \\\n",
       "1                 Aviva                   1                  1   \n",
       "2                 Tesco                   1                  1   \n",
       "\n",
       "  output_instrument_internal  output_units_factor  output_cost_factor  \\\n",
       "1                    EQ_1234                  1.1                   1   \n",
       "2                    EQ_1240                  2.0                   1   \n",
       "\n",
       "   dividend_yield  \n",
       "1             0.0  \n",
       "2             0.0  "
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "corporate_action_df.loc[corporate_action_df[\"code\"] != 5943592342]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Holding adjustment\n",
    "\n",
    "This is also true for the ID of the holding adjustment, however, in this case the ID used is simply the date-time that the adjustment was made. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>TransactionId</th>\n",
       "      <th>TransactionType</th>\n",
       "      <th>TransactionTypeDesc</th>\n",
       "      <th>ClientId</th>\n",
       "      <th>LusidInstrumentId</th>\n",
       "      <th>TransactionDate</th>\n",
       "      <th>SettlementDate</th>\n",
       "      <th>Units</th>\n",
       "      <th>TransactionAmount</th>\n",
       "      <th>Price</th>\n",
       "      <th>PriceType</th>\n",
       "      <th>TotalConsideration</th>\n",
       "      <th>TotalConsiderationCurrency</th>\n",
       "      <th>ExchangeRate</th>\n",
       "      <th>TransactionToPortfolioRate</th>\n",
       "      <th>TransactionCurrency</th>\n",
       "      <th>strategy(38b8-6dfa-6617-5c-Properties)</th>\n",
       "      <th>SourcePortfolioId(default-Properties)</th>\n",
       "      <th>SourcePortfolioScope(default-Properties)</th>\n",
       "      <th>ResultantHolding</th>\n",
       "      <th>InstrumentName</th>\n",
       "      <th>source</th>\n",
       "      <th>TransactionStatus</th>\n",
       "      <th>EntryDateTime</th>\n",
       "      <th>RealisedGainLoss</th>\n",
       "      <th>instrument_identifiers.Instrument/default/LusidInstrumentId</th>\n",
       "      <th>OriginalTradeDate(default-Properties)</th>\n",
       "      <th>OriginalSettlementDate(default-Properties)</th>\n",
       "      <th>InstrumentCurrency</th>\n",
       "      <th>TradeToPortfolioRate(default-Properties)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2020-01-01T00:00:00.0000000+00:00</td>\n",
       "      <td>AdjustmentIncrease</td>\n",
       "      <td>Increase Adjustment</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CCY_GBP</td>\n",
       "      <td>2020-01-01 00:00:00+00:00</td>\n",
       "      <td>2020-01-01 00:00:00+00:00</td>\n",
       "      <td>500000.0</td>\n",
       "      <td>500000.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Price</td>\n",
       "      <td>0.0</td>\n",
       "      <td>GBP</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>GBP</td>\n",
       "      <td>balanced</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>500000.0</td>\n",
       "      <td>CCY_GBP</td>\n",
       "      <td></td>\n",
       "      <td>Active</td>\n",
       "      <td>2020-08-04 10:48:51.449733+00:00</td>\n",
       "      <td>[]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CCY_GBP</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                       TransactionId     TransactionType  TransactionTypeDesc  \\\n",
       "8  2020-01-01T00:00:00.0000000+00:00  AdjustmentIncrease  Increase Adjustment   \n",
       "\n",
       "  ClientId LusidInstrumentId           TransactionDate  \\\n",
       "8      NaN           CCY_GBP 2020-01-01 00:00:00+00:00   \n",
       "\n",
       "             SettlementDate     Units  TransactionAmount  Price PriceType  \\\n",
       "8 2020-01-01 00:00:00+00:00  500000.0           500000.0    0.0     Price   \n",
       "\n",
       "   TotalConsideration TotalConsiderationCurrency  ExchangeRate  \\\n",
       "8                 0.0                        GBP           1.0   \n",
       "\n",
       "   TransactionToPortfolioRate TransactionCurrency  \\\n",
       "8                         1.0                 GBP   \n",
       "\n",
       "  strategy(38b8-6dfa-6617-5c-Properties)  \\\n",
       "8                               balanced   \n",
       "\n",
       "  SourcePortfolioId(default-Properties)  \\\n",
       "8                                   NaN   \n",
       "\n",
       "  SourcePortfolioScope(default-Properties)  ResultantHolding InstrumentName  \\\n",
       "8                                      NaN          500000.0        CCY_GBP   \n",
       "\n",
       "  source TransactionStatus                    EntryDateTime RealisedGainLoss  \\\n",
       "8                   Active 2020-08-04 10:48:51.449733+00:00               []   \n",
       "\n",
       "  instrument_identifiers.Instrument/default/LusidInstrumentId  \\\n",
       "8                                                NaN            \n",
       "\n",
       "  OriginalTradeDate(default-Properties)  \\\n",
       "8                                   NaN   \n",
       "\n",
       "  OriginalSettlementDate(default-Properties) InstrumentCurrency  \\\n",
       "8                                        NaN            CCY_GBP   \n",
       "\n",
       "   TradeToPortfolioRate(default-Properties)  \n",
       "8                                       1.0  "
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "build_transactions_df.loc[build_transactions_df[\"TransactionType\"] == \"AdjustmentIncrease\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The general pattern is that there is a single 'input transaction' that has a unique identifier and all 'built \n",
    "transactions' relating to that input have the same identifier so that they can be linked. \n",
    "\n",
    "#### Dividend payment\n",
    "\n",
    "This means that if we pro-rate a dividend payment across two sub-holding keys or if we split a transaction via strategy tags, each resultant transaction have the same id. We can see this by looking at the output transactions relating to our dividend payment from Barclays. The synthetic transactions that LUSID creates for each strategy have the same transaction IDs."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>TransactionId</th>\n",
       "      <th>TransactionType</th>\n",
       "      <th>TransactionTypeDesc</th>\n",
       "      <th>ClientId</th>\n",
       "      <th>LusidInstrumentId</th>\n",
       "      <th>TransactionDate</th>\n",
       "      <th>SettlementDate</th>\n",
       "      <th>Units</th>\n",
       "      <th>TransactionAmount</th>\n",
       "      <th>Price</th>\n",
       "      <th>PriceType</th>\n",
       "      <th>TotalConsideration</th>\n",
       "      <th>TotalConsiderationCurrency</th>\n",
       "      <th>ExchangeRate</th>\n",
       "      <th>TransactionToPortfolioRate</th>\n",
       "      <th>TransactionCurrency</th>\n",
       "      <th>strategy(38b8-6dfa-6617-5c-Properties)</th>\n",
       "      <th>SourcePortfolioId(default-Properties)</th>\n",
       "      <th>SourcePortfolioScope(default-Properties)</th>\n",
       "      <th>ResultantHolding</th>\n",
       "      <th>InstrumentName</th>\n",
       "      <th>source</th>\n",
       "      <th>TransactionStatus</th>\n",
       "      <th>EntryDateTime</th>\n",
       "      <th>RealisedGainLoss</th>\n",
       "      <th>instrument_identifiers.Instrument/default/LusidInstrumentId</th>\n",
       "      <th>OriginalTradeDate(default-Properties)</th>\n",
       "      <th>OriginalSettlementDate(default-Properties)</th>\n",
       "      <th>InstrumentCurrency</th>\n",
       "      <th>TradeToPortfolioRate(default-Properties)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>5943592342</td>\n",
       "      <td>Dividend</td>\n",
       "      <td>Dividend Payment</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CCY_GBP</td>\n",
       "      <td>2020-03-31 00:00:00+00:00</td>\n",
       "      <td>2020-03-31 00:00:00+00:00</td>\n",
       "      <td>255000.0</td>\n",
       "      <td>255000.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Price</td>\n",
       "      <td>0.0</td>\n",
       "      <td>GBP</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>GBP</td>\n",
       "      <td>balanced</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CCY_GBP</td>\n",
       "      <td></td>\n",
       "      <td>Active</td>\n",
       "      <td>2020-08-04 10:48:49.961555+00:00</td>\n",
       "      <td>[]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CCY_GBP</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>5943592342</td>\n",
       "      <td>Dividend</td>\n",
       "      <td>Dividend Payment</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CCY_GBP</td>\n",
       "      <td>2020-03-31 00:00:00+00:00</td>\n",
       "      <td>2020-03-31 00:00:00+00:00</td>\n",
       "      <td>170000.0</td>\n",
       "      <td>170000.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Price</td>\n",
       "      <td>0.0</td>\n",
       "      <td>GBP</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>GBP</td>\n",
       "      <td>banking</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CCY_GBP</td>\n",
       "      <td></td>\n",
       "      <td>Active</td>\n",
       "      <td>2020-08-04 10:48:49.961555+00:00</td>\n",
       "      <td>[]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CCY_GBP</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   TransactionId TransactionType TransactionTypeDesc ClientId  \\\n",
       "9     5943592342        Dividend    Dividend Payment      NaN   \n",
       "10    5943592342        Dividend    Dividend Payment      NaN   \n",
       "\n",
       "   LusidInstrumentId           TransactionDate            SettlementDate  \\\n",
       "9            CCY_GBP 2020-03-31 00:00:00+00:00 2020-03-31 00:00:00+00:00   \n",
       "10           CCY_GBP 2020-03-31 00:00:00+00:00 2020-03-31 00:00:00+00:00   \n",
       "\n",
       "       Units  TransactionAmount  Price PriceType  TotalConsideration  \\\n",
       "9   255000.0           255000.0    1.0     Price                 0.0   \n",
       "10  170000.0           170000.0    1.0     Price                 0.0   \n",
       "\n",
       "   TotalConsiderationCurrency  ExchangeRate  TransactionToPortfolioRate  \\\n",
       "9                         GBP           1.0                         0.0   \n",
       "10                        GBP           1.0                         0.0   \n",
       "\n",
       "   TransactionCurrency strategy(38b8-6dfa-6617-5c-Properties)  \\\n",
       "9                  GBP                               balanced   \n",
       "10                 GBP                                banking   \n",
       "\n",
       "   SourcePortfolioId(default-Properties)  \\\n",
       "9                                    NaN   \n",
       "10                                   NaN   \n",
       "\n",
       "   SourcePortfolioScope(default-Properties)  ResultantHolding InstrumentName  \\\n",
       "9                                       NaN               NaN        CCY_GBP   \n",
       "10                                      NaN               NaN        CCY_GBP   \n",
       "\n",
       "   source TransactionStatus                    EntryDateTime RealisedGainLoss  \\\n",
       "9                    Active 2020-08-04 10:48:49.961555+00:00               []   \n",
       "10                   Active 2020-08-04 10:48:49.961555+00:00               []   \n",
       "\n",
       "   instrument_identifiers.Instrument/default/LusidInstrumentId  \\\n",
       "9                                                 NaN            \n",
       "10                                                NaN            \n",
       "\n",
       "   OriginalTradeDate(default-Properties)  \\\n",
       "9                                    NaN   \n",
       "10                                   NaN   \n",
       "\n",
       "   OriginalSettlementDate(default-Properties) InstrumentCurrency  \\\n",
       "9                                         NaN            CCY_GBP   \n",
       "10                                        NaN            CCY_GBP   \n",
       "\n",
       "    TradeToPortfolioRate(default-Properties)  \n",
       "9                                        NaN  \n",
       "10                                       NaN  "
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "build_transactions_df.loc[build_transactions_df[\"TransactionType\"] == \"Dividend\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The transitions dataframe filtered on the dividend action:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>code</th>\n",
       "      <th>action_description</th>\n",
       "      <th>description</th>\n",
       "      <th>announcement_date</th>\n",
       "      <th>ex_date</th>\n",
       "      <th>record_date</th>\n",
       "      <th>payment_date</th>\n",
       "      <th>client_id</th>\n",
       "      <th>input_instrument_name</th>\n",
       "      <th>input_units_factor</th>\n",
       "      <th>input_cost_factor</th>\n",
       "      <th>output_instrument_internal</th>\n",
       "      <th>output_units_factor</th>\n",
       "      <th>output_cost_factor</th>\n",
       "      <th>dividend_yield</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5943592342</td>\n",
       "      <td>dividend-cash</td>\n",
       "      <td>Barclays</td>\n",
       "      <td>2020-03-31T00:00:00Z</td>\n",
       "      <td>2020-03-31T00:00:00Z</td>\n",
       "      <td>2020-03-31T00:00:00Z</td>\n",
       "      <td>2020-03-31T00:00:00Z</td>\n",
       "      <td>EQ_1236</td>\n",
       "      <td>Barclays</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>CCY_GBP</td>\n",
       "      <td>1.7</td>\n",
       "      <td>1</td>\n",
       "      <td>1.7</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         code action_description description     announcement_date  \\\n",
       "0  5943592342      dividend-cash    Barclays  2020-03-31T00:00:00Z   \n",
       "\n",
       "                ex_date           record_date          payment_date client_id  \\\n",
       "0  2020-03-31T00:00:00Z  2020-03-31T00:00:00Z  2020-03-31T00:00:00Z   EQ_1236   \n",
       "\n",
       "  input_instrument_name  input_units_factor  input_cost_factor  \\\n",
       "0              Barclays                   1                  1   \n",
       "\n",
       "  output_instrument_internal  output_units_factor  output_cost_factor  \\\n",
       "0                    CCY_GBP                  1.7                   1   \n",
       "\n",
       "   dividend_yield  \n",
       "0             1.7  "
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "corporate_action_df.loc[corporate_action_df[\"code\"] == 5943592342]"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}