examples/use-cases/ibor/Generating holdings with the movements engine in LUSID.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "\"\"\"Generating holdings\n",
    "\n",
    "Generating holdings with the movements engine\n",
    "\n",
    "Attributes\n",
    "----------\n",
    "cocoon\n",
    "instruments\n",
    "transactions\n",
    "transaction configuration\n",
    "\"\"\""
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Generating holdings with the movements engine\n",
    "\n",
    "In this notebook we will show you how to <b>create</b> and <b>modify</b> holdings in your portfolio using LUSID's movements engine:\n",
    "\n",
    "1. Create holdings by booking `Transactions`\n",
    "\n",
    "2. Create holdings by setting `Holdings` directly\n",
    "\n",
    "3. Create custom `Transaction Types` and `Sub-holding Keys` to control how LUSID's movements engine builds `Holdings`\n",
    "\n",
    "To demonstrate, consider the example of a simple equity portfolio holding FTSE100 stocks. First, we'll book transactions to generate holdings. Secondly, we'll set the holdings directly. Finally, we will create a custom transaction type to show how the holdings can be modified. For that example, we'll create a new `Transaction Type` to represent a stock lending transaction and show how this new `Transaction Type` impacts the holding report using `Sub-holding Keys`. \n",
    "\n",
    "<br>\n",
    "\n",
    "\n",
    "> **Key message:**  LUSID's movements engine provides you with flexibility to group, pivot and organise your holdings data.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 0.1) Import Python packages (LUSID and others)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import the general purpose Python packages\n",
    "\n",
    "import copy\n",
    "import os\n",
    "import pytz\n",
    "from datetime import datetime\n",
    "import warnings\n",
    "\n",
    "# Import lusid specific packages\n",
    "# These are the core lusid packages for interacting with the API via Python\n",
    "\n",
    "from lusid.utilities import ApiClientFactory\n",
    "from lusidjam import RefreshingToken\n",
    "from lusidtools.cocoon.cocoon import load_from_data_frame\n",
    "from lusidtools.pandas_utils.lusid_pandas import lusid_response_to_data_frame\n",
    "from lusidtools.cocoon.cocoon_printer import (\n",
    "    format_instruments_response,\n",
    "    format_portfolios_response,\n",
    "    format_transactions_response,\n",
    ")\n",
    "from lusidtools.cocoon.transaction_type_upload import (\n",
    "    create_transaction_type_configuration,\n",
    ")\n",
    "\n",
    "import lusid\n",
    "import lusid.models as models\n",
    "import globalfund as global_fund_tools\n",
    "\n",
    "# Import data wrangling packages\n",
    "# One we havent used in other notebooks is the pandas_utils package\n",
    "# We use this to flatten the results of a get_holdings response to parse the data into a neat DataFrame\n",
    "\n",
    "from IPython.display import display_html\n",
    "import pandas as pd\n",
    "import json\n",
    "\n",
    "pd.set_option(\"display.max_columns\", None)\n",
    "warnings.filterwarnings(\"ignore\")\n",
    "\n",
    "# Authenticate our user and create our API client\n",
    "secrets_path = os.getenv(\"FBN_SECRETS_PATH\")\n",
    "\n",
    "api_factory = lusid.utilities.ApiClientFactory(\n",
    "    token=RefreshingToken(),\n",
    "    api_secrets_filename=secrets_path,\n",
    "    app_name=\"LusidJupyterNotebook\",\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 0.2) Setup and load data\n",
    "\n",
    "Define two scopes and a portfolio:\n",
    "\n",
    "* The first <b>transactions_scope</b> will be used to show a holdings created by Transactions\n",
    "* The second <b>holdings_scope</b> will be used to show holdings created by setting holdings"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# define a scope and a porfolio to to used in this notebook\n",
    "transactions_scope = \"GenerateWithUpsertTransactions\"\n",
    "holdings_scope = \"GenerateWithSetHoldings\"\n",
    "\n",
    "# define a portfolio code \n",
    "portfolio_code = \"EQUITY_UK\"\n",
    "\n",
    "# define some dates we'll use below\n",
    "after_loan_date = \"2020-02-20\"\n",
    "before_loan_date = \"2020-01-31\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Load a mapping file to format our DataFrame columns:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load a file to format holding response\n",
    "with open(r\"config/generate-holdings/get_holdings_mapping.json\") as mappings_file:\n",
    "    format_holdings_response = json.load(mappings_file)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Define the transactions portfolio API"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "transaction_portfolios_api = api_factory.build(lusid.api.TransactionPortfoliosApi)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Define some useful functions for use below"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_holdings_df(scope, code, date=datetime.now(pytz.UTC)):\n",
    "\n",
    "    holdings_response = transaction_portfolios_api.get_holdings(\n",
    "        scope=scope,\n",
    "        code=code,\n",
    "        property_keys=[\"Instrument/default/Name\"],\n",
    "        effective_at=date,\n",
    "    )\n",
    "\n",
    "    holdings_df = lusid_response_to_data_frame(\n",
    "        holdings_response,\n",
    "        rename_properties=True,\n",
    "        column_name_mapping=format_holdings_response,\n",
    "    )\n",
    "\n",
    "    return holdings_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 0.3) Read in data from external files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>portfolio_code</th>\n",
       "      <th>portfolio_name</th>\n",
       "      <th>portfolio_base_currency</th>\n",
       "      <th>ticker</th>\n",
       "      <th>sedol</th>\n",
       "      <th>instrument_type</th>\n",
       "      <th>instrument_id</th>\n",
       "      <th>name</th>\n",
       "      <th>txn_id</th>\n",
       "      <th>txn_type</th>\n",
       "      <th>txn_trade_date</th>\n",
       "      <th>txn_settle_date</th>\n",
       "      <th>txn_units</th>\n",
       "      <th>txn_price</th>\n",
       "      <th>txn_consideration</th>\n",
       "      <th>currency</th>\n",
       "      <th>strategy</th>\n",
       "      <th>cash_transactions</th>\n",
       "      <th>StockLendingStatus</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>EQUITY_UK</td>\n",
       "      <td>Generic UK Equity portfolio</td>\n",
       "      <td>GBP</td>\n",
       "      <td>AVI</td>\n",
       "      <td>SEDOL1</td>\n",
       "      <td>equity</td>\n",
       "      <td>EQ_1234</td>\n",
       "      <td>Aviva</td>\n",
       "      <td>trd_0001</td>\n",
       "      <td>Buy</td>\n",
       "      <td>02/01/2020</td>\n",
       "      <td>04/01/2020</td>\n",
       "      <td>120000</td>\n",
       "      <td>5</td>\n",
       "      <td>600000</td>\n",
       "      <td>GBP</td>\n",
       "      <td>ftse_tracker</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PortfolioPosition</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>EQUITY_UK</td>\n",
       "      <td>Generic UK Equity portfolio</td>\n",
       "      <td>GBP</td>\n",
       "      <td>AVI</td>\n",
       "      <td>SEDOL1</td>\n",
       "      <td>equity</td>\n",
       "      <td>EQ_1234</td>\n",
       "      <td>Aviva</td>\n",
       "      <td>trd_0002</td>\n",
       "      <td>Buy</td>\n",
       "      <td>15/01/2020</td>\n",
       "      <td>17/01/2020</td>\n",
       "      <td>12000</td>\n",
       "      <td>5</td>\n",
       "      <td>60000</td>\n",
       "      <td>GBP</td>\n",
       "      <td>ftse_tracker</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PortfolioPosition</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>EQUITY_UK</td>\n",
       "      <td>Generic UK Equity portfolio</td>\n",
       "      <td>GBP</td>\n",
       "      <td>BH</td>\n",
       "      <td>SEDOL2</td>\n",
       "      <td>equity</td>\n",
       "      <td>EQ_1235</td>\n",
       "      <td>BHP</td>\n",
       "      <td>trd_0003</td>\n",
       "      <td>Buy</td>\n",
       "      <td>02/01/2020</td>\n",
       "      <td>04/01/2020</td>\n",
       "      <td>60000</td>\n",
       "      <td>18</td>\n",
       "      <td>1080000</td>\n",
       "      <td>GBP</td>\n",
       "      <td>ftse_tracker</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PortfolioPosition</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>EQUITY_UK</td>\n",
       "      <td>Generic UK Equity portfolio</td>\n",
       "      <td>GBP</td>\n",
       "      <td>BH</td>\n",
       "      <td>SEDOL2</td>\n",
       "      <td>equity</td>\n",
       "      <td>EQ_1235</td>\n",
       "      <td>BHP</td>\n",
       "      <td>trd_0004</td>\n",
       "      <td>Buy</td>\n",
       "      <td>15/01/2020</td>\n",
       "      <td>17/01/2020</td>\n",
       "      <td>60000</td>\n",
       "      <td>18</td>\n",
       "      <td>1080000</td>\n",
       "      <td>GBP</td>\n",
       "      <td>ftse_tracker</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PortfolioPosition</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>EQUITY_UK</td>\n",
       "      <td>Generic UK Equity portfolio</td>\n",
       "      <td>GBP</td>\n",
       "      <td>BARC</td>\n",
       "      <td>SEDOL3</td>\n",
       "      <td>equity</td>\n",
       "      <td>EQ_1236</td>\n",
       "      <td>Barclays</td>\n",
       "      <td>trd_0005</td>\n",
       "      <td>Buy</td>\n",
       "      <td>02/01/2020</td>\n",
       "      <td>04/01/2020</td>\n",
       "      <td>150000</td>\n",
       "      <td>2</td>\n",
       "      <td>300000</td>\n",
       "      <td>GBP</td>\n",
       "      <td>ftse_tracker</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PortfolioPosition</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>EQUITY_UK</td>\n",
       "      <td>Generic UK Equity portfolio</td>\n",
       "      <td>GBP</td>\n",
       "      <td>BARC</td>\n",
       "      <td>SEDOL3</td>\n",
       "      <td>equity</td>\n",
       "      <td>EQ_1236</td>\n",
       "      <td>Barclays</td>\n",
       "      <td>trd_0006</td>\n",
       "      <td>Buy</td>\n",
       "      <td>15/01/2020</td>\n",
       "      <td>17/01/2020</td>\n",
       "      <td>150000</td>\n",
       "      <td>2</td>\n",
       "      <td>300000</td>\n",
       "      <td>GBP</td>\n",
       "      <td>ftse_tracker</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PortfolioPosition</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>EQUITY_UK</td>\n",
       "      <td>Generic UK Equity portfolio</td>\n",
       "      <td>GBP</td>\n",
       "      <td>GBP</td>\n",
       "      <td>GBP</td>\n",
       "      <td>cash</td>\n",
       "      <td>GBP</td>\n",
       "      <td>GBP Cash</td>\n",
       "      <td>cash_001</td>\n",
       "      <td>FundsIn</td>\n",
       "      <td>02/01/2020</td>\n",
       "      <td>04/01/2020</td>\n",
       "      <td>12000000</td>\n",
       "      <td>1</td>\n",
       "      <td>5000000</td>\n",
       "      <td>GBP</td>\n",
       "      <td>ftse_tracker</td>\n",
       "      <td>GBP</td>\n",
       "      <td>PortfolioPosition</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  portfolio_code               portfolio_name portfolio_base_currency ticker  \\\n",
       "0      EQUITY_UK  Generic UK Equity portfolio                     GBP    AVI   \n",
       "1      EQUITY_UK  Generic UK Equity portfolio                     GBP    AVI   \n",
       "2      EQUITY_UK  Generic UK Equity portfolio                     GBP     BH   \n",
       "3      EQUITY_UK  Generic UK Equity portfolio                     GBP     BH   \n",
       "4      EQUITY_UK  Generic UK Equity portfolio                     GBP   BARC   \n",
       "5      EQUITY_UK  Generic UK Equity portfolio                     GBP   BARC   \n",
       "6      EQUITY_UK  Generic UK Equity portfolio                     GBP    GBP   \n",
       "\n",
       "    sedol instrument_type instrument_id      name    txn_id txn_type  \\\n",
       "0  SEDOL1          equity       EQ_1234     Aviva  trd_0001      Buy   \n",
       "1  SEDOL1          equity       EQ_1234     Aviva  trd_0002      Buy   \n",
       "2  SEDOL2          equity       EQ_1235       BHP  trd_0003      Buy   \n",
       "3  SEDOL2          equity       EQ_1235       BHP  trd_0004      Buy   \n",
       "4  SEDOL3          equity       EQ_1236  Barclays  trd_0005      Buy   \n",
       "5  SEDOL3          equity       EQ_1236  Barclays  trd_0006      Buy   \n",
       "6     GBP            cash           GBP  GBP Cash  cash_001  FundsIn   \n",
       "\n",
       "  txn_trade_date txn_settle_date  txn_units  txn_price  txn_consideration  \\\n",
       "0     02/01/2020      04/01/2020     120000          5             600000   \n",
       "1     15/01/2020      17/01/2020      12000          5              60000   \n",
       "2     02/01/2020      04/01/2020      60000         18            1080000   \n",
       "3     15/01/2020      17/01/2020      60000         18            1080000   \n",
       "4     02/01/2020      04/01/2020     150000          2             300000   \n",
       "5     15/01/2020      17/01/2020     150000          2             300000   \n",
       "6     02/01/2020      04/01/2020   12000000          1            5000000   \n",
       "\n",
       "  currency      strategy cash_transactions StockLendingStatus  \n",
       "0      GBP  ftse_tracker               NaN  PortfolioPosition  \n",
       "1      GBP  ftse_tracker               NaN  PortfolioPosition  \n",
       "2      GBP  ftse_tracker               NaN  PortfolioPosition  \n",
       "3      GBP  ftse_tracker               NaN  PortfolioPosition  \n",
       "4      GBP  ftse_tracker               NaN  PortfolioPosition  \n",
       "5      GBP  ftse_tracker               NaN  PortfolioPosition  \n",
       "6      GBP  ftse_tracker               GBP  PortfolioPosition  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Load in transactions CSV file\n",
    "\n",
    "transactions_df = pd.read_csv(\n",
    "    \"data/generate-holdings/generate_holdings_transactions.csv\"\n",
    ")\n",
    "transactions_df[\"StockLendingStatus\"] = \"PortfolioPosition\"\n",
    "transactions_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>instrument_name</th>\n",
       "      <th>quantity</th>\n",
       "      <th>price</th>\n",
       "      <th>currency</th>\n",
       "      <th>ClientInternal</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>Aviva</td>\n",
       "      <td>132000</td>\n",
       "      <td>5</td>\n",
       "      <td>GBP</td>\n",
       "      <td>EQ_1234</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>EQUITY_UK</td>\n",
       "      <td>BHP</td>\n",
       "      <td>120000</td>\n",
       "      <td>18</td>\n",
       "      <td>GBP</td>\n",
       "      <td>EQ_1235</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>EQUITY_UK</td>\n",
       "      <td>Barclays</td>\n",
       "      <td>300000</td>\n",
       "      <td>2</td>\n",
       "      <td>GBP</td>\n",
       "      <td>EQ_1236</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>EQUITY_UK</td>\n",
       "      <td>Cash</td>\n",
       "      <td>8580000</td>\n",
       "      <td>1</td>\n",
       "      <td>GBP</td>\n",
       "      <td>GBP</td>\n",
       "      <td>GBP</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  portfolio_code instrument_name  quantity  price currency ClientInternal  \\\n",
       "0      EQUITY_UK           Aviva    132000      5      GBP        EQ_1234   \n",
       "1      EQUITY_UK             BHP    120000     18      GBP        EQ_1235   \n",
       "2      EQUITY_UK        Barclays    300000      2      GBP        EQ_1236   \n",
       "3      EQUITY_UK            Cash   8580000      1      GBP            GBP   \n",
       "\n",
       "  cash_transactions  \n",
       "0               NaN  \n",
       "1               NaN  \n",
       "2               NaN  \n",
       "3               GBP  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Load in holdings CSV file\n",
    "\n",
    "holdings_df = pd.read_csv(\"data/generate-holdings/set_holdings.csv\")\n",
    "holdings_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 0.4) Setup the instrument master \n",
    "\n",
    "Here we setup an instrument master. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "instruments_df = transactions_df[[\"instrument_id\", \"name\"]]\n",
    "\n",
    "instrument_mapping = {\n",
    "    \"identifier_mapping\": {\"ClientInternal\": \"instrument_id\"},\n",
    "    \"required\": {\"name\": \"name\"},\n",
    "    \"properties\": [],\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   success  failed  errors\n",
       "0        4       0       0"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "upsert_instrument_response = load_from_data_frame(\n",
    "    api_factory=api_factory,\n",
    "    scope=transactions_scope,\n",
    "    data_frame=instruments_df,\n",
    "    mapping_required=instrument_mapping[\"required\"],\n",
    "    mapping_optional={},\n",
    "    file_type=\"instruments\",\n",
    "    identifier_mapping=instrument_mapping[\"identifier_mapping\"],\n",
    "    property_columns=instrument_mapping[\"properties\"],\n",
    "    properties_scope=transactions_scope,\n",
    ")\n",
    "\n",
    "succ, failed, errors = format_instruments_response(upsert_instrument_response)\n",
    "display(\n",
    "    pd.DataFrame(\n",
    "        data=[{\"success\": len(succ), \"failed\": len(failed), \"errors\": len(errors)}]\n",
    "    )\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 0.5) Setup the portfolio \n",
    "\n",
    "Next we setup a portfolio called EQUITY_UK in both scopes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>EQUITY_UK</td>\n",
       "      <td>Generic UK Equity portfolio</td>\n",
       "      <td>GBP</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  portfolio_code               portfolio_name portfolio_base_currency\n",
       "0      EQUITY_UK  Generic UK Equity portfolio                     GBP"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "portfolio_df = transactions_df[\n",
    "    [\"portfolio_code\", \"portfolio_name\", \"portfolio_base_currency\"]\n",
    "]\n",
    "portfolio_df.head(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "portfolio_mapping = {\n",
    "    \"required\": {\n",
    "        \"code\": \"portfolio_code\",\n",
    "        \"display_name\": \"portfolio_name\",\n",
    "        \"base_currency\": \"portfolio_base_currency\",\n",
    "        \"created\": \"$2018-01-01T00:00:00+00:00\",\n",
    "    }\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   success  failed  errors\n",
       "0        1       0       0"
      ]
     },
     "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>success</th>\n",
       "      <th>failed</th>\n",
       "      <th>errors</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   success  failed  errors\n",
       "0        1       0       0"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "for scope in transactions_scope, holdings_scope:\n",
    "\n",
    "    upsert_portfolio_scope = load_from_data_frame(\n",
    "        api_factory=api_factory,\n",
    "        scope=scope,\n",
    "        data_frame=portfolio_df,\n",
    "        mapping_required=portfolio_mapping[\"required\"],\n",
    "        mapping_optional={},\n",
    "        file_type=\"portfolios\",\n",
    "        sub_holding_keys=[\"Transaction/GenerateHoldings/StockLendingStatus\"],\n",
    "    )\n",
    "\n",
    "    succ, failed = format_portfolios_response(upsert_portfolio_scope)\n",
    "    display(\n",
    "        pd.DataFrame(\n",
    "            data=[{\"success\": len(succ), \"failed\": len(failed), \"errors\": len(errors)}]\n",
    "        )\n",
    "    )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1.1) Post transactions to generate holdings\n",
    "\n",
    "First we post some transactions into EQUITY_UK in the <b>transactions_scope</b>."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "transaction_mapping = {\n",
    "    \"identifier_mapping\": {\n",
    "        \"ClientInternal\": \"instrument_id\",\n",
    "        \"Currency\": \"cash_transactions\",\n",
    "    },\n",
    "    \"required\": {\n",
    "        \"code\": \"portfolio_code\",\n",
    "        \"transaction_id\": \"txn_id\",\n",
    "        \"type\": \"txn_type\",\n",
    "        \"transaction_price.price\": \"txn_price\",\n",
    "        \"transaction_price.type\": \"$Price\",\n",
    "        \"total_consideration.amount\": \"txn_consideration\",\n",
    "        \"units\": \"txn_units\",\n",
    "        \"transaction_date\": \"txn_trade_date\",\n",
    "        \"total_consideration.currency\": \"currency\",\n",
    "        \"settlement_date\": \"txn_settle_date\",\n",
    "    },\n",
    "    \"optional\": {},\n",
    "    \"properties\": [\"StockLendingStatus\"],\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "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>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   success  failed  errors\n",
       "0        1       0       0"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "result = load_from_data_frame(\n",
    "    api_factory=api_factory,\n",
    "    scope=transactions_scope,\n",
    "    data_frame=transactions_df,\n",
    "    mapping_required=transaction_mapping[\"required\"],\n",
    "    mapping_optional=transaction_mapping[\"optional\"],\n",
    "    file_type=\"transactions\",\n",
    "    identifier_mapping=transaction_mapping[\"identifier_mapping\"],\n",
    "    property_columns=transaction_mapping[\"properties\"],\n",
    "    properties_scope=\"GenerateHoldings\",\n",
    ")\n",
    "\n",
    "succ, failed = format_transactions_response(result)\n",
    "display(\n",
    "    pd.DataFrame(\n",
    "        data=[{\"success\": len(succ), \"failed\": len(failed), \"errors\": len(errors)}]\n",
    "    )\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Check the holdings.\n",
    "\n",
    "We see 3 equity positions and 1 GBP cash line."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "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>LusidInstrumentId</th>\n",
       "      <th>StockLendingStatus(GenerateHoldings-SubHoldingKeys)</th>\n",
       "      <th>InstrumentName</th>\n",
       "      <th>SourcePortfolioId</th>\n",
       "      <th>SourcePortfolioScope(default-Properties)</th>\n",
       "      <th>HoldingType</th>\n",
       "      <th>Units</th>\n",
       "      <th>SettledUnits</th>\n",
       "      <th>Amount-Cost</th>\n",
       "      <th>Currency-Cost</th>\n",
       "      <th>Amount-CostPortfolioCcy</th>\n",
       "      <th>Currenct-CostPortfolioCcy</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>LUID_ATFGUBHS</td>\n",
       "      <td>PortfolioPosition</td>\n",
       "      <td>Aviva</td>\n",
       "      <td>EQUITY_UK</td>\n",
       "      <td>GenerateWithUpsertTransactions</td>\n",
       "      <td>P</td>\n",
       "      <td>132000.00</td>\n",
       "      <td>132000.00</td>\n",
       "      <td>660000.00</td>\n",
       "      <td>GBP</td>\n",
       "      <td>0.00</td>\n",
       "      <td>GBP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>LUID_7XM08GZF</td>\n",
       "      <td>PortfolioPosition</td>\n",
       "      <td>BHP</td>\n",
       "      <td>EQUITY_UK</td>\n",
       "      <td>GenerateWithUpsertTransactions</td>\n",
       "      <td>P</td>\n",
       "      <td>120000.00</td>\n",
       "      <td>120000.00</td>\n",
       "      <td>2160000.00</td>\n",
       "      <td>GBP</td>\n",
       "      <td>0.00</td>\n",
       "      <td>GBP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>LUID_STGB38I6</td>\n",
       "      <td>PortfolioPosition</td>\n",
       "      <td>Barclays</td>\n",
       "      <td>EQUITY_UK</td>\n",
       "      <td>GenerateWithUpsertTransactions</td>\n",
       "      <td>P</td>\n",
       "      <td>300000.00</td>\n",
       "      <td>300000.00</td>\n",
       "      <td>600000.00</td>\n",
       "      <td>GBP</td>\n",
       "      <td>0.00</td>\n",
       "      <td>GBP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>CCY_GBP</td>\n",
       "      <td>PortfolioPosition</td>\n",
       "      <td>CCY_GBP</td>\n",
       "      <td>EQUITY_UK</td>\n",
       "      <td>GenerateWithUpsertTransactions</td>\n",
       "      <td>B</td>\n",
       "      <td>8580000.00</td>\n",
       "      <td>8580000.00</td>\n",
       "      <td>8580000.00</td>\n",
       "      <td>GBP</td>\n",
       "      <td>0.00</td>\n",
       "      <td>GBP</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  LusidInstrumentId StockLendingStatus(GenerateHoldings-SubHoldingKeys)  \\\n",
       "0     LUID_ATFGUBHS                                  PortfolioPosition    \n",
       "1     LUID_7XM08GZF                                  PortfolioPosition    \n",
       "2     LUID_STGB38I6                                  PortfolioPosition    \n",
       "3           CCY_GBP                                  PortfolioPosition    \n",
       "\n",
       "  InstrumentName SourcePortfolioId SourcePortfolioScope(default-Properties)  \\\n",
       "0          Aviva         EQUITY_UK           GenerateWithUpsertTransactions   \n",
       "1            BHP         EQUITY_UK           GenerateWithUpsertTransactions   \n",
       "2       Barclays         EQUITY_UK           GenerateWithUpsertTransactions   \n",
       "3        CCY_GBP         EQUITY_UK           GenerateWithUpsertTransactions   \n",
       "\n",
       "  HoldingType      Units  SettledUnits  Amount-Cost Currency-Cost  \\\n",
       "0           P  132000.00     132000.00    660000.00           GBP   \n",
       "1           P  120000.00     120000.00   2160000.00           GBP   \n",
       "2           P  300000.00     300000.00    600000.00           GBP   \n",
       "3           B 8580000.00    8580000.00   8580000.00           GBP   \n",
       "\n",
       "   Amount-CostPortfolioCcy Currenct-CostPortfolioCcy  \n",
       "0                     0.00                       GBP  \n",
       "1                     0.00                       GBP  \n",
       "2                     0.00                       GBP  \n",
       "3                     0.00                       GBP  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get_holdings_df(transactions_scope, portfolio_code, date=before_loan_date)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.1) Set holdings using holdings file\n",
    "\n",
    "Secondly, we set holdings directly using SetHoldings."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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>instrument_name</th>\n",
       "      <th>quantity</th>\n",
       "      <th>price</th>\n",
       "      <th>currency</th>\n",
       "      <th>ClientInternal</th>\n",
       "      <th>cash_transactions</th>\n",
       "      <th>market_value</th>\n",
       "      <th>StockLendingStatus</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>EQUITY_UK</td>\n",
       "      <td>Aviva</td>\n",
       "      <td>132000</td>\n",
       "      <td>5</td>\n",
       "      <td>GBP</td>\n",
       "      <td>EQ_1234</td>\n",
       "      <td>NaN</td>\n",
       "      <td>660000</td>\n",
       "      <td>PortfolioPosition</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>EQUITY_UK</td>\n",
       "      <td>BHP</td>\n",
       "      <td>120000</td>\n",
       "      <td>18</td>\n",
       "      <td>GBP</td>\n",
       "      <td>EQ_1235</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2160000</td>\n",
       "      <td>PortfolioPosition</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>EQUITY_UK</td>\n",
       "      <td>Barclays</td>\n",
       "      <td>300000</td>\n",
       "      <td>2</td>\n",
       "      <td>GBP</td>\n",
       "      <td>EQ_1236</td>\n",
       "      <td>NaN</td>\n",
       "      <td>600000</td>\n",
       "      <td>PortfolioPosition</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>EQUITY_UK</td>\n",
       "      <td>Cash</td>\n",
       "      <td>8580000</td>\n",
       "      <td>1</td>\n",
       "      <td>GBP</td>\n",
       "      <td>GBP</td>\n",
       "      <td>GBP</td>\n",
       "      <td>8580000</td>\n",
       "      <td>PortfolioPosition</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  portfolio_code instrument_name  quantity  price currency ClientInternal  \\\n",
       "0      EQUITY_UK           Aviva    132000      5      GBP        EQ_1234   \n",
       "1      EQUITY_UK             BHP    120000     18      GBP        EQ_1235   \n",
       "2      EQUITY_UK        Barclays    300000      2      GBP        EQ_1236   \n",
       "3      EQUITY_UK            Cash   8580000      1      GBP            GBP   \n",
       "\n",
       "  cash_transactions  market_value StockLendingStatus  \n",
       "0               NaN        660000  PortfolioPosition  \n",
       "1               NaN       2160000  PortfolioPosition  \n",
       "2               NaN        600000  PortfolioPosition  \n",
       "3               GBP       8580000  PortfolioPosition  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "holdings_df[\"market_value\"] = holdings_df[\"price\"] * holdings_df[\"quantity\"]\n",
    "holdings_df[\"StockLendingStatus\"] = \"PortfolioPosition\"\n",
    "holdings_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "holdings_mapping = {\n",
    "    \"name\": \"instrument_name\",\n",
    "    \"effective_at\": \"$2020-01-01\",\n",
    "    \"code\": \"portfolio_code\",\n",
    "    \"tax_lots.units\": \"quantity\",\n",
    "    \"tax_lots.price\": \"price\",\n",
    "    \"tax_lots.portfolio_cost\": \"market_value\",\n",
    "    \"tax_lots.cost.currency\": \"currency\",\n",
    "    \"tax_lots.cost.amount\": \"market_value\",\n",
    "}\n",
    "\n",
    "identifiers = {\"ClientInternal\": \"ClientInternal\", \"Currency\": \"cash_transactions\"}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "holdings_response = load_from_data_frame(\n",
    "    api_factory=api_factory,\n",
    "    scope=holdings_scope,\n",
    "    data_frame=holdings_df,\n",
    "    mapping_required=holdings_mapping,\n",
    "    mapping_optional={},\n",
    "    file_type=\"holdings\",\n",
    "    identifier_mapping=identifiers,\n",
    "    sub_holding_keys=[\"StockLendingStatus\"],\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Again, we see three equity positions and one GBP cash line."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "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>LusidInstrumentId</th>\n",
       "      <th>StockLendingStatus(GenerateHoldings-SubHoldingKeys)</th>\n",
       "      <th>InstrumentName</th>\n",
       "      <th>SourcePortfolioId</th>\n",
       "      <th>SourcePortfolioScope(default-Properties)</th>\n",
       "      <th>HoldingType</th>\n",
       "      <th>Units</th>\n",
       "      <th>SettledUnits</th>\n",
       "      <th>Amount-Cost</th>\n",
       "      <th>Currency-Cost</th>\n",
       "      <th>Amount-CostPortfolioCcy</th>\n",
       "      <th>Currenct-CostPortfolioCcy</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>LUID_ATFGUBHS</td>\n",
       "      <td>&lt;Not Classified&gt;</td>\n",
       "      <td>Aviva</td>\n",
       "      <td>EQUITY_UK</td>\n",
       "      <td>GenerateWithSetHoldings</td>\n",
       "      <td>P</td>\n",
       "      <td>132000.00</td>\n",
       "      <td>132000.00</td>\n",
       "      <td>660000.00</td>\n",
       "      <td>GBP</td>\n",
       "      <td>660000.00</td>\n",
       "      <td>GBP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>LUID_7XM08GZF</td>\n",
       "      <td>&lt;Not Classified&gt;</td>\n",
       "      <td>BHP</td>\n",
       "      <td>EQUITY_UK</td>\n",
       "      <td>GenerateWithSetHoldings</td>\n",
       "      <td>P</td>\n",
       "      <td>120000.00</td>\n",
       "      <td>120000.00</td>\n",
       "      <td>2160000.00</td>\n",
       "      <td>GBP</td>\n",
       "      <td>2160000.00</td>\n",
       "      <td>GBP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>LUID_STGB38I6</td>\n",
       "      <td>&lt;Not Classified&gt;</td>\n",
       "      <td>Barclays</td>\n",
       "      <td>EQUITY_UK</td>\n",
       "      <td>GenerateWithSetHoldings</td>\n",
       "      <td>P</td>\n",
       "      <td>300000.00</td>\n",
       "      <td>300000.00</td>\n",
       "      <td>600000.00</td>\n",
       "      <td>GBP</td>\n",
       "      <td>600000.00</td>\n",
       "      <td>GBP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>CCY_GBP</td>\n",
       "      <td>&lt;Not Classified&gt;</td>\n",
       "      <td>CCY_GBP</td>\n",
       "      <td>EQUITY_UK</td>\n",
       "      <td>GenerateWithSetHoldings</td>\n",
       "      <td>B</td>\n",
       "      <td>8580000.00</td>\n",
       "      <td>8580000.00</td>\n",
       "      <td>8580000.00</td>\n",
       "      <td>GBP</td>\n",
       "      <td>8580000.00</td>\n",
       "      <td>GBP</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  LusidInstrumentId StockLendingStatus(GenerateHoldings-SubHoldingKeys)  \\\n",
       "0     LUID_ATFGUBHS                                   <Not Classified>    \n",
       "1     LUID_7XM08GZF                                   <Not Classified>    \n",
       "2     LUID_STGB38I6                                   <Not Classified>    \n",
       "3           CCY_GBP                                   <Not Classified>    \n",
       "\n",
       "  InstrumentName SourcePortfolioId SourcePortfolioScope(default-Properties)  \\\n",
       "0          Aviva         EQUITY_UK                  GenerateWithSetHoldings   \n",
       "1            BHP         EQUITY_UK                  GenerateWithSetHoldings   \n",
       "2       Barclays         EQUITY_UK                  GenerateWithSetHoldings   \n",
       "3        CCY_GBP         EQUITY_UK                  GenerateWithSetHoldings   \n",
       "\n",
       "  HoldingType      Units  SettledUnits  Amount-Cost Currency-Cost  \\\n",
       "0           P  132000.00     132000.00    660000.00           GBP   \n",
       "1           P  120000.00     120000.00   2160000.00           GBP   \n",
       "2           P  300000.00     300000.00    600000.00           GBP   \n",
       "3           B 8580000.00    8580000.00   8580000.00           GBP   \n",
       "\n",
       "   Amount-CostPortfolioCcy Currenct-CostPortfolioCcy  \n",
       "0                660000.00                       GBP  \n",
       "1               2160000.00                       GBP  \n",
       "2                600000.00                       GBP  \n",
       "3               8580000.00                       GBP  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get_holdings_df(holdings_scope, portfolio_code, date=before_loan_date)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.1) Configure a new transaction type for a stock lending transaction\n",
    "\n",
    "In this section, we create a new `transaction type` to capture a stock lending transaction. When we loan a stock, we want to capture the loan amount in the IBOR, but it should be segragated from the other non-loan positions.  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Configure a new transaction type\n",
    "\n",
    "movement = [\n",
    "    models.TransactionConfigurationMovementDataRequest(\n",
    "        movement_types=\"StockMovement\",\n",
    "        side=\"Side1\",\n",
    "        direction=-1,\n",
    "        properties={},\n",
    "        mappings=[\n",
    "            models.TransactionPropertyMappingRequest(\n",
    "                property_key=f\"Transaction/GenerateHoldings/StockLendingStatus\",\n",
    "                set_to=\"PortfolioPosition\",\n",
    "            )\n",
    "        ],\n",
    "    ),\n",
    "    models.TransactionConfigurationMovementDataRequest(\n",
    "        movement_types=\"StockMovement\",\n",
    "        side=\"Side1\",\n",
    "        direction=1,\n",
    "        properties={},\n",
    "        mappings=[\n",
    "            models.TransactionPropertyMappingRequest(\n",
    "                property_key=f\"Transaction/GenerateHoldings/StockLendingStatus\",\n",
    "                set_to=\"Loan\",\n",
    "            )\n",
    "        ],\n",
    "    ),\n",
    "]\n",
    "\n",
    "\n",
    "alias = models.TransactionConfigurationTypeAlias(\n",
    "    type=\"StockLoanOut\",\n",
    "    description=\"Booking of stock lending transaction out\",\n",
    "    transaction_class=\"StockLending\",\n",
    "    transaction_group=\"default\",\n",
    "    transaction_roles=\"Shorter\",\n",
    ")\n",
    "response = create_transaction_type_configuration(api_factory, alias, movement)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.2) Post the stock lending transaction into our portfolio "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>portfolio_code</th>\n",
       "      <th>portfolio_name</th>\n",
       "      <th>portfolio_base_currency</th>\n",
       "      <th>ticker</th>\n",
       "      <th>sedol</th>\n",
       "      <th>instrument_type</th>\n",
       "      <th>instrument_id</th>\n",
       "      <th>name</th>\n",
       "      <th>txn_id</th>\n",
       "      <th>txn_type</th>\n",
       "      <th>txn_trade_date</th>\n",
       "      <th>txn_settle_date</th>\n",
       "      <th>txn_units</th>\n",
       "      <th>txn_price</th>\n",
       "      <th>txn_consideration</th>\n",
       "      <th>currency</th>\n",
       "      <th>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>Generic UK Equity portfolio</td>\n",
       "      <td>GBP</td>\n",
       "      <td>BARC</td>\n",
       "      <td>SEDOL3</td>\n",
       "      <td>equity</td>\n",
       "      <td>EQ_1236</td>\n",
       "      <td>Barclays</td>\n",
       "      <td>stocklend_0001</td>\n",
       "      <td>StockLoanOut</td>\n",
       "      <td>15/02/2020</td>\n",
       "      <td>17/02/2020</td>\n",
       "      <td>150000</td>\n",
       "      <td>2</td>\n",
       "      <td>300000</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 ticker  \\\n",
       "0      EQUITY_UK  Generic UK Equity portfolio                     GBP   BARC   \n",
       "\n",
       "    sedol instrument_type instrument_id      name          txn_id  \\\n",
       "0  SEDOL3          equity       EQ_1236  Barclays  stocklend_0001   \n",
       "\n",
       "       txn_type txn_trade_date txn_settle_date  txn_units  txn_price  \\\n",
       "0  StockLoanOut     15/02/2020      17/02/2020     150000          2   \n",
       "\n",
       "   txn_consideration currency      strategy  cash_transactions  \n",
       "0             300000      GBP  ftse_tracker                nan  "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "stocklend_df = pd.read_csv(\"data/generate-holdings/stock_lend.csv\")\n",
    "stocklend_df[\"txn_type\"] = \"StockLoanOut\"\n",
    "stocklend_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>success</th>\n",
       "      <th>failed</th>\n",
       "      <th>errors</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   success  failed  errors\n",
       "0        1       0       0"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "for scope in transactions_scope, holdings_scope:\n",
    "\n",
    "    stock_lending_transaction = load_from_data_frame(\n",
    "        api_factory=api_factory,\n",
    "        scope=scope,\n",
    "        data_frame=stocklend_df,\n",
    "        mapping_required=transaction_mapping[\"required\"],\n",
    "        mapping_optional=transaction_mapping[\"optional\"],\n",
    "        file_type=\"transactions\",\n",
    "        identifier_mapping=transaction_mapping[\"identifier_mapping\"],\n",
    "    )\n",
    "\n",
    "succ, failed = format_transactions_response(result)\n",
    "display(\n",
    "    pd.DataFrame(\n",
    "        data=[{\"success\": len(succ), \"failed\": len(failed), \"errors\": len(errors)}]\n",
    "    )\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.3) Check holdings after the stock lending transaction\n",
    "\n",
    "Result - we can see the <i>Barclays PLC</i> holding has been split in a <b>Loan</b> and <b>PortfolioPosition</b> amount. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>LusidInstrumentId</th>\n",
       "      <th>StockLendingStatus(GenerateHoldings-SubHoldingKeys)</th>\n",
       "      <th>InstrumentName</th>\n",
       "      <th>SourcePortfolioId</th>\n",
       "      <th>SourcePortfolioScope(default-Properties)</th>\n",
       "      <th>HoldingType</th>\n",
       "      <th>Units</th>\n",
       "      <th>SettledUnits</th>\n",
       "      <th>Amount-Cost</th>\n",
       "      <th>Currency-Cost</th>\n",
       "      <th>Amount-CostPortfolioCcy</th>\n",
       "      <th>Currenct-CostPortfolioCcy</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>LUID_ATFGUBHS</td>\n",
       "      <td>PortfolioPosition</td>\n",
       "      <td>Aviva</td>\n",
       "      <td>EQUITY_UK</td>\n",
       "      <td>GenerateWithUpsertTransactions</td>\n",
       "      <td>P</td>\n",
       "      <td>132000.00</td>\n",
       "      <td>132000.00</td>\n",
       "      <td>660000.00</td>\n",
       "      <td>GBP</td>\n",
       "      <td>0.00</td>\n",
       "      <td>GBP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>LUID_7XM08GZF</td>\n",
       "      <td>PortfolioPosition</td>\n",
       "      <td>BHP</td>\n",
       "      <td>EQUITY_UK</td>\n",
       "      <td>GenerateWithUpsertTransactions</td>\n",
       "      <td>P</td>\n",
       "      <td>120000.00</td>\n",
       "      <td>120000.00</td>\n",
       "      <td>2160000.00</td>\n",
       "      <td>GBP</td>\n",
       "      <td>0.00</td>\n",
       "      <td>GBP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>LUID_STGB38I6</td>\n",
       "      <td>PortfolioPosition</td>\n",
       "      <td>Barclays</td>\n",
       "      <td>EQUITY_UK</td>\n",
       "      <td>GenerateWithUpsertTransactions</td>\n",
       "      <td>P</td>\n",
       "      <td>150000.00</td>\n",
       "      <td>150000.00</td>\n",
       "      <td>300000.00</td>\n",
       "      <td>GBP</td>\n",
       "      <td>0.00</td>\n",
       "      <td>GBP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>LUID_STGB38I6</td>\n",
       "      <td>Loan</td>\n",
       "      <td>Barclays</td>\n",
       "      <td>EQUITY_UK</td>\n",
       "      <td>GenerateWithUpsertTransactions</td>\n",
       "      <td>P</td>\n",
       "      <td>150000.00</td>\n",
       "      <td>150000.00</td>\n",
       "      <td>300000.00</td>\n",
       "      <td>GBP</td>\n",
       "      <td>0.00</td>\n",
       "      <td>GBP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>CCY_GBP</td>\n",
       "      <td>PortfolioPosition</td>\n",
       "      <td>CCY_GBP</td>\n",
       "      <td>EQUITY_UK</td>\n",
       "      <td>GenerateWithUpsertTransactions</td>\n",
       "      <td>B</td>\n",
       "      <td>8580000.00</td>\n",
       "      <td>8580000.00</td>\n",
       "      <td>8580000.00</td>\n",
       "      <td>GBP</td>\n",
       "      <td>0.00</td>\n",
       "      <td>GBP</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  LusidInstrumentId StockLendingStatus(GenerateHoldings-SubHoldingKeys)  \\\n",
       "0     LUID_ATFGUBHS                                  PortfolioPosition    \n",
       "1     LUID_7XM08GZF                                  PortfolioPosition    \n",
       "2     LUID_STGB38I6                                  PortfolioPosition    \n",
       "3     LUID_STGB38I6                                               Loan    \n",
       "4           CCY_GBP                                  PortfolioPosition    \n",
       "\n",
       "  InstrumentName SourcePortfolioId SourcePortfolioScope(default-Properties)  \\\n",
       "0          Aviva         EQUITY_UK           GenerateWithUpsertTransactions   \n",
       "1            BHP         EQUITY_UK           GenerateWithUpsertTransactions   \n",
       "2       Barclays         EQUITY_UK           GenerateWithUpsertTransactions   \n",
       "3       Barclays         EQUITY_UK           GenerateWithUpsertTransactions   \n",
       "4        CCY_GBP         EQUITY_UK           GenerateWithUpsertTransactions   \n",
       "\n",
       "  HoldingType      Units  SettledUnits  Amount-Cost Currency-Cost  \\\n",
       "0           P  132000.00     132000.00    660000.00           GBP   \n",
       "1           P  120000.00     120000.00   2160000.00           GBP   \n",
       "2           P  150000.00     150000.00    300000.00           GBP   \n",
       "3           P  150000.00     150000.00    300000.00           GBP   \n",
       "4           B 8580000.00    8580000.00   8580000.00           GBP   \n",
       "\n",
       "   Amount-CostPortfolioCcy Currenct-CostPortfolioCcy  \n",
       "0                     0.00                       GBP  \n",
       "1                     0.00                       GBP  \n",
       "2                     0.00                       GBP  \n",
       "3                     0.00                       GBP  \n",
       "4                     0.00                       GBP  "
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get_holdings_df(transactions_scope, portfolio_code, date = after_loan_date)"
   ]
  }
 ],
 "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.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}