V1/features/instruments/Managing a transaction lifecycle using LUSID's properties.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Managing the transaction lifecycle on LUSID\\n\\nDemonstration of how to use properties to manage the transaction lifecycle\\n\\nAttributes\\n----------\\ninstruments\\ntransactions\\nproperties\\ndata types\\ncocoon\\n'"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\"\"\"Managing the transaction lifecycle on LUSID\n",
    "\n",
    "Demonstration of how to use properties to manage the transaction lifecycle\n",
    "\n",
    "Attributes\n",
    "----------\n",
    "instruments\n",
    "transactions\n",
    "properties\n",
    "data types\n",
    "cocoon\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Managing the transaction lifecycle on LUSID\n",
    "\n",
    "In this notebook we show how you can use `properties` to manage the transaction lifecycle. For the purposes of this demonstration, we're using the [transaction](https://support.finbourne.com/what-is-a-transaction) entity but the same principles could be applied to LUSID's orders and allocations, or indeed any data entity which has `properties`. In the example, we will add a <b>confirmation_status</b> and <b>settlement_status</b> to our transaction. In practise you can add as many statuses as you need. A quick note on terminology:\n",
    "\n",
    "* <b>Confirmation</b>: The process by which the two counterparties to a trade input their instructions to a central system which compares them and, if the instructions agree, confirms them and passes them on for settlement.\n",
    "* <b>Settlement</b>: The process of transferring securities into the account of a buyer and cash into the seller's account following a trade of stocks, bonds, futures or other financial assets.\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Setup LUSID"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "LUSID Environment Initialised\n",
      "LUSID API Version:  0.6.5682.0\n"
     ]
    }
   ],
   "source": [
    "# Import LUSID\n",
    "import lusid.models as models\n",
    "from lusidjam import RefreshingToken\n",
    "\n",
    "# Import Libraries\n",
    "import pprint\n",
    "from datetime import datetime, timedelta, time\n",
    "import pytz\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import json\n",
    "import requests\n",
    "import os\n",
    "import lusid\n",
    "import lusidtools.cocoon.cocoon as cocoon\n",
    "import warnings\n",
    "import uuid\n",
    "from lusidtools.cocoon.utilities import create_scope_id\n",
    "from lusidtools.cocoon.seed_sample_data import seed_data\n",
    "from lusidtools.pandas_utils.lusid_pandas import lusid_response_to_data_frame\n",
    "\n",
    "# Supress warnings\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",
    ")\n",
    "\n",
    "print(\"LUSID Environment Initialised\")\n",
    "print(\n",
    "    \"LUSID API Version: \",\n",
    "    api_factory.build(lusid.api.ApplicationMetadataApi)\n",
    "    .get_lusid_versions()\n",
    "    .build_version,\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# define a unqique scope\n",
    "scope = \"transaction_lifecycle\"\n",
    "portfolio_code = \"EQUITY_UK\"\n",
    "\n",
    "# Load a mapping file for loading data\n",
    "with open(r\"config/transaction-lifecycle/transaction_status.json\") as mappings_file:\n",
    "    transaction_status_mapping = json.load(mappings_file)\n",
    "\n",
    "# Load a mapping file for formatting DataFrame\n",
    "with open(\n",
    "    r\"config/transaction-lifecycle/get_transactions_mapping.json\"\n",
    ") as mappings_file:\n",
    "    get_transactions_df_mapping = json.load(mappings_file)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1) Introduction to transaction statuses\n",
    "\n",
    "\n",
    "In the code below, we're going to create a confirmation and settlement status with some values which can be assigned to a `transaction`. As with all LUSID properties, you're free to assign your own codes and values. The below is a sample implementation. The values and codes are not fixed. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "\n",
    "\n",
    "<u><i><center> Table showing sample confirmation statuses </center></i></u>\n",
    "\n",
    "\n",
    "| Confirmation status  | Status description |\n",
    "| :------------- | :------------- |\n",
    "|Unconfirmed |Trade is unconfirmed and no confirmation message has been sent to broker |\n",
    "|MessageSentToBroker | Trade is unconfirmed and confirmation message has been sent to broker |\n",
    "|AckRecFromBroker | Acknowledgement message has been recieved from broker |\n",
    "|TradeConfirmed| Trade has confirmed | \n",
    "|FailedConfirmation | Trade confirmation has failed |\n",
    "\n",
    "\n",
    "<br>\n",
    "<br>\n",
    "\n",
    "\n",
    "<u><i><center> Table showing sample settlement statuses </center></i></u>\n",
    "\n",
    "\n",
    "| Settlement status  | Status description |\n",
    "| :------------- | :------------- |\n",
    "|Unsettled |Trade has not settled and no settlement message has been sent to custodian |\n",
    "|MessageSentToCustodian | Trade is unsettled and settlement message has been sent to custodian |\n",
    "|AckRecFromCustodian | Acknowledgement message has been recieved from custodian |\n",
    "|Settled| Trade has settled | \n",
    "|FailedSettlement| Trade settlement has failed |\n",
    "\n",
    "<br>\n",
    "<br>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2) Create a new data type to hold acceptable values for the status\n",
    "\n",
    "In this section, we create a new LUSID data type object to hold the list of acceptable values for each status:\n",
    "\n",
    "* Acceptable values for confirmation status are: Unconfirmed, MessageSentToBroker, AckRecFromBroker, TradeConfirmed, and FailedConfirmation.\n",
    "* Acceptable values for settlement status are: Unsettled, MessageSentToCustodian, AckRecFromCustodian, Settled, and FailedSettlement.\n",
    "\n",
    "his data type will be used when creating the <b>confirmation_status</b> and <b>settlement_status</b> property definition."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# define lists for acceptable status values\n",
    "\n",
    "statuses = {\n",
    "    \"ConfirmationStatusCodes\": [\n",
    "        \"Unconfirmed\",\n",
    "        \"MessageSentToBroker\",\n",
    "        \"AckRecFromBroker\",\n",
    "        \"TradeConfirmed\",\n",
    "        \"FailedConfirmation\",\n",
    "    ],\n",
    "    \"SettlementStatusCodes\": [\n",
    "        \"Unsettled\",\n",
    "        \"MessageSentToCustodian\",\n",
    "        \"AckRecFromCustodian\",\n",
    "        \"Settled\",\n",
    "        \"FailedSettlement\",\n",
    "    ],\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'List of allowable values for ConfirmationStatusCodes'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "['TradeConfirmed',\n",
       " 'Unconfirmed',\n",
       " 'MessageSentToBroker',\n",
       " 'AckRecFromBroker',\n",
       " 'FailedConfirmation']"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "'List of allowable values for SettlementStatusCodes'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "['Settled',\n",
       " 'MessageSentToCustodian',\n",
       " 'AckRecFromCustodian',\n",
       " 'FailedSettlement',\n",
       " 'Unsettled']"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "for status_code, values_list in statuses.items():\n",
    "\n",
    "    try:\n",
    "\n",
    "        create_request = lusid.models.CreateDataTypeRequest(\n",
    "            scope=scope,\n",
    "            code=status_code,\n",
    "            type_value_range=\"Closed\",\n",
    "            display_name=f\"Available {status_code}\",\n",
    "            description=f\"List of allowable values for {status_code}\",\n",
    "            value_type=\"String\",\n",
    "            acceptable_values=values_list,\n",
    "        )\n",
    "\n",
    "        response = api_factory.build(lusid.api.DataTypesApi).create_data_type(\n",
    "            create_data_type_request=create_request\n",
    "        )\n",
    "\n",
    "        display(f\"Data Type of {status_code} has been created.\")\n",
    "        display(f\"The acceptable values for this data type are: {str(values_list)}\\n\")\n",
    "\n",
    "    except:\n",
    "\n",
    "        response = api_factory.build(lusid.api.DataTypesApi).get_data_type(\n",
    "            scope=scope, code=status_code\n",
    "        )\n",
    "\n",
    "        display(response.description)\n",
    "        display(response.acceptable_values)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3) Create new property definitions\n",
    "\n",
    "Next we define a new <b>confirmation_status</b> and <b>settlement_status</b> property which will be used on the transaction to set the allowed statuses. As you can see, we have passed the <b>data_type_id</b> which we created above."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "for status_code, data_type in [\n",
    "    (\"ConfirmationStatus\", \"ConfirmationStatusCodes\"),\n",
    "    (\"SettlementStatus\", \"SettlementStatusCodes\"),\n",
    "]:\n",
    "\n",
    "    try:\n",
    "\n",
    "        property_response = api_factory.build(\n",
    "            lusid.api.PropertyDefinitionsApi\n",
    "        ).create_property_definition(\n",
    "            create_property_definition_request=lusid.models.CreatePropertyDefinitionRequest(\n",
    "                domain=\"Transaction\",\n",
    "                scope=scope,\n",
    "                code=status_code,\n",
    "                value_required=None,\n",
    "                display_name=status_code,\n",
    "                data_type_id=lusid.ResourceId(scope=scope, code=data_type),\n",
    "                life_time=None,\n",
    "            )\n",
    "        )\n",
    "\n",
    "    except:\n",
    "        pass"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4) Create new portfolio with transactions\n",
    "\n",
    "In the code below, we create a new portfolio called <b>EQUITY_UK</b> with two `transactions` from the <i>equity_transactions.csv</i> file. There is one equity trade and one subsciption of cash. The `transactions` have the <b>transaction_status</b> of <i>Executed</i> and <i>Confirmed</i> which are allowed values per the data type above."
   ]
  },
  {
   "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>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>ConfirmationStatus</th>\n",
       "      <th>SettlementStatus</th>\n",
       "      <th>cash_transactions</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>EQUITY_UK</td>\n",
       "      <td>LUSID's top 10 FTSE stock portfolio</td>\n",
       "      <td>GBP</td>\n",
       "      <td>GB0002162385</td>\n",
       "      <td>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>MessageSentToBroker</td>\n",
       "      <td>MessageSentToCustodian</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>EQUITY_UK</td>\n",
       "      <td>LUSID's top 10 FTSE stock portfolio</td>\n",
       "      <td>GBP</td>\n",
       "      <td>GBP</td>\n",
       "      <td>GBP</td>\n",
       "      <td>cash</td>\n",
       "      <td>GBP</td>\n",
       "      <td>GBP Cash</td>\n",
       "      <td>trd_0021</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>12000000</td>\n",
       "      <td>GBP</td>\n",
       "      <td>TradeConfirmed</td>\n",
       "      <td>Settled</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  LUSID's top 10 FTSE stock portfolio                     GBP   \n",
       "1      EQUITY_UK  LUSID's top 10 FTSE stock portfolio                     GBP   \n",
       "\n",
       "         ticker   sedol instrument_type instrument_id      name    txn_id  \\\n",
       "0  GB0002162385  SEDOL1          equity       EQ_1234     Aviva  trd_0001   \n",
       "1           GBP     GBP            cash           GBP  GBP Cash  trd_0021   \n",
       "\n",
       "  txn_type txn_trade_date txn_settle_date  txn_units  txn_price  \\\n",
       "0      Buy     02/01/2020      04/01/2020     120000          5   \n",
       "1  FundsIn     02/01/2020      04/01/2020   12000000          1   \n",
       "\n",
       "   txn_consideration currency   ConfirmationStatus        SettlementStatus  \\\n",
       "0             600000      GBP  MessageSentToBroker  MessageSentToCustodian   \n",
       "1           12000000      GBP       TradeConfirmed                 Settled   \n",
       "\n",
       "  cash_transactions  \n",
       "0               NaN  \n",
       "1               GBP  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "transactions_file = r\"data/transaction-lifecycle/equity_transactions.csv\"\n",
    "transactions_df = pd.read_csv(transactions_file)\n",
    "transactions_df[\"portfolio_code\"] = portfolio_code\n",
    "transactions_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "WARNING:root:Data types don't match for column ConfirmationStatus it is ConfirmationStatusCodes in LUSID and object in file\n",
      "WARNING:root:Data types don't match for column SettlementStatus it is SettlementStatusCodes in LUSID and object in file\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Portfolio EQUITY_UK has been created with transactions.\n"
     ]
    }
   ],
   "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(\n",
    "    api_factory,\n",
    "    [\"portfolios\", \"instruments\", \"transactions\"],\n",
    "    scope,\n",
    "    transactions_file,\n",
    "    \"csv\",\n",
    "    mappings=transaction_status_mapping,\n",
    ")\n",
    "\n",
    "print(f\"Portfolio {portfolio_code} has been created with transactions.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 5) Check transactions were loaded successfully\n",
    "\n",
    "Check that the `transactions` loaded correctly by calling the [get transactions](https://www.lusid.com/docs/api/#operation/GetTransactions) method on the TransactionPortfolios API."
   ]
  },
  {
   "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>TransactionId</th>\n",
       "      <th>TransactionType</th>\n",
       "      <th>ClientId</th>\n",
       "      <th>LusidInstrumentId</th>\n",
       "      <th>TransactionDate</th>\n",
       "      <th>SettlementDate</th>\n",
       "      <th>Units</th>\n",
       "      <th>Price</th>\n",
       "      <th>PriceType</th>\n",
       "      <th>TotalConsideration</th>\n",
       "      <th>TotalConsiderationCurrency</th>\n",
       "      <th>ExchangeRate</th>\n",
       "      <th>TxnCurrency</th>\n",
       "      <th>SourcePortfolioId</th>\n",
       "      <th>SourcePortfolioScope</th>\n",
       "      <th>ConfirmationStatus</th>\n",
       "      <th>SettlementStatus</th>\n",
       "      <th>InstrumentCurrency</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>trd_0001</td>\n",
       "      <td>Buy</td>\n",
       "      <td>EQ_1234</td>\n",
       "      <td>LUID_BM2Y3771</td>\n",
       "      <td>2020-01-02 00:00:00+00:00</td>\n",
       "      <td>2020-01-04 00:00:00+00:00</td>\n",
       "      <td>120000.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>Price</td>\n",
       "      <td>600000.0</td>\n",
       "      <td>GBP</td>\n",
       "      <td>1.0</td>\n",
       "      <td>GBP</td>\n",
       "      <td>EQUITY_UK</td>\n",
       "      <td>transaction_lifecycle</td>\n",
       "      <td>MessageSentToBroker</td>\n",
       "      <td>MessageSentToCustodian</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>trd_0021</td>\n",
       "      <td>FundsIn</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CCY_GBP</td>\n",
       "      <td>2020-01-02 00:00:00+00:00</td>\n",
       "      <td>2020-01-04 00:00:00+00:00</td>\n",
       "      <td>12000000.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Price</td>\n",
       "      <td>12000000.0</td>\n",
       "      <td>GBP</td>\n",
       "      <td>1.0</td>\n",
       "      <td>GBP</td>\n",
       "      <td>EQUITY_UK</td>\n",
       "      <td>transaction_lifecycle</td>\n",
       "      <td>TradeConfirmed</td>\n",
       "      <td>Settled</td>\n",
       "      <td>GBP</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  TransactionId TransactionType ClientId LusidInstrumentId  \\\n",
       "0      trd_0001             Buy  EQ_1234     LUID_BM2Y3771   \n",
       "1      trd_0021         FundsIn      NaN           CCY_GBP   \n",
       "\n",
       "            TransactionDate            SettlementDate       Units  Price  \\\n",
       "0 2020-01-02 00:00:00+00:00 2020-01-04 00:00:00+00:00    120000.0    5.0   \n",
       "1 2020-01-02 00:00:00+00:00 2020-01-04 00:00:00+00:00  12000000.0    1.0   \n",
       "\n",
       "  PriceType  TotalConsideration TotalConsiderationCurrency  ExchangeRate  \\\n",
       "0     Price            600000.0                        GBP           1.0   \n",
       "1     Price          12000000.0                        GBP           1.0   \n",
       "\n",
       "  TxnCurrency SourcePortfolioId   SourcePortfolioScope   ConfirmationStatus  \\\n",
       "0         GBP         EQUITY_UK  transaction_lifecycle  MessageSentToBroker   \n",
       "1         GBP         EQUITY_UK  transaction_lifecycle       TradeConfirmed   \n",
       "\n",
       "         SettlementStatus InstrumentCurrency  \n",
       "0  MessageSentToCustodian                NaN  \n",
       "1                 Settled                GBP  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Define the transactions portfolio API\n",
    "transaction_portfolio_api = api_factory.build(lusid.api.TransactionPortfoliosApi)\n",
    "\n",
    "# Call the get transactions method\n",
    "get_transaction_response = transaction_portfolio_api.get_transactions(\n",
    "    scope=scope, code=portfolio_code\n",
    ")\n",
    "\n",
    "# Scrape the response into a DataFrame\n",
    "get_transactions_df = lusid_response_to_data_frame(\n",
    "    get_transaction_response,\n",
    "    rename_properties=True,\n",
    "    column_name_mapping=get_transactions_df_mapping,\n",
    ")\n",
    "\n",
    "# Print the DataFrame\n",
    "column_rename_dict = {\n",
    "    column: column[: (column.rfind(\"(\"))]\n",
    "    for column in get_transactions_df.filter(regex=\"Properties\").columns\n",
    "}\n",
    "get_transactions_df.rename(columns=column_rename_dict, inplace=True)\n",
    "get_transactions_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 6) Correct the confirmation status to <i>Matched</i> - this is an invalid value \n",
    "\n",
    "To show the data validation on <b>confirmation_status</b> field, we now want to add an unacceptable value. For the purposes of this demo, we will correct the <b>confirmation_status</b> to <i>Matched</i> which is not one of the 4 acceptable values."
   ]
  },
  {
   "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",
       "      <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>ConfirmationStatus</th>\n",
       "      <th>SettlementStatus</th>\n",
       "      <th>cash_transactions</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>EQUITY_UK</td>\n",
       "      <td>LUSID's top 10 FTSE stock portfolio</td>\n",
       "      <td>GBP</td>\n",
       "      <td>GB0002162385</td>\n",
       "      <td>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>Matched</td>\n",
       "      <td>MessageSentToCustodian</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>EQUITY_UK</td>\n",
       "      <td>LUSID's top 10 FTSE stock portfolio</td>\n",
       "      <td>GBP</td>\n",
       "      <td>GBP</td>\n",
       "      <td>GBP</td>\n",
       "      <td>cash</td>\n",
       "      <td>GBP</td>\n",
       "      <td>GBP Cash</td>\n",
       "      <td>trd_0021</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>12000000</td>\n",
       "      <td>GBP</td>\n",
       "      <td>TradeConfirmed</td>\n",
       "      <td>Settled</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  LUSID's top 10 FTSE stock portfolio                     GBP   \n",
       "1      EQUITY_UK  LUSID's top 10 FTSE stock portfolio                     GBP   \n",
       "\n",
       "         ticker   sedol instrument_type instrument_id      name    txn_id  \\\n",
       "0  GB0002162385  SEDOL1          equity       EQ_1234     Aviva  trd_0001   \n",
       "1           GBP     GBP            cash           GBP  GBP Cash  trd_0021   \n",
       "\n",
       "  txn_type txn_trade_date txn_settle_date  txn_units  txn_price  \\\n",
       "0      Buy     02/01/2020      04/01/2020     120000          5   \n",
       "1  FundsIn     02/01/2020      04/01/2020   12000000          1   \n",
       "\n",
       "   txn_consideration currency ConfirmationStatus        SettlementStatus  \\\n",
       "0             600000      GBP            Matched  MessageSentToCustodian   \n",
       "1           12000000      GBP     TradeConfirmed                 Settled   \n",
       "\n",
       "  cash_transactions  \n",
       "0               NaN  \n",
       "1               GBP  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "transactions_df.at[0, \"ConfirmationStatus\"] = \"Matched\"\n",
    "transactions_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As you can see, the request fails with an <b>InvalidTransactions</b> error: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "WARNING:root:Data types don't match for column ConfirmationStatus it is ConfirmationStatusCodes in LUSID and object in file\n",
      "WARNING:root:Data types don't match for column SettlementStatus it is SettlementStatusCodes in LUSID and object in file\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Error type:  InvalidTransactions \n",
      "\n",
      "Transaction ID which created error:  trd_0001 \n",
      "\n",
      "Error details:  Invalid value 'Matched' supplied to the field of data type transaction_lifecycle/ConfirmationStatusCodes. Validation error: The value is not in the list of acceptable values \n",
      "\n"
     ]
    }
   ],
   "source": [
    "result = cocoon.load_from_data_frame(\n",
    "    api_factory=api_factory,\n",
    "    scope=scope,\n",
    "    data_frame=transactions_df,\n",
    "    mapping_required=transaction_status_mapping[\"transactions\"][\"required\"],\n",
    "    mapping_optional=transaction_status_mapping[\"transactions\"][\"optional\"],\n",
    "    file_type=\"transactions\",\n",
    "    identifier_mapping=transaction_status_mapping[\"transactions\"][\"identifier_mapping\"],\n",
    "    property_columns=transaction_status_mapping[\"transactions\"][\"properties\"],\n",
    "    properties_scope=scope,\n",
    ")\n",
    "\n",
    "error_response = result[\"transactions\"][\"errors\"][0].body\n",
    "print(\"Error type: \", json.loads(error_response)[\"name\"], \"\\n\")\n",
    "print(\n",
    "    \"Transaction ID which created error: \",\n",
    "    json.loads(error_response)[\"errorDetails\"][0][\"id\"],\n",
    "    \"\\n\",\n",
    ")\n",
    "print(\"Error details: \", json.loads(error_response)[\"errorDetails\"][0][\"detail\"], \"\\n\")"
   ]
  }
 ],
 "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.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}