docs/Check for price outliers.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "mdt28",
   "metadata": {},
   "source": [
    "# Check for price outliers\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": "mds280",
   "metadata": {},
   "source": [
    "#### Step 0: Create instrument property definitions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ccs280",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%luminesce\n",
    "\n",
    "-- ===============================================================\n",
    "-- Description:\n",
    "-- In this query, we make properties available to Luminesce\n",
    "-- by writing them to the Lusid.Property.Definition provider.\n",
    "-- See the following page for further details:\n",
    "-- https://support.lusid.com/knowledgebase/article/KA-01702/en-us\n",
    "-- ===============================================================\n",
    "\n",
    "-- 1. Define new properties\n",
    "\n",
    "@newProperties =\n",
    "values\n",
    "   ('Instrument/ibor/Sector', 'Text', 'Sector', 'The sector that the instrument belongs to.');\n",
    "\n",
    "@property_definition =\n",
    "select Column3 as [DisplayName], 'Instrument' as [Domain], 'ibor' as [PropertyScope], Column3 as [PropertyCode], 'Property' as\n",
    "   [ConstraintStyle], 'system' as [DataTypeScope], case\n",
    "      when Column2 == 'Text'\n",
    "         then 'string'\n",
    "      else 'number'\n",
    "      end as [DataTypeCode]\n",
    "from @newProperties;\n",
    "\n",
    "-- 2. Write new properties to Lusid.Property.Definition provider\n",
    "\n",
    "@create_properties =\n",
    "select *\n",
    "from Lusid.Property.Definition.Writer\n",
    "where ToWrite = @property_definition;\n",
    "\n",
    "-- 3. The results of writing the new property definitions can be seen from the query below:\n",
    "\n",
    "select *\n",
    "from @create_properties;\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "mds281",
   "metadata": {},
   "source": [
    "#### Step 1: Setup instruments with properties"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ccs281",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%luminesce\n",
    "\n",
    "-- ============================================================\n",
    "-- Description:\n",
    "-- In this query we setup some equity instruments\n",
    "-- NOTE: You'll need to have \"Sector\" setup as an instrument\n",
    "-- property in LUSID and Luminesce as follows:\n",
    "-- Instrument/ibor/Sector\n",
    "-- ============================================================\n",
    "-- Load data from CSV\n",
    "@instruments_data =\n",
    "\n",
    "use Drive.Excel\n",
    "--file=/luminesce-examples/price_time_series.xlsx\n",
    "--worksheet=instrument\n",
    "enduse;\n",
    "\n",
    "-- 1. Upload values for custom instrument properties\n",
    "-- Transform data\n",
    "@ids =\n",
    "select inst_id as Id\n",
    "from @instruments_data;\n",
    "\n",
    "@inst_properties =\n",
    "select li.LusidInstrumentId as EntityId, 'LusidInstrumentId' as EntityIdType, 'Instrument' as Domain, 'ibor' as PropertyScope, a.PropertyCode, a.\n",
    "   Value\n",
    "from Lusid.Instrument li\n",
    "inner join (\n",
    "   select 'Sector' as PropertyCode, sector as Value, inst_id as EntityId\n",
    "   from @instruments_data\n",
    "   ) a\n",
    "   on li.ClientInternal = a.EntityId\n",
    "where li.ClientInternal in @ids;\n",
    "\n",
    "-- Write data to Lusid.Property\n",
    "select *\n",
    "from Lusid.Property.Writer\n",
    "where ToWrite = @inst_properties;\n",
    "\n",
    "-- 2. Upload instrument equity data to inbuilt properties\n",
    "-- Transform equity data\n",
    "@equity_instruments =\n",
    "select inst_id as ClientInternal, name as DisplayName, ccy as InferredDomCcy\n",
    "from @instruments_data;\n",
    "\n",
    "-- Write data to Lusid.Instrument.Equity. Print results of writing data to console.\n",
    "select *\n",
    "from Lusid.Instrument.Equity.Writer\n",
    "where ToWrite = @equity_instruments\n",
    "   and DeletePropertiesWhereNull = True;\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "mds282",
   "metadata": {},
   "source": [
    "#### Step 2: Upload quotes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ccs282",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%luminesce\n",
    "\n",
    "-- ============================================================\n",
    "-- Description:\n",
    "-- In this query we load a time series of equity prices\n",
    "-- Some of the prices will have outlier values\n",
    "-- ============================================================\n",
    "-- 1. Load data from CSV\n",
    "@quotes_data = use Drive.Excel\n",
    "--file=/luminesce-examples/price_time_series.xlsx\n",
    "--worksheet=price_time_series\n",
    "enduse;\n",
    "\n",
    "-- 2. Transform quotes data\n",
    "@quotes_for_upload =\n",
    "    select\n",
    "        'ClientInternal' as InstrumentIdType,\n",
    "        instrument_id as Instrumentid,\n",
    "        'luminesce-examples' as QuoteScope,\n",
    "        'Price' as QuoteType,\n",
    "        'Lusid' as Provider,\n",
    "        'Mid' as Field,\n",
    "        price_date as QuoteEffectiveAt,\n",
    "        price as Value,\n",
    "        ccy as Unit\n",
    "    from @quotes_data;\n",
    "\n",
    "-- 3. Upload quotes into LUSID. Print results of upload to console.\n",
    "select *\n",
    "from Lusid.Instrument.Quote.Writer\n",
    "where ToWrite = @quotes_for_upload;\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "mds283",
   "metadata": {},
   "source": [
    "#### Step 3: Create iqr checker view"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ccs283",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%luminesce\n",
    "\n",
    "-- ============================================================\n",
    "-- Description:\n",
    "-- Here we build a view which will return outliers for a given\n",
    "-- instrument and date range. We define outliers as observations\n",
    "-- that fall below Q1 - 1.5 IQR or above Q3 + 1.5 IQR\n",
    "-- ============================================================\n",
    "-- 1. Create view and set parameters\n",
    "@outlier_view = use Sys.Admin.SetupView\n",
    "--provider=Custom.PriceCheck.OnePointFiveIQR\n",
    "--parameters\n",
    "StartDate,Date,2022-01-01,true\n",
    "EndDate,Date,2022-12-31,true\n",
    "InstId,Text,EQ56JD720345,true\n",
    "----\n",
    "\n",
    "@@StartDate = select #PARAMETERVALUE(StartDate);\n",
    "@@EndDate = select #PARAMETERVALUE(EndDate);\n",
    "@@InstId = select #PARAMETERVALUE(InstId);\n",
    "\n",
    "-- 2. Collect quotes for instrument\n",
    "\n",
    "@quotes_data = select *\n",
    "    from Lusid.Instrument.Quote\n",
    "    where QuoteScope='luminesce-examples'\n",
    "        and InstrumentIdType = 'ClientInternal'\n",
    "        and QuoteType = 'Price'\n",
    "        and InstrumentId = @@InstId\n",
    "        and QuoteEffectiveAt between @@StartDate and @@EndDate;\n",
    "\n",
    "-- 3. Collect instrument static and join on data for sector instrument property\n",
    "\n",
    "@instrument_data = select\n",
    "    i.ClientInternal,\n",
    "    i.DisplayName,\n",
    "    p.Value as Sector,\n",
    "    i.InferredAssetClass as [AssetClass]\n",
    "    from Lusid.Instrument.Equity i\n",
    "    join Lusid.Instrument.Property p\n",
    "        on p.InstrumentId=i.LusidInstrumentId\n",
    "    where i.ClientInternal = @@InstId\n",
    "        and propertyscope = 'ibor'\n",
    "        and propertycode = 'Sector';\n",
    "\n",
    "-- 4. Generate time series\n",
    "\n",
    "@price_ts = select\n",
    "    ClientInternal,\n",
    "    DisplayName,\n",
    "    Sector,\n",
    "    AssetClass,\n",
    "    QuoteEffectiveAt as [PriceDate],\n",
    "    Unit as [Currency],\n",
    "    Value as [Price]\n",
    "    from @instrument_data i\n",
    "    join @quotes_data q on (i.ClientInternal = q.InstrumentId);\n",
    "\n",
    "-- 5. Run IQR checks\n",
    "\n",
    "@iqr_data = select\n",
    "    interquartile_range(price) * (1.5) as [iqr_x1_5],\n",
    "    quantile(price, 0.25) as [q1],\n",
    "    quantile(price, 0.75) as [q3]\n",
    "    from @price_ts;\n",
    "\n",
    "-- 6. Define and upper and lower limit for our price check\n",
    "\n",
    "@@upper_limit = select (q3 + iqr_x1_5 ) from  @iqr_data;\n",
    "@@lower_limit = select (q1 - iqr_x1_5 ) from  @iqr_data;\n",
    "\n",
    "-- 7. Print upper and lower limits to console\n",
    "\n",
    "@@upper_limit_log = select print('Upper limit for outlier check: {X:00000} ', '', 'Logs', @@upper_limit);\n",
    "@@lower_limit_log = select print('Lower limit for outlier check: {X:00000} ', '', 'Logs', @@lower_limit);\n",
    "\n",
    "select\n",
    "    PriceDate,\n",
    "    ClientInternal,\n",
    "    DisplayName,\n",
    "    @@upper_limit as [UpperLimit],\n",
    "    @@lower_limit as [LowerLimit],\n",
    "    Price,\n",
    "    'Outlier' as Result\n",
    "from @price_ts\n",
    "where price not between @@lower_limit and @@upper_limit;\n",
    "\n",
    "enduse;\n",
    "\n",
    "select * from @outlier_view ;\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "mds284",
   "metadata": {},
   "source": [
    "#### Step 4: Create price outlier view"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ccs284",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%luminesce\n",
    "\n",
    "-- ============================================================\n",
    "-- Description:\n",
    "-- Here we build a view which will return outlier for all\n",
    "-- Equities in a given sector between two date ranges\n",
    "-- ============================================================\n",
    "-- 1. Create view and set parameters\n",
    "@price_check_view =\n",
    "\n",
    "use Sys.Admin.SetupView\n",
    "--provider=DataQc.OutlierCheck.Prices\n",
    "--parameters\n",
    "StartDate,Date,2022-01-01,true\n",
    "EndDate,Date,2022-12-31,true\n",
    "Sector,Text,Technology,true\n",
    "AssetClass,Text,Equity,true\n",
    "----\n",
    "\n",
    "@@Sector = select #PARAMETERVALUE(Sector);\n",
    "@@StartDate = select #PARAMETERVALUE(StartDate);\n",
    "@@EndDate = select #PARAMETERVALUE(EndDate);\n",
    "@@AssetClass = select #PARAMETERVALUE(AssetClass);\n",
    "\n",
    "-- 2. Collect quotes for instrument\n",
    "\n",
    "@instrument_data =\n",
    "    select\n",
    "        i.ClientInternal\n",
    "    from Lusid.Instrument.Property p\n",
    "    join Lusid.Instrument.Equity i\n",
    "        on p.InstrumentId = i.LusidInstrumentId\n",
    "    where i.[Type]=@@AssetClass\n",
    "        and p.propertyscope = 'ibor'\n",
    "        and p.propertycode = 'Sector'\n",
    "        and p.value = @@Sector;\n",
    "\n",
    "-- 3. Collect instrument static and print view of the outliers for the given sector & date range to console\n",
    "\n",
    "select\n",
    "    i.ClientInternal,\n",
    "    r.DisplayName,\n",
    "    r.PriceDate,\n",
    "    r.Price,\n",
    "    r.LowerLimit,\n",
    "    r.UpperLimit,\n",
    "    r.Result\n",
    "from\n",
    "    @instrument_data i\n",
    "    cross apply\n",
    "    (\n",
    "        select * from\n",
    "        Custom.PriceCheck.OnePointFiveIQR iqr\n",
    "        where iqr.StartDate = @@StartDate\n",
    "        and iqr.EndDate = @@EndDate\n",
    "    ) r\n",
    "    where r.ClientInternal = i.ClientInternal\n",
    "\n",
    "enduse;\n",
    "\n",
    "select *\n",
    "from @price_check_view;\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "mds285",
   "metadata": {},
   "source": [
    "#### Step 5: Run price outlier view"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ccs285",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%luminesce\n",
    "\n",
    "-- ============================================================\n",
    "-- Description:\n",
    "-- This is the high level query which an end-user might run\n",
    "-- to collect all outlier prices between two dates for\n",
    "-- a Sector\n",
    "-- ============================================================\n",
    "\n",
    "select *\n",
    "from DataQc.OutlierCheck.Prices\n",
    "where Sector = 'Technology'\n",
    "and AssetClass= 'Equity'\n",
    "and StartDate = #2022-01-01#\n",
    "and EndDate = #2022-08-26#\n",
    "\n"
   ]
  }
 ],
 "metadata": {},
 "nbformat": 4,
 "nbformat_minor": 5
}