university/T01006-Introduction-to-transaction-configuration/T01006-Ensuring-different-transaction-codes-have-the-same-economic-impact.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "identical-fundamentals",
   "metadata": {},
   "source": [
    "# Ensuring different transaction codes have the same economic impact\n",
    "\n",
    "In this training module we'll see how to use LUSID to perform the following task:\n",
    "\n",
    "**<div align=\"center\">As a data controller, I load transactions from two providers that use a different transaction code to signal the same economic activity. I want to ensure that LUSID applies a uniform economic impact when generating holdings.</div>**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "progressive-origin",
   "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>api_version</th>\n",
       "      <th>build_version</th>\n",
       "      <th>excel_version</th>\n",
       "      <th>links</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>v0</td>\n",
       "      <td>0.6.10020.0</td>\n",
       "      <td>0.5.3017</td>\n",
       "      <td>{'relation': 'RequestLogs', 'href': 'http://ja...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  api_version build_version excel_version  \\\n",
       "0          v0   0.6.10020.0      0.5.3017   \n",
       "\n",
       "                                               links  \n",
       "0  {'relation': 'RequestLogs', 'href': 'http://ja...  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Set up LUSID\n",
    "import os\n",
    "import pandas as pd\n",
    "import json\n",
    "import uuid\n",
    "import matplotlib.pyplot as plt\n",
    "from IPython.core.display import HTML\n",
    "import logging\n",
    "logging.basicConfig(level=logging.INFO)\n",
    "\n",
    "import lusid as lu\n",
    "import lusid.api as la\n",
    "import lusid.models as lm\n",
    "\n",
    "from lusid.utilities import ApiClientFactory\n",
    "from lusidjam import RefreshingToken\n",
    "from lusidtools.pandas_utils.lusid_pandas import lusid_response_to_data_frame\n",
    "from lusidtools.jupyter_tools import StopExecution\n",
    "from lusidtools.lpt.lpt import to_date\n",
    "\n",
    "# Set pandas display options\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",
    "\n",
    "# Authenticate to SDK\n",
    "# Run the Notebook in Jupyterhub for your LUSID domain and authenticate automatically\n",
    "secrets_path = os.getenv(\"FBN_SECRETS_PATH\")\n",
    "# Run the Notebook locally using a secrets file (see https://support.lusid.com/knowledgebase/article/KA-01663)\n",
    "if secrets_path is None:\n",
    "    secrets_path = os.path.join(os.path.dirname(os.getcwd()), \"secrets.json\")\n",
    "\n",
    "api_factory = ApiClientFactory(\n",
    "    token = RefreshingToken(), \n",
    "    api_secrets_filename = secrets_path,\n",
    "    app_name = \"LusidJupyterNotebook\"\n",
    ")\n",
    "\n",
    "# Confirm success by printing SDK version\n",
    "api_status = pd.DataFrame(api_factory.build(lu.ApplicationMetadataApi).get_lusid_versions().to_dict())\n",
    "display(api_status)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "major-provision",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "'FBNUniversity/Module-6-1' scope and code created.\n"
     ]
    }
   ],
   "source": [
    "# Create a scope and code to segregate data in this module from other modules\n",
    "module_scope = \"FBNUniversity\"\n",
    "module_code = \"Module-6-1\"\n",
    "print(f\"'{module_scope}/{module_code}' scope and code created.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "responsible-booking",
   "metadata": {},
   "source": [
    "## 1. Examining the source file\n",
    "\n",
    "Imagine we load transactions into LUSID from both Societe Generale and Deutsche Bank:\n",
    "\n",
    "* SG uses a transaction code of `Acheter` to signal an equity purchase.\n",
    "* DB uses a transaction code of `Kaufen` to signal an equity purchase.\n",
    "\n",
    "We want to retain these codes in the imported data but ensure LUSID applies the same economic impact to every transaction when generating holdings."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "independent-first",
   "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>source</th>\n",
       "      <th>instrument</th>\n",
       "      <th>asset</th>\n",
       "      <th>figi</th>\n",
       "      <th>txn_id</th>\n",
       "      <th>txn_code</th>\n",
       "      <th>trade_date</th>\n",
       "      <th>settle_date</th>\n",
       "      <th>units</th>\n",
       "      <th>price</th>\n",
       "      <th>currency</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Lusid</td>\n",
       "      <td>GBP</td>\n",
       "      <td>Cash</td>\n",
       "      <td></td>\n",
       "      <td>61001</td>\n",
       "      <td>FundsIn</td>\n",
       "      <td>2022-06-06T09:00:00Z</td>\n",
       "      <td>2022-06-08T09:00:00Z</td>\n",
       "      <td>500</td>\n",
       "      <td>1</td>\n",
       "      <td>GBP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>SocieteGenerale</td>\n",
       "      <td>BP</td>\n",
       "      <td>Equity</td>\n",
       "      <td>BBG000C05BD1</td>\n",
       "      <td>61002</td>\n",
       "      <td>Acheter</td>\n",
       "      <td>2022-06-06T10:00:00Z</td>\n",
       "      <td>2022-06-08T09:00:00Z</td>\n",
       "      <td>50</td>\n",
       "      <td>5</td>\n",
       "      <td>GBP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>DeutscheBank</td>\n",
       "      <td>BP</td>\n",
       "      <td>Equity</td>\n",
       "      <td>BBG000C05BD1</td>\n",
       "      <td>61003</td>\n",
       "      <td>Kaufen</td>\n",
       "      <td>2022-06-06T11:00:00Z</td>\n",
       "      <td>2022-06-08T09:00:00Z</td>\n",
       "      <td>25</td>\n",
       "      <td>5</td>\n",
       "      <td>GBP</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            source instrument   asset          figi  txn_id txn_code  \\\n",
       "1            Lusid        GBP    Cash                 61001  FundsIn   \n",
       "2  SocieteGenerale         BP  Equity  BBG000C05BD1   61002  Acheter   \n",
       "3     DeutscheBank         BP  Equity  BBG000C05BD1   61003   Kaufen   \n",
       "\n",
       "             trade_date           settle_date  units  price currency  \n",
       "1  2022-06-06T09:00:00Z  2022-06-08T09:00:00Z    500      1      GBP  \n",
       "2  2022-06-06T10:00:00Z  2022-06-08T09:00:00Z     50      5      GBP  \n",
       "3  2022-06-06T11:00:00Z  2022-06-08T09:00:00Z     25      5      GBP  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Read transactions from amalgamated stream into pandas dataframe\n",
    "sg_db_df = pd.read_csv(\"data/different-codes-sg-db.csv\", keep_default_na = False)\n",
    "sg_db_df.index += 1\n",
    "display(sg_db_df)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "alone-fountain",
   "metadata": {},
   "source": [
    "## 2. Ensuring instruments are mastered correctly\n",
    "\n",
    "It's possible the equity instrument in our transaction source file is already mastered in LUSID as part of the demonstration data, but for the avoidance of doubt we'll master it separately in a segregated custom instrument scope.\n",
    "\n",
    "Note the `GBP` currency instrument, as per all currencies, is mastered out-of-the-box in the `default` instrument scope."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "sustained-director",
   "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>lusid_instrument_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>BP</td>\n",
       "      <td>LUID_00003DA0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  name lusid_instrument_id\n",
       "0   BP       LUID_00003DA0"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Obtain the LUSID Instruments API\n",
    "instruments_api = api_factory.build(la.InstrumentsApi)\n",
    "\n",
    "# Create a dictionary of instrument definitions for each equity in the transaction source file (ie. BP)\n",
    "definitions = {\n",
    "    security[\"instrument\"]: lm.InstrumentDefinition(\n",
    "            name = security[\"instrument\"],\n",
    "            identifiers = {\n",
    "                \"Figi\": lm.InstrumentIdValue(value = security[\"figi\"]),\n",
    "            },\n",
    "            definition = lm.Equity(\n",
    "                instrument_type = \"Equity\",\n",
    "                dom_ccy = security[\"currency\"],\n",
    "                identifiers = {}\n",
    "            )\n",
    "        )\n",
    "    for index, security in sg_db_df.iterrows() if security[\"asset\"] == \"Equity\"\n",
    "}\n",
    "\n",
    "# Upsert instruments to LUSID\n",
    "upsert_instruments_response = instruments_api.upsert_instruments(\n",
    "    request_body = definitions,\n",
    "    # Master the instruments in a custom scope\n",
    "    scope = f\"{module_scope}{module_code}\",\n",
    ")\n",
    "\n",
    "# Transform API response to a dataframe and show internally-generated unique LUID for each mastered instrument\n",
    "upsert_instruments_response_df = lusid_response_to_data_frame(list(upsert_instruments_response.values.values()))\n",
    "display(upsert_instruments_response_df[[\"name\", \"lusid_instrument_id\"]])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "stopped-texture",
   "metadata": {},
   "source": [
    "## 3. Creating a suitable portfolio \n",
    "We register the custom instrument scope using the `instrument_scopes` field to ensure transactions upserted to the portfolio resolve first against instruments mastered in the custom scope, before falling back to the `default` scope."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "swiss-stream",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "INFO:root:Could not create a portfolio with id 'Module-6-1' because it already exists in scope 'FBNUniversity'.\n"
     ]
    }
   ],
   "source": [
    "# Obtain the LUSID Transaction Portfolio API\n",
    "transaction_portfolios_api = api_factory.build(la.TransactionPortfoliosApi)\n",
    "\n",
    "# Create portfolio definition\n",
    "portfolio_definition=lm.CreateTransactionPortfolioRequest(\n",
    "    display_name=\"Training module 6.1 - Different transaction codes, same impact\",\n",
    "    code = module_code,\n",
    "    base_currency = \"GBP\",\n",
    "    # Must be before first transaction recorded\n",
    "    created=\"2022-01-01\",\n",
    "    # Attempt to resolve transactions to instruments in the custom scope before falling back to the default scope\n",
    "    instrument_scopes = [f\"{module_scope}{module_code}\"],\n",
    ")\n",
    "\n",
    "# Upsert portfolio to LUSID, making sure it's not already there\n",
    "try:\n",
    "    create_portfolio_response=transaction_portfolios_api.create_portfolio(\n",
    "        scope = module_scope,\n",
    "        create_transaction_portfolio_request = portfolio_definition\n",
    "    )\n",
    "    # Confirm success\n",
    "    print(f\"Portfolio with display name '{create_portfolio_response.display_name}' created effective {str(create_portfolio_response.created)}\")\n",
    "except lu.ApiException as e:\n",
    "    if json.loads(e.body)[\"name\"] == \"PortfolioWithIdAlreadyExists\":\n",
    "            logging.info(json.loads(e.body)[\"title\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "curious-crest",
   "metadata": {},
   "source": [
    "## 4. Configuring the built-in Buy transaction type\n",
    "\n",
    "The built-in `Buy` transaction type is designed to increase an instrument holding by the number of units purchased, and decrease a cash holding by the total consideration.\n",
    "\n",
    "We want `Acheter` and `Kaufen` to have the same economic impact.\n",
    "\n",
    "### 4.1 Retrieving the definition of Buy to obtain the correct settings\n",
    "\n",
    "We can call the `GetTransactionType` API to retrieve the definition of `Buy` before appending an alias for each of `Acheter` and `Kaufen`, to ensure we retain all the other settings correctly."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "floating-southwest",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'aliases': [{'description': 'Purchase',\n",
       "              'is_default': False,\n",
       "              'transaction_class': 'Basic',\n",
       "              'transaction_roles': 'LongLonger',\n",
       "              'type': 'Buy'},\n",
       "             {'description': 'Purchase from Societe Generale',\n",
       "              'is_default': False,\n",
       "              'transaction_class': 'Basic',\n",
       "              'transaction_roles': 'LongLonger',\n",
       "              'type': 'Acheter'},\n",
       "             {'description': 'Purchase from Deutsche Bank',\n",
       "              'is_default': False,\n",
       "              'transaction_class': 'Basic',\n",
       "              'transaction_roles': 'LongLonger',\n",
       "              'type': 'Kaufen'}],\n",
       " 'links': [{'description': 'A link to the LUSID Insights website showing all '\n",
       "                           'logs related to this request',\n",
       "            'href': 'http://jamleed.lusid.com/app/insights/logs/0HMKT2ARKRQJ8:00000047',\n",
       "            'method': 'GET',\n",
       "            'relation': 'RequestLogs'}],\n",
       " 'movements': [{'direction': 1,\n",
       "                'mappings': [],\n",
       "                'movement_types': 'StockMovement',\n",
       "                'name': None,\n",
       "                'properties': {},\n",
       "                'side': 'Side1'},\n",
       "               {'direction': -1,\n",
       "                'mappings': [],\n",
       "                'movement_types': 'CashCommitment',\n",
       "                'name': None,\n",
       "                'properties': {},\n",
       "                'side': 'Side2'}],\n",
       " 'properties': {}}"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Obtain the LUSID Transaction Type Configuration API\n",
    "transaction_config_api = api_factory.build(la.TransactionConfigurationApi)\n",
    "\n",
    "# Get existing transaction type definition    \n",
    "try:\n",
    "    response = transaction_config_api.get_transaction_type(\n",
    "        # Specify the source of the transaction type\n",
    "        source = \"default\",\n",
    "        # Specify the primary alias name (in this case, the name of the only alias in the transaction type)\n",
    "        type = \"Buy\",\n",
    "    )\n",
    "    display(response)\n",
    "except lu.ApiException as e:\n",
    "    if json.loads(e.body)[\"name\"] == \"TransactionTypeNotFound\":\n",
    "        logging.info(\"Transaction type not found\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cross-hospital",
   "metadata": {},
   "source": [
    "### 4.2 Modifying the definition of Buy to append new aliases\n",
    "\n",
    "We can now create a new definition for `Buy` that appends aliases for `Acheter` and `Kaufen` but retains all the other settings."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "southwest-grave",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'aliases': [{'description': 'Purchase',\n",
       "              'is_default': False,\n",
       "              'transaction_class': 'Basic',\n",
       "              'transaction_roles': 'LongLonger',\n",
       "              'type': 'Buy'},\n",
       "             {'description': 'Purchase from Societe Generale',\n",
       "              'is_default': False,\n",
       "              'transaction_class': 'Basic',\n",
       "              'transaction_roles': 'LongLonger',\n",
       "              'type': 'Acheter'},\n",
       "             {'description': 'Purchase from Deutsche Bank',\n",
       "              'is_default': False,\n",
       "              'transaction_class': 'Basic',\n",
       "              'transaction_roles': 'LongLonger',\n",
       "              'type': 'Kaufen'}],\n",
       " 'links': [{'description': 'A link to the LUSID Insights website showing all '\n",
       "                           'logs related to this request',\n",
       "            'href': 'http://jamleed.lusid.com/app/insights/logs/0HMKT2ASMJ0RA:000000B8',\n",
       "            'method': 'GET',\n",
       "            'relation': 'RequestLogs'}],\n",
       " 'movements': [{'direction': 1,\n",
       "                'mappings': [],\n",
       "                'movement_types': 'StockMovement',\n",
       "                'name': None,\n",
       "                'properties': {},\n",
       "                'side': 'Side1'},\n",
       "               {'direction': -1,\n",
       "                'mappings': [],\n",
       "                'movement_types': 'CashCommitment',\n",
       "                'name': None,\n",
       "                'properties': {},\n",
       "                'side': 'Side2'}],\n",
       " 'properties': {}}"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "transaction_type_definition = lm.TransactionTypeRequest(\n",
    "    aliases = [\n",
    "        # Recreate the built-in Buy alias\n",
    "        lm.TransactionTypeAlias(\n",
    "            type = \"Buy\",\n",
    "            description = \"Purchase\",\n",
    "            transaction_class = \"Basic\",\n",
    "            transaction_roles = \"LongLonger\"\n",
    "        ),\n",
    "        # Append a new alias for Acheter\n",
    "        lm.TransactionTypeAlias(\n",
    "            type = \"Acheter\",\n",
    "            description = \"Purchase from Societe Generale\",\n",
    "            transaction_class = \"Basic\",\n",
    "            transaction_roles = \"LongLonger\"\n",
    "        ),\n",
    "        # Append a new alias for Kaufen\n",
    "        lm.TransactionTypeAlias(\n",
    "            type = \"Kaufen\",\n",
    "            description = \"Purchase from Deutsche Bank\",\n",
    "            transaction_class = \"Basic\",\n",
    "            transaction_roles = \"LongLonger\"\n",
    "        )\n",
    "    ],\n",
    "    # Recreate the built-in Buy movements\n",
    "    movements = [\n",
    "        lm.TransactionTypeMovement(\n",
    "            movement_types = \"StockMovement\",\n",
    "            side = \"Side1\",\n",
    "            direction = 1,\n",
    "        ),\n",
    "        lm.TransactionTypeMovement(\n",
    "            movement_types = \"CashCommitment\",\n",
    "            side = \"Side2\",\n",
    "            direction = -1,\n",
    "        ),\n",
    "    ]\n",
    ")\n",
    "\n",
    "# Replace transaction type in LUSID    \n",
    "try:\n",
    "    response = transaction_config_api.set_transaction_type(\n",
    "        # Specify the source (data provider) of the transaction type (Buy is in the 'default' source)\n",
    "        source = \"default\",\n",
    "        # Specify the primary alias name\n",
    "        type = \"Buy\",\n",
    "        transaction_type_request = transaction_type_definition\n",
    "    )\n",
    "    # Confirm by retrieving the newly-modified transaction type\n",
    "    display(transaction_config_api.get_transaction_type(source = \"default\", type = \"Buy\"))\n",
    "except lu.ApiException as e:\n",
    "    print(e)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "infrared-settlement",
   "metadata": {},
   "source": [
    "## 5. Loading transactions into the portfolio\n",
    "\n",
    "We can now upsert transactions into LUSID, mapping the **txn_code** column in the source file to the `type` field, safe in the knowledge that the `Acheter` and `Kaufen` transaction types will have the same economic impact as `Buy`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "restricted-interview",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Transactions loaded at 2022-07-15 07:58:22.143422+00:00'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "{'href': 'https://jamleed.lusid.com/api/api/transactionportfolios/FBNUniversity/Module-6-1/transactions?asAt=2022-07-15T07%3A58%3A22.1434220%2B00%3A00',\n",
       " 'links': [{'description': None,\n",
       "            'href': 'https://jamleed.lusid.com/api/api/portfolios/FBNUniversity/Module-6-1?effectiveAt=2022-01-01T00%3A00%3A00.0000000%2B00%3A00&asAt=2022-07-15T07%3A58%3A22.1434220%2B00%3A00',\n",
       "            'method': 'GET',\n",
       "            'relation': 'Root'},\n",
       "           {'description': None,\n",
       "            'href': 'https://jamleed.lusid.com/api/api/schemas/entities/UpsertPortfolioTransactionsResponse',\n",
       "            'method': 'GET',\n",
       "            'relation': 'EntitySchema'},\n",
       "           {'description': 'A link to the LUSID Insights website showing all '\n",
       "                           'logs related to this request',\n",
       "            'href': 'http://jamleed.lusid.com/app/insights/logs/0HMKT2ASMJ0RA:000000B9',\n",
       "            'method': 'GET',\n",
       "            'relation': 'RequestLogs'}],\n",
       " 'version': {'as_at_date': datetime.datetime(2022, 7, 15, 7, 58, 22, 143422, tzinfo=tzutc()),\n",
       "             'effective_from': datetime.datetime(2022, 1, 1, 0, 0, tzinfo=tzutc())}}"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Create list of transactions to upsert\n",
    "transactions = []\n",
    "\n",
    "# For each row in dataframe\n",
    "for index, txn in sg_db_df.iterrows():\n",
    "\n",
    "    # Set variables based on whether instrument is cash or not\n",
    "    if txn[\"asset\"] == \"Cash\":\n",
    "        # Set the unique instrument identifier to be a currency\n",
    "        identifiers = {\"Instrument/default/Currency\": txn[\"currency\"]}\n",
    "    else:\n",
    "        # Set the unique instrument identifier to be a FIGI\n",
    "        identifiers = {\"Instrument/default/Figi\": txn[\"figi\"]}\n",
    "\n",
    "    transactions.append(\n",
    "        lm.TransactionRequest(\n",
    "            transaction_id = str(txn[\"txn_id\"]),\n",
    "            # Set the transaction type to the transaction code from the data provider\n",
    "            type = txn[\"txn_code\"],\n",
    "            instrument_identifiers = identifiers,\n",
    "            transaction_date = txn[\"trade_date\"],\n",
    "            settlement_date = txn[\"settle_date\"],\n",
    "            units = txn[\"units\"],\n",
    "            transaction_price = lm.TransactionPrice(price = txn[\"price\"], type=\"Price\"),\n",
    "            total_consideration = lm.CurrencyAndAmount(\n",
    "                # Calculate cost on-the-fly\n",
    "                amount = txn[\"units\"] * txn[\"price\"],\n",
    "                currency = txn[\"currency\"]\n",
    "            ),\n",
    "        )\n",
    "    )\n",
    "\n",
    "# Upsert transactions to LUSID\n",
    "upsert_transactions_response = transaction_portfolios_api.upsert_transactions(\n",
    "    scope = module_scope, \n",
    "    code = module_code, \n",
    "    transaction_request = transactions\n",
    ")\n",
    "\n",
    "display(f\"Transactions loaded at {str(upsert_transactions_response.version.as_at_date)}\")\n",
    "display(upsert_transactions_response)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "south-parameter",
   "metadata": {},
   "source": [
    "## 6. Generating holdings\n",
    "\n",
    "### 6.1 On the trade date\n",
    "We can now call the `GetHoldings` API for end-of-day on the trade date (6 June 2022) to see the interim impact of our transaction types on holdings."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "hollywood-literature",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>instrument_scope</th>\n",
       "      <th>instrument_uid</th>\n",
       "      <th>Name(default-Properties)</th>\n",
       "      <th>holding_type</th>\n",
       "      <th>units</th>\n",
       "      <th>settled_units</th>\n",
       "      <th>cost.amount</th>\n",
       "      <th>cost.currency</th>\n",
       "      <th>cost_portfolio_ccy.amount</th>\n",
       "      <th>transaction.transaction_id</th>\n",
       "      <th>transaction.type</th>\n",
       "      <th>transaction.instrument_identifiers.Instrument/default/Currency</th>\n",
       "      <th>transaction.instrument_scope</th>\n",
       "      <th>transaction.instrument_uid</th>\n",
       "      <th>transaction.transaction_date</th>\n",
       "      <th>transaction.settlement_date</th>\n",
       "      <th>transaction.units</th>\n",
       "      <th>transaction.transaction_price.price</th>\n",
       "      <th>transaction.transaction_price.type</th>\n",
       "      <th>transaction.total_consideration.amount</th>\n",
       "      <th>transaction.total_consideration.currency</th>\n",
       "      <th>transaction.exchange_rate</th>\n",
       "      <th>transaction.transaction_currency</th>\n",
       "      <th>transaction.properties</th>\n",
       "      <th>transaction.entry_date_time</th>\n",
       "      <th>transaction.transaction_status</th>\n",
       "      <th>transaction.instrument_identifiers.Instrument/default/Figi</th>\n",
       "      <th>transaction.properties.Transaction/default/ResultantHolding.key</th>\n",
       "      <th>transaction.properties.Transaction/default/ResultantHolding.value.metric_value.value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>FBNUniversityModule-6-1</td>\n",
       "      <td>LUID_00003DA0</td>\n",
       "      <td>BP</td>\n",
       "      <td>P</td>\n",
       "      <td>75.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>375.00</td>\n",
       "      <td>GBP</td>\n",
       "      <td>375.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>default</td>\n",
       "      <td>CCY_GBP</td>\n",
       "      <td>GBP</td>\n",
       "      <td>A</td>\n",
       "      <td>500.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>500.00</td>\n",
       "      <td>GBP</td>\n",
       "      <td>500.00</td>\n",
       "      <td>61001</td>\n",
       "      <td>FundsIn</td>\n",
       "      <td>GBP</td>\n",
       "      <td>default</td>\n",
       "      <td>CCY_GBP</td>\n",
       "      <td>2022-06-06 09:00:00+00:00</td>\n",
       "      <td>2022-06-08 09:00:00+00:00</td>\n",
       "      <td>500.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>Price</td>\n",
       "      <td>500.00</td>\n",
       "      <td>GBP</td>\n",
       "      <td>1.00</td>\n",
       "      <td>GBP</td>\n",
       "      <td>{}</td>\n",
       "      <td>2022-07-15 07:58:25.479849+00:00</td>\n",
       "      <td>Active</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>default</td>\n",
       "      <td>CCY_GBP</td>\n",
       "      <td>GBP</td>\n",
       "      <td>C</td>\n",
       "      <td>-250.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>-250.00</td>\n",
       "      <td>GBP</td>\n",
       "      <td>-250.00</td>\n",
       "      <td>61002</td>\n",
       "      <td>Acheter</td>\n",
       "      <td>NaN</td>\n",
       "      <td>FBNUniversityModule-6-1</td>\n",
       "      <td>LUID_00003DA0</td>\n",
       "      <td>2022-06-06 10:00:00+00:00</td>\n",
       "      <td>2022-06-08 09:00:00+00:00</td>\n",
       "      <td>50.00</td>\n",
       "      <td>5.00</td>\n",
       "      <td>Price</td>\n",
       "      <td>250.00</td>\n",
       "      <td>GBP</td>\n",
       "      <td>1.00</td>\n",
       "      <td>GBP</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-07-15 07:58:25.479849+00:00</td>\n",
       "      <td>Active</td>\n",
       "      <td>BBG000C05BD1</td>\n",
       "      <td>Transaction/default/ResultantHolding</td>\n",
       "      <td>50.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>default</td>\n",
       "      <td>CCY_GBP</td>\n",
       "      <td>GBP</td>\n",
       "      <td>C</td>\n",
       "      <td>-125.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>-125.00</td>\n",
       "      <td>GBP</td>\n",
       "      <td>-125.00</td>\n",
       "      <td>61003</td>\n",
       "      <td>Kaufen</td>\n",
       "      <td>NaN</td>\n",
       "      <td>FBNUniversityModule-6-1</td>\n",
       "      <td>LUID_00003DA0</td>\n",
       "      <td>2022-06-06 11:00:00+00:00</td>\n",
       "      <td>2022-06-08 09:00:00+00:00</td>\n",
       "      <td>25.00</td>\n",
       "      <td>5.00</td>\n",
       "      <td>Price</td>\n",
       "      <td>125.00</td>\n",
       "      <td>GBP</td>\n",
       "      <td>1.00</td>\n",
       "      <td>GBP</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-07-15 07:58:25.479849+00:00</td>\n",
       "      <td>Active</td>\n",
       "      <td>BBG000C05BD1</td>\n",
       "      <td>Transaction/default/ResultantHolding</td>\n",
       "      <td>75.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          instrument_scope instrument_uid Name(default-Properties)  \\\n",
       "0  FBNUniversityModule-6-1  LUID_00003DA0                       BP   \n",
       "1                  default        CCY_GBP                      GBP   \n",
       "2                  default        CCY_GBP                      GBP   \n",
       "3                  default        CCY_GBP                      GBP   \n",
       "\n",
       "  holding_type   units  settled_units  cost.amount cost.currency  \\\n",
       "0            P   75.00           0.00       375.00           GBP   \n",
       "1            A  500.00           0.00       500.00           GBP   \n",
       "2            C -250.00           0.00      -250.00           GBP   \n",
       "3            C -125.00           0.00      -125.00           GBP   \n",
       "\n",
       "   cost_portfolio_ccy.amount transaction.transaction_id transaction.type  \\\n",
       "0                     375.00                        NaN              NaN   \n",
       "1                     500.00                      61001          FundsIn   \n",
       "2                    -250.00                      61002          Acheter   \n",
       "3                    -125.00                      61003           Kaufen   \n",
       "\n",
       "  transaction.instrument_identifiers.Instrument/default/Currency  \\\n",
       "0                                                NaN               \n",
       "1                                                GBP               \n",
       "2                                                NaN               \n",
       "3                                                NaN               \n",
       "\n",
       "  transaction.instrument_scope transaction.instrument_uid  \\\n",
       "0                          NaN                        NaN   \n",
       "1                      default                    CCY_GBP   \n",
       "2      FBNUniversityModule-6-1              LUID_00003DA0   \n",
       "3      FBNUniversityModule-6-1              LUID_00003DA0   \n",
       "\n",
       "  transaction.transaction_date transaction.settlement_date  transaction.units  \\\n",
       "0                          NaT                         NaT                NaN   \n",
       "1    2022-06-06 09:00:00+00:00   2022-06-08 09:00:00+00:00             500.00   \n",
       "2    2022-06-06 10:00:00+00:00   2022-06-08 09:00:00+00:00              50.00   \n",
       "3    2022-06-06 11:00:00+00:00   2022-06-08 09:00:00+00:00              25.00   \n",
       "\n",
       "   transaction.transaction_price.price transaction.transaction_price.type  \\\n",
       "0                                  NaN                                NaN   \n",
       "1                                 1.00                              Price   \n",
       "2                                 5.00                              Price   \n",
       "3                                 5.00                              Price   \n",
       "\n",
       "   transaction.total_consideration.amount  \\\n",
       "0                                     NaN   \n",
       "1                                  500.00   \n",
       "2                                  250.00   \n",
       "3                                  125.00   \n",
       "\n",
       "  transaction.total_consideration.currency  transaction.exchange_rate  \\\n",
       "0                                      NaN                        NaN   \n",
       "1                                      GBP                       1.00   \n",
       "2                                      GBP                       1.00   \n",
       "3                                      GBP                       1.00   \n",
       "\n",
       "  transaction.transaction_currency transaction.properties  \\\n",
       "0                              NaN                    NaN   \n",
       "1                              GBP                     {}   \n",
       "2                              GBP                    NaN   \n",
       "3                              GBP                    NaN   \n",
       "\n",
       "       transaction.entry_date_time transaction.transaction_status  \\\n",
       "0                              NaT                            NaN   \n",
       "1 2022-07-15 07:58:25.479849+00:00                         Active   \n",
       "2 2022-07-15 07:58:25.479849+00:00                         Active   \n",
       "3 2022-07-15 07:58:25.479849+00:00                         Active   \n",
       "\n",
       "  transaction.instrument_identifiers.Instrument/default/Figi  \\\n",
       "0                                                NaN           \n",
       "1                                                NaN           \n",
       "2                                       BBG000C05BD1           \n",
       "3                                       BBG000C05BD1           \n",
       "\n",
       "  transaction.properties.Transaction/default/ResultantHolding.key  \\\n",
       "0                                                NaN                \n",
       "1                                                NaN                \n",
       "2               Transaction/default/ResultantHolding                \n",
       "3               Transaction/default/ResultantHolding                \n",
       "\n",
       "   transaction.properties.Transaction/default/ResultantHolding.value.metric_value.value  \n",
       "0                                                NaN                                     \n",
       "1                                                NaN                                     \n",
       "2                                              50.00                                     \n",
       "3                                              75.00                                     "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Get holdings for portfolio effective EOD 6 June 2022\n",
    "get_holdings_response=transaction_portfolios_api.get_holdings(\n",
    "    scope = module_scope, \n",
    "    code = module_code,\n",
    "    # Specify an explicit date\n",
    "    effective_at = \"2022-06-06T17:00:00Z\",\n",
    "    # Decorate the instrument name property onto holdings to make the API response more intuitive\n",
    "    property_keys=[\"Instrument/default/Name\"],\n",
    ")\n",
    "\n",
    "# Transform API response to a pandas dataframe and show it\n",
    "get_holdings_response_df=lusid_response_to_data_frame(get_holdings_response, rename_properties=True)\n",
    "# Drop some noisy columns\n",
    "get_holdings_response_df.drop(columns=[\n",
    "    \"sub_holding_keys\", \"cost_portfolio_ccy.currency\", \"currency\", \"SourcePortfolioId(default-Properties)\", \"SourcePortfolioScope(default-Properties)\" ], inplace=True)\n",
    "display(get_holdings_response_df)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "designed-evidence",
   "metadata": {},
   "source": [
    "### 6.2 Post-settlement date\n",
    "\n",
    "If we call `GetHoldings` API without an explicit date, LUSID uses today's date so we can see the impact on holdings after the settlement date."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "breathing-concrete",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>instrument_scope</th>\n",
       "      <th>instrument_uid</th>\n",
       "      <th>Name(default-Properties)</th>\n",
       "      <th>holding_type</th>\n",
       "      <th>units</th>\n",
       "      <th>settled_units</th>\n",
       "      <th>cost.amount</th>\n",
       "      <th>cost.currency</th>\n",
       "      <th>cost_portfolio_ccy.amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>FBNUniversityModule-6-1</td>\n",
       "      <td>LUID_00003DA0</td>\n",
       "      <td>BP</td>\n",
       "      <td>P</td>\n",
       "      <td>75.00</td>\n",
       "      <td>75.00</td>\n",
       "      <td>375.00</td>\n",
       "      <td>GBP</td>\n",
       "      <td>375.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>default</td>\n",
       "      <td>CCY_GBP</td>\n",
       "      <td>GBP</td>\n",
       "      <td>B</td>\n",
       "      <td>125.00</td>\n",
       "      <td>125.00</td>\n",
       "      <td>125.00</td>\n",
       "      <td>GBP</td>\n",
       "      <td>125.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          instrument_scope instrument_uid Name(default-Properties)  \\\n",
       "0  FBNUniversityModule-6-1  LUID_00003DA0                       BP   \n",
       "1                  default        CCY_GBP                      GBP   \n",
       "\n",
       "  holding_type  units  settled_units  cost.amount cost.currency  \\\n",
       "0            P  75.00          75.00       375.00           GBP   \n",
       "1            B 125.00         125.00       125.00           GBP   \n",
       "\n",
       "   cost_portfolio_ccy.amount  \n",
       "0                     375.00  \n",
       "1                     125.00  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Get holdings for portfolio effective today\n",
    "get_holdings_response=transaction_portfolios_api.get_holdings(\n",
    "    scope = module_scope, \n",
    "    code = module_code,\n",
    "    # Decorate the instrument name property onto holdings to make the API response more intuitive\n",
    "    property_keys=[\"Instrument/default/Name\"],\n",
    ")\n",
    "\n",
    "# Transform API response to a pandas dataframe and show it\n",
    "get_holdings_response_df=lusid_response_to_data_frame(get_holdings_response, rename_properties=True)\n",
    "# Drop some noisy columns\n",
    "get_holdings_response_df.drop(columns=[\n",
    "    \"sub_holding_keys\", \"cost_portfolio_ccy.currency\", \"currency\", \"SourcePortfolioId(default-Properties)\", \"SourcePortfolioScope(default-Properties)\"], inplace=True)\n",
    "display(get_holdings_response_df)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "venv",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.6 (main, Aug  3 2022, 12:12:57) [Clang 13.1.6 (clang-1316.0.21.2.5)]"
  },
  "vscode": {
   "interpreter": {
    "hash": "9b2ad45377be23472136155f9be1fd1f9127868d50a62bcbda4e8cdcd384c3ac"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}