examples/use-cases/valuation/Futures Valuation with Differing Cost Basis Treatments (Synthetic Cash Method).ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 6,
   "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",
    "\"\"\"Futures Valuation Workflow\n",
    "\n",
    "Attributes\n",
    "----------\n",
    "futures\n",
    "transaction types\n",
    "recipes\n",
    "valuations\n",
    "\"\"\"\n",
    "\n",
    "toggle_code(\"Toggle Docstring\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Computing Intraday P&L for Futures with Synthetic Cash\n",
    "\n",
    "In this notebook, we demonstrate how P&L can be computed for Futures instruments under two different cash accounting treatments using the Synthetic Cash method. We look at these treatments over the course of three days for both a Dec 21 and Mar 22 Bund Futures contract. Note that we maintain the same market quotes and transaction amounts for each Futures contract to more easily demonstrate how the PV and P&L measures evolve over time.\n",
    "\n",
    "### Synthetic Cash Method\n",
    "The Synthetic Cash method of modelling Futures contracts introduces two key differences from the standard model:\n",
    "\n",
    "1. The PVs of our Futures positions are defined as the market exposure of the position rather than the unrealized P&L of the position.\n",
    "2. A synthetic cash position is created upon purchase or sale of a Futures contract such that the aggregate PV of the Future and cash position net to 0 for a given transaction.\n",
    "\n",
    "### Dec 21 Bund Futures with Unrealized P&L (‘non-close out’)\n",
    "In our first example, we look at a Futures contract whereby its daily P&L is left as unrealised on the position as opposed to marked-to-market on a daily basis.\n",
    "\n",
    "### Mar 22 Bund Futures with Realized P&L ('close out’)\n",
    "In our second example, we look at a Futures contract whereby the previous day's P&L is realized at the beginning of each day and booked down into a separate cash line item. We then reset the cost basis of our holding to reflect this mark-to-market such that the computed PV at the start of each day is 0."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Table of Contents\n",
    "* [1. Create Portfolio](#-Create-Portfolio)\n",
    "* [2. Create Properties](#-Create-Properties)\n",
    "* [3. Create Futures](#-Create-Futures)\n",
    "* [4. Create Transactions](#-Transactions)\n",
    "* [5. Quotes](#-Quotes)\n",
    "* [6. Valuations](#-Valuations)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style>.container { width:90% !important; }</style>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Import generic non-LUSID packages\n",
    "import os\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "from datetime import datetime\n",
    "import json\n",
    "import pytz\n",
    "from IPython.core.display import HTML\n",
    "\n",
    "# Import key modules from the LUSID package\n",
    "import lusid\n",
    "import lusid.models as models\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",
    "from lusidtools.cocoon.utilities import create_scope_id\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",
    "# Create API client\n",
    "api_factory = lusid.utilities.ApiClientFactory(\n",
    "    token=RefreshingToken(),\n",
    "    app_name=\"futuresLoader\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# LUSID Variable Definitions\n",
    "portfolio_api = api_factory.build(lusid.api.PortfoliosApi)\n",
    "properties_api = api_factory.build(lusid.api.PropertyDefinitionsApi)\n",
    "transaction_portfolio_api = api_factory.build(lusid.api.TransactionPortfoliosApi)\n",
    "instruments_api = api_factory.build(lusid.api.InstrumentsApi)\n",
    "quotes_api = api_factory.build(lusid.api.QuotesApi)\n",
    "configuration_recipe_api = api_factory.build(lusid.api.ConfigurationRecipeApi)\n",
    "transaction_configuration_api = api_factory.build(lusid.api.TransactionConfigurationApi)\n",
    "aggregration_api = api_factory.build(lusid.api.AggregationApi)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Define scopes\n",
    "scope = \"ibor\"\n",
    "quotes_scope = \"ibor\"\n",
    "transaction_type_scope = create_scope_id()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 1. Create Portfolio<a class=\"anchor\" id=\"-Create-Portfolio\"></a>\n",
    "\n",
    "In order to support the grouping of our Futures contracts with their offsetting synthetic cash positions, we're going to use the LUSID subholding key feature. This allows us to tie two holdings together by one or more values specified within our input transactions.\n",
    "\n",
    "In our example, we're going to create two subholding keys: FutCode and FutHoldType. \n",
    "\n",
    "- **FutCode** - This will serve as our 'top level' aggregation code and will allow us to tie our Futures contract positions to our synthetic and P&L cash positions. This will take on the value of either 'Dec21' or 'Mar22'.\n",
    "- **FutHoldType** - This will serve as our 'second level' aggregation and will allow us to split out the underlying transactions that make up a single Futures aggregation. In our example, this will take on the value of 'PnLCash', 'SyntheticCash', 'Dec21Future', or 'Mar21Future'."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "portfolio_code = \"SynthCashFuturesPortWithDiffCostBasis\"\n",
    "\n",
    "try:\n",
    "    transaction_portfolio_api.create_portfolio(\n",
    "        scope=scope,\n",
    "        create_transaction_portfolio_request=models.CreateTransactionPortfolioRequest(\n",
    "            display_name=portfolio_code,\n",
    "            code=portfolio_code,\n",
    "            base_currency=\"EUR\",\n",
    "            created=\"2010-01-01\",\n",
    "            sub_holding_keys=[f\"Transaction/{scope}/FutCode\", f\"Transaction/{scope}/FutHoldType\"],\n",
    "        ),\n",
    "    )\n",
    "\n",
    "except lusid.ApiException as e:\n",
    "    print(json.loads(e.body)[\"title\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 2. Create Properties<a class=\"anchor\" id=\"-Create-Properties\"></a>\n",
    "\n",
    "In order to populate our subholding keys, we're going to create five transaction properties. We describe each property below:\n",
    "\n",
    "1. **FutCode** - This property maps each transaction to a top level Futures aggregation. In our example, this will take on the value of 'Dec21' or 'Mar22'. This property maps directly to the corresponding portfolio subholding key.\n",
    "2. **FutHoldType** - This property represents the 'second level' of our Futures valuation aggregation and is used to split out our aggregations into their component parts. In our example, possible values are: 'Dec21Future', 'Mar22Future', 'PnLCash', and 'SyntheticCash'. This property maps to our second subholding key and derives its value from the FutCodeSubType, FutSynthCashCode, and FutPnLCashCode transaction properties.\n",
    "3. **FutCodeSubType** - This property is used to distinguish our Futures transactions from the cash transactions within the second level of aggregation. The value is composed of the FutCode property suffixed with 'Future'\n",
    "4. **FutSynthCashCode** - Used to mark the Synthetic Cash component of a transaction.\n",
    "5. **FutPnLCashCode** - Used to mark the PnL cash component of a transaction."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "properties = [\n",
    "    (\"FutCode\", \"string\"),\n",
    "    (\"FutHoldType\", \"string\"),\n",
    "    (\"FutCodeSubType\", \"string\"),    \n",
    "    (\"FutSynthCashCode\", \"string\"),\n",
    "    (\"FutPnLCashCode\", \"string\"),\n",
    "]\n",
    "\n",
    "for property_code, dtype in properties:\n",
    "    try:\n",
    "        properties_api.create_property_definition(\n",
    "            create_property_definition_request=models.CreatePropertyDefinitionRequest(\n",
    "                domain=\"Transaction\",\n",
    "                scope=scope,\n",
    "                code=property_code,\n",
    "                display_name=property_code,\n",
    "                data_type_id=models.ResourceId(code=dtype, scope=\"system\"),\n",
    "            )\n",
    "        )\n",
    "    except lusid.ApiException as e:\n",
    "        print(json.loads(e.body)[\"title\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 3. Create Futures<a class=\"anchor\" id=\"-Create-Futures\"></a>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Define function that creates futures\n",
    "def create_futures_contract(\n",
    "        dom_ccy,\n",
    "        contract_code,\n",
    "        contract_month,\n",
    "        contract_size,\n",
    "        convention,\n",
    "        country_id,\n",
    "        fut_name,\n",
    "        exchange_code,\n",
    "        exchange_name,\n",
    "        ticker_step,\n",
    "        unit_value,\n",
    "        ref_spot_price,\n",
    "        start_date,\n",
    "        maturity_date,\n",
    "        fut_identifier,\n",
    "):\n",
    "    ctc = models.FuturesContractDetails(\n",
    "        dom_ccy=dom_ccy,\n",
    "        contract_code=contract_code,\n",
    "        contract_month=contract_month,\n",
    "        contract_size=contract_size,\n",
    "        convention=convention,\n",
    "        country=country_id,\n",
    "        description=fut_name,\n",
    "        exchange_code=exchange_code,\n",
    "        exchange_name=exchange_name,\n",
    "        ticker_step=ticker_step,\n",
    "        unit_value=unit_value,\n",
    "    )\n",
    "    futuredef = models.Future(\n",
    "        start_date=start_date,\n",
    "        maturity_date=maturity_date,\n",
    "        identifiers={},\n",
    "        contract_details=ctc,\n",
    "        contracts=1,\n",
    "        ref_spot_price=ref_spot_price,\n",
    "        underlying=models.ExoticInstrument(\n",
    "            instrument_format=models.InstrumentDefinitionFormat(\n",
    "                \"custom\", \"custom\", \"0.0.0\"\n",
    "            ),\n",
    "            content=\"{}\",\n",
    "            instrument_type=\"ExoticInstrument\",\n",
    "        ),\n",
    "        instrument_type=\"Future\",\n",
    "    )\n",
    "    # persist the instrument\n",
    "    futureDefinition = models.InstrumentDefinition(\n",
    "        name=fut_name,\n",
    "        identifiers={\"ClientInternal\": models.InstrumentIdValue(fut_identifier)},\n",
    "        definition=futuredef,\n",
    "    )\n",
    "    batchUpsertRequest = {fut_identifier: futureDefinition}\n",
    "    upsertResponse = instruments_api.upsert_instruments(request_body=batchUpsertRequest)\n",
    "    futLuid = upsertResponse.values[fut_identifier].lusid_instrument_id\n",
    "    print(futLuid)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3.1 Create Bund Futures Contract Expiring Dec 21"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "LUID_00003D62\n"
     ]
    }
   ],
   "source": [
    "start_date = datetime(2021, 3, 8, tzinfo=pytz.utc)\n",
    "effectiveAt = datetime.today().replace(tzinfo=pytz.utc)\n",
    "maturity_date = datetime(2021, 12, 8, tzinfo=pytz.utc)\n",
    "\n",
    "dom_ccy = \"EUR\"\n",
    "contract_code = \"FGBL\" #bbg=OE\n",
    "contract_month = \"Z\"\n",
    "contract_size = 100000\n",
    "convention = \"ActualActual\"\n",
    "country_id= \"DE\"\n",
    "fut_name = \"EURO-BUND FUTURE Dec21\"\n",
    "exchange_code = \"EUREX\"\n",
    "exchange_name =\"Eurex\"\n",
    "ticker_step = 0.01\n",
    "unit_value = 10\n",
    "ref_spot_price_val = None\n",
    "identifier = \"FutBund001\"\n",
    "\n",
    "# Create Futures Contract function\n",
    "create_futures_contract(\n",
    "    dom_ccy,\n",
    "    contract_code,\n",
    "    contract_month,\n",
    "    contract_size,\n",
    "    convention,\n",
    "    country_id,\n",
    "    fut_name,\n",
    "    exchange_code,\n",
    "    exchange_name,\n",
    "    ticker_step,\n",
    "    unit_value,\n",
    "    ref_spot_price_val,\n",
    "    start_date,\n",
    "    maturity_date,\n",
    "    identifier,\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3.2 Create Bund Futures Contract Expiring Mar 22"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "LUID_00003D63\n"
     ]
    }
   ],
   "source": [
    "start_date = datetime(2021, 6, 8, tzinfo=pytz.utc)\n",
    "effectiveAt = datetime.today().replace(tzinfo=pytz.utc)\n",
    "maturity_date = datetime(2022, 3, 8, tzinfo=pytz.utc)\n",
    "\n",
    "dom_ccy = \"EUR\"\n",
    "contract_code = \"FGBL\" #bbg=OE\n",
    "contract_month = \"H\"\n",
    "contract_size = 100000\n",
    "convention = \"ActualActual\"\n",
    "country_id= \"DE\"\n",
    "fut_name = \"EURO-BUND FUTURE Mar22\"\n",
    "exchange_code = \"EUREX\"\n",
    "exchange_name =\"Eurex\"\n",
    "ticker_step = 0.01\n",
    "unit_value = 10\n",
    "ref_spot_price_val = 0\n",
    "identifier = \"FutBund002\"\n",
    "\n",
    "# Create Futures Contract function\n",
    "create_futures_contract(\n",
    "    dom_ccy,\n",
    "    contract_code,\n",
    "    contract_month,\n",
    "    contract_size,\n",
    "    convention,\n",
    "    country_id,\n",
    "    fut_name,\n",
    "    exchange_code,\n",
    "    exchange_name,\n",
    "    ticker_step,\n",
    "    unit_value,\n",
    "    ref_spot_price_val,\n",
    "    start_date,\n",
    "    maturity_date,\n",
    "    identifier,\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 4. Transactions<a class=\"anchor\" id=\"-Transactions\"></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4.1 Create Transaction Types\n",
    "\n",
    "To book Synthetic Cash based Futures transactions, there's two key considerations to make. First, we need to create a set of transactions to faciliate the purchase of our Futures including the realization of P&L and the offsetting of PV via a synthetic cash holding. Second, we must map our various transaction properties to our two subholding keys: **FutCode** and **FutHoldType**. \n",
    "\n",
    "#### 4.1.2 Transaction Definitions\n",
    "\n",
    "##### OpenContract\n",
    "The first transaction type 'OpenContract' is a standard transaction used to enter into our Futures positions. This contains two movements. The first increases the size of the position based on a quantity provided, while the second creates an offsetting synthetic cash position.\n",
    "\n",
    "##### RealizePnLIncrease and RealisePnLDecrease\n",
    "\n",
    "The second two transaction types 'RealisePnLIncrease' and 'RealisePnLDecrease' are specific to the close out method used by our Mar 22 Futures position. These transactions exist to adjust the cost basis up or down depending on the position's daily P&L.\n",
    "\n",
    "These transactions have three movements:\n",
    "\n",
    "- The first movement updates the cost basis of the Futures position to reflect the daily mark-to-market change. This is done by adjusting the total_consideration of the position up or down while leaving the quantity of the position unchanged (in the transaction file we specify a quantity of 0). \n",
    "- The second movement records the cash generated from the daily mark-to-market change. This again gets its amount from the total_consideration value in the Excel file.\n",
    "- Lastly, we create a synthetic cash amount to offset the realised P&L from the second movement.\n",
    "\n",
    "#### 4.1.3 Subholding Key Mapping\n",
    "\n",
    "Within each movement of our three transactions, we leverage LUSID's transaction mapping feature to populate the values of our subholding keys. Each mapping is based on whether the movement is related to the Future instrument itself, realised P&L cash, or synthetic cash."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Add default side definitions to the non-default transaction type scope.\n",
    "# If working in the default scope, these side definitions are set by default so, unless these sides have been removed, this setting of sides can be skipped.\n",
    "default_side_definitions = [\n",
    "    models.SidesDefinitionRequest(\n",
    "        side=\"Side1\", \n",
    "        side_request=models.SideDefinitionRequest(\n",
    "            security=\"Txn:LusidInstrumentId\",\n",
    "            currency=\"Txn:TradeCurrency\",\n",
    "            rate=\"Txn:TradeToPortfolioRate\",\n",
    "            units=\"Txn:Units\",\n",
    "            amount=\"Txn:TradeAmount\")),\n",
    "    models.SidesDefinitionRequest(\n",
    "        side=\"Side2\", \n",
    "        side_request=models.SideDefinitionRequest(\n",
    "            security=\"Txn:SettleCcy\",\n",
    "            currency=\"Txn:SettlementCurrency\",\n",
    "            rate=\"SettledToPortfolioRate\",\n",
    "            units=\"Txn:TotalConsideration\",\n",
    "            amount=\"Txn:TotalConsideration\"))\n",
    "]\n",
    "\n",
    "transaction_configuration_api.set_side_definitions(default_side_definitions, scope = transaction_type_scope)\n",
    "\n",
    "\n",
    "# Now insert the transaction types\n",
    "transaction_type_requests = [\n",
    "    models.TransactionTypeRequest(\n",
    "        aliases=[\n",
    "            models.TransactionTypeAlias(\n",
    "                type=\"OpenContract\",\n",
    "                description=\"An FuturesTxnType transaction type\",\n",
    "                transaction_class=\"default\",\n",
    "                transaction_roles=\"Longer\",\n",
    "            )\n",
    "        ],\n",
    "        movements=[\n",
    "            models.TransactionTypeMovement(\n",
    "                movement_types=\"StockMovement\",\n",
    "                side=\"Side1\",\n",
    "                direction=1,\n",
    "                properties=None,\n",
    "                mappings=[\n",
    "                    models.TransactionTypePropertyMapping(property_key=f\"Transaction/{scope}/FutCode\", map_from=f\"Transaction/{scope}/FutCode\"),\n",
    "                    models.TransactionTypePropertyMapping(property_key=f\"Transaction/{scope}/FutHoldType\", map_from=f\"Transaction/{scope}/FutCodeSubType\")\n",
    "                ]\n",
    "            ),models.TransactionTypeMovement(\n",
    "                movement_types=\"CashReceivable\",\n",
    "                side=\"Side2\",\n",
    "                direction=-1,\n",
    "                properties=None,\n",
    "                mappings=[\n",
    "                    models.TransactionTypePropertyMapping(property_key=f\"Transaction/{scope}/FutCode\", map_from=f\"Transaction/{scope}/FutCode\"),\n",
    "                    models.TransactionTypePropertyMapping(property_key=f\"Transaction/{scope}/FutHoldType\", map_from=f\"Transaction/{scope}/FutSynthCashCode\")\n",
    "                ]\n",
    "            )\n",
    "        ],\n",
    "        properties=None,\n",
    "    ),\n",
    "    models.TransactionTypeRequest(    \n",
    "        aliases=[        \n",
    "            models.TransactionTypeAlias(\n",
    "                type=\"RealisePnLIncrease\",\n",
    "                description=\"Increase cost basis and adjust cash\",\n",
    "                transaction_class=\"default\",\n",
    "                transaction_roles=\"Longer\",\n",
    "            )\n",
    "        ],\n",
    "        movements=[\n",
    "            models.TransactionTypeMovement(\n",
    "                movement_types=\"StockMovement\",\n",
    "                side=\"Side1\",\n",
    "                direction=1,\n",
    "                properties=None,\n",
    "                mappings=[\n",
    "                    models.TransactionTypePropertyMapping(property_key=f\"Transaction/{scope}/FutCode\", map_from=f\"Transaction/{scope}/FutCode\"),\n",
    "                    models.TransactionTypePropertyMapping(property_key=f\"Transaction/{scope}/FutHoldType\", map_from=f\"Transaction/{scope}/FutCodeSubType\")\n",
    "                ]\n",
    "            ),models.TransactionTypeMovement(\n",
    "                movement_types=\"CashReceivable\",\n",
    "                side=\"Side2\",\n",
    "                direction=1,\n",
    "                properties=None,\n",
    "                mappings=[\n",
    "                    models.TransactionTypePropertyMapping(property_key=f\"Transaction/{scope}/FutCode\", map_from=f\"Transaction/{scope}/FutCode\"),\n",
    "                    models.TransactionTypePropertyMapping(property_key=f\"Transaction/{scope}/FutHoldType\", map_from=f\"Transaction/{scope}/FutPnLCashCode\")\n",
    "                ]                \n",
    "            ),models.TransactionTypeMovement(\n",
    "                movement_types=\"CashReceivable\",\n",
    "                side=\"Side2\",\n",
    "                direction=-1,\n",
    "                properties=None,\n",
    "                mappings=[\n",
    "                    models.TransactionTypePropertyMapping(property_key=f\"Transaction/{scope}/FutCode\", map_from=f\"Transaction/{scope}/FutCode\"),\n",
    "                    models.TransactionTypePropertyMapping(property_key=f\"Transaction/{scope}/FutHoldType\", map_from=f\"Transaction/{scope}/FutSynthCashCode\")\n",
    "                ]\n",
    "            )\n",
    "        ],\n",
    "        properties=None,\n",
    "    ),\n",
    "    models.TransactionTypeRequest(    \n",
    "        aliases=[        \n",
    "            models.TransactionTypeAlias(\n",
    "                type=\"RealisePnLDecrease\",\n",
    "                description=\"Decrease cost basis and adjust cash\",\n",
    "                transaction_class=\"default\",\n",
    "                transaction_roles=\"Longer\",\n",
    "            )\n",
    "        ],\n",
    "        movements=[\n",
    "            models.TransactionTypeMovement(\n",
    "                movement_types=\"StockMovement\",\n",
    "                side=\"Side1\",\n",
    "                direction=-1,\n",
    "                properties=None,\n",
    "                mappings=[\n",
    "                    models.TransactionTypePropertyMapping(property_key=f\"Transaction/{scope}/FutCode\", map_from=f\"Transaction/{scope}/FutCode\"),\n",
    "                    models.TransactionTypePropertyMapping(property_key=f\"Transaction/{scope}/FutHoldType\", map_from=f\"Transaction/{scope}/FutCodeSubType\")\n",
    "                ]\n",
    "            ),models.TransactionTypeMovement(\n",
    "                movement_types=\"CashReceivable\",\n",
    "                side=\"Side2\",\n",
    "                direction=-1,\n",
    "                properties=None,\n",
    "                mappings=[\n",
    "                    models.TransactionTypePropertyMapping(property_key=f\"Transaction/{scope}/FutCode\", map_from=f\"Transaction/{scope}/FutCode\"),\n",
    "                    models.TransactionTypePropertyMapping(property_key=f\"Transaction/{scope}/FutHoldType\", map_from=f\"Transaction/{scope}/FutPnLCashCode\")\n",
    "                ]\n",
    "            ),models.TransactionTypeMovement(\n",
    "                movement_types=\"CashReceivable\",\n",
    "                side=\"Side2\",\n",
    "                direction=1,\n",
    "                properties=None,\n",
    "                mappings=[\n",
    "                    models.TransactionTypePropertyMapping(property_key=f\"Transaction/{scope}/FutCode\", map_from=f\"Transaction/{scope}/FutCode\"),\n",
    "                    models.TransactionTypePropertyMapping(property_key=f\"Transaction/{scope}/FutHoldType\", map_from=f\"Transaction/{scope}/FutSynthCashCode\")\n",
    "                ]\n",
    "            )\n",
    "        ],\n",
    "        properties={}\n",
    "    )\n",
    "]\n",
    "\n",
    "new_txn_config = transaction_configuration_api.set_transaction_type_source(\n",
    "    source=\"default\",\n",
    "    transaction_type_request=transaction_type_requests,\n",
    "    scope=transaction_type_scope\n",
    ")\n",
    "\n",
    "# Call LUSID to update the transaction type scope of your portfolio \n",
    "patch_document = [\n",
    "    {\n",
    "        \"value\": transaction_type_scope,\n",
    "        \"path\": \"/transactiontypescope\",\n",
    "        \"op\": \"add\"\n",
    "    }\n",
    "]\n",
    "patch_response = api_factory.build(lusid.api.TransactionPortfoliosApi).patch_portfolio_details(\n",
    "    scope=scope,\n",
    "    code=portfolio_code,\n",
    "    operation=patch_document)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4.2 Load Transaction Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Read in transaction data read from file\n",
    "futures_transactions = pd.read_excel(\"data/synthetic_cash_futures_data.xlsx\", sheet_name=\"transactions\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "for index, row in futures_transactions.iterrows():\n",
    "\n",
    "    primary_instrument_identifier = { \"Instrument/default/ClientInternal\": row[\"client_id\"] }\n",
    "\n",
    "    upsert_transactions = transaction_portfolio_api.upsert_transactions(\n",
    "        scope=scope,\n",
    "        code=row['portfolio'],\n",
    "        transaction_request=[\n",
    "            models.TransactionRequest(\n",
    "                transaction_id=row[\"txn_id\"],\n",
    "                type=row[\"txn_type\"],\n",
    "                instrument_identifiers=primary_instrument_identifier,\n",
    "                transaction_date=row[\"trade_date\"],\n",
    "                settlement_date=row[\"trade_date\"],\n",
    "                units=row[\"quantity\"],\n",
    "                transaction_price=models.TransactionPrice(\n",
    "                    price=row[\"price\"], type=\"Price\"\n",
    "                ),\n",
    "                total_consideration=models.CurrencyAndAmount(\n",
    "                    amount=row[\"total_consideration\"], currency=row[\"currency_id\"]\n",
    "                ),\n",
    "                properties={\n",
    "                    f\"Transaction/{scope}/FutCode\": models.ModelProperty(\n",
    "                        key=f\"Transaction/{scope}/FutCode\",\n",
    "                        value=models.PropertyValue(label_value=row['fut_code']),\n",
    "                    ),\n",
    "                    f\"Transaction/{scope}/FutCodeSubType\": models.ModelProperty(\n",
    "                        key=f\"Transaction/{scope}/FutCodeSubType\",\n",
    "                        value=models.PropertyValue(label_value=row['fut_code'] + \"Future\"),\n",
    "                    ),\n",
    "                    f\"Transaction/{scope}/FutSynthCashCode\": models.ModelProperty(\n",
    "                        key=f\"Transaction/{scope}/FutSynthCashCode\",\n",
    "                        value=models.PropertyValue(label_value=row['fut_synth_cash_code']),\n",
    "                    ),\n",
    "                    f\"Transaction/{scope}/FutPnLCashCode\": models.ModelProperty(\n",
    "                        key=f\"Transaction/{scope}/FutPnLCashCode\",\n",
    "                        value=models.PropertyValue(label_value=row['fut_pnl_cash_code']),\n",
    "                    ),\n",
    "                },\n",
    "            )\n",
    "        ],\n",
    "    )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 5. Quotes<a class=\"anchor\" id=\"-Quotes\"></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5.1 Book Quotes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>prices</th>\n",
       "      <th>luid</th>\n",
       "      <th>id_type</th>\n",
       "      <th>currency</th>\n",
       "      <th>scaling_factor</th>\n",
       "      <th>note</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2021-09-01T08:00:00Z</td>\n",
       "      <td>100.00</td>\n",
       "      <td>FutBund001</td>\n",
       "      <td>ClientInternal</td>\n",
       "      <td>EUR</td>\n",
       "      <td>100</td>\n",
       "      <td>Day 1 opening price</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021-09-01T12:00:00Z</td>\n",
       "      <td>100.50</td>\n",
       "      <td>FutBund001</td>\n",
       "      <td>ClientInternal</td>\n",
       "      <td>EUR</td>\n",
       "      <td>100</td>\n",
       "      <td>Day 1 intraday MTM price</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2021-09-01T17:00:00Z</td>\n",
       "      <td>100.50</td>\n",
       "      <td>FutBund001</td>\n",
       "      <td>ClientInternal</td>\n",
       "      <td>EUR</td>\n",
       "      <td>100</td>\n",
       "      <td>Day 1 closing price</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2021-09-02T08:00:00Z</td>\n",
       "      <td>100.50</td>\n",
       "      <td>FutBund001</td>\n",
       "      <td>ClientInternal</td>\n",
       "      <td>EUR</td>\n",
       "      <td>100</td>\n",
       "      <td>Day 2 opening price</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2021-09-02T14:15:00Z</td>\n",
       "      <td>99.50</td>\n",
       "      <td>FutBund001</td>\n",
       "      <td>ClientInternal</td>\n",
       "      <td>EUR</td>\n",
       "      <td>100</td>\n",
       "      <td>Day 2 intraday trade price</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2021-09-02T17:00:00Z</td>\n",
       "      <td>98.00</td>\n",
       "      <td>FutBund001</td>\n",
       "      <td>ClientInternal</td>\n",
       "      <td>EUR</td>\n",
       "      <td>100</td>\n",
       "      <td>Day 2 closing price</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2021-09-03T08:00:00Z</td>\n",
       "      <td>98.00</td>\n",
       "      <td>FutBund001</td>\n",
       "      <td>ClientInternal</td>\n",
       "      <td>EUR</td>\n",
       "      <td>100</td>\n",
       "      <td>Day 3 opening price</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2021-09-03T14:15:00Z</td>\n",
       "      <td>104.00</td>\n",
       "      <td>FutBund001</td>\n",
       "      <td>ClientInternal</td>\n",
       "      <td>EUR</td>\n",
       "      <td>100</td>\n",
       "      <td>Day 3 intraday trade price</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2021-09-03T17:00:00Z</td>\n",
       "      <td>104.00</td>\n",
       "      <td>FutBund001</td>\n",
       "      <td>ClientInternal</td>\n",
       "      <td>EUR</td>\n",
       "      <td>100</td>\n",
       "      <td>Day 3 closing price</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2021-09-01T08:00:00Z</td>\n",
       "      <td>100.00</td>\n",
       "      <td>FutBund002</td>\n",
       "      <td>ClientInternal</td>\n",
       "      <td>EUR</td>\n",
       "      <td>100</td>\n",
       "      <td>Day 1 opening price</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2021-09-01T12:00:00Z</td>\n",
       "      <td>100.50</td>\n",
       "      <td>FutBund002</td>\n",
       "      <td>ClientInternal</td>\n",
       "      <td>EUR</td>\n",
       "      <td>100</td>\n",
       "      <td>Day 1 intraday MTM price</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2021-09-01T17:00:00Z</td>\n",
       "      <td>100.50</td>\n",
       "      <td>FutBund002</td>\n",
       "      <td>ClientInternal</td>\n",
       "      <td>EUR</td>\n",
       "      <td>100</td>\n",
       "      <td>Day 1 closing price</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>2021-09-02T08:00:00Z</td>\n",
       "      <td>100.50</td>\n",
       "      <td>FutBund002</td>\n",
       "      <td>ClientInternal</td>\n",
       "      <td>EUR</td>\n",
       "      <td>100</td>\n",
       "      <td>Day 2 opening price</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>2021-09-02T14:15:00Z</td>\n",
       "      <td>99.50</td>\n",
       "      <td>FutBund002</td>\n",
       "      <td>ClientInternal</td>\n",
       "      <td>EUR</td>\n",
       "      <td>100</td>\n",
       "      <td>Day 2 intraday trade price</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>2021-09-02T17:00:00Z</td>\n",
       "      <td>98.00</td>\n",
       "      <td>FutBund002</td>\n",
       "      <td>ClientInternal</td>\n",
       "      <td>EUR</td>\n",
       "      <td>100</td>\n",
       "      <td>Day 2 closing price</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>2021-09-03T08:00:00Z</td>\n",
       "      <td>98.00</td>\n",
       "      <td>FutBund002</td>\n",
       "      <td>ClientInternal</td>\n",
       "      <td>EUR</td>\n",
       "      <td>100</td>\n",
       "      <td>Day 3 opening price</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>2021-09-03T14:15:00Z</td>\n",
       "      <td>104.00</td>\n",
       "      <td>FutBund002</td>\n",
       "      <td>ClientInternal</td>\n",
       "      <td>EUR</td>\n",
       "      <td>100</td>\n",
       "      <td>Day 3 intraday trade price</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>2021-09-03T17:00:00Z</td>\n",
       "      <td>104.00</td>\n",
       "      <td>FutBund002</td>\n",
       "      <td>ClientInternal</td>\n",
       "      <td>EUR</td>\n",
       "      <td>100</td>\n",
       "      <td>Day 3 closing price</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    date  prices        luid         id_type currency  \\\n",
       "0   2021-09-01T08:00:00Z  100.00  FutBund001  ClientInternal      EUR   \n",
       "1   2021-09-01T12:00:00Z  100.50  FutBund001  ClientInternal      EUR   \n",
       "2   2021-09-01T17:00:00Z  100.50  FutBund001  ClientInternal      EUR   \n",
       "3   2021-09-02T08:00:00Z  100.50  FutBund001  ClientInternal      EUR   \n",
       "4   2021-09-02T14:15:00Z   99.50  FutBund001  ClientInternal      EUR   \n",
       "5   2021-09-02T17:00:00Z   98.00  FutBund001  ClientInternal      EUR   \n",
       "6   2021-09-03T08:00:00Z   98.00  FutBund001  ClientInternal      EUR   \n",
       "7   2021-09-03T14:15:00Z  104.00  FutBund001  ClientInternal      EUR   \n",
       "8   2021-09-03T17:00:00Z  104.00  FutBund001  ClientInternal      EUR   \n",
       "9   2021-09-01T08:00:00Z  100.00  FutBund002  ClientInternal      EUR   \n",
       "10  2021-09-01T12:00:00Z  100.50  FutBund002  ClientInternal      EUR   \n",
       "11  2021-09-01T17:00:00Z  100.50  FutBund002  ClientInternal      EUR   \n",
       "12  2021-09-02T08:00:00Z  100.50  FutBund002  ClientInternal      EUR   \n",
       "13  2021-09-02T14:15:00Z   99.50  FutBund002  ClientInternal      EUR   \n",
       "14  2021-09-02T17:00:00Z   98.00  FutBund002  ClientInternal      EUR   \n",
       "15  2021-09-03T08:00:00Z   98.00  FutBund002  ClientInternal      EUR   \n",
       "16  2021-09-03T14:15:00Z  104.00  FutBund002  ClientInternal      EUR   \n",
       "17  2021-09-03T17:00:00Z  104.00  FutBund002  ClientInternal      EUR   \n",
       "\n",
       "    scaling_factor                        note  \n",
       "0              100         Day 1 opening price  \n",
       "1              100    Day 1 intraday MTM price  \n",
       "2              100         Day 1 closing price  \n",
       "3              100         Day 2 opening price  \n",
       "4              100  Day 2 intraday trade price  \n",
       "5              100         Day 2 closing price  \n",
       "6              100         Day 3 opening price  \n",
       "7              100  Day 3 intraday trade price  \n",
       "8              100         Day 3 closing price  \n",
       "9              100         Day 1 opening price  \n",
       "10             100    Day 1 intraday MTM price  \n",
       "11             100         Day 1 closing price  \n",
       "12             100         Day 2 opening price  \n",
       "13             100  Day 2 intraday trade price  \n",
       "14             100         Day 2 closing price  \n",
       "15             100         Day 3 opening price  \n",
       "16             100  Day 3 intraday trade price  \n",
       "17             100         Day 3 closing price  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Read in futures data from file\n",
    "futures_prices = pd.read_excel(\"data/synthetic_cash_futures_data.xlsx\", sheet_name=\"prices\")\n",
    "# Output futures data\n",
    "futures_prices"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "for index, row in futures_prices.iterrows():\n",
    "\n",
    "    instrument_quotes = {\n",
    "        \"upsert_request_1\": models.UpsertQuoteRequest(\n",
    "            quote_id=models.QuoteId(\n",
    "                quote_series_id=models.QuoteSeriesId(\n",
    "                    provider=\"Lusid\",\n",
    "                    instrument_id=row[\"luid\"],\n",
    "                    instrument_id_type=\"ClientInternal\",\n",
    "                    quote_type=\"Price\",\n",
    "                    field=\"mid\",\n",
    "                ),\n",
    "                effective_at=row[\"date\"],\n",
    "            ),\n",
    "            metric_value=models.MetricValue(value=row[\"prices\"], unit=row[\"currency\"]),\n",
    "            scale_factor=row[\"scaling_factor\"]            \n",
    "        )\n",
    "    }\n",
    "\n",
    "    # Upsert the quotes into LUSID\n",
    "    response = quotes_api.upsert_quotes(\n",
    "        scope=quotes_scope, request_body=instrument_quotes\n",
    "    )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 6. Valuations<a class=\"anchor\" id=\"-Valuations\"></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6.1 Create valuation recipe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a recipe to perform a valuation\n",
    "configuration_recipe = models.ConfigurationRecipe(\n",
    "    scope=scope,\n",
    "    code=\"futuresValuation\",\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",
    "                quote_interval=\"5D\",\n",
    "            )\n",
    "        ],\n",
    "        options=models.MarketOptions(\n",
    "            default_supplier=\"Lusid\",\n",
    "            default_instrument_code_type=\"ClientInternal\",\n",
    "            default_scope=scope,\n",
    "        ),\n",
    "    ),\n",
    "    pricing=models.PricingContext(\n",
    "        model_rules=[\n",
    "            models.VendorModelRule(\n",
    "                supplier=\"Lusid\",\n",
    "                model_name=\"SimpleStatic\",\n",
    "                instrument_type=\"Future\",\n",
    "                parameters=\"{}\",\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=configuration_recipe\n",
    "        )\n",
    "    )\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6.2 Create daily valuation function\n",
    "\n",
    "The get_daily_fut_val() function when given a date & time along with a portfolio returns a valuation of the held instruments with an indicator of their price relative to the agreed future price. This valuation therefore enables you to know if your futures are worth more or less than you agreed to pay for them at any given time."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_daily_fut_val(date, portfolio_code, aggregateTopLevel):\n",
    "\n",
    "    # Define valuation aggregation and grouping parameters\n",
    "    metricsList=[\n",
    "            models.AggregateSpec(f\"Transaction/{scope}/FutCode\", \"Value\"),        \n",
    "            models.AggregateSpec(\"Instrument/Definition/ContractSize\", \"Value\"),\n",
    "            models.AggregateSpec(\"Quotes/Price\", \"Value\"),\n",
    "            models.AggregateSpec(\"Holding/default/Units\", \"Value\"),\n",
    "            models.AggregateSpec(\"Holding/default/Cost\", \"Value\"),\n",
    "            models.AggregateSpec(\"Valuation/PV/Amount\", \"Sum\"),\n",
    "            models.AggregateSpec(\"Valuation/Exposure/Amount\", \"Sum\"),\n",
    "        ]\n",
    "        \n",
    "    if (aggregateTopLevel == False):\n",
    "        metricsList.append(models.AggregateSpec(f\"Transaction/{scope}/FutHoldType\", \"Value\"))\n",
    "    \n",
    "    group_by = [f\"Transaction/{scope}/FutCode\"]\n",
    "    \n",
    "    if (aggregateTopLevel == False):\n",
    "        group_by.append(f\"Transaction/{scope}/FutHoldType\")\n",
    "\n",
    "        \n",
    "    # Build and run valuation request\n",
    "    valuation_request = models.ValuationRequest(\n",
    "        recipe_id=models.ResourceId(scope=scope, code=\"futuresValuation\"),\n",
    "        metrics=metricsList,\n",
    "        group_by=group_by,\n",
    "        portfolio_entity_ids=[\n",
    "            models.PortfolioEntityId(scope=scope, code=portfolio_code)\n",
    "        ],\n",
    "        valuation_schedule=models.ValuationSchedule(effective_at=date),\n",
    "    )\n",
    "\n",
    "    val_data = aggregration_api.get_valuation(valuation_request=valuation_request).data\n",
    "    \n",
    "    vals_df = pd.DataFrame(val_data)\n",
    "    \n",
    "    sorted_df = vals_df\n",
    "    \n",
    "    if (aggregateTopLevel == True):\n",
    "        sorted_df = vals_df.sort_values(by=[f\"Transaction/{scope}/FutCode\"], ascending=True)\n",
    "    else:\n",
    "        sorted_df = vals_df.sort_values(by=[f\"Transaction/{scope}/FutHoldType\"], ascending=True)\n",
    "\n",
    "        \n",
    "    # Rename result columns\n",
    "    columnsToRename={\n",
    "            \"Instrument/Definition/ContractSize\": \"Contract Size\",\n",
    "            \"Quotes/Price\": \"Price\",\n",
    "            \"Holding/default/Units\": \"Units\",\n",
    "            \"Holding/default/Cost\": \"Cost\",\n",
    "            \"Sum(Valuation/PV/Amount)\": \"PV\",\n",
    "            \"Sum(Valuation/Exposure/Amount)\": \"Exposure\",\n",
    "            f\"Transaction/{scope}/FutCode\": \"FutCode\",\n",
    "    }   \n",
    "        \n",
    "    if (aggregateTopLevel == False):\n",
    "        columnsToRename[f\"Transaction/{scope}/FutHoldType\"] = \"FutHoldType\"          \n",
    "\n",
    "    sorted_df.rename(\n",
    "        columns=columnsToRename,\n",
    "        inplace=True,\n",
    "    )\n",
    "    \n",
    "    \n",
    "    # Re-order result columns\n",
    "    col_list = ['FutCode']\n",
    "    \n",
    "    if (aggregateTopLevel == False):\n",
    "        col_list.append(\"FutHoldType\")\n",
    "    \n",
    "    col_list.extend(['PV', 'Exposure'])\n",
    "    \n",
    "    col_list.extend(['Contract Size', 'Price', 'Units', 'Cost'])\n",
    "\n",
    "    sorted_df = sorted_df.reindex(columns=col_list)\n",
    "\n",
    "    return sorted_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6.3 Daily valuations\n",
    "\n",
    "## Day 1\n",
    "\n",
    "#### Start of Day"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "At 8:00am on Day 1, we enter into our two Bund Futures contracts at a price of 100.00."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>txn_id</th>\n",
       "      <th>fut_code</th>\n",
       "      <th>fut_synth_cash_code</th>\n",
       "      <th>fut_pnl_cash_code</th>\n",
       "      <th>txn_type</th>\n",
       "      <th>trade_date</th>\n",
       "      <th>quantity</th>\n",
       "      <th>client_id</th>\n",
       "      <th>currency_id</th>\n",
       "      <th>price</th>\n",
       "      <th>total_consideration</th>\n",
       "      <th>portfolio</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>futa_txn_001</td>\n",
       "      <td>Dec21</td>\n",
       "      <td>SynthCash</td>\n",
       "      <td>None</td>\n",
       "      <td>OpenContract</td>\n",
       "      <td>2021-09-01T08:00:00Z</td>\n",
       "      <td>10</td>\n",
       "      <td>FutBund001</td>\n",
       "      <td>EUR</td>\n",
       "      <td>100.00</td>\n",
       "      <td>1000000</td>\n",
       "      <td>SynthCashFuturesPortWithDiffCostBasis</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>futb_txn_001</td>\n",
       "      <td>Mar22</td>\n",
       "      <td>SynthCash</td>\n",
       "      <td>None</td>\n",
       "      <td>OpenContract</td>\n",
       "      <td>2021-09-01T08:00:00Z</td>\n",
       "      <td>10</td>\n",
       "      <td>FutBund002</td>\n",
       "      <td>EUR</td>\n",
       "      <td>100.00</td>\n",
       "      <td>1000000</td>\n",
       "      <td>SynthCashFuturesPortWithDiffCostBasis</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         txn_id fut_code fut_synth_cash_code fut_pnl_cash_code      txn_type  \\\n",
       "0  futa_txn_001    Dec21           SynthCash              None  OpenContract   \n",
       "1  futb_txn_001    Mar22           SynthCash              None  OpenContract   \n",
       "\n",
       "             trade_date  quantity   client_id currency_id  price  \\\n",
       "0  2021-09-01T08:00:00Z        10  FutBund001         EUR 100.00   \n",
       "1  2021-09-01T08:00:00Z        10  FutBund002         EUR 100.00   \n",
       "\n",
       "   total_consideration                              portfolio  \n",
       "0              1000000  SynthCashFuturesPortWithDiffCostBasis  \n",
       "1              1000000  SynthCashFuturesPortWithDiffCostBasis  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "futures_transactions.iloc[0:2]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can first see a disaggregated view of our positions where we have two Futures positions with a PV of 1,000,000 EUR as well as two offsetting synthetic cash positions with PVs of -1,000,000 EUR."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>FutCode</th>\n",
       "      <th>FutHoldType</th>\n",
       "      <th>PV</th>\n",
       "      <th>Exposure</th>\n",
       "      <th>Contract Size</th>\n",
       "      <th>Price</th>\n",
       "      <th>Units</th>\n",
       "      <th>Cost</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Dec21</td>\n",
       "      <td>Dec21Future</td>\n",
       "      <td>1,000,000.00</td>\n",
       "      <td>1,000,000.00</td>\n",
       "      <td>100,000.00</td>\n",
       "      <td>100.00</td>\n",
       "      <td>10.00</td>\n",
       "      <td>1,000,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Mar22</td>\n",
       "      <td>Mar22Future</td>\n",
       "      <td>1,000,000.00</td>\n",
       "      <td>1,000,000.00</td>\n",
       "      <td>100,000.00</td>\n",
       "      <td>100.00</td>\n",
       "      <td>10.00</td>\n",
       "      <td>1,000,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Dec21</td>\n",
       "      <td>SynthCash</td>\n",
       "      <td>-1,000,000.00</td>\n",
       "      <td>-1,000,000.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1,000,000.00</td>\n",
       "      <td>-1,000,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Mar22</td>\n",
       "      <td>SynthCash</td>\n",
       "      <td>-1,000,000.00</td>\n",
       "      <td>-1,000,000.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1,000,000.00</td>\n",
       "      <td>-1,000,000.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  FutCode  FutHoldType            PV      Exposure  Contract Size  Price  \\\n",
       "0   Dec21  Dec21Future  1,000,000.00  1,000,000.00     100,000.00 100.00   \n",
       "3   Mar22  Mar22Future  1,000,000.00  1,000,000.00     100,000.00 100.00   \n",
       "1   Dec21    SynthCash -1,000,000.00 -1,000,000.00           1.00    NaN   \n",
       "2   Mar22    SynthCash -1,000,000.00 -1,000,000.00           1.00    NaN   \n",
       "\n",
       "          Units          Cost  \n",
       "0         10.00  1,000,000.00  \n",
       "3         10.00  1,000,000.00  \n",
       "1 -1,000,000.00 -1,000,000.00  \n",
       "2 -1,000,000.00 -1,000,000.00  "
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get_daily_fut_val(\"2021-09-01T08:00:00Z\", portfolio_code, False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We we then role these up based on the 'FutCode' subholding key, we can see the aggregate PV is 0."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "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>FutCode</th>\n",
       "      <th>PV</th>\n",
       "      <th>Exposure</th>\n",
       "      <th>Contract Size</th>\n",
       "      <th>Price</th>\n",
       "      <th>Units</th>\n",
       "      <th>Cost</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Dec21</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>None</td>\n",
       "      <td>100.00</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Mar22</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>None</td>\n",
       "      <td>100.00</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  FutCode   PV  Exposure Contract Size  Price Units  Cost\n",
       "0   Dec21 0.00      0.00          None 100.00  None  None\n",
       "1   Mar22 0.00      0.00          None 100.00  None  None"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get_daily_fut_val(\"2021-09-01T08:00:00Z\", portfolio_code, True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Intraday"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "At 12:00pm noon, we decide to run an intraday mark-to-market valuation. We can see that the contract price for both instruments has moved from the original purchase price of 100.00, to 100.50. As a result, we see the PV of the two Futures positions has increased by 5,000 EUR while the synthetic cash position amounts remain unchanged."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>FutCode</th>\n",
       "      <th>FutHoldType</th>\n",
       "      <th>PV</th>\n",
       "      <th>Exposure</th>\n",
       "      <th>Contract Size</th>\n",
       "      <th>Price</th>\n",
       "      <th>Units</th>\n",
       "      <th>Cost</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Dec21</td>\n",
       "      <td>Dec21Future</td>\n",
       "      <td>1,005,000.00</td>\n",
       "      <td>1,005,000.00</td>\n",
       "      <td>100,000.00</td>\n",
       "      <td>100.50</td>\n",
       "      <td>10.00</td>\n",
       "      <td>1,000,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Mar22</td>\n",
       "      <td>Mar22Future</td>\n",
       "      <td>1,005,000.00</td>\n",
       "      <td>1,005,000.00</td>\n",
       "      <td>100,000.00</td>\n",
       "      <td>100.50</td>\n",
       "      <td>10.00</td>\n",
       "      <td>1,000,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Dec21</td>\n",
       "      <td>SynthCash</td>\n",
       "      <td>-1,000,000.00</td>\n",
       "      <td>-1,000,000.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1,000,000.00</td>\n",
       "      <td>-1,000,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Mar22</td>\n",
       "      <td>SynthCash</td>\n",
       "      <td>-1,000,000.00</td>\n",
       "      <td>-1,000,000.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1,000,000.00</td>\n",
       "      <td>-1,000,000.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  FutCode  FutHoldType            PV      Exposure  Contract Size  Price  \\\n",
       "0   Dec21  Dec21Future  1,005,000.00  1,005,000.00     100,000.00 100.50   \n",
       "3   Mar22  Mar22Future  1,005,000.00  1,005,000.00     100,000.00 100.50   \n",
       "1   Dec21    SynthCash -1,000,000.00 -1,000,000.00           1.00    NaN   \n",
       "2   Mar22    SynthCash -1,000,000.00 -1,000,000.00           1.00    NaN   \n",
       "\n",
       "          Units          Cost  \n",
       "0         10.00  1,000,000.00  \n",
       "3         10.00  1,000,000.00  \n",
       "1 -1,000,000.00 -1,000,000.00  \n",
       "2 -1,000,000.00 -1,000,000.00  "
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get_daily_fut_val(\"2021-09-01T12:00:00Z\", portfolio_code, False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "When we look at the aggregated view, we can see the PV of the Futures holdings have increased by 5,000 EUR."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>FutCode</th>\n",
       "      <th>PV</th>\n",
       "      <th>Exposure</th>\n",
       "      <th>Contract Size</th>\n",
       "      <th>Price</th>\n",
       "      <th>Units</th>\n",
       "      <th>Cost</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Dec21</td>\n",
       "      <td>5,000.00</td>\n",
       "      <td>5,000.00</td>\n",
       "      <td>None</td>\n",
       "      <td>100.50</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Mar22</td>\n",
       "      <td>5,000.00</td>\n",
       "      <td>5,000.00</td>\n",
       "      <td>None</td>\n",
       "      <td>100.50</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  FutCode       PV  Exposure Contract Size  Price Units  Cost\n",
       "0   Dec21 5,000.00  5,000.00          None 100.50  None  None\n",
       "1   Mar22 5,000.00  5,000.00          None 100.50  None  None"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get_daily_fut_val(\"2021-09-01T12:00:00Z\", portfolio_code, True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### End of Day\n",
    "\n",
    "At the end of the day, we see the contract prices remain at 100.50 yielding us the same valuation we produced intraday"
   ]
  },
  {
   "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>FutCode</th>\n",
       "      <th>FutHoldType</th>\n",
       "      <th>PV</th>\n",
       "      <th>Exposure</th>\n",
       "      <th>Contract Size</th>\n",
       "      <th>Price</th>\n",
       "      <th>Units</th>\n",
       "      <th>Cost</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Dec21</td>\n",
       "      <td>Dec21Future</td>\n",
       "      <td>1,005,000.00</td>\n",
       "      <td>1,005,000.00</td>\n",
       "      <td>100,000.00</td>\n",
       "      <td>100.50</td>\n",
       "      <td>10.00</td>\n",
       "      <td>1,000,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Mar22</td>\n",
       "      <td>Mar22Future</td>\n",
       "      <td>1,005,000.00</td>\n",
       "      <td>1,005,000.00</td>\n",
       "      <td>100,000.00</td>\n",
       "      <td>100.50</td>\n",
       "      <td>10.00</td>\n",
       "      <td>1,000,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Dec21</td>\n",
       "      <td>SynthCash</td>\n",
       "      <td>-1,000,000.00</td>\n",
       "      <td>-1,000,000.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1,000,000.00</td>\n",
       "      <td>-1,000,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Mar22</td>\n",
       "      <td>SynthCash</td>\n",
       "      <td>-1,000,000.00</td>\n",
       "      <td>-1,000,000.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1,000,000.00</td>\n",
       "      <td>-1,000,000.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  FutCode  FutHoldType            PV      Exposure  Contract Size  Price  \\\n",
       "0   Dec21  Dec21Future  1,005,000.00  1,005,000.00     100,000.00 100.50   \n",
       "3   Mar22  Mar22Future  1,005,000.00  1,005,000.00     100,000.00 100.50   \n",
       "1   Dec21    SynthCash -1,000,000.00 -1,000,000.00           1.00    NaN   \n",
       "2   Mar22    SynthCash -1,000,000.00 -1,000,000.00           1.00    NaN   \n",
       "\n",
       "          Units          Cost  \n",
       "0         10.00  1,000,000.00  \n",
       "3         10.00  1,000,000.00  \n",
       "1 -1,000,000.00 -1,000,000.00  \n",
       "2 -1,000,000.00 -1,000,000.00  "
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get_daily_fut_val(\"2021-09-01T17:00:00Z\", portfolio_code, False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>FutCode</th>\n",
       "      <th>PV</th>\n",
       "      <th>Exposure</th>\n",
       "      <th>Contract Size</th>\n",
       "      <th>Price</th>\n",
       "      <th>Units</th>\n",
       "      <th>Cost</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Dec21</td>\n",
       "      <td>5,000.00</td>\n",
       "      <td>5,000.00</td>\n",
       "      <td>None</td>\n",
       "      <td>100.50</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Mar22</td>\n",
       "      <td>5,000.00</td>\n",
       "      <td>5,000.00</td>\n",
       "      <td>None</td>\n",
       "      <td>100.50</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  FutCode       PV  Exposure Contract Size  Price Units  Cost\n",
       "0   Dec21 5,000.00  5,000.00          None 100.50  None  None\n",
       "1   Mar22 5,000.00  5,000.00          None 100.50  None  None"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get_daily_fut_val(\"2021-09-01T17:00:00Z\", portfolio_code, True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Day 2\n",
    "#### Start of Day\n",
    "\n",
    "At 8:00am on Day 2, we book a 'RealisePnlIncrease' transaction in order to adjust the cost basis of our Mar 22 Bund Future upwards such that its aggregated start of day PV is 0. This adjustment can be automatically applied in LUSID removing the need to manually book these transactions each day."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>txn_id</th>\n",
       "      <th>fut_code</th>\n",
       "      <th>fut_synth_cash_code</th>\n",
       "      <th>fut_pnl_cash_code</th>\n",
       "      <th>txn_type</th>\n",
       "      <th>trade_date</th>\n",
       "      <th>quantity</th>\n",
       "      <th>client_id</th>\n",
       "      <th>currency_id</th>\n",
       "      <th>price</th>\n",
       "      <th>total_consideration</th>\n",
       "      <th>portfolio</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>rpnlb_txn_001</td>\n",
       "      <td>Mar22</td>\n",
       "      <td>SynthCash</td>\n",
       "      <td>PnLCash</td>\n",
       "      <td>RealisePnLIncrease</td>\n",
       "      <td>2021-09-02T08:00:00Z</td>\n",
       "      <td>0</td>\n",
       "      <td>FutBund002</td>\n",
       "      <td>EUR</td>\n",
       "      <td>0.00</td>\n",
       "      <td>5000</td>\n",
       "      <td>SynthCashFuturesPortWithDiffCostBasis</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          txn_id fut_code fut_synth_cash_code fut_pnl_cash_code  \\\n",
       "2  rpnlb_txn_001    Mar22           SynthCash           PnLCash   \n",
       "\n",
       "             txn_type            trade_date  quantity   client_id currency_id  \\\n",
       "2  RealisePnLIncrease  2021-09-02T08:00:00Z         0  FutBund002         EUR   \n",
       "\n",
       "   price  total_consideration                              portfolio  \n",
       "2   0.00                 5000  SynthCashFuturesPortWithDiffCostBasis  "
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "futures_transactions.iloc[2:3]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We again run a start of day valuation immediately afterwards and can see that both the Dec 21 and Mar 22 Futures contracts have a PV of 1,005,000 EUR. We note however that a new cash entry with a FutHoldType subholding key value of 'PnLCash' appears. This amount is the realized P&L from the previous day's end of day mark-to-market gain (5,000 EUR). We also add an offsetting synthetic cash amount to the Mar 22 Future of the same amount. We can see the 'SynthCash' bucket has decreased from -1,000,000 EUR to -1,005,000 EUR."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>FutCode</th>\n",
       "      <th>FutHoldType</th>\n",
       "      <th>PV</th>\n",
       "      <th>Exposure</th>\n",
       "      <th>Contract Size</th>\n",
       "      <th>Price</th>\n",
       "      <th>Units</th>\n",
       "      <th>Cost</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Dec21</td>\n",
       "      <td>Dec21Future</td>\n",
       "      <td>1,005,000.00</td>\n",
       "      <td>1,005,000.00</td>\n",
       "      <td>100,000.00</td>\n",
       "      <td>100.50</td>\n",
       "      <td>10.00</td>\n",
       "      <td>1,000,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Mar22</td>\n",
       "      <td>Mar22Future</td>\n",
       "      <td>1,005,000.00</td>\n",
       "      <td>1,005,000.00</td>\n",
       "      <td>100,000.00</td>\n",
       "      <td>100.50</td>\n",
       "      <td>10.00</td>\n",
       "      <td>1,005,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Mar22</td>\n",
       "      <td>PnLCash</td>\n",
       "      <td>5,000.00</td>\n",
       "      <td>5,000.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5,000.00</td>\n",
       "      <td>5,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Dec21</td>\n",
       "      <td>SynthCash</td>\n",
       "      <td>-1,000,000.00</td>\n",
       "      <td>-1,000,000.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1,000,000.00</td>\n",
       "      <td>-1,000,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Mar22</td>\n",
       "      <td>SynthCash</td>\n",
       "      <td>-1,005,000.00</td>\n",
       "      <td>-1,005,000.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1,005,000.00</td>\n",
       "      <td>-1,005,000.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  FutCode  FutHoldType            PV      Exposure  Contract Size  Price  \\\n",
       "0   Dec21  Dec21Future  1,005,000.00  1,005,000.00     100,000.00 100.50   \n",
       "4   Mar22  Mar22Future  1,005,000.00  1,005,000.00     100,000.00 100.50   \n",
       "3   Mar22      PnLCash      5,000.00      5,000.00           1.00    NaN   \n",
       "1   Dec21    SynthCash -1,000,000.00 -1,000,000.00           1.00    NaN   \n",
       "2   Mar22    SynthCash -1,005,000.00 -1,005,000.00           1.00    NaN   \n",
       "\n",
       "          Units          Cost  \n",
       "0         10.00  1,000,000.00  \n",
       "4         10.00  1,005,000.00  \n",
       "3      5,000.00      5,000.00  \n",
       "1 -1,000,000.00 -1,000,000.00  \n",
       "2 -1,005,000.00 -1,005,000.00  "
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get_daily_fut_val(\"2021-09-02T08:00:00Z\", portfolio_code, False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "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>FutCode</th>\n",
       "      <th>PV</th>\n",
       "      <th>Exposure</th>\n",
       "      <th>Contract Size</th>\n",
       "      <th>Price</th>\n",
       "      <th>Units</th>\n",
       "      <th>Cost</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Dec21</td>\n",
       "      <td>5,000.00</td>\n",
       "      <td>5,000.00</td>\n",
       "      <td>None</td>\n",
       "      <td>100.50</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Mar22</td>\n",
       "      <td>5,000.00</td>\n",
       "      <td>5,000.00</td>\n",
       "      <td>None</td>\n",
       "      <td>100.50</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  FutCode       PV  Exposure Contract Size  Price Units  Cost\n",
       "0   Dec21 5,000.00  5,000.00          None 100.50  None  None\n",
       "1   Mar22 5,000.00  5,000.00          None 100.50  None  None"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get_daily_fut_val(\"2021-09-02T08:00:00Z\", portfolio_code, True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Intraday\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "At 2:15pm, we decide to book a new transaction in the amount of 5 contracts of each Future at a price of 99.50."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>txn_id</th>\n",
       "      <th>fut_code</th>\n",
       "      <th>fut_synth_cash_code</th>\n",
       "      <th>fut_pnl_cash_code</th>\n",
       "      <th>txn_type</th>\n",
       "      <th>trade_date</th>\n",
       "      <th>quantity</th>\n",
       "      <th>client_id</th>\n",
       "      <th>currency_id</th>\n",
       "      <th>price</th>\n",
       "      <th>total_consideration</th>\n",
       "      <th>portfolio</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>futa_txn_002</td>\n",
       "      <td>Dec21</td>\n",
       "      <td>SynthCash</td>\n",
       "      <td>None</td>\n",
       "      <td>OpenContract</td>\n",
       "      <td>2021-09-02T14:15:00Z</td>\n",
       "      <td>5</td>\n",
       "      <td>FutBund001</td>\n",
       "      <td>EUR</td>\n",
       "      <td>99.50</td>\n",
       "      <td>497500</td>\n",
       "      <td>SynthCashFuturesPortWithDiffCostBasis</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>futb_txn_002</td>\n",
       "      <td>Mar22</td>\n",
       "      <td>SynthCash</td>\n",
       "      <td>None</td>\n",
       "      <td>OpenContract</td>\n",
       "      <td>2021-09-02T14:15:00Z</td>\n",
       "      <td>5</td>\n",
       "      <td>FutBund002</td>\n",
       "      <td>EUR</td>\n",
       "      <td>99.50</td>\n",
       "      <td>497500</td>\n",
       "      <td>SynthCashFuturesPortWithDiffCostBasis</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         txn_id fut_code fut_synth_cash_code fut_pnl_cash_code      txn_type  \\\n",
       "3  futa_txn_002    Dec21           SynthCash              None  OpenContract   \n",
       "4  futb_txn_002    Mar22           SynthCash              None  OpenContract   \n",
       "\n",
       "             trade_date  quantity   client_id currency_id  price  \\\n",
       "3  2021-09-02T14:15:00Z         5  FutBund001         EUR  99.50   \n",
       "4  2021-09-02T14:15:00Z         5  FutBund002         EUR  99.50   \n",
       "\n",
       "   total_consideration                              portfolio  \n",
       "3               497500  SynthCashFuturesPortWithDiffCostBasis  \n",
       "4               497500  SynthCashFuturesPortWithDiffCostBasis  "
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "futures_transactions.iloc[3:5]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We then run a valuation to see the intraday PV of both contracts which is computed as follows:\n",
    "##### PV = (99.50 \\* 10 \\* 100,000) / 100 + (99.50 \\* 5 \\* 100,000) / 100 =  1,492,500 EUR  \n",
    "Where 99.50 is the latest market price (as well as the transaction price of the latest purchase).\n",
    "\n",
    "We also see that the synthetic cash amount for both contracts has decreased by the same purchase amount:\n",
    "##### Change in Synthetic Cash = (99.50 \\* 5 \\* 100,000) / 100 = 497,500"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>FutCode</th>\n",
       "      <th>FutHoldType</th>\n",
       "      <th>PV</th>\n",
       "      <th>Exposure</th>\n",
       "      <th>Contract Size</th>\n",
       "      <th>Price</th>\n",
       "      <th>Units</th>\n",
       "      <th>Cost</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Dec21</td>\n",
       "      <td>Dec21Future</td>\n",
       "      <td>1,492,500.00</td>\n",
       "      <td>1,492,500.00</td>\n",
       "      <td>100,000.00</td>\n",
       "      <td>99.50</td>\n",
       "      <td>15.00</td>\n",
       "      <td>1,497,500.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Mar22</td>\n",
       "      <td>Mar22Future</td>\n",
       "      <td>1,492,500.00</td>\n",
       "      <td>1,492,500.00</td>\n",
       "      <td>100,000.00</td>\n",
       "      <td>99.50</td>\n",
       "      <td>15.00</td>\n",
       "      <td>1,502,500.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Mar22</td>\n",
       "      <td>PnLCash</td>\n",
       "      <td>5,000.00</td>\n",
       "      <td>5,000.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5,000.00</td>\n",
       "      <td>5,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Dec21</td>\n",
       "      <td>SynthCash</td>\n",
       "      <td>-1,497,500.00</td>\n",
       "      <td>-1,497,500.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1,497,500.00</td>\n",
       "      <td>-1,497,500.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Mar22</td>\n",
       "      <td>SynthCash</td>\n",
       "      <td>-1,502,500.00</td>\n",
       "      <td>-1,502,500.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1,502,500.00</td>\n",
       "      <td>-1,502,500.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  FutCode  FutHoldType            PV      Exposure  Contract Size  Price  \\\n",
       "0   Dec21  Dec21Future  1,492,500.00  1,492,500.00     100,000.00  99.50   \n",
       "4   Mar22  Mar22Future  1,492,500.00  1,492,500.00     100,000.00  99.50   \n",
       "3   Mar22      PnLCash      5,000.00      5,000.00           1.00    NaN   \n",
       "1   Dec21    SynthCash -1,497,500.00 -1,497,500.00           1.00    NaN   \n",
       "2   Mar22    SynthCash -1,502,500.00 -1,502,500.00           1.00    NaN   \n",
       "\n",
       "          Units          Cost  \n",
       "0         15.00  1,497,500.00  \n",
       "4         15.00  1,502,500.00  \n",
       "3      5,000.00      5,000.00  \n",
       "1 -1,497,500.00 -1,497,500.00  \n",
       "2 -1,502,500.00 -1,502,500.00  "
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get_daily_fut_val(\"2021-09-02T14:15:00Z\", portfolio_code, False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "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>FutCode</th>\n",
       "      <th>PV</th>\n",
       "      <th>Exposure</th>\n",
       "      <th>Contract Size</th>\n",
       "      <th>Price</th>\n",
       "      <th>Units</th>\n",
       "      <th>Cost</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Dec21</td>\n",
       "      <td>-5,000.00</td>\n",
       "      <td>-5,000.00</td>\n",
       "      <td>None</td>\n",
       "      <td>99.50</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Mar22</td>\n",
       "      <td>-5,000.00</td>\n",
       "      <td>-5,000.00</td>\n",
       "      <td>None</td>\n",
       "      <td>99.50</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  FutCode        PV  Exposure Contract Size  Price Units  Cost\n",
       "0   Dec21 -5,000.00 -5,000.00          None  99.50  None  None\n",
       "1   Mar22 -5,000.00 -5,000.00          None  99.50  None  None"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get_daily_fut_val(\"2021-09-02T14:15:00Z\", portfolio_code, True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### End of Day\n",
    "At the close of the day, both Futures prices have moved down further to 98.00. Our PVs are now computed as follows:\n",
    "\n",
    "##### PV = (98.00 \\* 10 \\* 100,000) / 100 + (98.00 \\* 5 \\* 100,000) / 100 =  1,470,500 EUR  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "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>FutCode</th>\n",
       "      <th>FutHoldType</th>\n",
       "      <th>PV</th>\n",
       "      <th>Exposure</th>\n",
       "      <th>Contract Size</th>\n",
       "      <th>Price</th>\n",
       "      <th>Units</th>\n",
       "      <th>Cost</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Dec21</td>\n",
       "      <td>Dec21Future</td>\n",
       "      <td>1,470,000.00</td>\n",
       "      <td>1,470,000.00</td>\n",
       "      <td>100,000.00</td>\n",
       "      <td>98.00</td>\n",
       "      <td>15.00</td>\n",
       "      <td>1,497,500.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Mar22</td>\n",
       "      <td>Mar22Future</td>\n",
       "      <td>1,470,000.00</td>\n",
       "      <td>1,470,000.00</td>\n",
       "      <td>100,000.00</td>\n",
       "      <td>98.00</td>\n",
       "      <td>15.00</td>\n",
       "      <td>1,502,500.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Mar22</td>\n",
       "      <td>PnLCash</td>\n",
       "      <td>5,000.00</td>\n",
       "      <td>5,000.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5,000.00</td>\n",
       "      <td>5,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Dec21</td>\n",
       "      <td>SynthCash</td>\n",
       "      <td>-1,497,500.00</td>\n",
       "      <td>-1,497,500.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1,497,500.00</td>\n",
       "      <td>-1,497,500.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Mar22</td>\n",
       "      <td>SynthCash</td>\n",
       "      <td>-1,502,500.00</td>\n",
       "      <td>-1,502,500.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1,502,500.00</td>\n",
       "      <td>-1,502,500.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  FutCode  FutHoldType            PV      Exposure  Contract Size  Price  \\\n",
       "0   Dec21  Dec21Future  1,470,000.00  1,470,000.00     100,000.00  98.00   \n",
       "4   Mar22  Mar22Future  1,470,000.00  1,470,000.00     100,000.00  98.00   \n",
       "3   Mar22      PnLCash      5,000.00      5,000.00           1.00    NaN   \n",
       "1   Dec21    SynthCash -1,497,500.00 -1,497,500.00           1.00    NaN   \n",
       "2   Mar22    SynthCash -1,502,500.00 -1,502,500.00           1.00    NaN   \n",
       "\n",
       "          Units          Cost  \n",
       "0         15.00  1,497,500.00  \n",
       "4         15.00  1,502,500.00  \n",
       "3      5,000.00      5,000.00  \n",
       "1 -1,497,500.00 -1,497,500.00  \n",
       "2 -1,502,500.00 -1,502,500.00  "
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get_daily_fut_val(\"2021-09-02T17:00:00Z\", portfolio_code, False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "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>FutCode</th>\n",
       "      <th>PV</th>\n",
       "      <th>Exposure</th>\n",
       "      <th>Contract Size</th>\n",
       "      <th>Price</th>\n",
       "      <th>Units</th>\n",
       "      <th>Cost</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Dec21</td>\n",
       "      <td>-27,500.00</td>\n",
       "      <td>-27,500.00</td>\n",
       "      <td>None</td>\n",
       "      <td>98.00</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Mar22</td>\n",
       "      <td>-27,500.00</td>\n",
       "      <td>-27,500.00</td>\n",
       "      <td>None</td>\n",
       "      <td>98.00</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  FutCode         PV   Exposure Contract Size  Price Units  Cost\n",
       "0   Dec21 -27,500.00 -27,500.00          None  98.00  None  None\n",
       "1   Mar22 -27,500.00 -27,500.00          None  98.00  None  None"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get_daily_fut_val(\"2021-09-02T17:00:00Z\", portfolio_code, True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Day 3\n",
    "#### Start of Day\n",
    "At 8:00am on Day 3, we book a 'RealisePnlDecrease' transaction to adjust the cost basis of the Mar 22 Bund Future downwards in the amount of yesterday's P&L giving us a start of day PV of 0.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>txn_id</th>\n",
       "      <th>fut_code</th>\n",
       "      <th>fut_synth_cash_code</th>\n",
       "      <th>fut_pnl_cash_code</th>\n",
       "      <th>txn_type</th>\n",
       "      <th>trade_date</th>\n",
       "      <th>quantity</th>\n",
       "      <th>client_id</th>\n",
       "      <th>currency_id</th>\n",
       "      <th>price</th>\n",
       "      <th>total_consideration</th>\n",
       "      <th>portfolio</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>rpnbl_txn_002</td>\n",
       "      <td>Mar22</td>\n",
       "      <td>SynthCash</td>\n",
       "      <td>PnLCash</td>\n",
       "      <td>RealisePnLDecrease</td>\n",
       "      <td>2021-09-03T08:00:00Z</td>\n",
       "      <td>0</td>\n",
       "      <td>FutBund002</td>\n",
       "      <td>EUR</td>\n",
       "      <td>0.00</td>\n",
       "      <td>32500</td>\n",
       "      <td>SynthCashFuturesPortWithDiffCostBasis</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          txn_id fut_code fut_synth_cash_code fut_pnl_cash_code  \\\n",
       "5  rpnbl_txn_002    Mar22           SynthCash           PnLCash   \n",
       "\n",
       "             txn_type            trade_date  quantity   client_id currency_id  \\\n",
       "5  RealisePnLDecrease  2021-09-03T08:00:00Z         0  FutBund002         EUR   \n",
       "\n",
       "   price  total_consideration                              portfolio  \n",
       "5   0.00                32500  SynthCashFuturesPortWithDiffCostBasis  "
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "futures_transactions.iloc[5:6]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We then take a start of day valuation of the two contracts and can see the PVs remain unchanged from the previous days close with a market price of 98.00. The PnL cash line has decreased by 32,500 EUR.\n",
    "At the start of the day, we take a valuation with a price of 98.00."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "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>FutCode</th>\n",
       "      <th>FutHoldType</th>\n",
       "      <th>PV</th>\n",
       "      <th>Exposure</th>\n",
       "      <th>Contract Size</th>\n",
       "      <th>Price</th>\n",
       "      <th>Units</th>\n",
       "      <th>Cost</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Dec21</td>\n",
       "      <td>Dec21Future</td>\n",
       "      <td>1,470,000.00</td>\n",
       "      <td>1,470,000.00</td>\n",
       "      <td>100,000.00</td>\n",
       "      <td>98.00</td>\n",
       "      <td>15.00</td>\n",
       "      <td>1,497,500.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Mar22</td>\n",
       "      <td>Mar22Future</td>\n",
       "      <td>1,470,000.00</td>\n",
       "      <td>1,470,000.00</td>\n",
       "      <td>100,000.00</td>\n",
       "      <td>98.00</td>\n",
       "      <td>15.00</td>\n",
       "      <td>1,470,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Mar22</td>\n",
       "      <td>PnLCash</td>\n",
       "      <td>-27,500.00</td>\n",
       "      <td>-27,500.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-27,500.00</td>\n",
       "      <td>-27,500.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Dec21</td>\n",
       "      <td>SynthCash</td>\n",
       "      <td>-1,497,500.00</td>\n",
       "      <td>-1,497,500.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1,497,500.00</td>\n",
       "      <td>-1,497,500.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Mar22</td>\n",
       "      <td>SynthCash</td>\n",
       "      <td>-1,470,000.00</td>\n",
       "      <td>-1,470,000.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1,470,000.00</td>\n",
       "      <td>-1,470,000.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  FutCode  FutHoldType            PV      Exposure  Contract Size  Price  \\\n",
       "0   Dec21  Dec21Future  1,470,000.00  1,470,000.00     100,000.00  98.00   \n",
       "4   Mar22  Mar22Future  1,470,000.00  1,470,000.00     100,000.00  98.00   \n",
       "3   Mar22      PnLCash    -27,500.00    -27,500.00           1.00    NaN   \n",
       "1   Dec21    SynthCash -1,497,500.00 -1,497,500.00           1.00    NaN   \n",
       "2   Mar22    SynthCash -1,470,000.00 -1,470,000.00           1.00    NaN   \n",
       "\n",
       "          Units          Cost  \n",
       "0         15.00  1,497,500.00  \n",
       "4         15.00  1,470,000.00  \n",
       "3    -27,500.00    -27,500.00  \n",
       "1 -1,497,500.00 -1,497,500.00  \n",
       "2 -1,470,000.00 -1,470,000.00  "
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get_daily_fut_val(\"2021-09-03T08:00:00Z\", portfolio_code, False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "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>FutCode</th>\n",
       "      <th>PV</th>\n",
       "      <th>Exposure</th>\n",
       "      <th>Contract Size</th>\n",
       "      <th>Price</th>\n",
       "      <th>Units</th>\n",
       "      <th>Cost</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Dec21</td>\n",
       "      <td>-27,500.00</td>\n",
       "      <td>-27,500.00</td>\n",
       "      <td>None</td>\n",
       "      <td>98.00</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Mar22</td>\n",
       "      <td>-27,500.00</td>\n",
       "      <td>-27,500.00</td>\n",
       "      <td>None</td>\n",
       "      <td>98.00</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  FutCode         PV   Exposure Contract Size  Price Units  Cost\n",
       "0   Dec21 -27,500.00 -27,500.00          None  98.00  None  None\n",
       "1   Mar22 -27,500.00 -27,500.00          None  98.00  None  None"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get_daily_fut_val(\"2021-09-03T08:00:00Z\", portfolio_code, True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Intraday\n",
    "\n",
    "Intraday, we take a valuation of our portfolio where the price has moved to 104.00\n",
    "\n",
    "##### PV = (104.00 * 15 * 10,000) / 100 = 1,560,000 EUR\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "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>FutCode</th>\n",
       "      <th>FutHoldType</th>\n",
       "      <th>PV</th>\n",
       "      <th>Exposure</th>\n",
       "      <th>Contract Size</th>\n",
       "      <th>Price</th>\n",
       "      <th>Units</th>\n",
       "      <th>Cost</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Dec21</td>\n",
       "      <td>Dec21Future</td>\n",
       "      <td>1,560,000.00</td>\n",
       "      <td>1,560,000.00</td>\n",
       "      <td>100,000.00</td>\n",
       "      <td>104.00</td>\n",
       "      <td>15.00</td>\n",
       "      <td>1,497,500.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Mar22</td>\n",
       "      <td>Mar22Future</td>\n",
       "      <td>1,560,000.00</td>\n",
       "      <td>1,560,000.00</td>\n",
       "      <td>100,000.00</td>\n",
       "      <td>104.00</td>\n",
       "      <td>15.00</td>\n",
       "      <td>1,470,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Mar22</td>\n",
       "      <td>PnLCash</td>\n",
       "      <td>-27,500.00</td>\n",
       "      <td>-27,500.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-27,500.00</td>\n",
       "      <td>-27,500.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Dec21</td>\n",
       "      <td>SynthCash</td>\n",
       "      <td>-1,497,500.00</td>\n",
       "      <td>-1,497,500.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1,497,500.00</td>\n",
       "      <td>-1,497,500.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Mar22</td>\n",
       "      <td>SynthCash</td>\n",
       "      <td>-1,470,000.00</td>\n",
       "      <td>-1,470,000.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1,470,000.00</td>\n",
       "      <td>-1,470,000.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  FutCode  FutHoldType            PV      Exposure  Contract Size  Price  \\\n",
       "0   Dec21  Dec21Future  1,560,000.00  1,560,000.00     100,000.00 104.00   \n",
       "4   Mar22  Mar22Future  1,560,000.00  1,560,000.00     100,000.00 104.00   \n",
       "3   Mar22      PnLCash    -27,500.00    -27,500.00           1.00    NaN   \n",
       "1   Dec21    SynthCash -1,497,500.00 -1,497,500.00           1.00    NaN   \n",
       "2   Mar22    SynthCash -1,470,000.00 -1,470,000.00           1.00    NaN   \n",
       "\n",
       "          Units          Cost  \n",
       "0         15.00  1,497,500.00  \n",
       "4         15.00  1,470,000.00  \n",
       "3    -27,500.00    -27,500.00  \n",
       "1 -1,497,500.00 -1,497,500.00  \n",
       "2 -1,470,000.00 -1,470,000.00  "
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get_daily_fut_val(\"2021-09-03T14:15:00Z\", portfolio_code, False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "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>FutCode</th>\n",
       "      <th>PV</th>\n",
       "      <th>Exposure</th>\n",
       "      <th>Contract Size</th>\n",
       "      <th>Price</th>\n",
       "      <th>Units</th>\n",
       "      <th>Cost</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Dec21</td>\n",
       "      <td>62,500.00</td>\n",
       "      <td>62,500.00</td>\n",
       "      <td>None</td>\n",
       "      <td>104.00</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Mar22</td>\n",
       "      <td>62,500.00</td>\n",
       "      <td>62,500.00</td>\n",
       "      <td>None</td>\n",
       "      <td>104.00</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  FutCode        PV  Exposure Contract Size  Price Units  Cost\n",
       "0   Dec21 62,500.00 62,500.00          None 104.00  None  None\n",
       "1   Mar22 62,500.00 62,500.00          None 104.00  None  None"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get_daily_fut_val(\"2021-09-03T14:15:00Z\", portfolio_code, True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### End of Day\n",
    "\n",
    "Similar to our intraday valuation, we see the price hasn't changed at 104.00. We take a final end of day valuation.\n",
    "\n",
    "##### PV = (104.00 * 15 * 10,000) / 100 = 1,560,000 EUR"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "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>FutCode</th>\n",
       "      <th>FutHoldType</th>\n",
       "      <th>PV</th>\n",
       "      <th>Exposure</th>\n",
       "      <th>Contract Size</th>\n",
       "      <th>Price</th>\n",
       "      <th>Units</th>\n",
       "      <th>Cost</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Dec21</td>\n",
       "      <td>Dec21Future</td>\n",
       "      <td>1,560,000.00</td>\n",
       "      <td>1,560,000.00</td>\n",
       "      <td>100,000.00</td>\n",
       "      <td>104.00</td>\n",
       "      <td>15.00</td>\n",
       "      <td>1,497,500.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Mar22</td>\n",
       "      <td>Mar22Future</td>\n",
       "      <td>1,560,000.00</td>\n",
       "      <td>1,560,000.00</td>\n",
       "      <td>100,000.00</td>\n",
       "      <td>104.00</td>\n",
       "      <td>15.00</td>\n",
       "      <td>1,470,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Mar22</td>\n",
       "      <td>PnLCash</td>\n",
       "      <td>-27,500.00</td>\n",
       "      <td>-27,500.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-27,500.00</td>\n",
       "      <td>-27,500.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Dec21</td>\n",
       "      <td>SynthCash</td>\n",
       "      <td>-1,497,500.00</td>\n",
       "      <td>-1,497,500.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1,497,500.00</td>\n",
       "      <td>-1,497,500.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Mar22</td>\n",
       "      <td>SynthCash</td>\n",
       "      <td>-1,470,000.00</td>\n",
       "      <td>-1,470,000.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1,470,000.00</td>\n",
       "      <td>-1,470,000.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  FutCode  FutHoldType            PV      Exposure  Contract Size  Price  \\\n",
       "0   Dec21  Dec21Future  1,560,000.00  1,560,000.00     100,000.00 104.00   \n",
       "4   Mar22  Mar22Future  1,560,000.00  1,560,000.00     100,000.00 104.00   \n",
       "3   Mar22      PnLCash    -27,500.00    -27,500.00           1.00    NaN   \n",
       "1   Dec21    SynthCash -1,497,500.00 -1,497,500.00           1.00    NaN   \n",
       "2   Mar22    SynthCash -1,470,000.00 -1,470,000.00           1.00    NaN   \n",
       "\n",
       "          Units          Cost  \n",
       "0         15.00  1,497,500.00  \n",
       "4         15.00  1,470,000.00  \n",
       "3    -27,500.00    -27,500.00  \n",
       "1 -1,497,500.00 -1,497,500.00  \n",
       "2 -1,470,000.00 -1,470,000.00  "
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get_daily_fut_val(\"2021-09-03T17:00:00Z\", portfolio_code, False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {
    "scrolled": true
   },
   "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>FutCode</th>\n",
       "      <th>PV</th>\n",
       "      <th>Exposure</th>\n",
       "      <th>Contract Size</th>\n",
       "      <th>Price</th>\n",
       "      <th>Units</th>\n",
       "      <th>Cost</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Dec21</td>\n",
       "      <td>62,500.00</td>\n",
       "      <td>62,500.00</td>\n",
       "      <td>None</td>\n",
       "      <td>104.00</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Mar22</td>\n",
       "      <td>62,500.00</td>\n",
       "      <td>62,500.00</td>\n",
       "      <td>None</td>\n",
       "      <td>104.00</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  FutCode        PV  Exposure Contract Size  Price Units  Cost\n",
       "0   Dec21 62,500.00 62,500.00          None 104.00  None  None\n",
       "1   Mar22 62,500.00 62,500.00          None 104.00  None  None"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get_daily_fut_val(\"2021-09-03T17:00:00Z\", portfolio_code, True)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.10.7 64-bit",
   "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.7"
  },
  "vscode": {
   "interpreter": {
    "hash": "b0fa6594d8f4cbf19f97940f81e996739fb7646882a419484c72d19e05852a7e"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}