docs/Reconcile instruments.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "mdt27",
   "metadata": {},
   "source": [
    "# Reconcile instruments\n",
    "\n",
    "You can run the cells below directly in LUSID's JupyterHub.\n",
    "\n",
    "The `%%luminesce` is a magic command which passes the cell query string to Lumipy,\n",
    "which then returns a DataFrame.\n",
    "    "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "mds270",
   "metadata": {},
   "source": [
    "#### Step 1: Create instrument properties"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ccs270",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%luminesce\n",
    "\n",
    "-- ==================================================================\n",
    "-- Description:\n",
    "-- 1. In this example, we create some custom properties in LUSID,\n",
    "-- loaded from an Excel file.\n",
    "-- ===================================================================\n",
    "\n",
    "-- Load a CSV file of properties from LUSID Drive\n",
    "\n",
    "@properties = use Drive.Excel\n",
    "--file=/luminesce-examples/fi_instrument_master.xlsx\n",
    "--worksheet=properties\n",
    "enduse;\n",
    "\n",
    "-- Transform the CSV data into a LUSID format\n",
    "\n",
    "@property_definition = select\n",
    "PropertyCode as [DisplayName],\n",
    "'Instrument' as [Domain],\n",
    "Scope as [PropertyScope],\n",
    "PropertyCode as [PropertyCode],\n",
    "'Property' as [ConstraintStyle],\n",
    "'system' as [DataTypeScope],\n",
    "DataType as [DataTypeCode]\n",
    "from @properties;\n",
    "\n",
    "-- Upload the properties into LUSID\n",
    "\n",
    "select * from Lusid.Property.Definition.Writer\n",
    "where ToWrite = @property_definition;\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "mds271",
   "metadata": {},
   "source": [
    "#### Step 2: Setup instrument with properties"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ccs271",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%luminesce\n",
    "\n",
    "-- ===============================================================\n",
    "-- Description:\n",
    "-- In this file, we load instruments into LUSID with the new\n",
    "-- properties defined in the first file.\n",
    "-- ===============================================================\n",
    "\n",
    "@instruments_source_a = use Drive.Excel\n",
    "--file=/luminesce-examples/fi_instrument_master.xlsx\n",
    "--worksheet=SourceA\n",
    "enduse;\n",
    "\n",
    "-- 1. Upload instruments with default properties to Lusid\n",
    "-- Transform data using Luminesce\n",
    "@fi_instruments = select\n",
    "Name as DisplayName,\n",
    "Currency as DomCcy,\n",
    "'Bonds' as SimpleInstrumentType,\n",
    "'Credit' as AssetClass,\n",
    "ClientInternal,\n",
    "CountryIssue,\n",
    "IssuePrice\n",
    "from @instruments_source_a;\n",
    "\n",
    "-- Upload the transformed data into LUSID\n",
    "@write_instruments =\n",
    "select *\n",
    "from Lusid.Instrument.SimpleInstrument.Writer\n",
    "where ToWrite = @fi_instruments\n",
    "and DeletePropertiesWhereNull=True;\n",
    "\n",
    "-- 2. Upload values for custom properties to Lusid\n",
    "-- Transform data using Luminesce\n",
    "@custom_props =\n",
    "select ClientInternal, CountryIssue, HoldingType, InstrumentType, GICSLevel1,\n",
    "GICSLevel2, SnPRating, MoodyRating, MIFIDFlag\n",
    "from @instruments_source_a;\n",
    "\n",
    "@unpivoted =\n",
    "use Tools.Unpivot with @custom_props\n",
    "--key=ClientInternal\n",
    "--keyIsNotUnique\n",
    "enduse;\n",
    "\n",
    "@instr_props =\n",
    "select li.LusidInstrumentId as EntityId, 'LusidInstrumentId' as EntityIdType, 'Instrument' as Domain,\n",
    "    a.ValueColumnName as PropertyCode, 'SourceA' as PropertyScope, a.ValueText as Value\n",
    "from @unpivoted a\n",
    "inner join Lusid.Instrument li\n",
    "   on li.ClientInternal = a.ClientInternal;\n",
    "\n",
    "-- Upload the transformed data into LUSID\n",
    "@write_props =\n",
    "select *\n",
    "from Lusid.Property.Writer\n",
    "where ToWrite = @instr_props;\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "mds272",
   "metadata": {},
   "source": [
    "#### Step 3: Load instruments into source a"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ccs272",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%luminesce\n",
    "\n",
    "-- ===============================================================\n",
    "-- Description:\n",
    "-- In this file, we load instruments into LUSID with the new\n",
    "-- properties defined in the first file.\n",
    "-- ===============================================================\n",
    "\n",
    "@instruments_source_a = use Drive.Excel\n",
    "--file=/luminesce-examples/fi_instrument_master.xlsx\n",
    "--worksheet=SourceA\n",
    "enduse;\n",
    "\n",
    "-- 1. Upload instruments with default properties to Lusid\n",
    "-- Transform data using Luminesce\n",
    "@fi_instruments = select\n",
    "Name as DisplayName,\n",
    "Currency as DomCcy,\n",
    "'Bonds' as SimpleInstrumentType,\n",
    "'Credit' as AssetClass,\n",
    "ClientInternal,\n",
    "CountryIssue,\n",
    "IssuePrice\n",
    "from @instruments_source_a;\n",
    "\n",
    "-- Upload the transformed data into LUSID\n",
    "@write_instruments =\n",
    "select *\n",
    "from Lusid.Instrument.SimpleInstrument.Writer\n",
    "where ToWrite = @fi_instruments\n",
    "and DeletePropertiesWhereNull=True;\n",
    "\n",
    "-- 2. Upload values for custom properties to Lusid\n",
    "-- Transform data using Luminesce\n",
    "@custom_props =\n",
    "select ClientInternal, HoldingType, InstrumentType, GICSLevel1,\n",
    "GICSLevel2, SnPRating, MoodyRating, MIFIDFlag\n",
    "from @instruments_source_a;\n",
    "\n",
    "@unpivoted =\n",
    "use Tools.Unpivot with @custom_props\n",
    "--key=ClientInternal\n",
    "--keyIsNotUnique\n",
    "enduse;\n",
    "\n",
    "@instr_props =\n",
    "select li.LusidInstrumentId as EntityId, 'LusidInstrumentId' as EntityIdType, 'Instrument' as Domain,\n",
    "    a.ValueColumnName as PropertyCode, 'SourceA' as PropertyScope, a.ValueText as Value\n",
    "from @unpivoted a\n",
    "inner join Lusid.Instrument li\n",
    "   on li.ClientInternal = a.ClientInternal;\n",
    "\n",
    "-- Upload the transformed data into LUSID\n",
    "@write_props =\n",
    "select *\n",
    "from Lusid.Property.Writer\n",
    "where ToWrite = @instr_props;\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "mds273",
   "metadata": {},
   "source": [
    "#### Step 4: Load instruments into source b"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ccs273",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%luminesce\n",
    "\n",
    "-- ===============================================================\n",
    "-- Description:\n",
    "-- In this file, we load instruments into LUSID with the new\n",
    "-- properties defined in the first file.\n",
    "-- ===============================================================\n",
    "\n",
    "@instruments_source_b =\n",
    "use Drive.Excel\n",
    "--file=/luminesce-examples/fi_instrument_master.xlsx\n",
    "--worksheet=SourceB\n",
    "enduse;\n",
    "\n",
    "-- 1. Upload instruments with default properties to Lusid\n",
    "-- Transform data using Luminesce\n",
    "@fi_instruments =\n",
    "select Name as DisplayName, Currency as DomCcy, 'Bonds' as SimpleInstrumentType, 'Credit' as AssetClass,\n",
    "   client_internal as ClientInternal\n",
    "from @instruments_source_b;\n",
    "\n",
    "-- Upload the transformed data into LUSID\n",
    "@write_instruments =\n",
    "select *\n",
    "from Lusid.Instrument.SimpleInstrument.Writer\n",
    "where ToWrite = @fi_instruments\n",
    "   and DeletePropertiesWhereNull = True;\n",
    "\n",
    "-- 2. Upload values for custom properties to Lusid\n",
    "-- Transform data using Luminesce\n",
    "@custom_props =\n",
    "select client_internal, holding_type, instrument_type, gics_level_1, gics_level_2, snp_rating, moody_rating,\n",
    "   mifid_flag, country_issue, issue_price\n",
    "from @instruments_source_b;\n",
    "\n",
    "@unpivoted =\n",
    "use Tools.Unpivot with @custom_props\n",
    "--key=client_internal\n",
    "--keyIsNotUnique\n",
    "enduse;\n",
    "\n",
    "@instr_props =\n",
    "select li.LusidInstrumentId as EntityId, 'LusidInstrumentId' as EntityIdType, 'Instrument' as Domain, 'SourceB' as\n",
    "   PropertyScope, a.ValueColumnName as PropertyCode, a.ValueText as Value\n",
    "from @unpivoted a\n",
    "inner join Lusid.Instrument li\n",
    "   on li.ClientInternal = a.client_internal;\n",
    "\n",
    "-- Upload the transformed data into LUSID\n",
    "@write_props =\n",
    "select *\n",
    "from Lusid.Property.Writer\n",
    "where ToWrite = @instr_props;\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "mds274",
   "metadata": {},
   "source": [
    "#### Step 5: Run reconciliation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ccs274",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%luminesce\n",
    "\n",
    "-- ===============================================================\n",
    "-- Description:\n",
    "-- In this file, we run a reconciliation on three of the properties\n",
    "-- ===============================================================\n",
    "\n",
    "-- 1. Create a view of instruments with custom properties\n",
    "-- Get data from LUSID\n",
    "@instr_props =\n",
    "select li.ClientInternal, li.DisplayName, PropertyCode, Value\n",
    "from Lusid.Instrument.Property p\n",
    "inner join Lusid.Instrument.SimpleInstrument li\n",
    "   on li.LusidInstrumentId = p.InstrumentId\n",
    "where li.SimpleInstrumentType = 'Bonds'\n",
    "   and li.AssetClass = 'Credit'\n",
    "    and ((p.propertyscope  = 'SourceA'\n",
    "    and p.propertycode in ('HoldingType', 'CountryIssue', 'GICSLevel1', 'InstrumentType'))\n",
    "    or \n",
    "    (p.propertyscope  = 'SourceB'\n",
    "    and p.propertycode in ('holding_type', 'country_issue', 'gics_level_1', 'instrument_type'))) ;\n",
    "\n",
    "\n",
    "-- Transform data using luminesce\n",
    "@pivoted =\n",
    "use Tools.Pivot with @instr_props\n",
    "--key=PropertyCode\n",
    "--aggregateColumns=Value\n",
    "enduse;\n",
    "\n",
    "-- 2. Run reconcilliation\n",
    "select ClientInternal, DisplayName, case\n",
    "      when HoldingType = holding_type\n",
    "         then 'Reconciled: ' || HoldingType\n",
    "      else 'Break: ' || HoldingType || ' versus ' || holding_type\n",
    "      end as HoldingType, case\n",
    "      when CountryIssue = country_issue\n",
    "         then 'Reconciled: ' || CountryIssue\n",
    "      else 'Break: ' || CountryIssue || ' versus ' || country_issue\n",
    "      end as CountryIssue, case\n",
    "      when GICSLevel1 = gics_level_1\n",
    "         then 'Reconciled: ' || GICSLevel1\n",
    "      else 'Break: ' || GICSLevel1 || ' versus ' || gics_level_1\n",
    "      end as GICSLevel1\n",
    "from @pivoted\n",
    "where (\n",
    "      HoldingType is not null\n",
    "      and InstrumentType is not null\n",
    "      );\n",
    "\n"
   ]
  }
 ],
 "metadata": {},
 "nbformat": 4,
 "nbformat_minor": 5
}