V1/features/instruments/Bond.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",
    "\"\"\"Bonds - Computing P&L and Accrued Interest for Bonds\n",
    "\n",
    "Attributes\n",
    "----------\n",
    "bond\n",
    "bonds\n",
    "recipes\n",
    "valuations\n",
    "accrued interest\n",
    "P&L\n",
    "\"\"\"\n",
    "\n",
    "toggle_code(\"Toggle Docstring\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Computing P&L and Accrued Interest for Bonds\n",
    "\n",
    "In this notebook, we demonstrate how P&L and accrued interests can be computed for Bond instruments as well as how to book in bond coupons to our cash position. \n",
    "In this example, we will be using a 30Y US Treasury bond.\n",
    "\n",
    "## Table of Contents:\n",
    "- 1. [Create transaction types in a custom scope](#1.-Create-Transaction-Types)\n",
    "- 2. [Create a portfolio](#2.-Create-Portfolio)\n",
    "- 3. [Creating a bond instrument](#3.-Create-Bond)\n",
    "- 4. [Transactions](#3.-Transactions)\n",
    "- 5. [Quotes](#5.-Quotes)\n",
    "- 6. [Valuation & PnL](#6.-Valuations)\n",
    "- 7. [Cash flows](#7.-Cash-Flows)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "LUSID Environment Initialised\n",
      "LUSID API Version : 0.6.11998.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",
    "from utilities.formatting_tools import cashladder_to_df\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.lpt.lpt import to_date\n",
    "from lusidjam import RefreshingToken\n",
    "from lusidtools.cocoon.utilities import generate_time_based_unique_id\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",
    "# 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": 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",
    "quotes_api = api_factory.build(lu.api.QuotesApi)\n",
    "configuration_recipe_api = api_factory.build(lu.api.ConfigurationRecipeApi)\n",
    "aggregration_api = api_factory.build(lu.api.AggregationApi)\n",
    "transaction_configuration_api = api_factory.build(lu.api.TransactionConfigurationApi)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Define scopes\n",
    "scope = \"ibor\"\n",
    "quotes_scope = \"ibor\"\n",
    "transaction_type_scope = \"bond_nbk_txn_type\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 1. Create Transaction Types\n",
    "\n",
    "While it is possible to use the default transaction type scope that contains a basic set of transaction types, we will use a custom transaction type scope and set the transaction types by calling LUSID endpoints. For more information, see https://support.lusid.com/knowledgebase/article/KA-02092/. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# First add default side definitions to the non-default transaction type scope.\n",
    "default_side_definitions = [\n",
    "    lm.SidesDefinitionRequest(\n",
    "        side=\"Side1\", \n",
    "        side_request=lm.SideDefinitionRequest(\n",
    "            security=\"Txn:LusidInstrumentId\",\n",
    "            currency=\"Txn:TradeCurrency\",\n",
    "            rate=\"Txn:TradeToPortfolioRate\",\n",
    "            units=\"Txn:Units\",\n",
    "            amount=\"Txn:TradeAmount\")),\n",
    "    lm.SidesDefinitionRequest(\n",
    "        side=\"Side2\", \n",
    "        side_request=lm.SideDefinitionRequest(\n",
    "            security=\"Txn:SettleCcy\",\n",
    "            currency=\"Txn:SettlementCurrency\",\n",
    "            rate=\"SettledToPortfolioRate\",\n",
    "            units=\"Txn:TotalConsideration\",\n",
    "            amount=\"Txn:TotalConsideration\"))\n",
    "]\n",
    "\n",
    "transaction_configuration_api.set_side_definitions(default_side_definitions, scope = transaction_type_scope)\n",
    "\n",
    "# Add transaction types by calling LUSID with data taken from a json file\n",
    "default_transaction_mapping=open('data/default_transaction_mapping.json').read()\n",
    "default_transaction_mapping = json.loads(default_transaction_mapping)\n",
    "\n",
    "# These functions will be used to map the json to LUSID models\n",
    "def map_properties(properties):\n",
    "    return {property[\"key\"]: lm.PerpetualProperty(property[\"key\"], lm.PropertyValue(property[\"value\"])) for property in properties}\n",
    "def map_alias(alias):\n",
    "    return lm.TransactionTypeAlias(alias[\"type\"], alias[\"description\"], alias[\"transactionClass\"], alias[\"transactionRoles\"])\n",
    "def map_movement(movement):\n",
    "    return lm.TransactionTypeMovement(movement[\"movementTypes\"], movement[\"side\"], movement[\"direction\"], map_properties(movement[\"properties\"]))\n",
    "def map_transaction_type_request(transaction_type_request):\n",
    "    return lm.TransactionTypeRequest(\n",
    "        [map_alias(alias) for alias in transaction_type_request[\"aliases\"]],\n",
    "        [map_movement(movement) for movement in transaction_type_request[\"movements\"]],\n",
    "        map_properties(transaction_type_request[\"properties\"]))\n",
    "\n",
    "for configuration in default_transaction_mapping:\n",
    "    # Create the LUSID models to be used in the request\n",
    "    transaction_type_requests = [map_transaction_type_request(transaction_type_request) for transaction_type_request in configuration[\"transactionTypeRequests\"]]\n",
    "    \n",
    "    # Call LUSID to set your configuration for our transaction types\n",
    "    transaction_configuration_api.set_transaction_type_source(\n",
    "        source=configuration[\"source\"],\n",
    "        transaction_type_request=transaction_type_requests,\n",
    "        scope=transaction_type_scope\n",
    "    )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 2. Create Portfolio\n",
    "\n",
    "We must first create a portfolio to keep our bond in, this will allow us to perform a valuation and inspect the cash flows at a later stage."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Using a LUSID utility function to create a random portfolio code\n",
    "portfolio_code = generate_time_based_unique_id(None)\n",
    "\n",
    "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",
    "            transaction_type_scope=transaction_type_scope\n",
    "        ),\n",
    "    )\n",
    "\n",
    "except lu.ApiException as e:\n",
    "    print(json.loads(e.body)[\"title\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 3. Create Bond\n",
    "\n",
    "We create a function that takes in our bond variables and upserts a bond to LUSID."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "def create_bond(\n",
    "    currency,\n",
    "    payment_frequency,\n",
    "    roll_convention,\n",
    "    day_count_convention,\n",
    "    payment_calendars,\n",
    "    reset_calendars,\n",
    "    settle_days,\n",
    "    reset_days,\n",
    "    start_date,\n",
    "    maturity_date,\n",
    "    dom_ccy,\n",
    "    principal,\n",
    "    coupon_rate,\n",
    "    bond_identifier,\n",
    "    bond_name,\n",
    "):\n",
    "\n",
    "    flow_conventions = lm.FlowConventions(\n",
    "        currency=currency,\n",
    "        payment_frequency=payment_frequency,\n",
    "        roll_convention=roll_convention,\n",
    "        day_count_convention=day_count_convention,\n",
    "        payment_calendars=payment_calendars,\n",
    "        reset_calendars=reset_calendars,\n",
    "        settle_days=settle_days,\n",
    "        reset_days=reset_days,\n",
    "    )\n",
    "\n",
    "    bond = lm.Bond(\n",
    "        start_date=start_date,\n",
    "        maturity_date=maturity_date,\n",
    "        dom_ccy=dom_ccy,\n",
    "        principal=principal,\n",
    "        coupon_rate=coupon_rate,\n",
    "        flow_conventions=flow_conventions,\n",
    "        identifiers={},\n",
    "        instrument_type=\"Bond\",\n",
    "        calculation_type=\"Standard\",\n",
    "    )\n",
    "\n",
    "    # define the instrument to be upserted\n",
    "    bond_definition = lm.InstrumentDefinition(\n",
    "        name=bond_name,\n",
    "        identifiers={\"ClientInternal\": lm.InstrumentIdValue(bond_identifier)},\n",
    "        definition=bond,\n",
    "    )\n",
    "\n",
    "    # upsert the instrument\n",
    "    upsert_request = {bond_identifier: bond_definition}\n",
    "    upsert_response = instruments_api.upsert_instruments(request_body=upsert_request)\n",
    "    bond_luid = upsert_response.values[bond_identifier].lusid_instrument_id\n",
    "    print(bond_luid)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 3.1 Upsert a bond\n",
    "\n",
    "We can now set the variables of the bond and upsert it into LUSID using the function we built in the cell above."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "LUID_0000BYLX\n"
     ]
    }
   ],
   "source": [
    "currency = \"USD\"\n",
    "payment_frequency = \"6M\"\n",
    "roll_convention = \"none\"\n",
    "day_count_convention = \"ActualActual\"\n",
    "payment_calendars = []\n",
    "reset_calendars = []\n",
    "settle_days = 0\n",
    "reset_days = 0\n",
    "start_date = datetime(2019, 2, 15, 00, tzinfo=pytz.utc)\n",
    "maturity_date = datetime(2049, 2, 15, 00, tzinfo=pytz.utc)\n",
    "dom_ccy = \"USD\"\n",
    "principal = 1\n",
    "coupon_rate = 0.03\n",
    "bond_identifier = \"T_3_02/15/49\"\n",
    "bond_name = \"T 3 02/15/49\"\n",
    "\n",
    "create_bond(\n",
    "    currency,\n",
    "    payment_frequency,\n",
    "    roll_convention,\n",
    "    day_count_convention,\n",
    "    payment_calendars,\n",
    "    reset_calendars,\n",
    "    settle_days,\n",
    "    reset_days,\n",
    "    start_date,\n",
    "    maturity_date,\n",
    "    dom_ccy,\n",
    "    principal,\n",
    "    coupon_rate,\n",
    "    bond_identifier,\n",
    "    bond_name,\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 4. Transactions"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4.1 Create Transaction Request\n",
    "\n",
    "Once the bond has been created, we can generate a transaction that adds it to our portfolio."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>txn001</td>\n",
       "      <td>StockIn</td>\n",
       "      <td>T_3_02/15/49</td>\n",
       "      <td>2019-04-19T10:00:00Z</td>\n",
       "      <td>2019-04-20T10:00:00Z</td>\n",
       "      <td>1000000</td>\n",
       "      <td>100.76</td>\n",
       "      <td>1,007,578.13</td>\n",
       "      <td>USD</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   txn_id     type     client_id            trade_date       settlement_date  \\\n",
       "0  txn001  StockIn  T_3_02/15/49  2019-04-19T10:00:00Z  2019-04-20T10:00:00Z   \n",
       "\n",
       "   quantity  price  total_consideration currency  \n",
       "0   1000000 100.76         1,007,578.13      USD  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "transactions = pd.read_csv(\"data/bond_transaction_data.csv\")\n",
    "transactions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Transactions succesfully updated at time: 2023-10-03 08:45:42.663611+00:00\n"
     ]
    }
   ],
   "source": [
    "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",
    "        },\n",
    "        transaction_date=to_date(row[\"trade_date\"]).isoformat(),\n",
    "        settlement_date=to_date(row[\"settlement_date\"]).isoformat(),\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 transactions.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": [
    "# 5. Quotes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The bond is in our portfolio, but currently lacks pricing. We will thus upsert quotes for the bond that we retrieved from our market data system. We upload prices from when the bond was created until today (at the time of writing, 29 October 2021). Below we can see the last 5 of the ~ 700 quotes that were uploaded."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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>client_internal</th>\n",
       "      <th>date</th>\n",
       "      <th>price</th>\n",
       "      <th>currency</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>T_3_02/15/49</td>\n",
       "      <td>22/04/2019</td>\n",
       "      <td>100.15</td>\n",
       "      <td>USD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>T_3_02/15/49</td>\n",
       "      <td>23/04/2019</td>\n",
       "      <td>100.38</td>\n",
       "      <td>USD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>T_3_02/15/49</td>\n",
       "      <td>14/08/2019</td>\n",
       "      <td>121.92</td>\n",
       "      <td>USD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>T_3_02/15/49</td>\n",
       "      <td>15/08/2019</td>\n",
       "      <td>123.05</td>\n",
       "      <td>USD</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  client_internal        date  price currency\n",
       "0    T_3_02/15/49  22/04/2019 100.15      USD\n",
       "1    T_3_02/15/49  23/04/2019 100.38      USD\n",
       "2    T_3_02/15/49  14/08/2019 121.92      USD\n",
       "3    T_3_02/15/49  15/08/2019 123.05      USD"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bond_prices = pd.read_csv(\"data/bond_quotes_data.csv\")\n",
    "bond_prices"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Quote successfully loaded into LUSID. 4 quotes loaded.\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/adammassoud/work/code/repos/sample-notebooks-1/.venv/lib/python3.11/site-packages/lusidtools/lpt/lpt.py:67: UserWarning: Parsing dates in %d/%m/%Y format when dayfirst=False (the default) was specified. Pass `dayfirst=True` or specify a format to silence this warning.\n",
      "  return pd.to_datetime(date, utc=True, **kwargs) if date is not None else None\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[\"client_internal\"],\n",
    "                instrument_id_type=\"ClientInternal\",\n",
    "                quote_type=\"Price\",\n",
    "                field=\"mid\",\n",
    "            ),\n",
    "            effective_at=to_date(row[\"date\"]).isoformat(),\n",
    "        ),\n",
    "        metric_value=lm.MetricValue(value=row[\"price\"], unit=row[\"currency\"]),\n",
    "        scale_factor=100,\n",
    "    )\n",
    "    for index, row in bond_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": [
    "# 6. Valuations\n",
    "\n",
    "Once we have the bond booked into a portfolio, we can now value this portfolio. The recipe below describes how we will go about valuing the credit instruments in our portfolio (in this case just our bond). It details which model we will be using, SimpleStatic, which is supplied by Lusid."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6.1 Create valuation recipe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a recipe to perform a valuation\n",
    "configuration_recipe = lm.ConfigurationRecipe(\n",
    "    scope=scope,\n",
    "    code=\"bondValuation\",\n",
    "    market=lm.MarketContext(\n",
    "        market_rules=[\n",
    "            lm.MarketDataKeyRule(\n",
    "                key=\"Credit.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",
    "    pricing=lm.PricingContext(\n",
    "        model_rules=[\n",
    "            lm.VendorModelRule(\n",
    "                supplier=\"Lusid\",\n",
    "                model_name=\"SimpleStatic\",\n",
    "                instrument_type=\"Bond\",\n",
    "                parameters=\"{}\",\n",
    "            )\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, Accrued Interest and PnL as well as some instrument identifiers."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_val(date, portfolio_code):\n",
    "\n",
    "    valuation_request = lm.ValuationRequest(\n",
    "        recipe_id=lm.ResourceId(scope=scope, code=\"bondValuation\"),\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(\"Holding/default/Accrual\", \"Value\"),\n",
    "            lm.AggregateSpec(\"Valuation/PnL/Tm1\", \"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",
    "            \"Holding/default/Accrual\": \"Accrued Interest\",\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 bond. Recall that we have bought this bond on Friday 19 April 2019 and this bond will only settle by Monday 22 April. Day 1 in our scenario is thus 22 April 2019."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Day 1\n",
    "\n",
    "At 10AM on day 1, once our bond has settled, we value our bond position with the SimpleStatic model and find that it is worth USD 1,006,909.03. This value includes about two months worth of accrued interest as the last coupon was on 2019-02-15 and we are now valuing it on 2019-04-22."
   ]
  },
  {
   "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>Present Value</th>\n",
       "      <th>InstrumentName</th>\n",
       "      <th>ClientInternal</th>\n",
       "      <th>Quotes/Price</th>\n",
       "      <th>Holding/default/Units</th>\n",
       "      <th>Accrued Interest</th>\n",
       "      <th>PnL (1-day)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1,006,953.99</td>\n",
       "      <td>T 3 02/15/49</td>\n",
       "      <td>T_3_02/15/49</td>\n",
       "      <td>100.15</td>\n",
       "      <td>1,000,000.00</td>\n",
       "      <td>5,469.61</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Present Value InstrumentName ClientInternal  Quotes/Price  \\\n",
       "0   1,006,953.99   T 3 02/15/49   T_3_02/15/49        100.15   \n",
       "\n",
       "   Holding/default/Units  Accrued Interest PnL (1-day)  \n",
       "0           1,000,000.00          5,469.61        None  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get_val(\"2019-04-22T10:00:00Z\", portfolio_code)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Day 2\n",
    "\n",
    "On day 2, we run our valuation again and find that our accrued interest increased and the price has moved up. Our position is now worth USD 2,347.82 more than on day 1. Our PnL after 1 day is thus USD 2,347.82 on this position."
   ]
  },
  {
   "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>Present Value</th>\n",
       "      <th>InstrumentName</th>\n",
       "      <th>ClientInternal</th>\n",
       "      <th>Quotes/Price</th>\n",
       "      <th>Holding/default/Units</th>\n",
       "      <th>Accrued Interest</th>\n",
       "      <th>PnL (1-day)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1,009,302.49</td>\n",
       "      <td>T 3 02/15/49</td>\n",
       "      <td>T_3_02/15/49</td>\n",
       "      <td>100.38</td>\n",
       "      <td>1,000,000.00</td>\n",
       "      <td>5,552.49</td>\n",
       "      <td>2,348.50</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Present Value InstrumentName ClientInternal  Quotes/Price  \\\n",
       "0   1,009,302.49   T 3 02/15/49   T_3_02/15/49        100.38   \n",
       "\n",
       "   Holding/default/Units  Accrued Interest  PnL (1-day)  \n",
       "0           1,000,000.00          5,552.49     2,348.50  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get_val(\"2019-04-23T10:00:00Z\", portfolio_code)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Coupon date"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "One day before our first coupon date, we see the accrued interest approaching the coupon value of USD 15,000. When the coupon date hits, the accrued interest will reset to zero. In our case, we want to add a cash position of about USD 15,000 to our portfolio so that we end up with the correct value on the coupon date. This is where our cash flow management functions come in."
   ]
  },
  {
   "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>Present Value</th>\n",
       "      <th>InstrumentName</th>\n",
       "      <th>ClientInternal</th>\n",
       "      <th>Quotes/Price</th>\n",
       "      <th>Holding/default/Units</th>\n",
       "      <th>Accrued Interest</th>\n",
       "      <th>PnL (1-day)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1,234,135.88</td>\n",
       "      <td>T 3 02/15/49</td>\n",
       "      <td>T_3_02/15/49</td>\n",
       "      <td>121.92</td>\n",
       "      <td>1,000,000.00</td>\n",
       "      <td>14,917.13</td>\n",
       "      <td>35,707.87</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Present Value InstrumentName ClientInternal  Quotes/Price  \\\n",
       "0   1,234,135.88   T 3 02/15/49   T_3_02/15/49        121.92   \n",
       "\n",
       "   Holding/default/Units  Accrued Interest  PnL (1-day)  \n",
       "0           1,000,000.00         14,917.13    35,707.87  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get_val(\"2019-08-14T10:00:00Z\", portfolio_code)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "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>Accrued Interest</th>\n",
       "      <th>PnL (1-day)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1,230,468.75</td>\n",
       "      <td>T 3 02/15/49</td>\n",
       "      <td>T_3_02/15/49</td>\n",
       "      <td>123.05</td>\n",
       "      <td>1,000,000.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>-3,667.13</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Present Value InstrumentName ClientInternal  Quotes/Price  \\\n",
       "0   1,230,468.75   T 3 02/15/49   T_3_02/15/49        123.05   \n",
       "\n",
       "   Holding/default/Units  Accrued Interest  PnL (1-day)  \n",
       "0           1,000,000.00              0.00    -3,667.13  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get_val(\"2019-08-15T10:00:00Z\", portfolio_code)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 7. Cash Flows"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 7.1 Cash Ladder"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If we take a look at the cash ladder between our first day of owning this bond, 22 April 2019, and today (28 October 2021 at time of writing) we can see the coupons that will have been paid out to us during this timeframe."
   ]
  },
  {
   "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>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>2019-08-15 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>2019-08-15 00:00:00+00:00</td>\n",
       "      <td>Coupon</td>\n",
       "      <td>15,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>USD</td>\n",
       "      <td>2019-08-15 00:00:00+00:00</td>\n",
       "      <td>Close</td>\n",
       "      <td>15,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>USD</td>\n",
       "      <td>2020-02-15 00:00:00+00:00</td>\n",
       "      <td>Open</td>\n",
       "      <td>15,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>USD</td>\n",
       "      <td>2020-02-15 00:00:00+00:00</td>\n",
       "      <td>Coupon</td>\n",
       "      <td>15,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>USD</td>\n",
       "      <td>2020-02-15 00:00:00+00:00</td>\n",
       "      <td>Close</td>\n",
       "      <td>30,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>USD</td>\n",
       "      <td>2020-08-15 00:00:00+00:00</td>\n",
       "      <td>Open</td>\n",
       "      <td>30,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>USD</td>\n",
       "      <td>2020-08-15 00:00:00+00:00</td>\n",
       "      <td>Coupon</td>\n",
       "      <td>15,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>USD</td>\n",
       "      <td>2020-08-15 00:00:00+00:00</td>\n",
       "      <td>Close</td>\n",
       "      <td>45,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>USD</td>\n",
       "      <td>2021-02-15 00:00:00+00:00</td>\n",
       "      <td>Open</td>\n",
       "      <td>45,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>USD</td>\n",
       "      <td>2021-02-15 00:00:00+00:00</td>\n",
       "      <td>Coupon</td>\n",
       "      <td>15,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>USD</td>\n",
       "      <td>2021-02-15 00:00:00+00:00</td>\n",
       "      <td>Close</td>\n",
       "      <td>60,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>USD</td>\n",
       "      <td>2021-08-15 00:00:00+00:00</td>\n",
       "      <td>Open</td>\n",
       "      <td>60,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>USD</td>\n",
       "      <td>2021-08-15 00:00:00+00:00</td>\n",
       "      <td>Coupon</td>\n",
       "      <td>15,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>USD</td>\n",
       "      <td>2021-08-15 00:00:00+00:00</td>\n",
       "      <td>Close</td>\n",
       "      <td>75,000.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Currency                      Date Activity     Value\n",
       "0       USD 2019-08-15 00:00:00+00:00     Open      0.00\n",
       "1       USD 2019-08-15 00:00:00+00:00   Coupon 15,000.00\n",
       "2       USD 2019-08-15 00:00:00+00:00    Close 15,000.00\n",
       "3       USD 2020-02-15 00:00:00+00:00     Open 15,000.00\n",
       "4       USD 2020-02-15 00:00:00+00:00   Coupon 15,000.00\n",
       "5       USD 2020-02-15 00:00:00+00:00    Close 30,000.00\n",
       "6       USD 2020-08-15 00:00:00+00:00     Open 30,000.00\n",
       "7       USD 2020-08-15 00:00:00+00:00   Coupon 15,000.00\n",
       "8       USD 2020-08-15 00:00:00+00:00    Close 45,000.00\n",
       "9       USD 2021-02-15 00:00:00+00:00     Open 45,000.00\n",
       "10      USD 2021-02-15 00:00:00+00:00   Coupon 15,000.00\n",
       "11      USD 2021-02-15 00:00:00+00:00    Close 60,000.00\n",
       "12      USD 2021-08-15 00:00:00+00:00     Open 60,000.00\n",
       "13      USD 2021-08-15 00:00:00+00:00   Coupon 15,000.00\n",
       "14      USD 2021-08-15 00:00:00+00:00    Close 75,000.00"
      ]
     },
     "execution_count": 19,
     "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=\"2019-04-22T10:00:00Z\",\n",
    "    from_effective_at=\"2019-04-22T10:00:00Z\",\n",
    "    to_effective_at=\"2021-10-28T10:00:00Z\",\n",
    ")\n",
    "\n",
    "cashladder_to_df(cash_ladder)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can also take a look and see all the future cash flows we can expect from this bond by running the same function, but setting the time frame from now until a few years in the future. In this example we are retrieving the expected cash flows from 2021-10-28 until 2025-10-28."
   ]
  },
  {
   "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>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>2022-02-15 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>2022-02-15 00:00:00+00:00</td>\n",
       "      <td>Coupon</td>\n",
       "      <td>15,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>USD</td>\n",
       "      <td>2022-02-15 00:00:00+00:00</td>\n",
       "      <td>Close</td>\n",
       "      <td>15,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>USD</td>\n",
       "      <td>2022-08-15 00:00:00+00:00</td>\n",
       "      <td>Open</td>\n",
       "      <td>15,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>USD</td>\n",
       "      <td>2022-08-15 00:00:00+00:00</td>\n",
       "      <td>Coupon</td>\n",
       "      <td>15,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>USD</td>\n",
       "      <td>2022-08-15 00:00:00+00:00</td>\n",
       "      <td>Close</td>\n",
       "      <td>30,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>USD</td>\n",
       "      <td>2023-02-15 00:00:00+00:00</td>\n",
       "      <td>Open</td>\n",
       "      <td>30,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>USD</td>\n",
       "      <td>2023-02-15 00:00:00+00:00</td>\n",
       "      <td>Coupon</td>\n",
       "      <td>15,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>USD</td>\n",
       "      <td>2023-02-15 00:00:00+00:00</td>\n",
       "      <td>Close</td>\n",
       "      <td>45,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>USD</td>\n",
       "      <td>2023-08-15 00:00:00+00:00</td>\n",
       "      <td>Open</td>\n",
       "      <td>45,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>USD</td>\n",
       "      <td>2023-08-15 00:00:00+00:00</td>\n",
       "      <td>Coupon</td>\n",
       "      <td>15,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>USD</td>\n",
       "      <td>2023-08-15 00:00:00+00:00</td>\n",
       "      <td>Close</td>\n",
       "      <td>60,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>USD</td>\n",
       "      <td>2024-02-15 00:00:00+00:00</td>\n",
       "      <td>Open</td>\n",
       "      <td>60,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>USD</td>\n",
       "      <td>2024-02-15 00:00:00+00:00</td>\n",
       "      <td>Coupon</td>\n",
       "      <td>15,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>USD</td>\n",
       "      <td>2024-02-15 00:00:00+00:00</td>\n",
       "      <td>Close</td>\n",
       "      <td>75,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>USD</td>\n",
       "      <td>2024-08-15 00:00:00+00:00</td>\n",
       "      <td>Open</td>\n",
       "      <td>75,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>USD</td>\n",
       "      <td>2024-08-15 00:00:00+00:00</td>\n",
       "      <td>Coupon</td>\n",
       "      <td>15,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>USD</td>\n",
       "      <td>2024-08-15 00:00:00+00:00</td>\n",
       "      <td>Close</td>\n",
       "      <td>90,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>USD</td>\n",
       "      <td>2025-02-15 00:00:00+00:00</td>\n",
       "      <td>Open</td>\n",
       "      <td>90,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>USD</td>\n",
       "      <td>2025-02-15 00:00:00+00:00</td>\n",
       "      <td>Coupon</td>\n",
       "      <td>15,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>USD</td>\n",
       "      <td>2025-02-15 00:00:00+00:00</td>\n",
       "      <td>Close</td>\n",
       "      <td>105,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>USD</td>\n",
       "      <td>2025-08-15 00:00:00+00:00</td>\n",
       "      <td>Open</td>\n",
       "      <td>105,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>USD</td>\n",
       "      <td>2025-08-15 00:00:00+00:00</td>\n",
       "      <td>Coupon</td>\n",
       "      <td>15,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>USD</td>\n",
       "      <td>2025-08-15 00:00:00+00:00</td>\n",
       "      <td>Close</td>\n",
       "      <td>120,000.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Currency                      Date Activity      Value\n",
       "0       USD 2022-02-15 00:00:00+00:00     Open       0.00\n",
       "1       USD 2022-02-15 00:00:00+00:00   Coupon  15,000.00\n",
       "2       USD 2022-02-15 00:00:00+00:00    Close  15,000.00\n",
       "3       USD 2022-08-15 00:00:00+00:00     Open  15,000.00\n",
       "4       USD 2022-08-15 00:00:00+00:00   Coupon  15,000.00\n",
       "5       USD 2022-08-15 00:00:00+00:00    Close  30,000.00\n",
       "6       USD 2023-02-15 00:00:00+00:00     Open  30,000.00\n",
       "7       USD 2023-02-15 00:00:00+00:00   Coupon  15,000.00\n",
       "8       USD 2023-02-15 00:00:00+00:00    Close  45,000.00\n",
       "9       USD 2023-08-15 00:00:00+00:00     Open  45,000.00\n",
       "10      USD 2023-08-15 00:00:00+00:00   Coupon  15,000.00\n",
       "11      USD 2023-08-15 00:00:00+00:00    Close  60,000.00\n",
       "12      USD 2024-02-15 00:00:00+00:00     Open  60,000.00\n",
       "13      USD 2024-02-15 00:00:00+00:00   Coupon  15,000.00\n",
       "14      USD 2024-02-15 00:00:00+00:00    Close  75,000.00\n",
       "15      USD 2024-08-15 00:00:00+00:00     Open  75,000.00\n",
       "16      USD 2024-08-15 00:00:00+00:00   Coupon  15,000.00\n",
       "17      USD 2024-08-15 00:00:00+00:00    Close  90,000.00\n",
       "18      USD 2025-02-15 00:00:00+00:00     Open  90,000.00\n",
       "19      USD 2025-02-15 00:00:00+00:00   Coupon  15,000.00\n",
       "20      USD 2025-02-15 00:00:00+00:00    Close 105,000.00\n",
       "21      USD 2025-08-15 00:00:00+00:00     Open 105,000.00\n",
       "22      USD 2025-08-15 00:00:00+00:00   Coupon  15,000.00\n",
       "23      USD 2025-08-15 00:00:00+00:00    Close 120,000.00"
      ]
     },
     "execution_count": 20,
     "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-10-28T10:00:00Z\",\n",
    "    from_effective_at=\"2021-10-28T10:00:00Z\",\n",
    "    to_effective_at=\"2025-10-28T10:00:00Z\",\n",
    ")\n",
    "cashladder_to_df(cash_ladder)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 7.2 Cash Flow table\n",
    "\n",
    "Below we can see the end of the cash flow table for our portfolio which contains our bond. We can see the coupons we are owed and the final principal repayment on the maturity date.\n",
    "\n",
    "The table below is just to illustrate the final few payments and how the final payment with the principal at maturity date will be handled. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "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>transaction_id</th>\n",
       "      <th>type</th>\n",
       "      <th>instrument_scope</th>\n",
       "      <th>transaction_date</th>\n",
       "      <th>settlement_date</th>\n",
       "      <th>total_consideration.amount</th>\n",
       "      <th>total_consideration.currency</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>txn001-LUID_0000BYLX-20450815-Coupon-USD-Receive</td>\n",
       "      <td>CashFlow</td>\n",
       "      <td>default</td>\n",
       "      <td>2045-08-15 00:00:00+00:00</td>\n",
       "      <td>2045-08-15 00:00:00+00:00</td>\n",
       "      <td>15,000.00</td>\n",
       "      <td>USD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>txn001-LUID_0000BYLX-20460215-Coupon-USD-Receive</td>\n",
       "      <td>CashFlow</td>\n",
       "      <td>default</td>\n",
       "      <td>2046-02-15 00:00:00+00:00</td>\n",
       "      <td>2046-02-15 00:00:00+00:00</td>\n",
       "      <td>15,000.00</td>\n",
       "      <td>USD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>txn001-LUID_0000BYLX-20460815-Coupon-USD-Receive</td>\n",
       "      <td>CashFlow</td>\n",
       "      <td>default</td>\n",
       "      <td>2046-08-15 00:00:00+00:00</td>\n",
       "      <td>2046-08-15 00:00:00+00:00</td>\n",
       "      <td>15,000.00</td>\n",
       "      <td>USD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>txn001-LUID_0000BYLX-20470215-Coupon-USD-Receive</td>\n",
       "      <td>CashFlow</td>\n",
       "      <td>default</td>\n",
       "      <td>2047-02-15 00:00:00+00:00</td>\n",
       "      <td>2047-02-15 00:00:00+00:00</td>\n",
       "      <td>15,000.00</td>\n",
       "      <td>USD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>txn001-LUID_0000BYLX-20470815-Coupon-USD-Receive</td>\n",
       "      <td>CashFlow</td>\n",
       "      <td>default</td>\n",
       "      <td>2047-08-15 00:00:00+00:00</td>\n",
       "      <td>2047-08-15 00:00:00+00:00</td>\n",
       "      <td>15,000.00</td>\n",
       "      <td>USD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>txn001-LUID_0000BYLX-20480215-Coupon-USD-Receive</td>\n",
       "      <td>CashFlow</td>\n",
       "      <td>default</td>\n",
       "      <td>2048-02-15 00:00:00+00:00</td>\n",
       "      <td>2048-02-15 00:00:00+00:00</td>\n",
       "      <td>15,000.00</td>\n",
       "      <td>USD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>txn001-LUID_0000BYLX-20480815-Coupon-USD-Receive</td>\n",
       "      <td>CashFlow</td>\n",
       "      <td>default</td>\n",
       "      <td>2048-08-15 00:00:00+00:00</td>\n",
       "      <td>2048-08-15 00:00:00+00:00</td>\n",
       "      <td>15,000.00</td>\n",
       "      <td>USD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>txn001-LUID_0000BYLX-20490215-Coupon-USD-Receive</td>\n",
       "      <td>CashFlow</td>\n",
       "      <td>default</td>\n",
       "      <td>2049-02-15 00:00:00+00:00</td>\n",
       "      <td>2049-02-15 00:00:00+00:00</td>\n",
       "      <td>15,000.00</td>\n",
       "      <td>USD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>txn001-LUID_0000BYLX-20490215-Principal-USD-Re...</td>\n",
       "      <td>CashFlow</td>\n",
       "      <td>default</td>\n",
       "      <td>2049-02-15 00:00:00+00:00</td>\n",
       "      <td>2049-02-15 00:00:00+00:00</td>\n",
       "      <td>1,000,000.00</td>\n",
       "      <td>USD</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                      transaction_id      type  \\\n",
       "0   txn001-LUID_0000BYLX-20450815-Coupon-USD-Receive  CashFlow   \n",
       "1   txn001-LUID_0000BYLX-20460215-Coupon-USD-Receive  CashFlow   \n",
       "2   txn001-LUID_0000BYLX-20460815-Coupon-USD-Receive  CashFlow   \n",
       "3   txn001-LUID_0000BYLX-20470215-Coupon-USD-Receive  CashFlow   \n",
       "4   txn001-LUID_0000BYLX-20470815-Coupon-USD-Receive  CashFlow   \n",
       "5   txn001-LUID_0000BYLX-20480215-Coupon-USD-Receive  CashFlow   \n",
       "6   txn001-LUID_0000BYLX-20480815-Coupon-USD-Receive  CashFlow   \n",
       "7   txn001-LUID_0000BYLX-20490215-Coupon-USD-Receive  CashFlow   \n",
       "8  txn001-LUID_0000BYLX-20490215-Principal-USD-Re...  CashFlow   \n",
       "\n",
       "  instrument_scope          transaction_date           settlement_date  \\\n",
       "0          default 2045-08-15 00:00:00+00:00 2045-08-15 00:00:00+00:00   \n",
       "1          default 2046-02-15 00:00:00+00:00 2046-02-15 00:00:00+00:00   \n",
       "2          default 2046-08-15 00:00:00+00:00 2046-08-15 00:00:00+00:00   \n",
       "3          default 2047-02-15 00:00:00+00:00 2047-02-15 00:00:00+00:00   \n",
       "4          default 2047-08-15 00:00:00+00:00 2047-08-15 00:00:00+00:00   \n",
       "5          default 2048-02-15 00:00:00+00:00 2048-02-15 00:00:00+00:00   \n",
       "6          default 2048-08-15 00:00:00+00:00 2048-08-15 00:00:00+00:00   \n",
       "7          default 2049-02-15 00:00:00+00:00 2049-02-15 00:00:00+00:00   \n",
       "8          default 2049-02-15 00:00:00+00:00 2049-02-15 00:00:00+00:00   \n",
       "\n",
       "   total_consideration.amount total_consideration.currency  \n",
       "0                   15,000.00                          USD  \n",
       "1                   15,000.00                          USD  \n",
       "2                   15,000.00                          USD  \n",
       "3                   15,000.00                          USD  \n",
       "4                   15,000.00                          USD  \n",
       "5                   15,000.00                          USD  \n",
       "6                   15,000.00                          USD  \n",
       "7                   15,000.00                          USD  \n",
       "8                1,000,000.00                          USD  "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "upsertable_cash_flows = transaction_portfolios_api.get_upsertable_portfolio_cash_flows(\n",
    "    scope=scope,\n",
    "    code=portfolio_code,\n",
    "    effective_at=\"2021-08-14T10:00:00Z\",\n",
    "    window_start=\"2045-02-15T10:00:00Z\",\n",
    "    window_end=\"2049-02-15T10:00:00Z\",\n",
    ")\n",
    "\n",
    "# we create a dataframe out of the cash flows table and drop some columns to improve readability\n",
    "cash_flow_table = lusid_response_to_data_frame(upsertable_cash_flows)\n",
    "cash_flow_table[\n",
    "    [\n",
    "        \"transaction_id\",\n",
    "        \"type\",\n",
    "        \"instrument_scope\",\n",
    "        \"transaction_date\",\n",
    "        \"settlement_date\",\n",
    "        \"total_consideration.amount\",\n",
    "        \"total_consideration.currency\"\n",
    "    ]\n",
    "]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In our valuation of the coupon date we did in part \"5.2 Create Daily Valuation Function\", we saw the accrued interest for the coupon on 2019-08-15 reset. Now we will retrieve this cash flow and upsert it to LUSID so that in our valuation, we can see the bond and the cash position together."
   ]
  },
  {
   "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>Present Value</th>\n",
       "      <th>InstrumentName</th>\n",
       "      <th>ClientInternal</th>\n",
       "      <th>Quotes/Price</th>\n",
       "      <th>Holding/default/Units</th>\n",
       "      <th>Accrued Interest</th>\n",
       "      <th>PnL (1-day)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1,230,468.75</td>\n",
       "      <td>T 3 02/15/49</td>\n",
       "      <td>T_3_02/15/49</td>\n",
       "      <td>123.05</td>\n",
       "      <td>1,000,000.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>-3,667.13</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Present Value InstrumentName ClientInternal  Quotes/Price  \\\n",
       "0   1,230,468.75   T 3 02/15/49   T_3_02/15/49        123.05   \n",
       "\n",
       "   Holding/default/Units  Accrued Interest  PnL (1-day)  \n",
       "0           1,000,000.00              0.00    -3,667.13  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get_val(\"2019-08-15T10:00:00Z\", portfolio_code)"
   ]
  },
  {
   "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>transaction_id</th>\n",
       "      <th>type</th>\n",
       "      <th>instrument_identifiers.Instrument/default/LusidInstrumentId</th>\n",
       "      <th>instrument_scope</th>\n",
       "      <th>instrument_uid</th>\n",
       "      <th>transaction_date</th>\n",
       "      <th>settlement_date</th>\n",
       "      <th>units</th>\n",
       "      <th>transaction_price.price</th>\n",
       "      <th>transaction_price.type</th>\n",
       "      <th>total_consideration.amount</th>\n",
       "      <th>total_consideration.currency</th>\n",
       "      <th>exchange_rate</th>\n",
       "      <th>transaction_currency</th>\n",
       "      <th>properties.Transaction/default/ParentLuid.key</th>\n",
       "      <th>properties.Transaction/default/ParentLuid.value.label_value</th>\n",
       "      <th>properties.Transaction/default/InstrumentEventId.key</th>\n",
       "      <th>properties.Transaction/default/InstrumentEventId.value.label_value</th>\n",
       "      <th>source</th>\n",
       "      <th>entry_date_time</th>\n",
       "      <th>transaction_status</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>txn001-LUID_0000BYLX-20190815-Coupon-USD-Receive</td>\n",
       "      <td>CashFlow</td>\n",
       "      <td>LUID_0000BYLX</td>\n",
       "      <td>default</td>\n",
       "      <td>LUID_0000BYLX</td>\n",
       "      <td>2019-08-15 00:00:00+00:00</td>\n",
       "      <td>2019-08-15 00:00:00+00:00</td>\n",
       "      <td>15,000.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>Price</td>\n",
       "      <td>15,000.00</td>\n",
       "      <td>USD</td>\n",
       "      <td>1.00</td>\n",
       "      <td>USD</td>\n",
       "      <td>Transaction/default/ParentLuid</td>\n",
       "      <td>LUID_0000BYLX</td>\n",
       "      <td>Transaction/default/InstrumentEventId</td>\n",
       "      <td>LUID_0000BYLX-20190815-Coupon-USD-Receive-v1</td>\n",
       "      <td>default</td>\n",
       "      <td>0001-01-01 00:00:00+00:00</td>\n",
       "      <td>Active</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                     transaction_id      type  \\\n",
       "0  txn001-LUID_0000BYLX-20190815-Coupon-USD-Receive  CashFlow   \n",
       "\n",
       "  instrument_identifiers.Instrument/default/LusidInstrumentId  \\\n",
       "0                                      LUID_0000BYLX            \n",
       "\n",
       "  instrument_scope instrument_uid          transaction_date  \\\n",
       "0          default  LUID_0000BYLX 2019-08-15 00:00:00+00:00   \n",
       "\n",
       "            settlement_date     units  transaction_price.price  \\\n",
       "0 2019-08-15 00:00:00+00:00 15,000.00                     1.00   \n",
       "\n",
       "  transaction_price.type  total_consideration.amount  \\\n",
       "0                  Price                   15,000.00   \n",
       "\n",
       "  total_consideration.currency  exchange_rate transaction_currency  \\\n",
       "0                          USD           1.00                  USD   \n",
       "\n",
       "  properties.Transaction/default/ParentLuid.key  \\\n",
       "0                Transaction/default/ParentLuid   \n",
       "\n",
       "  properties.Transaction/default/ParentLuid.value.label_value  \\\n",
       "0                                      LUID_0000BYLX            \n",
       "\n",
       "  properties.Transaction/default/InstrumentEventId.key  \\\n",
       "0              Transaction/default/InstrumentEventId     \n",
       "\n",
       "  properties.Transaction/default/InstrumentEventId.value.label_value   source  \\\n",
       "0       LUID_0000BYLX-20190815-Coupon-USD-Receive-v1                  default   \n",
       "\n",
       "             entry_date_time transaction_status  \n",
       "0  0001-01-01 00:00:00+00:00             Active  "
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "upsertable_cash_flows = transaction_portfolios_api.get_upsertable_portfolio_cash_flows(\n",
    "    scope=scope,\n",
    "    code=portfolio_code,\n",
    "    effective_at=\"2019-08-14T10:00:00Z\",\n",
    "    window_start=\"2019-08-14T10:00:00Z\",\n",
    "    window_end=\"2019-08-30T10:00:00Z\",\n",
    ")\n",
    "lusid_response_to_data_frame(upsertable_cash_flows)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We want to book this cashflow into LUSID now so that we have an accurate cash position for our valuation. We have to make a few minor adjustments such as specifying which instrument we want to book these cash flows into. In our case, this is the USD currency identifier. We must also ensure the transaction ID's are unique, thus we tag on the date of the coupon to the transaction ID.\n",
    "\n",
    "Once this is done, we upsert these cash flows to our portfolio."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "for x in upsertable_cash_flows.values:\n",
    "    x.instrument_identifiers = {\"Instrument/default/Currency\": \"USD\"}\n",
    "    x.transaction_id = x.transaction_id + \"-\" + str(x.transaction_date)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "upsert_transactions = transaction_portfolios_api.upsert_transactions(\n",
    "    scope=scope, code=portfolio_code, transaction_request=upsertable_cash_flows.values\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If we now want to see the valuation of our portfolio on the coupon date, we can see the bond position has diminished by the coupon amount and the cash position has increased by that amount."
   ]
  },
  {
   "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>Present Value</th>\n",
       "      <th>InstrumentName</th>\n",
       "      <th>ClientInternal</th>\n",
       "      <th>Quotes/Price</th>\n",
       "      <th>Holding/default/Units</th>\n",
       "      <th>Accrued Interest</th>\n",
       "      <th>PnL (1-day)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1,230,468.75</td>\n",
       "      <td>T 3 02/15/49</td>\n",
       "      <td>T_3_02/15/49</td>\n",
       "      <td>123.05</td>\n",
       "      <td>1,000,000.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>-3,667.13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>15,000.00</td>\n",
       "      <td>USD</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>15,000.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Present Value InstrumentName ClientInternal  Quotes/Price  \\\n",
       "0   1,230,468.75   T 3 02/15/49   T_3_02/15/49        123.05   \n",
       "1      15,000.00            USD           None           NaN   \n",
       "\n",
       "   Holding/default/Units  Accrued Interest  PnL (1-day)  \n",
       "0           1,000,000.00              0.00    -3,667.13  \n",
       "1              15,000.00              0.00         0.00  "
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get_val(\"2019-08-15T10:00:00Z\", portfolio_code)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "# TEARDOWN\n",
    "try:\n",
    "    portfolio_api.delete_portfolio(\n",
    "        scope=scope,\n",
    "        code=portfolio_code\n",
    "    )\n",
    "\n",
    "except lu.ApiException as e:\n",
    "    print(json.loads(e.body)[\"title\"])\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "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.11.2"
  },
  "vscode": {
   "interpreter": {
    "hash": "9b2ad45377be23472136155f9be1fd1f9127868d50a62bcbda4e8cdcd384c3ac"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}