V1/features/instruments/Futures Valuation with notional cost and variation margin.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 673,
   "metadata": {
    "dotnet_interactive": {
     "language": "csharp"
    }
   },
   "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",
    "import os\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 and without Daily Close outs\n",
    "\n",
    "In this notebook, we demonstrate how P&L can be computed for Futures instruments under two different cash accounting treatments. 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",
    "\n",
    "### Dec 21 Bund Futures with Unrealized P&L (‘non-close out’)\n",
    "In our first example, we look at a Futures contract whereby the contract's daily P&L is left as unrealised on the position as opposed to marked-to-market on a daily basis.\n",
    "\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 as variation margin. 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 Futures](#-Create-Futures)\n",
    "* [3. Transactions](#-Transactions)\n",
    "* [4. Quotes](#-Quotes)\n",
    "* [5. Valuations](#-Valuations)\n",
    "* [6. A2B report](#-A2B-report)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 674,
   "metadata": {
    "dotnet_interactive": {
     "language": "csharp"
    },
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style>.container { width:90% !important; }</style>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "LUSID Environment Initialised\n"
     ]
    }
   ],
   "source": [
    "# Import generic non-LUSID packages\n",
    "import os\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "from datetime import datetime\n",
    "import json\n",
    "import pytz\n",
    "from IPython.core.display import HTML\n",
    "import uuid\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 lusidtools.cocoon.cocoon import load_from_data_frame\n",
    "from lusidtools.cocoon.utilities import create_scope_id\n",
    "from lusidjam import RefreshingToken\n",
    "\n",
    "# Set DataFrame display formats\n",
    "pd.set_option(\"display.max_columns\", None)\n",
    "pd.set_option(\"display.max_rows\", None)\n",
    "pd.options.display.float_format = \"{:,.2f}\".format\n",
    "display(HTML(\"<style>.container { width:90% !important; }</style>\"))\n",
    "\n",
    "# Authenticate our user and create our API client\n",
    "secrets_path = os.getenv(\"FBN_SECRETS_PATH\")\n",
    "\n",
    "# For running the notebook locally\n",
    "if secrets_path is None:\n",
    "    secrets_path = os.path.join(os.path.dirname(os.getcwd()), \"secrets.json\")\n",
    "\n",
    "# Authenticate our user and create our API client\n",
    "api_factory = lusid.utilities.ApiClientFactory(\n",
    "    token=RefreshingToken(),\n",
    "    api_secrets_filename=secrets_path,\n",
    "    app_name=\"LusidFuturesNotebook\"\n",
    ")\n",
    "\n",
    "print(\"LUSID Environment Initialised\")\n",
    "\n",
    "print(\n",
    "\n",
    "  \"LUSID API Version :\",\n",
    "\n",
    "  api_factory.build(lusid.api.ApplicationMetadataApi).get_lusid_versions().build_version,\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "dotnet_interactive": {
     "language": "csharp"
    }
   },
   "outputs": [],
   "source": [
    "# LUSID Variable Definitions\n",
    "portfolio_api = api_factory.build(lusid.api.PortfoliosApi)\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": null,
   "metadata": {
    "dotnet_interactive": {
     "language": "csharp"
    }
   },
   "outputs": [],
   "source": [
    "# Define scopes\n",
    "scope = \"FuturesValuationNotebook\"\n",
    "quotes_scope = \"FuturesValuationNotebook\"\n",
    "portfolio_code = \"FutureWithNotionalCost\"\n",
    "recipeCode = \"futuresValuation\"\n",
    "instruments_scope=\"FuturesValuationNotebook\"\n",
    "base_currency = \"EUR\"\n",
    "transaction_type_scope = create_scope_id()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 1. Create Portfolio<a class=\"anchor\" id=\"-Create-Portfolio\"></a>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "dotnet_interactive": {
     "language": "csharp"
    },
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Could not create a portfolio with id 'FutureWithNotionalCost' because it already exists in scope 'ibor'.\n"
     ]
    }
   ],
   "source": [
    "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=base_currency,\n",
    "            created=\"2010-01-01\",\n",
    "            sub_holding_keys=[],\n",
    "            instrument_scopes=[instruments_scope]\n",
    "        ),\n",
    "    )\n",
    "\n",
    "except lusid.ApiException as e:\n",
    "    if(e.status == 401):\n",
    "        print(e.reason)\n",
    "    else:\n",
    "        print(json.loads(e.body)[\"title\"])\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 2. Create Futures<a class=\"anchor\" id=\"-Create-Futures\"></a>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "dotnet_interactive": {
     "language": "csharp"
    }
   },
   "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, scope=instruments_scope)\n",
    "    futLuid = upsertResponse.values[fut_identifier].lusid_instrument_id\n",
    "    print(futLuid)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.1 Create Bund Futures Contract Expiring Dec 21"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "dotnet_interactive": {
     "language": "csharp"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "LUID_00006A64\n"
     ]
    }
   ],
   "source": [
    "start_date = datetime(2021, 3, 8, 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": [
    "## 2.2 Create Bund Futures Contract Expiring Mar 22"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "dotnet_interactive": {
     "language": "csharp"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "LUID_00007VI6\n"
     ]
    }
   ],
   "source": [
    "start_date = datetime(2021, 6, 8, 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": [
    "# 3. Transactions<a class=\"anchor\" id=\"-Transactions\"></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3.1 Create Transaction Types\n",
    "\n",
    "To book Futures transactions under the close out and non-close out method, we need to create two sides and three transaction types. \n",
    "\n",
    "See documentation for sides [here](https://support.lusid.com/knowledgebase/article/KA-01875/en-us) and for transaction types [here](https://support.lusid.com/knowledgebase/article/KA-01749/en-us).\n",
    "\n",
    "### 3.1.1 Sides\n",
    "A side specifies what fields or properties of a transaction to use when generating holdings.\n",
    "\n",
    "#### Notional\n",
    "The definition for this side specifies the following:\n",
    "  * **Security**: Use the `LusidInstrumentId` field from the transaction which will have the identifier of the instrument to book the trade against\n",
    "  * **Currency**: Use the currency specified on the transaction as the currency of this trade\n",
    "  * **Rate**: Use the rate specified on the transaction to convert between the trade currency and portfolio currency\n",
    "  * **Units**: Use the `Units` fields on the transaction as the value for the number of securities traded\n",
    "  * **Amount**: Use the `TotalConsideration` field on the transaction as the value for the value of this trade\n",
    "  * **Notional amount**: Use the value on the property with the `Transaction/default/NotionalAmount` key on the transaction as the underlying value of the Future. Note that the property definition for this property is a system default. \n",
    "\n",
    "#### VM\n",
    "Similar to the 'Notional' side, without using the notional amount; this side will be used to record the close out trade\n",
    "\n",
    "### 3.1.2 Transaction Types\n",
    "\n",
    "#### OpenContract\n",
    "\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 decreases the cash position by the `TotalConsideration` amount on the transaction, used to record any fees for opening the contract.\n",
    "\n",
    "#### CloseContract\n",
    "\n",
    "The second transaction type 'CloseContract' is the same as 'OpenContract' in the opposite direction. \n",
    "\n",
    "#### VM\n",
    "\n",
    "The third transaction type is 'VM' which is used to adjust the cost basis (variation margin) based on the position's daily P&L.\n",
    "This contains two movements representing the two economic changes to the portfolio:\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 csv file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "dotnet_interactive": {
     "language": "csharp"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Notional already exists in LUSID\n",
      "VM already exists in LUSID\n",
      "OpenContract has been created in LUSID\n",
      "CloseContract has been created in LUSID\n",
      "VM has been created in LUSID\n"
     ]
    }
   ],
   "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",
    "# Construct custom sides\n",
    "side_list = [\n",
    "    models.SidesDefinitionRequest(\n",
    "        side=\"Notional\", \n",
    "        side_request=models.SideDefinitionRequest(\n",
    "            security=\"Txn:LusidInstrumentId\",\n",
    "            currency=\"Txn:TradeCurrency\",\n",
    "            rate=\"Txn:TradeToPortfolioRate\",\n",
    "            units=\"Txn:Units\",\n",
    "            amount=\"Txn:TotalConsideration\",\n",
    "            notional_amount=\"Transaction/default/NotionalAmount\"\n",
    "    )),\n",
    "    models.SidesDefinitionRequest(\n",
    "        side=\"VM\",\n",
    "        side_request=models.SideDefinitionRequest(\n",
    "             security=\"Txn:LusidInstrumentId\",\n",
    "             currency=\"Txn:TradeCurrency\",\n",
    "             rate=\"Txn:TradeToPortfolioRate\",\n",
    "             units=\"Txn:Units\",\n",
    "             amount=\"Txn:TotalConsideration\"\n",
    "     ))\n",
    "]\n",
    "\n",
    "# Set all sides in the non-default transaction type scope\n",
    "transaction_configuration_api.set_side_definitions(default_side_definitions + side_list, scope = transaction_type_scope)\n",
    "\n",
    "transaction_type_req = [\n",
    "   (\"OpenContract\", models.TransactionTypeRequest(\n",
    "        aliases=[\n",
    "            models.TransactionTypeAlias(\n",
    "                type=\"OpenContract\",\n",
    "                description=\"Open a long future contract\",\n",
    "                transaction_class=\"Futures\",\n",
    "                transaction_roles=\"LongLonger\",\n",
    "            )\n",
    "        ],\n",
    "        movements=[\n",
    "            models.TransactionTypeMovement(\n",
    "                movement_types=\"StockMovement\",\n",
    "                side=\"Notional\",\n",
    "                direction=1,\n",
    "                properties=None,\n",
    "                mappings=[],\n",
    "                name=\"Open contract\"\n",
    "            ),\n",
    "            models.TransactionTypeMovement(\n",
    "                movement_types=\"CashCommitment\",\n",
    "                side=\"Side2\",\n",
    "                direction=-1,\n",
    "                properties=None,\n",
    "                mappings=[],\n",
    "                name=\"Fees\"\n",
    "            )\n",
    "        ],\n",
    "        properties=None,\n",
    "    )),\n",
    "    (\"CloseContract\", models.TransactionTypeRequest(\n",
    "        aliases=[\n",
    "            models.TransactionTypeAlias(\n",
    "                type=\"CloseContract\",\n",
    "                description=\"Close a long future contract\",\n",
    "                transaction_class=\"Futures\",\n",
    "                transaction_roles=\"LongShorter\",\n",
    "            )\n",
    "        ],\n",
    "        movements=[\n",
    "            models.TransactionTypeMovement(\n",
    "                movement_types=\"StockMovement\",\n",
    "                side=\"Notional\",\n",
    "                direction=-1,\n",
    "                properties=None,\n",
    "                mappings=[],\n",
    "                name=\"Close contract\"\n",
    "            ),\n",
    "            models.TransactionTypeMovement(\n",
    "                movement_types=\"CashCommitment\",\n",
    "                side=\"Side2\",\n",
    "                direction=1,\n",
    "                properties=None,\n",
    "                mappings=[],\n",
    "                name=\"Fees\"\n",
    "            )\n",
    "        ],\n",
    "        properties=None,\n",
    "    )),\n",
    "   (\"VM\", models.TransactionType(\n",
    "        aliases=[\n",
    "            models.TransactionTypeAlias(\n",
    "                type=\"VM\",\n",
    "                description=\"Increase cost basis and adjust cash\",\n",
    "                transaction_class=\"Futures\",\n",
    "                transaction_roles=\"AllRoles\",\n",
    "            )\n",
    "        ],\n",
    "        movements=[\n",
    "            models.TransactionTypeMovement(\n",
    "                movement_types=\"VariationMargin\",\n",
    "                side=\"VM\",\n",
    "                direction=1,\n",
    "                properties=None,\n",
    "                mappings=[],\n",
    "                name=\"Variation margin\"\n",
    "            ), models.TransactionTypeMovement(\n",
    "                movement_types=\"CashReceivable\",\n",
    "                side=\"Side2\",\n",
    "                direction=1,\n",
    "                properties=None,\n",
    "                mappings=[],\n",
    "                name=\"VM Received/Paid\"\n",
    "            )\n",
    "        ],\n",
    "        properties=None,\n",
    "    ))\n",
    "]\n",
    "\n",
    "for(type, type_req) in transaction_type_req:\n",
    "    transaction_configuration_api.set_transaction_type(\"default\", type, type_req, scope=transaction_type_scope)\n",
    "    print(f\"{type} has been created in LUSID\")\n",
    "\n",
    "# Having created the transaction types in the custom scope, we update the transaction type scope of the 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": [
    "## 3.2 Load Transaction Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "dotnet_interactive": {
     "language": "csharp"
    }
   },
   "outputs": [],
   "source": [
    "# Read in transaction data read from file\n",
    "futures_transactions = pd.read_csv(\"data/futures_notional_data.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "dotnet_interactive": {
     "language": "csharp"
    }
   },
   "outputs": [],
   "source": [
    "transaction_requests=[\n",
    "     models.TransactionRequest(\n",
    "                transaction_id=row[\"txn_id\"],\n",
    "                type=row[\"txn_type\"],\n",
    "                instrument_identifiers={ \"Instrument/default/ClientInternal\": row[\"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=base_currency\n",
    "                ),\n",
    "                properties={\"Transaction/default/NotionalAmount\": models.PerpetualProperty(\n",
    "                    key=\"Transaction/default/NotionalAmount\",\n",
    "                value=models.PropertyValue(metric_value=models.MetricValue(value=row[\"notional_amount\"],unit=\"EUR\")))}\n",
    "            ) for _ , row in futures_transactions.iterrows()\n",
    "]\n",
    "\n",
    "upsert_transactions = transaction_portfolio_api.upsert_transactions(\n",
    "        scope=scope,\n",
    "        code=portfolio_code,\n",
    "        transaction_request=transaction_requests)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 4. Quotes<a class=\"anchor\" id=\"-Quotes\"></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4.1 Book Quotes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "dotnet_interactive": {
     "language": "csharp"
    }
   },
   "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>identifier</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>Day2 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>Day2 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>Day3 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>Day3 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>Day3 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>Day1 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  identifier         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   Day2 intraday trade price  \n",
       "5              100          Day2 closing price  \n",
       "6              100          Day3 opening price  \n",
       "7              100   Day3 intraday trade price  \n",
       "8              100          Day3 closing price  \n",
       "9              100          Day1 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": 654,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Read in futures data from file\n",
    "futures_prices = pd.read_csv(\"data/futures_notional_prices.csv\")\n",
    "# Output futures data\n",
    "futures_prices"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "instrument_quotes = {\n",
    "    index: models.UpsertQuoteRequest(\n",
    "        quote_id=models.QuoteId(\n",
    "            quote_series_id=models.QuoteSeriesId(\n",
    "                provider=\"Lusid\",\n",
    "                instrument_id=row[\"identifier\"],\n",
    "                instrument_id_type=\"ClientInternal\",\n",
    "                quote_type=\"Price\",\n",
    "                field=\"mid\"),\n",
    "            effective_at=row[\"date\"]),\n",
    "        metric_value=models.MetricValue(value=row[\"prices\"], unit=row[\"currency\"]),\n",
    "        scale_factor=row[\"scaling_factor\"]            \n",
    "        ) for index, row in futures_prices.iterrows()\n",
    "    }\n",
    "\n",
    "#Upsert the quotes into LUSID\n",
    "response = quotes_api.upsert_quotes(scope=scope, request_body=instrument_quotes)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 5. Valuations<a class=\"anchor\" id=\"-Valuations\"></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5.1 Create valuation recipe\n",
    "\n",
    "We use a simple recipe that uses the quotes upserted earlier in LUSID and a `ConstantTimeValueofMoney` pricing model that does not apply any discounting."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "dotnet_interactive": {
     "language": "csharp"
    }
   },
   "outputs": [],
   "source": [
    "# Create a recipe to perform a valuation\n",
    "configuration_recipe = models.ConfigurationRecipe(\n",
    "    scope=scope,\n",
    "    code=recipeCode,\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=\"ConstantTimeValueofMoney\",\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": [
    "## 5.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": null,
   "metadata": {
    "dotnet_interactive": {
     "language": "csharp"
    }
   },
   "outputs": [],
   "source": [
    "def get_daily_fut_val(date, portfolio_code):\n",
    "    \n",
    "    metricsList=[\n",
    "            models.AggregateSpec(\"Instrument/default/Name\", \"Value\"),\n",
    "            models.AggregateSpec(\"Instrument/default/ClientInternal\", \"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\", \"Value\"),\n",
    "            models.AggregateSpec(\"Valuation/Exposure/Amount\", \"Value\"),\n",
    "        \n",
    "        ]\n",
    "        \n",
    "    valuation_request = models.ValuationRequest(\n",
    "        recipe_id=models.ResourceId(scope=scope, code=recipeCode),\n",
    "        metrics=metricsList,\n",
    "      \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",
    "    columnsToRename={\n",
    "            \"Instrument/default/Name\": \"InstrumentName\",\n",
    "            \"Instrument/default/ClientInternal\": \"ClientInternal\",\n",
    "            \"Instrument/Definition/ContractSize\": \"Contract Size\",\n",
    "            \"Quotes/Price\": \"Price\",\n",
    "            \"Holding/default/Units\": \"Units\",\n",
    "            \"Holding/default/Cost\": \"Cost\",\n",
    "            \"Valuation/PV\": \"PV\",\n",
    "            \"Valuation/Exposure/Amount\": \"Exposure\",\n",
    "    }      \n",
    "\n",
    "    vals_df.rename(\n",
    "        columns=columnsToRename,\n",
    "        inplace=True,\n",
    "    )\n",
    "\n",
    "    return vals_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5.3 Daily valuations\n",
    "\n",
    "### Day 1\n",
    "\n",
    "#### Start of Day\n",
    "At 8:00am on Day 1, we enter into our two Bund Futures contracts with starting prices of 100.00. The notional amount is 1,000,000 for FutBund001, where  **notional_amount = price * quantity * contractSize**.\n",
    "\n",
    "The *total_consideration* column accounts for the cost of open the contract which is EUR 30 for each contract."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "dotnet_interactive": {
     "language": "csharp"
    }
   },
   "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>txn_type</th>\n",
       "      <th>trade_date</th>\n",
       "      <th>quantity</th>\n",
       "      <th>identifier</th>\n",
       "      <th>price</th>\n",
       "      <th>total_consideration</th>\n",
       "      <th>notional_amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>fut_open_1</td>\n",
       "      <td>OpenContract</td>\n",
       "      <td>2021-09-01T08:00:00Z</td>\n",
       "      <td>10</td>\n",
       "      <td>FutBund001</td>\n",
       "      <td>100</td>\n",
       "      <td>30</td>\n",
       "      <td>1000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>fut_open_2</td>\n",
       "      <td>OpenContract</td>\n",
       "      <td>2021-09-01T08:00:00Z</td>\n",
       "      <td>5</td>\n",
       "      <td>FutBund002</td>\n",
       "      <td>100</td>\n",
       "      <td>30</td>\n",
       "      <td>500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>vm_1</td>\n",
       "      <td>VM</td>\n",
       "      <td>2021-09-02T08:00:00Z</td>\n",
       "      <td>0</td>\n",
       "      <td>FutBund001</td>\n",
       "      <td>0</td>\n",
       "      <td>5000</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>fut_close_1</td>\n",
       "      <td>CloseContract</td>\n",
       "      <td>2021-09-02T14:15:00Z</td>\n",
       "      <td>5</td>\n",
       "      <td>FutBund001</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>fut_close_2</td>\n",
       "      <td>CloseContract</td>\n",
       "      <td>2021-09-02T14:15:00Z</td>\n",
       "      <td>5</td>\n",
       "      <td>FutBund002</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>vm_2</td>\n",
       "      <td>VM</td>\n",
       "      <td>2021-09-03T08:00:00Z</td>\n",
       "      <td>0</td>\n",
       "      <td>FutBund001</td>\n",
       "      <td>0</td>\n",
       "      <td>-12500</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        txn_id       txn_type            trade_date  quantity  identifier  \\\n",
       "0   fut_open_1   OpenContract  2021-09-01T08:00:00Z        10  FutBund001   \n",
       "1   fut_open_2   OpenContract  2021-09-01T08:00:00Z         5  FutBund002   \n",
       "2         vm_1             VM  2021-09-02T08:00:00Z         0  FutBund001   \n",
       "3  fut_close_1  CloseContract  2021-09-02T14:15:00Z         5  FutBund001   \n",
       "4  fut_close_2  CloseContract  2021-09-02T14:15:00Z         5  FutBund002   \n",
       "5         vm_2             VM  2021-09-03T08:00:00Z         0  FutBund001   \n",
       "\n",
       "   price  total_consideration  notional_amount  \n",
       "0    100                   30          1000000  \n",
       "1    100                   30           500000  \n",
       "2      0                 5000                0  \n",
       "3      0                    0                0  \n",
       "4      0                    0                0  \n",
       "5      0               -12500                0  "
      ]
     },
     "execution_count": 658,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "futures_transactions"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We then immediately run a start of day valuation. Our positions have an exposure of EUR 1,000,000 for the Dec21 contract and EUR 500,000 for the Mar 22 one. \n",
    "\n",
    "PV is 0 for both contracts as there has been no price change since entering the contract.\n",
    "\n",
    "The EUR -60.00 position reflects the cost to open the two contracts. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "dotnet_interactive": {
     "language": "csharp"
    }
   },
   "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>Exposure</th>\n",
       "      <th>InstrumentName</th>\n",
       "      <th>ClientInternal</th>\n",
       "      <th>Contract Size</th>\n",
       "      <th>Price</th>\n",
       "      <th>Units</th>\n",
       "      <th>Cost</th>\n",
       "      <th>PV</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1,000,000.00</td>\n",
       "      <td>EURO-BUND FUTURE Dec21</td>\n",
       "      <td>FutBund001</td>\n",
       "      <td>100,000.00</td>\n",
       "      <td>100.00</td>\n",
       "      <td>10.00</td>\n",
       "      <td>30.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>-60.00</td>\n",
       "      <td>Euro</td>\n",
       "      <td>None</td>\n",
       "      <td>1.00</td>\n",
       "      <td>nan</td>\n",
       "      <td>-60.00</td>\n",
       "      <td>-60.00</td>\n",
       "      <td>-60.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>500,000.00</td>\n",
       "      <td>EURO-BUND FUTURE Mar22</td>\n",
       "      <td>FutBund002</td>\n",
       "      <td>100,000.00</td>\n",
       "      <td>100.00</td>\n",
       "      <td>5.00</td>\n",
       "      <td>30.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      Exposure          InstrumentName ClientInternal  Contract Size  Price  \\\n",
       "0 1,000,000.00  EURO-BUND FUTURE Dec21     FutBund001     100,000.00 100.00   \n",
       "1       -60.00                    Euro           None           1.00    nan   \n",
       "2   500,000.00  EURO-BUND FUTURE Mar22     FutBund002     100,000.00 100.00   \n",
       "\n",
       "   Units   Cost     PV  \n",
       "0  10.00  30.00   0.00  \n",
       "1 -60.00 -60.00 -60.00  \n",
       "2   5.00  30.00   0.00  "
      ]
     },
     "execution_count": 659,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get_daily_fut_val(\"2021-09-01T08:00:00Z\", portfolio_code)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Intraday\n",
    "\n",
    "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. This gives us an intraday PV for the contracts of EUR 5,000 and EUR 2,500 respectively."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "dotnet_interactive": {
     "language": "csharp"
    }
   },
   "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>Exposure</th>\n",
       "      <th>InstrumentName</th>\n",
       "      <th>ClientInternal</th>\n",
       "      <th>Contract Size</th>\n",
       "      <th>Price</th>\n",
       "      <th>Units</th>\n",
       "      <th>Cost</th>\n",
       "      <th>PV</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1,005,000.00</td>\n",
       "      <td>EURO-BUND FUTURE Dec21</td>\n",
       "      <td>FutBund001</td>\n",
       "      <td>100,000.00</td>\n",
       "      <td>100.50</td>\n",
       "      <td>10.00</td>\n",
       "      <td>30.00</td>\n",
       "      <td>5,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>-60.00</td>\n",
       "      <td>Euro</td>\n",
       "      <td>None</td>\n",
       "      <td>1.00</td>\n",
       "      <td>nan</td>\n",
       "      <td>-60.00</td>\n",
       "      <td>-60.00</td>\n",
       "      <td>-60.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>502,500.00</td>\n",
       "      <td>EURO-BUND FUTURE Mar22</td>\n",
       "      <td>FutBund002</td>\n",
       "      <td>100,000.00</td>\n",
       "      <td>100.50</td>\n",
       "      <td>5.00</td>\n",
       "      <td>30.00</td>\n",
       "      <td>2,500.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      Exposure          InstrumentName ClientInternal  Contract Size  Price  \\\n",
       "0 1,005,000.00  EURO-BUND FUTURE Dec21     FutBund001     100,000.00 100.50   \n",
       "1       -60.00                    Euro           None           1.00    nan   \n",
       "2   502,500.00  EURO-BUND FUTURE Mar22     FutBund002     100,000.00 100.50   \n",
       "\n",
       "   Units   Cost       PV  \n",
       "0  10.00  30.00 5,000.00  \n",
       "1 -60.00 -60.00   -60.00  \n",
       "2   5.00  30.00 2,500.00  "
      ]
     },
     "execution_count": 660,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get_daily_fut_val(\"2021-09-01T12:00:00Z\", portfolio_code)"
   ]
  },
  {
   "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": null,
   "metadata": {
    "dotnet_interactive": {
     "language": "csharp"
    }
   },
   "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>Exposure</th>\n",
       "      <th>InstrumentName</th>\n",
       "      <th>ClientInternal</th>\n",
       "      <th>Contract Size</th>\n",
       "      <th>Price</th>\n",
       "      <th>Units</th>\n",
       "      <th>Cost</th>\n",
       "      <th>PV</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1,005,000.00</td>\n",
       "      <td>EURO-BUND FUTURE Dec21</td>\n",
       "      <td>FutBund001</td>\n",
       "      <td>100,000.00</td>\n",
       "      <td>100.50</td>\n",
       "      <td>10.00</td>\n",
       "      <td>30.00</td>\n",
       "      <td>5,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>-60.00</td>\n",
       "      <td>Euro</td>\n",
       "      <td>None</td>\n",
       "      <td>1.00</td>\n",
       "      <td>nan</td>\n",
       "      <td>-60.00</td>\n",
       "      <td>-60.00</td>\n",
       "      <td>-60.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>502,500.00</td>\n",
       "      <td>EURO-BUND FUTURE Mar22</td>\n",
       "      <td>FutBund002</td>\n",
       "      <td>100,000.00</td>\n",
       "      <td>100.50</td>\n",
       "      <td>5.00</td>\n",
       "      <td>30.00</td>\n",
       "      <td>2,500.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      Exposure          InstrumentName ClientInternal  Contract Size  Price  \\\n",
       "0 1,005,000.00  EURO-BUND FUTURE Dec21     FutBund001     100,000.00 100.50   \n",
       "1       -60.00                    Euro           None           1.00    nan   \n",
       "2   502,500.00  EURO-BUND FUTURE Mar22     FutBund002     100,000.00 100.50   \n",
       "\n",
       "   Units   Cost       PV  \n",
       "0  10.00  30.00 5,000.00  \n",
       "1 -60.00 -60.00   -60.00  \n",
       "2   5.00  30.00 2,500.00  "
      ]
     },
     "execution_count": 661,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get_daily_fut_val(\"2021-09-01T17:00:00Z\", portfolio_code)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Day 2\n",
    "#### Start of Day\n",
    "\n",
    "At 8:00am on Day 2, we book a 'VariationMargin' transaction in order to adjust the cost basis of the Dec 21 Bund Future upwards such that its start of day PV is 0. This adjustment can be automatically applied in LUSID removing the need to manually book these transactions each day.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "dotnet_interactive": {
     "language": "csharp"
    }
   },
   "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>txn_type</th>\n",
       "      <th>trade_date</th>\n",
       "      <th>quantity</th>\n",
       "      <th>identifier</th>\n",
       "      <th>price</th>\n",
       "      <th>total_consideration</th>\n",
       "      <th>notional_amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>vm_1</td>\n",
       "      <td>VM</td>\n",
       "      <td>2021-09-02T08:00:00Z</td>\n",
       "      <td>0</td>\n",
       "      <td>FutBund001</td>\n",
       "      <td>0</td>\n",
       "      <td>5000</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  txn_id txn_type            trade_date  quantity  identifier  price  \\\n",
       "2   vm_1       VM  2021-09-02T08:00:00Z         0  FutBund001      0   \n",
       "\n",
       "   total_consideration  notional_amount  \n",
       "2                 5000                0  "
      ]
     },
     "execution_count": 662,
     "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 the Mar 22 Futures contract still has a PV of 2,500 EUR while the Dec 21 contract has a PV of 0. This is expected as we've now booked a cost basis adjustment upward in the amount of 5000 EUR for the Dec 21 contract. The Euro position has also increased by 5000 EUR."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "dotnet_interactive": {
     "language": "csharp"
    }
   },
   "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>Exposure</th>\n",
       "      <th>InstrumentName</th>\n",
       "      <th>ClientInternal</th>\n",
       "      <th>Contract Size</th>\n",
       "      <th>Price</th>\n",
       "      <th>Units</th>\n",
       "      <th>Cost</th>\n",
       "      <th>PV</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1,005,000.00</td>\n",
       "      <td>EURO-BUND FUTURE Dec21</td>\n",
       "      <td>FutBund001</td>\n",
       "      <td>100,000.00</td>\n",
       "      <td>100.50</td>\n",
       "      <td>10.00</td>\n",
       "      <td>30.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4,940.00</td>\n",
       "      <td>Euro</td>\n",
       "      <td>None</td>\n",
       "      <td>1.00</td>\n",
       "      <td>nan</td>\n",
       "      <td>4,940.00</td>\n",
       "      <td>4,940.00</td>\n",
       "      <td>4,940.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>502,500.00</td>\n",
       "      <td>EURO-BUND FUTURE Mar22</td>\n",
       "      <td>FutBund002</td>\n",
       "      <td>100,000.00</td>\n",
       "      <td>100.50</td>\n",
       "      <td>5.00</td>\n",
       "      <td>30.00</td>\n",
       "      <td>2,500.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      Exposure          InstrumentName ClientInternal  Contract Size  Price  \\\n",
       "0 1,005,000.00  EURO-BUND FUTURE Dec21     FutBund001     100,000.00 100.50   \n",
       "1     4,940.00                    Euro           None           1.00    nan   \n",
       "2   502,500.00  EURO-BUND FUTURE Mar22     FutBund002     100,000.00 100.50   \n",
       "\n",
       "     Units     Cost       PV  \n",
       "0    10.00    30.00     0.00  \n",
       "1 4,940.00 4,940.00 4,940.00  \n",
       "2     5.00    30.00 2,500.00  "
      ]
     },
     "execution_count": 663,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get_daily_fut_val(\"2021-09-02T08:00:00Z\", portfolio_code)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Intraday\n",
    "\n",
    "At 2:15pm, we decide to book a new transaction closing 5 contracts of each Future."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "dotnet_interactive": {
     "language": "csharp"
    }
   },
   "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>txn_type</th>\n",
       "      <th>trade_date</th>\n",
       "      <th>quantity</th>\n",
       "      <th>identifier</th>\n",
       "      <th>price</th>\n",
       "      <th>total_consideration</th>\n",
       "      <th>notional_amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>fut_close_1</td>\n",
       "      <td>CloseContract</td>\n",
       "      <td>2021-09-02T14:15:00Z</td>\n",
       "      <td>5</td>\n",
       "      <td>FutBund001</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>fut_close_2</td>\n",
       "      <td>CloseContract</td>\n",
       "      <td>2021-09-02T14:15:00Z</td>\n",
       "      <td>5</td>\n",
       "      <td>FutBund002</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        txn_id       txn_type            trade_date  quantity  identifier  \\\n",
       "3  fut_close_1  CloseContract  2021-09-02T14:15:00Z         5  FutBund001   \n",
       "4  fut_close_2  CloseContract  2021-09-02T14:15:00Z         5  FutBund002   \n",
       "\n",
       "   price  total_consideration  notional_amount  \n",
       "3      0                    0                0  \n",
       "4      0                    0                0  "
      ]
     },
     "execution_count": 664,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "futures_transactions.iloc[3:5]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We then run a valuation and see that the intraday PV of the Dec 22 contract is now -5000 EUR while the PV of the March 22 contract is now 0 as there are no contracts left. \n",
    "\n",
    "##### Dec 21 (PV) = (99.50 - 100.00) \\* 10 \\* 100,000 / 100 - (99.50 - 100.00) \\* 10 \\* 100,000 / 100 =  -5,000 EUR  \n",
    "Where 100.00 is the transaction price of the first transaction and where 99.50 is the transaction price of the latest transaction.\n",
    "\n",
    "<br/>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "dotnet_interactive": {
     "language": "csharp"
    }
   },
   "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>Exposure</th>\n",
       "      <th>InstrumentName</th>\n",
       "      <th>ClientInternal</th>\n",
       "      <th>Contract Size</th>\n",
       "      <th>Price</th>\n",
       "      <th>Units</th>\n",
       "      <th>Cost</th>\n",
       "      <th>PV</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>497,500.00</td>\n",
       "      <td>EURO-BUND FUTURE Dec21</td>\n",
       "      <td>FutBund001</td>\n",
       "      <td>100,000.00</td>\n",
       "      <td>99.50</td>\n",
       "      <td>5.00</td>\n",
       "      <td>15.00</td>\n",
       "      <td>-5,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4,940.00</td>\n",
       "      <td>Euro</td>\n",
       "      <td>None</td>\n",
       "      <td>1.00</td>\n",
       "      <td>nan</td>\n",
       "      <td>4,940.00</td>\n",
       "      <td>4,940.00</td>\n",
       "      <td>4,940.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.00</td>\n",
       "      <td>EURO-BUND FUTURE Mar22</td>\n",
       "      <td>FutBund002</td>\n",
       "      <td>100,000.00</td>\n",
       "      <td>99.50</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Exposure          InstrumentName ClientInternal  Contract Size  Price  \\\n",
       "0 497,500.00  EURO-BUND FUTURE Dec21     FutBund001     100,000.00  99.50   \n",
       "1   4,940.00                    Euro           None           1.00    nan   \n",
       "2       0.00  EURO-BUND FUTURE Mar22     FutBund002     100,000.00  99.50   \n",
       "\n",
       "     Units     Cost        PV  \n",
       "0     5.00    15.00 -5,000.00  \n",
       "1 4,940.00 4,940.00  4,940.00  \n",
       "2     0.00     0.00      0.00  "
      ]
     },
     "execution_count": 665,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get_daily_fut_val(\"2021-09-02T14:15:00Z\", portfolio_code)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### End of Day\n",
    "\n",
    "At the close of the day, the future price has moved down further to 98.00. PVs is now computed as follows:\n",
    "\n",
    "##### Dec 21 (PV) = (98.00 - 100.00) \\* 5 \\* 100,000 / 100 - 2,500 = -12,500 EUR\n",
    "Where 100.00 is the cost basis of the first transaction and 2,500 is half of the variation margin transaction booked earlier.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "dotnet_interactive": {
     "language": "csharp"
    }
   },
   "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>Exposure</th>\n",
       "      <th>InstrumentName</th>\n",
       "      <th>ClientInternal</th>\n",
       "      <th>Contract Size</th>\n",
       "      <th>Price</th>\n",
       "      <th>Units</th>\n",
       "      <th>Cost</th>\n",
       "      <th>PV</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>490,000.00</td>\n",
       "      <td>EURO-BUND FUTURE Dec21</td>\n",
       "      <td>FutBund001</td>\n",
       "      <td>100,000.00</td>\n",
       "      <td>98.00</td>\n",
       "      <td>5.00</td>\n",
       "      <td>15.00</td>\n",
       "      <td>-12,500.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4,940.00</td>\n",
       "      <td>Euro</td>\n",
       "      <td>None</td>\n",
       "      <td>1.00</td>\n",
       "      <td>nan</td>\n",
       "      <td>4,940.00</td>\n",
       "      <td>4,940.00</td>\n",
       "      <td>4,940.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.00</td>\n",
       "      <td>EURO-BUND FUTURE Mar22</td>\n",
       "      <td>FutBund002</td>\n",
       "      <td>100,000.00</td>\n",
       "      <td>98.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Exposure          InstrumentName ClientInternal  Contract Size  Price  \\\n",
       "0 490,000.00  EURO-BUND FUTURE Dec21     FutBund001     100,000.00  98.00   \n",
       "1   4,940.00                    Euro           None           1.00    nan   \n",
       "2       0.00  EURO-BUND FUTURE Mar22     FutBund002     100,000.00  98.00   \n",
       "\n",
       "     Units     Cost         PV  \n",
       "0     5.00    15.00 -12,500.00  \n",
       "1 4,940.00 4,940.00   4,940.00  \n",
       "2     0.00     0.00       0.00  "
      ]
     },
     "execution_count": 666,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get_daily_fut_val(\"2021-09-02T17:00:00Z\", portfolio_code)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Day 3\n",
    "#### Start of Day\n",
    "\n",
    "At 8:00am on Day 3, we book a 'VariationMargin' transaction to adjust the cost basis of the Dec 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": null,
   "metadata": {
    "dotnet_interactive": {
     "language": "csharp"
    }
   },
   "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>txn_type</th>\n",
       "      <th>trade_date</th>\n",
       "      <th>quantity</th>\n",
       "      <th>identifier</th>\n",
       "      <th>price</th>\n",
       "      <th>total_consideration</th>\n",
       "      <th>notional_amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>vm_2</td>\n",
       "      <td>VM</td>\n",
       "      <td>2021-09-03T08:00:00Z</td>\n",
       "      <td>0</td>\n",
       "      <td>FutBund001</td>\n",
       "      <td>0</td>\n",
       "      <td>-12500</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  txn_id txn_type            trade_date  quantity  identifier  price  \\\n",
       "5   vm_2       VM  2021-09-03T08:00:00Z         0  FutBund001      0   \n",
       "\n",
       "   total_consideration  notional_amount  \n",
       "5               -12500                0  "
      ]
     },
     "execution_count": 667,
     "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 Dec 21 contract has a PV of 0. We've updated today's cash by subtracting yesterday's PV of -12,500 EUR from yesterdays cash balance of 4,940 EUR to arrive at today's new cash balance of -7,560 EUR.\n",
    "<br/><br/>\n",
    "At the start of the day, we take a valuation with a price of 98.00"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "dotnet_interactive": {
     "language": "csharp"
    }
   },
   "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>Exposure</th>\n",
       "      <th>InstrumentName</th>\n",
       "      <th>ClientInternal</th>\n",
       "      <th>Contract Size</th>\n",
       "      <th>Price</th>\n",
       "      <th>Units</th>\n",
       "      <th>Cost</th>\n",
       "      <th>PV</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>490,000.00</td>\n",
       "      <td>EURO-BUND FUTURE Dec21</td>\n",
       "      <td>FutBund001</td>\n",
       "      <td>100,000.00</td>\n",
       "      <td>98.00</td>\n",
       "      <td>5.00</td>\n",
       "      <td>15.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>-7,560.00</td>\n",
       "      <td>Euro</td>\n",
       "      <td>None</td>\n",
       "      <td>1.00</td>\n",
       "      <td>nan</td>\n",
       "      <td>-7,560.00</td>\n",
       "      <td>-7,560.00</td>\n",
       "      <td>-7,560.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.00</td>\n",
       "      <td>EURO-BUND FUTURE Mar22</td>\n",
       "      <td>FutBund002</td>\n",
       "      <td>100,000.00</td>\n",
       "      <td>98.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Exposure          InstrumentName ClientInternal  Contract Size  Price  \\\n",
       "0 490,000.00  EURO-BUND FUTURE Dec21     FutBund001     100,000.00  98.00   \n",
       "1  -7,560.00                    Euro           None           1.00    nan   \n",
       "2       0.00  EURO-BUND FUTURE Mar22     FutBund002     100,000.00  98.00   \n",
       "\n",
       "      Units      Cost        PV  \n",
       "0      5.00     15.00      0.00  \n",
       "1 -7,560.00 -7,560.00 -7,560.00  \n",
       "2      0.00      0.00      0.00  "
      ]
     },
     "execution_count": 668,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get_daily_fut_val(\"2021-09-03T08:00:00Z\", portfolio_code)"
   ]
  },
  {
   "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",
    "##### Dec 21 (PV) = (104.00 - 98) * 5 * 100,00/100 = 30,000 EUR\n",
    "Where 98 is yesterday's price"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "dotnet_interactive": {
     "language": "csharp"
    }
   },
   "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>Exposure</th>\n",
       "      <th>InstrumentName</th>\n",
       "      <th>ClientInternal</th>\n",
       "      <th>Contract Size</th>\n",
       "      <th>Price</th>\n",
       "      <th>Units</th>\n",
       "      <th>Cost</th>\n",
       "      <th>PV</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>520,000.00</td>\n",
       "      <td>EURO-BUND FUTURE Dec21</td>\n",
       "      <td>FutBund001</td>\n",
       "      <td>100,000.00</td>\n",
       "      <td>104.00</td>\n",
       "      <td>5.00</td>\n",
       "      <td>15.00</td>\n",
       "      <td>30,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>-7,560.00</td>\n",
       "      <td>Euro</td>\n",
       "      <td>None</td>\n",
       "      <td>1.00</td>\n",
       "      <td>nan</td>\n",
       "      <td>-7,560.00</td>\n",
       "      <td>-7,560.00</td>\n",
       "      <td>-7,560.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.00</td>\n",
       "      <td>EURO-BUND FUTURE Mar22</td>\n",
       "      <td>FutBund002</td>\n",
       "      <td>100,000.00</td>\n",
       "      <td>104.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Exposure          InstrumentName ClientInternal  Contract Size  Price  \\\n",
       "0 520,000.00  EURO-BUND FUTURE Dec21     FutBund001     100,000.00 104.00   \n",
       "1  -7,560.00                    Euro           None           1.00    nan   \n",
       "2       0.00  EURO-BUND FUTURE Mar22     FutBund002     100,000.00 104.00   \n",
       "\n",
       "      Units      Cost        PV  \n",
       "0      5.00     15.00 30,000.00  \n",
       "1 -7,560.00 -7,560.00 -7,560.00  \n",
       "2      0.00      0.00      0.00  "
      ]
     },
     "execution_count": 669,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get_daily_fut_val(\"2021-09-03T14:15:00Z\", portfolio_code)"
   ]
  },
  {
   "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 and take a final end of day valuation\n",
    "\n",
    "##### Dec 21 (PV) = (104.00 - 98) * 5 * 100,00/100 = 30,000 EUR\n",
    "Where 98 is yesterday's price"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "dotnet_interactive": {
     "language": "csharp"
    }
   },
   "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>Exposure</th>\n",
       "      <th>InstrumentName</th>\n",
       "      <th>ClientInternal</th>\n",
       "      <th>Contract Size</th>\n",
       "      <th>Price</th>\n",
       "      <th>Units</th>\n",
       "      <th>Cost</th>\n",
       "      <th>PV</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>520,000.00</td>\n",
       "      <td>EURO-BUND FUTURE Dec21</td>\n",
       "      <td>FutBund001</td>\n",
       "      <td>100,000.00</td>\n",
       "      <td>104.00</td>\n",
       "      <td>5.00</td>\n",
       "      <td>15.00</td>\n",
       "      <td>30,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>-7,560.00</td>\n",
       "      <td>Euro</td>\n",
       "      <td>None</td>\n",
       "      <td>1.00</td>\n",
       "      <td>nan</td>\n",
       "      <td>-7,560.00</td>\n",
       "      <td>-7,560.00</td>\n",
       "      <td>-7,560.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.00</td>\n",
       "      <td>EURO-BUND FUTURE Mar22</td>\n",
       "      <td>FutBund002</td>\n",
       "      <td>100,000.00</td>\n",
       "      <td>104.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Exposure          InstrumentName ClientInternal  Contract Size  Price  \\\n",
       "0 520,000.00  EURO-BUND FUTURE Dec21     FutBund001     100,000.00 104.00   \n",
       "1  -7,560.00                    Euro           None           1.00    nan   \n",
       "2       0.00  EURO-BUND FUTURE Mar22     FutBund002     100,000.00 104.00   \n",
       "\n",
       "      Units      Cost        PV  \n",
       "0      5.00     15.00 30,000.00  \n",
       "1 -7,560.00 -7,560.00 -7,560.00  \n",
       "2      0.00      0.00      0.00  "
      ]
     },
     "execution_count": 670,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get_daily_fut_val(\"2021-09-03T17:00:00Z\", portfolio_code)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 6. A2B Report <a class=\"anchor\" id=\"-A2B-report\"></a>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Helper function for displaying A2B Report\n",
    "def format_df(response):\n",
    "    a2b_df = lusid_response_to_data_frame(response, rename_properties=True)\n",
    "    \n",
    "    # Reduce column properties\n",
    "    exclude = [\"instrument_uid\", \"sub_holding_keys\",\"currency\",\"group_id\", \"portfolio_id.scope\",\"portfolio_id.code\", \"holding_type\", \"instrument_scope\", \"end.portfolio_currency.components\"]\n",
    "    \n",
    "    # Drop results in holding currency and results that indicate currency of the activity; we know that everything is in EUR in this example\n",
    "    cols_to_drop = [col for col in a2b_df.columns for exclusion in exclude if (exclusion == col) or (\"holding_currency\" in col) or (\"currency.currency\" in col)]\n",
    "    \n",
    "    result = a2b_df.drop(columns=cols_to_drop)\n",
    "    \n",
    "    columns_to_rename = {\n",
    "        \"Name(default-Properties)\": \"Name\",\n",
    "       \n",
    "        \"start.portfolio_currency.components.Cost\": \"Start/Cost\",\n",
    "        \"start.portfolio_currency.components.MarketGain\": \"Start/Market Gain\",\n",
    "        \"start.portfolio_currency.total\": \"Start/Total\",\n",
    "        \"gains.portfolio_currency.components.DeltaMarketGain\": \"Gains/Delta Market Gain\",\n",
    "        \"gains.portfolio_currency.total\": \"Gains/Total\",\n",
    "        \"flows.portfolio_currency.components.VM Received/Paid\": \"Flow/VM Received/Paid\",\n",
    "        \"flows.portfolio_currency.components.Variation margin\": \"Flow/Variation Margin\",\n",
    "        \"flows.portfolio_currency.total\": \"Flows/Total\",\n",
    "        \"end.portfolio_currency.components.Cost\": \"End/Cost\",\n",
    "        \"end.portfolio_currency.components.MarketGain\": \"End/Market Gain\",\n",
    "        \"end.portfolio_currency.components.VariationMargin\": \"End/Variation Margin\",\n",
    "        \"end.portfolio_currency.total\": \"End/Total\",\n",
    "\n",
    "    }\n",
    "    result=result[columns_to_rename.keys()]\n",
    "    result.rename(columns=columns_to_rename, inplace=True)\n",
    "    return result"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For breaking down the profit and loss occurred in a given period, LUSID offers an A2B report, see [here](https://www.lusid.com/docs/api#operation/GetA2BData) for documentation.\n",
    "\n",
    "The AtoB report will give a breakdown of each holding's P&L, including the flows (transactions) that have occurred in the selected time window as well as totals at the start and end of the window. Within the report you will find the following details:\n",
    "\n",
    " - **Market Value(Start)**: The market value at the start of the window.\n",
    " - **Gains**: Capital Gain, or gain due to asset appreciation based on available market data.\n",
    " - **Flows**: Any transaction activity occurring in the period.\n",
    " \n",
    "Note, the following table has been filtered down to some core fields."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 6.1 A2B breakdown\n",
    "The A2B time window runs between 1st of September 2021 at 08:00 and 2nd of September 2021 at 14:00. That is, from the time of contract open to right before the contract close. The following components are returned:\n",
    "\n",
    "* **Start**:\n",
    "  * EUR : The start position for cash is a cost of EUR -60.00 which is the sum of the contract open fees. There is no market gain as there were no previous positions and the contract were opened in the same currency as the portfolio, so no FX P&L.\n",
    "  * The 2 contracts have the same starting values: a cost of EUR 30.00 each and equivalent offset in market gain.\n",
    "* **Gains and flows**:\n",
    "  * EUR: No gains to be seen as there's no FX P&L\n",
    "  * Dec 21 contract: There is a EUR 5,000 Market Gain and an offset in cash of EUR -5,000; the flow columns show that it's the Variation Margin trade\n",
    "  * Mar 22 contract: There is a EUR 2,500 Market gain with no offsetting flows because there was no Variation Margin trade booked against this contract. The gain reflects the price increase from the start of the contract up to the end of the time window \n",
    "* **End**\n",
    "  * The end columns show a sum of all the activities for each position"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "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>Name</th>\n",
       "      <th>Start/Cost</th>\n",
       "      <th>Start/Market Gain</th>\n",
       "      <th>Start/Total</th>\n",
       "      <th>Gains/Delta Market Gain</th>\n",
       "      <th>Gains/Total</th>\n",
       "      <th>Flow/VM Received/Paid</th>\n",
       "      <th>Flow/Variation Margin</th>\n",
       "      <th>Flows/Total</th>\n",
       "      <th>End/Cost</th>\n",
       "      <th>End/Market Gain</th>\n",
       "      <th>End/Variation Margin</th>\n",
       "      <th>End/Total</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Euro</td>\n",
       "      <td>-60.00</td>\n",
       "      <td>nan</td>\n",
       "      <td>-60.00</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>5,000.00</td>\n",
       "      <td>nan</td>\n",
       "      <td>5,000.00</td>\n",
       "      <td>4,940.00</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>4,940.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>EURO-BUND FUTURE Dec21</td>\n",
       "      <td>30.00</td>\n",
       "      <td>-30.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>5,000.00</td>\n",
       "      <td>5,000.00</td>\n",
       "      <td>nan</td>\n",
       "      <td>-5,000.00</td>\n",
       "      <td>-5,000.00</td>\n",
       "      <td>30.00</td>\n",
       "      <td>4,970.00</td>\n",
       "      <td>-5,000.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>EURO-BUND FUTURE Mar22</td>\n",
       "      <td>30.00</td>\n",
       "      <td>-30.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>2,500.00</td>\n",
       "      <td>2,500.00</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>30.00</td>\n",
       "      <td>2,470.00</td>\n",
       "      <td>nan</td>\n",
       "      <td>2,500.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     Name  Start/Cost  Start/Market Gain  Start/Total  \\\n",
       "0                    Euro      -60.00                nan       -60.00   \n",
       "1  EURO-BUND FUTURE Dec21       30.00             -30.00         0.00   \n",
       "2  EURO-BUND FUTURE Mar22       30.00             -30.00         0.00   \n",
       "\n",
       "   Gains/Delta Market Gain  Gains/Total  Flow/VM Received/Paid  \\\n",
       "0                      nan          nan               5,000.00   \n",
       "1                 5,000.00     5,000.00                    nan   \n",
       "2                 2,500.00     2,500.00                    nan   \n",
       "\n",
       "   Flow/Variation Margin  Flows/Total  End/Cost  End/Market Gain  \\\n",
       "0                    nan     5,000.00  4,940.00              nan   \n",
       "1              -5,000.00    -5,000.00     30.00         4,970.00   \n",
       "2                    nan          nan     30.00         2,470.00   \n",
       "\n",
       "   End/Variation Margin  End/Total  \n",
       "0                   nan   4,940.00  \n",
       "1             -5,000.00       0.00  \n",
       "2                   nan   2,500.00  "
      ]
     },
     "execution_count": 672,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Request A2B report\n",
    "a2b = transaction_portfolio_api.get_a2_b_data(\n",
    "    scope=scope,\n",
    "    code=portfolio_code,\n",
    "    from_effective_at=datetime(2021, 9, 1, 8, tzinfo=pytz.utc),\n",
    "    to_effective_at=datetime(2021, 9, 2, 14, tzinfo=pytz.utc),\n",
    "    recipe_id_scope=scope,\n",
    "    recipe_id_code=recipeCode,\n",
    "    property_keys=[\"Instrument/default/Name\"]\n",
    ")\n",
    "\n",
    "a2b_df = format_df(a2b)\n",
    "a2b_df"
   ]
  }
 ],
 "metadata": {
  "interpreter": {
   "hash": "aee8b7b246df8f9039afb4144a1f6fd8d2ca17a180786b69acc140d282b71a49"
  },
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}