V1/product/ibor/Manual journal entries to correct cash balances.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Correcting cash balances with manual journal entries\\n\\nDemonstration of how to model manual journal entries in LUSID\\n\\nAttributes\\n----------\\nreconcilliations\\ncocoon\\nholdings\\n'"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\"\"\"Correcting cash balances with manual journal entries\n",
    "\n",
    "Demonstration of how to model manual journal entries in LUSID\n",
    "\n",
    "Attributes\n",
    "----------\n",
    "reconciliations\n",
    "cocoon\n",
    "holdings\n",
    "transaction configuration\n",
    "cancel transactions\n",
    "\"\"\""
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Correcting cash balances with manual journal entries\n",
    "\n",
    "In this notebook, we demonstrate how users can create manual journal entries in LUSID. For the purposes of this notebook, we will consider the scenario where a portfolio's custodian has included a stock exchange fee of £5000 in its GBP cash balance calculation. The same fee has not been included in the IBOR. This might lead a portfolio manager to go into overdraft if they trade on that amount. Therefore we create a manual entry in LUSID while the reconcilations team investigate the break."
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Setup LUSID"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "LUSID Environment Initialised\n",
      "LUSID API Version:  0.5.4408.0\n"
     ]
    }
   ],
   "source": [
    "# Import LUSID\n",
    "import lusid.models as models\n",
    "from lusidjam import RefreshingToken\n",
    "import lusid\n",
    "import lusidtools.cocoon.cocoon as cocoon\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",
    "from lusidtools.cocoon.cocoon_printer import format_transactions_response\n",
    "\n",
    "# Import Libraries\n",
    "import pprint\n",
    "import pytz\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import json\n",
    "import requests\n",
    "import os\n",
    "import warnings\n",
    "from datetime import datetime, timedelta, time\n",
    "\n",
    "# Configure notebook logging and warnings\n",
    "import logging\n",
    "\n",
    "logger = logging.getLogger()\n",
    "logger.setLevel(logging.ERROR)\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",
    ")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1) Prepare setup data\n",
    "\n",
    "In this notebook we have a portfolio called GLOBAL-EQUITY. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Portfolio code\n",
    "portfolio_code = \"GLOBAL-EQUITY\"\n",
    "\n",
    "# Load a mapping file for loading data\n",
    "with open(r\"config/seed_data.json\") as mappings_file:\n",
    "    seed_data_mapping = json.load(mappings_file)\n",
    "\n",
    "# Load a file to format holding response\n",
    "with open(r\"config/format_holdings_response.json\") as mappings_file:\n",
    "    format_holdings_response = json.load(mappings_file)\n",
    "\n",
    "# Load transaction file\n",
    "transactions_file = r\"data/manual_cash_data.csv\"\n",
    "transactions_df = pd.read_csv(transactions_file)\n",
    "\n",
    "# Define transaction portfolio API\n",
    "transaction_portfolios_api = api_factory.build(lusid.api.TransactionPortfoliosApi)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Define function to load holdings DF\n",
    "\n",
    "\n",
    "def get_holdings_df(scope, code, date=datetime.now(pytz.UTC)):\n",
    "\n",
    "    holdings_response = transaction_portfolios_api.get_holdings(\n",
    "        scope=scope, code=code, property_keys=[\"Instrument/default/Name\"]\n",
    "    )\n",
    "\n",
    "    holdings_df = lusid_response_to_data_frame(\n",
    "        holdings_response, rename_properties=True\n",
    "    )\n",
    "\n",
    "    return holdings_df"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2) Load IBOR data\n",
    "\n",
    "We have transactions from the IBOR which we load into the <b>ibor-nb</b> `scope`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Portfolio GLOBAL-EQUITY has been created in scope ibor-nb123 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",
    "ibor_df = transactions_df[transactions_df[\"scope\"] == \"IBOR\"]\n",
    "ibor_df.drop(columns=[\"scope\"], inplace=True)\n",
    "\n",
    "ibor_scope = \"ibor-nb123\"\n",
    "\n",
    "seed_data_response = seed_data(\n",
    "    api_factory,\n",
    "    [\"portfolios\", \"instruments\", \"transactions\"],\n",
    "    ibor_scope,\n",
    "    ibor_df,\n",
    "    \"DataFrame\",\n",
    "    mappings=seed_data_mapping,\n",
    ")\n",
    "\n",
    "print(\n",
    "    f\"Portfolio {portfolio_code} has been created in scope {ibor_scope} with transactions.\"\n",
    ")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Show IBOR holdings:\n",
    "\n",
    "* The IBOR has 500,000 GBP in Cash"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>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_ATFGUBHS</td>\n",
       "      <td>{}</td>\n",
       "      <td>Aviva</td>\n",
       "      <td>ibor-nb123/GLOBAL-EQUITY</td>\n",
       "      <td>P</td>\n",
       "      <td>100000.0</td>\n",
       "      <td>100000.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>1</th>\n",
       "      <td>LUID_STGB38I6</td>\n",
       "      <td>{}</td>\n",
       "      <td>Barclays</td>\n",
       "      <td>ibor-nb123/GLOBAL-EQUITY</td>\n",
       "      <td>P</td>\n",
       "      <td>100000.0</td>\n",
       "      <td>100000.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>2</th>\n",
       "      <td>LUID_PVOJGULG</td>\n",
       "      <td>{}</td>\n",
       "      <td>BP</td>\n",
       "      <td>ibor-nb123/GLOBAL-EQUITY</td>\n",
       "      <td>P</td>\n",
       "      <td>300000.0</td>\n",
       "      <td>300000.0</td>\n",
       "      <td>300000.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>CCY_GBP</td>\n",
       "      <td>{}</td>\n",
       "      <td>CCY_GBP</td>\n",
       "      <td>ibor-nb123/GLOBAL-EQUITY</td>\n",
       "      <td>B</td>\n",
       "      <td>500000.0</td>\n",
       "      <td>500000.0</td>\n",
       "      <td>500000.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_ATFGUBHS               {}                    Aviva   \n",
       "1  LUID_STGB38I6               {}                 Barclays   \n",
       "2  LUID_PVOJGULG               {}                       BP   \n",
       "3        CCY_GBP               {}                  CCY_GBP   \n",
       "\n",
       "  SourcePortfolioId(default-Properties) holding_type     units  settled_units  \\\n",
       "0              ibor-nb123/GLOBAL-EQUITY            P  100000.0       100000.0   \n",
       "1              ibor-nb123/GLOBAL-EQUITY            P  100000.0       100000.0   \n",
       "2              ibor-nb123/GLOBAL-EQUITY            P  300000.0       300000.0   \n",
       "3              ibor-nb123/GLOBAL-EQUITY            B  500000.0       500000.0   \n",
       "\n",
       "   cost.amount cost.currency  cost_portfolio_ccy.amount  \\\n",
       "0     100000.0           GBP                        0.0   \n",
       "1     100000.0           GBP                        0.0   \n",
       "2     300000.0           GBP                        0.0   \n",
       "3     500000.0           GBP                        0.0   \n",
       "\n",
       "  cost_portfolio_ccy.currency  \n",
       "0                         GBP  \n",
       "1                         GBP  \n",
       "2                         GBP  \n",
       "3                         GBP  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get_holdings_df(ibor_scope, \"GLOBAL-EQUITY\")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3) Load Custodian data\n",
    "\n",
    "We have transactions from the IBOR which we load into the <b>custodian-nb</b> `scope`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Portfolio GLOBAL-EQUITY has been created in scope custodian-nb123 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",
    "custodian_df = transactions_df[transactions_df[\"scope\"] == \"Custodian\"]\n",
    "custodian_df.drop(columns=[\"scope\"], inplace=True)\n",
    "\n",
    "custodian_scope = \"custodian-nb123\"\n",
    "\n",
    "seed_data_response = seed_data(\n",
    "    api_factory,\n",
    "    [\"portfolios\", \"instruments\", \"transactions\"],\n",
    "    custodian_scope,\n",
    "    custodian_df,\n",
    "    \"DataFrame\",\n",
    "    mappings=seed_data_mapping,\n",
    ")\n",
    "\n",
    "print(\n",
    "    f\"Portfolio {portfolio_code} has been created in scope {custodian_scope} with transactions.\"\n",
    ")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Show Custodian holdings:\n",
    "\n",
    "* The Custodian has 595,000 GBP in Cash"
   ]
  },
  {
   "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>instrument_uid</th>\n",
       "      <th>sub_holding_keys</th>\n",
       "      <th>Name(default-Properties)</th>\n",
       "      <th>SourcePortfolioId(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_ATFGUBHS</td>\n",
       "      <td>{}</td>\n",
       "      <td>Aviva</td>\n",
       "      <td>custodian-nb123/GLOBAL-EQUITY</td>\n",
       "      <td>P</td>\n",
       "      <td>100000.0</td>\n",
       "      <td>100000.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>1</th>\n",
       "      <td>LUID_STGB38I6</td>\n",
       "      <td>{}</td>\n",
       "      <td>Barclays</td>\n",
       "      <td>custodian-nb123/GLOBAL-EQUITY</td>\n",
       "      <td>P</td>\n",
       "      <td>100000.0</td>\n",
       "      <td>100000.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>2</th>\n",
       "      <td>LUID_PVOJGULG</td>\n",
       "      <td>{}</td>\n",
       "      <td>BP</td>\n",
       "      <td>custodian-nb123/GLOBAL-EQUITY</td>\n",
       "      <td>P</td>\n",
       "      <td>300000.0</td>\n",
       "      <td>300000.0</td>\n",
       "      <td>300000.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>CCY_GBP</td>\n",
       "      <td>{}</td>\n",
       "      <td>CCY_GBP</td>\n",
       "      <td>custodian-nb123/GLOBAL-EQUITY</td>\n",
       "      <td>B</td>\n",
       "      <td>495000.0</td>\n",
       "      <td>495000.0</td>\n",
       "      <td>495000.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_ATFGUBHS               {}                    Aviva   \n",
       "1  LUID_STGB38I6               {}                 Barclays   \n",
       "2  LUID_PVOJGULG               {}                       BP   \n",
       "3        CCY_GBP               {}                  CCY_GBP   \n",
       "\n",
       "  SourcePortfolioId(default-Properties) holding_type     units  settled_units  \\\n",
       "0         custodian-nb123/GLOBAL-EQUITY            P  100000.0       100000.0   \n",
       "1         custodian-nb123/GLOBAL-EQUITY            P  100000.0       100000.0   \n",
       "2         custodian-nb123/GLOBAL-EQUITY            P  300000.0       300000.0   \n",
       "3         custodian-nb123/GLOBAL-EQUITY            B  495000.0       495000.0   \n",
       "\n",
       "   cost.amount cost.currency  cost_portfolio_ccy.amount  \\\n",
       "0     100000.0           GBP                        0.0   \n",
       "1     100000.0           GBP                        0.0   \n",
       "2     300000.0           GBP                        0.0   \n",
       "3     495000.0           GBP                        0.0   \n",
       "\n",
       "  cost_portfolio_ccy.currency  \n",
       "0                         GBP  \n",
       "1                         GBP  \n",
       "2                         GBP  \n",
       "3                         GBP  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get_holdings_df(custodian_scope, \"GLOBAL-EQUITY\")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4) Reconcile Custodian versus IBOR\n",
    "\n",
    "We use LUSID's holdings [reconcilation functionality](https://support.finbourne.com/how-do-i-reconcile-my-holdings-in-lusid) to reconcile the IBOR's view against the Custodian's view of GLOBAL-EQUITY. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "def run_ibor_cust_recon(statement_datetime):\n",
    "\n",
    "    ibor_portfolio = models.PortfolioReconciliationRequest(\n",
    "        portfolio_id=models.ResourceId(scope=ibor_scope, code=portfolio_code),\n",
    "        effective_at=statement_datetime,\n",
    "        as_at=statement_datetime,\n",
    "    )\n",
    "\n",
    "    # Define our fund accountant portfolio\n",
    "    custodian_portfolio = models.PortfolioReconciliationRequest(\n",
    "        portfolio_id=models.ResourceId(scope=custodian_scope, code=portfolio_code),\n",
    "        effective_at=statement_datetime,\n",
    "        as_at=statement_datetime,\n",
    "    )\n",
    "\n",
    "    # Create our reconciliation request\n",
    "    reconcile_holdings_request = models.PortfoliosReconciliationRequest(\n",
    "        left=ibor_portfolio,\n",
    "        right=custodian_portfolio,\n",
    "        instrument_property_keys=[\"Instrument/default/Name\"],\n",
    "    )\n",
    "\n",
    "    # Reconcile the two portfolios\n",
    "    reconciliation = api_factory.build(lusid.api.ReconciliationsApi).reconcile_holdings(\n",
    "        portfolios_reconciliation_request=reconcile_holdings_request\n",
    "    )\n",
    "\n",
    "    return reconciliation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The AsAt time for the recon is: 2020-05-14 09:19:35.124358+00:00\n"
     ]
    }
   ],
   "source": [
    "first_recon_datetime = datetime.now(pytz.UTC).isoformat()\n",
    "recon_result = run_ibor_cust_recon(first_recon_datetime)\n",
    "\n",
    "print(f\"The AsAt time for the recon is: {first_recon_datetime}\")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 5) Result: we have a break of £5000!"
   ]
  },
  {
   "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>instrument_uid</th>\n",
       "      <th>sub_holding_keys</th>\n",
       "      <th>left_units</th>\n",
       "      <th>right_units</th>\n",
       "      <th>difference_units</th>\n",
       "      <th>left_cost.amount</th>\n",
       "      <th>left_cost.currency</th>\n",
       "      <th>right_cost.amount</th>\n",
       "      <th>right_cost.currency</th>\n",
       "      <th>difference_cost.amount</th>\n",
       "      <th>difference_cost.currency</th>\n",
       "      <th>instrument_properties.0.key</th>\n",
       "      <th>instrument_properties.0.value.label_value</th>\n",
       "      <th>instrument_properties.0.effective_from</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>CCY_GBP</td>\n",
       "      <td>{}</td>\n",
       "      <td>500000.0</td>\n",
       "      <td>495000.0</td>\n",
       "      <td>-5000.0</td>\n",
       "      <td>500000.0</td>\n",
       "      <td>GBP</td>\n",
       "      <td>495000.0</td>\n",
       "      <td>GBP</td>\n",
       "      <td>-5000.0</td>\n",
       "      <td>GBP</td>\n",
       "      <td>Instrument/default/Name</td>\n",
       "      <td>CCY_GBP</td>\n",
       "      <td>0001-01-01 00:00:00+00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  instrument_uid sub_holding_keys  left_units  right_units  difference_units  \\\n",
       "0        CCY_GBP               {}    500000.0     495000.0           -5000.0   \n",
       "\n",
       "   left_cost.amount left_cost.currency  right_cost.amount right_cost.currency  \\\n",
       "0          500000.0                GBP           495000.0                 GBP   \n",
       "\n",
       "   difference_cost.amount difference_cost.currency  \\\n",
       "0                 -5000.0                      GBP   \n",
       "\n",
       "  instrument_properties.0.key instrument_properties.0.value.label_value  \\\n",
       "0     Instrument/default/Name                                   CCY_GBP   \n",
       "\n",
       "  instrument_properties.0.effective_from  \n",
       "0              0001-01-01 00:00:00+00:00  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lusid_response_to_data_frame(recon_result, rename_properties=True)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 6) Create a manual journal entry to correct the break"
   ]
  },
  {
   "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>portfolio_code</th>\n",
       "      <th>portfolio_name</th>\n",
       "      <th>portfolio_base_currency</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>cash_transactions</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>GLOBAL-EQUITY</td>\n",
       "      <td>A generic global Equity portfolio</td>\n",
       "      <td>GBP</td>\n",
       "      <td>cash</td>\n",
       "      <td>GBP</td>\n",
       "      <td>Manual adjustment</td>\n",
       "      <td>cash_003</td>\n",
       "      <td>ManualEntryCashOut</td>\n",
       "      <td>01/05/2020</td>\n",
       "      <td>03/05/2020</td>\n",
       "      <td>5000</td>\n",
       "      <td>1</td>\n",
       "      <td>5000</td>\n",
       "      <td>GBP</td>\n",
       "      <td>GBP</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  portfolio_code                     portfolio_name portfolio_base_currency  \\\n",
       "0  GLOBAL-EQUITY  A generic global Equity portfolio                     GBP   \n",
       "\n",
       "  instrument_type instrument_id               name    txn_id  \\\n",
       "0            cash           GBP  Manual adjustment  cash_003   \n",
       "\n",
       "             txn_type txn_trade_date txn_settle_date  txn_units  txn_price  \\\n",
       "0  ManualEntryCashOut     01/05/2020      03/05/2020       5000          1   \n",
       "\n",
       "   txn_consideration currency cash_transactions  \n",
       "0               5000      GBP               GBP  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "transactions_file = r\"data/break_correction.csv\"\n",
    "break_correction_df = pd.read_csv(transactions_file)\n",
    "break_correction_df[\"portfolio_code\"] = portfolio_code\n",
    "break_correction_df[\"txn_type\"] = \"ManualEntryCashOut\"\n",
    "break_correction_df.drop(columns=[\"scope\"], inplace=True)\n",
    "break_correction_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "transaction_mapping = {\n",
    "    \"identifier_mapping\": {\n",
    "        \"ClientInternal\": \"instrument_id\",\n",
    "        \"Currency\": \"cash_transactions\",\n",
    "    },\n",
    "    \"required\": {\n",
    "        \"code\": \"portfolio_code\",\n",
    "        \"transaction_id\": \"txn_id\",\n",
    "        \"type\": \"txn_type\",\n",
    "        \"transaction_price.price\": \"txn_price\",\n",
    "        \"transaction_price.type\": \"$Price\",\n",
    "        \"total_consideration.amount\": \"txn_consideration\",\n",
    "        \"units\": \"txn_units\",\n",
    "        \"transaction_date\": \"txn_trade_date\",\n",
    "        \"total_consideration.currency\": \"currency\",\n",
    "        \"settlement_date\": \"txn_settle_date\",\n",
    "    },\n",
    "    \"optional\": {},\n",
    "    \"properties\": [],\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "number of successful portfolios requests: 1\n",
      "number of failed portfolios requests    : 0\n"
     ]
    }
   ],
   "source": [
    "result = cocoon.load_from_data_frame(\n",
    "    api_factory=api_factory,\n",
    "    scope=ibor_scope,\n",
    "    data_frame=break_correction_df,\n",
    "    mapping_required=transaction_mapping[\"required\"],\n",
    "    mapping_optional=transaction_mapping[\"optional\"],\n",
    "    file_type=\"transactions\",\n",
    "    identifier_mapping=transaction_mapping[\"identifier_mapping\"],\n",
    "    property_columns=transaction_mapping[\"properties\"],\n",
    "    properties_scope=ibor_scope,\n",
    ")\n",
    "\n",
    "succ, failed = format_transactions_response(result)\n",
    "print(f\"number of successful portfolios requests: {len(succ)}\")\n",
    "print(f\"number of failed portfolios requests    : {len(failed)}\")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 7) Create a new transaction type for the journal entry"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "movement = lusid.models.TransactionTypeMovement(\n",
    "    movement_types=\"CashAccrual\",\n",
    "    side=\"Side1\",\n",
    "    direction=-1,\n",
    "    properties={},\n",
    "    mappings=[],\n",
    ")\n",
    "\n",
    "alias = lusid.models.TransactionTypeAlias(\n",
    "    type=\"ManualEntryCashOut\",\n",
    "    description=\"Booking of manual cash out ledgder entry\",\n",
    "    transaction_class=\"JournalEntry\",\n",
    "    transaction_roles=\"Shorter\",\n",
    ")\n",
    "response = api_factory.build(lusid.api.TransactionConfigurationApi).set_transaction_type(\n",
    "    source=\"default\",\n",
    "    type=\"ManualEntryCashOut\",\n",
    "    transaction_type_request=lusid.models.TransactionTypeRequest(\n",
    "        aliases=[alias],\n",
    "        movements=[movement],\n",
    "    )\n",
    ")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 8) Rerun the reconcilation\n",
    "\n",
    "The result is empty - there are no breaks."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[]\n",
      "The AsAt time for the recon is: 2020-05-14 09:19:36.851025+00:00\n"
     ]
    }
   ],
   "source": [
    "second_recon_datetime = datetime.now(pytz.UTC).isoformat()\n",
    "second_recon_response = run_ibor_cust_recon(second_recon_datetime).values\n",
    "\n",
    "print(second_recon_response)\n",
    "print(f\"The AsAt time for the recon is: {second_recon_datetime}\")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Cleanup\n",
    "\n",
    "Cancel the adjustment to keep notebook idempotent."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The  cancel datetime request is: 2020-05-14 09:19:37.537570+00:00\n"
     ]
    }
   ],
   "source": [
    "cancel_response = transaction_portfolios_api.cancel_transactions(\n",
    "    scope=ibor_scope, code=portfolio_code, transaction_ids=[\"cash_003\"]\n",
    ")\n",
    "\n",
    "cancel_time = cancel_response.as_at\n",
    "\n",
    "print(f\"The  cancel datetime request is: {cancel_time}\")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}