V1/product/edm/TimeVariant Properties.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "    <form action=\"javascript:code_toggle()\"><input type=\"submit\" id=\"toggleButton\" value=\"Toggle Docstring\"></form>\n",
       "    \n",
       "         <script>\n",
       "         function code_toggle() {\n",
       "             if ($('div.cell.code_cell.rendered.selected div.input').css('display')!='none'){\n",
       "                 $('div.cell.code_cell.rendered.selected div.input').hide();\n",
       "             } else {\n",
       "                 $('div.cell.code_cell.rendered.selected div.input').show();\n",
       "             }\n",
       "         }\n",
       "         </script>\n",
       "\n",
       "     "
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "from lusidtools.jupyter_tools import toggle_code\n",
    "\n",
    "\"\"\"Time-variant Properties (e.g. coupon schedule) in LUSID \n",
    "\n",
    "Illustrates the use of time-variant properties, a type of property that depend on different effective dates.\n",
    "\n",
    "Attributes\n",
    "----------\n",
    "coupon schedules\n",
    "multi-valued properties\n",
    "time-variant properties\n",
    "\"\"\"\n",
    "\n",
    "toggle_code(\"Toggle Docstring\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Time-variant Properties\n",
    "\n",
    "This notebook illustrates the use of time-variant properties, which are a type of property that depend on different effective dates. \n",
    "\n",
    "In the example below we use a quarterly ratings schedule as a demonstrative example, showing how the LUSID API can be used to query values on different effective dates. We will also demonstrate the [**bi-temporality**](https://support.finbourne.com/what-is-bi-temporal-data) of the data, using different [**'asAt'**](https://support.finbourne.com/what-is-asat-time) dates.  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "LUSID Environment Initialised\n",
      "LUSID version :  0.6.5656.0\n"
     ]
    }
   ],
   "source": [
    "# Import lusid specific packages\n",
    "# These are the core lusid packages for interacting with the API via Python\n",
    "\n",
    "import lusid\n",
    "import lusid.models as models\n",
    "from lusid.utilities import ApiClientFactory\n",
    "from lusidjam.refreshing_token import RefreshingToken\n",
    "from lusidtools.cocoon.cocoon import load_from_data_frame\n",
    "from lusidtools.cocoon.cocoon_printer import (\n",
    "    format_instruments_response,\n",
    "    format_portfolios_response,\n",
    "    format_transactions_response,\n",
    "    format_quotes_response,\n",
    ")\n",
    "from lusidtools.pandas_utils.lusid_pandas import lusid_response_to_data_frame\n",
    "\n",
    "# Import libraries\n",
    "from datetime import datetime, timedelta\n",
    "import time\n",
    "import pytz\n",
    "import json\n",
    "import os\n",
    "import pandas as pd\n",
    "\n",
    "# Set pandas dataframe display formatting\n",
    "pd.set_option('display.max_columns', None)\n",
    "pd.options.display.float_format = '{:,.2f}'.format\n",
    "\n",
    "# Configure notebook logging and warnings\n",
    "import logging\n",
    "logging.basicConfig(level=logging.INFO)\n",
    "\n",
    "# Authenticate our user and create our API client\n",
    "secrets_path = os.getenv(\"FBN_SECRETS_PATH\")\n",
    "\n",
    "# Initiate an API Factory which is the client side object for interacting with LUSID APIs\n",
    "api_factory = lusid.utilities.ApiClientFactory(\n",
    "    token=RefreshingToken(),\n",
    "    api_secrets_filename = secrets_path,\n",
    "    app_name=\"LusidJupyterNotebook\")\n",
    "\n",
    "# Import required LUSID APIs\n",
    "property_definitions_api = api_factory.build(lusid.api.PropertyDefinitionsApi)\n",
    "instruments_api = api_factory.build(lusid.api.InstrumentsApi)\n",
    "\n",
    "print('LUSID Environment Initialised')\n",
    "print('LUSID version : ', api_factory.build(lusid.api.ApplicationMetadataApi).get_lusid_versions().build_version)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. Load Data\n",
    "\n",
    "### 1.1 Instruments\n",
    "\n",
    "Load the instruments data from the source file and upload them into LUSID. The dataset in the example containins large cap stocks from the FTSE 100 Index, which we can use as examples to later setup our properties to. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>portfolio_code</th>\n",
       "      <th>portfolio_name</th>\n",
       "      <th>portfolio_base_currency</th>\n",
       "      <th>ISIN</th>\n",
       "      <th>sedol</th>\n",
       "      <th>instrument_type</th>\n",
       "      <th>instrument_id</th>\n",
       "      <th>name</th>\n",
       "      <th>txn_id</th>\n",
       "      <th>txn_type</th>\n",
       "      <th>txn_trade_date</th>\n",
       "      <th>txn_settle_date</th>\n",
       "      <th>txn_units</th>\n",
       "      <th>txn_price</th>\n",
       "      <th>txn_consideration</th>\n",
       "      <th>currency</th>\n",
       "      <th>strategy</th>\n",
       "      <th>cash_transactions</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>EQUITY_UK</td>\n",
       "      <td>LUSID's top 10 FTSE stock portfolio</td>\n",
       "      <td>GBP</td>\n",
       "      <td>GB0002162385</td>\n",
       "      <td>0216238</td>\n",
       "      <td>equity</td>\n",
       "      <td>EQ_1234</td>\n",
       "      <td>Aviva</td>\n",
       "      <td>txn-1</td>\n",
       "      <td>StockIn</td>\n",
       "      <td>02/01/2020</td>\n",
       "      <td>04/01/2020</td>\n",
       "      <td>120000</td>\n",
       "      <td>4.23</td>\n",
       "      <td>600000</td>\n",
       "      <td>GBP</td>\n",
       "      <td>ftse_tracker</td>\n",
       "      <td>nan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>EQUITY_UK</td>\n",
       "      <td>LUSID's top 10 FTSE stock portfolio</td>\n",
       "      <td>GBP</td>\n",
       "      <td>GB00BH0P3Z91</td>\n",
       "      <td>BH0P3Z9</td>\n",
       "      <td>equity</td>\n",
       "      <td>EQ_1235</td>\n",
       "      <td>BHP</td>\n",
       "      <td>txn-2</td>\n",
       "      <td>StockIn</td>\n",
       "      <td>02/01/2020</td>\n",
       "      <td>04/01/2020</td>\n",
       "      <td>60000</td>\n",
       "      <td>17.89</td>\n",
       "      <td>1080000</td>\n",
       "      <td>GBP</td>\n",
       "      <td>ftse_tracker</td>\n",
       "      <td>nan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>EQUITY_UK</td>\n",
       "      <td>LUSID's top 10 FTSE stock portfolio</td>\n",
       "      <td>GBP</td>\n",
       "      <td>GB0031348658</td>\n",
       "      <td>3134865</td>\n",
       "      <td>equity</td>\n",
       "      <td>EQ_1236</td>\n",
       "      <td>Barclays</td>\n",
       "      <td>txn-3</td>\n",
       "      <td>StockIn</td>\n",
       "      <td>02/01/2020</td>\n",
       "      <td>04/01/2020</td>\n",
       "      <td>150000</td>\n",
       "      <td>1.80</td>\n",
       "      <td>300000</td>\n",
       "      <td>GBP</td>\n",
       "      <td>ftse_tracker</td>\n",
       "      <td>nan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>EQUITY_UK</td>\n",
       "      <td>LUSID's top 10 FTSE stock portfolio</td>\n",
       "      <td>GBP</td>\n",
       "      <td>GB0007980591</td>\n",
       "      <td>0798059</td>\n",
       "      <td>equity</td>\n",
       "      <td>EQ_1237</td>\n",
       "      <td>BP</td>\n",
       "      <td>txn-4</td>\n",
       "      <td>StockIn</td>\n",
       "      <td>02/01/2020</td>\n",
       "      <td>04/01/2020</td>\n",
       "      <td>100000</td>\n",
       "      <td>4.75</td>\n",
       "      <td>500000</td>\n",
       "      <td>GBP</td>\n",
       "      <td>ftse_tracker</td>\n",
       "      <td>nan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>EQUITY_UK</td>\n",
       "      <td>LUSID's top 10 FTSE stock portfolio</td>\n",
       "      <td>GBP</td>\n",
       "      <td>GB0005405286</td>\n",
       "      <td>0540528</td>\n",
       "      <td>equity</td>\n",
       "      <td>EQ_1238</td>\n",
       "      <td>HSBC</td>\n",
       "      <td>txn-5</td>\n",
       "      <td>StockIn</td>\n",
       "      <td>02/01/2020</td>\n",
       "      <td>04/01/2020</td>\n",
       "      <td>20000</td>\n",
       "      <td>5.89</td>\n",
       "      <td>120000</td>\n",
       "      <td>GBP</td>\n",
       "      <td>ftse_tracker</td>\n",
       "      <td>nan</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  portfolio_code                       portfolio_name portfolio_base_currency  \\\n",
       "0      EQUITY_UK  LUSID's top 10 FTSE stock portfolio                     GBP   \n",
       "1      EQUITY_UK  LUSID's top 10 FTSE stock portfolio                     GBP   \n",
       "2      EQUITY_UK  LUSID's top 10 FTSE stock portfolio                     GBP   \n",
       "3      EQUITY_UK  LUSID's top 10 FTSE stock portfolio                     GBP   \n",
       "4      EQUITY_UK  LUSID's top 10 FTSE stock portfolio                     GBP   \n",
       "\n",
       "           ISIN    sedol instrument_type instrument_id      name txn_id  \\\n",
       "0  GB0002162385  0216238          equity       EQ_1234     Aviva  txn-1   \n",
       "1  GB00BH0P3Z91  BH0P3Z9          equity       EQ_1235       BHP  txn-2   \n",
       "2  GB0031348658  3134865          equity       EQ_1236  Barclays  txn-3   \n",
       "3  GB0007980591  0798059          equity       EQ_1237        BP  txn-4   \n",
       "4  GB0005405286  0540528          equity       EQ_1238      HSBC  txn-5   \n",
       "\n",
       "  txn_type txn_trade_date txn_settle_date  txn_units  txn_price  \\\n",
       "0  StockIn     02/01/2020      04/01/2020     120000       4.23   \n",
       "1  StockIn     02/01/2020      04/01/2020      60000      17.89   \n",
       "2  StockIn     02/01/2020      04/01/2020     150000       1.80   \n",
       "3  StockIn     02/01/2020      04/01/2020     100000       4.75   \n",
       "4  StockIn     02/01/2020      04/01/2020      20000       5.89   \n",
       "\n",
       "   txn_consideration currency      strategy  cash_transactions  \n",
       "0             600000      GBP  ftse_tracker                nan  \n",
       "1            1080000      GBP  ftse_tracker                nan  \n",
       "2             300000      GBP  ftse_tracker                nan  \n",
       "3             500000      GBP  ftse_tracker                nan  \n",
       "4             120000      GBP  ftse_tracker                nan  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Read the instruments data\n",
    "df = pd.read_csv(\"data/equity_transactions_isin.csv\").drop_duplicates()\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>success</th>\n",
       "      <th>failed</th>\n",
       "      <th>errors</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   success  failed  errors\n",
       "0       10       0       0"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create a mapping schema for the instruments in the portfolio\n",
    "instrument_mapping = {\n",
    "    \"identifier_mapping\": {\n",
    "        \"ClientInternal\": \"ISIN\"\n",
    "    },\n",
    "    \"required\": {\n",
    "        \"name\": \"name\"\n",
    "    },\n",
    "}\n",
    "\n",
    "# Instruments can be loaded using a dataframe with file_type set to \"instruments\"\n",
    "result = load_from_data_frame(\n",
    "    api_factory=api_factory,\n",
    "    scope=\"TimeVariant\",\n",
    "    data_frame=df,\n",
    "    mapping_required=instrument_mapping[\"required\"],\n",
    "    mapping_optional={},\n",
    "    file_type=\"instruments\",\n",
    "    identifier_mapping=instrument_mapping[\"identifier_mapping\"],\n",
    ")\n",
    "\n",
    "succ, failed, errors = format_instruments_response(result)\n",
    "pd.DataFrame(data=[{\"success\": len(succ), \"failed\": len(failed), \"errors\": len(errors)}])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1.2 Create the time-variant properties\n",
    "\n",
    "With the instruments in LUSID, we can now define the properties and add them to the selected instruments. To this end we will need to define a [**property definition**](https://support.finbourne.com/what-is-a-property-definition), where we also need to specify the `life time` of the property as `TimeVariant`.  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "INFO:root:Property Instrument/TimeVariantProperty/QuarterlyRating already exists\n"
     ]
    }
   ],
   "source": [
    "# Setup the property details\n",
    "property_scope = \"TimeVariantProperty\"\n",
    "property_code = \"QuarterlyRating\"\n",
    "\n",
    "def create_property(property_scope, property_code):\n",
    "    # Create the property definition request\n",
    "    property_definition = models.CreatePropertyDefinitionRequest(\n",
    "                domain=\"Instrument\",\n",
    "                scope=property_scope,\n",
    "                code=property_code,\n",
    "                display_name=\"Quarterly Ratings Estimates\",\n",
    "                data_type_id=lusid.ResourceId(scope=\"system\", code=\"number\"),\n",
    "                life_time=\"TimeVariant\",\n",
    "            )\n",
    "\n",
    "    # create property definition\n",
    "    try:\n",
    "        property_definition_request = property_definitions_api.create_property_definition(\n",
    "            create_property_definition_request=property_definition\n",
    "        )\n",
    "\n",
    "    except lusid.ApiException as e:\n",
    "        if json.loads(e.body)[\"name\"] == \"PropertyAlreadyExists\":\n",
    "            logging.info(\n",
    "                f\"Property {property_definition.domain}/{property_definition.scope}/{property_definition.code} already exists\"\n",
    "            )\n",
    "    return property_definition\n",
    "\n",
    "# Pass our property scope and code to the property_definition\n",
    "property_definition = create_property(property_scope, property_code)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1.3 Upsert the instrument properties\n",
    "\n",
    "In order to upsert the instrument property, we create a function defining the body of our property request and use the `InstrumentsApi` to pass the values for our instrument.For more details see [**upsert_instruments_properties**](https://www.lusid.com/docs/api/#operation/UpsertInstrumentsProperties).\n",
    "\n",
    "In this case we've used Aviva as an example, to which we are passing dates and numeric values to using the example schedule found below. The dates will be stored in the property's `effective_from` parameter as seen below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Upserted property for ISIN: GB0002162385, with Value: 5 and Eff. Date: 2020-12-31 00:00:00+00:00\n",
      "Upserted property for ISIN: GB0002162385, with Value: 4 and Eff. Date: 2021-03-30 23:00:00+00:00\n",
      "Upserted property for ISIN: GB0002162385, with Value: 3 and Eff. Date: 2021-06-29 23:00:00+00:00\n",
      "Upserted property for ISIN: GB0002162385, with Value: 3 and Eff. Date: 2021-09-29 23:00:00+00:00\n"
     ]
    }
   ],
   "source": [
    "# set the property key using the property_definition -- this will follow the format domain/scope/code\n",
    "property_key = f\"{property_definition.domain}/{property_definition.scope}/{property_definition.code}\"\n",
    "\n",
    "# create a function to upsert the properties for a selected instrument using instruments_api\n",
    "def upsert_instrument_property(ISIN, value, property_key, effectiveFrom):\n",
    "    property_request = [\n",
    "        models.UpsertInstrumentPropertyRequest(\n",
    "            identifier_type=\"ClientInternal\",\n",
    "            identifier=ISIN,\n",
    "            properties=[\n",
    "                models.ModelProperty(\n",
    "                    key=property_key,\n",
    "                    value=models.PropertyValue(\n",
    "                        metric_value=models.MetricValue(\n",
    "                            value=value\n",
    "                        )\n",
    "                    ),\n",
    "                    effective_from=effectiveFrom\n",
    "                )\n",
    "            ]\n",
    "        )\n",
    "    ]\n",
    "\n",
    "    response = instruments_api.upsert_instruments_properties(\n",
    "                        upsert_instrument_property_request=property_request)\n",
    "    print(f'Upserted property for ISIN: {instrument_id}, with Value: {element[key]} and Eff. Date: {date}')\n",
    "    return response\n",
    "\n",
    "# pass in the schedule effective dates and values for a selected ISIN\n",
    "instrument_id = \"GB0002162385\"\n",
    "schedule = [\n",
    "    { \"2020-12-31\" : \"5\"},\n",
    "    { \"2021-03-31\" : \"4\"},\n",
    "    { \"2021-06-30\" : \"3\"},\n",
    "    { \"2021-09-30\" : \"3\"},\n",
    "]\n",
    "\n",
    "\n",
    "for element in schedule:\n",
    "    for key in element:\n",
    "        date = datetime.strptime(key, \"%Y-%m-%d\").astimezone(pytz.utc)\n",
    "        upsert_instrument_property(instrument_id, float(element[key]), property_key, effectiveFrom=date)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. Querying Instrument Properties\n",
    "\n",
    "### 2.1 Get properties by effective dates\n",
    "\n",
    "We can use the [**get_instruments**](https://www.lusid.com/docs/api#operation/GetInstruments) call to the API along with the instrument property key in order to query the properties attached to the instrument for a given effective date. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Effective Date</th>\n",
       "      <th>Value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-12-31 00:00:00</td>\n",
       "      <td>5.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021-03-30 23:00:00</td>\n",
       "      <td>4.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2021-06-29 23:00:00</td>\n",
       "      <td>3.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2021-06-29 23:00:00</td>\n",
       "      <td>3.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       Effective Date  Value\n",
       "0 2020-12-31 00:00:00   5.00\n",
       "1 2021-03-30 23:00:00   4.00\n",
       "2 2021-06-29 23:00:00   3.00\n",
       "3 2021-06-29 23:00:00   3.00"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Create a function to query properties for a given instrument_id and effective date\n",
    "def get_properties(ISIN, property_key, effective_date):\n",
    "    response = instruments_api.get_instruments(\n",
    "        identifier_type=\"ClientInternal\",\n",
    "        request_body=[ISIN],\n",
    "        property_keys=[property_key],\n",
    "        effective_at=datetime.strptime(effective_date, \"%Y-%m-%d\").astimezone(pytz.utc).isoformat(),\n",
    "    )\n",
    "    # return the effective date and property value\n",
    "    return (response.values[ISIN].properties[0].effective_from,\n",
    "            response.values[ISIN].properties[0].value.metric_value.value)\n",
    "\n",
    "dates=[\"2020-12-31\",\n",
    "       \"2021-03-31\",\n",
    "       \"2021-06-30\",\n",
    "       \"2021-09-30\"]\n",
    "\n",
    "data = [get_properties(instrument_id, property_key, date) for date in dates]\n",
    "df = pd.DataFrame(data, columns=['Effective Date', 'Value'])\n",
    "df['Effective Date'] = pd.to_datetime(df[\"Effective Date\"].dt.strftime('%m/%d/%Y %I:%M:%S %p')) \n",
    "display(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.2 Viewing properties by different  _'as at'_ dates\n",
    "\n",
    "The below is an example of the bi-temporality of data stored in LUSID, which means that we can view data as it was on a certain 'as at' date. In this case, we will assume that there was an update to the entries with our previous ratings example, requiring an amendment to the property values. \n",
    "\n",
    "Given the bi-temporal feature, we will still be able to view the original entries as they were before this amendment was made. In this example, we update the expected ratings using a hypothetical downgrade.  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a function to query properties for a given instrument_id and effective date\n",
    "def get_properties_as_at(ISIN, effective_date, as_at_date):\n",
    "    response = instruments_api.get_instruments(\n",
    "        identifier_type=\"ClientInternal\",\n",
    "        request_body=[ISIN],\n",
    "        property_keys=[property_key],\n",
    "        effective_at=datetime.strptime(key, \"%Y-%m-%d\").astimezone(pytz.utc).isoformat(),\n",
    "        as_at = as_at_date\n",
    "    )\n",
    "    # return the effective date and property value\n",
    "    return (response.values[ISIN].properties[0].effective_from,\n",
    "            as_at_date,\n",
    "            response.values[ISIN].properties[0].value.metric_value.value)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Upserted property for ISIN: GB0002162385, with Value: 5 and Eff. Date: 2020-12-31 00:00:00+00:00\n",
      "Upserted property for ISIN: GB0002162385, with Value: 2 and Eff. Date: 2021-03-30 23:00:00+00:00\n",
      "Upserted property for ISIN: GB0002162385, with Value: 1 and Eff. Date: 2021-06-29 23:00:00+00:00\n",
      "Upserted property for ISIN: GB0002162385, with Value: 1 and Eff. Date: 2021-09-29 23:00:00+00:00\n"
     ]
    }
   ],
   "source": [
    "# We begin by storing the current asAt value for '2021-06-30' before the change is made\n",
    "time_now = datetime.now().astimezone(pytz.utc) \n",
    "data_1 = [get_properties_as_at(instrument_id, \"2021-06-30\", time_now)]\n",
    "df_1 = pd.DataFrame(data_1, columns=['Effective Date', 'As at Date','Value'])\n",
    "df_1['Effective Date'] = pd.to_datetime(df_1['Effective Date'].dt.strftime('%m/%d/%Y %I:%M:%S %p'))\n",
    "df_1['As at Date'] = pd.to_datetime(df_1['As at Date'].dt.strftime('%m/%d/%Y %I:%M:%S %p'))\n",
    "\n",
    "\n",
    "# Upsert the new set of data for the same instrument used before\n",
    "new_schedule = [\n",
    "    { \"2020-12-31\" : \"5\"},\n",
    "    { \"2021-03-31\" : \"2\"},\n",
    "    { \"2021-06-30\" : \"1\"},\n",
    "    { \"2021-09-30\" : \"1\"},\n",
    "]\n",
    "\n",
    "for element in new_schedule:\n",
    "    for key in element:\n",
    "        date = datetime.strptime(key, \"%Y-%m-%d\").astimezone(pytz.utc)\n",
    "        upsert_instrument_property(instrument_id, float(element[key]), property_key, effectiveFrom=date)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The [**get_instruments**](https://www.lusid.com/docs/api#operation/GetInstruments) call from the instruments API can be queried with the additional _'as at'_ parameter, which we will add to the get properties function below.  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Set the new as_at time to illustrate the updated value for the same effective_date\n",
    "time_now = datetime.now().astimezone(pytz.utc) \n",
    "\n",
    "# Create a dataframe with the new updated time\n",
    "data_2 = [get_properties_as_at(instrument_id, \"2021-06-30\", time_now)]\n",
    "df_2 = pd.DataFrame(data_2, columns=['Effective Date', 'As at Date','Value'])\n",
    "df_2['Effective Date'] = pd.to_datetime(df_2['Effective Date'].dt.strftime('%m/%d/%Y %I:%M:%S %p'))\n",
    "df_2['As at Date'] = pd.to_datetime(df_2['As at Date'].dt.strftime('%m/%d/%Y %I:%M:%S %p'))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Effective Date</th>\n",
       "      <th>As at Date</th>\n",
       "      <th>Value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2021-06-29 23:00:00</td>\n",
       "      <td>2020-10-28 21:00:03</td>\n",
       "      <td>3.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       Effective Date          As at Date  Value\n",
       "0 2021-06-29 23:00:00 2020-10-28 21:00:03   3.00"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Effective Date</th>\n",
       "      <th>As at Date</th>\n",
       "      <th>Value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2021-06-29 23:00:00</td>\n",
       "      <td>2020-10-28 21:00:04</td>\n",
       "      <td>1.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       Effective Date          As at Date  Value\n",
       "0 2021-06-29 23:00:00 2020-10-28 21:00:04   1.00"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Show value for different as_at times\n",
    "display(df_1)\n",
    "display(df_2)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}