V1/features/instruments/Mortgage Backed Securities.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",
    "\"\"\"Mortgage Backed Securities in LUSID\n",
    "\n",
    "Attributes\n",
    "----------\n",
    "mortgage backed securities\n",
    "complex bond\n",
    "mbs\n",
    "schedule\n",
    "pool factor\n",
    "\"\"\"\n",
    "\n",
    "toggle_code(\"Toggle Docstring\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# Demonstrating LUSID Support for Mortgage Backed Securities\n",
    "\n",
    "In this notebook we show how to:\n",
    " - book an MBS bond into LUSID \n",
    " - query past and future cashflows and verify cashflow entitlements\n",
    " - query accrued interest and current notional\n",
    " - value a portfolio that has this bond\n",
    "\n",
    "The example is based off a real Mortgage Backed Security bond issued by Fannie Mae (Isin: US3136AKAD59).\n",
    "\n",
    "## Table of Contents:\n",
    "- 1. [Set up the environment required for valuation](#1.-Set-Up-Environment)\n",
    "- 2. [Create portfolios for the tests](#2.-Create-Portfolios)\n",
    "- 3. [Creating an MBS instrument](#3.-Creating-an-MBS-Instrument)\n",
    "- 4. [Pool Factors](#4.-Pool-Factors)\n",
    "- 5. [Transactions](#5.-Transactions)\n",
    "- 6. [Cash flows and Entitlements](#6.-Cash-Flows-and-Entitlements)\n",
    "- 7. [Valuation & PnL](#7.-Valuations)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "source": [
    "# 1. Set Up Environment\n",
    "\n",
    "First we set up the local variables and the test environment:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "LUSID Environment Initialised\n",
      "LUSID API Version : 0.6.12722.0\n"
     ]
    }
   ],
   "source": [
    "# Import generic non-LUSID packages\n",
    "import os\n",
    "import numpy as np\n",
    "import datetime as dt\n",
    "import pandas as pd\n",
    "import json\n",
    "import pytz\n",
    "import itertools\n",
    "import concurrent.futures\n",
    "from datetime import datetime, timedelta, timezone\n",
    "from dateutil.relativedelta import relativedelta\n",
    "from random import seed, randint\n",
    "\n",
    "# Import key modules from the LUSID package\n",
    "import lusid as lu\n",
    "import lusid.models as lm\n",
    "from lusid.utilities import ApiClientFactory\n",
    "from lusidtools.cocoon.cocoon import load_from_data_frame\n",
    "from lusidtools.cocoon.cocoon_printer import format_transactions_response\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 lusidjam import RefreshingToken\n",
    "\n",
    "# Set pandas to display a nice number formatting\n",
    "pd.options.display.float_format = '{:,.2f}'.format\n",
    "\n",
    "# Set the secrets path\n",
    "secrets_path = os.getenv(\"FBN_SECRETS_PATH\")\n",
    "\n",
    "# For running the notebook locally\n",
    "if secrets_path is None:\n",
    "    secrets_path = os.path.join(os.path.dirname(os.getcwd()), \"secrets.json\")\n",
    "\n",
    "# Authenticate our user and create our API client\n",
    "api_factory = lu.utilities.ApiClientFactory(\n",
    "    token=RefreshingToken(), api_secrets_filename=secrets_path\n",
    ")\n",
    "\n",
    "print(\"LUSID Environment Initialised\")\n",
    "print(\n",
    "    \"LUSID API Version :\",\n",
    "    api_factory.build(lu.api.ApplicationMetadataApi).get_lusid_versions().build_version,\n",
    ")\n",
    "\n",
    "# set random codes for portfolios to avoid concurrency issues\n",
    "seed()\n",
    "portfolio_1_code = f\"portfolio_{randint(0,5000)}\"\n",
    "portfolio_2_code = f\"portfolio_{randint(5001,10000)}\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "outputs": [],
   "source": [
    "# LUSID Variable Definitions\n",
    "transaction_portfolios_api = api_factory.build(lu.api.TransactionPortfoliosApi)\n",
    "instruments_api = api_factory.build(lu.api.InstrumentsApi)\n",
    "portfolios_api = api_factory.build(lu.api.PortfoliosApi)\n",
    "recipes_api = api_factory.build(lu.api.ConfigurationRecipeApi)\n",
    "aggregation_api = api_factory.build(lu.api.AggregationApi)\n",
    "quotes_api = api_factory.build(lu.api.QuotesApi)\n",
    "calendar_api = api_factory.build(lu.api.CalendarsApi)\n",
    "configuration_recipe_api = api_factory.build(lu.api.ConfigurationRecipeApi)\n",
    "scope = \"TESTMBS\"\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "source": [
    "# 2. Create Portfolios\n",
    "\n",
    "We need to book transactions into portfolios. So the first step is to create them!\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And now we create the two new portfolios required for our demo."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "outputs": [],
   "source": [
    "def create_portfolio(portfolio):\n",
    "\n",
    "    try:\n",
    "        transaction_portfolios_api.create_portfolio(\n",
    "            scope=scope,\n",
    "            create_transaction_portfolio_request=lm.CreateTransactionPortfolioRequest(\n",
    "                display_name=portfolio,\n",
    "                code=portfolio,\n",
    "                base_currency=\"USD\",\n",
    "                created=datetime(year=1900, month=1, day=1, tzinfo=pytz.UTC),\n",
    "                instrument_scopes=[f\"{scope}\"],\n",
    "            ),\n",
    "        )\n",
    "        print(f'Portfolio \"{scope}/{portfolio}\" created')\n",
    "        return 1\n",
    "\n",
    "    except lu.ApiException as e:\n",
    "        detail = json.loads(e.body)\n",
    "        if detail[\"code\"] == 112:\n",
    "            print(f'Portfolio \"{scope}/{portfolio}\" already exists')\n",
    "            return 0\n",
    "        else:\n",
    "            print(e)\n",
    "            raise e"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Portfolio \"TESTMBS/portfolio_52\" created\n",
      "Portfolio \"TESTMBS/portfolio_8252\" created\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "1"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "create_portfolio(portfolio_1_code)\n",
    "create_portfolio(portfolio_2_code)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# 3. Creating an MBS Instrument\n",
    "\n",
    "Now let's define a function that takes in the information required to build a Fannie Mae Fixed Rate MBS and upserts it to lusid."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "def create_fixed_rate_Fannie_Mae_MBS(\n",
    "   bond_name,\n",
    "   ISIN,\n",
    "   currency,\n",
    "   start_date,\n",
    "   maturity_date,\n",
    "   original_notional,\n",
    "   coupon,\n",
    "   pay_delay,\n",
    "   payment_frequency,\n",
    "   roll_convention,\n",
    "   stub_type\n",
    "):\n",
    "    bond_flow_conventions = lm.FlowConventions(\n",
    "            currency = currency,\n",
    "            payment_frequency = payment_frequency,\n",
    "            roll_convention = roll_convention,\n",
    "            day_count_convention = 'Thirty360',\n",
    "            payment_calendars = [],\n",
    "            reset_calendars = []\n",
    "    )\n",
    "\n",
    "    pay_delay_information = lm.ExDividendConfiguration(\n",
    "        ex_dividend_days = pay_delay,\n",
    "        apply_thirty360_pay_delay = True\n",
    "    )\n",
    "\n",
    "    fixed_schedule = lm.FixedSchedule(\n",
    "        schedule_type = \"FixedSchedule\",\n",
    "        start_date = start_date,\n",
    "        maturity_date = maturity_date,\n",
    "        coupon_rate = coupon,\n",
    "        notional = original_notional,\n",
    "        payment_currency = currency,\n",
    "        stub_type = 'None',\n",
    "        flow_conventions = bond_flow_conventions,\n",
    "        ex_dividend_configuration = pay_delay_information\n",
    "    )\n",
    "\n",
    "    bond = lm.ComplexBond(\n",
    "        asset_backed = True,\n",
    "        asset_pool_identifier = ISIN,\n",
    "        instrument_type = \"ComplexBond\",\n",
    "        schedules = [fixed_schedule]\n",
    "    )\n",
    "\n",
    "    # define the instrument to be upserted\n",
    "    bond_definition = lm.InstrumentDefinition(\n",
    "        name=bond_name,\n",
    "        identifiers={\"ClientInternal\": lm.InstrumentIdValue(ISIN)},\n",
    "        definition=bond,\n",
    "    )\n",
    "\n",
    "    # upsert the instrument\n",
    "    upsert_request = {ISIN: bond_definition}\n",
    "    upsert_response = instruments_api.upsert_instruments(request_body=upsert_request)\n",
    "    bond_luid = upsert_response.values[ISIN].lusid_instrument_id\n",
    "    print(f\"Bond {ISIN} upserted successfully - LUID is {bond_luid}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We defined using the LUSID SDK a function that creates a typical case of an MBS from relevant trade information. This function can be very handy if we need to create several MBSs, as we encapsulate the handling of all the required complex bond objects into a single function call."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "source": [
    "And here we create the MBS of our interest (US3136AKAD59):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Bond US3136AKAD59 upserted successfully - LUID is LUID_0008WM6L\n"
     ]
    }
   ],
   "source": [
    "coupon_rate = 0.025\n",
    "bond_name = 'FED NATIONAL MTGE ASSOC 2014-33'\n",
    "ISIN = 'US3136AKAD59'\n",
    "currency = 'USD'\n",
    "start_date = datetime(2014, 5, 1, 00, tzinfo=pytz.utc)\n",
    "maturity_date = datetime(2029, 9, 25, 00, tzinfo=pytz.utc)\n",
    "original_notional = 1000000\n",
    "pay_delay = 24\n",
    "payment_frequency = '1M'\n",
    "roll_convention = 'Day25'\n",
    "stub_type = 'None'\n",
    "\n",
    "create_fixed_rate_Fannie_Mae_MBS(\n",
    "   bond_name,\n",
    "   ISIN,\n",
    "   currency,\n",
    "   start_date,\n",
    "   maturity_date,\n",
    "   original_notional,\n",
    "   coupon_rate,\n",
    "   pay_delay,\n",
    "   payment_frequency,\n",
    "   roll_convention,\n",
    "   stub_type\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# 4. Pool Factors\n",
    "\n",
    "Additionally, we also need to upsert the required pool factors. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "pool_factor_data = [[\"US3136AKAD59\", \"2023-05-01\", 0.100641],\n",
    "                    [\"US3136AKAD59\", \"2023-06-01\", 0.098082],\n",
    "                    [\"US3136AKAD59\", \"2023-07-01\", 0.095056]]\n",
    "pool_factors = pd.DataFrame(pool_factor_data, columns=[\"ISIN\",\"Date\",\"Factor\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "outputs": [],
   "source": [
    "def upsert_factor(ISIN, date, factor):\n",
    "    quotes = {\n",
    "            0: lm.UpsertQuoteRequest(\n",
    "            quote_id=lm.QuoteId(\n",
    "                quote_series_id=lm.QuoteSeriesId(\n",
    "                    provider=\"Lusid\",\n",
    "                    instrument_id=ISIN,\n",
    "                    instrument_id_type=\"Isin\",\n",
    "                    quote_type=\"PoolFactor\",\n",
    "                    field=\"mid\",\n",
    "                ),\n",
    "                effective_at=date.isoformat()+'Z',\n",
    "            ),\n",
    "            metric_value=lm.MetricValue(value=factor, unit=\"ratio\"),\n",
    "            scale_factor=1\n",
    "        )\n",
    "    }\n",
    "    upsert_response = quotes_api.upsert_quotes(scope=scope, request_body=quotes)\n",
    "    if upsert_response.failed:\n",
    "        print(f\"Failed to upsert {factor} for {ISIN} at {date}.\")\n",
    "    else:\n",
    "        print(f\"Upserting {factor} for {ISIN} at {date} completed successfully.\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Upserting 0.100641 for US3136AKAD59 at 2023-05-01 00:00:00 completed successfully.\n",
      "Upserting 0.098082 for US3136AKAD59 at 2023-06-01 00:00:00 completed successfully.\n",
      "Upserting 0.095056 for US3136AKAD59 at 2023-07-01 00:00:00 completed successfully.\n"
     ]
    }
   ],
   "source": [
    "for idx in pool_factors.index:\n",
    "    upsert_factor(\n",
    "        pool_factors['ISIN'][idx], \n",
    "        datetime.strptime(pool_factors['Date'][idx],\"%Y-%m-%d\"), \n",
    "        pool_factors['Factor'][idx])\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# 5. Transactions\n",
    "\n",
    "In this demo, first we will:\n",
    " - Buy 8,000,000 USD (original notional value) of the bond on the 28-May-2023 and add this position to portfolio 1\n",
    " - Buy 8,000,000 USD (original notional value) unit of the same bond on the 03-Jun-2023 and add this position to portfolio 2\n",
    " - Compare the different cashflow profiles between portfolio 1 and portfolio 2\n",
    "\n",
    "The first comparison will be as at 15-Jul-2023, so that we can see the effect of cashflow entitlement dates for the different portfolios. However, we will also compare the accrued interest as at 15-Jun-2023 to confirm that the entitlement to accrued interest for the month of June is consistent between the two portfolios.\n",
    "\n",
    "To complete this demo, we will:\n",
    " - Sell on the 22-Jun-2023 4,000,000 USD (original notional value) the position held in portfolio 1\n",
    " - Confirm that the entitlement to the past (and still unpaid) coupon has remained unchanged but the entitlements to future cashflows and accrued interest have halved\n",
    " \n",
    "In all transactions, we are paying / receiving 100,000 USD per 1,000,000 USD of original notional value - this generates the additional cash entry in the valuation requests.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "outputs": [],
   "source": [
    "trade_date_1 = datetime(2023, 5, 28, tzinfo=timezone.utc)\n",
    "trade_date_2 = datetime(2023, 6, 3, tzinfo=timezone.utc)\n",
    "sell_date = datetime(2023, 6, 22, tzinfo=timezone.utc)\n",
    "\n",
    "effective_at_date = datetime(2023, 7, 15, tzinfo=timezone.utc)\n",
    "window_start_date = datetime(2023, 6, 1, tzinfo=timezone.utc)\n",
    "window_end_date = datetime(2023, 8, 1, tzinfo=timezone.utc)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "scrolled": false,
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Buy transaction upserted successfully for portfolio_1\n"
     ]
    }
   ],
   "source": [
    "open_position_1=[lm.TransactionRequest(\n",
    "        transaction_id=\"txn_1\",\n",
    "        type=\"Buy\",\n",
    "        instrument_identifiers={\n",
    "            \"Instrument/default/ClientInternal\" : \"US3136AKAD59\",\n",
    "            \"Instrument/default/Isin\" : \"US3136AKAD59\"\n",
    "        },\n",
    "        transaction_date=trade_date_1.isoformat(),\n",
    "        settlement_date=trade_date_1.isoformat(),\n",
    "        units=8,\n",
    "        transaction_price=lm.TransactionPrice(price=1, type=\"Price\"),\n",
    "        total_consideration=lm.CurrencyAndAmount(amount=800000, currency=\"USD\"),\n",
    "        transaction_currency=\"USD\",\n",
    "        properties={}\n",
    "    )]\n",
    "\n",
    "response = transaction_portfolios_api.upsert_transactions(\n",
    "    scope=scope,\n",
    "    code=portfolio_1_code,\n",
    "    transaction_request=open_position_1\n",
    ")\n",
    "\n",
    "print(\"Buy transaction upserted successfully for portfolio_1\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Buy transaction upserted successfully for portfolio_2\n"
     ]
    }
   ],
   "source": [
    "open_position_2=[lm.TransactionRequest(\n",
    "        transaction_id=\"txn_2\",\n",
    "        type=\"Buy\",\n",
    "        instrument_identifiers={\n",
    "            \"Instrument/default/ClientInternal\" : \"US3136AKAD59\",\n",
    "            \"Instrument/default/Isin\" : \"US3136AKAD59\"\n",
    "        },\n",
    "        transaction_date=trade_date_2.isoformat(),\n",
    "        settlement_date=trade_date_2.isoformat(),\n",
    "        units=8,\n",
    "        transaction_price=lm.TransactionPrice(price=1, type=\"Price\"),\n",
    "        total_consideration=lm.CurrencyAndAmount(amount=800000, currency=\"USD\"),\n",
    "        transaction_currency=\"USD\",\n",
    "        properties={}\n",
    "    )]\n",
    "\n",
    "response = transaction_portfolios_api.upsert_transactions(\n",
    "    scope=scope,\n",
    "    code=portfolio_2_code,\n",
    "    transaction_request=open_position_2\n",
    ")\n",
    "\n",
    "print(\"Buy transaction upserted successfully for portfolio_2\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 6. Cash Flows and Entitlements\n",
    "\n",
    "Before we can check the cashflows of our first two transactions, we need to define a recipe that can pick up the pool factors and that specifies the valuation model as \"Constant Time Value of Money\"."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "outputs": [],
   "source": [
    "recipe_code = \"TESTMBS\"\n",
    "\n",
    "pool_factor_data_rule = lu.models.MarketDataKeyRule(\n",
    "                        key = 'Quote.Isin.*',\n",
    "                        supplier = 'Lusid',\n",
    "                        data_scope = scope,\n",
    "                        quote_type = 'PoolFactor',\n",
    "                        field = 'mid')\n",
    "\n",
    "resp = recipes_api.upsert_configuration_recipe(\n",
    "    upsert_recipe_request = lu.models.UpsertRecipeRequest(\n",
    "        configuration_recipe = lu.models.ConfigurationRecipe(\n",
    "            scope = scope,\n",
    "            code = recipe_code,\n",
    "            market = lu.models.MarketContext(\n",
    "                market_rules = [\n",
    "                    pool_factor_data_rule\n",
    "                ],\n",
    "                options = lu.models.MarketOptions(\n",
    "                    default_scope = 'default',\n",
    "                    calendar_scope = scope\n",
    "                )\n",
    "            ),\n",
    "            pricing = lu.models.PricingContext(\n",
    "                options = lu.models.PricingOptions(\n",
    "                    model_selection = lu.models.ModelSelection(\n",
    "                        library = 'Lusid',\n",
    "                        model = 'ConstantTimeValueOfMoney'\n",
    "                    ),\n",
    "                    allow_partially_successful_evaluation = True\n",
    "                )\n",
    "            )\n",
    "        )\n",
    "    )\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we build the queries for the portfolio cash flows:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "scrolled": false,
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "outputs": [],
   "source": [
    "upsertable_cash_flows_portfolio_1 = transaction_portfolios_api.get_upsertable_portfolio_cash_flows(\n",
    "    scope=scope,\n",
    "    code=portfolio_1_code,\n",
    "    effective_at=effective_at_date,\n",
    "    window_start=window_start_date,\n",
    "    window_end=window_end_date,\n",
    "    recipe_id_scope=scope,\n",
    "    recipe_id_code=recipe_code\n",
    ")\n",
    "lusid_response_portfolio_1=lusid_response_to_data_frame(upsertable_cash_flows_portfolio_1)\n",
    "\n",
    "upsertable_cash_flows_portfolio_2 = transaction_portfolios_api.get_upsertable_portfolio_cash_flows(\n",
    "    scope=scope,\n",
    "    code=portfolio_2_code,\n",
    "    effective_at=effective_at_date,\n",
    "    window_start=window_start_date,\n",
    "    window_end=window_end_date,\n",
    "    recipe_id_scope=scope,\n",
    "    recipe_id_code=recipe_code\n",
    ")\n",
    "lusid_response_portfolio_2=lusid_response_to_data_frame(upsertable_cash_flows_portfolio_2)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "Here is the response for portfolio 1 (as at 15-Jul-2023):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "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>settlement_date</th>\n",
       "      <th>units</th>\n",
       "      <th>transaction_currency</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>txn_1-LUID_0008WM6L-20230625-Coupon-USD-Receive</td>\n",
       "      <td>2023-06-25 00:00:00+00:00</td>\n",
       "      <td>1,677.35</td>\n",
       "      <td>USD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>txn_1-LUID_0008WM6L-20230625-Principal-USD-Rec...</td>\n",
       "      <td>2023-06-25 00:00:00+00:00</td>\n",
       "      <td>20,472.00</td>\n",
       "      <td>USD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>txn_1-LUID_0008WM6L-20230725-Coupon-USD-Receive</td>\n",
       "      <td>2023-07-25 00:00:00+00:00</td>\n",
       "      <td>1,634.70</td>\n",
       "      <td>USD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>txn_1-LUID_0008WM6L-20230725-Principal-USD-Rec...</td>\n",
       "      <td>2023-07-25 00:00:00+00:00</td>\n",
       "      <td>24,208.00</td>\n",
       "      <td>USD</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                      transaction_id  \\\n",
       "0    txn_1-LUID_0008WM6L-20230625-Coupon-USD-Receive   \n",
       "1  txn_1-LUID_0008WM6L-20230625-Principal-USD-Rec...   \n",
       "2    txn_1-LUID_0008WM6L-20230725-Coupon-USD-Receive   \n",
       "3  txn_1-LUID_0008WM6L-20230725-Principal-USD-Rec...   \n",
       "\n",
       "            settlement_date     units transaction_currency  \n",
       "0 2023-06-25 00:00:00+00:00  1,677.35                  USD  \n",
       "1 2023-06-25 00:00:00+00:00 20,472.00                  USD  \n",
       "2 2023-07-25 00:00:00+00:00  1,634.70                  USD  \n",
       "3 2023-07-25 00:00:00+00:00 24,208.00                  USD  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(lusid_response_portfolio_1[[\"transaction_id\", \"settlement_date\", \"units\", \"transaction_currency\"]])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "Here is the response for portfolio 2 (as at 15-Jul-2023), not entitled to the June cashflows:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "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>settlement_date</th>\n",
       "      <th>units</th>\n",
       "      <th>transaction_currency</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>txn_2-LUID_0008WM6L-20230725-Coupon-USD-Receive</td>\n",
       "      <td>2023-07-25 00:00:00+00:00</td>\n",
       "      <td>1,634.70</td>\n",
       "      <td>USD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>txn_2-LUID_0008WM6L-20230725-Principal-USD-Rec...</td>\n",
       "      <td>2023-07-25 00:00:00+00:00</td>\n",
       "      <td>24,208.00</td>\n",
       "      <td>USD</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                      transaction_id  \\\n",
       "0    txn_2-LUID_0008WM6L-20230725-Coupon-USD-Receive   \n",
       "1  txn_2-LUID_0008WM6L-20230725-Principal-USD-Rec...   \n",
       "\n",
       "            settlement_date     units transaction_currency  \n",
       "0 2023-07-25 00:00:00+00:00  1,634.70                  USD  \n",
       "1 2023-07-25 00:00:00+00:00 24,208.00                  USD  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(lusid_response_portfolio_2[[\"transaction_id\", \"settlement_date\", \"units\", \"transaction_currency\"]])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "As the first portfolio was bought before the end of the month of May-2023 and the second portfolio was bought after the end of that month, the two portfolios will have different entitlements in the month of June (namely, the second portfolio will not be entitled to the principal and interest payments due on the 25-June-2023).\n",
    "\n",
    "However, in any date in June after the purchase of the second portfolio, the two portfolios are entitled to the same accrued interest. Let's confirm that by comparing the accrued interest for the two portfolios as at 15-Jun-2023:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "outputs": [],
   "source": [
    "def run_lusid_valuation(portfolio_code, date):\n",
    "    valuation_request = lm.ValuationRequest(\n",
    "        recipe_id=lm.ResourceId(scope=scope, code=recipe_code),\n",
    "        metrics=[\n",
    "            lm.AggregateSpec(\"Instrument/default/Name\", \"Value\"),\n",
    "            lm.AggregateSpec(\"Instrument/default/ClientInternal\", \"Value\"),\n",
    "            lm.AggregateSpec(\"Holding/default/Units\", \"Value\"),\n",
    "            lm.AggregateSpec(\"Valuation/CurrentNotional\", \"Value\"),\n",
    "            lm.AggregateSpec(\"Holding/default/Accrual\", \"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.isoformat()),\n",
    "    )\n",
    "    val_data = aggregation_api.get_valuation(valuation_request=valuation_request).data\n",
    "    vals_df = pd.DataFrame(val_data)\n",
    "    vals_df.rename(\n",
    "        columns={\n",
    "            \"Instrument/default/Name\": \"Instrument Name\",\n",
    "            \"Instrument/default/ClientInternal\": \"ISIN\",\n",
    "            \"Holding/default/Units\": \"Units Held\",\n",
    "            \"Valuation/CurrentNotional\": \"Current Notional per Unit\",\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": "code",
   "execution_count": 19,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Portfolio 1 Accrued Interest Profile as at 15-Jun-2023\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Instrument Name</th>\n",
       "      <th>ISIN</th>\n",
       "      <th>Units Held</th>\n",
       "      <th>Current Notional per Unit</th>\n",
       "      <th>Accrued Interest</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>FED NATIONAL MTGE ASSOC 2014-33</td>\n",
       "      <td>US3136AKAD59</td>\n",
       "      <td>8.00</td>\n",
       "      <td>98,082.00</td>\n",
       "      <td>762.86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>USD</td>\n",
       "      <td>None</td>\n",
       "      <td>-800,000.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   Instrument Name          ISIN  Units Held  \\\n",
       "0  FED NATIONAL MTGE ASSOC 2014-33  US3136AKAD59        8.00   \n",
       "1                              USD          None -800,000.00   \n",
       "\n",
       "   Current Notional per Unit  Accrued Interest  \n",
       "0                  98,082.00            762.86  \n",
       "1                        NaN              0.00  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "effective_at_date = datetime.strptime(\"2023-06-15T10:00:00+00:00\",\"%Y-%m-%dT%H:%M:%S%z\")\n",
    "print(\"Portfolio 1 Accrued Interest Profile as at 15-Jun-2023\")\n",
    "run_lusid_valuation(portfolio_1_code, effective_at_date)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Portfolio 2 Accrued Interest Profile as at 15-Jun-2023\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Instrument Name</th>\n",
       "      <th>ISIN</th>\n",
       "      <th>Units Held</th>\n",
       "      <th>Current Notional per Unit</th>\n",
       "      <th>Accrued Interest</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>FED NATIONAL MTGE ASSOC 2014-33</td>\n",
       "      <td>US3136AKAD59</td>\n",
       "      <td>8.00</td>\n",
       "      <td>98,082.00</td>\n",
       "      <td>762.86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>USD</td>\n",
       "      <td>None</td>\n",
       "      <td>-800,000.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   Instrument Name          ISIN  Units Held  \\\n",
       "0  FED NATIONAL MTGE ASSOC 2014-33  US3136AKAD59        8.00   \n",
       "1                              USD          None -800,000.00   \n",
       "\n",
       "   Current Notional per Unit  Accrued Interest  \n",
       "0                  98,082.00            762.86  \n",
       "1                        NaN              0.00  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(\"Portfolio 2 Accrued Interest Profile as at 15-Jun-2023\")\n",
    "run_lusid_valuation(portfolio_2_code, effective_at_date)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "To further demonstrate how the entitlements for MBS cashflows work in lusid, we will now sell half of the portfolio 1 and get the past and future cashflows and accrued interest, as at 15-Jul-2023. \n",
    "\n",
    "Notice now that the entitlement cashflows due on the 25-Jun-2023 have remained unchanged (as the entitlement boundary is the beginning of the month of June) but the values to be received on the 25-Jul-2023 have halved (as the halving transaction happened in June, before the entitlement boundary date for July cashflows)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Sell transaction upserted successfully for portfolio_1\n"
     ]
    }
   ],
   "source": [
    "sell_half_position_1=[lm.TransactionRequest(\n",
    "        transaction_id=\"txn_3\",\n",
    "        type=\"Sell\",\n",
    "        instrument_identifiers={\n",
    "            \"Instrument/default/ClientInternal\" : \"US3136AKAD59\",\n",
    "            \"Instrument/default/Isin\" : \"US3136AKAD59\"\n",
    "        },\n",
    "        transaction_date=sell_date.isoformat(),\n",
    "        settlement_date=sell_date.isoformat(),\n",
    "        units=4,\n",
    "        transaction_price=lm.TransactionPrice(price=1, type=\"Price\"),\n",
    "        total_consideration=lm.CurrencyAndAmount(amount=400000, currency=\"USD\"),\n",
    "        transaction_currency=\"USD\",\n",
    "        properties={}\n",
    "    )]\n",
    "\n",
    "response = transaction_portfolios_api.upsert_transactions(\n",
    "    scope=scope,\n",
    "    code=portfolio_1_code,\n",
    "    transaction_request=sell_half_position_1\n",
    ")\n",
    "\n",
    "print(\"Sell transaction upserted successfully for portfolio_1\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "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>settlement_date</th>\n",
       "      <th>units</th>\n",
       "      <th>transaction_currency</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>txn_3-LUID_0008WM6L-20230625-Coupon-USD-Receive</td>\n",
       "      <td>2023-06-25 00:00:00+00:00</td>\n",
       "      <td>1,677.35</td>\n",
       "      <td>USD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>txn_3-LUID_0008WM6L-20230625-Principal-USD-Rec...</td>\n",
       "      <td>2023-06-25 00:00:00+00:00</td>\n",
       "      <td>20,472.00</td>\n",
       "      <td>USD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>txn_3-LUID_0008WM6L-20230725-Coupon-USD-Receive</td>\n",
       "      <td>2023-07-25 00:00:00+00:00</td>\n",
       "      <td>817.35</td>\n",
       "      <td>USD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>txn_3-LUID_0008WM6L-20230725-Principal-USD-Rec...</td>\n",
       "      <td>2023-07-25 00:00:00+00:00</td>\n",
       "      <td>12,104.00</td>\n",
       "      <td>USD</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                      transaction_id  \\\n",
       "0    txn_3-LUID_0008WM6L-20230625-Coupon-USD-Receive   \n",
       "1  txn_3-LUID_0008WM6L-20230625-Principal-USD-Rec...   \n",
       "2    txn_3-LUID_0008WM6L-20230725-Coupon-USD-Receive   \n",
       "3  txn_3-LUID_0008WM6L-20230725-Principal-USD-Rec...   \n",
       "\n",
       "            settlement_date     units transaction_currency  \n",
       "0 2023-06-25 00:00:00+00:00  1,677.35                  USD  \n",
       "1 2023-06-25 00:00:00+00:00 20,472.00                  USD  \n",
       "2 2023-07-25 00:00:00+00:00    817.35                  USD  \n",
       "3 2023-07-25 00:00:00+00:00 12,104.00                  USD  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "effective_at_date = datetime.strptime(\"2023-07-15T10:00:00+00:00\",\"%Y-%m-%dT%H:%M:%S%z\")\n",
    "\n",
    "upsertable_cash_flows_portfolio_1_post_sale = transaction_portfolios_api.get_upsertable_portfolio_cash_flows(\n",
    "    scope=scope,\n",
    "    code=portfolio_1_code,\n",
    "    effective_at=effective_at_date,\n",
    "    window_start=window_start_date,\n",
    "    window_end=window_end_date,\n",
    "    recipe_id_scope=scope,\n",
    "    recipe_id_code=recipe_code\n",
    ")\n",
    "lusid_response_portfolio_1_post_sale=lusid_response_to_data_frame(upsertable_cash_flows_portfolio_1_post_sale)\n",
    "\n",
    "lusid_response_portfolio_1_post_sale[[\"transaction_id\", \"settlement_date\", \"units\", \"transaction_currency\"]]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# 7. Valuations\n",
    "\n",
    "In addition from querying future cashflows, we may want to value our portfolio on a daily basis using market quotes and obtain a daily P&L. To do that, we now move from the \"Constant Time Value of Money\" model into the \"Bond Lookup Pricer\" model.\n",
    "\n",
    "The first step is to define appropriate recipes and quote upserting functions."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "quoted_price_data = [[\"US3136AKAD59\", \"2023-07-05\", 100, \"USD\"],\n",
    "                    [\"US3136AKAD59\", \"2023-07-06\", 100.1, \"USD\"],\n",
    "                    [\"US3136AKAD59\", \"2023-07-07\", 99.93, \"USD\"]]\n",
    "bond_quotes = pd.DataFrame(quoted_price_data, columns=[\"ISIN\",\"Date\",\"Price\",\"Currency\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Quote successfully loaded into LUSID. 3 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[\"ISIN\"],\n",
    "                instrument_id_type=\"ClientInternal\",\n",
    "                quote_type=\"Price\",\n",
    "                field=\"mid\",\n",
    "            ),\n",
    "            effective_at=datetime.strptime(row[\"Date\"],\"%Y-%m-%d\").isoformat()+'Z',\n",
    "        ),\n",
    "        metric_value=lm.MetricValue(value=row[\"Price\"], unit=row[\"Currency\"]),\n",
    "        scale_factor=100,\n",
    "    )\n",
    "    for index, row in bond_quotes.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": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a recipe to perform a valuation\n",
    "recipe_code_for_lookup_pricing = \"MBS_LOOKUP_PRICER\"\n",
    "\n",
    "quoted_price_key_rule = 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",
    "configuration_recipe = lm.ConfigurationRecipe(\n",
    "    scope=scope,\n",
    "    code=recipe_code_for_lookup_pricing,\n",
    "    market=lm.MarketContext(\n",
    "        market_rules=[\n",
    "            pool_factor_data_rule,\n",
    "            quoted_price_key_rule\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=\"BondLookupPricer\",\n",
    "                instrument_type=\"ComplexBond\",\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": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_val(date, portfolio_code):\n",
    "\n",
    "    valuation_request = lm.ValuationRequest(\n",
    "        recipe_id=lm.ResourceId(scope=scope, code=recipe_code_for_lookup_pricing),\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 = aggregation_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\": \"ISIN\",\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": [
    "And we complete this demo by showing what is the value of our MBS position on the second day (06-Jul-2023) and the PnL of our portfolio compared to the previous day (05-Jul-2023). The PnL includes accrued interest and market moves."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Present Value</th>\n",
       "      <th>InstrumentName</th>\n",
       "      <th>ISIN</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>380,736.25</td>\n",
       "      <td>FED NATIONAL MTGE ASSOC 2014-33</td>\n",
       "      <td>US3136AKAD59</td>\n",
       "      <td>100.10</td>\n",
       "      <td>4.00</td>\n",
       "      <td>132.02</td>\n",
       "      <td>406.63</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>-400,000.00</td>\n",
       "      <td>USD</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-400,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          ISIN  Quotes/Price  \\\n",
       "0     380,736.25  FED NATIONAL MTGE ASSOC 2014-33  US3136AKAD59        100.10   \n",
       "1    -400,000.00                              USD          None           NaN   \n",
       "\n",
       "   Holding/default/Units  Accrued Interest  PnL (1-day)  \n",
       "0                   4.00            132.02       406.63  \n",
       "1            -400,000.00              0.00         0.00  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(get_val(\"2023-07-06T10:00:00Z\", portfolio_1_code))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Portfolio \"TESTMBS/portfolio_52\" deleted\n",
      "Portfolio \"TESTMBS/portfolio_8252\" deleted\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "1"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Auxiliar code to delete the portfolios created in this notebook to ensure the test environment stays clean\n",
    "\n",
    "def delete_portfolio(portfolio):\n",
    "\n",
    "    try:\n",
    "        portfolios_api.delete_portfolio(\n",
    "            scope=scope,\n",
    "            code=portfolio\n",
    "        )\n",
    "        print(f'Portfolio \"{scope}/{portfolio}\" deleted')\n",
    "        return 1\n",
    "\n",
    "    except lu.ApiException as e:\n",
    "        detail = json.loads(e.body)\n",
    "        if detail[\"code\"] == 109:\n",
    "            print(f'Portfolio \"{scope}/{portfolio}\" did not exist')\n",
    "            return 0\n",
    "        else:\n",
    "            raise e\n",
    "\n",
    "delete_portfolio(portfolio_1_code)\n",
    "delete_portfolio(portfolio_2_code)"
   ]
  }
 ],
 "metadata": {
  "celltoolbar": "Slideshow",
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.9"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {
    "height": "585.99px",
    "width": "307.986px"
   },
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  },
  "varInspector": {
   "cols": {
    "lenName": 16,
    "lenType": 16,
    "lenVar": 40
   },
   "kernels_config": {
    "python": {
     "delete_cmd_postfix": "",
     "delete_cmd_prefix": "del ",
     "library": "var_list.py",
     "varRefreshCmd": "print(var_dic_list())"
    },
    "r": {
     "delete_cmd_postfix": ") ",
     "delete_cmd_prefix": "rm(",
     "library": "var_list.r",
     "varRefreshCmd": "cat(var_dic_list()) "
    }
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": false
  },
  "vscode": {
   "interpreter": {
    "hash": "b85f25b42a0aa57e840f83865f6eb6e0ab82a33c02eb4ca8642dc3b6e659e4b7"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}