V1/product/ibor/Load Transactions from an External System.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "\"\"\"Loading transactions from an external system\n",
    "\n",
    "Demonstration of loading a transaction XML file from another \"External System\" into LUSID.\n",
    "\n",
    "Attributes\n",
    "----------\n",
    "cocoon\n",
    "transactions\n",
    "holdings\n",
    "\"\"\""
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Loading an \"External System\" Transaction XML into LUSID\n",
    "\n",
    "The purpose of this notebook  is to demonstrate how a user could load a transaction XML file from another \"External System\" into LUSID. The notebook will walk through the following steps: <br>\n",
    "\n",
    "(1) Load transaction XML into DataFrame. <br>\n",
    "(2) Scan for portfolio code in DataFrame. If portfolio code from DataFrame does not exist in LUSID, create the portfolio in LUSID. <br>\n",
    "(3) Translate the transaction data in DataFrame into data types which LUSID will recognise (e.g. transform \"strings\" to \"datetimes\"). <br>\n",
    "(4) Use client identifiers to create placeholder LUIDS (these can be enriched at a later date once we receive the \"External System\" instrument master file). <br>\n",
    "(5) Load the transaction into LUSID using the Transactions API. <br><br>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Setup: Load libraries and create our LUSID API client\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "LUSID Client Initialised\n",
      "LUSID version :  0.5.3388.0\n"
     ]
    }
   ],
   "source": [
    "# Import LUSID\n",
    "import lusid\n",
    "import lusid.models as models\n",
    "import lusid_sample_data as import_data\n",
    "import globalfund as global_fund_tools\n",
    "import lusidtools.cocoon.cocoon as cocoon_tools\n",
    "from lusidjam import RefreshingToken\n",
    "\n",
    "# Import Libraries\n",
    "import pprint\n",
    "from datetime import datetime, timedelta, time, date\n",
    "import pytz\n",
    "import uuid\n",
    "import printer as prettyprint\n",
    "from datetime import datetime\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import os\n",
    "import json\n",
    "import xml.etree.ElementTree as ET\n",
    "import io\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",
    "\n",
    "print('LUSID Client Initialised')\n",
    "print('LUSID version : ', api_factory.build(lusid.api.ApplicationMetadataApi).get_lusid_versions().build_version)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As part of the setup, we need to define a Scope. Scopes in LUSID are used to partition data. In this example, we will use the Scope \"EXT_SYSTEM\" to represent an external trading system. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "scope = \"EXT_SYSTEM\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Step 1: Scrape Transaction XML data into a Python DataFrame"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "First we need to load the XML file into a Python DataFrame. This will enable us to work with the data, then pass it to the relevant APIs."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load the XML file into memory\n",
    "\n",
    "ext_system_file = os.path.join(\"data\", \"ext_system_file.xml\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "TRD_TRADER\n",
      "INST_TICKER\n",
      "INST_DESC\n",
      "PORT_NAME\n",
      "CLIENT_ID\n",
      "TRD_CURRENCY\n",
      "TRD_PRINCIPAL\n",
      "TRANSACTIONS\n",
      "TRD_TRADE_DATE\n",
      "TRD_TRANS_TYPE\n",
      "TRD_COUNTERPARTY\n",
      "TRD_PRICE\n",
      "TRD_TOUCH_COUNT\n",
      "TRD_QUANTITY\n",
      "TRD_NUMBER\n",
      "TRD_LOCATION\n",
      "INST_GROUP\n",
      "TRADE\n",
      "INST_TYPE\n",
      "TRD_SETTLE_DATE\n"
     ]
    }
   ],
   "source": [
    "# Create a Python object from the XML file\n",
    "# List out the individual tags which will need to be mapped\n",
    "\n",
    "tree = ET.parse(ext_system_file)\n",
    "root = tree.getroot()\n",
    "list_tags = list(set([elem.tag for elem in root.iter()]))\n",
    "for tag in list_tags:\n",
    "    print(tag)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Create a list of the tags which we want to load into LUSID. For the purposes of this example, we will select a subset of fields from the sample XML file. In production, you may need to load more."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "list_tags = [\n",
    "    \n",
    "    \"TRD_TRANS_TYPE\",\n",
    "    \"TRD_QUANTITY\",\n",
    "    \"TRD_TRADE_DATE\",\n",
    "    \"PORT_NAME\",\n",
    "    \"INST_GROUP\",\n",
    "    \"TRD_CURRENCY\",\n",
    "    \"TRD_TOUCH_COUNT\",\n",
    "    \"INST_DESC\",\n",
    "    \"INST_TYPE\",\n",
    "    \"TRD_PRINCIPAL\",\n",
    "    \"TRD_TRADER\",\n",
    "    \"TRD_PRICE\",\n",
    "    \"TRD_COUNTERPARTY\",\n",
    "    \"INST_TICKER\",\n",
    "    \"CLIENT_ID\",\n",
    "    \"TRD_SETTLE_DATE\",\n",
    "    \"TRD_LOCATION\",\n",
    "    \"TRD_NUMBER\"\n",
    "    \n",
    "]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Iterate over the \"External System\" transaction XML and scrape the results into a Pandas DataFrame. We scrape the results into a DataFrame for two reasons:<br>\n",
    "\n",
    "(1) We can use the DataFrame methods to transform the data into a format LUSID likes. <br>\n",
    "(2) We can then use the SDKs to pass the DataFrame directly to LUSID<br>\n",
    "<br>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Define an empty list which will be used to create a DataFrame from XML results \n",
    "rows= []\n",
    "\n",
    "# Iterate over the XML to find transactions in the sub-XML nested under the \"TRADE\" tag.\n",
    "# Store each individual transaction as a dictionary.\n",
    "# Then add each dictionary (representing a transaction) to a list.\n",
    "# Use this list of dictionaries to create a DataFrame.\n",
    "\n",
    "for trade in root.iter('TRADE'):\n",
    "    \n",
    "    trade_list = []\n",
    "    for tag in list_tags:\n",
    "        \n",
    "        try:       \n",
    "            trade_list.append((trade.find(tag).text))\n",
    "        except AttributeError:\n",
    "            trade_list.append(None)\n",
    "\n",
    "    rows.append({\"TRD_TRANS_TYPE\": trade_list[0], \n",
    "                 \"TRD_QUANTITY\": trade_list[1], \n",
    "                 \"TRD_TRADE_DATE\": trade_list[2],\n",
    "                 \"PORT_NAME\": trade_list[3],\n",
    "                 \"INST_GROUP\": trade_list[4],\n",
    "                 \"TRD_CURRENCY\": trade_list[5],\n",
    "                 \"TRD_TOUCH_COUNT\": trade_list[6],\n",
    "                 \"INST_DESC\": trade_list[7],\n",
    "                 \"INST_TYPE\": trade_list[8],\n",
    "                 \"TRD_PRINCIPAL\": trade_list[9],\n",
    "                 \"TRD_TRADER\": trade_list[10],\n",
    "                 \"TRD_PRICE\": trade_list[11],\n",
    "                 \"TRD_COUNTERPARTY\": trade_list[12],\n",
    "                 \"INST_TICKER\": trade_list[13],\n",
    "                 \"CLIENT_ID\": trade_list[14],\n",
    "                 \"TRD_SETTLE_DATE\": trade_list[15],\n",
    "                 \"TRD_LOCATION\": trade_list[16],\n",
    "                 \"TRD_NUMBER\": trade_list[17]\n",
    "                \n",
    "                })\n",
    "    \n",
    "ext_system_df = pd.DataFrame(rows)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's take a look at the DataFrame. The results look good. We see trade details - broker, security type, price etc. "
   ]
  },
  {
   "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>CLIENT_ID</th>\n",
       "      <th>INST_DESC</th>\n",
       "      <th>INST_GROUP</th>\n",
       "      <th>INST_TICKER</th>\n",
       "      <th>INST_TYPE</th>\n",
       "      <th>PORT_NAME</th>\n",
       "      <th>TRD_COUNTERPARTY</th>\n",
       "      <th>TRD_CURRENCY</th>\n",
       "      <th>TRD_LOCATION</th>\n",
       "      <th>TRD_NUMBER</th>\n",
       "      <th>TRD_PRICE</th>\n",
       "      <th>TRD_PRINCIPAL</th>\n",
       "      <th>TRD_QUANTITY</th>\n",
       "      <th>TRD_SETTLE_DATE</th>\n",
       "      <th>TRD_TOUCH_COUNT</th>\n",
       "      <th>TRD_TRADER</th>\n",
       "      <th>TRD_TRADE_DATE</th>\n",
       "      <th>TRD_TRANS_TYPE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>EXT1YU6H5</td>\n",
       "      <td>INTERNATIONAL BUSINESS MACHINES CORP</td>\n",
       "      <td>EQUITY</td>\n",
       "      <td>IBM</td>\n",
       "      <td>COMMON_STOCK</td>\n",
       "      <td>TEST_PORT1</td>\n",
       "      <td>GS</td>\n",
       "      <td>USD</td>\n",
       "      <td>LONDON</td>\n",
       "      <td>0011223</td>\n",
       "      <td>142.99</td>\n",
       "      <td>7864450.000</td>\n",
       "      <td>55000.0000000000</td>\n",
       "      <td>8/20/2018</td>\n",
       "      <td>2</td>\n",
       "      <td>TEST_TRADER</td>\n",
       "      <td>8/16/2018</td>\n",
       "      <td>BUY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>EXT1YU6H6</td>\n",
       "      <td>BANK OF AMERICA CORP</td>\n",
       "      <td>EQUITY</td>\n",
       "      <td>BAC</td>\n",
       "      <td>COMMON_STOCK</td>\n",
       "      <td>TEST_PORT1</td>\n",
       "      <td>JPM</td>\n",
       "      <td>USD</td>\n",
       "      <td>LONDON</td>\n",
       "      <td>0011224</td>\n",
       "      <td>28.99</td>\n",
       "      <td>2899000.000</td>\n",
       "      <td>100000.0000000000</td>\n",
       "      <td>9/20/2018</td>\n",
       "      <td>1</td>\n",
       "      <td>TEST_TRADER</td>\n",
       "      <td>9/16/2018</td>\n",
       "      <td>BUY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>EXT1YU6H7</td>\n",
       "      <td>WALMART INC</td>\n",
       "      <td>EQUITY</td>\n",
       "      <td>WMT</td>\n",
       "      <td>COMMON_STOCK</td>\n",
       "      <td>TEST_PORT2</td>\n",
       "      <td>JPM</td>\n",
       "      <td>USD</td>\n",
       "      <td>LONDON</td>\n",
       "      <td>0011225</td>\n",
       "      <td>116.99</td>\n",
       "      <td>11699000</td>\n",
       "      <td>100000.0000000000</td>\n",
       "      <td>10/20/2018</td>\n",
       "      <td>1</td>\n",
       "      <td>TEST_TRADER</td>\n",
       "      <td>10/16/2018</td>\n",
       "      <td>SELL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>EXT1YU6H8</td>\n",
       "      <td>3M CO</td>\n",
       "      <td>EQUITY</td>\n",
       "      <td>MMM</td>\n",
       "      <td>COMMON_STOCK</td>\n",
       "      <td>TEST_PORT2</td>\n",
       "      <td>MSUSA</td>\n",
       "      <td>USD</td>\n",
       "      <td>LONDON</td>\n",
       "      <td>0011226</td>\n",
       "      <td>155.99</td>\n",
       "      <td>11599000</td>\n",
       "      <td>100000.0000000000</td>\n",
       "      <td>5/20/2018</td>\n",
       "      <td>6</td>\n",
       "      <td>TEST_TRADER</td>\n",
       "      <td>5/16/2018</td>\n",
       "      <td>BUY</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   CLIENT_ID                             INST_DESC INST_GROUP INST_TICKER  \\\n",
       "0  EXT1YU6H5  INTERNATIONAL BUSINESS MACHINES CORP     EQUITY         IBM   \n",
       "1  EXT1YU6H6                  BANK OF AMERICA CORP     EQUITY         BAC   \n",
       "2  EXT1YU6H7                           WALMART INC     EQUITY         WMT   \n",
       "3  EXT1YU6H8                                 3M CO     EQUITY         MMM   \n",
       "\n",
       "      INST_TYPE   PORT_NAME TRD_COUNTERPARTY TRD_CURRENCY TRD_LOCATION  \\\n",
       "0  COMMON_STOCK  TEST_PORT1               GS          USD       LONDON   \n",
       "1  COMMON_STOCK  TEST_PORT1              JPM          USD       LONDON   \n",
       "2  COMMON_STOCK  TEST_PORT2              JPM          USD       LONDON   \n",
       "3  COMMON_STOCK  TEST_PORT2            MSUSA          USD       LONDON   \n",
       "\n",
       "  TRD_NUMBER TRD_PRICE TRD_PRINCIPAL       TRD_QUANTITY TRD_SETTLE_DATE  \\\n",
       "0    0011223    142.99   7864450.000   55000.0000000000       8/20/2018   \n",
       "1    0011224     28.99   2899000.000  100000.0000000000       9/20/2018   \n",
       "2    0011225    116.99      11699000  100000.0000000000      10/20/2018   \n",
       "3    0011226    155.99      11599000  100000.0000000000       5/20/2018   \n",
       "\n",
       "  TRD_TOUCH_COUNT   TRD_TRADER TRD_TRADE_DATE TRD_TRANS_TYPE  \n",
       "0               2  TEST_TRADER      8/16/2018            BUY  \n",
       "1               1  TEST_TRADER      9/16/2018            BUY  \n",
       "2               1  TEST_TRADER     10/16/2018           SELL  \n",
       "3               6  TEST_TRADER      5/16/2018            BUY  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ext_system_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Step 2: Create portfolios in LUSID\n",
    "\n",
    "Next we need to create portfolios in LUSID. The \"External System\" transaction XMLs will be loaded into these portfolio."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a list of the unique portfolios from the transaction XML file\n",
    "\n",
    "portfolios = list(set(ext_system_df[\"PORT_NAME\"].tolist()))"
   ]
  },
  {
   "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>FundCode</th>\n",
       "      <th>base_currency</th>\n",
       "      <th>created</th>\n",
       "      <th>description</th>\n",
       "      <th>display_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>TEST_PORT2</td>\n",
       "      <td>USD</td>\n",
       "      <td>2016-10-09</td>\n",
       "      <td>A portfolio to hold trades from an external sy...</td>\n",
       "      <td>External System Positions TEST_PORT2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>TEST_PORT1</td>\n",
       "      <td>USD</td>\n",
       "      <td>2016-10-09</td>\n",
       "      <td>A portfolio to hold trades from an external sy...</td>\n",
       "      <td>External System Positions TEST_PORT1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     FundCode base_currency     created  \\\n",
       "0  TEST_PORT2           USD  2016-10-09   \n",
       "1  TEST_PORT1           USD  2016-10-09   \n",
       "\n",
       "                                         description  \\\n",
       "0  A portfolio to hold trades from an external sy...   \n",
       "1  A portfolio to hold trades from an external sy...   \n",
       "\n",
       "                           display_name  \n",
       "0  External System Positions TEST_PORT2  \n",
       "1  External System Positions TEST_PORT1  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Prepare a DataFrame of portfolios\n",
    "\n",
    "rows = []\n",
    "for port in portfolios:\n",
    "    \n",
    "    rows.append(\n",
    "        \n",
    "        {'FundCode': port,\n",
    "         'display_name': 'External System Positions {}'.format(port),\n",
    "         'created': '2016-10-09',\n",
    "         'base_currency': 'USD',\n",
    "        'description': 'A portfolio to hold trades from an external system - {}'.format(port)}\n",
    "        \n",
    "    )\n",
    "    \n",
    "\n",
    "portfolios_data = pd.DataFrame(rows)\n",
    "portfolios_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "portfolio_mapping_required = {\n",
    "  'code': 'FundCode',\n",
    "  'display_name': 'display_name',\n",
    "  'base_currency': 'base_currency'\n",
    "}\n",
    "\n",
    "portfolio_mapping_optional = {\n",
    "  'created': 'created',\n",
    "  'description': 'description',\n",
    "  'accounting_method': None\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create the portfolios if they do not already exist in LUSID\n",
    "\n",
    "responses = cocoon_tools.load_from_data_frame(\n",
    "    api_factory=api_factory, \n",
    "    scope=scope, \n",
    "    data_frame=portfolios_data, \n",
    "    mapping_required=portfolio_mapping_required, \n",
    "    mapping_optional=portfolio_mapping_optional,\n",
    "    file_type='portfolio')\n",
    "\n",
    "for response in responses[\"portfolios\"][\"success\"]:\n",
    "    prettyprint.portfolio_response(response)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Step 3: Translate the data into a LUSID-friendly format\n",
    "\n",
    "The script above loads \"Trade Date\" and \"Settle Date\" as strings. LUSID needs these as datetimes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "ext_system_df['TRD_TRADE_DATE'] = pd.to_datetime(\n",
    "    ext_system_df['TRD_TRADE_DATE'], format=\"%m/%d/%Y\").apply(lambda x: pytz.utc.localize(x))\n",
    "\n",
    "ext_system_df['TRD_SETTLE_DATE'] = pd.to_datetime(\n",
    "    ext_system_df['TRD_SETTLE_DATE'], format=\"%m/%d/%Y\").apply(lambda x: pytz.utc.localize(x))"
   ]
  },
  {
   "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>CLIENT_ID</th>\n",
       "      <th>INST_DESC</th>\n",
       "      <th>INST_GROUP</th>\n",
       "      <th>INST_TICKER</th>\n",
       "      <th>INST_TYPE</th>\n",
       "      <th>PORT_NAME</th>\n",
       "      <th>TRD_COUNTERPARTY</th>\n",
       "      <th>TRD_CURRENCY</th>\n",
       "      <th>TRD_LOCATION</th>\n",
       "      <th>TRD_NUMBER</th>\n",
       "      <th>TRD_PRICE</th>\n",
       "      <th>TRD_PRINCIPAL</th>\n",
       "      <th>TRD_QUANTITY</th>\n",
       "      <th>TRD_SETTLE_DATE</th>\n",
       "      <th>TRD_TOUCH_COUNT</th>\n",
       "      <th>TRD_TRADER</th>\n",
       "      <th>TRD_TRADE_DATE</th>\n",
       "      <th>TRD_TRANS_TYPE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>EXT1YU6H5</td>\n",
       "      <td>INTERNATIONAL BUSINESS MACHINES CORP</td>\n",
       "      <td>EQUITY</td>\n",
       "      <td>IBM</td>\n",
       "      <td>COMMON_STOCK</td>\n",
       "      <td>TEST_PORT1</td>\n",
       "      <td>GS</td>\n",
       "      <td>USD</td>\n",
       "      <td>LONDON</td>\n",
       "      <td>0011223</td>\n",
       "      <td>142.99</td>\n",
       "      <td>7864450.000</td>\n",
       "      <td>55000.0000000000</td>\n",
       "      <td>2018-08-20 00:00:00+00:00</td>\n",
       "      <td>2</td>\n",
       "      <td>TEST_TRADER</td>\n",
       "      <td>2018-08-16 00:00:00+00:00</td>\n",
       "      <td>BUY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>EXT1YU6H6</td>\n",
       "      <td>BANK OF AMERICA CORP</td>\n",
       "      <td>EQUITY</td>\n",
       "      <td>BAC</td>\n",
       "      <td>COMMON_STOCK</td>\n",
       "      <td>TEST_PORT1</td>\n",
       "      <td>JPM</td>\n",
       "      <td>USD</td>\n",
       "      <td>LONDON</td>\n",
       "      <td>0011224</td>\n",
       "      <td>28.99</td>\n",
       "      <td>2899000.000</td>\n",
       "      <td>100000.0000000000</td>\n",
       "      <td>2018-09-20 00:00:00+00:00</td>\n",
       "      <td>1</td>\n",
       "      <td>TEST_TRADER</td>\n",
       "      <td>2018-09-16 00:00:00+00:00</td>\n",
       "      <td>BUY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>EXT1YU6H7</td>\n",
       "      <td>WALMART INC</td>\n",
       "      <td>EQUITY</td>\n",
       "      <td>WMT</td>\n",
       "      <td>COMMON_STOCK</td>\n",
       "      <td>TEST_PORT2</td>\n",
       "      <td>JPM</td>\n",
       "      <td>USD</td>\n",
       "      <td>LONDON</td>\n",
       "      <td>0011225</td>\n",
       "      <td>116.99</td>\n",
       "      <td>11699000</td>\n",
       "      <td>100000.0000000000</td>\n",
       "      <td>2018-10-20 00:00:00+00:00</td>\n",
       "      <td>1</td>\n",
       "      <td>TEST_TRADER</td>\n",
       "      <td>2018-10-16 00:00:00+00:00</td>\n",
       "      <td>SELL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>EXT1YU6H8</td>\n",
       "      <td>3M CO</td>\n",
       "      <td>EQUITY</td>\n",
       "      <td>MMM</td>\n",
       "      <td>COMMON_STOCK</td>\n",
       "      <td>TEST_PORT2</td>\n",
       "      <td>MSUSA</td>\n",
       "      <td>USD</td>\n",
       "      <td>LONDON</td>\n",
       "      <td>0011226</td>\n",
       "      <td>155.99</td>\n",
       "      <td>11599000</td>\n",
       "      <td>100000.0000000000</td>\n",
       "      <td>2018-05-20 00:00:00+00:00</td>\n",
       "      <td>6</td>\n",
       "      <td>TEST_TRADER</td>\n",
       "      <td>2018-05-16 00:00:00+00:00</td>\n",
       "      <td>BUY</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   CLIENT_ID                             INST_DESC INST_GROUP INST_TICKER  \\\n",
       "0  EXT1YU6H5  INTERNATIONAL BUSINESS MACHINES CORP     EQUITY         IBM   \n",
       "1  EXT1YU6H6                  BANK OF AMERICA CORP     EQUITY         BAC   \n",
       "2  EXT1YU6H7                           WALMART INC     EQUITY         WMT   \n",
       "3  EXT1YU6H8                                 3M CO     EQUITY         MMM   \n",
       "\n",
       "      INST_TYPE   PORT_NAME TRD_COUNTERPARTY TRD_CURRENCY TRD_LOCATION  \\\n",
       "0  COMMON_STOCK  TEST_PORT1               GS          USD       LONDON   \n",
       "1  COMMON_STOCK  TEST_PORT1              JPM          USD       LONDON   \n",
       "2  COMMON_STOCK  TEST_PORT2              JPM          USD       LONDON   \n",
       "3  COMMON_STOCK  TEST_PORT2            MSUSA          USD       LONDON   \n",
       "\n",
       "  TRD_NUMBER TRD_PRICE TRD_PRINCIPAL       TRD_QUANTITY  \\\n",
       "0    0011223    142.99   7864450.000   55000.0000000000   \n",
       "1    0011224     28.99   2899000.000  100000.0000000000   \n",
       "2    0011225    116.99      11699000  100000.0000000000   \n",
       "3    0011226    155.99      11599000  100000.0000000000   \n",
       "\n",
       "            TRD_SETTLE_DATE TRD_TOUCH_COUNT   TRD_TRADER  \\\n",
       "0 2018-08-20 00:00:00+00:00               2  TEST_TRADER   \n",
       "1 2018-09-20 00:00:00+00:00               1  TEST_TRADER   \n",
       "2 2018-10-20 00:00:00+00:00               1  TEST_TRADER   \n",
       "3 2018-05-20 00:00:00+00:00               6  TEST_TRADER   \n",
       "\n",
       "             TRD_TRADE_DATE TRD_TRANS_TYPE  \n",
       "0 2018-08-16 00:00:00+00:00            BUY  \n",
       "1 2018-09-16 00:00:00+00:00            BUY  \n",
       "2 2018-10-16 00:00:00+00:00           SELL  \n",
       "3 2018-05-16 00:00:00+00:00            BUY  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ext_system_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Step 4: Create placeholder LUIDs from \"External System\" client IDs\n",
    "\n",
    "In an ideal world, we would setup our instrument master *before* upserting transactions. Unfortunately, that is not always possible. In this example, the external system or party has sent us the transaction file before an instrument master file. Therefore, as a workaround, we will map the unique CLIENT_IDs to a placeholder LUSID Unique Identifer (called a LUID). These LUIDs can be updated with more data (Public IDs, Sectors, Ratings etc) when the client \"Instrument Master\" file is sent to us later in the day.<br>"
   ]
  },
  {
   "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>CLIENT_ID</th>\n",
       "      <th>INST_DESC</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>EXT1YU6H5</td>\n",
       "      <td>INTERNATIONAL BUSINESS MACHINES CORP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>EXT1YU6H6</td>\n",
       "      <td>BANK OF AMERICA CORP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>EXT1YU6H7</td>\n",
       "      <td>WALMART INC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>EXT1YU6H8</td>\n",
       "      <td>3M CO</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   CLIENT_ID                             INST_DESC\n",
       "0  EXT1YU6H5  INTERNATIONAL BUSINESS MACHINES CORP\n",
       "1  EXT1YU6H6                  BANK OF AMERICA CORP\n",
       "2  EXT1YU6H7                           WALMART INC\n",
       "3  EXT1YU6H8                                 3M CO"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create a DataFrame containing the \"External System\" ID\n",
    "\n",
    "instruments = ext_system_df[[\"CLIENT_ID\", \"INST_DESC\"]]\n",
    "instruments"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "instrument_mapping_required = {\n",
    "  'name': 'INST_DESC'\n",
    "}\n",
    "\n",
    "instrument_identifier_mapping = {\n",
    "    'ClientInternal': 'CLIENT_ID'\n",
    "}\n",
    "\n",
    "instrument_mapping_optional = {}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\u001B[91m\u001B[1mInstruments Successfully Upserted: \u001B[0m\n"
     ]
    },
    {
     "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</th>\n",
       "      <th>ClientInternal ID</th>\n",
       "      <th>LUSID Instrument ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>WALMART INC</td>\n",
       "      <td>EXT1YU6H7</td>\n",
       "      <td>LUID_1IQX5N1J</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>BANK OF AMERICA CORP</td>\n",
       "      <td>EXT1YU6H6</td>\n",
       "      <td>LUID_RE6ECJE8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>INTERNATIONAL BUSINESS MACHINES CORP</td>\n",
       "      <td>EXT1YU6H5</td>\n",
       "      <td>LUID_SPJFM4L1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3M CO</td>\n",
       "      <td>EXT1YU6H8</td>\n",
       "      <td>LUID_D0CJRFUB</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                             Instrument ClientInternal ID LUSID Instrument ID\n",
       "0                           WALMART INC         EXT1YU6H7       LUID_1IQX5N1J\n",
       "1                  BANK OF AMERICA CORP         EXT1YU6H6       LUID_RE6ECJE8\n",
       "2  INTERNATIONAL BUSINESS MACHINES CORP         EXT1YU6H5       LUID_SPJFM4L1\n",
       "3                                 3M CO         EXT1YU6H8       LUID_D0CJRFUB"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "response = cocoon_tools.load_from_data_frame(\n",
    "    api_factory=api_factory, \n",
    "    data_frame=instruments, \n",
    "    identifier_mapping=instrument_identifier_mapping, \n",
    "    mapping_required=instrument_mapping_required,\n",
    "    mapping_optional=instrument_mapping_optional,\n",
    "    file_type='instruments',\n",
    "    scope='ExtSysProperties001')\n",
    "\n",
    "prettyprint.instrument_response(response[\"instruments\"][\"success\"][0])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Step 5: Load transactions into LUSID\n",
    "\n",
    "Finally, we want to load the transactions into LUSID. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Up to row 0\n"
     ]
    }
   ],
   "source": [
    "transaction_field_mapping_required = {\n",
    "    \"code\": \"PORT_NAME\",\n",
    "    \"transaction_id\": \"TRD_NUMBER\",\n",
    "    \"type\": 'TRD_TRANS_TYPE',\n",
    "    \"transaction_date\": 'TRD_TRADE_DATE',\n",
    "    \"settlement_date\": 'TRD_SETTLE_DATE',\n",
    "    \"units\": \"TRD_QUANTITY\",\n",
    "    \"transaction_price.price\": \"TRD_PRICE\",\n",
    "    \"transaction_price.type\": \"$Price\",\n",
    "    \"total_consideration.amount\": \"TRD_PRINCIPAL\",\n",
    "    \"total_consideration.currency\": \"TRD_CURRENCY\",\n",
    "    \"transaction_currency\": \"TRD_CURRENCY\"\n",
    "    }\n",
    "\n",
    "transaction_field_mapping_optional = {\n",
    "    \"exchange_rate\": \"$1\",\n",
    "    \"source\": f\"${scope}\"\n",
    "}\n",
    "\n",
    "transaction_identifier_mapping = {\n",
    "    'ClientInternal': \"CLIENT_ID\"\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "data types don't match for column is_cash_with_currency it is string in LUSID and float64 in file\n",
      "Updated is_cash_with_currency to object\n",
      "Check for missing transaction properties complete\n",
      "\n",
      "\n",
      "\u001B[1mTransactions Successfully Upserted into Portfolio\u001B[0m\n",
      "\u001B[1mScope: \u001B[0mEXT_SYSTEM\n",
      "\u001B[1mCode: \u001B[0mportfolio\n",
      "\u001B[1mTransactions Effective From: \u001B[0m2018-10-16 00:00:00+00:00\n",
      "\u001B[1mTransactions Created On: \u001B[0m2019-10-10 16:25:27.299610+00:00\n",
      "\n"
     ]
    }
   ],
   "source": [
    "responses = cocoon_tools.load_from_data_frame(\n",
    "    api_factory=api_factory, \n",
    "    scope=scope, \n",
    "    data_frame=ext_system_df,\n",
    "    mapping_required=transaction_field_mapping_required, \n",
    "    mapping_optional=transaction_field_mapping_optional,\n",
    "    identifier_mapping=transaction_identifier_mapping,\n",
    "    file_type=\"transaction\")\n",
    "\n",
    "for response in responses[\"transactions\"][\"success\"]:\n",
    "    print ('\\n')\n",
    "    prettyprint.transactions_response(response, scope, response.href.split(\"/\")[7])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The transactions are now visible in the front-end LUSID tool."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![Init](img/read_transactions.png)"
   ]
  }
 ],
 "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.8.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}