docs/Cross sectional outliers.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "mdt30",
   "metadata": {},
   "source": [
    "# Cross sectional 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": "mds300",
   "metadata": {},
   "source": [
    "#### Step 0: Create properties"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ccs300",
   "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','ibor', 'Sector', 'The sector that the instrument belongs to.'),\n",
    "   ('Instrument/Fundamentals/pe_ratio','number','Fundamentals','pe_ratio','The P/E ratio.');\n",
    "\n",
    "@property_definition =\n",
    "select Column4 as [DisplayName], 'Instrument' as [Domain], Column3 as [PropertyScope], Column4 as [PropertyCode], 'Property' as\n",
    "   [ConstraintStyle], 'system' as [DataTypeScope], \n",
    "   case\n",
    "      when Column2 == 'Text'\n",
    "         then 'string'\n",
    "      else 'number'\n",
    "      end as [DataTypeCode],\n",
    "    case\n",
    "      when Column4 == 'pe_ratio'\n",
    "         then 'TimeVariant'\n",
    "      else 'Perpetual'\n",
    "      end as [Lifetime]\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"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "mds301",
   "metadata": {},
   "source": [
    "#### Step 1: Upload instrumets"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ccs301",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%luminesce\n",
    "\n",
    "-- ============================================================\n",
    "-- Description:\n",
    "-- In this query we setup some instruments with a P/E property \n",
    "-- and a Sector proprerty\n",
    "-- NOTE: You'll need to have the properties setup as an instrument\n",
    "-- properties in LUSID and Luminesce as follows:\n",
    "-- Instrument/ibor/Sector\n",
    "-- Instrument/Fundamentals/pe_ratio\n",
    "-- ============================================================\n",
    "\n",
    "@@Scope = select 'PriceEarningRatioScope';\n",
    "\n",
    "@instruments_data =\n",
    "\n",
    "use Drive.Excel\n",
    "--file=/luminesce-examples/PEdata.xlsx\n",
    "--worksheet=instrument\n",
    "enduse;\n",
    "\n",
    "-- 1. Upload values for custom instrument properties\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, a.PropertyScope as PropertyScope, a.PropertyCode, a.\n",
    "  Value, @@Scope as EntityScope\n",
    "from Lusid.Instrument li\n",
    "inner join (\n",
    "  select 'pe_ratio' as PropertyCode, 'Fundamentals' as PropertyScope, pe_ratio 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",
    "union\n",
    "select li.LusidInstrumentId as EntityId, 'LusidInstrumentId' as EntityIdType, 'Instrument' as Domain, a.PropertyScope as PropertyScope, a.PropertyCode, a.\n",
    "  Value, @@Scope as EntityScope\n",
    "from Lusid.Instrument li\n",
    "inner join (\n",
    "  select 'Sector' as PropertyCode, 'ibor' as PropertyScope, 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",
    "\n",
    "-- select * from @inst_properties\n",
    "\n",
    "select *\n",
    "from Lusid.Property.Writer\n",
    "where ToWrite = @inst_properties;\n",
    "\n",
    "-- 2. Upload instrument data to inbuilt properties\n",
    "-- Transform equity data\n",
    "@equity_instruments =\n",
    "select inst_id as ClientInternal, name as DisplayName, ccy as DomCcy, @@Scope as Scope \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",
    "   \n",
    "   \n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "mds302",
   "metadata": {},
   "source": [
    "#### Step 2: Create cross sectional outlier view"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ccs302",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%luminesce\n",
    "\n",
    "-- ==========================================================================\n",
    "-- Description:\n",
    "-- Here we build a view which will return all the P/E ratio outliers within\n",
    "-- a threshold of 2 standard deviations from the mean for a given\n",
    "-- EffectiveAt/AsAt date and belonging to the group (Sector = Utility)\n",
    "-- ==========================================================================\n",
    "-- 1. Create view and set parameters\n",
    "\n",
    "@cross_sectional_view =\n",
    "use Sys.Admin.SetupView\n",
    "--provider=DataQc.CrossSectionalOutlierCheck\n",
    "--parameters\n",
    "AsAt,Date,2023-07-06,true\n",
    "EffectiveAt,Date,2023-07-06,true\n",
    "StandardDeviations,Int,2,true\n",
    "PropertyScopes,Text,ibor,true\n",
    "PropertyKeys,Text,Sector,true\n",
    "PropertyValues,Text,Utility,true\n",
    "----\n",
    "@@StandardDeviations = select  #PARAMETERVALUE(StandardDeviations);\n",
    "@@Property_Scope = select #PARAMETERVALUE(PropertyScopes);\n",
    "@@Property_Key = select  #PARAMETERVALUE(PropertyKeys);\n",
    "@@Property_Value = select  #PARAMETERVALUE(PropertyValues);\n",
    "@@AsAt = select #PARAMETERVALUE(AsAt);\n",
    "@@EffectiveAt = select #PARAMETERVALUE(EffectiveAt);\n",
    "\n",
    "-- 2. Collect instruments within property Scope/Code and get instrument pe_ratio propertys\n",
    "\n",
    "@custom_props = \n",
    "select p.InstrumentId, b.propertycode as PCode, b.Value as PVal\n",
    "from Lusid.Instrument.Property p\n",
    "inner join (\n",
    "select InstrumentId, propertycode, Value\n",
    "   from Lusid.Instrument.Property p\n",
    "   where propertyscope = 'Fundamentals' \n",
    "   and propertycode = 'pe_ratio') b\n",
    "   on p.InstrumentId = b.InstrumentId\n",
    "   where p.propertyscope = @@Property_Scope \n",
    "   and p.propertycode = @@Property_Key \n",
    "   and p.Value = @@Property_Value ;\n",
    "\n",
    "@pivoted = \n",
    "use Tools.Pivot with @custom_props\n",
    "--key=PCode\n",
    "--aggregateColumns=PVal\n",
    "enduse;\n",
    "\n",
    "-- 3. Join properties onto Instrument data\n",
    "@instruments = \n",
    "select q.*, p.*\n",
    "from @pivoted p\n",
    "inner join (\n",
    "select *\n",
    "from Lusid.Instrument\n",
    "where Scope = 'PriceEarningRatioScope'\n",
    ") q\n",
    "on q.LusidInstrumentId = p.InstrumentId;\n",
    "\n",
    "\n",
    "-- 3. Calculate group mean & SD\n",
    "@@mean = select avg(pe_ratio) from @instruments;\n",
    "@sd = select power(avg(power(pe_ratio - @@mean, 2)), 0.5) as x from @instruments;\n",
    "@@sd = select x from @sd;\n",
    "\n",
    "@@mean_log = select print('Mean: {X} ', '', 'Logs', @@mean);\n",
    "@@sd_log = select print('SD: {X} ', '', 'Logs', @@sd);\n",
    "\n",
    "-- 4. Filter Instruments that are outliers within x Standard Deviations from the mean\n",
    "select\n",
    "    InstrumentId,DisplayName,pe_ratio,\n",
    "    'Outlier' as Result\n",
    "from @instruments\n",
    "where abs(pe_ratio - @@mean) >= @@StandardDeviations * @@sd\n",
    "\n",
    "enduse;\n",
    "\n",
    "select *\n",
    "from @cross_sectional_view;\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "mds303",
   "metadata": {},
   "source": [
    "#### Step 3: Run cross sectional outlier view"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ccs303",
   "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 P/E Ratio outliers outside 2 Standard Deviations\n",
    "-- of the mean belonging to the property group (Sector = Tech) on \n",
    "-- an AsAt/EffectiveAt\n",
    "-- ============================================================\n",
    "\n",
    "select *\n",
    "from DataQc.CrossSectionalOutlierCheck\n",
    "where PropertyScopes = 'ibor'\n",
    "and PropertyKeys= 'Sector'\n",
    "and PropertyValues= 'Tech'\n",
    "and StandardDeviations = 2\n",
    "and AsAt = '2023-07-06'\n",
    "and EffectiveAt = '2023-07-06'\n",
    "\n",
    "\n"
   ]
  }
 ],
 "metadata": {},
 "nbformat": 4,
 "nbformat_minor": 5
}