docs/Run a reconciliation.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "md20",
   "metadata": {},
   "source": [
    "# Setup\n",
    "\n",
    "You might need to run the cell below to setup data required by the Luminesce commands.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cc20",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import general modules\n",
    "import logging\n",
    "import os\n",
    "import pathlib\n",
    "\n",
    "\n",
    "import lusid_notifications\n",
    "from fbnsdkutilities import ApiClientFactory, ApiConfigurationLoader\n",
    "import lusid_notifications.models as ln_models\n",
    "import json\n",
    "\n",
    "# Import LUSID Drive modules\n",
    "from lusid_drive.utilities import ApiClientFactory as DriveApiClientFactory\n",
    "\n",
    "from runner import create_temp_folder, add_file_to_temp_folder\n",
    "\n",
    "# Create loggers\n",
    "logging.basicConfig(level=logging.INFO)\n",
    "logger = logging.getLogger()\n",
    "\n",
    "unique_folder_name = \"recon-data-bootstrap\"\n",
    "\n",
    "\n",
    "def portfolio_setup_main(api_factory, data_dir):\n",
    "\n",
    "    # Create a new temp folder\n",
    "    logger.debug(f\"Create a new folder: {unique_folder_name}\")\n",
    "    create_temp_folder(api_factory, unique_folder_name)\n",
    "\n",
    "    for file in data_dir.iterdir():\n",
    "\n",
    "        file_name = os.path.basename(file)\n",
    "\n",
    "        if file_name.endswith((\"csv\", \"xlsx\")):\n",
    "\n",
    "            logger.debug(f\"Adding the following file to folder: {file}\")\n",
    "\n",
    "            add_file_to_temp_folder(api_factory, file, unique_folder_name)\n",
    "\n",
    "\n",
    "def create_manual_email_subscription(scope, code):\n",
    "\n",
    "    try:\n",
    "        subs_api.create_subscription(\n",
    "            create_subscription=ln_models.CreateSubscription(\n",
    "                id=ln_models.ResourceId(scope=scope, code=code),\n",
    "                display_name=\"Subscription for holdings rec result\",\n",
    "                description=\"Subscription for holdings rec result\",\n",
    "                status=\"Active\",\n",
    "                matching_pattern=ln_models.MatchingPattern(event_type=\"Manual\",\n",
    "                                                           filter=f\"Message eq '{code}'\"))\n",
    "        )\n",
    "\n",
    "    except lusid_notifications.ApiException as e:\n",
    "        if json.loads(e.body)[\"code\"] == 711:\n",
    "            logging.warning( json.loads(e.body)[\"detail\"])\n",
    "        else:\n",
    "            raise\n",
    "\n",
    "\n",
    "def notifications_setup():\n",
    "\n",
    "    notification_scope = \"ibor\"\n",
    "    notification_codes = [\"HoldingReconFailed\", \"HoldingReconPassed\"]\n",
    "\n",
    "    for code in notification_codes:\n",
    "\n",
    "        create_manual_email_subscription(scope=notification_scope, code=code)\n",
    "        create_email_notification(scope=notification_scope, code=code)\n",
    "\n",
    "\n",
    "def create_email_notification(scope, code):\n",
    "\n",
    "    \"\"\"\n",
    "    IMPORTANT:\n",
    "    This function is used to create Email notifications.\n",
    "    We have disabled the function for CI/CD.\n",
    "    To enable this function, add an email as a param under the\n",
    "    CreateEmailNotification model\n",
    "    \"\"\"\n",
    "\n",
    "    # Notifications don't have a unique id that we can set, so instead search by their description\n",
    "    description = 'Email for results of holding recon'\n",
    "\n",
    "    notifications = notifications_api.list_notifications(scope=scope, code=code)\n",
    "    existing_notification = [i.description for i in notifications.values if i.description == description]\n",
    "\n",
    "    if(not existing_notification):\n",
    "\n",
    "        notifications_api.create_email_notification(\n",
    "                scope=scope, code=code,\n",
    "                create_email_notification=ln_models.CreateEmailNotification(\n",
    "                    description=description,\n",
    "                    subject=f\"Holding reconcilation: {code}\",\n",
    "                    plain_text_body=f\"\"\"\n",
    "                    Holdings reconcilation has finished with status {code}.\n",
    "                    Please see results in the following LUSID drive directory:\n",
    "                    <Enter LUSID Drive location here>\n",
    "                    \"\"\",\n",
    "                    email_address_to=[\"steve.collie@finbourne.com\"] # IMPORTANT - update this email\n",
    "            )\n",
    "        )\n",
    "\n",
    "    else:\n",
    "        logger.warning(f\"Creation skipped because there are already notifications attached to subscription scope:{scope} code:{code}\")\n",
    "\n",
    "if __name__ == \"__main__\":\n",
    "\n",
    "    data_dir = pathlib.Path(__file__).parent.resolve()\n",
    "\n",
    "    # Create API factory\n",
    "    secrets_file = (\n",
    "        pathlib.Path(__file__)\n",
    "        .parent.parent.parent.parent.parent.joinpath(\"runner\", \"secrets.json\")\n",
    "        .resolve()\n",
    "    )\n",
    "\n",
    "    # Notifications API\n",
    "    config = ApiConfigurationLoader.load(lusid_notifications, secrets_file)\n",
    "    naf = ApiClientFactory(lusid_notifications, token=config.access_token, api_secrets_filename=secrets_file)\n",
    "    subs_api = naf.build(lusid_notifications.api.SubscriptionsApi)\n",
    "    notifications_api = naf.build(lusid_notifications.api.NotificationsApi)\n",
    "\n",
    "    # Drive APIs\n",
    "    api_factory = DriveApiClientFactory(token=config.access_token, api_secrets_filename=secrets_file)\n",
    "\n",
    "    # Setup porfolio data in Drive\n",
    "    portfolio_setup_main(api_factory, data_dir)\n",
    "\n",
    "    # Create email notifications\n",
    "\n",
    "    ### NOTE ###\n",
    "    # Uncomment to run.\n",
    "    # This will attempt to generate emails.\n",
    "\n",
    "    #notifications_setup()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "mdt20",
   "metadata": {},
   "source": [
    "# Run a reconciliation\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": "mds200",
   "metadata": {},
   "source": [
    "#### Step 1: Create transaction portfolio"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ccs200",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%luminesce\n",
    "\n",
    "-- ===============================================================\n",
    "-- Description:\n",
    "-- 1. In this query, we create two Transaction Portfolios in LUSID\n",
    "-- ===============================================================\n",
    "\n",
    "-- Defining scope and code variables\n",
    "@@portfolioScope =\n",
    "\n",
    "select 'LuminesceReconExample';\n",
    "\n",
    "@@portfolioCode1 =\n",
    "\n",
    "select 'UkEquityTracker';\n",
    "\n",
    "@@portfolioCode2 =\n",
    "\n",
    "select 'UkEquityActive';\n",
    "\n",
    "@@base_currency =\n",
    "\n",
    "select 'GBP';\n",
    "\n",
    "@@created_date = select #2000-01-01#;\n",
    "\n",
    "-- Define the portfolio data\n",
    "@create_portfolio =\n",
    "\n",
    "select 'Transaction' as PortfolioType, @@portfolioScope as PortfolioScope, @@portfolioCode1 as\n",
    "   PortfolioCode, @@portfolioCode1 as DisplayName, '' as Description, @@created_date as Created,\n",
    "   '' as SubHoldingKeys, @@base_currency as BaseCurrency\n",
    "\n",
    "union all\n",
    "\n",
    "values (\n",
    "   'Transaction', @@portfolioScope, @@portfolioCode2, @@portfolioCode2, '', @@created_date, '',\n",
    "   @@base_currency\n",
    "   );\n",
    "\n",
    "-- Upload the portfolio into LUSID\n",
    "@response_create_portfolio =\n",
    "\n",
    "select *\n",
    "from Lusid.Portfolio.Writer\n",
    "where ToWrite = @create_portfolio;\n",
    "\n",
    "select *\n",
    "from @response_create_portfolio;\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "mds201",
   "metadata": {},
   "source": [
    "#### Step 2: Create instruments"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ccs201",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%luminesce\n",
    "\n",
    "-- =====================================================\n",
    "-- Description:\n",
    "-- 1. In this query, we  load instruments from an Excel\n",
    "-- file into LUSID\n",
    "-- =====================================================\n",
    "\n",
    "@@file_date =\n",
    "\n",
    "select strftime('20221101');\n",
    "\n",
    "@instruments_from_excel =\n",
    "\n",
    "use Drive.Excel with @@file_date\n",
    "--file=/luminesce-examples/equity_holdings_{@@file_date}.xlsx\n",
    "--worksheet=instruments\n",
    "--addFileName\n",
    "enduse;\n",
    "\n",
    "-- Run instruments transformation\n",
    "@instruments_for_upload =\n",
    "\n",
    "select Ticker, Name as DisplayName, ISIN as Isin, 'EQ' || Isin as ClientInternal, SEDOL as Sedol,\n",
    "   Currency as DomCcy, 'Equities' as AssetClass, 'Equities' as SimpleInstrumentType\n",
    "from @instruments_from_excel;\n",
    "\n",
    "-- Upload the transformed data into LUSID\n",
    "select *\n",
    "from Lusid.Instrument.SimpleInstrument.Writer\n",
    "where ToWrite = @instruments_for_upload;\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "mds202",
   "metadata": {},
   "source": [
    "#### Step 3: Create holdings"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ccs202",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%luminesce\n",
    "\n",
    "-- =====================================================\n",
    "-- Description:\n",
    "-- 1. In this query, we load holdings into LUSID for the\n",
    "-- two portfolios.\n",
    "-- =====================================================\n",
    "\n",
    "@@file_date =\n",
    "\n",
    "select strftime('20221101');\n",
    "\n",
    "@@portfolioScope =\n",
    "\n",
    "select 'LuminesceReconExample';\n",
    "\n",
    "@holdings_from_excel =\n",
    "\n",
    "use Drive.Excel with @@file_date\n",
    "--file=/luminesce-examples/equity_holdings_{@@file_date}.xlsx\n",
    "--worksheet=lusid_holdings\n",
    "--addFileName\n",
    "enduse;\n",
    "\n",
    "-- Run holdings transformation\n",
    "@holdings_for_upload =\n",
    "\n",
    "select portfolio as [PortfolioCode], @@portfolioScope as [PortfolioScope], holding_date as\n",
    "   [EffectiveAt], 'EQ' || InstrumentId as [ClientInternal], units as [Units], Currency as\n",
    "   CostCurrency\n",
    "from @holdings_from_excel;\n",
    "\n",
    "-- Upload the transformed data into LUSID\n",
    "select *\n",
    "from Lusid.Portfolio.Holding.Writer\n",
    "where ToWrite = @holdings_for_upload;\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "mds203",
   "metadata": {},
   "source": [
    "#### Step 4: Create reconciliation view"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ccs203",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%luminesce\n",
    "\n",
    "-- ============================================================\n",
    "-- Description:\n",
    "-- 1. In this query, we create a view to run the reconciliation\n",
    "-- ============================================================\n",
    "\n",
    "\n",
    "@recon_view =\n",
    "\n",
    "use Sys.Admin.SetupView\n",
    "--provider=Test.Example.HoldingsRecon\n",
    "\n",
    "--parameters\n",
    "file_name,Text,equity_holdings_20221101.xlsx,true\n",
    "portfolio,Text,UkEquityTracker,true\n",
    "scope,Text,LuminesceReconExample,true\n",
    "recon_date,Date,2022-03-01,true\n",
    "----\n",
    "\n",
    "@@file_name = select #PARAMETERVALUE(file_name);\n",
    "@@portfolio = select #PARAMETERVALUE(portfolio);\n",
    "@@scope = select #PARAMETERVALUE(scope);\n",
    "@@recon_date = select #PARAMETERVALUE(recon_date);\n",
    "\n",
    "\n",
    "@ext_holdings_from_excel =\n",
    "\n",
    "use Drive.Excel with @@file_name\n",
    "--file=/luminesce-examples/{@@file_name}\n",
    "--worksheet=ext_holdings\n",
    "--addFileName\n",
    "enduse;\n",
    "\n",
    "-- Get LUSID holdings\n",
    "\n",
    "@holdings_from_lusid = select\n",
    "h.PortfolioCode,\n",
    "i.DisplayName as [InstrumentName],\n",
    "i.LusidInstrumentId,\n",
    "i.ClientInternal,\n",
    "i.Isin,\n",
    "h.HoldingType,\n",
    "h.Units\n",
    "from Lusid.Portfolio.Holding h\n",
    "join Lusid.Instrument i on (i.LusidInstrumentId = h.LusidInstrumentId)\n",
    "where h.PortfolioScope = @@scope\n",
    "and h.PortfolioCode = @@portfolio\n",
    "and h.EffectiveAt = @@recon_date\n",
    "order by h.HoldingType desc;\n",
    "\n",
    "-- Run a reconciliation\n",
    "\n",
    "select\n",
    "l.PortfolioCode as [Left_PortfolioCode],\n",
    "l.InstrumentName as [Left_InstrumentName],\n",
    "l.Isin as [Left_Isin],\n",
    "l.HoldingType as [Left_HoldingType],\n",
    "l.Units as [Left_Units],\n",
    "r.Units as [Right_Units],\n",
    "(r.Units - l.Units) as [Units_Diff],\n",
    "round(((r.Units *1.0) / l.Units) -1 ,5) as [Units_Diff_Pct]\n",
    "from @holdings_from_lusid l\n",
    "join @ext_holdings_from_excel r on (l.ClientInternal = 'EQ' || r.InstrumentId\n",
    "and r.portfolio = @@portfolio);\n",
    "\n",
    "enduse;\n",
    "\n",
    "\n",
    "-- wait for view/provider to be created on the grid\n",
    "\n",
    "select * from @recon_view wait 20;\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "mds204",
   "metadata": {},
   "source": [
    "#### Step 5: Run recon with notifications"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ccs204",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%luminesce\n",
    "\n",
    "-- =====================================================\n",
    "-- Description:\n",
    "-- 1. In this query, we create event notifications from\n",
    "-- the reconciliation results\n",
    "-- =====================================================\n",
    "\n",
    "-- define strings for a pass and fail reconciliation\n",
    "\n",
    "@@failed_event =\n",
    "\n",
    "select 'HoldingReconFailed';\n",
    "\n",
    "@@passed_event =\n",
    "\n",
    "select 'HoldingReconPassed';\n",
    "\n",
    "-- Count the number of breaks from thr reconciliation\n",
    "-- We use an absolute count to assess if there are\n",
    "-- any breaks or not\n",
    "\n",
    "@recon_result =\n",
    "\n",
    "select sum(abs(Units_Diff)) as [Breaks]\n",
    "from Test.Example.HoldingsRecon\n",
    "where portfolio = 'UkEquityActive';\n",
    "\n",
    "-- If there are breaks, we generate a failed event\n",
    "-- If there are no breaks, we generate a passed event\n",
    "\n",
    "@event =\n",
    "\n",
    "select iif(Breaks = 0, @@passed_event, @@failed_event) as [EventToGenerate]\n",
    "from @recon_result;\n",
    "\n",
    "-- Generate the event in LUSID\n",
    "\n",
    "@@event_to_generate =\n",
    "\n",
    "select EventToGenerate\n",
    "from @event;\n",
    "\n",
    "@event_details =\n",
    "\n",
    "select 'Manual' as [EventType], @@event_to_generate as [Message], ('Holdings recon: ' || @@event_to_generate\n",
    "      ) as [Subject], date () as [EventTime];\n",
    "\n",
    "select *\n",
    "from Notification.Event.Writer\n",
    "where ToWrite = @event_details;\n"
   ]
  }
 ],
 "metadata": {},
 "nbformat": 4,
 "nbformat_minor": 5
}