examples/use-cases/valuation/Look-through valuation.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "5872a6f9",
   "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",
    "\"\"\"Portfolio look-through in LUSID\n",
    "\n",
    "Shows how to compute the value of a child portfolio's holding as though they were directly held by the parent portfolio.\n",
    "\n",
    "Attributes\n",
    "----------\n",
    "valuations\n",
    "portfolios\n",
    "holdings\n",
    "look through\n",
    "securitised portfolios\n",
    "\"\"\"\n",
    "\n",
    "toggle_code(\"Toggle Docstring\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "43595264",
   "metadata": {},
   "source": [
    "# Look-through valuation"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ee4a67a2",
   "metadata": {},
   "source": [
    "This notebook shows the portfolio look-through functionality in LUSID. The term \"look-through\" typically refers to a situation where the holdings of a portfolio are themselves securitised portfolios. \"Looking through\" the parent portfolio means considering the child portfolio holdings as though they were directly held by the parent.\n",
    "\n",
    "This is useful for monitoring the performance of underlying holdings, or for ensuring that total exposure to an instrument does not exceed a compliance limit. Without a look-through function, the user would have to aggregate the holdings manually to ensure that there are no breaches.\n",
    "\n",
    "In the example that follows, we will load holdings for two child portfolios and add them to a parent portfolio. We then demonstrate how to run valuation on the parent portfolio with and without look-through."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "667db937",
   "metadata": {},
   "source": [
    "Table of contents:\n",
    "- [1. Setup](#1.-Setup)\n",
    "- [2. Prepare child portfolios](#2.-Prepare-child-portfolios)\n",
    "  - [2.1 Load portfolios](#2.1-Load-portfolios)\n",
    "  - [2.2 Securitise portfolios](#2.2-Securitise-portfolios)\n",
    "  - [2.3 Load quotes for holdings](#2.3-Load-quotes-for-holdings)\n",
    "  - [2.4 Load FX rate quotes](#2.4-Load-FX-rate-quotes)\n",
    "- [3. Create parent portfolio](#3.-Create-parent-portfolio-and-recipes-for-valuation)\n",
    "- [4. Run valuation on parent portfolio](#4.-Run-valuation-on-parent-portfolio)\n",
    "    - [4.1 Create valuation recipes](#4.1-Create-valuation-recipes)\n",
    "    - [4.2 Run valuation](#4.2-Run-valuation)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "702f97d5",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7b81dec7",
   "metadata": {},
   "source": [
    "## 1. Setup"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "01cd202d",
   "metadata": {},
   "source": [
    "Before we can begin, we need to import the required libraries and authenticate on LUSID. For guidance on how to authenticate, see [this page](https://support.lusid.com/knowledgebase/article/KA-01916/en-us)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "4bd8900f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style>.container { width:90% !important; }</style>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>api_version</th>\n",
       "      <th>build_version</th>\n",
       "      <th>excel_version</th>\n",
       "      <th>links</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>v0</td>\n",
       "      <td>0.6.11289.0</td>\n",
       "      <td>0.5.3240</td>\n",
       "      <td>{'relation': 'RequestLogs', 'href': 'http://ca...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  api_version build_version excel_version  \\\n",
       "0          v0   0.6.11289.0      0.5.3240   \n",
       "\n",
       "                                               links  \n",
       "0  {'relation': 'RequestLogs', 'href': 'http://ca...  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Import Libraries\n",
    "import os\n",
    "import pandas as pd\n",
    "import json\n",
    "import datetime\n",
    "import pytz\n",
    "\n",
    "from typing import List, Tuple, Dict\n",
    "from IPython.core.display import HTML\n",
    "from datetime import datetime, timedelta\n",
    "\n",
    "# Import LUSID\n",
    "import lusid\n",
    "import lusid.models as models\n",
    "\n",
    "from lusidtools.cocoon import cocoon\n",
    "from lusidjam import RefreshingToken\n",
    "from lusidtools.cocoon.seed_sample_data import seed_data\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",
    "# Authenticate our user and create our API client\n",
    "secrets_path = os.getenv(\"FBN_SECRETS_PATH\")\n",
    "\n",
    "api_factory = lusid.utilities.ApiClientFactory(\n",
    "    token=RefreshingToken(),\n",
    "    api_secrets_filename=secrets_path,\n",
    "    app_name=\"LusidJupyterNotebook\",\n",
    ")\n",
    "\n",
    "api_status = pd.DataFrame(\n",
    "    api_factory.build(lusid.api.ApplicationMetadataApi).get_lusid_versions().to_dict()\n",
    ")\n",
    "\n",
    "display(api_status)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "8de4a70f",
   "metadata": {},
   "outputs": [],
   "source": [
    "scope = \"fundOfFunds\"\n",
    "\n",
    "child_port_data_filepath = r\"data/equity_uk_us_data.csv\"\n",
    "parent_port_data_filepath = r\"data/parent_fund_holdings.csv\"\n",
    "mappings_filepath = r\"config/upload_config.json\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "7c872800",
   "metadata": {},
   "outputs": [],
   "source": [
    "quotes_api = api_factory.build(lusid.api.QuotesApi)\n",
    "configuration_recipe_api = api_factory.build(lusid.api.ConfigurationRecipeApi)\n",
    "instruments_api = api_factory.build(lusid.api.InstrumentsApi)\n",
    "aggregation_api = api_factory.build(lusid.AggregationApi)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "26ac3623",
   "metadata": {},
   "source": [
    "## 2. Prepare child portfolios"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3874fb10",
   "metadata": {},
   "source": [
    "### 2.1 Load portfolios"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fc6e05a6",
   "metadata": {},
   "source": [
    "We'll start by loading the data for our two child portfolios, one containing UK equities and another containing US equities.\n",
    "\n",
    "To do so, we need two things: the data itself, and a specification of how to map it onto LUSID's [Data Model](https://www.lusid.com/docs/api/#section/Data-Model). The latter is typically provided via a mappings file that contains information about which identifiers and properties are being loaded for each entity (`Portfolio`, `Instrument`, `Transaction`)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "bfe9ffce",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>portfolio_code</th>\n",
       "      <th>portfolio_name</th>\n",
       "      <th>portfolio_base_currency</th>\n",
       "      <th>ticker</th>\n",
       "      <th>sedol</th>\n",
       "      <th>instrument_type</th>\n",
       "      <th>instrument_id</th>\n",
       "      <th>name</th>\n",
       "      <th>txn_id</th>\n",
       "      <th>txn_type</th>\n",
       "      <th>txn_trade_date</th>\n",
       "      <th>txn_settle_date</th>\n",
       "      <th>txn_units</th>\n",
       "      <th>txn_price</th>\n",
       "      <th>txn_consideration</th>\n",
       "      <th>currency</th>\n",
       "      <th>sector</th>\n",
       "      <th>cash_transactions</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ukEquityPortfolio</td>\n",
       "      <td>UK Active Equity Portfolio</td>\n",
       "      <td>GBP</td>\n",
       "      <td>BT.A</td>\n",
       "      <td>SEDOL1</td>\n",
       "      <td>equity</td>\n",
       "      <td>EQ_1234</td>\n",
       "      <td>BT GROUP PLC</td>\n",
       "      <td>trd_0001</td>\n",
       "      <td>StockIn</td>\n",
       "      <td>02/01/2020</td>\n",
       "      <td>04/01/2020</td>\n",
       "      <td>60000</td>\n",
       "      <td>1</td>\n",
       "      <td>60000</td>\n",
       "      <td>GBP</td>\n",
       "      <td>media</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ukEquityPortfolio</td>\n",
       "      <td>UK Active Equity Portfolio</td>\n",
       "      <td>GBP</td>\n",
       "      <td>STAN</td>\n",
       "      <td>SEDOL2</td>\n",
       "      <td>equity</td>\n",
       "      <td>EQ_1235</td>\n",
       "      <td>STANDARD CHARTERED PLC</td>\n",
       "      <td>trd_0002</td>\n",
       "      <td>StockIn</td>\n",
       "      <td>02/01/2020</td>\n",
       "      <td>04/01/2020</td>\n",
       "      <td>100000</td>\n",
       "      <td>1</td>\n",
       "      <td>100000</td>\n",
       "      <td>GBP</td>\n",
       "      <td>financial</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      portfolio_code              portfolio_name portfolio_base_currency  \\\n",
       "0  ukEquityPortfolio  UK Active Equity Portfolio                     GBP   \n",
       "1  ukEquityPortfolio  UK Active Equity Portfolio                     GBP   \n",
       "\n",
       "  ticker   sedol instrument_type instrument_id                    name  \\\n",
       "0   BT.A  SEDOL1          equity       EQ_1234            BT GROUP PLC   \n",
       "1   STAN  SEDOL2          equity       EQ_1235  STANDARD CHARTERED PLC   \n",
       "\n",
       "     txn_id txn_type txn_trade_date txn_settle_date  txn_units  txn_price  \\\n",
       "0  trd_0001  StockIn     02/01/2020      04/01/2020      60000          1   \n",
       "1  trd_0002  StockIn     02/01/2020      04/01/2020     100000          1   \n",
       "\n",
       "   txn_consideration currency     sector cash_transactions  \n",
       "0              60000      GBP      media               NaN  \n",
       "1             100000      GBP  financial               NaN  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "child_portfolio_data = pd.read_csv(child_port_data_filepath)\n",
    "\n",
    "child_portfolio_data.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "a4fdb233",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Process a mapping file for loading data\n",
    "with open(mappings_filepath) as mappings_file:\n",
    "    seed_data_mapping = json.load(mappings_file)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "8a9e6646",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load the portfolio data using the mappings provided\n",
    "seed_data_response = seed_data(\n",
    "    api_factory,\n",
    "    [\"portfolios\", \"instruments\", \"transactions\"],\n",
    "    scope,\n",
    "    child_portfolio_data,\n",
    "    \"DataFrame\",\n",
    "    mappings=seed_data_mapping,\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4dcc1347",
   "metadata": {},
   "source": [
    "### 2.2 Securitise portfolios"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "37430d1e",
   "metadata": {},
   "source": [
    "In order for the child portfolios to be considered as holdings in a parent portfolio, they must first be securitised. In LUSID, this corresponds to registering them as `Instruments` linked to a portfolio, which we can do using the [Instruments API](https://www.lusid.com/docs/api/#tag/Instruments). We register these instruments with the `SimpleInstrument` instrument type, and we will later instruct LUSID how to treat these instruments using a [Configuration Recipes](https://support.lusid.com/knowledgebase/article/KA-01895)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "7938b520",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('ukEquityPortfolio', 'ukEquityPortfolioInstrumentCode'),\n",
       " ('usEquityPortfolio', 'usEquityPortfolioInstrumentCode')]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "portfolios = child_portfolio_data[\"portfolio_code\"].unique()\n",
    "\n",
    "# Creating IDs for the securitised portfolios\n",
    "securitised_portfolio_ids = [port + \"InstrumentCode\" for port in portfolios]\n",
    "securitised_portfolios = list(zip(portfolios, securitised_portfolio_ids))\n",
    "\n",
    "securitised_portfolios"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "07572e1b",
   "metadata": {},
   "outputs": [],
   "source": [
    "equity = models.SimpleInstrument(\n",
    "    instrument_type=\"SimpleInstrument\",\n",
    "    dom_ccy=\"USD\",\n",
    "    asset_class=\"Equities\",\n",
    "    simple_instrument_type=\"EquityIndex\"\n",
    ")\n",
    "\n",
    "# Insert the two new instruments into LUSID\n",
    "for port, instr_id in securitised_portfolios:\n",
    "    instr_result = instruments_api.upsert_instruments(\n",
    "        request_body={\n",
    "            \"look_through\": models.InstrumentDefinition(\n",
    "                name=port,\n",
    "                identifiers={\n",
    "                    \"ClientInternal\": models.InstrumentIdValue(value=instr_id)\n",
    "                },\n",
    "                definition=equity,\n",
    "                look_through_portfolio_id=models.ResourceId(scope=scope, code=port),\n",
    "            )\n",
    "        }\n",
    "    )"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fa345fb9",
   "metadata": {},
   "source": [
    "### 2.3 Load quotes for holdings"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6c0e0a84",
   "metadata": {},
   "source": [
    "Next, we want to generate price quotes for:\n",
    "- each holding in the child portfolio;\n",
    "- the securitised child portfolios;\n",
    "- USD and GBP FX rates, since we have a mix of US and UK equities;\n",
    "\n",
    "We need these quotes so we can run valuation on the portfolios later.\n",
    "\n",
    "Quotes are added through calls to the `UpsertQuotes` end-point of the LUSID [Quotes API](https://www.lusid.com/docs/api/#tag/Quotes). For our example, we'll generate dummy price quotes for the first 90 days of 2021, although typically quotes would be sourced from a market data provider."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "449d4ed5",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['2021-01-01', '2021-01-02', '2021-01-03', '2021-01-04', '2021-01-05']"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "start_date = datetime(year=2021, month=1, day=1)\n",
    "num_of_days = 90\n",
    "\n",
    "# Generate dates for the first 90 days of the current year in YYYY-mm-dd format\n",
    "days = [\n",
    "    (start_date + timedelta(days=x)).strftime(format=\"%Y-%m-%d\")\n",
    "    for x in range(num_of_days)\n",
    "]\n",
    "\n",
    "days[:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "7f9c15dd",
   "metadata": {},
   "outputs": [],
   "source": [
    "def generate_quotes(\n",
    "    instruments_prices: List[Tuple[str, int, str]],\n",
    "    instrument_id_type: str,\n",
    "    quote_type: str,\n",
    "    pricing_type: str,\n",
    ") -> Dict:\n",
    "    \"\"\"Generates price quotes compatible with the `UpsertQuotes` endpoint.\n",
    "\n",
    "    Args:\n",
    "        instruments_prices (List[Tuple[str, int, str]]): list of tuples containing instrument ID, price and currency\n",
    "        instrument_id_type (str): type of instrument ID to generate a QuoteSeriesId for\n",
    "        quote_type (str): type of quote to generate\n",
    "        pricing_type (str): \"Static\" pricing requests quote with the same price across all 90 days.\n",
    "        \"Dynamic\" pricing starts with a base price of 10 and increases it by 5% each day.\n",
    "\n",
    "    Returns:\n",
    "        Dict: quotes to upsert\n",
    "    \"\"\"\n",
    "\n",
    "    quotes_for_upsert = {}\n",
    "\n",
    "    for instr_id, price, currency in instruments_prices:\n",
    "\n",
    "        if pricing_type == \"Static\":\n",
    "            prices = [price] * len(days)\n",
    "        elif pricing_type == \"Dynamic\":\n",
    "            prices = [10 + (x * 0.05) for x in range(num_of_days)]\n",
    "        else:\n",
    "            raise ValueError(\"Unknown pricing type. Should be 'static' or 'dynamic'\")\n",
    "\n",
    "        daily_prices = tuple(zip(days, prices))\n",
    "\n",
    "        for date, price in daily_prices:\n",
    "\n",
    "            # Generate the quote\n",
    "            quotes_for_upsert[\n",
    "                \"quotes_request_\" + instr_id + \"_\" + date.replace(\"-\", \"\")\n",
    "            ] = models.UpsertQuoteRequest(\n",
    "                quote_id=models.QuoteId(\n",
    "                    quote_series_id=models.QuoteSeriesId(\n",
    "                        provider=\"Lusid\",\n",
    "                        instrument_id=instr_id,\n",
    "                        instrument_id_type=instrument_id_type,\n",
    "                        quote_type=quote_type,\n",
    "                        field=\"mid\",\n",
    "                    ),\n",
    "                    effective_at=date,\n",
    "                ),\n",
    "                metric_value=models.MetricValue(value=price, unit=currency),\n",
    "            )\n",
    "\n",
    "    return quotes_for_upsert"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "15574715",
   "metadata": {},
   "outputs": [],
   "source": [
    "quotes_for_upsert = {}\n",
    "holdings_prices = []\n",
    "\n",
    "for index, row in child_portfolio_data[[\"instrument_id\", \"currency\"]].iterrows():\n",
    "    holdings_prices.append((row[\"instrument_id\"], 1, row[\"currency\"]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "c96433ff",
   "metadata": {},
   "outputs": [],
   "source": [
    "quotes_for_upsert = generate_quotes(\n",
    "    instruments_prices=holdings_prices,\n",
    "    instrument_id_type=\"ClientInternal\",\n",
    "    quote_type=\"Price\",\n",
    "    pricing_type=\"Dynamic\",\n",
    ")\n",
    "\n",
    "# Make API call to upsert the holdings' quotes\n",
    "quote_response = quotes_api.upsert_quotes(scope=scope, request_body=quotes_for_upsert)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8fc4cd24",
   "metadata": {},
   "source": [
    "For the securitised portfolios, we use static prices of £1,000 and $1,000, respectively. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "10251013",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('ukEquityPortfolioInstrumentCode', 1000, 'GBP'),\n",
       " ('usEquityPortfolioInstrumentCode', 1000, 'USD')]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "port_prices = [1000, 1000]\n",
    "port_currencies = [\"GBP\", \"USD\"]\n",
    "child_port_prices = list(zip(securitised_portfolio_ids, port_prices, port_currencies))\n",
    "\n",
    "child_port_prices"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "6a24e0fa",
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "quotes_for_upsert = generate_quotes(\n",
    "    instruments_prices=child_port_prices,\n",
    "    instrument_id_type=\"ClientInternal\",\n",
    "    quote_type=\"Price\",\n",
    "    pricing_type=\"Static\",\n",
    ")\n",
    "\n",
    "# Make API call to upsert the securitised portfolios' quotes\n",
    "quote_response = quotes_api.upsert_quotes(scope=scope, request_body=quotes_for_upsert)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f7028fcc",
   "metadata": {},
   "source": [
    "### 2.4 Load FX rate quotes"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "138fbd63",
   "metadata": {},
   "source": [
    "We're also going to load in FX rate quotes for GBP/EUR and GBP/USD. This works similarly to the quote generation above, except we load a `Rate` instead of a `Price`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "bf3e89c4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('GBP/EUR', 1.1, 'GBP'), ('GBP/USD', 1.3, 'GBP')]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fx_rates = [1.1, 1.3]\n",
    "ccy_pairs = [\"GBP/EUR\", \"GBP/USD\"]\n",
    "base_currency = [ccy[:3] for ccy in ccy_pairs]\n",
    "\n",
    "pairs_rates = list(zip(ccy_pairs, fx_rates, base_currency))\n",
    "\n",
    "pairs_rates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "eddbcca9",
   "metadata": {},
   "outputs": [],
   "source": [
    "quotes_for_upsert = generate_quotes(\n",
    "    instruments_prices=pairs_rates,\n",
    "    instrument_id_type=\"CurrencyPair\",\n",
    "    quote_type=\"Price\",\n",
    "    pricing_type=\"Static\",\n",
    ")\n",
    "\n",
    "# Make API call to upsert FX rate quotes\n",
    "quote_response = quotes_api.upsert_quotes(scope=scope, request_body=quotes_for_upsert)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ced7acec",
   "metadata": {},
   "source": [
    "## 3. Create parent portfolio"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b3d580c2",
   "metadata": {},
   "source": [
    "Having securitised the two child portfolios and added price quotes, let's create the parent portfolio and load the former as holdings."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "01ee14ae",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>portfolio_code</th>\n",
       "      <th>portfolio_name</th>\n",
       "      <th>portfolio_base_currency</th>\n",
       "      <th>ticker</th>\n",
       "      <th>sedol</th>\n",
       "      <th>instrument_type</th>\n",
       "      <th>instrument_id</th>\n",
       "      <th>name</th>\n",
       "      <th>txn_id</th>\n",
       "      <th>txn_type</th>\n",
       "      <th>txn_trade_date</th>\n",
       "      <th>txn_settle_date</th>\n",
       "      <th>txn_units</th>\n",
       "      <th>txn_price</th>\n",
       "      <th>txn_consideration</th>\n",
       "      <th>currency</th>\n",
       "      <th>sector</th>\n",
       "      <th>cash_transactions</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>FundOfHedgeFunds</td>\n",
       "      <td>Fund of fund for Hedge Funds</td>\n",
       "      <td>EUR</td>\n",
       "      <td>ukEquityPortfolioInstrumentCode</td>\n",
       "      <td>ukEquityPortfolioInstrumentCode</td>\n",
       "      <td>equityFund</td>\n",
       "      <td>ukEquityPortfolioInstrumentCode</td>\n",
       "      <td>ukEquityPortfolioInstrumentCode</td>\n",
       "      <td>trd_0001</td>\n",
       "      <td>StockIn</td>\n",
       "      <td>02/01/2020</td>\n",
       "      <td>04/01/2020</td>\n",
       "      <td>1000</td>\n",
       "      <td>1</td>\n",
       "      <td>1000</td>\n",
       "      <td>GBP</td>\n",
       "      <td>funds</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>FundOfHedgeFunds</td>\n",
       "      <td>Fund of fund for Hedge Funds</td>\n",
       "      <td>EUR</td>\n",
       "      <td>usEquityPortfolioInstrumentCode</td>\n",
       "      <td>usEquityPortfolioInstrumentCode</td>\n",
       "      <td>equityFund</td>\n",
       "      <td>usEquityPortfolioInstrumentCode</td>\n",
       "      <td>usEquityPortfolioInstrumentCode</td>\n",
       "      <td>trd_0002</td>\n",
       "      <td>StockIn</td>\n",
       "      <td>02/01/2020</td>\n",
       "      <td>04/01/2020</td>\n",
       "      <td>1000</td>\n",
       "      <td>1</td>\n",
       "      <td>1000</td>\n",
       "      <td>USD</td>\n",
       "      <td>funds</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     portfolio_code                portfolio_name portfolio_base_currency  \\\n",
       "0  FundOfHedgeFunds  Fund of fund for Hedge Funds                     EUR   \n",
       "1  FundOfHedgeFunds  Fund of fund for Hedge Funds                     EUR   \n",
       "\n",
       "                            ticker                            sedol  \\\n",
       "0  ukEquityPortfolioInstrumentCode  ukEquityPortfolioInstrumentCode   \n",
       "1  usEquityPortfolioInstrumentCode  usEquityPortfolioInstrumentCode   \n",
       "\n",
       "  instrument_type                    instrument_id  \\\n",
       "0      equityFund  ukEquityPortfolioInstrumentCode   \n",
       "1      equityFund  usEquityPortfolioInstrumentCode   \n",
       "\n",
       "                              name    txn_id txn_type txn_trade_date  \\\n",
       "0  ukEquityPortfolioInstrumentCode  trd_0001  StockIn     02/01/2020   \n",
       "1  usEquityPortfolioInstrumentCode  trd_0002  StockIn     02/01/2020   \n",
       "\n",
       "  txn_settle_date  txn_units  txn_price  txn_consideration currency sector  \\\n",
       "0      04/01/2020       1000          1               1000      GBP  funds   \n",
       "1      04/01/2020       1000          1               1000      USD  funds   \n",
       "\n",
       "   cash_transactions  \n",
       "0                NaN  \n",
       "1                NaN  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "parent_portfolio_code = \"FundOfHedgeFunds\"\n",
    "parent_portfolio_data = pd.read_csv(parent_port_data_filepath)\n",
    "\n",
    "parent_portfolio_data.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "2ae13397",
   "metadata": {},
   "outputs": [],
   "source": [
    "seed_data_response = seed_data(\n",
    "    api_factory,\n",
    "    [\"portfolios\", \"transactions\"],\n",
    "    scope,\n",
    "    parent_portfolio_data,\n",
    "    \"DataFrame\",\n",
    "    mappings=seed_data_mapping,\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "44eacd8c",
   "metadata": {},
   "source": [
    "## 4. Run valuation on parent portfolio"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "36cb9c20",
   "metadata": {},
   "source": [
    "Now that the data is ready, let's value the parent portfolio. To run valuation, we need a set of holdings with price quotes (which we have) and a valuation recipe."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "64e97187",
   "metadata": {},
   "source": [
    "### 4.1 Create valuation recipes\n",
    "\n",
    "[Recipes](https://support.lusid.com/knowledgebase/article/KA-01895) are sets of instructions for how the valuation is to be calculated. For more information about their usage in LUSID, see [this article](https://support.lusid.com/knowledgebase/article/KA-01896/en-us) in the documentation.\n",
    "\n",
    "We'll create one recipe with look-through enabled, and one without. We'll then feed these into a function to generate a valuation request, and have LUSID run the valuation itself. The model rule in the lookthrough-enabled recipe instructs LUSID to price `SimpleInstrument`s with the `SimpleStatic` (i.e. quote lookup) model, and the `Sum` portfolio scaling option causes those instruments to expand themselves into a number of copies of their lookthrough portfolios that preserves the correct total value."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "310fc3b3",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create look-through-enabled recipe\n",
    "lookthrough_config_recipe = models.ConfigurationRecipe(\n",
    "    scope=scope,\n",
    "    code=\"lookthrough\",\n",
    "    market=models.MarketContext(\n",
    "        market_rules=[\n",
    "            models.MarketDataKeyRule(\n",
    "                key=\"Quote.*.*\",\n",
    "                supplier=\"Lusid\",\n",
    "                data_scope=scope,\n",
    "                quote_type=\"Price\",\n",
    "                field=\"mid\",\n",
    "            ),\n",
    "            models.MarketDataKeyRule(\n",
    "                key=\"FX.CurrencyPair.*\",\n",
    "                supplier=\"Lusid\",\n",
    "                data_scope=scope,\n",
    "                quote_type=\"Rate\",\n",
    "                field=\"mid\",\n",
    "            ),\n",
    "        ],\n",
    "        options=models.MarketOptions(\n",
    "            default_supplier=\"Lusid\",\n",
    "            default_instrument_code_type=\"ClientInternal\",\n",
    "            default_scope=scope,\n",
    "            attempt_to_infer_missing_fx=True,\n",
    "        ),\n",
    "    ),\n",
    "    pricing=models.PricingContext(\n",
    "        options={\"AllowPartiallySuccessfulEvaluation\": False},\n",
    "        # toggle look through\n",
    "        model_rules=[\n",
    "            models.VendorModelRule(\n",
    "                supplier=\"Lusid\",\n",
    "                model_name=\"SimpleStatic\",\n",
    "                instrument_type=\"SimpleInstrument\",\n",
    "                model_options=models.IndexModelOptions(portfolio_scaling=\"Sum\", model_options_type=\"IndexModelOptions\"),\n",
    "            )\n",
    "        ],\n",
    "    ),\n",
    ")\n",
    "\n",
    "upsert_configuration_recipe_response = (\n",
    "    configuration_recipe_api.upsert_configuration_recipe(\n",
    "        upsert_recipe_request=models.UpsertRecipeRequest(\n",
    "            configuration_recipe=lookthrough_config_recipe\n",
    "        )\n",
    "    )\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "453d741d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a non-look-through recipe\n",
    "non_lookthrough_config_recipe = models.ConfigurationRecipe(\n",
    "    scope=scope,\n",
    "    code=\"no-lookthrough\",\n",
    "    market=models.MarketContext(\n",
    "        market_rules=[\n",
    "            models.MarketDataKeyRule(\n",
    "                key=\"Quote.ClientInternal.*\",\n",
    "                supplier=\"Lusid\",\n",
    "                data_scope=scope,\n",
    "                quote_type=\"Price\",\n",
    "                field=\"mid\",\n",
    "            ),\n",
    "            models.MarketDataKeyRule(\n",
    "                key=\"FX.CurrencyPair.*\",\n",
    "                supplier=\"Lusid\",\n",
    "                data_scope=scope,\n",
    "                quote_type=\"Rate\",\n",
    "                field=\"mid\",\n",
    "            ),\n",
    "        ],\n",
    "        options=models.MarketOptions(\n",
    "            default_supplier=\"Lusid\",\n",
    "            default_instrument_code_type=\"ClientInternal\",\n",
    "            default_scope=scope,\n",
    "            attempt_to_infer_missing_fx=True,\n",
    "        ),\n",
    "    ),\n",
    "    pricing=models.PricingContext(\n",
    "        options={\"AllowPartiallySuccessfulEvaluation\": False},\n",
    "        # toggle look through\n",
    "        model_rules=[\n",
    "            models.VendorModelRule(\n",
    "                supplier=\"Lusid\",\n",
    "                model_name=\"SimpleStatic\",\n",
    "                instrument_type=\"SimpleInstrument\",\n",
    "            )\n",
    "        ],\n",
    "    ),\n",
    ")\n",
    "\n",
    "upsert_configuration_recipe_response = (\n",
    "    configuration_recipe_api.upsert_configuration_recipe(\n",
    "        upsert_recipe_request=models.UpsertRecipeRequest(\n",
    "            configuration_recipe=non_lookthrough_config_recipe\n",
    "        )\n",
    "    )\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "15b3dc14",
   "metadata": {},
   "source": [
    "### 4.2 Run valuation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "56e955fd",
   "metadata": {},
   "outputs": [],
   "source": [
    "def generate_valuation_request(\n",
    "    valuation_effective_at: datetime, recipe_code: str\n",
    ") -> lusid.models.valuation_request.ValuationRequest:\n",
    "    \"\"\"Generate a valuation request compatible with the `GetValuation` endpoint.\n",
    "\n",
    "    Args:\n",
    "        valuation_effective_at (datetime): effective date for the valuation\n",
    "        recipe_code (string): ID of the recipe to be used in the valuation\n",
    "\n",
    "    Returns:\n",
    "        valuation_request (lusid.models.valuation_request.ValuationRequest): a LUSID valuation request\n",
    "    \"\"\"\n",
    "    # Create the valuation request\n",
    "    valuation_request = models.ValuationRequest(\n",
    "        recipe_id=models.ResourceId(scope=scope, code=recipe_code),\n",
    "        metrics=[\n",
    "            models.AggregateSpec(\"Instrument/default/Name\", \"Value\"),\n",
    "            models.AggregateSpec(\"Valuation/PvInReportCcy\", \"Proportion\"),\n",
    "            models.AggregateSpec(\"Valuation/PvInReportCcy\", \"Sum\"),\n",
    "            models.AggregateSpec(\"Holding/default/Units\", \"Sum\"),\n",
    "            models.AggregateSpec(\"Holding/DomCcy\", \"Value\"),\n",
    "        ],\n",
    "        group_by=[\"Instrument/default/Name\"],\n",
    "        portfolio_entity_ids=[\n",
    "            models.PortfolioEntityId(scope=scope, code=parent_portfolio_code)\n",
    "        ],\n",
    "        valuation_schedule=models.ValuationSchedule(\n",
    "            effective_at=valuation_effective_at.isoformat()\n",
    "        ),\n",
    "        report_currency=\"EUR\"\n",
    "    )\n",
    "\n",
    "    return valuation_request"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c6f6b250",
   "metadata": {},
   "source": [
    "Without look-through, the only holdings in the parent fund are the two securitised child portfolios:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "09c42ff9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Instrument/default/Name</th>\n",
       "      <th>Proportion(Valuation/PvInReportCcy)</th>\n",
       "      <th>Sum(Valuation/PvInReportCcy)</th>\n",
       "      <th>Sum(Holding/default/Units)</th>\n",
       "      <th>Holding/DomCcy</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ukEquityPortfolio</td>\n",
       "      <td>0.57</td>\n",
       "      <td>1,100,000.00</td>\n",
       "      <td>1,000.00</td>\n",
       "      <td>GBP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>usEquityPortfolio</td>\n",
       "      <td>0.43</td>\n",
       "      <td>846,153.85</td>\n",
       "      <td>1,000.00</td>\n",
       "      <td>USD</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Instrument/default/Name  Proportion(Valuation/PvInReportCcy)  \\\n",
       "0       ukEquityPortfolio                                 0.57   \n",
       "1       usEquityPortfolio                                 0.43   \n",
       "\n",
       "   Sum(Valuation/PvInReportCcy)  Sum(Holding/default/Units) Holding/DomCcy  \n",
       "0                  1,100,000.00                    1,000.00            GBP  \n",
       "1                    846,153.85                    1,000.00            USD  "
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "aggregation = aggregation_api.get_valuation(\n",
    "    valuation_request=generate_valuation_request(\n",
    "        datetime(year=2021, month=3, day=30, tzinfo=pytz.UTC), \"no-lookthrough\"\n",
    "    )\n",
    ")\n",
    "\n",
    "pd.DataFrame(aggregation.data)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "880e1336",
   "metadata": {},
   "source": [
    "With look-through, we can see the holdings of each of the child portfolios:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "57bde54e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Instrument/default/Name</th>\n",
       "      <th>Proportion(Valuation/PvInReportCcy)</th>\n",
       "      <th>Sum(Valuation/PvInReportCcy)</th>\n",
       "      <th>Sum(Holding/default/Units)</th>\n",
       "      <th>Holding/DomCcy</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>BT GROUP PLC</td>\n",
       "      <td>0.05</td>\n",
       "      <td>99,894.89</td>\n",
       "      <td>6,306.50</td>\n",
       "      <td>GBP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>STANDARD CHARTERED PLC</td>\n",
       "      <td>0.09</td>\n",
       "      <td>166,491.49</td>\n",
       "      <td>10,510.83</td>\n",
       "      <td>GBP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>J SAINSBURY PLC</td>\n",
       "      <td>0.06</td>\n",
       "      <td>116,544.04</td>\n",
       "      <td>7,357.58</td>\n",
       "      <td>GBP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>BARCLAYS PLC</td>\n",
       "      <td>0.03</td>\n",
       "      <td>49,947.45</td>\n",
       "      <td>3,153.25</td>\n",
       "      <td>GBP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>BP PLC</td>\n",
       "      <td>0.07</td>\n",
       "      <td>133,193.19</td>\n",
       "      <td>8,408.66</td>\n",
       "      <td>GBP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>GLAXOSMITHKLINE PLC</td>\n",
       "      <td>0.09</td>\n",
       "      <td>166,491.49</td>\n",
       "      <td>10,510.83</td>\n",
       "      <td>GBP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>BURBERRY GROUP PLC</td>\n",
       "      <td>0.09</td>\n",
       "      <td>166,491.49</td>\n",
       "      <td>10,510.83</td>\n",
       "      <td>GBP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>OCADO GROUP PLC</td>\n",
       "      <td>0.09</td>\n",
       "      <td>166,491.49</td>\n",
       "      <td>10,510.83</td>\n",
       "      <td>GBP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>NEXT PLC</td>\n",
       "      <td>0.02</td>\n",
       "      <td>33,298.30</td>\n",
       "      <td>2,102.17</td>\n",
       "      <td>GBP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>GBP</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1,156.19</td>\n",
       "      <td>1,051.08</td>\n",
       "      <td>GBP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>3M COMPANY</td>\n",
       "      <td>0.05</td>\n",
       "      <td>98,541.17</td>\n",
       "      <td>8,087.34</td>\n",
       "      <td>USD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>ABBOTT LABS</td>\n",
       "      <td>0.06</td>\n",
       "      <td>110,858.81</td>\n",
       "      <td>9,098.26</td>\n",
       "      <td>USD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>AMERICAN AIRLINES GROUP</td>\n",
       "      <td>0.01</td>\n",
       "      <td>24,635.29</td>\n",
       "      <td>2,021.84</td>\n",
       "      <td>USD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>AMERICAN EXPRESS</td>\n",
       "      <td>0.03</td>\n",
       "      <td>49,270.58</td>\n",
       "      <td>4,043.67</td>\n",
       "      <td>USD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>GENERAL MILLS</td>\n",
       "      <td>0.06</td>\n",
       "      <td>123,176.46</td>\n",
       "      <td>10,109.18</td>\n",
       "      <td>USD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>KELLOGG</td>\n",
       "      <td>0.06</td>\n",
       "      <td>123,176.46</td>\n",
       "      <td>10,109.18</td>\n",
       "      <td>USD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>CHEVRON CORP</td>\n",
       "      <td>0.07</td>\n",
       "      <td>135,494.11</td>\n",
       "      <td>11,120.10</td>\n",
       "      <td>USD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>EXXON MOBIL CORP</td>\n",
       "      <td>0.03</td>\n",
       "      <td>49,270.58</td>\n",
       "      <td>4,043.67</td>\n",
       "      <td>USD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>WALMART</td>\n",
       "      <td>0.06</td>\n",
       "      <td>123,176.46</td>\n",
       "      <td>10,109.18</td>\n",
       "      <td>USD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>USD</td>\n",
       "      <td>0.00</td>\n",
       "      <td>8,553.92</td>\n",
       "      <td>10,109.18</td>\n",
       "      <td>USD</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Instrument/default/Name  Proportion(Valuation/PvInReportCcy)  \\\n",
       "0              BT GROUP PLC                                 0.05   \n",
       "1    STANDARD CHARTERED PLC                                 0.09   \n",
       "2           J SAINSBURY PLC                                 0.06   \n",
       "3              BARCLAYS PLC                                 0.03   \n",
       "4                    BP PLC                                 0.07   \n",
       "5       GLAXOSMITHKLINE PLC                                 0.09   \n",
       "6        BURBERRY GROUP PLC                                 0.09   \n",
       "7           OCADO GROUP PLC                                 0.09   \n",
       "8                  NEXT PLC                                 0.02   \n",
       "9                       GBP                                 0.00   \n",
       "10               3M COMPANY                                 0.05   \n",
       "11              ABBOTT LABS                                 0.06   \n",
       "12  AMERICAN AIRLINES GROUP                                 0.01   \n",
       "13         AMERICAN EXPRESS                                 0.03   \n",
       "14            GENERAL MILLS                                 0.06   \n",
       "15                  KELLOGG                                 0.06   \n",
       "16             CHEVRON CORP                                 0.07   \n",
       "17         EXXON MOBIL CORP                                 0.03   \n",
       "18                  WALMART                                 0.06   \n",
       "19                      USD                                 0.00   \n",
       "\n",
       "    Sum(Valuation/PvInReportCcy)  Sum(Holding/default/Units) Holding/DomCcy  \n",
       "0                      99,894.89                    6,306.50            GBP  \n",
       "1                     166,491.49                   10,510.83            GBP  \n",
       "2                     116,544.04                    7,357.58            GBP  \n",
       "3                      49,947.45                    3,153.25            GBP  \n",
       "4                     133,193.19                    8,408.66            GBP  \n",
       "5                     166,491.49                   10,510.83            GBP  \n",
       "6                     166,491.49                   10,510.83            GBP  \n",
       "7                     166,491.49                   10,510.83            GBP  \n",
       "8                      33,298.30                    2,102.17            GBP  \n",
       "9                       1,156.19                    1,051.08            GBP  \n",
       "10                     98,541.17                    8,087.34            USD  \n",
       "11                    110,858.81                    9,098.26            USD  \n",
       "12                     24,635.29                    2,021.84            USD  \n",
       "13                     49,270.58                    4,043.67            USD  \n",
       "14                    123,176.46                   10,109.18            USD  \n",
       "15                    123,176.46                   10,109.18            USD  \n",
       "16                    135,494.11                   11,120.10            USD  \n",
       "17                     49,270.58                    4,043.67            USD  \n",
       "18                    123,176.46                   10,109.18            USD  \n",
       "19                      8,553.92                   10,109.18            USD  "
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "aggregation = aggregation_api.get_valuation(\n",
    "    valuation_request=generate_valuation_request(\n",
    "        datetime(year=2021, month=3, day=30, tzinfo=pytz.UTC), \"lookthrough\"\n",
    "    )\n",
    ")\n",
    "\n",
    "pd.DataFrame(aggregation.data)"
   ]
  }
 ],
 "metadata": {
  "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.9.7"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": false,
   "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": "2631b36fd78b5e9ba2bda367fd8deaf1b7c0a55746e49610a3543f723e389a55"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}