examples/use-cases/instruments/Simple Equity.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 - Computing P&L and Handling Dividends for Equities\n",
    "\n",
    "Attributes\n",
    "----------\n",
    "equity\n",
    "dividend\n",
    "recipes\n",
    "valuations\n",
    "P&L\n",
    "\"\"\"\n",
    "\n",
    "toggle_code(\"Toggle Docstring\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Computing P&L and Handling Dividends for Equities\n",
    "\n",
    "In this notebook, we demonstrate how P&L can be calculated for Equity instruments as well as how to book in dividends to our cash position. \n",
    "In this example, we will be using Microsoft shares.\n",
    "\n",
    "## Table of Contents:\n",
    "* [1. Create a portfolio](#1.-Create-Portfolio)\n",
    "* [2. Creating an equity instrument](#2.-Create-an-Equity-Instrument)\n",
    "* [3. Transactions](#3.-Transactions)\n",
    "* [4. Quotes](#4.-Quotes)\n",
    "* [5. Creating a corporate action](#5.-Create-a-Corporate-Action)\n",
    "* [6. Valuations](#6.-Valuations)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "LUSID Environment Initialised\n",
      "LUSID API Version : 0.6.8497.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",
    "import time\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 lusidtools.pandas_utils.lusid_pandas import lusid_response_to_data_frame\n",
    "from lusidtools.cocoon.transaction_type_upload import upsert_transaction_type_alias\n",
    "from lusidtools.lpt.lpt import to_date\n",
    "from lusidjam import RefreshingToken\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",
    "from utilities.formatting_tools import cashladder_to_df\n",
    "\n",
    "# display(HTML(\"<style>.container { width:90% !important; }</style>\"))\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": "code",
   "execution_count": 36,
   "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",
    "quotes_api = api_factory.build(lu.api.QuotesApi)\n",
    "configuration_recipe_api = api_factory.build(lu.api.ConfigurationRecipeApi)\n",
    "system_configuration_api = api_factory.build(lu.api.SystemConfigurationApi)\n",
    "aggregration_api = api_factory.build(lu.api.AggregationApi)\n",
    "corporate_action_sources_api = api_factory.build(lu.api.CorporateActionSourcesApi)\n",
    "property_definitions_api = api_factory.build(lu.api.PropertyDefinitionsApi)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Define scopes\n",
    "scope = \"ibor\"\n",
    "quotes_scope = \"ibor\"\n",
    "ca_code = \"ibor_corp_act\"\n",
    "portfolio_code = \"EquityPortfolioForPnLCalc\"\n",
    "recipe_code = \"equityValuation\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 1. Create a Portfolio\n",
    "\n",
    "We must first create a corporate action source to link to the portfolio we plan to keep our equity in. This will allow us to perform a valuation and inspect the cash flows at a later stage."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The portfolio already exists\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",
    "    source_result = api_factory.build(\n",
    "        lu.api.CorporateActionSourcesApi\n",
    "    ).create_corporate_action_source(\n",
    "        create_corporate_action_source_request=source_request\n",
    "    )\n",
    "\n",
    "except lu.ApiException as e:\n",
    "        detail = json.loads(e.body)\n",
    "        if detail['code'] ==173:\n",
    "            print('The portfolio already exists')\n",
    "        else:\n",
    "             raise e"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Could not create a portfolio with id 'EquityPortfolioForPnLCalc' 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": [
    "# 2. Creating an Equity Instrument\n",
    "\n",
    "We must first create a property definition for the dividend yield property we are planning on using later in this notebook."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Error creating Property Definition 'Instrument/ibor/dividend_yield' because it already exists.\n"
     ]
    }
   ],
   "source": [
    "try:\n",
    "    property_definition_request = lm.CreatePropertyDefinitionRequest(\n",
    "        domain=\"Instrument\",\n",
    "        scope=scope,\n",
    "        code=\"dividend_yield\",\n",
    "        display_name=\"Dividend Yield\",\n",
    "        data_type_id=lm.ResourceId(\n",
    "            scope=\"system\",\n",
    "            code=\"number\",\n",
    "        ),\n",
    "        life_time=\"Perpetual\",\n",
    "    )\n",
    "\n",
    "    property_definitions_api.create_property_definition(\n",
    "        create_property_definition_request=property_definition_request\n",
    "    )\n",
    "\n",
    "except lu.ApiException as e:\n",
    "    print(json.loads(e.body)[\"title\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We create a function that takes in our equity variables and upserts an equity to LUSID."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "def create_equity(\n",
    "    name,\n",
    "    client_internal,\n",
    "    dom_ccy,\n",
    "    dividend_yield,\n",
    "):\n",
    "\n",
    "    equity = lm.SimpleInstrument(\n",
    "        instrument_type=\"SimpleInstrument\",\n",
    "        dom_ccy=dom_ccy,\n",
    "        asset_class=\"Equities\",\n",
    "        simple_instrument_type=\"Equity\",\n",
    "    )\n",
    "\n",
    "    # properties = lm.InstrumentProperties()\n",
    "    properties = lm.ModelProperty(\n",
    "        key=f\"Instrument/{scope}/dividend_yield\",\n",
    "        value=lm.PropertyValue(\n",
    "            metric_value=lm.MetricValue(\n",
    "                value=dividend_yield,\n",
    "                # unit=\"Decimal\",\n",
    "            )\n",
    "        ),\n",
    "    )\n",
    "\n",
    "    equity_definition = lm.InstrumentDefinition(\n",
    "        name=name,\n",
    "        identifiers={\"ClientInternal\": lm.InstrumentIdValue(client_internal)},\n",
    "        definition=equity,\n",
    "        properties=[properties],\n",
    "    )\n",
    "\n",
    "    # upsert the instrument\n",
    "    upsert_request = {client_internal: equity_definition}\n",
    "    upsert_response = instruments_api.upsert_instruments(request_body=upsert_request)\n",
    "    equity_luid = upsert_response.values[client_internal].lusid_instrument_id\n",
    "    print(equity_luid)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.1 Upsert an Equity\n",
    "\n",
    "We can now set the variables of the equity and upsert it into LUSID using the function we built in the cell above."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "LUID_FP69LVAZ\n"
     ]
    }
   ],
   "source": [
    "name = \"Microsoft\"\n",
    "identifier = \"MSFT\"\n",
    "dom_ccy = \"USD\"\n",
    "dividend_yield = 0.88\n",
    "\n",
    "create_equity(name, identifier, dom_ccy, dividend_yield)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 3. Transactions"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3.1 Create Transaction Request\n",
    "\n",
    "Once the equity has been created, we can generate a transaction that adds it to our portfolio."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "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>txn_id</th>\n",
       "      <th>type</th>\n",
       "      <th>Isin</th>\n",
       "      <th>client_id</th>\n",
       "      <th>trade_date</th>\n",
       "      <th>settlement_date</th>\n",
       "      <th>quantity</th>\n",
       "      <th>price</th>\n",
       "      <th>total_consideration</th>\n",
       "      <th>currency</th>\n",
       "      <th>portfolio</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>txn001</td>\n",
       "      <td>StockIn</td>\n",
       "      <td>US5949181045</td>\n",
       "      <td>MSFT</td>\n",
       "      <td>2021-09-01T10:00:00Z</td>\n",
       "      <td>2021-09-02T10:00:00Z</td>\n",
       "      <td>1000</td>\n",
       "      <td>300</td>\n",
       "      <td>300000</td>\n",
       "      <td>USD</td>\n",
       "      <td>EquityPortfolioForPnLCalc</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   txn_id     type          Isin client_id            trade_date  \\\n",
       "0  txn001  StockIn  US5949181045      MSFT  2021-09-01T10:00:00Z   \n",
       "\n",
       "        settlement_date  quantity  price  total_consideration currency  \\\n",
       "0  2021-09-02T10:00:00Z      1000    300               300000      USD   \n",
       "\n",
       "                   portfolio  \n",
       "0  EquityPortfolioForPnLCalc  "
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "transactions = pd.read_csv(\"data/equity_transaction_data.csv\")\n",
    "transactions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Transactions succesfully updated at time: 2021-11-25 17:54:21.763810+00:00\n"
     ]
    }
   ],
   "source": [
    "for portfolio_code, grouped_df in transactions.groupby(\"portfolio\"):\n",
    "\n",
    "    transaction_request = [\n",
    "        lm.TransactionRequest(\n",
    "            transaction_id=row[\"txn_id\"],\n",
    "            type=row[\"type\"],\n",
    "            instrument_identifiers={\n",
    "                \"Instrument/default/ClientInternal\": row[\"client_id\"],\n",
    "                \"Instrument/default/Isin\": row[\"Isin\"],\n",
    "            },\n",
    "            transaction_date=row[\"trade_date\"],\n",
    "            settlement_date=row[\"settlement_date\"],\n",
    "            units=row[\"quantity\"],\n",
    "            transaction_price=lm.TransactionPrice(price=row[\"price\"], type=\"Price\"),\n",
    "            total_consideration=lm.CurrencyAndAmount(\n",
    "                amount=row[\"total_consideration\"], currency=row[\"currency\"]\n",
    "            ),\n",
    "        )\n",
    "        for index, row in grouped_df.iterrows()\n",
    "    ]\n",
    "\n",
    "    response = transaction_portfolios_api.upsert_transactions(\n",
    "        scope=scope, code=portfolio_code, transaction_request=transaction_request\n",
    "    )\n",
    "\n",
    "    print(f\"Transactions succesfully updated at time: {response.version.as_at_date}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 4. Quotes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The equity is in our portfolio, but currently lacks pricing. We will thus upsert quotes for the equity that we retrieved from our market data system. We upload prices from when the equity was bought until today (at the time of writing, 12 November 2021). Below we can see the last 5 of the ~50 quotes that were uploaded."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "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>ClientInternal</th>\n",
       "      <th>date</th>\n",
       "      <th>price</th>\n",
       "      <th>currency</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>MSFT</td>\n",
       "      <td>2021-11-08</td>\n",
       "      <td>336.99</td>\n",
       "      <td>USD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>MSFT</td>\n",
       "      <td>2021-11-09</td>\n",
       "      <td>337.05</td>\n",
       "      <td>USD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>MSFT</td>\n",
       "      <td>2021-11-10</td>\n",
       "      <td>338.05</td>\n",
       "      <td>USD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>MSFT</td>\n",
       "      <td>2021-11-11</td>\n",
       "      <td>339.05</td>\n",
       "      <td>USD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51</th>\n",
       "      <td>MSFT</td>\n",
       "      <td>2021-11-12</td>\n",
       "      <td>340.05</td>\n",
       "      <td>USD</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   ClientInternal        date  price currency\n",
       "47           MSFT  2021-11-08 336.99      USD\n",
       "48           MSFT  2021-11-09 337.05      USD\n",
       "49           MSFT  2021-11-10 338.05      USD\n",
       "50           MSFT  2021-11-11 339.05      USD\n",
       "51           MSFT  2021-11-12 340.05      USD"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "equity_prices = pd.read_csv(\"data/equity_quotes_data.csv\")\n",
    "equity_prices.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Quote successfully loaded into LUSID. 52 quotes loaded.\n"
     ]
    }
   ],
   "source": [
    "# Create quotes request\n",
    "instrument_quotes = {\n",
    "    index: lm.UpsertQuoteRequest(\n",
    "        quote_id=lm.QuoteId(\n",
    "            quote_series_id=lm.QuoteSeriesId(\n",
    "                provider=\"Lusid\",\n",
    "                instrument_id=row[\"ClientInternal\"],\n",
    "                instrument_id_type=\"ClientInternal\",\n",
    "                quote_type=\"Price\",\n",
    "                field=\"mid\",\n",
    "            ),\n",
    "            effective_at=to_date(row[\"date\"]),\n",
    "        ),\n",
    "        metric_value=lm.MetricValue(value=row[\"price\"], unit=row[\"currency\"]),\n",
    "    )\n",
    "    for index, row in equity_prices.iterrows()\n",
    "}\n",
    "\n",
    "# Upsert the quotes into LUSID\n",
    "response = quotes_api.upsert_quotes(scope=scope, request_body=instrument_quotes)\n",
    "\n",
    "if response.failed == {}:\n",
    "    print(\n",
    "        f\"Quote successfully loaded into LUSID. {len(response.values)} quotes loaded.\"\n",
    "    )\n",
    "\n",
    "else:\n",
    "    print(\n",
    "        f\"Some failures occurred during quotes upsertion, {len(response.failed)} did not get loaded into LUSID.\"\n",
    "    )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 5. Creating a Corporate Action"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To create our dividend corporate action, we define an input and an output transition. Our input transition is the number of shares in Microsoft, denoted by the Client Internal ID \"MSFT\". The output transition is the amount of USD per share we will receive. The result is that for every 1 share, we receive 0.56 USD. You can structure all sorts of corporate actions in this way. For our example, we structured a divident payment. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'failed': {},\n",
       " 'href': None,\n",
       " 'links': [{'description': None,\n",
       "            'href': 'https://fbn-ci.lusid.com/api/api/schemas/entities/UpsertCorporateActionsResponse',\n",
       "            'method': 'GET',\n",
       "            'relation': 'EntitySchema'},\n",
       "           {'description': 'A link to the LUSID Insights website showing all '\n",
       "                           'logs related to this request',\n",
       "            'href': 'http://fbn-ci.lusid.com/app/insights/logs/0HMEV1VNMDS5S:00000026',\n",
       "            'method': 'GET',\n",
       "            'relation': 'RequestLogs'}],\n",
       " 'values': {'ibor_corp_act': {'announcement_date': datetime.datetime(2021, 9, 2, 0, 0, tzinfo=tzutc()),\n",
       "                              'corporate_action_code': 'ibor_corp_act',\n",
       "                              'description': None,\n",
       "                              'ex_date': datetime.datetime(2021, 9, 3, 0, 0, tzinfo=tzutc()),\n",
       "                              'payment_date': datetime.datetime(2021, 9, 9, 0, 0, tzinfo=tzutc()),\n",
       "                              'record_date': datetime.datetime(2021, 9, 4, 0, 0, tzinfo=tzutc()),\n",
       "                              'transitions': [{'input_transition': {'cost_factor': 1.0,\n",
       "                                                                    'instrument_identifiers': {'Instrument/default/ClientInternal': 'MSFT'},\n",
       "                                                                    'instrument_uid': 'LUID_FP69LVAZ',\n",
       "                                                                    'units_factor': 1.0},\n",
       "                                               'output_transitions': [{'cost_factor': 1.0,\n",
       "                                                                       'instrument_identifiers': {'Instrument/default/Currency': 'USD'},\n",
       "                                                                       'instrument_uid': 'CCY_USD',\n",
       "                                                                       'units_factor': 0.56}]}]}}}"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "transitions = [\n",
    "    lm.CorporateActionTransition(\n",
    "        input_transition=lm.CorporateActionTransitionComponentRequest(\n",
    "            instrument_identifiers={\n",
    "                \"Instrument/default/ClientInternal\": \"MSFT\"\n",
    "            },\n",
    "            units_factor=1,\n",
    "            cost_factor=1,\n",
    "        ),\n",
    "        output_transitions=[\n",
    "            lm.CorporateActionTransitionComponentRequest(\n",
    "                instrument_identifiers={\"Instrument/default/Currency\": \"USD\"},\n",
    "                units_factor=0.56,\n",
    "                cost_factor=1,\n",
    "            )\n",
    "        ],\n",
    "    )\n",
    "]\n",
    "\n",
    "dividend = lm.UpsertCorporateActionRequest(\n",
    "    corporate_action_code=ca_code,\n",
    "    announcement_date=datetime(2021, 9, 2, 0, tzinfo=pytz.utc),\n",
    "    ex_date=datetime(2021, 9, 3, 0, tzinfo=pytz.utc),\n",
    "    record_date=datetime(2021, 9, 4, 0, tzinfo=pytz.utc),\n",
    "    payment_date=datetime(2021, 9, 9, 0, tzinfo=pytz.utc),\n",
    "    transitions=transitions,\n",
    ")\n",
    "\n",
    "corporate_action_sources_api.batch_upsert_corporate_actions(\n",
    "    scope=scope, code=ca_code, upsert_corporate_action_request=[dividend]\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 6. Valuations\n",
    "\n",
    "Once we have the equity booked into a portfolio, we can now value this portfolio. The recipe below describes how we will go about valuing the instruments in our portfolio (in this case just our equity)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6.1 Create valuation recipe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a recipe to perform a valuation\n",
    "configuration_recipe = lm.ConfigurationRecipe(\n",
    "    scope=scope,\n",
    "    code=recipe_code,\n",
    "    market=lm.MarketContext(\n",
    "        market_rules=[\n",
    "            lm.MarketDataKeyRule(\n",
    "                key=\"Quote.ClientInternal.*\",\n",
    "                supplier=\"Lusid\",\n",
    "                data_scope=scope,\n",
    "                quote_type=\"Price\",\n",
    "                field=\"mid\",\n",
    "                quote_interval=\"5D.0D\",\n",
    "            )\n",
    "        ],\n",
    "        options=lm.MarketOptions(\n",
    "            default_supplier=\"Lusid\",\n",
    "            default_instrument_code_type=\"ClientInternal\",\n",
    "            default_scope=scope,\n",
    "        ),\n",
    "    ),\n",
    ")\n",
    "\n",
    "upsert_configuration_recipe_response = (\n",
    "    configuration_recipe_api.upsert_configuration_recipe(\n",
    "        upsert_recipe_request=lm.UpsertRecipeRequest(\n",
    "            configuration_recipe=configuration_recipe\n",
    "        )\n",
    "    )\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6.2 Create daily valuation function\n",
    "\n",
    "Once we have made a recipe, we can now create a function that outputs a dataframe with the valuation of our portfolio. We choose to display PV, PnL and some instrument identifiers."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_val(date, portfolio_code):\n",
    "\n",
    "    valuation_request = lm.ValuationRequest(\n",
    "        recipe_id=lm.ResourceId(scope=scope, code=\"equityValuation\"),\n",
    "        metrics=[\n",
    "            lm.AggregateSpec(\"Instrument/default/Name\", \"Value\"),\n",
    "            lm.AggregateSpec(\"Instrument/default/ClientInternal\", \"Value\"),\n",
    "            lm.AggregateSpec(\"Quotes/Price\", \"Value\"),\n",
    "            lm.AggregateSpec(\"Holding/default/Units\", \"Value\"),\n",
    "            lm.AggregateSpec(\"Valuation/PV/Amount\", \"Value\"),\n",
    "            lm.AggregateSpec(\"Valuation/PnL/Tm1\", \"Value\"),\n",
    "            lm.AggregateSpec(\"Instrument/ibor/dividend_yield\", \"Value\"),\n",
    "        ],\n",
    "        group_by=[\"Instrument/default/Name\"],\n",
    "        portfolio_entity_ids=[lm.PortfolioEntityId(scope=scope, code=portfolio_code)],\n",
    "        valuation_schedule=lm.ValuationSchedule(effective_at=date),\n",
    "    )\n",
    "\n",
    "    val_data = aggregration_api.get_valuation(valuation_request=valuation_request).data\n",
    "\n",
    "    vals_df = pd.DataFrame(val_data)\n",
    "\n",
    "    vals_df.rename(\n",
    "        columns={\n",
    "            \"Instrument/default/Name\": \"InstrumentName\",\n",
    "            \"Instrument/default/ClientInternal\": \"ClientInternal\",\n",
    "            \"Valuation/PV/Amount\": \"Present Value\",\n",
    "            \"Valuation/PnL/Tm1\": \"PnL (1-day)\",\n",
    "            \"Instrument/ibor/dividend_yield\": \"Dividend Yield\",\n",
    "        },\n",
    "        inplace=True,\n",
    "    )\n",
    "    try:\n",
    "        return vals_df.drop(\"Aggregation/Errors\", axis=1)\n",
    "    except:\n",
    "        return vals_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We will now display the first few days in the lifecycle of our equity. Recall that we have bought this equity on the 1st of September 2021."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Day 1\n",
    "\n",
    "At 10AM on day 1, we value our equity position and find that it is worth 300,000 USD."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "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>Present Value</th>\n",
       "      <th>InstrumentName</th>\n",
       "      <th>ClientInternal</th>\n",
       "      <th>Quotes/Price</th>\n",
       "      <th>Holding/default/Units</th>\n",
       "      <th>PnL (1-day)</th>\n",
       "      <th>Dividend Yield</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>300,000.00</td>\n",
       "      <td>Microsoft</td>\n",
       "      <td>MSFT</td>\n",
       "      <td>300.00</td>\n",
       "      <td>1,000.00</td>\n",
       "      <td>None</td>\n",
       "      <td>0.88</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Present Value InstrumentName ClientInternal  Quotes/Price  \\\n",
       "0     300,000.00      Microsoft           MSFT        300.00   \n",
       "\n",
       "   Holding/default/Units PnL (1-day)  Dividend Yield  \n",
       "0               1,000.00        None            0.88  "
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get_val(\"2021-09-01T10:00:00Z\", portfolio_code)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Day 2\n",
    "\n",
    "On day 2 the share price of MSFT moves to 301.15, giving us a 1 day PnL of 1,150 USD."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "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>Present Value</th>\n",
       "      <th>InstrumentName</th>\n",
       "      <th>ClientInternal</th>\n",
       "      <th>Quotes/Price</th>\n",
       "      <th>Holding/default/Units</th>\n",
       "      <th>PnL (1-day)</th>\n",
       "      <th>Dividend Yield</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>301,150.00</td>\n",
       "      <td>Microsoft</td>\n",
       "      <td>MSFT</td>\n",
       "      <td>301.15</td>\n",
       "      <td>1,000.00</td>\n",
       "      <td>1,150.00</td>\n",
       "      <td>0.88</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Present Value InstrumentName ClientInternal  Quotes/Price  \\\n",
       "0     301,150.00      Microsoft           MSFT        301.15   \n",
       "\n",
       "   Holding/default/Units  PnL (1-day)  Dividend Yield  \n",
       "0               1,000.00     1,150.00            0.88  "
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get_val(\"2021-09-02T10:00:00Z\", portfolio_code)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Cash Ladder\n",
    "\n",
    "If we take a look at the cash ladder between our first day of owning this equity, 1 September 2021, and today (12 November 2021 at time of writing) we can see all cash flows associated with this position so far."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "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>Currency</th>\n",
       "      <th>Date</th>\n",
       "      <th>Activity</th>\n",
       "      <th>Value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>USD</td>\n",
       "      <td>2021-09-09 00:00:00+00:00</td>\n",
       "      <td>Open</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>USD</td>\n",
       "      <td>2021-09-09 00:00:00+00:00</td>\n",
       "      <td>CorporateActionTransition</td>\n",
       "      <td>560.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>USD</td>\n",
       "      <td>2021-09-09 00:00:00+00:00</td>\n",
       "      <td>Close</td>\n",
       "      <td>560.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Currency                      Date                   Activity  Value\n",
       "0      USD 2021-09-09 00:00:00+00:00                       Open   0.00\n",
       "1      USD 2021-09-09 00:00:00+00:00  CorporateActionTransition 560.00\n",
       "2      USD 2021-09-09 00:00:00+00:00                      Close 560.00"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cash_ladder = transaction_portfolios_api.get_portfolio_cash_ladder(\n",
    "    scope=scope,\n",
    "    code=portfolio_code,\n",
    "    effective_at=\"2021-11-12T10:00:00Z\",\n",
    "    from_effective_at=\"2021-09-01T10:00:00Z\",\n",
    "    to_effective_at=\"2021-11-12T10:00:00Z\",\n",
    "    recipe_id_scope=scope,\n",
    "    recipe_id_code=recipe_code\n",
    ")\n",
    "\n",
    "cashladder_to_df(cash_ladder)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Dividend Date\n",
    "In the cash ladder, we can see that our dividend comes in on the 9th of September 2021.\n",
    "\n",
    "We run our valuation again for the dividend date and find that we have received a dividend payment of 0.56 USD per share. This results in an cash position of 560 USD."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "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>Present Value</th>\n",
       "      <th>InstrumentName</th>\n",
       "      <th>ClientInternal</th>\n",
       "      <th>Quotes/Price</th>\n",
       "      <th>Holding/default/Units</th>\n",
       "      <th>PnL (1-day)</th>\n",
       "      <th>Dividend Yield</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>297,250.00</td>\n",
       "      <td>Microsoft</td>\n",
       "      <td>MSFT</td>\n",
       "      <td>297.25</td>\n",
       "      <td>1,000.00</td>\n",
       "      <td>-2,960.00</td>\n",
       "      <td>0.88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>560.00</td>\n",
       "      <td>USD</td>\n",
       "      <td>None</td>\n",
       "      <td>nan</td>\n",
       "      <td>560.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>nan</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Present Value InstrumentName ClientInternal  Quotes/Price  \\\n",
       "0     297,250.00      Microsoft           MSFT        297.25   \n",
       "1         560.00            USD           None           nan   \n",
       "\n",
       "   Holding/default/Units  PnL (1-day)  Dividend Yield  \n",
       "0               1,000.00    -2,960.00            0.88  \n",
       "1                 560.00         0.00             nan  "
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get_val(\"2021-09-09T10:00:00Z\", portfolio_code)"
   ]
  }
 ],
 "metadata": {
  "interpreter": {
   "hash": "2db524e06e9f5f4ffedc911c917cb75e12dbc923643829bf417064a77eb14d37"
  },
  "kernelspec": {
   "display_name": "Python 3.8.0 64-bit",
   "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.8.0"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}