V1/product/ibor/Generating an IBOR extract with LUSID's GetHoldings method.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Generating an IBOR extract\\n\\nDemonstrates how to use the GetHoldings API to generate IBOR extracts.\\n\\nAttributes\\n----------\\ncocoon - seed_data\\nholdings\\n'"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\"\"\"Generating an IBOR extract\n",
    "\n",
    "Demonstrates how to use the GetHoldings API to generate IBOR extracts.\n",
    "\n",
    "Attributes\n",
    "----------\n",
    "cocoon - seed_data\n",
    "holdings\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Generating an IBOR extract with LUSID's GetHoldings method\n",
    "\n",
    "This notebooks shows how you can use the [GetHoldings](https://www.lusid.com/docs/api/#operation/GetHoldings) API to generate IBOR extracts."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Setup LUSID"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import system packages\n",
    "\n",
    "import os\n",
    "\n",
    "# Import lusid specific packages\n",
    "# These are the core lusid packages for interacting with the API via Python\n",
    "import lusid\n",
    "import lusid.models as models\n",
    "from lusid.utilities import ApiClientFactory\n",
    "from lusidjam.refreshing_token import RefreshingToken\n",
    "from lusidtools.pandas_utils.lusid_pandas import lusid_response_to_data_frame\n",
    "from lusidtools.cocoon.seed_sample_data import seed_data\n",
    "from lusidtools.cocoon.utilities import create_scope_id\n",
    "\n",
    "# Import data wrangling and data management packages\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import json\n",
    "import openpyxl\n",
    "pd.set_option('display.max_columns', None)\n",
    "\n",
    "# Authenticate our user and create our API client\n",
    "secrets_path = os.getenv(\"FBN_SECRETS_PATH\")\n",
    "\n",
    "# Initiate an API Factory which is the client side object for interacting with LUSID APIs\n",
    "api_factory = lusid.utilities.ApiClientFactory(\n",
    "    token=RefreshingToken(),\n",
    "    api_secrets_filename = secrets_path,\n",
    "    app_name=\"LusidJupyterNotebook\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load a mapping file for formatting DataFrame columns\n",
    "\n",
    "with open(r\"config/build_transactions_mapping.json\") as mappings_file:\n",
    "    build_txn_json_mappings = json.load(mappings_file)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Load transactions into a new scope\n",
    "\n",
    "For the purpose of this demo, we first need to make sure we have a portfolio with some data. In the code below, we create a new portfolio called <b>EQUITY_UK</b> with some transactions from the <i>equity_transactions.csv</i> file. The focus of this notebook is on getting holdings so we use the <i>seed_data()</i> function to quickly generate a demo portfolio. For a more in-depth look into loading transactions and an instrument master in LUSID, see our long-form [tutorial](https://support.finbourne.com/how-do-i-create-holdings) on the support page. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a new scope\n",
    "# Declare a variable to hold our portfolio name\n",
    "\n",
    "scope = create_scope_id()\n",
    "portfolio_code = \"EQUITY_UK\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load a file of equity transactions\n",
    "\n",
    "transactions_file = r\"data/equity_transactions.csv\"\n",
    "transactions_df = pd.read_csv(transactions_file)\n",
    "transactions_df[\"portfolio_code\"] = portfolio_code"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# The seed_data() function takes a file of transaction data\n",
    "# and loads portfolios, instruments, and transactions into LUSID\n",
    "# We use this function as a quick way of generating a demo portfolio\n",
    "\n",
    "seed_data_response = seed_data(api_factory,\n",
    "          [\"portfolios\", \"instruments\", \"transactions\"],\n",
    "          scope,\n",
    "          transactions_file,\n",
    "          \"csv\"\n",
    "         )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## GetHoldings\n",
    "\n",
    "We call the <b>GetHoldings</b> method to generate an IBOR extract. This method requires two inputs: a portfolio and a scope. If we do not pass a date, then the default position date is today. You can modify the <b>scope</b> and <b>code</b> below to generate holdings for your own portfolios."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Define the transaction portfolio API\n",
    "\n",
    "txn_port_api = api_factory.build(lusid.api.TransactionPortfoliosApi)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Call the get_holdings method on the TransactionPortfoliosApi object\n",
    "# The python SDK uses snake case get_holdings to represent LUSID's GetHoldings method\n",
    "# We also pass a property to show each instrument's name\n",
    "\n",
    "holdings_response = txn_port_api.get_holdings(scope=scope,\n",
    "                                     code=portfolio_code,\n",
    "                                     property_keys=[\"Instrument/default/Name\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In the holdings's response, we can see a seperate value (represented as a row in the DataFrame) for each holdings. Holdings in securities have a <b>HoldingType</b> of <b>P</b> while holdings of cash balances have a <b>HoldingType</b> of <b>B</b>. See the linked [tutorial](https://support.finbourne.com/how-do-i-create-holdings) for a full list of holding types in LUSID."
   ]
  },
  {
   "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>instrument_uid</th>\n",
       "      <th>sub_holding_keys</th>\n",
       "      <th>Name(default-Properties)</th>\n",
       "      <th>SourcePortfolioId(default-Properties)</th>\n",
       "      <th>SourcePortfolioScope(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>cost_portfolio_ccy.currency</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>LUID_YN3FEUD5</td>\n",
       "      <td>{}</td>\n",
       "      <td>Aviva</td>\n",
       "      <td>EQUITY_UK</td>\n",
       "      <td>3967-b0e1-f73b-26</td>\n",
       "      <td>P</td>\n",
       "      <td>132000.0</td>\n",
       "      <td>132000.0</td>\n",
       "      <td>660000.0</td>\n",
       "      <td>GBP</td>\n",
       "      <td>0.0</td>\n",
       "      <td>GBP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>LUID_7EQ664NC</td>\n",
       "      <td>{}</td>\n",
       "      <td>BHP</td>\n",
       "      <td>EQUITY_UK</td>\n",
       "      <td>3967-b0e1-f73b-26</td>\n",
       "      <td>P</td>\n",
       "      <td>120000.0</td>\n",
       "      <td>120000.0</td>\n",
       "      <td>2160000.0</td>\n",
       "      <td>GBP</td>\n",
       "      <td>0.0</td>\n",
       "      <td>GBP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>LUID_F87JY45S</td>\n",
       "      <td>{}</td>\n",
       "      <td>Barclays</td>\n",
       "      <td>EQUITY_UK</td>\n",
       "      <td>3967-b0e1-f73b-26</td>\n",
       "      <td>P</td>\n",
       "      <td>300000.0</td>\n",
       "      <td>300000.0</td>\n",
       "      <td>600000.0</td>\n",
       "      <td>GBP</td>\n",
       "      <td>0.0</td>\n",
       "      <td>GBP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>LUID_8X5TTJTL</td>\n",
       "      <td>{}</td>\n",
       "      <td>BP</td>\n",
       "      <td>EQUITY_UK</td>\n",
       "      <td>3967-b0e1-f73b-26</td>\n",
       "      <td>P</td>\n",
       "      <td>200000.0</td>\n",
       "      <td>200000.0</td>\n",
       "      <td>1000000.0</td>\n",
       "      <td>GBP</td>\n",
       "      <td>0.0</td>\n",
       "      <td>GBP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>LUID_H9EN8GYB</td>\n",
       "      <td>{}</td>\n",
       "      <td>HSBC</td>\n",
       "      <td>EQUITY_UK</td>\n",
       "      <td>3967-b0e1-f73b-26</td>\n",
       "      <td>P</td>\n",
       "      <td>40000.0</td>\n",
       "      <td>40000.0</td>\n",
       "      <td>240000.0</td>\n",
       "      <td>GBP</td>\n",
       "      <td>0.0</td>\n",
       "      <td>GBP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>CCY_GBP</td>\n",
       "      <td>{}</td>\n",
       "      <td>GBP</td>\n",
       "      <td>EQUITY_UK</td>\n",
       "      <td>3967-b0e1-f73b-26</td>\n",
       "      <td>B</td>\n",
       "      <td>3260000.0</td>\n",
       "      <td>3260000.0</td>\n",
       "      <td>3260000.0</td>\n",
       "      <td>GBP</td>\n",
       "      <td>0.0</td>\n",
       "      <td>GBP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>LUID_OSXJYVK6</td>\n",
       "      <td>{}</td>\n",
       "      <td>Morrisons</td>\n",
       "      <td>EQUITY_UK</td>\n",
       "      <td>3967-b0e1-f73b-26</td>\n",
       "      <td>P</td>\n",
       "      <td>360000.0</td>\n",
       "      <td>360000.0</td>\n",
       "      <td>720000.0</td>\n",
       "      <td>GBP</td>\n",
       "      <td>0.0</td>\n",
       "      <td>GBP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>LUID_6RGYR2L4</td>\n",
       "      <td>{}</td>\n",
       "      <td>Tesco</td>\n",
       "      <td>EQUITY_UK</td>\n",
       "      <td>3967-b0e1-f73b-26</td>\n",
       "      <td>P</td>\n",
       "      <td>12000.0</td>\n",
       "      <td>12000.0</td>\n",
       "      <td>100000.0</td>\n",
       "      <td>GBP</td>\n",
       "      <td>0.0</td>\n",
       "      <td>GBP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>LUID_2IXG0MUX</td>\n",
       "      <td>{}</td>\n",
       "      <td>Rightmove</td>\n",
       "      <td>EQUITY_UK</td>\n",
       "      <td>3967-b0e1-f73b-26</td>\n",
       "      <td>P</td>\n",
       "      <td>160000.0</td>\n",
       "      <td>160000.0</td>\n",
       "      <td>960000.0</td>\n",
       "      <td>GBP</td>\n",
       "      <td>0.0</td>\n",
       "      <td>GBP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>LUID_OSHLMH9J</td>\n",
       "      <td>{}</td>\n",
       "      <td>vodafone</td>\n",
       "      <td>EQUITY_UK</td>\n",
       "      <td>3967-b0e1-f73b-26</td>\n",
       "      <td>P</td>\n",
       "      <td>900000.0</td>\n",
       "      <td>900000.0</td>\n",
       "      <td>900000.0</td>\n",
       "      <td>GBP</td>\n",
       "      <td>0.0</td>\n",
       "      <td>GBP</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  instrument_uid sub_holding_keys Name(default-Properties)  \\\n",
       "0  LUID_YN3FEUD5               {}                    Aviva   \n",
       "1  LUID_7EQ664NC               {}                      BHP   \n",
       "2  LUID_F87JY45S               {}                 Barclays   \n",
       "3  LUID_8X5TTJTL               {}                       BP   \n",
       "4  LUID_H9EN8GYB               {}                     HSBC   \n",
       "5        CCY_GBP               {}                      GBP   \n",
       "6  LUID_OSXJYVK6               {}                Morrisons   \n",
       "7  LUID_6RGYR2L4               {}                    Tesco   \n",
       "8  LUID_2IXG0MUX               {}                Rightmove   \n",
       "9  LUID_OSHLMH9J               {}                 vodafone   \n",
       "\n",
       "  SourcePortfolioId(default-Properties)  \\\n",
       "0                             EQUITY_UK   \n",
       "1                             EQUITY_UK   \n",
       "2                             EQUITY_UK   \n",
       "3                             EQUITY_UK   \n",
       "4                             EQUITY_UK   \n",
       "5                             EQUITY_UK   \n",
       "6                             EQUITY_UK   \n",
       "7                             EQUITY_UK   \n",
       "8                             EQUITY_UK   \n",
       "9                             EQUITY_UK   \n",
       "\n",
       "  SourcePortfolioScope(default-Properties) holding_type      units  \\\n",
       "0                        3967-b0e1-f73b-26            P   132000.0   \n",
       "1                        3967-b0e1-f73b-26            P   120000.0   \n",
       "2                        3967-b0e1-f73b-26            P   300000.0   \n",
       "3                        3967-b0e1-f73b-26            P   200000.0   \n",
       "4                        3967-b0e1-f73b-26            P    40000.0   \n",
       "5                        3967-b0e1-f73b-26            B  3260000.0   \n",
       "6                        3967-b0e1-f73b-26            P   360000.0   \n",
       "7                        3967-b0e1-f73b-26            P    12000.0   \n",
       "8                        3967-b0e1-f73b-26            P   160000.0   \n",
       "9                        3967-b0e1-f73b-26            P   900000.0   \n",
       "\n",
       "   settled_units  cost.amount cost.currency  cost_portfolio_ccy.amount  \\\n",
       "0       132000.0     660000.0           GBP                        0.0   \n",
       "1       120000.0    2160000.0           GBP                        0.0   \n",
       "2       300000.0     600000.0           GBP                        0.0   \n",
       "3       200000.0    1000000.0           GBP                        0.0   \n",
       "4        40000.0     240000.0           GBP                        0.0   \n",
       "5      3260000.0    3260000.0           GBP                        0.0   \n",
       "6       360000.0     720000.0           GBP                        0.0   \n",
       "7        12000.0     100000.0           GBP                        0.0   \n",
       "8       160000.0     960000.0           GBP                        0.0   \n",
       "9       900000.0     900000.0           GBP                        0.0   \n",
       "\n",
       "  cost_portfolio_ccy.currency  \n",
       "0                         GBP  \n",
       "1                         GBP  \n",
       "2                         GBP  \n",
       "3                         GBP  \n",
       "4                         GBP  \n",
       "5                         GBP  \n",
       "6                         GBP  \n",
       "7                         GBP  \n",
       "8                         GBP  \n",
       "9                         GBP  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Convert JSON response to DataFrame\n",
    "\n",
    "lusid_response_to_data_frame(holdings_response, rename_properties=True).head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## GetHoldings for a specific date and time\n",
    "\n",
    "In the code below, we generate holdings again, but this time we provide an effective date of 5 January 20202 at 12:30 PM (UTC). As we can see in the DataFrame below, the portfolio has a different set of holdings on that date.\n",
    "\n",
    "If you pass a date with no time (e.g. <b>2020-01-05</b> then LUSID will set the default time to midnight or <b>00:00:00</b>)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "holdings_response_fifth_jan = txn_port_api.get_holdings(scope=scope,\n",
    "                                     code=portfolio_code,\n",
    "                                     effective_at=\"2020-01-05T12:30:00Z\",\n",
    "                                     property_keys=[\"Instrument/default/Name\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>luid</th>\n",
       "      <th>instrumentName</th>\n",
       "      <th>holdingType</th>\n",
       "      <th>units</th>\n",
       "      <th>settledUnits</th>\n",
       "      <th>costAmount</th>\n",
       "      <th>costCurrency</th>\n",
       "      <th>portfolioCurrency</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>LUID_YN3FEUD5</td>\n",
       "      <td>Aviva</td>\n",
       "      <td>P</td>\n",
       "      <td>132000.0</td>\n",
       "      <td>132000.0</td>\n",
       "      <td>660000.0</td>\n",
       "      <td>GBP</td>\n",
       "      <td>GBP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>LUID_7EQ664NC</td>\n",
       "      <td>BHP</td>\n",
       "      <td>P</td>\n",
       "      <td>120000.0</td>\n",
       "      <td>120000.0</td>\n",
       "      <td>2160000.0</td>\n",
       "      <td>GBP</td>\n",
       "      <td>GBP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>LUID_F87JY45S</td>\n",
       "      <td>Barclays</td>\n",
       "      <td>P</td>\n",
       "      <td>300000.0</td>\n",
       "      <td>300000.0</td>\n",
       "      <td>600000.0</td>\n",
       "      <td>GBP</td>\n",
       "      <td>GBP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>LUID_8X5TTJTL</td>\n",
       "      <td>BP</td>\n",
       "      <td>P</td>\n",
       "      <td>200000.0</td>\n",
       "      <td>200000.0</td>\n",
       "      <td>1000000.0</td>\n",
       "      <td>GBP</td>\n",
       "      <td>GBP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>LUID_H9EN8GYB</td>\n",
       "      <td>HSBC</td>\n",
       "      <td>P</td>\n",
       "      <td>40000.0</td>\n",
       "      <td>40000.0</td>\n",
       "      <td>240000.0</td>\n",
       "      <td>GBP</td>\n",
       "      <td>GBP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>CCY_GBP</td>\n",
       "      <td>GBP</td>\n",
       "      <td>B</td>\n",
       "      <td>7340000.0</td>\n",
       "      <td>7340000.0</td>\n",
       "      <td>7340000.0</td>\n",
       "      <td>GBP</td>\n",
       "      <td>GBP</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            luid instrumentName holdingType      units  settledUnits  \\\n",
       "0  LUID_YN3FEUD5          Aviva           P   132000.0      132000.0   \n",
       "1  LUID_7EQ664NC            BHP           P   120000.0      120000.0   \n",
       "2  LUID_F87JY45S       Barclays           P   300000.0      300000.0   \n",
       "3  LUID_8X5TTJTL             BP           P   200000.0      200000.0   \n",
       "4  LUID_H9EN8GYB           HSBC           P    40000.0       40000.0   \n",
       "5        CCY_GBP            GBP           B  7340000.0     7340000.0   \n",
       "\n",
       "   costAmount costCurrency portfolioCurrency  \n",
       "0    660000.0          GBP               GBP  \n",
       "1   2160000.0          GBP               GBP  \n",
       "2    600000.0          GBP               GBP  \n",
       "3   1000000.0          GBP               GBP  \n",
       "4    240000.0          GBP               GBP  \n",
       "5   7340000.0          GBP               GBP  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Convert JSON response to DataFrame\n",
    "\n",
    "holdings_fifth_jan = lusid_response_to_data_frame(holdings_response_fifth_jan, rename_properties=True)\n",
    "\n",
    "#Format the columns we want to view\n",
    "\n",
    "column_rename = {\n",
    "    \"instrument_uid\": \"luid\",\n",
    "    \"Name(default-Properties)\": \"instrumentName\",\n",
    "    \"holding_type\": \"holdingType\",\n",
    "    \"units\": \"units\",\n",
    "    \"settled_units\": \"settledUnits\",\n",
    "    \"cost.amount\": \"costAmount\",\n",
    "    \"cost.currency\": \"costCurrency\",\n",
    "    \"cost_portfolio_ccy.currency\": \"portfolioCurrency\"\n",
    "}\n",
    "\n",
    "holdings_fifth_jan = holdings_fifth_jan.rename(columns = column_rename)[column_rename.values()].copy()\n",
    "display(holdings_fifth_jan)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## What transactions produced my holdings?\n",
    "\n",
    "The holdings in LUSID's <b>Transaction Portfolios</b> are produced as the result of transactions. You can link a holding back to its source transaction using the build transactions endpoint. Let's do a deep-dive into <i>Tesco</i> which is one of our holdings."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Call the build transactions endpoint\n",
    "\n",
    "build_transactions_response = txn_port_api.build_transactions(scope=scope,\n",
    "                               code=portfolio_code,\n",
    "                                property_keys=[\"Instrument/default/Name\"],\n",
    "                               transaction_query_parameters = models.TransactionQueryParameters(start_date=\"2020-01-01\",\n",
    "                                                                                   end_date=\"2020-12-31\"))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "By calling the build transactions endpoint, we can see that two trades produce our holding of 12,000 units:\n",
    "\n",
    "* One transaction on the 17 January 2020 for 8,000 units\n",
    "* Then a second transaction on the 18 January 2020 for 4,000 units\n",
    "\n",
    "The <b>ResultantHolding</b> column tracks the total holding in the portfolio for each unique instrument as of the transaction effective date and time. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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>TransactionId</th>\n",
       "      <th>TransactionType</th>\n",
       "      <th>LusidInstrumentId</th>\n",
       "      <th>InstrumentName</th>\n",
       "      <th>SettlementDate</th>\n",
       "      <th>Price</th>\n",
       "      <th>Units</th>\n",
       "      <th>ResultantHolding</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>trd_0001</td>\n",
       "      <td>Buy</td>\n",
       "      <td>LUID_YN3FEUD5</td>\n",
       "      <td>Aviva</td>\n",
       "      <td>2020-01-04 00:00:00+00:00</td>\n",
       "      <td>5.0</td>\n",
       "      <td>120000.0</td>\n",
       "      <td>120000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>trd_0002</td>\n",
       "      <td>Buy</td>\n",
       "      <td>LUID_YN3FEUD5</td>\n",
       "      <td>Aviva</td>\n",
       "      <td>2020-01-04 00:00:00+00:00</td>\n",
       "      <td>5.0</td>\n",
       "      <td>12000.0</td>\n",
       "      <td>132000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>trd_0003</td>\n",
       "      <td>Buy</td>\n",
       "      <td>LUID_7EQ664NC</td>\n",
       "      <td>BHP</td>\n",
       "      <td>2020-01-04 00:00:00+00:00</td>\n",
       "      <td>18.0</td>\n",
       "      <td>60000.0</td>\n",
       "      <td>60000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>trd_0004</td>\n",
       "      <td>Buy</td>\n",
       "      <td>LUID_7EQ664NC</td>\n",
       "      <td>BHP</td>\n",
       "      <td>2020-01-04 00:00:00+00:00</td>\n",
       "      <td>18.0</td>\n",
       "      <td>60000.0</td>\n",
       "      <td>120000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>trd_0005</td>\n",
       "      <td>Buy</td>\n",
       "      <td>LUID_F87JY45S</td>\n",
       "      <td>Barclays</td>\n",
       "      <td>2020-01-04 00:00:00+00:00</td>\n",
       "      <td>2.0</td>\n",
       "      <td>150000.0</td>\n",
       "      <td>150000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>trd_0006</td>\n",
       "      <td>Buy</td>\n",
       "      <td>LUID_F87JY45S</td>\n",
       "      <td>Barclays</td>\n",
       "      <td>2020-01-04 00:00:00+00:00</td>\n",
       "      <td>2.0</td>\n",
       "      <td>150000.0</td>\n",
       "      <td>300000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>trd_0007</td>\n",
       "      <td>Buy</td>\n",
       "      <td>LUID_8X5TTJTL</td>\n",
       "      <td>BP</td>\n",
       "      <td>2020-01-04 00:00:00+00:00</td>\n",
       "      <td>5.0</td>\n",
       "      <td>100000.0</td>\n",
       "      <td>100000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>trd_0008</td>\n",
       "      <td>Buy</td>\n",
       "      <td>LUID_8X5TTJTL</td>\n",
       "      <td>BP</td>\n",
       "      <td>2020-01-04 00:00:00+00:00</td>\n",
       "      <td>5.0</td>\n",
       "      <td>100000.0</td>\n",
       "      <td>200000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>trd_0009</td>\n",
       "      <td>Buy</td>\n",
       "      <td>LUID_H9EN8GYB</td>\n",
       "      <td>HSBC</td>\n",
       "      <td>2020-01-04 00:00:00+00:00</td>\n",
       "      <td>6.0</td>\n",
       "      <td>20000.0</td>\n",
       "      <td>20000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>trd_0010</td>\n",
       "      <td>Buy</td>\n",
       "      <td>LUID_H9EN8GYB</td>\n",
       "      <td>HSBC</td>\n",
       "      <td>2020-01-04 00:00:00+00:00</td>\n",
       "      <td>6.0</td>\n",
       "      <td>20000.0</td>\n",
       "      <td>40000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>trd_0021</td>\n",
       "      <td>FundsIn</td>\n",
       "      <td>CCY_GBP</td>\n",
       "      <td>GBP</td>\n",
       "      <td>2020-01-04 00:00:00+00:00</td>\n",
       "      <td>1.0</td>\n",
       "      <td>12000000.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>trd_0011</td>\n",
       "      <td>Buy</td>\n",
       "      <td>LUID_OSXJYVK6</td>\n",
       "      <td>Morrisons</td>\n",
       "      <td>2020-01-17 00:00:00+00:00</td>\n",
       "      <td>2.0</td>\n",
       "      <td>180000.0</td>\n",
       "      <td>180000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>trd_0012</td>\n",
       "      <td>Buy</td>\n",
       "      <td>LUID_OSXJYVK6</td>\n",
       "      <td>Morrisons</td>\n",
       "      <td>2020-01-17 00:00:00+00:00</td>\n",
       "      <td>2.0</td>\n",
       "      <td>180000.0</td>\n",
       "      <td>360000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>trd_0022</td>\n",
       "      <td>Buy</td>\n",
       "      <td>LUID_6RGYR2L4</td>\n",
       "      <td>Tesco</td>\n",
       "      <td>2020-01-17 00:00:00+00:00</td>\n",
       "      <td>8.0</td>\n",
       "      <td>8000.0</td>\n",
       "      <td>8000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>trd_0015</td>\n",
       "      <td>Buy</td>\n",
       "      <td>LUID_2IXG0MUX</td>\n",
       "      <td>Rightmove</td>\n",
       "      <td>2020-01-17 00:00:00+00:00</td>\n",
       "      <td>6.0</td>\n",
       "      <td>80000.0</td>\n",
       "      <td>80000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>trd_0016</td>\n",
       "      <td>Buy</td>\n",
       "      <td>LUID_2IXG0MUX</td>\n",
       "      <td>Rightmove</td>\n",
       "      <td>2020-01-17 00:00:00+00:00</td>\n",
       "      <td>6.0</td>\n",
       "      <td>80000.0</td>\n",
       "      <td>160000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>trd_0017</td>\n",
       "      <td>Buy</td>\n",
       "      <td>LUID_OSHLMH9J</td>\n",
       "      <td>vodafone</td>\n",
       "      <td>2020-01-17 00:00:00+00:00</td>\n",
       "      <td>1.0</td>\n",
       "      <td>450000.0</td>\n",
       "      <td>450000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>trd_0018</td>\n",
       "      <td>Buy</td>\n",
       "      <td>LUID_OSHLMH9J</td>\n",
       "      <td>vodafone</td>\n",
       "      <td>2020-01-17 00:00:00+00:00</td>\n",
       "      <td>1.0</td>\n",
       "      <td>450000.0</td>\n",
       "      <td>900000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>trd_0019</td>\n",
       "      <td>Buy</td>\n",
       "      <td>LUID_89QAQSYC</td>\n",
       "      <td>Anglo American plc</td>\n",
       "      <td>2020-01-17 00:00:00+00:00</td>\n",
       "      <td>20.0</td>\n",
       "      <td>35000.0</td>\n",
       "      <td>35000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>trd_0020</td>\n",
       "      <td>Buy</td>\n",
       "      <td>LUID_89QAQSYC</td>\n",
       "      <td>Anglo American plc</td>\n",
       "      <td>2020-01-17 00:00:00+00:00</td>\n",
       "      <td>20.0</td>\n",
       "      <td>35000.0</td>\n",
       "      <td>70000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>trd_0014</td>\n",
       "      <td>Buy</td>\n",
       "      <td>LUID_6RGYR2L4</td>\n",
       "      <td>Tesco</td>\n",
       "      <td>2020-01-18 00:00:00+00:00</td>\n",
       "      <td>9.0</td>\n",
       "      <td>4000.0</td>\n",
       "      <td>12000.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   TransactionId TransactionType LusidInstrumentId      InstrumentName  \\\n",
       "0       trd_0001             Buy     LUID_YN3FEUD5               Aviva   \n",
       "1       trd_0002             Buy     LUID_YN3FEUD5               Aviva   \n",
       "2       trd_0003             Buy     LUID_7EQ664NC                 BHP   \n",
       "3       trd_0004             Buy     LUID_7EQ664NC                 BHP   \n",
       "4       trd_0005             Buy     LUID_F87JY45S            Barclays   \n",
       "5       trd_0006             Buy     LUID_F87JY45S            Barclays   \n",
       "6       trd_0007             Buy     LUID_8X5TTJTL                  BP   \n",
       "7       trd_0008             Buy     LUID_8X5TTJTL                  BP   \n",
       "8       trd_0009             Buy     LUID_H9EN8GYB                HSBC   \n",
       "9       trd_0010             Buy     LUID_H9EN8GYB                HSBC   \n",
       "10      trd_0021         FundsIn           CCY_GBP                 GBP   \n",
       "11      trd_0011             Buy     LUID_OSXJYVK6           Morrisons   \n",
       "12      trd_0012             Buy     LUID_OSXJYVK6           Morrisons   \n",
       "13      trd_0022             Buy     LUID_6RGYR2L4               Tesco   \n",
       "14      trd_0015             Buy     LUID_2IXG0MUX           Rightmove   \n",
       "15      trd_0016             Buy     LUID_2IXG0MUX           Rightmove   \n",
       "16      trd_0017             Buy     LUID_OSHLMH9J            vodafone   \n",
       "17      trd_0018             Buy     LUID_OSHLMH9J            vodafone   \n",
       "18      trd_0019             Buy     LUID_89QAQSYC  Anglo American plc   \n",
       "19      trd_0020             Buy     LUID_89QAQSYC  Anglo American plc   \n",
       "20      trd_0014             Buy     LUID_6RGYR2L4               Tesco   \n",
       "\n",
       "              SettlementDate  Price       Units  ResultantHolding  \n",
       "0  2020-01-04 00:00:00+00:00    5.0    120000.0          120000.0  \n",
       "1  2020-01-04 00:00:00+00:00    5.0     12000.0          132000.0  \n",
       "2  2020-01-04 00:00:00+00:00   18.0     60000.0           60000.0  \n",
       "3  2020-01-04 00:00:00+00:00   18.0     60000.0          120000.0  \n",
       "4  2020-01-04 00:00:00+00:00    2.0    150000.0          150000.0  \n",
       "5  2020-01-04 00:00:00+00:00    2.0    150000.0          300000.0  \n",
       "6  2020-01-04 00:00:00+00:00    5.0    100000.0          100000.0  \n",
       "7  2020-01-04 00:00:00+00:00    5.0    100000.0          200000.0  \n",
       "8  2020-01-04 00:00:00+00:00    6.0     20000.0           20000.0  \n",
       "9  2020-01-04 00:00:00+00:00    6.0     20000.0           40000.0  \n",
       "10 2020-01-04 00:00:00+00:00    1.0  12000000.0               NaN  \n",
       "11 2020-01-17 00:00:00+00:00    2.0    180000.0          180000.0  \n",
       "12 2020-01-17 00:00:00+00:00    2.0    180000.0          360000.0  \n",
       "13 2020-01-17 00:00:00+00:00    8.0      8000.0            8000.0  \n",
       "14 2020-01-17 00:00:00+00:00    6.0     80000.0           80000.0  \n",
       "15 2020-01-17 00:00:00+00:00    6.0     80000.0          160000.0  \n",
       "16 2020-01-17 00:00:00+00:00    1.0    450000.0          450000.0  \n",
       "17 2020-01-17 00:00:00+00:00    1.0    450000.0          900000.0  \n",
       "18 2020-01-17 00:00:00+00:00   20.0     35000.0           35000.0  \n",
       "19 2020-01-17 00:00:00+00:00   20.0     35000.0           70000.0  \n",
       "20 2020-01-18 00:00:00+00:00    9.0      4000.0           12000.0  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Filter for a list of columns we want to show in the notebook\n",
    "\n",
    "columns = [\"TransactionId\",\n",
    "\"TransactionType\",\n",
    "\"LusidInstrumentId\",\n",
    "\"InstrumentName\",\n",
    "\"SettlementDate\",\n",
    "\"Price\",\n",
    "\"Units\",\n",
    "\"ResultantHolding\"]\n",
    "\n",
    "# Target the rows for Tesco only\n",
    "\n",
    "transactions_df = lusid_response_to_data_frame(build_transactions_response, column_name_mapping=build_txn_json_mappings, rename_properties=True)\n",
    "transactions_df = transactions_df[columns]\n",
    "transactions_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exporting holdings into an Excel file\n",
    "\n",
    "You can then use standard panda's functions to export the holdings into an Excel file (or various other formats required by downstream systems or users)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "holdings_fifth_jan.to_excel(\"data/extracts/holdings_20200105.xlsx\")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}