V1/product/ibor/Perform a reconciliation.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "\"\"\"Reconciliations\n",
    "\n",
    "Demonstration of how to use LUSID to find discrepancies between versions of a portfolio\n",
    "\n",
    "Attributes\n",
    "----------\n",
    "transactions\n",
    "portfolio groups\n",
    "instruments\n",
    "set holdings\n",
    "adjust holdings\n",
    "reconciliations\n",
    "properties\n",
    "\"\"\""
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## The Challenge"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We are an asset manager who has outsourced our fund accounting. We have multiple clients, each with multiple portfolios containing different investment strategies and priorities.\n",
    "\n",
    "We are feeling a lot of fee pressure and to maintain profitability we need to keep our costs low.\n",
    "\n",
    "Outsourcing our fund accounting was a big part of this plan, however there have been a large number of significant discrepancies between our records and those of the fund accountant. Thus we have had to set up an internal 'shadow' team to reconcile the differences between our records and those of the fund accountant. The cost of running this team almost completely negates the cost saving of outsourcing our fund accounting. In addition, there have been many times were we have just had to accept the records of the fund accountant as being correct.\n",
    "\n",
    "This has lead our portfolio managers to become incredibly frustrated as they can't accurately determine their position and are being forced into trading on the basis of unreliable information.\n",
    "\n",
    "We desperately need a solution that allows us to improve our reconciliation process so that we can easily identify & reconcile the discrepancies between our records and those of the fund accountant."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## The Solution"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "LUSID has been designed to help address challenges such as this.\n",
    "\n",
    "Using LUSID we can automate the reconciliation processs to help our investment managers get a better understanding of their position and remove the need to have a separate shadow accounting team.\n",
    "\n",
    "We can do this by:\n",
    "\n",
    "1. Creating one environment for our internal records and one for those of the fund accountant\n",
    "2. Creating the current holdings for our clients in both environments\n",
    "3. Completing a day of trading and updating our internal records with the day's trades\n",
    "4. Receiving a daily holdings report from our fund accountant and updating the fund accountant record\n",
    "5. Reconciling our internal records with those of the fund accountant\n",
    "6. Building a system to automatically identify and flag any discrepancies\n",
    "\n",
    "At the end of this process our LUSID environment will look like the below:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![Scopes](img/transparency-matchedexceptions.gif)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "pycharm": {
     "is_executing": false
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Import LUSID\n",
    "import lusid\n",
    "import lusid.models as models\n",
    "import lusid_sample_data as import_data\n",
    "from lusidjam import RefreshingToken\n",
    "\n",
    "# Import Libraries\n",
    "import pprint\n",
    "from datetime import datetime, timedelta, time\n",
    "import pytz\n",
    "import printer as prettyprint\n",
    "import os\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 Environment Initialised')\n",
    "print ('LUSID API Version :', api_factory.build(lusid.api.ApplicationMetadataApi).get_lusid_versions().build_version)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 1) Create our client portfolios in an internal and fund accountant environment"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### a) Create our Scopes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In LUSID we can create separate environments for our records and those of the fund accountant using scopes. A scope is a container for LUSID objects and can be thought of as a separate identity namespace. This allows us to have a portfolio with the same name in an 'internal-records' scope as well as in a 'fund-accountant-records' scope without having to worry about collisions.\n",
    "\n",
    "We will thus use two scopes, one for our internal records and one for the fund accountant's records.\n",
    "\n",
    "*Run the cell below to initialise our two scopes. Note that each scope will have a unique 4 character code appended to it to make the name more unique.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "pycharm": {
     "is_executing": false
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Fetch our scopes\n",
    "scope_id = import_data.create_scope_id()\n",
    "internal_scope_code = 'internal-records-{}'.format(scope_id)\n",
    "scope_id = import_data.create_scope_id()\n",
    "fund_accountant_scope_code = 'fund-accountant-records-{}'.format(scope_id)\n",
    "\n",
    "scopes = [internal_scope_code, fund_accountant_scope_code]\n",
    "\n",
    "prettyprint.heading('Internal Scope Code', internal_scope_code)\n",
    "prettyprint.heading('Fund Accountant Scope Code', fund_accountant_scope_code)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Our LUSID environment now looks like the below. We will update this with each step."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![Scopes](img/transparency-scopes.gif)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### b) Create our portfolios "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now that we have our scopes we can create our portfolios.\n",
    "\n",
    "The first thing that we need to do here is give each portfolio a unique name. In LUSID these are refered to as 'codes'. Almost every object in LUSID can be refered to by the combination of the scope that it is contained in and its code. The code must be unique to the scope.\n",
    "\n",
    "We have three clients each with a varying number of portfolios. Each portfolio has a different objective to meet the client's needs. For example a client may have a balanced portfolio which allows them to generate a sustained and predictable return and a fixed income portfolio to meet their distribution requirements.\n",
    "\n",
    "Let us call these clients:\n",
    "\n",
    "- Client A\n",
    "- Client B\n",
    "- Client C\n",
    "\n",
    "In LUSID we can use portfolio groups to group the different portfolios of each of our clients together. This allows us to easily keep track of and work with the holdings of each of our clients.\n",
    "\n",
    "*Run the cell below to import the names for our portfolio groups and their corresponding portfolios.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "pycharm": {
     "is_executing": false
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "client_portfolios = import_data.fetch_portfolio_names('portfolios.csv')\n",
    "prettyprint.portfolios(client_portfolios)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now that we have the codes for our portfolios, let us create them in LUSID across both our internal and fund accountant scopes.\n",
    "\n",
    "We can only create one portfolio at a time so what we will do is create each of the portfolios in a group and then once they have all been created create the group binding them all together.\n",
    "\n",
    "Note that we have had these clients for a while and so the portfolios have actually been live for 2 years, they just haven't been added to LUSID yet. when we create them we will specify the date they went live using the 'created' parameter.\n",
    "\n",
    "This allows us to backdate trades. If we don't specify the correct created date we won't be able to add any trades before today's date.\n",
    "\n",
    "When we create a portfolio we will specify the following, note that not all of these fields are compulsory.\n",
    "\n",
    "- Display Name\n",
    "- Code\n",
    "- Base Currency\n",
    "- Description\n",
    "- Creation Date\n",
    "\n",
    "*Run the cell below to create our portfolios and their portfolio groups*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "pycharm": {
     "is_executing": false
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# The date our portfolios were first created\n",
    "portfolio_creation_date = (datetime.now(pytz.UTC) - timedelta(days=895)).isoformat()\n",
    "\n",
    "# Iterate over our two scopes, this will create our portfolios and portfolio groups in both scopes\n",
    "for scope in scopes:\n",
    "    \n",
    "    # Iterate over our portfolio groups selecting the name of the group and the list of portfolios\n",
    "    for portfolio_group_code, portfolio_group in client_portfolios.items():\n",
    "\n",
    "        # Loop over our list of portfolios selecting the portfolio code\n",
    "        for portfolio_code in portfolio_group:\n",
    "\n",
    "            # Create the request to add our portfolio\n",
    "            portfolio_request = models.CreateTransactionPortfolioRequest(\n",
    "                display_name=portfolio_code,\n",
    "                code=portfolio_code,\n",
    "                base_currency='GBP',\n",
    "                description=portfolio_code,\n",
    "                created=portfolio_creation_date)\n",
    "\n",
    "            # Create our portfolio\n",
    "            response = api_factory.build(lusid.api.TransactionPortfoliosApi).create_portfolio(\n",
    "                scope=scope,\n",
    "                create_transaction_portfolio_request=portfolio_request)\n",
    "            \n",
    "            prettyprint.portfolio_response(response)\n",
    "\n",
    "\n",
    "        '''\n",
    "        Now that we have created all of the portfolios for this client we can create our portfolio group and add the\n",
    "        portfolios to it. To add a portfolio to a group we refer to it by its LUSID ResourceId. This is simply the\n",
    "        scope and code of the portfolio\n",
    "        '''\n",
    "\n",
    "        # Prepare a list of ResourceIDs for our portfolios\n",
    "        portfolio_resource_ids = [\n",
    "            models.ResourceId(\n",
    "                scope=scope,\n",
    "                code=portfolio_code) for portfolio_code in portfolio_group]\n",
    "\n",
    "        # Build our request\n",
    "        portfolio_group_request = models.CreatePortfolioGroupRequest(\n",
    "            code=portfolio_group_code,\n",
    "            display_name=portfolio_group_code,\n",
    "            values=portfolio_resource_ids,\n",
    "            description=portfolio_group_code)\n",
    "        \n",
    "        # Create our portfolio group\n",
    "        response = api_factory.build(lusid.api.PortfolioGroupsApi).create_portfolio_group(\n",
    "            scope=scope,\n",
    "            create_portfolio_group_request=portfolio_group_request)\n",
    "        \n",
    "        prettyprint.portfolio_group_response(response, 'created')\n",
    "        \n",
    "print ('All portfolios have been created')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Our LUSID environment is now populated with our client's portfolios in both of our scopes. These portfolios are currently empty."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![Scopes](img/transparency-portfolios.gif)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 2) Create our current holdings for our clients "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now that we have our portfolios and groups set up for our clients we can add in their current holdings. We will populate the same holdings for both the internal scope and the fund accountant scope. This will allow us to start with both records completely in sync with each other."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### a) Create our instrument universe"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Before we can set our holdings we first need to upsert our instrument into LUSID.\n",
    "\n",
    "In LUSID we use an upsert method. As opposed to Insert, Upsert will insert a new record if it does not exist and update the existing record if it does exist. This allows us to add instruments more simply than checking if an instrument exists before we insert it.\n",
    "\n",
    "*Run the cell below to import our instrument universe. This will return a name for each of our instruments and the internal ID we have assigned to it*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "pycharm": {
     "is_executing": false
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "instrument_universe = import_data.fetch_instrument_universe('instruments.csv')\n",
    "prettyprint.instruments(instrument_universe, 'ClientInternal')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now that we have our instruments, we can upsert our instruments individually or in a batch. Either way the request should be in the form of key value pairs. We will use the instrument's name as the key and our request to upsert the instrument as the value.\n",
    "\n",
    "The request requires a name for the instrument which is a string. In this case we will use the same name as our key.\n",
    "\n",
    "It also requires at least one unique identifier for the instrument. This again takes the form of a key value pair. In this case we will use 'ClientInternal' as the key. The value is a string which is the identifier. The allowed unique identifiers currently supported are:\n",
    "\n",
    "* ClientInternal\n",
    "* Figi\n",
    "* RIC\n",
    "* QuotePermId\n",
    "\n",
    "Note that many other non-unique identifiers such as ISIN, SEDOL etc. are also supported. However as these are not necessarily unique and may be re-used over time they are unsuitable as unique identifers for our instruments. Instead we can add them as properties.\n",
    "\n",
    "Note that unlike most LUSID objects such as our portfolios, instruments are global to your account and are not contained within a scope.\n",
    "\n",
    "*Run the cell below to upsert our instruments into LUSID*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "pycharm": {
     "is_executing": false
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Initialise our batch upsert request\n",
    "batch_upsert_request = {}\n",
    "# Using our instrument universe create our batch request\n",
    "for instrument_name, instrument in instrument_universe.items():\n",
    "    batch_upsert_request[instrument_name] = models.InstrumentDefinition(\n",
    "        name=instrument_name,\n",
    "        identifiers={\n",
    "            'ClientInternal': models.InstrumentIdValue(\n",
    "                value=instrument['identifiers']['ClientInternal'])\n",
    "        }\n",
    "    )\n",
    "    \n",
    "# Upsert our batch\n",
    "instrument_response = api_factory.build(lusid.api.InstrumentsApi).upsert_instruments(request_body=batch_upsert_request)\n",
    "\n",
    "display(instrument_response)\n",
    "prettyprint.instrument_response(instrument_response)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Our LUSID environment now has an instrument master containing all of our instruments that we will be trading."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![Scopes](img/transparency-instrumentmaster.gif)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "During the upsertion process you may have noticed that that every instrument that is created in LUSID given a unique LUSID Instrument Id or LUID for short. This ID is used for many methods and is how LUSID uniquely identifies an instrument.\n",
    "\n",
    "Note that there is also a match feature that allows you to search for instruments using the identifiers that we defined for each instrument, however it is preferred to use the LUID as this is guaranteed to be unique and there is absolutely no chance of a collision.\n",
    "\n",
    "We therefore want to add our newly created LUIDs to our instrument list so that we can easily reference our instruments in future.\n",
    "\n",
    "Note that in addition to our instruments, there is also cash in our holdings. Instruments that represent cash are already set-up in LUSID by default. In LUSID the identifier for cash is the ISO4217 currency code. For example British Pounds have an identifier of GBP. We will add these to our local instrument universe so that we can reference them.\n",
    "\n",
    "*Run the cell below to add our LUIDs and cash to our local instrument universe*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "pycharm": {
     "is_executing": false
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Loop over our recently upserted instruments\n",
    "for instrument_name, instrument in instrument_response.values.items():\n",
    "    # Add our LUID as a new identifier so that we can use it in our calls later\n",
    "    instrument_universe[instrument_name]['identifiers']['LUID'] = instrument.lusid_instrument_id\n",
    "    \n",
    "# We will also add some cash instruments to our universe\n",
    "instrument_universe['GBP_Cash'] = {\n",
    "    'identifiers': {'LUID': 'GBP'},\n",
    "    'currency': 'GBP'}\n",
    "\n",
    "instrument_universe['USD_Cash'] = {\n",
    "    'identifiers': {'LUID': 'USD'},\n",
    "    'currency': 'USD'}\n",
    "            \n",
    "prettyprint.instruments(instrument_universe, 'LUID')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### b) Set our take on balances"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let us load our take on balances. Note that in practice this data will likely be imported from a CSV or report generated from an internal system or by a third party that summarises our client's holdings.\n",
    "\n",
    "Each holding as the instrument name, the quantity currently held and the weighted average price of the holding. Note that LUSID can support other more complex accounting methods such as First in First Out (FIFO), Last in First Out (LIFO) etc.\n",
    "\n",
    "*Run the cell below to import our initial holdings information*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "pycharm": {
     "is_executing": false
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "client_holdings = import_data.fetch_client_take_on_balances('holdings.csv')\n",
    "prettyprint.holdings(client_holdings)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To set our holdings in LUSID we use a list of adjustment requests.\n",
    "\n",
    "Using these requests LUSID will determine what transactions are required to update our portfolios so that they have these holdings.\n",
    "\n",
    "Each request object has two fields these are:\n",
    "\n",
    "* instrument_uid: This is the LUID of the instrument we want to adjust the holdings for\n",
    "\n",
    "* tax_lots: This is a list of tax lots. A tax lot being instruments purchased at different times and thus needing different treatment for tax purposes. In this case we will use a single tax lot\n",
    "\n",
    "    * units: This is the quantity of the instrument that we are holding in this tax lot\n",
    "\n",
    "    * cost: This is the total cost of the instrument and the currency it is in for this tax lot\n",
    "        * amount: The total cost/value of the instrument, here we multiply the instrument's price by its quantity to get the total amount\n",
    "        * currency: The currency that the instrument is in as an ISO4217 currency code\n",
    "\n",
    "    * portfolio_cost: This is the total cost of the instrument in the portfolio's currency for this tax lot\n",
    "\n",
    "    * price: This is the price of the instrument in the instrument's currency for this tax lot\n",
    "\n",
    "We will make our holdings effecitve as of two days ago. This gives us room to have a day of trading before we reconcile our internal records with those of the fund accountant.\n",
    "\n",
    "Just like when we created our portfolios we can only adjust the holdings one portfolio at a time. We will loop over our portfolios to create our adjustment requests before making our adjustments across our internal-records scope and our fund-accountant-records scope.\n",
    "\n",
    "*Run the cell below to create our requests and adjust our holdings*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "pycharm": {
     "is_executing": false
    },
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "holdings_effective_date = (datetime.now(pytz.UTC) - timedelta(days=2)).isoformat()\n",
    "\n",
    "# Iterate over our portfolios\n",
    "for portfolio_name, portfolio in client_holdings.items():\n",
    "    \n",
    "    # Initialise our list to hold the holding adjustments\n",
    "    holding_adjustments = []\n",
    "\n",
    "    # Iterate over the holdings in each portfolio\n",
    "    for instrument_name, holding in portfolio.items():\n",
    "        # Get our Lusid Instrument ID\n",
    "        if 'Cash' in instrument_name:\n",
    "            identifier_key = 'Instrument/default/Currency'\n",
    "        else:\n",
    "            identifier_key = 'Instrument/default/LusidInstrumentId'\n",
    "            \n",
    "        Luid = instrument_universe[instrument_name]['identifiers']['LUID']\n",
    "        # Create our adjust holdings request using our instrument universe to get the LUID identifier for the instrument\n",
    "        holding_adjustments.append(\n",
    "            models.AdjustHoldingRequest(\n",
    "                instrument_identifiers={\n",
    "                    identifier_key: Luid},\n",
    "                tax_lots=[\n",
    "                    models.TargetTaxLotRequest(\n",
    "                        units=holding['quantity'],\n",
    "                        cost=models.CurrencyAndAmount(\n",
    "                           amount=holding['quantity'] *\n",
    "                                  holding['price'],\n",
    "                           currency=instrument_universe[instrument_name]['currency']),\n",
    "                        portfolio_cost=holding['quantity'] *\n",
    "                                      holding['price'],\n",
    "                        price=holding['price'])\n",
    "                ])\n",
    "        )\n",
    "\n",
    "    # Iterate over our two scopes\n",
    "    for scope in scopes:\n",
    "\n",
    "        set_holdings_response = api_factory.build(lusid.api.TransactionPortfoliosApi).set_holdings(\n",
    "            scope=scope,\n",
    "            code=portfolio_name,\n",
    "            effective_at=holdings_effective_date,\n",
    "            adjust_holding_request=holding_adjustments)\n",
    "\n",
    "        prettyprint.set_holdings_response(set_holdings_response, scope, portfolio_name)\n",
    "\n",
    "print ('All portfolios have been set')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Our portfolios are no longer empty and now contain our initial holdings. Both the internal-records scope and the fund-accountant-records scope are in sync."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![Scopes](img/transparency-holdings.gif)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 3) Complete a day of trading and update our internal records"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now that we have our portfolios populated with their holdings we are going to simulate a day of trading. We are going to add all of yesterdays transactions to our internal-records portfolios.\n",
    "\n",
    "We had some exciting new strategies to implement across our portfolios that involved\n",
    "\n",
    "1. Selling stock in WPP\n",
    "2. Buying more stock in MicroFocus\n",
    "3. Selling stock in Kingfisher\n",
    "4. Buying more of the UK Treasury Gilt maturing in 2034 with coupon rate of 4.5%\n",
    "5. Buying some more UK Treasury Gilts and selling US Treasury Bonds\n",
    "6. Selling stock in Whitebread\n",
    "7. Selling some stock in TESCO\n",
    "\n",
    "In reality these trades would be added to our internal scope as soon as they are executed. They are also sent to our fund manager's systems in real time via API through our execution management system. Note that the fund accountant scope is not updated in real time. Instead we receive a daily report from our fund accountant. We then update the fund accountant scope using this report.\n",
    "\n",
    "Let us import our trade below. Keeping in mind that these would be generated from our order management or execution management system in reality. Each trade has a unique identifier prefixed with tid_. It also has a transaction date and settlement date.\n",
    "\n",
    "*Run the cell below to set time that trading opened yesterday and to import our trades*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "pycharm": {
     "is_executing": false
    },
    "scrolled": true,
    "tags": []
   },
   "outputs": [],
   "source": [
    "client_transactions, yesterday_trade_open = import_data.client_transactions('transactions.csv', instrument_universe)\n",
    "prettyprint.transactions(client_transactions)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now that we have imported our trades we can create a batch transaction request for each portfolio. This comes in the form of a list of transaction requests.\n",
    "\n",
    "Each transaction request object has the following properties:\n",
    "\n",
    "* transaction_id: A unique transaction identifier\n",
    "\n",
    "* type: The transaction type, by default the following are already available \n",
    "    * 'Buy'\n",
    "    * 'Sell'\n",
    "    * 'StockIn'\n",
    "    * 'StockOut'\n",
    "    * 'CoverShort'\n",
    "    * 'SellShort'\n",
    "    * 'FxBuy'\n",
    "    * 'FxSell'\n",
    "    * 'FwdFxBuy'\n",
    "    * 'FwdFxSell'\n",
    "    * 'FundsIn'\n",
    "    * 'FundsOut'\n",
    "    * 'OLFC' (Open Long Futures Contract)\n",
    "    * 'CLFC' (Close Long Futures Contract)\n",
    "    * 'OSFC' (Open Short Futures contract)\n",
    "    * 'CSFC' (Close Short Futures Contract)\n",
    "\n",
    "* instrument_uid: The Lusid Instrument Id also known as LUID\n",
    "\n",
    "* transaction_date: The date of the transaction as an ISO8601 datetime, that is \"YYYY-MM-DDTHH:MM:SSZ\"\n",
    "\n",
    "* settlement_date: The date of the settlement as an ISO8601 datetime, that is \"YYYY-MM-DDTHH:MM:SSZ\"\n",
    "\n",
    "* units: The quantity of units of the instrument involved in the transaction\n",
    "\n",
    "* transactionPrice: A TransactionPrice object with a price and type\n",
    "    * price: The price of the transaction\n",
    "    * type: The type of the price, available options are 'Price', 'Yield' or 'Spread'\n",
    "\n",
    "* totalConsideration: The total value of the transaction in settlement currency as a CurrencyAndAmount object\n",
    "    * amount: The amount of the currency\n",
    "    * currency: The currency as an ISO 4217 currency code\n",
    "\n",
    "* exchangeRate: Rate between transaction and settlement currency\n",
    "\n",
    "* transactionCurrency: The currency of the transaction as an ISO 4217 currency code\n",
    "\n",
    "* source: Where this transaction came from, the options are 'System' and 'Client'\n",
    "\n",
    "We will iterate over each potfolio to create our requests and then upsert them into LUSID.\n",
    "\n",
    "*Run the cell below to create our transaction requests and add them to LUSID*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "pycharm": {
     "is_executing": false
    },
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# Iterate over our portfolios\n",
    "for portfolio_name, portfolio in client_transactions.items():\n",
    "\n",
    "    # Initialise a list to hold our transactions for each portfolio\n",
    "    batch_transaction_requests = []\n",
    "\n",
    "    # Iterate over the transactions for each portfolio\n",
    "    for transaction_id, transaction in portfolio.items():\n",
    "\n",
    "        batch_transaction_requests.append(\n",
    "            models.TransactionRequest(\n",
    "                transaction_id=transaction_id,\n",
    "                type=transaction['type'],\n",
    "                instrument_identifiers={\n",
    "                    'Instrument/default/LusidInstrumentId': transaction['instrument_uid']},\n",
    "                transaction_date=transaction['transaction_date'],\n",
    "                settlement_date=transaction['settlement_date'],\n",
    "                units=transaction['units'],\n",
    "                transaction_price=models.TransactionPrice(\n",
    "                    price=transaction['transaction_price'],\n",
    "                    type='Price'),\n",
    "                total_consideration=models.CurrencyAndAmount(\n",
    "                    amount=transaction['units'] * transaction['transaction_price'],\n",
    "                    currency=transaction['transaction_currency']),\n",
    "                source='Client',\n",
    "                transaction_currency=transaction['transaction_currency'])\n",
    "        )\n",
    "\n",
    "    transaction_response = api_factory.build(lusid.api.TransactionPortfoliosApi).upsert_transactions(\n",
    "        scope=internal_scope_code,\n",
    "        code=portfolio_name,\n",
    "        transaction_request=batch_transaction_requests)\n",
    "\n",
    "    prettyprint.transactions_response(\n",
    "        transaction_response, \n",
    "        internal_scope_code, \n",
    "        portfolio_name)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can now see that in LUSID the portfolios that have had transactions added to them are no longer in sync with our fund accountant records. These portfolios are in pink in the image below."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![Scopes](img/transparency-transactions.gif)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 4) Receive our daily holdings report and update the fund accountant record"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It is now early in the morning the day after our recent trades. Our fund accountant has just sent us the daily report with our current position according to their records.\n",
    "\n",
    "We will update our fund accountant scope with their records.\n",
    "\n",
    "*Run the cell below to import the fund accountants records*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "pycharm": {
     "is_executing": false
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "fund_accountant_daily_holdings_report = import_data.fetch_fund_accountant_daily_holdings_report('fundaccountantreport.csv')\n",
    "prettyprint.holdings(fund_accountant_daily_holdings_report)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now that we have imported our report, we can create holding adjustments like we did when we first populated our portfolios. These will post adjustment transactions to bring our portfolios in line with the fund accountant's records.\n",
    "\n",
    "*Run the cell below to make the required adjustments to bring our fund-accountant-records scope in line with the fund accountant's daily report*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "pycharm": {
     "is_executing": false
    }
   },
   "outputs": [],
   "source": [
    "today = datetime.now(pytz.UTC)\n",
    "t = time(hour=6, minute=30)\n",
    "this_morning = pytz.utc.localize(datetime.combine(today, t)).isoformat()\n",
    "    \n",
    "for portfolio_name, portfolio in fund_accountant_daily_holdings_report.items():\n",
    "\n",
    "    # Create a key and initialise a list to hold our adjustments for each portfolio\n",
    "    holding_adjustments = []\n",
    "\n",
    "    # Iterate over the holdings in each portfolio\n",
    "    for instrument_name, holding in portfolio.items():\n",
    "        # Get our Lusid Instrument Id\n",
    "        if 'Cash' in instrument_name:\n",
    "            identifier_key = 'Instrument/default/Currency'\n",
    "        else:\n",
    "            identifier_key = 'Instrument/default/LusidInstrumentId'\n",
    "            \n",
    "        Luid = instrument_universe[instrument_name]['identifiers']['LUID']\n",
    "        \n",
    "        # Create our adjust holdings request using our instrument universe to get the LUID identifier for the instrument\n",
    "        holding_adjustments.append(\n",
    "            models.AdjustHoldingRequest(\n",
    "                instrument_identifiers={\n",
    "                    identifier_key: Luid},\n",
    "                tax_lots=[\n",
    "                    models.TargetTaxLotRequest(units=holding['quantity'],\n",
    "                                               cost=models.CurrencyAndAmount(\n",
    "                                                   amount=holding['quantity'] *\n",
    "                                                          holding['price'],\n",
    "                                                   currency=instrument_universe[instrument_name][\n",
    "                                                       'currency']),\n",
    "                                               portfolio_cost=holding['quantity'] *\n",
    "                                                              holding['price'],\n",
    "                                               price=holding['price'])\n",
    "                ])\n",
    "        )\n",
    "        \n",
    "    response = api_factory.build(lusid.api.TransactionPortfoliosApi).adjust_holdings(\n",
    "        scope=fund_accountant_scope_code,\n",
    "        code=portfolio_name,\n",
    "        effective_at=this_morning,\n",
    "        adjust_holding_request=holding_adjustments)\n",
    "        \n",
    "    prettyprint.adjust_holdings_response(\n",
    "        response, \n",
    "        fund_accountant_scope_code, \n",
    "        portfolio_name)\n",
    "        \n",
    "print ('All holdings adjusted')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Off the back of this adjustment we now have an updated set of portfolios in the fund accountant scope. We are unsure if these reconcile with our internal records or not."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![Scopes](img/transparency-fundaccountantreport.gif)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 5) Reconcile our internal records with those of the fund accountant"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now that we have the fund accountant scope updated with this morning's report, we need to see how different the fund accountant's view of our position is with our own internal records.\n",
    "\n",
    "We can do this by reconciling across all the portfolios inside the two scopes. We can only reconcile one portfolio at a time.\n",
    "\n",
    "We can complete a reconciliation by defining the two portfolios to compare as well as the effective date and the as at date to use to view the holdings of each portfolio.\n",
    "\n",
    "LUSID uses a bi-temporal data store. This means that not only can you reconcile two portfolios as they look today, you can also compare how one looks today, compared with how the other looked last week.\n",
    "\n",
    "This makes reconciliation much easier and we will take advantage of this if we come across any discrepancies between our internal accounts and fund accountant's records.\n",
    "\n",
    "*Run the cell below to complete reconciliations across all of our portfolios*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "pycharm": {
     "is_executing": false
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Initialise our reconiled portfolios to dictionary to hold any reconciliation breaks\n",
    "reconciled_portfolios = {}\n",
    "\n",
    "# Iterate over our portfolios\n",
    "for portfolio_name in fund_accountant_daily_holdings_report:\n",
    "\n",
    "    # Define our internal portfolio\n",
    "    internal_portfolio = models.PortfolioReconciliationRequest(\n",
    "        portfolio_id=models.ResourceId(\n",
    "            scope=internal_scope_code,\n",
    "            code=portfolio_name),\n",
    "        effective_at=this_morning,\n",
    "        as_at=datetime.now(pytz.UTC).isoformat())\n",
    "\n",
    "    # Define our fund accountant portfolio\n",
    "    fund_accountant_portfolio = models.PortfolioReconciliationRequest(\n",
    "        portfolio_id=models.ResourceId(\n",
    "            scope=fund_accountant_scope_code,\n",
    "            code=portfolio_name),\n",
    "        effective_at=this_morning,\n",
    "        as_at=datetime.now(pytz.UTC).isoformat())\n",
    "\n",
    "    # Create our reconciliation request\n",
    "    reconcile_holdings_request = models.PortfoliosReconciliationRequest(\n",
    "        left=internal_portfolio,\n",
    "        right=fund_accountant_portfolio,\n",
    "        instrument_property_keys=[\"Instrument/default/Name\"])\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",
    "    # If there are any breaks, add them all to our dictionary\n",
    "    if len(reconciliation.values) > 0:\n",
    "        reconciled_portfolios[portfolio_name] = reconciliation\n",
    "            \n",
    "prettyprint.reconciliation(reconciled_portfolios, )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Okay so looking over our reconciliations we can see that 2 of our portfolios do not reconcile. This is Client A's balanced portfolio and Client B's balanced portfolio. All other portfolios are in sync and so we have coloured them light blue again."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![Scopes](img/transparency-reconciliationbreak.gif)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Looking over the reconciliation breaks above we can see that the discrepancy in Client A's balanced portfolio is due to our fund accountant portfolio having 10501 fewer units of an instrument than our own records. We have 87982 units of this instrument according to our records, however the fund accountant only has 77481 units. There is also a difference in the cash balance in the other direction.\n",
    "\n",
    "This seems to indicate that perhaps a Buy transaction has not gone through.\n",
    "\n",
    "For Client B's balanced portfolio we can see that the discrepancy is due to our fund accountant portfolio having 342000 more units of an instrument than our own records. We have 1,864,441 units of this instrument according to our records, and the fund accountant has 2,206,441 units. Once again there is also a difference in the cash balance in the opposite direction.\n",
    "\n",
    "This seems to indicatethat perhaps a Sell transaction has not gone through.\n",
    "\n",
    "So perhaps in the fund accountant's record there is a Buy transaction missing from Client A's balanced portfolio and a Sell transaction missing from Client B's balanced portfolio. We would now need to try and identify the transaction, perhaps using a spreadsheet or similar tool.\n",
    "\n",
    "This is the sort of manual work that our 'shadow' accountant team and investment managers are currently having to do manually every morning. Let's see if we can automate this."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 6) Build a system to automatically identify and flag discrepancies"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So we have identified a number of discrepancies between our internal records and the fund accountant's records. It seems as though perhaps a buy and a sell transaction have not gone through.\n",
    "\n",
    "We do know that the fund accountant finalise their accounts at the close of trade which is 8 hours after trading opens each day. Any transactions posted after that won't appear in the next morning's report. Perhaps we had some late trades yesterday which caused the issue.\n",
    "\n",
    "We can wind back the clock to the close of trade yesterday and see how our two records compare.\n",
    "\n",
    "*Run the cell below to wind back the clock to the close of trade yesterday and compare our two books of record*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "pycharm": {
     "is_executing": false
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "trade_close_time = (yesterday_trade_open + timedelta(hours=8)).isoformat()\n",
    "\n",
    "# Initialise our dictionary to hold our reconciliation breaks\n",
    "reconciled_portfolios_trade_close = {}\n",
    "    \n",
    "# Iterate over our portfolios\n",
    "for portfolio_name in fund_accountant_daily_holdings_report:\n",
    "\n",
    "    # Internal portfolio\n",
    "    internal_portfolio = models.PortfolioReconciliationRequest(\n",
    "        portfolio_id=models.ResourceId(\n",
    "            scope=internal_scope_code,\n",
    "            code=portfolio_name),\n",
    "        effective_at=trade_close_time,\n",
    "        as_at=datetime.now(pytz.UTC).isoformat())\n",
    "    \n",
    "    # Fund accountant portfolio\n",
    "    fund_accountant_portfolio = models.PortfolioReconciliationRequest(\n",
    "        portfolio_id=models.ResourceId(\n",
    "            scope=fund_accountant_scope_code,\n",
    "            code=portfolio_name),\n",
    "        effective_at=this_morning,\n",
    "        as_at=datetime.now(pytz.UTC).isoformat())\n",
    "\n",
    "    # Create our reconciliation request\n",
    "    reconcile_holdings_request = models.PortfoliosReconciliationRequest(\n",
    "        left=internal_portfolio,\n",
    "        right=fund_accountant_portfolio,\n",
    "        instrument_property_keys=[])\n",
    "\n",
    "    # Reconcile our two portfolios\n",
    "    reconciliation = api_factory.build(lusid.api.ReconciliationsApi).reconcile_holdings(\n",
    "        portfolios_reconciliation_request=reconcile_holdings_request)\n",
    "\n",
    "    # If there are any breaks, add them all to our dictionary\n",
    "    if len(reconciliation.values) > 0:\n",
    "        reconciled_portfolios_trade_close[portfolio_name] = reconciliation\n",
    "\n",
    "prettyprint.reconciliation(reconciled_portfolios_trade_close, True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here we can see that the two portfolios reconcile perfectly. Let's find out what happened after trading. So any divergence between our records and those of the fund accountant happened after the close of trading. Let's take a look at what transactions happened after this time.\n",
    "\n",
    "We can do this by looping over our portfolios and retrieving any transactions that happened between the close of trade and this morning before trade begins.\n",
    "\n",
    "*Run the cell below to identify late trades*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "pycharm": {
     "is_executing": false
    }
   },
   "outputs": [],
   "source": [
    "late_trades = {}\n",
    "\n",
    "for portfolio_name in fund_accountant_daily_holdings_report:\n",
    "\n",
    "    late_trade = api_factory.build(lusid.api.TransactionPortfoliosApi).get_transactions(\n",
    "        scope=internal_scope_code,\n",
    "        code=portfolio_name,\n",
    "        from_transaction_date=trade_close_time,\n",
    "        to_transaction_date=this_morning)\n",
    "    \n",
    "    if len(late_trade.values) > 0:\n",
    "        late_trades[portfolio_name] = late_trade\n",
    "\n",
    "prettyprint.trades(late_trades)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We have identifed transactions made after the close of trade"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![Scopes](img/transparency-latetrades.gif)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can see that there were two lates trades yesterday. Let's see if these transactions match up with our reconciliation breaks. We can do this by checking if the instrument Id and the number of units match. If so we can be reasonably confident that the late trade is the cause of the reconciliation break. We can add further matching details to increase our confidence as we see fit.\n",
    "\n",
    "*Run the cell below to see if our late trades match with either of our reconciliation breaks*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "pycharm": {
     "is_executing": false
    }
   },
   "outputs": [],
   "source": [
    "# Initialise our dictionary to hold our exceptions\n",
    "matched_exceptions = []\n",
    "  \n",
    "# Iterate over our portfolios and the late trades we have identified\n",
    "for portfolio_name, late_trade in late_trades.items():\n",
    "    # Loop over each late trade\n",
    "    for trade in late_trade.values:\n",
    "        # Check if it matches a reconciliation break\n",
    "        for reconciliation_break in reconciled_portfolios[portfolio_name].values:\n",
    "            '''\n",
    "            Here we use the absolute difference in units to reduce the complexity in identifying if a\n",
    "            transaction is a buy or a sell and whether we need to match a positive or negative difference in\n",
    "            units. In practice we can add in the ability to check the direction as well\n",
    "            '''\n",
    "            units = abs(reconciliation_break.difference_units)\n",
    "            instrument_uid = reconciliation_break.instrument_uid\n",
    "\n",
    "            # If the instrument id and units match, we have identified the cause of the reconciliation break\n",
    "            if (trade.instrument_uid == instrument_uid) and (trade.units == units):\n",
    "                matched_exceptions.append((trade, reconciliation_break, portfolio_name))\n",
    "\n",
    "prettyprint.exceptions(matched_exceptions)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here we can see that our two late trades match the instrument reconciliation breaks that we have identified.\n",
    "\n",
    "Now that we have identified the reason for the reconciliation breaks, we want to flag the transactions in question so that we can confirm using tomorrow's report whether or not they have finally been included in the fund accountant's reporting.\n",
    "\n",
    "To do this we can use LUSID properties. We can create properties for any object inside LUSID. These properties are defined by us in advance.\n",
    "\n",
    "In this case we will create a property called 'late_trade' which will be a boolean property that is True if the trade has been flagged as a late trade. We will make it so that if we add this property to a trade it is required to have the value.\n",
    "\n",
    "When we set up a property we are required to give it a data type. We do this by specifying a pre-configured data type using its ResourceId. Once again this is the scope and code of the object. There are a number of pre-configured types in the default scope which we can draw from. In this case we will use the boolean type. This means that when we set the 'late_trade' property we have to specify a value of 'True' or 'False'.\n",
    "\n",
    "*Run the cell below to set a late_trade = True property on our late trades*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "property = models.CreatePropertyDefinitionRequest(\n",
    "    domain='Transaction',\n",
    "    scope=internal_scope_code,\n",
    "    code='late_trade',\n",
    "    value_required=True,\n",
    "    display_name='late_trade',\n",
    "    data_type_id=models.ResourceId(\n",
    "        scope='system',\n",
    "        code='boolean'))\n",
    "\n",
    "api_factory.build(lusid.api.PropertyDefinitionsApi).create_property_definition(\n",
    "    create_property_definition_request=property)\n",
    "\n",
    "for exception in matched_exceptions:\n",
    "    \n",
    "    transaction_id = exception[0].transaction_id\n",
    "    portfolio_name = exception[2]\n",
    "    property_key = 'Transaction/{}/late_trade'.format(internal_scope_code)\n",
    "    r = api_factory.build(lusid.api.TransactionPortfoliosApi).upsert_transaction_properties(\n",
    "        scope=internal_scope_code,\n",
    "        code=portfolio_name,\n",
    "        transaction_id=transaction_id,\n",
    "        request_body={\n",
    "            property_key: \n",
    "                models.PerpetualProperty(\n",
    "                    key=property_key,\n",
    "                    value=models.PropertyValue(label_value='True')\n",
    "                )\n",
    "        })\n",
    "\n",
    "    prettyprint.add_property_response(\n",
    "        r, \n",
    "        internal_scope_code, \n",
    "        portfolio_name, \n",
    "        transaction_id)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We now have the ability to automatically update our fund accountant record each day with their daily report, reconcile their understanding of our holdings against our own and automatically identify any discrepancies caused by late trades.\n",
    "\n",
    "We can build on this to automatically identify and flag other causes of discrepencies. This will allow us to reduce the resources assigned to our 'shadow' accounting team and free up our portfolio managers.\n",
    "\n",
    "Our final state is shown in the diagram below."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![Scopes](img/transparency-matchedexceptions.gif)"
   ]
  }
 ],
 "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.7-final"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}