examples/use-cases/ibor/Corporate Actions.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=\"Toggle 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",
    "\"\"\"Equity - Handling Corporate Actions\n",
    "\n",
    "Attributes\n",
    "----------\n",
    "equity\n",
    "split\n",
    "merger\n",
    "spin-off\n",
    "bonus issue\n",
    "dividend\n",
    "recipes\n",
    "valuations\n",
    "corporate actions\n",
    "\"\"\"\n",
    "\n",
    "toggle_code(\"Toggle Docstring\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Creation and Lifecycle of Corporate Actions\n",
    "\n",
    "In this notebook, we demonstrate how you can create corporate actions and apply them to a portfolio.\n",
    "We will also demonstrate how the holdings in the portfolio change as we go through the lifecycle of the corporate action.\n",
    "\n",
    "## Table of Contents:\n",
    "* [1. Upload Instrument Master](#1.-Upload-Instrument-Master)\n",
    "* [2. Create a Corporate Action Source and Link to a Portfolio](#2.-Create-a-Corporate-Action-Source-and-Link-to-a-Portfolio)\n",
    "* [3. Transactions](#3.-Transactions)\n",
    "* [4. Creating a Corporate Action](#4.-Creating-a-Corporate-Action)\n",
    "* [4.1 Dividend](#4.1-Dividend)\n",
    "* [4.2 Merger](#4.2-Merger)\n",
    "* [4.3 Spin-off](#4.3-Spin-off)\n",
    "* [4.4 Split](#4.4-Split)\n",
    "* [4.5 Bonus Issue](#4.5-Bonus-Issue)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "LUSID Environment Initialised\n",
      "LUSID API Version : 0.6.10068.0\n"
     ]
    }
   ],
   "source": [
    "# Import generic non-LUSID packages\n",
    "import os\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "from datetime import datetime\n",
    "import json\n",
    "import pytz\n",
    "from IPython.core.display import HTML\n",
    "\n",
    "# Import key modules from the LUSID package\n",
    "import lusid as lu\n",
    "import lusid.models as lm\n",
    "\n",
    "# Import key functions from Lusid-Python-Tools and other packages\n",
    "from corporate_actions_utilities import (\n",
    "    run_portfolio_valuation,\n",
    "    load_eod_prices,\n",
    "    load_corporate_action_split_price,\n",
    ")\n",
    "from lusidtools.pandas_utils.lusid_pandas import lusid_response_to_data_frame\n",
    "from lusidtools.cocoon.cocoon import load_from_data_frame\n",
    "from lusidjam import RefreshingToken\n",
    "from lusidtools.cocoon.cocoon_printer import (\n",
    "    format_instruments_response,\n",
    "    format_transactions_response,\n",
    ")\n",
    "\n",
    "\n",
    "# Set DataFrame display formats\n",
    "pd.set_option(\"display.max_columns\", None)\n",
    "pd.set_option(\"display.max_rows\", None)\n",
    "pd.options.display.float_format = \"{:,.2f}\".format\n",
    "\n",
    "# Set the secrets path\n",
    "secrets_path = os.getenv(\"FBN_SECRETS_PATH\")\n",
    "\n",
    "# For running the notebook locally\n",
    "if secrets_path is None:\n",
    "    secrets_path = os.path.join(os.path.dirname(os.getcwd()), \"secrets.json\")\n",
    "\n",
    "# Authenticate our user and create our API client\n",
    "api_factory = lu.utilities.ApiClientFactory(\n",
    "    token=RefreshingToken(), api_secrets_filename=secrets_path\n",
    ")\n",
    "\n",
    "print(\"LUSID Environment Initialised\")\n",
    "print(\n",
    "    \"LUSID API Version :\",\n",
    "    api_factory.build(lu.api.ApplicationMetadataApi).get_lusid_versions().build_version,\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We will first set up our APIs and variables as well as create 2 helper functions that we will use in this notebook."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# LUSID Variable Definitions\n",
    "portfolio_api = api_factory.build(lu.api.PortfoliosApi)\n",
    "transaction_portfolios_api = api_factory.build(lu.api.TransactionPortfoliosApi)\n",
    "instruments_api = api_factory.build(lu.api.InstrumentsApi)\n",
    "corporate_action_sources_api = api_factory.build(lu.api.CorporateActionSourcesApi)\n",
    "aggregation_api = api_factory.build(lu.AggregationApi)\n",
    "quotes_api = api_factory.build(lu.QuotesApi)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Define scopes\n",
    "scope = \"ibor\"\n",
    "ca_code = \"ibor_corp_act\"\n",
    "portfolio_code = \"CorporateActionsPortfolio\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "def to_datetime(date):\n",
    "    datetime_date = datetime.strptime(date, \"%d/%m/%Y\").replace(tzinfo=pytz.utc)\n",
    "    return datetime_date\n",
    "\n",
    "\n",
    "def get_portfolio_holdings(date):\n",
    "    holdings = transaction_portfolios_api.get_holdings(\n",
    "        scope=scope,\n",
    "        code=portfolio_code,\n",
    "        effective_at=to_datetime(date),\n",
    "        property_keys=[\"instrument/default/Name\"],\n",
    "    )\n",
    "\n",
    "    return lusid_response_to_data_frame(holdings)[\n",
    "        [\n",
    "            \"properties.Instrument/default/Name.value.label_value\",\n",
    "            \"holding_type\",\n",
    "            \"units\",\n",
    "            \"settled_units\",\n",
    "            \"cost.amount\",\n",
    "            \"cost.currency\",\n",
    "            \"cost_portfolio_ccy.amount\",\n",
    "        ]\n",
    "    ]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. Upload instrument master"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Before creating a portfolio, we must first define the assets that we will be working with by uploading an instrument master."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>instrument_name</th>\n",
       "      <th>client_internal</th>\n",
       "      <th>currency</th>\n",
       "      <th>isin</th>\n",
       "      <th>figi</th>\n",
       "      <th>exchange_code</th>\n",
       "      <th>country_issue</th>\n",
       "      <th>ticker</th>\n",
       "      <th>market_sector</th>\n",
       "      <th>security_type</th>\n",
       "      <th>coupon</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Amazon_Nasdaq_AMZN</td>\n",
       "      <td>imd_34634534</td>\n",
       "      <td>USD</td>\n",
       "      <td>US0231351067</td>\n",
       "      <td>BBG000BVPXP1</td>\n",
       "      <td>UN</td>\n",
       "      <td>united_states_america</td>\n",
       "      <td>AMZN</td>\n",
       "      <td>equity</td>\n",
       "      <td>common_stock</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Apple_Nasdaq_AAPL</td>\n",
       "      <td>imd_35345345</td>\n",
       "      <td>USD</td>\n",
       "      <td>US0378331005</td>\n",
       "      <td>BBG000B9XVV8</td>\n",
       "      <td>UN</td>\n",
       "      <td>united_states_america</td>\n",
       "      <td>AAPL</td>\n",
       "      <td>equity</td>\n",
       "      <td>common_stock</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ExpressScripts_NYSE_ESRX</td>\n",
       "      <td>imd_34352311</td>\n",
       "      <td>USD</td>\n",
       "      <td>US30219G1085</td>\n",
       "      <td>BBG000C16621</td>\n",
       "      <td>UN</td>\n",
       "      <td>united_states_america</td>\n",
       "      <td>ESRX</td>\n",
       "      <td>equity</td>\n",
       "      <td>common_stock</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>TrinityIndustries_NYSE_TRN</td>\n",
       "      <td>imd_34235200</td>\n",
       "      <td>USD</td>\n",
       "      <td>US8965221091</td>\n",
       "      <td>BBG000BVL406</td>\n",
       "      <td>UN</td>\n",
       "      <td>united_states_america</td>\n",
       "      <td>TRN</td>\n",
       "      <td>equity</td>\n",
       "      <td>common_stock</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Trex_NYSE_TREX</td>\n",
       "      <td>imd_32423956</td>\n",
       "      <td>USD</td>\n",
       "      <td>US89531P1057</td>\n",
       "      <td>BBG000BTGM43</td>\n",
       "      <td>UN</td>\n",
       "      <td>united_states_america</td>\n",
       "      <td>TREX</td>\n",
       "      <td>equity</td>\n",
       "      <td>common_stock</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Cigna_NYSE_CI</td>\n",
       "      <td>imd_32452391</td>\n",
       "      <td>USD</td>\n",
       "      <td>US1255091092</td>\n",
       "      <td>BBG00KXXK940</td>\n",
       "      <td>UN</td>\n",
       "      <td>united_states_america</td>\n",
       "      <td>CI</td>\n",
       "      <td>equity</td>\n",
       "      <td>common_stock</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Arcosa_NYSE_ACA</td>\n",
       "      <td>imd_23423409</td>\n",
       "      <td>USD</td>\n",
       "      <td>US0396531008</td>\n",
       "      <td>BBG00JGMWFQ5</td>\n",
       "      <td>UN</td>\n",
       "      <td>united_states_america</td>\n",
       "      <td>ACA</td>\n",
       "      <td>equity</td>\n",
       "      <td>common_stock</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>ACM_Research_Inc</td>\n",
       "      <td>imd_23423410</td>\n",
       "      <td>USD</td>\n",
       "      <td>US0236530000</td>\n",
       "      <td>BBG00HPSG933</td>\n",
       "      <td>UN</td>\n",
       "      <td>united_states_america</td>\n",
       "      <td>ACMR</td>\n",
       "      <td>equity</td>\n",
       "      <td>common_stock</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              instrument_name client_internal currency          isin  \\\n",
       "0          Amazon_Nasdaq_AMZN    imd_34634534      USD  US0231351067   \n",
       "1           Apple_Nasdaq_AAPL    imd_35345345      USD  US0378331005   \n",
       "2    ExpressScripts_NYSE_ESRX    imd_34352311      USD  US30219G1085   \n",
       "3  TrinityIndustries_NYSE_TRN    imd_34235200      USD  US8965221091   \n",
       "4              Trex_NYSE_TREX    imd_32423956      USD  US89531P1057   \n",
       "5               Cigna_NYSE_CI    imd_32452391      USD  US1255091092   \n",
       "6             Arcosa_NYSE_ACA    imd_23423409      USD  US0396531008   \n",
       "7            ACM_Research_Inc    imd_23423410      USD  US0236530000   \n",
       "\n",
       "           figi exchange_code          country_issue ticker market_sector  \\\n",
       "0  BBG000BVPXP1            UN  united_states_america   AMZN        equity   \n",
       "1  BBG000B9XVV8            UN  united_states_america   AAPL        equity   \n",
       "2  BBG000C16621            UN  united_states_america   ESRX        equity   \n",
       "3  BBG000BVL406            UN  united_states_america    TRN        equity   \n",
       "4  BBG000BTGM43            UN  united_states_america   TREX        equity   \n",
       "5  BBG00KXXK940            UN  united_states_america     CI        equity   \n",
       "6  BBG00JGMWFQ5            UN  united_states_america    ACA        equity   \n",
       "7  BBG00HPSG933            UN  united_states_america   ACMR        equity   \n",
       "\n",
       "  security_type  coupon  \n",
       "0  common_stock     NaN  \n",
       "1  common_stock     NaN  \n",
       "2  common_stock     NaN  \n",
       "3  common_stock     NaN  \n",
       "4  common_stock     NaN  \n",
       "5  common_stock     NaN  \n",
       "6  common_stock     NaN  \n",
       "7  common_stock     NaN  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "instruments = pd.read_csv(\"data/corporateactions-instruments.csv\")\n",
    "instruments"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>8</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   success  failed  errors\n",
       "0        8       0       0"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Load the instrument into LUSID\n",
    "\n",
    "instrument_identifier_mapping = {\n",
    "    \"Figi\": \"figi\",\n",
    "    \"Isin\": \"isin\",\n",
    "}\n",
    "\n",
    "instrument_mapping_required = {\"name\": \"instrument_name\"}\n",
    "instrument_mapping_optional = {}\n",
    "\n",
    "responses = load_from_data_frame(\n",
    "    api_factory=api_factory,\n",
    "    scope=scope,\n",
    "    data_frame=instruments,\n",
    "    mapping_required=instrument_mapping_required,\n",
    "    mapping_optional=instrument_mapping_optional,\n",
    "    file_type=\"instrument\",\n",
    "    identifier_mapping=instrument_identifier_mapping,\n",
    ")\n",
    "\n",
    "succ, failed, errors = format_instruments_response(responses)\n",
    "pd.DataFrame(\n",
    "    data=[{\"success\": len(succ), \"failed\": len(failed), \"errors\": len(errors)}]\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. Create a Corporate Action Source and Link to a Portfolio"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "All corporate actions are stored in a corporate actions source. This source will then be linked to a portfolio using the corporate_action_source_id parameter upon creation of the portfolio. Alternatively, you can also update the corporate actions source of a portfolio after creating it by using https://www.lusid.com/docs/api/#operation/UpsertPortfolioDetails."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Could not create a Corporate Action Source with id ibor_corp_act because it already exists in scope ibor.\n"
     ]
    }
   ],
   "source": [
    "try:\n",
    "\n",
    "    source_request = lm.CreateCorporateActionSourceRequest(\n",
    "        scope=scope,\n",
    "        code=ca_code,\n",
    "        display_name=\"Ibor Corporate Action Source\",\n",
    "        description=\"Corporate Actions source for sample notebook\",\n",
    "    )\n",
    "\n",
    "    corporate_action_sources_api.create_corporate_action_source(\n",
    "        create_corporate_action_source_request=source_request\n",
    "    )\n",
    "\n",
    "except lu.ApiException as e:\n",
    "    print(json.loads(e.body)[\"title\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Could not create a portfolio with id 'CorporateActionsPortfolio' because it already exists in scope 'ibor'.\n"
     ]
    }
   ],
   "source": [
    "try:\n",
    "    transaction_portfolios_api.create_portfolio(\n",
    "        scope=scope,\n",
    "        create_transaction_portfolio_request=lm.CreateTransactionPortfolioRequest(\n",
    "            display_name=portfolio_code,\n",
    "            code=portfolio_code,\n",
    "            base_currency=\"USD\",\n",
    "            created=\"2010-01-01\",\n",
    "            sub_holding_keys=[],\n",
    "            corporate_action_source_id=lu.ResourceId(scope=scope, code=ca_code),\n",
    "        ),\n",
    "    )\n",
    "\n",
    "except lu.ApiException as e:\n",
    "    print(json.loads(e.body)[\"title\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. Transactions"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We populate our portfolio with transactions so we have assets to perform our corporate actions on."
   ]
  },
  {
   "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>portfolio_code</th>\n",
       "      <th>transaction_id</th>\n",
       "      <th>figi</th>\n",
       "      <th>ticker</th>\n",
       "      <th>name</th>\n",
       "      <th>transaction_type</th>\n",
       "      <th>transaction_date</th>\n",
       "      <th>settlement_date</th>\n",
       "      <th>transaction_units</th>\n",
       "      <th>transaction_price</th>\n",
       "      <th>total_consideration</th>\n",
       "      <th>transaction_currency</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>CorporateActionsPortfolio</td>\n",
       "      <td>TX001</td>\n",
       "      <td>BBG000B9XVV8</td>\n",
       "      <td>AAPL</td>\n",
       "      <td>Apple_Nasdaq_AAPL</td>\n",
       "      <td>StockIn</td>\n",
       "      <td>01/01/2018</td>\n",
       "      <td>02/01/2018</td>\n",
       "      <td>100</td>\n",
       "      <td>43.75</td>\n",
       "      <td>4,375.00</td>\n",
       "      <td>USD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>CorporateActionsPortfolio</td>\n",
       "      <td>TX002</td>\n",
       "      <td>BBG000C16621</td>\n",
       "      <td>ESRX</td>\n",
       "      <td>ExpressScripts_NYSE_ESRX</td>\n",
       "      <td>StockIn</td>\n",
       "      <td>01/01/2018</td>\n",
       "      <td>02/01/2018</td>\n",
       "      <td>100</td>\n",
       "      <td>76.01</td>\n",
       "      <td>7,601.00</td>\n",
       "      <td>USD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>CorporateActionsPortfolio</td>\n",
       "      <td>TX003</td>\n",
       "      <td>BBG000BVL406</td>\n",
       "      <td>TRN</td>\n",
       "      <td>TrinityIndustries_NYSE_TRN</td>\n",
       "      <td>StockIn</td>\n",
       "      <td>01/01/2018</td>\n",
       "      <td>02/01/2018</td>\n",
       "      <td>100</td>\n",
       "      <td>26.34</td>\n",
       "      <td>2,634.00</td>\n",
       "      <td>USD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>CorporateActionsPortfolio</td>\n",
       "      <td>TX004</td>\n",
       "      <td>BBG000BTGM43</td>\n",
       "      <td>TREX</td>\n",
       "      <td>Trex_NYSE_TREX</td>\n",
       "      <td>StockIn</td>\n",
       "      <td>01/01/2018</td>\n",
       "      <td>02/01/2018</td>\n",
       "      <td>100</td>\n",
       "      <td>27.83</td>\n",
       "      <td>2,783.00</td>\n",
       "      <td>USD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>CorporateActionsPortfolio</td>\n",
       "      <td>TX005</td>\n",
       "      <td>BBG00HPSG933</td>\n",
       "      <td>ACMR</td>\n",
       "      <td>ACM_Research_Inc</td>\n",
       "      <td>StockIn</td>\n",
       "      <td>01/01/2018</td>\n",
       "      <td>02/01/2018</td>\n",
       "      <td>100</td>\n",
       "      <td>2.00</td>\n",
       "      <td>200.00</td>\n",
       "      <td>USD</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              portfolio_code transaction_id          figi ticker  \\\n",
       "0  CorporateActionsPortfolio          TX001  BBG000B9XVV8   AAPL   \n",
       "1  CorporateActionsPortfolio          TX002  BBG000C16621   ESRX   \n",
       "2  CorporateActionsPortfolio          TX003  BBG000BVL406    TRN   \n",
       "3  CorporateActionsPortfolio          TX004  BBG000BTGM43   TREX   \n",
       "4  CorporateActionsPortfolio          TX005  BBG00HPSG933   ACMR   \n",
       "\n",
       "                         name transaction_type transaction_date  \\\n",
       "0           Apple_Nasdaq_AAPL          StockIn       01/01/2018   \n",
       "1    ExpressScripts_NYSE_ESRX          StockIn       01/01/2018   \n",
       "2  TrinityIndustries_NYSE_TRN          StockIn       01/01/2018   \n",
       "3              Trex_NYSE_TREX          StockIn       01/01/2018   \n",
       "4            ACM_Research_Inc          StockIn       01/01/2018   \n",
       "\n",
       "  settlement_date  transaction_units  transaction_price total_consideration  \\\n",
       "0      02/01/2018                100              43.75            4,375.00   \n",
       "1      02/01/2018                100              76.01            7,601.00   \n",
       "2      02/01/2018                100              26.34            2,634.00   \n",
       "3      02/01/2018                100              27.83            2,783.00   \n",
       "4      02/01/2018                100               2.00              200.00   \n",
       "\n",
       "  transaction_currency  \n",
       "0                  USD  \n",
       "1                  USD  \n",
       "2                  USD  \n",
       "3                  USD  \n",
       "4                  USD  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# upsert transactions\n",
    "transactions = pd.read_csv(\"data/corporate_actions_transactions.csv\")\n",
    "transactions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "# map the transactions file\n",
    "\n",
    "transaction_field_mapping_required = {\n",
    "    \"code\": \"portfolio_code\",\n",
    "    \"transaction_id\": \"transaction_id\",\n",
    "    \"type\": \"transaction_type\",\n",
    "    \"transaction_date\": \"transaction_date\",\n",
    "    \"settlement_date\": \"settlement_date\",\n",
    "    \"units\": \"transaction_units\",\n",
    "    \"transaction_price.price\": \"transaction_price\",\n",
    "    \"transaction_price.type\": \"$Price\",\n",
    "    \"total_consideration.amount\": \"total_consideration\",\n",
    "    \"total_consideration.currency\": \"transaction_currency\",\n",
    "    \"transaction_currency\": \"transaction_currency\",\n",
    "}\n",
    "\n",
    "\n",
    "transaction_identifier_mapping = {\n",
    "    \"Figi\": \"figi\",\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>success</th>\n",
       "      <th>failed</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   success  failed\n",
       "0        1       0"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "responses = load_from_data_frame(\n",
    "    api_factory=api_factory,\n",
    "    scope=scope,\n",
    "    data_frame=transactions,\n",
    "    mapping_required=transaction_field_mapping_required,\n",
    "    mapping_optional={},\n",
    "    identifier_mapping=transaction_identifier_mapping,\n",
    "    file_type=\"transaction\",\n",
    ")\n",
    "\n",
    "succ, failed = format_transactions_response(responses)\n",
    "display(pd.DataFrame(data=[{\"success\": len(succ), \"failed\": len(failed)}]))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4. Creating corporate actions"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The below contains details of the corporate actions that we will be using for these examples. For details on how corporate actions are modelled in LUSID, see https://support.lusid.com/knowledgebase/article/KA-01867/en-us"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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>Description</th>\n",
       "      <th>code</th>\n",
       "      <th>action_description</th>\n",
       "      <th>announcement_date</th>\n",
       "      <th>ex_date</th>\n",
       "      <th>record_date</th>\n",
       "      <th>payment_date</th>\n",
       "      <th>input_instrument_figi</th>\n",
       "      <th>instrument_input_ticker</th>\n",
       "      <th>input_instrument_name</th>\n",
       "      <th>input_units_factor</th>\n",
       "      <th>input_cost_factor</th>\n",
       "      <th>output_instrument_figi</th>\n",
       "      <th>output_instrument_name</th>\n",
       "      <th>output_ticker</th>\n",
       "      <th>output_instrument_internal</th>\n",
       "      <th>output_units_factor</th>\n",
       "      <th>output_cost_factor</th>\n",
       "      <th>optional_second_output_instrument_figi</th>\n",
       "      <th>optional_second_output_instrument_name</th>\n",
       "      <th>optional_second_output_ticker</th>\n",
       "      <th>optional_second_output_instrument_internal</th>\n",
       "      <th>optional_second_output_units_factor</th>\n",
       "      <th>optional_second_output_cost_factor</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Apple Dividend of 0.17 cents per share</td>\n",
       "      <td>ca001</td>\n",
       "      <td>dividend</td>\n",
       "      <td>29/01/2019</td>\n",
       "      <td>08/02/2019</td>\n",
       "      <td>11/02/2019</td>\n",
       "      <td>14/02/2019</td>\n",
       "      <td>BBG000B9XVV8</td>\n",
       "      <td>AAPL</td>\n",
       "      <td>APPLE INC</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CCY_USD</td>\n",
       "      <td>0.17</td>\n",
       "      <td>0.00</td>\n",
       "      <td>NaN</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>Cigna &amp; Express Scripts Merger</td>\n",
       "      <td>ca002</td>\n",
       "      <td>merger (equivalent to exchange example)</td>\n",
       "      <td>08/03/2018</td>\n",
       "      <td>10/12/2018</td>\n",
       "      <td>13/12/2018</td>\n",
       "      <td>20/12/2018</td>\n",
       "      <td>BBG000C16621</td>\n",
       "      <td>ESRX</td>\n",
       "      <td>EXPRESS SCRIPTS HOLDINGS CO</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>BBG00KXXK940</td>\n",
       "      <td>CIGNA CORP</td>\n",
       "      <td>CI</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.24</td>\n",
       "      <td>1.00</td>\n",
       "      <td>NaN</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>Acosa spin-off from Trinity Industries</td>\n",
       "      <td>ca003</td>\n",
       "      <td>spin-off (equivalent to exchange example)</td>\n",
       "      <td>01/01/2018</td>\n",
       "      <td>17/10/2018</td>\n",
       "      <td>20/10/2018</td>\n",
       "      <td>01/11/2018</td>\n",
       "      <td>BBG000BVL406</td>\n",
       "      <td>TRN</td>\n",
       "      <td>TRINITY INDUSTRIES INC</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>BBG00JGMWFQ5</td>\n",
       "      <td>ARCOSA INC</td>\n",
       "      <td>ACA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.00</td>\n",
       "      <td>0.28</td>\n",
       "      <td>BBG000BVL406</td>\n",
       "      <td>TRN</td>\n",
       "      <td>TRINITY INDUSTRIES INC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.00</td>\n",
       "      <td>0.72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Trex 2 for 1 stock split</td>\n",
       "      <td>ca004</td>\n",
       "      <td>split</td>\n",
       "      <td>07/05/2018</td>\n",
       "      <td>21/05/2018</td>\n",
       "      <td>23/05/2018</td>\n",
       "      <td>19/06/2018</td>\n",
       "      <td>BBG000BTGM43</td>\n",
       "      <td>TREX</td>\n",
       "      <td>TREX COMPANY INC</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>BBG000BTGM43</td>\n",
       "      <td>TREX COMPANY INC</td>\n",
       "      <td>TREX</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>NaN</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>ACM Research Inc bonus issue</td>\n",
       "      <td>ca005</td>\n",
       "      <td>bonus issue</td>\n",
       "      <td>03/03/2022</td>\n",
       "      <td>14/03/2022</td>\n",
       "      <td>16/03/2022</td>\n",
       "      <td>23/03/2022</td>\n",
       "      <td>BBG00HPSG933</td>\n",
       "      <td>ACMR</td>\n",
       "      <td>ACM Research Inc</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>BBG00HPSG933</td>\n",
       "      <td>ACM Research Inc</td>\n",
       "      <td>ACMR</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                              Description   code  \\\n",
       "0  Apple Dividend of 0.17 cents per share  ca001   \n",
       "1          Cigna & Express Scripts Merger  ca002   \n",
       "2  Acosa spin-off from Trinity Industries  ca003   \n",
       "3                Trex 2 for 1 stock split  ca004   \n",
       "4            ACM Research Inc bonus issue  ca005   \n",
       "\n",
       "                          action_description announcement_date     ex_date  \\\n",
       "0                                   dividend        29/01/2019  08/02/2019   \n",
       "1    merger (equivalent to exchange example)        08/03/2018  10/12/2018   \n",
       "2  spin-off (equivalent to exchange example)        01/01/2018  17/10/2018   \n",
       "3                                      split        07/05/2018  21/05/2018   \n",
       "4                                bonus issue        03/03/2022  14/03/2022   \n",
       "\n",
       "  record_date payment_date input_instrument_figi instrument_input_ticker  \\\n",
       "0  11/02/2019   14/02/2019          BBG000B9XVV8                    AAPL   \n",
       "1  13/12/2018   20/12/2018          BBG000C16621                    ESRX   \n",
       "2  20/10/2018   01/11/2018          BBG000BVL406                     TRN   \n",
       "3  23/05/2018   19/06/2018          BBG000BTGM43                    TREX   \n",
       "4  16/03/2022   23/03/2022          BBG00HPSG933                    ACMR   \n",
       "\n",
       "         input_instrument_name  input_units_factor  input_cost_factor  \\\n",
       "0                    APPLE INC                   1                  1   \n",
       "1  EXPRESS SCRIPTS HOLDINGS CO                   1                  1   \n",
       "2       TRINITY INDUSTRIES INC                   3                  1   \n",
       "3             TREX COMPANY INC                   1                  1   \n",
       "4             ACM Research Inc                   1                  1   \n",
       "\n",
       "  output_instrument_figi output_instrument_name output_ticker  \\\n",
       "0                    NaN                    NaN           NaN   \n",
       "1           BBG00KXXK940             CIGNA CORP            CI   \n",
       "2           BBG00JGMWFQ5             ARCOSA INC           ACA   \n",
       "3           BBG000BTGM43       TREX COMPANY INC          TREX   \n",
       "4           BBG00HPSG933       ACM Research Inc          ACMR   \n",
       "\n",
       "  output_instrument_internal  output_units_factor  output_cost_factor  \\\n",
       "0                    CCY_USD                 0.17                0.00   \n",
       "1                        NaN                 0.24                1.00   \n",
       "2                        NaN                 1.00                0.28   \n",
       "3                        NaN                 2.00                1.00   \n",
       "4                        NaN                 2.00                1.00   \n",
       "\n",
       "  optional_second_output_instrument_figi  \\\n",
       "0                                    NaN   \n",
       "1                                    NaN   \n",
       "2                           BBG000BVL406   \n",
       "3                                    NaN   \n",
       "4                                    NaN   \n",
       "\n",
       "  optional_second_output_instrument_name optional_second_output_ticker  \\\n",
       "0                                    NaN                           NaN   \n",
       "1                                    NaN                           NaN   \n",
       "2                                    TRN        TRINITY INDUSTRIES INC   \n",
       "3                                    NaN                           NaN   \n",
       "4                                    NaN                           NaN   \n",
       "\n",
       "   optional_second_output_instrument_internal  \\\n",
       "0                                         NaN   \n",
       "1                                         NaN   \n",
       "2                                         NaN   \n",
       "3                                         NaN   \n",
       "4                                         NaN   \n",
       "\n",
       "   optional_second_output_units_factor  optional_second_output_cost_factor  \n",
       "0                                  NaN                                 NaN  \n",
       "1                                  NaN                                 NaN  \n",
       "2                                 1.00                                0.72  \n",
       "3                                  NaN                                 NaN  \n",
       "4                                  NaN                                 NaN  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ca_data = pd.read_csv(\"data/corporate_actions.csv\")\n",
    "ca_data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4.1. Dividend"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A dividend will give us a certain amount of money or shares for every share we currently hold in a company. In our scenario, we are getting 17 cents per share of Apple stock. We own 100 shares in Apple so we can expect to receive 17 USD. We can see that our input factor is 1 and our output factor is 0.17. This means that for every 1 unit of our input instrument (Apple shares) we will receive 0.17 units of our output instrument (USD)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "dataset = ca_data.iloc[0]\n",
    "\n",
    "transitions = [\n",
    "    lm.CorporateActionTransition(\n",
    "        input_transition=lm.CorporateActionTransitionComponentRequest(\n",
    "            instrument_identifiers={\n",
    "                \"Instrument/default/Figi\": dataset[\"input_instrument_figi\"],\n",
    "            },\n",
    "            units_factor=float(dataset[\"input_units_factor\"]),\n",
    "            cost_factor=float(dataset[\"input_cost_factor\"]),\n",
    "        ),\n",
    "        output_transitions=[\n",
    "            lm.CorporateActionTransitionComponentRequest(\n",
    "                instrument_identifiers={\"Instrument/default/Currency\": \"USD\"},\n",
    "                units_factor=float(dataset[\"output_units_factor\"]),\n",
    "                cost_factor=float(dataset[\"output_cost_factor\"]),\n",
    "            )\n",
    "        ],\n",
    "    )\n",
    "]\n",
    "\n",
    "dividend_request = lm.UpsertCorporateActionRequest(\n",
    "    corporate_action_code=dataset[\"code\"],\n",
    "    announcement_date=to_datetime(dataset[\"announcement_date\"]),\n",
    "    ex_date=to_datetime(dataset[\"ex_date\"]),\n",
    "    record_date=to_datetime(dataset[\"record_date\"]),\n",
    "    payment_date=to_datetime(dataset[\"payment_date\"]),\n",
    "    transitions=transitions,\n",
    ")\n",
    "\n",
    "result = corporate_action_sources_api.batch_upsert_corporate_actions(\n",
    "    scope=scope, code=ca_code, upsert_corporate_action_request=[dividend_request]\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "On the announcement date of the dividend, we take a look at our holdings and see just a position in Apple of 100 units."
   ]
  },
  {
   "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>properties.Instrument/default/Name.value.label_value</th>\n",
       "      <th>holding_type</th>\n",
       "      <th>units</th>\n",
       "      <th>settled_units</th>\n",
       "      <th>cost.amount</th>\n",
       "      <th>cost.currency</th>\n",
       "      <th>cost_portfolio_ccy.amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Apple_Nasdaq_AAPL</td>\n",
       "      <td>P</td>\n",
       "      <td>100.00</td>\n",
       "      <td>100.00</td>\n",
       "      <td>4,375.00</td>\n",
       "      <td>USD</td>\n",
       "      <td>4,375.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ExpressScripts_NYSE_ESRX</td>\n",
       "      <td>P</td>\n",
       "      <td>100.00</td>\n",
       "      <td>100.00</td>\n",
       "      <td>7,601.00</td>\n",
       "      <td>USD</td>\n",
       "      <td>7,601.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>TrinityIndustries_NYSE_TRN</td>\n",
       "      <td>P</td>\n",
       "      <td>34.00</td>\n",
       "      <td>34.00</td>\n",
       "      <td>1,895.93</td>\n",
       "      <td>USD</td>\n",
       "      <td>1,895.93</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Trex_NYSE_TREX</td>\n",
       "      <td>P</td>\n",
       "      <td>200.00</td>\n",
       "      <td>200.00</td>\n",
       "      <td>2,783.00</td>\n",
       "      <td>USD</td>\n",
       "      <td>2,783.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ACM_Research_Inc</td>\n",
       "      <td>P</td>\n",
       "      <td>100.00</td>\n",
       "      <td>100.00</td>\n",
       "      <td>200.00</td>\n",
       "      <td>USD</td>\n",
       "      <td>200.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Arcosa_NYSE_ACA</td>\n",
       "      <td>P</td>\n",
       "      <td>33.00</td>\n",
       "      <td>33.00</td>\n",
       "      <td>738.07</td>\n",
       "      <td>USD</td>\n",
       "      <td>738.07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Cigna_NYSE_CI</td>\n",
       "      <td>P</td>\n",
       "      <td>24.00</td>\n",
       "      <td>24.00</td>\n",
       "      <td>7,601.00</td>\n",
       "      <td>USD</td>\n",
       "      <td>7,601.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  properties.Instrument/default/Name.value.label_value holding_type  units  \\\n",
       "0                                  Apple_Nasdaq_AAPL              P 100.00   \n",
       "1                           ExpressScripts_NYSE_ESRX              P 100.00   \n",
       "2                         TrinityIndustries_NYSE_TRN              P  34.00   \n",
       "3                                     Trex_NYSE_TREX              P 200.00   \n",
       "4                                   ACM_Research_Inc              P 100.00   \n",
       "5                                    Arcosa_NYSE_ACA              P  33.00   \n",
       "6                                      Cigna_NYSE_CI              P  24.00   \n",
       "\n",
       "   settled_units  cost.amount cost.currency  cost_portfolio_ccy.amount  \n",
       "0         100.00     4,375.00           USD                   4,375.00  \n",
       "1         100.00     7,601.00           USD                   7,601.00  \n",
       "2          34.00     1,895.93           USD                   1,895.93  \n",
       "3         200.00     2,783.00           USD                   2,783.00  \n",
       "4         100.00       200.00           USD                     200.00  \n",
       "5          33.00       738.07           USD                     738.07  \n",
       "6          24.00     7,601.00           USD                   7,601.00  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get_portfolio_holdings(\"29/01/2019\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "On the ex-date, we can see the USD cash dividend coming in but it has not settled yet. We can see this under settled_units which is still 0."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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>properties.Instrument/default/Name.value.label_value</th>\n",
       "      <th>holding_type</th>\n",
       "      <th>units</th>\n",
       "      <th>settled_units</th>\n",
       "      <th>cost.amount</th>\n",
       "      <th>cost.currency</th>\n",
       "      <th>cost_portfolio_ccy.amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Apple_Nasdaq_AAPL</td>\n",
       "      <td>P</td>\n",
       "      <td>100.00</td>\n",
       "      <td>100.00</td>\n",
       "      <td>4,375.00</td>\n",
       "      <td>USD</td>\n",
       "      <td>4,375.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ExpressScripts_NYSE_ESRX</td>\n",
       "      <td>P</td>\n",
       "      <td>100.00</td>\n",
       "      <td>100.00</td>\n",
       "      <td>7,601.00</td>\n",
       "      <td>USD</td>\n",
       "      <td>7,601.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>TrinityIndustries_NYSE_TRN</td>\n",
       "      <td>P</td>\n",
       "      <td>34.00</td>\n",
       "      <td>34.00</td>\n",
       "      <td>1,895.93</td>\n",
       "      <td>USD</td>\n",
       "      <td>1,895.93</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Trex_NYSE_TREX</td>\n",
       "      <td>P</td>\n",
       "      <td>200.00</td>\n",
       "      <td>200.00</td>\n",
       "      <td>2,783.00</td>\n",
       "      <td>USD</td>\n",
       "      <td>2,783.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ACM_Research_Inc</td>\n",
       "      <td>P</td>\n",
       "      <td>100.00</td>\n",
       "      <td>100.00</td>\n",
       "      <td>200.00</td>\n",
       "      <td>USD</td>\n",
       "      <td>200.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Arcosa_NYSE_ACA</td>\n",
       "      <td>P</td>\n",
       "      <td>33.00</td>\n",
       "      <td>33.00</td>\n",
       "      <td>738.07</td>\n",
       "      <td>USD</td>\n",
       "      <td>738.07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Cigna_NYSE_CI</td>\n",
       "      <td>P</td>\n",
       "      <td>24.00</td>\n",
       "      <td>24.00</td>\n",
       "      <td>7,601.00</td>\n",
       "      <td>USD</td>\n",
       "      <td>7,601.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>USD</td>\n",
       "      <td>A</td>\n",
       "      <td>17.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>17.00</td>\n",
       "      <td>USD</td>\n",
       "      <td>17.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  properties.Instrument/default/Name.value.label_value holding_type  units  \\\n",
       "0                                  Apple_Nasdaq_AAPL              P 100.00   \n",
       "1                           ExpressScripts_NYSE_ESRX              P 100.00   \n",
       "2                         TrinityIndustries_NYSE_TRN              P  34.00   \n",
       "3                                     Trex_NYSE_TREX              P 200.00   \n",
       "4                                   ACM_Research_Inc              P 100.00   \n",
       "5                                    Arcosa_NYSE_ACA              P  33.00   \n",
       "6                                      Cigna_NYSE_CI              P  24.00   \n",
       "7                                                USD              A  17.00   \n",
       "\n",
       "   settled_units  cost.amount cost.currency  cost_portfolio_ccy.amount  \n",
       "0         100.00     4,375.00           USD                   4,375.00  \n",
       "1         100.00     7,601.00           USD                   7,601.00  \n",
       "2          34.00     1,895.93           USD                   1,895.93  \n",
       "3         200.00     2,783.00           USD                   2,783.00  \n",
       "4         100.00       200.00           USD                     200.00  \n",
       "5          33.00       738.07           USD                     738.07  \n",
       "6          24.00     7,601.00           USD                   7,601.00  \n",
       "7           0.00        17.00           USD                      17.00  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get_portfolio_holdings(\"08/02/2019\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Finally on the payment date, we can see the USD cash position settled into our holdings."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "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>properties.Instrument/default/Name.value.label_value</th>\n",
       "      <th>holding_type</th>\n",
       "      <th>units</th>\n",
       "      <th>settled_units</th>\n",
       "      <th>cost.amount</th>\n",
       "      <th>cost.currency</th>\n",
       "      <th>cost_portfolio_ccy.amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Apple_Nasdaq_AAPL</td>\n",
       "      <td>P</td>\n",
       "      <td>100.00</td>\n",
       "      <td>100.00</td>\n",
       "      <td>4,375.00</td>\n",
       "      <td>USD</td>\n",
       "      <td>4,375.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ExpressScripts_NYSE_ESRX</td>\n",
       "      <td>P</td>\n",
       "      <td>100.00</td>\n",
       "      <td>100.00</td>\n",
       "      <td>7,601.00</td>\n",
       "      <td>USD</td>\n",
       "      <td>7,601.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>TrinityIndustries_NYSE_TRN</td>\n",
       "      <td>P</td>\n",
       "      <td>34.00</td>\n",
       "      <td>34.00</td>\n",
       "      <td>1,895.93</td>\n",
       "      <td>USD</td>\n",
       "      <td>1,895.93</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Trex_NYSE_TREX</td>\n",
       "      <td>P</td>\n",
       "      <td>200.00</td>\n",
       "      <td>200.00</td>\n",
       "      <td>2,783.00</td>\n",
       "      <td>USD</td>\n",
       "      <td>2,783.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ACM_Research_Inc</td>\n",
       "      <td>P</td>\n",
       "      <td>100.00</td>\n",
       "      <td>100.00</td>\n",
       "      <td>200.00</td>\n",
       "      <td>USD</td>\n",
       "      <td>200.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Arcosa_NYSE_ACA</td>\n",
       "      <td>P</td>\n",
       "      <td>33.00</td>\n",
       "      <td>33.00</td>\n",
       "      <td>738.07</td>\n",
       "      <td>USD</td>\n",
       "      <td>738.07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Cigna_NYSE_CI</td>\n",
       "      <td>P</td>\n",
       "      <td>24.00</td>\n",
       "      <td>24.00</td>\n",
       "      <td>7,601.00</td>\n",
       "      <td>USD</td>\n",
       "      <td>7,601.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>USD</td>\n",
       "      <td>B</td>\n",
       "      <td>17.00</td>\n",
       "      <td>17.00</td>\n",
       "      <td>17.00</td>\n",
       "      <td>USD</td>\n",
       "      <td>17.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  properties.Instrument/default/Name.value.label_value holding_type  units  \\\n",
       "0                                  Apple_Nasdaq_AAPL              P 100.00   \n",
       "1                           ExpressScripts_NYSE_ESRX              P 100.00   \n",
       "2                         TrinityIndustries_NYSE_TRN              P  34.00   \n",
       "3                                     Trex_NYSE_TREX              P 200.00   \n",
       "4                                   ACM_Research_Inc              P 100.00   \n",
       "5                                    Arcosa_NYSE_ACA              P  33.00   \n",
       "6                                      Cigna_NYSE_CI              P  24.00   \n",
       "7                                                USD              B  17.00   \n",
       "\n",
       "   settled_units  cost.amount cost.currency  cost_portfolio_ccy.amount  \n",
       "0         100.00     4,375.00           USD                   4,375.00  \n",
       "1         100.00     7,601.00           USD                   7,601.00  \n",
       "2          34.00     1,895.93           USD                   1,895.93  \n",
       "3         200.00     2,783.00           USD                   2,783.00  \n",
       "4         100.00       200.00           USD                     200.00  \n",
       "5          33.00       738.07           USD                     738.07  \n",
       "6          24.00     7,601.00           USD                   7,601.00  \n",
       "7          17.00        17.00           USD                      17.00  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get_portfolio_holdings(\"14/02/2019\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4.2. Merger"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In our example of the merger here, we will show you what happened to Express Scripts when it got acquired by Cigna. In this merger, the shareholders of Express Scripts received 0.24 shares in Cigna for every Express Scripts share they held. We can see that the output factor for this transaction is 0.24. Given that we owned 100 shares in Express Scripts at the time of this merger, we should receive 24 shares in Cigna."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "dataset = ca_data.iloc[1]\n",
    "\n",
    "transitions = [\n",
    "    lm.CorporateActionTransition(\n",
    "        input_transition=lm.CorporateActionTransitionComponentRequest(\n",
    "            instrument_identifiers={\n",
    "                \"Instrument/default/Figi\": dataset[\"input_instrument_figi\"]\n",
    "            },\n",
    "            units_factor=float(dataset[\"input_units_factor\"]),\n",
    "            cost_factor=float(dataset[\"input_cost_factor\"]),\n",
    "        ),\n",
    "        output_transitions=[\n",
    "            lm.CorporateActionTransitionComponentRequest(\n",
    "                instrument_identifiers={\n",
    "                    \"Instrument/default/Figi\": dataset[\"output_instrument_figi\"]\n",
    "                },\n",
    "                units_factor=float(dataset[\"output_units_factor\"]),\n",
    "                cost_factor=float(dataset[\"output_cost_factor\"]),\n",
    "            )\n",
    "        ],\n",
    "    )\n",
    "]\n",
    "\n",
    "merger_request = lm.UpsertCorporateActionRequest(\n",
    "    corporate_action_code=dataset[\"code\"],\n",
    "    announcement_date=to_datetime(dataset[\"announcement_date\"]),\n",
    "    ex_date=to_datetime(dataset[\"ex_date\"]),\n",
    "    record_date=to_datetime(dataset[\"record_date\"]),\n",
    "    payment_date=to_datetime(dataset[\"payment_date\"]),\n",
    "    transitions=transitions,\n",
    ")\n",
    "\n",
    "result = corporate_action_sources_api.batch_upsert_corporate_actions(\n",
    "    scope=scope, code=ca_code, upsert_corporate_action_request=[merger_request]\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "On the announcement date of the merger, we can see a position of 100 units in Express Scripts in our holdings."
   ]
  },
  {
   "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>properties.Instrument/default/Name.value.label_value</th>\n",
       "      <th>holding_type</th>\n",
       "      <th>units</th>\n",
       "      <th>settled_units</th>\n",
       "      <th>cost.amount</th>\n",
       "      <th>cost.currency</th>\n",
       "      <th>cost_portfolio_ccy.amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Apple_Nasdaq_AAPL</td>\n",
       "      <td>P</td>\n",
       "      <td>100.00</td>\n",
       "      <td>100.00</td>\n",
       "      <td>4,375.00</td>\n",
       "      <td>USD</td>\n",
       "      <td>4,375.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ExpressScripts_NYSE_ESRX</td>\n",
       "      <td>P</td>\n",
       "      <td>100.00</td>\n",
       "      <td>100.00</td>\n",
       "      <td>7,601.00</td>\n",
       "      <td>USD</td>\n",
       "      <td>7,601.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>TrinityIndustries_NYSE_TRN</td>\n",
       "      <td>P</td>\n",
       "      <td>100.00</td>\n",
       "      <td>100.00</td>\n",
       "      <td>2,634.00</td>\n",
       "      <td>USD</td>\n",
       "      <td>2,634.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Trex_NYSE_TREX</td>\n",
       "      <td>P</td>\n",
       "      <td>100.00</td>\n",
       "      <td>100.00</td>\n",
       "      <td>2,783.00</td>\n",
       "      <td>USD</td>\n",
       "      <td>2,783.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ACM_Research_Inc</td>\n",
       "      <td>P</td>\n",
       "      <td>100.00</td>\n",
       "      <td>100.00</td>\n",
       "      <td>200.00</td>\n",
       "      <td>USD</td>\n",
       "      <td>200.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  properties.Instrument/default/Name.value.label_value holding_type  units  \\\n",
       "0                                  Apple_Nasdaq_AAPL              P 100.00   \n",
       "1                           ExpressScripts_NYSE_ESRX              P 100.00   \n",
       "2                         TrinityIndustries_NYSE_TRN              P 100.00   \n",
       "3                                     Trex_NYSE_TREX              P 100.00   \n",
       "4                                   ACM_Research_Inc              P 100.00   \n",
       "\n",
       "   settled_units  cost.amount cost.currency  cost_portfolio_ccy.amount  \n",
       "0         100.00     4,375.00           USD                   4,375.00  \n",
       "1         100.00     7,601.00           USD                   7,601.00  \n",
       "2         100.00     2,634.00           USD                   2,634.00  \n",
       "3         100.00     2,783.00           USD                   2,783.00  \n",
       "4         100.00       200.00           USD                     200.00  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get_portfolio_holdings(\"08/03/2018\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "On the ex-date, we see an unsettled position of 24 units in Cigna as expected. These will then settle on 20/12/2018 which is the payment date."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "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>properties.Instrument/default/Name.value.label_value</th>\n",
       "      <th>holding_type</th>\n",
       "      <th>units</th>\n",
       "      <th>settled_units</th>\n",
       "      <th>cost.amount</th>\n",
       "      <th>cost.currency</th>\n",
       "      <th>cost_portfolio_ccy.amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Apple_Nasdaq_AAPL</td>\n",
       "      <td>P</td>\n",
       "      <td>100.00</td>\n",
       "      <td>100.00</td>\n",
       "      <td>4,375.00</td>\n",
       "      <td>USD</td>\n",
       "      <td>4,375.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ExpressScripts_NYSE_ESRX</td>\n",
       "      <td>P</td>\n",
       "      <td>100.00</td>\n",
       "      <td>100.00</td>\n",
       "      <td>7,601.00</td>\n",
       "      <td>USD</td>\n",
       "      <td>7,601.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>TrinityIndustries_NYSE_TRN</td>\n",
       "      <td>P</td>\n",
       "      <td>34.00</td>\n",
       "      <td>34.00</td>\n",
       "      <td>1,895.93</td>\n",
       "      <td>USD</td>\n",
       "      <td>1,895.93</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Trex_NYSE_TREX</td>\n",
       "      <td>P</td>\n",
       "      <td>200.00</td>\n",
       "      <td>200.00</td>\n",
       "      <td>2,783.00</td>\n",
       "      <td>USD</td>\n",
       "      <td>2,783.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ACM_Research_Inc</td>\n",
       "      <td>P</td>\n",
       "      <td>100.00</td>\n",
       "      <td>100.00</td>\n",
       "      <td>200.00</td>\n",
       "      <td>USD</td>\n",
       "      <td>200.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Arcosa_NYSE_ACA</td>\n",
       "      <td>P</td>\n",
       "      <td>33.00</td>\n",
       "      <td>33.00</td>\n",
       "      <td>738.07</td>\n",
       "      <td>USD</td>\n",
       "      <td>738.07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Cigna_NYSE_CI</td>\n",
       "      <td>P</td>\n",
       "      <td>24.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>7,601.00</td>\n",
       "      <td>USD</td>\n",
       "      <td>7,601.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  properties.Instrument/default/Name.value.label_value holding_type  units  \\\n",
       "0                                  Apple_Nasdaq_AAPL              P 100.00   \n",
       "1                           ExpressScripts_NYSE_ESRX              P 100.00   \n",
       "2                         TrinityIndustries_NYSE_TRN              P  34.00   \n",
       "3                                     Trex_NYSE_TREX              P 200.00   \n",
       "4                                   ACM_Research_Inc              P 100.00   \n",
       "5                                    Arcosa_NYSE_ACA              P  33.00   \n",
       "6                                      Cigna_NYSE_CI              P  24.00   \n",
       "\n",
       "   settled_units  cost.amount cost.currency  cost_portfolio_ccy.amount  \n",
       "0         100.00     4,375.00           USD                   4,375.00  \n",
       "1         100.00     7,601.00           USD                   7,601.00  \n",
       "2          34.00     1,895.93           USD                   1,895.93  \n",
       "3         200.00     2,783.00           USD                   2,783.00  \n",
       "4         100.00       200.00           USD                     200.00  \n",
       "5          33.00       738.07           USD                     738.07  \n",
       "6           0.00     7,601.00           USD                   7,601.00  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get_portfolio_holdings(\"10/12/2018\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4.3. Spin-off"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In a spin-off the company will sell off a part of their business. In our example, the company will reward its shareholders with shares of the spun-off company. Owners of Trinity Industries shares will receive 1 share in Arcosa for every 3 shares they hold in Trinity Industries."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "dataset = ca_data.iloc[2]\n",
    "\n",
    "transitions = [\n",
    "    lm.CorporateActionTransition(\n",
    "        input_transition=lm.CorporateActionTransitionComponentRequest(\n",
    "            instrument_identifiers={\n",
    "                \"Instrument/default/Figi\": dataset[\"input_instrument_figi\"]\n",
    "            },\n",
    "            units_factor=float(dataset[\"input_units_factor\"]),\n",
    "            cost_factor=float(dataset[\"input_cost_factor\"]),\n",
    "        ),\n",
    "        output_transitions=[\n",
    "            lm.CorporateActionTransitionComponentRequest(\n",
    "                instrument_identifiers={\n",
    "                    \"Instrument/default/Figi\": dataset[\"output_instrument_figi\"]\n",
    "                },\n",
    "                units_factor=float(dataset[\"output_units_factor\"]),\n",
    "                cost_factor=float(dataset[\"output_cost_factor\"]),\n",
    "            ),\n",
    "            lm.CorporateActionTransitionComponentRequest(\n",
    "                instrument_identifiers={\n",
    "                    \"Instrument/default/Figi\": dataset[\n",
    "                        \"optional_second_output_instrument_figi\"\n",
    "                    ]\n",
    "                },\n",
    "                units_factor=float(dataset[\"optional_second_output_units_factor\"]),\n",
    "                cost_factor=float(dataset[\"optional_second_output_cost_factor\"]),\n",
    "            ),\n",
    "        ],\n",
    "    )\n",
    "]\n",
    "\n",
    "spin_off_request = lm.UpsertCorporateActionRequest(\n",
    "    corporate_action_code=dataset[\"code\"],\n",
    "    announcement_date=to_datetime(dataset[\"announcement_date\"]),\n",
    "    ex_date=to_datetime(dataset[\"ex_date\"]),\n",
    "    record_date=to_datetime(dataset[\"record_date\"]),\n",
    "    payment_date=to_datetime(dataset[\"payment_date\"]),\n",
    "    transitions=transitions,\n",
    ")\n",
    "\n",
    "result = corporate_action_sources_api.batch_upsert_corporate_actions(\n",
    "    scope=scope, code=ca_code, upsert_corporate_action_request=[spin_off_request]\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "On the day of the announcement, we can see that we hold 100 shares in Trinity Industries."
   ]
  },
  {
   "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>properties.Instrument/default/Name.value.label_value</th>\n",
       "      <th>holding_type</th>\n",
       "      <th>units</th>\n",
       "      <th>settled_units</th>\n",
       "      <th>cost.amount</th>\n",
       "      <th>cost.currency</th>\n",
       "      <th>cost_portfolio_ccy.amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Apple_Nasdaq_AAPL</td>\n",
       "      <td>P</td>\n",
       "      <td>100.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>4,375.00</td>\n",
       "      <td>USD</td>\n",
       "      <td>4,375.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ExpressScripts_NYSE_ESRX</td>\n",
       "      <td>P</td>\n",
       "      <td>100.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>7,601.00</td>\n",
       "      <td>USD</td>\n",
       "      <td>7,601.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>TrinityIndustries_NYSE_TRN</td>\n",
       "      <td>P</td>\n",
       "      <td>100.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>2,634.00</td>\n",
       "      <td>USD</td>\n",
       "      <td>2,634.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Trex_NYSE_TREX</td>\n",
       "      <td>P</td>\n",
       "      <td>100.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>2,783.00</td>\n",
       "      <td>USD</td>\n",
       "      <td>2,783.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ACM_Research_Inc</td>\n",
       "      <td>P</td>\n",
       "      <td>100.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>200.00</td>\n",
       "      <td>USD</td>\n",
       "      <td>200.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  properties.Instrument/default/Name.value.label_value holding_type  units  \\\n",
       "0                                  Apple_Nasdaq_AAPL              P 100.00   \n",
       "1                           ExpressScripts_NYSE_ESRX              P 100.00   \n",
       "2                         TrinityIndustries_NYSE_TRN              P 100.00   \n",
       "3                                     Trex_NYSE_TREX              P 100.00   \n",
       "4                                   ACM_Research_Inc              P 100.00   \n",
       "\n",
       "   settled_units  cost.amount cost.currency  cost_portfolio_ccy.amount  \n",
       "0           0.00     4,375.00           USD                   4,375.00  \n",
       "1           0.00     7,601.00           USD                   7,601.00  \n",
       "2           0.00     2,634.00           USD                   2,634.00  \n",
       "3           0.00     2,783.00           USD                   2,783.00  \n",
       "4           0.00       200.00           USD                     200.00  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get_portfolio_holdings(\"01/01/2018\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In this spin-off, we have an input units factor of 3 and an output units factor of 1. This means that for every 3 shares in Trinity Industries we hold, we will receive 1 share in Arcosa.\n",
    "We can see this reflected in our holdings, as we had 100 units of Trinity Industries and we thus we receive 33 shares in Arcosa on the payment date."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "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>properties.Instrument/default/Name.value.label_value</th>\n",
       "      <th>holding_type</th>\n",
       "      <th>units</th>\n",
       "      <th>settled_units</th>\n",
       "      <th>cost.amount</th>\n",
       "      <th>cost.currency</th>\n",
       "      <th>cost_portfolio_ccy.amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Apple_Nasdaq_AAPL</td>\n",
       "      <td>P</td>\n",
       "      <td>100.00</td>\n",
       "      <td>100.00</td>\n",
       "      <td>4,375.00</td>\n",
       "      <td>USD</td>\n",
       "      <td>4,375.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ExpressScripts_NYSE_ESRX</td>\n",
       "      <td>P</td>\n",
       "      <td>100.00</td>\n",
       "      <td>100.00</td>\n",
       "      <td>7,601.00</td>\n",
       "      <td>USD</td>\n",
       "      <td>7,601.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>TrinityIndustries_NYSE_TRN</td>\n",
       "      <td>P</td>\n",
       "      <td>34.00</td>\n",
       "      <td>34.00</td>\n",
       "      <td>1,895.93</td>\n",
       "      <td>USD</td>\n",
       "      <td>1,895.93</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Trex_NYSE_TREX</td>\n",
       "      <td>P</td>\n",
       "      <td>200.00</td>\n",
       "      <td>200.00</td>\n",
       "      <td>2,783.00</td>\n",
       "      <td>USD</td>\n",
       "      <td>2,783.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ACM_Research_Inc</td>\n",
       "      <td>P</td>\n",
       "      <td>100.00</td>\n",
       "      <td>100.00</td>\n",
       "      <td>200.00</td>\n",
       "      <td>USD</td>\n",
       "      <td>200.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Arcosa_NYSE_ACA</td>\n",
       "      <td>P</td>\n",
       "      <td>33.00</td>\n",
       "      <td>33.00</td>\n",
       "      <td>738.07</td>\n",
       "      <td>USD</td>\n",
       "      <td>738.07</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  properties.Instrument/default/Name.value.label_value holding_type  units  \\\n",
       "0                                  Apple_Nasdaq_AAPL              P 100.00   \n",
       "1                           ExpressScripts_NYSE_ESRX              P 100.00   \n",
       "2                         TrinityIndustries_NYSE_TRN              P  34.00   \n",
       "3                                     Trex_NYSE_TREX              P 200.00   \n",
       "4                                   ACM_Research_Inc              P 100.00   \n",
       "5                                    Arcosa_NYSE_ACA              P  33.00   \n",
       "\n",
       "   settled_units  cost.amount cost.currency  cost_portfolio_ccy.amount  \n",
       "0         100.00     4,375.00           USD                   4,375.00  \n",
       "1         100.00     7,601.00           USD                   7,601.00  \n",
       "2          34.00     1,895.93           USD                   1,895.93  \n",
       "3         200.00     2,783.00           USD                   2,783.00  \n",
       "4         100.00       200.00           USD                     200.00  \n",
       "5          33.00       738.07           USD                     738.07  "
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get_portfolio_holdings(\"01/11/2018\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4.4. Split"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "During a stock split, the company will multiply the amount of shares and divide the price by an equal amount. This causes them to lower the share price but keep the market cap of the company equal. In our example we have a 2 for 1 stock split, meaning shareholders get 2 shares for every share they own and their cost basis remains the same. In this scenario our input factor is 1 and our output factor is 2 while the cost factors remain 1 for both. As a result you get 2 times the output instrument for every 1 unit of input instrument."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "dataset = ca_data.iloc[3]\n",
    "\n",
    "transitions = [\n",
    "    lm.CorporateActionTransition(\n",
    "        input_transition=lm.CorporateActionTransitionComponentRequest(\n",
    "            instrument_identifiers={\n",
    "                \"Instrument/default/Figi\": dataset[\"input_instrument_figi\"]\n",
    "            },\n",
    "            units_factor=float(dataset[\"input_units_factor\"]),\n",
    "            cost_factor=float(dataset[\"input_cost_factor\"]),\n",
    "        ),\n",
    "        output_transitions=[\n",
    "            lm.CorporateActionTransitionComponentRequest(\n",
    "                instrument_identifiers={\n",
    "                    \"Instrument/default/Figi\": dataset[\"output_instrument_figi\"]\n",
    "                },\n",
    "                units_factor=float(dataset[\"output_units_factor\"]),\n",
    "                cost_factor=float(dataset[\"output_cost_factor\"]),\n",
    "            )\n",
    "        ],\n",
    "    )\n",
    "]\n",
    "\n",
    "split_request = lm.UpsertCorporateActionRequest(\n",
    "    corporate_action_code=dataset[\"code\"],\n",
    "    announcement_date=to_datetime(dataset[\"announcement_date\"]),\n",
    "    ex_date=to_datetime(dataset[\"ex_date\"]),\n",
    "    record_date=to_datetime(dataset[\"record_date\"]),\n",
    "    payment_date=to_datetime(dataset[\"payment_date\"]),\n",
    "    transitions=transitions,\n",
    ")\n",
    "\n",
    "result = corporate_action_sources_api.batch_upsert_corporate_actions(\n",
    "    scope=scope, code=ca_code, upsert_corporate_action_request=[split_request]\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "On the announcement date of this split (7th of May), we can see that we have 100 shares in Trex. The split will be 2 for 1 so we expect to have 200 shares on the Ex Date (21st of May)."
   ]
  },
  {
   "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>properties.Instrument/default/Name.value.label_value</th>\n",
       "      <th>holding_type</th>\n",
       "      <th>units</th>\n",
       "      <th>settled_units</th>\n",
       "      <th>cost.amount</th>\n",
       "      <th>cost.currency</th>\n",
       "      <th>cost_portfolio_ccy.amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Apple_Nasdaq_AAPL</td>\n",
       "      <td>P</td>\n",
       "      <td>100.00</td>\n",
       "      <td>100.00</td>\n",
       "      <td>4,375.00</td>\n",
       "      <td>USD</td>\n",
       "      <td>4,375.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ExpressScripts_NYSE_ESRX</td>\n",
       "      <td>P</td>\n",
       "      <td>100.00</td>\n",
       "      <td>100.00</td>\n",
       "      <td>7,601.00</td>\n",
       "      <td>USD</td>\n",
       "      <td>7,601.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>TrinityIndustries_NYSE_TRN</td>\n",
       "      <td>P</td>\n",
       "      <td>100.00</td>\n",
       "      <td>100.00</td>\n",
       "      <td>2,634.00</td>\n",
       "      <td>USD</td>\n",
       "      <td>2,634.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Trex_NYSE_TREX</td>\n",
       "      <td>P</td>\n",
       "      <td>100.00</td>\n",
       "      <td>100.00</td>\n",
       "      <td>2,783.00</td>\n",
       "      <td>USD</td>\n",
       "      <td>2,783.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ACM_Research_Inc</td>\n",
       "      <td>P</td>\n",
       "      <td>100.00</td>\n",
       "      <td>100.00</td>\n",
       "      <td>200.00</td>\n",
       "      <td>USD</td>\n",
       "      <td>200.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  properties.Instrument/default/Name.value.label_value holding_type  units  \\\n",
       "0                                  Apple_Nasdaq_AAPL              P 100.00   \n",
       "1                           ExpressScripts_NYSE_ESRX              P 100.00   \n",
       "2                         TrinityIndustries_NYSE_TRN              P 100.00   \n",
       "3                                     Trex_NYSE_TREX              P 100.00   \n",
       "4                                   ACM_Research_Inc              P 100.00   \n",
       "\n",
       "   settled_units  cost.amount cost.currency  cost_portfolio_ccy.amount  \n",
       "0         100.00     4,375.00           USD                   4,375.00  \n",
       "1         100.00     7,601.00           USD                   7,601.00  \n",
       "2         100.00     2,634.00           USD                   2,634.00  \n",
       "3         100.00     2,783.00           USD                   2,783.00  \n",
       "4         100.00       200.00           USD                     200.00  "
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get_portfolio_holdings(\"07/05/2018\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "On the Ex Date, we can see that we have 200 units in Trex as we expected and our cost remains unaffected."
   ]
  },
  {
   "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>properties.Instrument/default/Name.value.label_value</th>\n",
       "      <th>holding_type</th>\n",
       "      <th>units</th>\n",
       "      <th>settled_units</th>\n",
       "      <th>cost.amount</th>\n",
       "      <th>cost.currency</th>\n",
       "      <th>cost_portfolio_ccy.amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Apple_Nasdaq_AAPL</td>\n",
       "      <td>P</td>\n",
       "      <td>100.00</td>\n",
       "      <td>100.00</td>\n",
       "      <td>4,375.00</td>\n",
       "      <td>USD</td>\n",
       "      <td>4,375.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ExpressScripts_NYSE_ESRX</td>\n",
       "      <td>P</td>\n",
       "      <td>100.00</td>\n",
       "      <td>100.00</td>\n",
       "      <td>7,601.00</td>\n",
       "      <td>USD</td>\n",
       "      <td>7,601.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>TrinityIndustries_NYSE_TRN</td>\n",
       "      <td>P</td>\n",
       "      <td>100.00</td>\n",
       "      <td>100.00</td>\n",
       "      <td>2,634.00</td>\n",
       "      <td>USD</td>\n",
       "      <td>2,634.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Trex_NYSE_TREX</td>\n",
       "      <td>P</td>\n",
       "      <td>200.00</td>\n",
       "      <td>100.00</td>\n",
       "      <td>2,783.00</td>\n",
       "      <td>USD</td>\n",
       "      <td>2,783.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ACM_Research_Inc</td>\n",
       "      <td>P</td>\n",
       "      <td>100.00</td>\n",
       "      <td>100.00</td>\n",
       "      <td>200.00</td>\n",
       "      <td>USD</td>\n",
       "      <td>200.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  properties.Instrument/default/Name.value.label_value holding_type  units  \\\n",
       "0                                  Apple_Nasdaq_AAPL              P 100.00   \n",
       "1                           ExpressScripts_NYSE_ESRX              P 100.00   \n",
       "2                         TrinityIndustries_NYSE_TRN              P 100.00   \n",
       "3                                     Trex_NYSE_TREX              P 200.00   \n",
       "4                                   ACM_Research_Inc              P 100.00   \n",
       "\n",
       "   settled_units  cost.amount cost.currency  cost_portfolio_ccy.amount  \n",
       "0         100.00     4,375.00           USD                   4,375.00  \n",
       "1         100.00     7,601.00           USD                   7,601.00  \n",
       "2         100.00     2,634.00           USD                   2,634.00  \n",
       "3         100.00     2,783.00           USD                   2,783.00  \n",
       "4         100.00       200.00           USD                     200.00  "
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get_portfolio_holdings(\"21/05/2018\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 4.4.1 Running valuations before and after splits\n",
    "\n",
    "As part of the stock split, we also need to adjust the quotes in LUSID in proportion to the split. For example, after a 2 for 1 split, we will hold twice as many shares, but each is worth half the previous amount. Therefore we need a new price to account for this change. In the example below, we run two valuations using <b>Trex_NYSE_TREX</b> as an example:\n",
    "\n",
    "* The first valuation occurs just before midnight on the 20th of May. At this point, the stock has not been split. We are using a price of USD 53.45 from the `Quotes` store.\n",
    "* The stock is then spit in LUSID at midnight or 00:00:00 on 21st of May (this is the ExDate in LUSID)\n",
    "* We then run another valuation at 8AM the next day (i.e. after the split), and we can see that the price has been adjusted to USD 26.73\n",
    "\n",
    "The key message here is that while the units increase from 100 to 200 (due to the 2 for 1 split), the PV of the position remains unchanged. If we did not adjust the price, then the PV would be incorrect. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To illustrate, we run a valuation just before the Trex stock is split in the IBOR. Take note of the PV and Units."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load some quotes to value the portfolio pre-split\n",
    "\n",
    "load_eod_prices(scope)"
   ]
  },
  {
   "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>Instrument/default/Name</th>\n",
       "      <th>Proportion(Valuation/PvInReportCcy)</th>\n",
       "      <th>Quotes/Price</th>\n",
       "      <th>Sum(Valuation/PvInReportCcy)</th>\n",
       "      <th>Sum(Holding/default/Units)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Apple_Nasdaq_AAPL</td>\n",
       "      <td>0.36</td>\n",
       "      <td>131.33</td>\n",
       "      <td>13,133.00</td>\n",
       "      <td>100.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ExpressScripts_NYSE_ESRX</td>\n",
       "      <td>0.31</td>\n",
       "      <td>112.34</td>\n",
       "      <td>11,234.00</td>\n",
       "      <td>100.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>TrinityIndustries_NYSE_TRN</td>\n",
       "      <td>0.06</td>\n",
       "      <td>23.44</td>\n",
       "      <td>2,344.00</td>\n",
       "      <td>100.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Trex_NYSE_TREX</td>\n",
       "      <td>0.15</td>\n",
       "      <td>53.45</td>\n",
       "      <td>5,345.00</td>\n",
       "      <td>100.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ACM_Research_Inc</td>\n",
       "      <td>0.12</td>\n",
       "      <td>44.66</td>\n",
       "      <td>4,466.00</td>\n",
       "      <td>100.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      Instrument/default/Name  Proportion(Valuation/PvInReportCcy)  \\\n",
       "0           Apple_Nasdaq_AAPL                                 0.36   \n",
       "1    ExpressScripts_NYSE_ESRX                                 0.31   \n",
       "2  TrinityIndustries_NYSE_TRN                                 0.06   \n",
       "3              Trex_NYSE_TREX                                 0.15   \n",
       "4            ACM_Research_Inc                                 0.12   \n",
       "\n",
       "   Quotes/Price  Sum(Valuation/PvInReportCcy)  Sum(Holding/default/Units)  \n",
       "0        131.33                     13,133.00                      100.00  \n",
       "1        112.34                     11,234.00                      100.00  \n",
       "2         23.44                      2,344.00                      100.00  \n",
       "3         53.45                      5,345.00                      100.00  \n",
       "4         44.66                      4,466.00                      100.00  "
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "run_portfolio_valuation(portfolio_code, \"2018-05-20T23:59:59Z\", scope)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Then we run a valuation immediately after the stock split. We can see that the units have changed but the PV remains the same."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Run a \"post corporate action\" process to automatically adjust prices for instruments which have undergone corporate actions.\n",
    "# These updated prices are loaded into the quotes store with a 4am timestamp.  This distinguishs them from the official \"eod\" prices loaded in the previous section\n",
    "# (For the purposes of this example the post-split price of 26.725 has been calculated externally.)\n",
    "\n",
    "load_corporate_action_split_price(\"BBG000BTGM43\", 26.725, \"2018-05-21T04:00:00Z\", scope)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "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>Instrument/default/Name</th>\n",
       "      <th>Proportion(Valuation/PvInReportCcy)</th>\n",
       "      <th>Quotes/Price</th>\n",
       "      <th>Sum(Valuation/PvInReportCcy)</th>\n",
       "      <th>Sum(Holding/default/Units)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Apple_Nasdaq_AAPL</td>\n",
       "      <td>0.36</td>\n",
       "      <td>131.33</td>\n",
       "      <td>13,133.00</td>\n",
       "      <td>100.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ExpressScripts_NYSE_ESRX</td>\n",
       "      <td>0.31</td>\n",
       "      <td>112.34</td>\n",
       "      <td>11,234.00</td>\n",
       "      <td>100.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>TrinityIndustries_NYSE_TRN</td>\n",
       "      <td>0.06</td>\n",
       "      <td>23.44</td>\n",
       "      <td>2,344.00</td>\n",
       "      <td>100.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Trex_NYSE_TREX</td>\n",
       "      <td>0.15</td>\n",
       "      <td>26.73</td>\n",
       "      <td>5,345.00</td>\n",
       "      <td>200.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ACM_Research_Inc</td>\n",
       "      <td>0.12</td>\n",
       "      <td>44.66</td>\n",
       "      <td>4,466.00</td>\n",
       "      <td>100.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      Instrument/default/Name  Proportion(Valuation/PvInReportCcy)  \\\n",
       "0           Apple_Nasdaq_AAPL                                 0.36   \n",
       "1    ExpressScripts_NYSE_ESRX                                 0.31   \n",
       "2  TrinityIndustries_NYSE_TRN                                 0.06   \n",
       "3              Trex_NYSE_TREX                                 0.15   \n",
       "4            ACM_Research_Inc                                 0.12   \n",
       "\n",
       "   Quotes/Price  Sum(Valuation/PvInReportCcy)  Sum(Holding/default/Units)  \n",
       "0        131.33                     13,133.00                      100.00  \n",
       "1        112.34                     11,234.00                      100.00  \n",
       "2         23.44                      2,344.00                      100.00  \n",
       "3         26.73                      5,345.00                      200.00  \n",
       "4         44.66                      4,466.00                      100.00  "
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "run_portfolio_valuation(portfolio_code, \"2018-05-21T08:00:00Z\", scope)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4.5. Bonus Issue"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A bonus issue is essentially a stock dividend, the company will reward shareholders by handing out extra shares to current shareholders. In our example the company ACM Research will reward each shareholder with an additional share. This results in multiplying their holdings by 2. We thus have an input factor of 1 and an output factor of 2 while keeping the cost equal. This means that for every 1 share, we receive 2 shares. This will double the position and not affect the cost basis as our output cost factor is 1."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "dataset = ca_data.iloc[4]\n",
    "\n",
    "transitions = [\n",
    "    lm.CorporateActionTransition(\n",
    "        input_transition=lm.CorporateActionTransitionComponentRequest(\n",
    "            instrument_identifiers={\n",
    "                \"Instrument/default/Figi\": dataset[\"input_instrument_figi\"]\n",
    "            },\n",
    "            units_factor=float(dataset[\"input_units_factor\"]),\n",
    "            cost_factor=float(dataset[\"input_cost_factor\"]),\n",
    "        ),\n",
    "        output_transitions=[\n",
    "            lm.CorporateActionTransitionComponentRequest(\n",
    "                instrument_identifiers={\n",
    "                    \"Instrument/default/Figi\": dataset[\"output_instrument_figi\"]\n",
    "                },\n",
    "                units_factor=float(dataset[\"output_units_factor\"]),\n",
    "                cost_factor=float(dataset[\"output_cost_factor\"]),\n",
    "            )\n",
    "        ],\n",
    "    )\n",
    "]\n",
    "\n",
    "bonus_issue_request = lm.UpsertCorporateActionRequest(\n",
    "    corporate_action_code=dataset[\"code\"],\n",
    "    announcement_date=to_datetime(dataset[\"announcement_date\"]),\n",
    "    ex_date=to_datetime(dataset[\"ex_date\"]),\n",
    "    record_date=to_datetime(dataset[\"record_date\"]),\n",
    "    payment_date=to_datetime(dataset[\"payment_date\"]),\n",
    "    transitions=transitions,\n",
    ")\n",
    "\n",
    "result = corporate_action_sources_api.batch_upsert_corporate_actions(\n",
    "    scope=scope, code=ca_code, upsert_corporate_action_request=[bonus_issue_request]\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "During a bonus issue, a company will provide shareholders with extra shares in the company. On the announcement date we can see that we hold 100 shares in ACM Research."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "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>properties.Instrument/default/Name.value.label_value</th>\n",
       "      <th>holding_type</th>\n",
       "      <th>units</th>\n",
       "      <th>settled_units</th>\n",
       "      <th>cost.amount</th>\n",
       "      <th>cost.currency</th>\n",
       "      <th>cost_portfolio_ccy.amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Apple_Nasdaq_AAPL</td>\n",
       "      <td>P</td>\n",
       "      <td>100.00</td>\n",
       "      <td>100.00</td>\n",
       "      <td>4,375.00</td>\n",
       "      <td>USD</td>\n",
       "      <td>4,375.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ExpressScripts_NYSE_ESRX</td>\n",
       "      <td>P</td>\n",
       "      <td>100.00</td>\n",
       "      <td>100.00</td>\n",
       "      <td>7,601.00</td>\n",
       "      <td>USD</td>\n",
       "      <td>7,601.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>TrinityIndustries_NYSE_TRN</td>\n",
       "      <td>P</td>\n",
       "      <td>34.00</td>\n",
       "      <td>34.00</td>\n",
       "      <td>1,895.93</td>\n",
       "      <td>USD</td>\n",
       "      <td>1,895.93</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Trex_NYSE_TREX</td>\n",
       "      <td>P</td>\n",
       "      <td>200.00</td>\n",
       "      <td>200.00</td>\n",
       "      <td>2,783.00</td>\n",
       "      <td>USD</td>\n",
       "      <td>2,783.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ACM_Research_Inc</td>\n",
       "      <td>P</td>\n",
       "      <td>100.00</td>\n",
       "      <td>100.00</td>\n",
       "      <td>200.00</td>\n",
       "      <td>USD</td>\n",
       "      <td>200.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Arcosa_NYSE_ACA</td>\n",
       "      <td>P</td>\n",
       "      <td>33.00</td>\n",
       "      <td>33.00</td>\n",
       "      <td>738.07</td>\n",
       "      <td>USD</td>\n",
       "      <td>738.07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Cigna_NYSE_CI</td>\n",
       "      <td>P</td>\n",
       "      <td>24.00</td>\n",
       "      <td>24.00</td>\n",
       "      <td>7,601.00</td>\n",
       "      <td>USD</td>\n",
       "      <td>7,601.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>USD</td>\n",
       "      <td>B</td>\n",
       "      <td>17.00</td>\n",
       "      <td>17.00</td>\n",
       "      <td>17.00</td>\n",
       "      <td>USD</td>\n",
       "      <td>17.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  properties.Instrument/default/Name.value.label_value holding_type  units  \\\n",
       "0                                  Apple_Nasdaq_AAPL              P 100.00   \n",
       "1                           ExpressScripts_NYSE_ESRX              P 100.00   \n",
       "2                         TrinityIndustries_NYSE_TRN              P  34.00   \n",
       "3                                     Trex_NYSE_TREX              P 200.00   \n",
       "4                                   ACM_Research_Inc              P 100.00   \n",
       "5                                    Arcosa_NYSE_ACA              P  33.00   \n",
       "6                                      Cigna_NYSE_CI              P  24.00   \n",
       "7                                                USD              B  17.00   \n",
       "\n",
       "   settled_units  cost.amount cost.currency  cost_portfolio_ccy.amount  \n",
       "0         100.00     4,375.00           USD                   4,375.00  \n",
       "1         100.00     7,601.00           USD                   7,601.00  \n",
       "2          34.00     1,895.93           USD                   1,895.93  \n",
       "3         200.00     2,783.00           USD                   2,783.00  \n",
       "4         100.00       200.00           USD                     200.00  \n",
       "5          33.00       738.07           USD                     738.07  \n",
       "6          24.00     7,601.00           USD                   7,601.00  \n",
       "7          17.00        17.00           USD                      17.00  "
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get_portfolio_holdings(\"03/03/2022\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "On the payment date, we can see that we hold 200 shares, we have received 100 shares. This is since the output factor was 2 in our data. Also note that we did not have to pay for these shares, so our cost amount remains unchanged."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "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>properties.Instrument/default/Name.value.label_value</th>\n",
       "      <th>holding_type</th>\n",
       "      <th>units</th>\n",
       "      <th>settled_units</th>\n",
       "      <th>cost.amount</th>\n",
       "      <th>cost.currency</th>\n",
       "      <th>cost_portfolio_ccy.amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Apple_Nasdaq_AAPL</td>\n",
       "      <td>P</td>\n",
       "      <td>100.00</td>\n",
       "      <td>100.00</td>\n",
       "      <td>4,375.00</td>\n",
       "      <td>USD</td>\n",
       "      <td>4,375.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ExpressScripts_NYSE_ESRX</td>\n",
       "      <td>P</td>\n",
       "      <td>100.00</td>\n",
       "      <td>100.00</td>\n",
       "      <td>7,601.00</td>\n",
       "      <td>USD</td>\n",
       "      <td>7,601.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>TrinityIndustries_NYSE_TRN</td>\n",
       "      <td>P</td>\n",
       "      <td>34.00</td>\n",
       "      <td>34.00</td>\n",
       "      <td>1,895.93</td>\n",
       "      <td>USD</td>\n",
       "      <td>1,895.93</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Trex_NYSE_TREX</td>\n",
       "      <td>P</td>\n",
       "      <td>200.00</td>\n",
       "      <td>200.00</td>\n",
       "      <td>2,783.00</td>\n",
       "      <td>USD</td>\n",
       "      <td>2,783.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ACM_Research_Inc</td>\n",
       "      <td>P</td>\n",
       "      <td>200.00</td>\n",
       "      <td>200.00</td>\n",
       "      <td>200.00</td>\n",
       "      <td>USD</td>\n",
       "      <td>200.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Arcosa_NYSE_ACA</td>\n",
       "      <td>P</td>\n",
       "      <td>33.00</td>\n",
       "      <td>33.00</td>\n",
       "      <td>738.07</td>\n",
       "      <td>USD</td>\n",
       "      <td>738.07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Cigna_NYSE_CI</td>\n",
       "      <td>P</td>\n",
       "      <td>24.00</td>\n",
       "      <td>24.00</td>\n",
       "      <td>7,601.00</td>\n",
       "      <td>USD</td>\n",
       "      <td>7,601.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>USD</td>\n",
       "      <td>B</td>\n",
       "      <td>17.00</td>\n",
       "      <td>17.00</td>\n",
       "      <td>17.00</td>\n",
       "      <td>USD</td>\n",
       "      <td>17.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  properties.Instrument/default/Name.value.label_value holding_type  units  \\\n",
       "0                                  Apple_Nasdaq_AAPL              P 100.00   \n",
       "1                           ExpressScripts_NYSE_ESRX              P 100.00   \n",
       "2                         TrinityIndustries_NYSE_TRN              P  34.00   \n",
       "3                                     Trex_NYSE_TREX              P 200.00   \n",
       "4                                   ACM_Research_Inc              P 200.00   \n",
       "5                                    Arcosa_NYSE_ACA              P  33.00   \n",
       "6                                      Cigna_NYSE_CI              P  24.00   \n",
       "7                                                USD              B  17.00   \n",
       "\n",
       "   settled_units  cost.amount cost.currency  cost_portfolio_ccy.amount  \n",
       "0         100.00     4,375.00           USD                   4,375.00  \n",
       "1         100.00     7,601.00           USD                   7,601.00  \n",
       "2          34.00     1,895.93           USD                   1,895.93  \n",
       "3         200.00     2,783.00           USD                   2,783.00  \n",
       "4         200.00       200.00           USD                     200.00  \n",
       "5          33.00       738.07           USD                     738.07  \n",
       "6          24.00     7,601.00           USD                   7,601.00  \n",
       "7          17.00        17.00           USD                      17.00  "
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get_portfolio_holdings(\"23/03/2022\")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.10.6 ('.env': venv)",
   "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.10.6"
  },
  "vscode": {
   "interpreter": {
    "hash": "2631b36fd78b5e9ba2bda367fd8deaf1b7c0a55746e49610a3543f723e389a55"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}