docs/Instruments.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "mdt16",
   "metadata": {},
   "source": [
    "# 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": "mds160",
   "metadata": {},
   "source": [
    "#### Query instruments"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ccs160",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%luminesce\n",
    "\n",
    "-- ============================================================\n",
    "-- Description:\n",
    "-- 1. In this query, we filter various types of instruments.\n",
    "-- 2. These can be uploaded using examples in this folder.\n",
    "-- 3. We will query three different types of instrument,\n",
    "--    and join the results using union.\n",
    "-- ============================================================\n",
    "\n",
    "-- Select common columns and filter by ClientInternal IDs\n",
    "\n",
    "select DisplayName, ClientInternal, DomCcy, LusidInstrumentId\n",
    "from Lusid.Instrument.Equity\n",
    "where ClientInternal = 'EQ18BCC14487A54'\n",
    "\n",
    "union\n",
    "\n",
    "select DisplayName, ClientInternal, DomCcy, LusidInstrumentId\n",
    "from Lusid.Instrument.SimpleInstrument\n",
    "where ClientInternal = 'SHOPCENCI3'\n",
    "\n",
    "union\n",
    "\n",
    "select DisplayName, ClientInternal, DomCcy, LusidInstrumentId\n",
    "from Lusid.Instrument.TermDeposit\n",
    "where ClientInternal = 'TDDFFFA295'\n",
    "\n",
    "union\n",
    "\n",
    "select DisplayName, ClientInternal, DomCcy, LusidInstrumentId\n",
    "from Lusid.Instrument.Bond\n",
    "where ClientInternal = 'UKT 2 09/07/25 GB00BTHH2R79';\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "mds161",
   "metadata": {},
   "source": [
    "#### Set properties from csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ccs161",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%luminesce\n",
    "\n",
    "-- Define file name, this is the instruments file on Drive\n",
    "\n",
    "@@file_name = select 'example_instruments.csv';\n",
    "\n",
    "-- Load data from Drive\n",
    "\n",
    "@instruments_data =\n",
    "use Drive.Csv with @@file_name\n",
    "--file=/luminesce-examples/{@@file_name}\n",
    "--addFileName\n",
    "enduse;\n",
    "\n",
    "@instProperties =\n",
    "select \n",
    "ClientInternal as EntityId, \n",
    "'ClientInternal' as EntityIdType, \n",
    "'Instrument' as Domain,\n",
    "\"Ratings\" as PropertyScope, \n",
    "RatingType as PropertyCode,\n",
    "Rating as Value,\n",
    "\"Ratings\" as EntityScope\n",
    "from @instruments_data;\n",
    "\n",
    "-- Upload the transformed data into LUSID\n",
    "\n",
    "select *\n",
    "from Lusid.Property.Writer\n",
    "where ToWrite = @instProperties\n",
    "limit 5;\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "mds162",
   "metadata": {},
   "source": [
    "#### Upload bond instruments"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ccs162",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%luminesce\n",
    "\n",
    "-- ============================================================\n",
    "-- Description:\n",
    "-- 1. In this query, we run an ETL process on some instruments.\n",
    "-- 2. First, we load a XML file of bond instruments from Drive.\n",
    "-- 3. Next, we transform the shape of the instrument data.\n",
    "-- 4. Finally we upload the instrument data into LUSID.\n",
    "-- ============================================================\n",
    "\n",
    "-- Extract Bond instrument data from LUSID Drive\n",
    "\n",
    "@instruments_data =\n",
    "use Drive.Xml\n",
    "--file=/luminesce-examples/instruments.xml\n",
    "--nodePath=instruments/bond\n",
    "--columns\n",
    "BondID=bondId\n",
    "Name=name\n",
    "ISIN=isin\n",
    "IssueDate=issueDate\n",
    "Maturity=maturity\n",
    "Currency=crncy\n",
    "CouponRate=couponRate\n",
    "Principal=principal\n",
    "CouponFreq=cpnFreq\n",
    "DayCountConvention=dayCountConvention\n",
    "RollConvention=rollConvention\n",
    "PaymentCalendars=paymentCalendars\n",
    "ResetCalendars=resetCalendars\n",
    "SettleDays=settleDays\n",
    "ResetDays=resetDays\n",
    "enduse;\n",
    "\n",
    "-- Transform data using SQL\n",
    "\n",
    "@instruments = \n",
    "select\n",
    "Name as DisplayName,\n",
    "ISIN as Isin,\n",
    "Name || ' ' || ISIN as ClientInternal,\n",
    "IssueDate as StartDate,\n",
    "CouponRate as CouponRate,\n",
    "Currency as DomCcy,\n",
    "Currency as FlowConventionsCurrency,\n",
    "CouponFreq as FlowConventionsPaymentFrequency,\n",
    "DayCountConvention as FlowConventionsDayCountConvention,\n",
    "RollConvention as FlowConventionsRollConvention,\n",
    "'' as FlowConventionsPaymentCalendars,\n",
    "'' as FlowConventionsResetCalendars,\n",
    "SettleDays as FlowConventionsSettleDays,\n",
    "ResetDays as FlowConventionsResetDays,\n",
    "Principal as Principal,\n",
    "Maturity as MaturityDate\n",
    "from @instruments_data;\n",
    "\n",
    "-- Upload the transformed data into LUSID\n",
    "\n",
    "select *\n",
    "from Lusid.Instrument.Bond.Writer\n",
    "where ToWrite = @instruments;\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "mds163",
   "metadata": {},
   "source": [
    "#### Upload equity instruments"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ccs163",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%luminesce\n",
    "\n",
    "-- ============================================================\n",
    "-- Description:\n",
    "-- 1. In this query, we run an ETL process on some instruments.\n",
    "-- 2. First, we load a CSV file of instruments from Drive.\n",
    "-- 3. Next, we transform the shape of the instrument data.\n",
    "-- 4. Finally we upload the instrument data into LUSID.\n",
    "-- ============================================================\n",
    "\n",
    "-- Extract equity instrument data from LUSID Drive\n",
    "\n",
    "@instruments_data =\n",
    "use Drive.Csv\n",
    "--file=/luminesce-examples/uk_instruments.csv\n",
    "enduse;\n",
    "\n",
    "-- Transform data using SQL\n",
    "\n",
    "@equity_instruments =\n",
    "select\n",
    "Name as DisplayName,\n",
    "ISIN as Isin,\n",
    "ClientInternal as ClientInternal,\n",
    "SEDOL as Sedol,\n",
    "'GBP' as DomCcy\n",
    "from @instruments_data;\n",
    "\n",
    "-- Upload the transformed data into LUSID\n",
    "\n",
    "select *\n",
    "from Lusid.Instrument.Equity.Writer\n",
    "where ToWrite = @equity_instruments;\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "mds164",
   "metadata": {},
   "source": [
    "#### Upload future instruments"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ccs164",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%luminesce\n",
    "\n",
    "-- ============================================================\n",
    "-- Description:\n",
    "-- 1. In this query, we run an ETL process on some instruments.\n",
    "-- 2. First, we load a CSV file of futures instruments from Drive.\n",
    "-- 3. Next, we transform the shape of the instrument data.\n",
    "-- 4. Finally we upload the instrument data into LUSID.\n",
    "-- ============================================================\n",
    "\n",
    "-- Extract Future instrument data from LUSID Drive\n",
    "\n",
    "@instruments_data =\n",
    "use Drive.Csv\n",
    "--file=/luminesce-examples/futures.csv\n",
    "enduse;\n",
    "\n",
    "-- Transform data using SQL\n",
    "@instruments = \n",
    "select\n",
    "-- Contract details\n",
    "contract_code as ContractDetailsContractCode,\n",
    "contract_month as ContractDetailsContractMonth,\n",
    "contract_size as ContractDetailsContractSize,\n",
    "convention as ContractDetailsConvention,\n",
    "country_id as ContractDetailsCountry,\n",
    "fut_name as ContractDetailsDescription,\n",
    "dom_ccy as ContractDetailsDomCcy,\n",
    "exchange_code as ContractDetailsExchangeCode,\n",
    "exchange_name as ContractDetailsExchangeName,\n",
    "ticker_step as ContractDetailsTickerStep,\n",
    "unit_value as ContractDetailsUnitValue,\n",
    "-- Future details\n",
    "start_date as StartDate,\n",
    "maturity_date as MaturityDate,\n",
    "1 as Contracts,\n",
    "id as ClientInternal,\n",
    "fut_name as DisplayName\n",
    "from @instruments_data;\n",
    "\n",
    "-- Create future instrument\n",
    "select *\n",
    "from Lusid.Instrument.Future.Writer\n",
    "where ToWrite = @instruments;\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "mds165",
   "metadata": {},
   "source": [
    "#### Upload fx forward"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ccs165",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%luminesce\n",
    "\n",
    "-- ===============================================================\n",
    "-- Description:\n",
    "-- 1. In this query, we run an ETL process on an Fx Forward.\n",
    "-- 2. First, we load a CSV file of the forward from LUSID drive.\n",
    "-- 3. Next, we transform the shape of the forward data.\n",
    "-- 4. Finally we upload the forward data into LUSID.\n",
    "-- ===============================================================\n",
    "\n",
    "-- Defining scope variable\n",
    "\n",
    "@@instrumentScope = select 'luminesce-examples';\n",
    "\n",
    "-- Load forward into table\n",
    "\n",
    "@fx_data = \n",
    "use Drive.Csv\n",
    "--file=/luminesce-examples/fx_forward.csv\n",
    "enduse;\n",
    "\n",
    "-- Define informtion to upsert from file\n",
    "\n",
    "@fx_forward_instrument =\n",
    "select  SecurityDescription as DisplayName,\n",
    "        Figi as Figi,\n",
    "        TradeDate as StartDate,\n",
    "        SettlementDate as MaturityDate,\n",
    "        Ccy as DomCcy,\n",
    "        Ccy2 as FgnCcy,\n",
    "        CcyAmount as DomAmount,\n",
    "        -1 * Ccy2Amount as FgnAmount,\n",
    "        @@instrumentScope as Scope\n",
    "from @fx_data;\n",
    "\n",
    "-- Upsert Fx Forward to LUSID\n",
    "\n",
    "select * from lusid.Instrument.FxForward.Writer\n",
    "where ToWrite=@fx_forward_instrument;\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "mds166",
   "metadata": {},
   "source": [
    "#### Upload simple instruments"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ccs166",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%luminesce\n",
    "\n",
    "-- ============================================================\n",
    "-- Description:\n",
    "-- 1. In this query, we run an ETL process on some instruments.\n",
    "-- 2. First, we load a Excel file of instruments from Drive.\n",
    "-- 3. Next, we transform the shape of the instrument data.\n",
    "-- 4. Finally we upload the instrument data into LUSID.\n",
    "-- ============================================================\n",
    "\n",
    "-- Extract Simple Instrument data from LUSID Drive\n",
    "\n",
    "@instruments_data =\n",
    "use Drive.Excel\n",
    "--file=/luminesce-examples/instruments.xlsx\n",
    "--worksheet=simple_instruments\n",
    "enduse;\n",
    "\n",
    "-- Transform data using SQL\n",
    "\n",
    "@simple_instruments =\n",
    "select\n",
    "Name as DisplayName,\n",
    "ClientInternal as ClientInternal,\n",
    "Currency as DomCcy,\n",
    "Class as AssetClass,\n",
    "Type as SimpleInstrumentType\n",
    "from @instruments_data;\n",
    "\n",
    "-- Upload the transformed data into LUSID\n",
    "\n",
    "select *\n",
    "from Lusid.Instrument.SimpleInstrument.Writer\n",
    "where ToWrite = @simple_instruments;\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "mds167",
   "metadata": {},
   "source": [
    "#### Upload term deposit instruments"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ccs167",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%luminesce\n",
    "\n",
    "-- ============================================================\n",
    "-- Description:\n",
    "-- 1. In this query, we run an ETL process on some instruments.\n",
    "-- 2. First, we load a Excel file of instruments from Drive.\n",
    "-- 3. Next, we transform the shape of the instrument data.\n",
    "-- 4. Finally we upload the instrument data into LUSID.\n",
    "-- ============================================================\n",
    "\n",
    "-- Extract Term Deposit data from LUSID Drive\n",
    "\n",
    "@term_deposit_file = use Drive.Excel\n",
    "--file=/luminesce-examples/instruments.xlsx\n",
    "--worksheet=term_deposits\n",
    "enduse;\n",
    "\n",
    "-- Transform data using SQL\n",
    "\n",
    "@term_deposit_instruments = select\n",
    "id as ClientInternal,\n",
    "name as DisplayName,\n",
    "start_date as StartDate,\n",
    "mat_date as MaturityDate,\n",
    "rate as Rate,\n",
    "payment_freq as FlowConventionPaymentFrequency,\n",
    "day_count_convention as FlowConventionDayCountConvention,\n",
    "flow_convention as FlowConventionRollConvention,\n",
    "payment_cals as FlowConventionPaymentCalendars,\n",
    "reset_cals as FlowConventionResetCalendars,\n",
    "currency as FlowConventionCurrency\n",
    "from @term_deposit_file;\n",
    "\n",
    "-- Upload the transformed data into LUSID\n",
    "\n",
    "select *\n",
    "from Lusid.Instrument.TermDeposit.Writer\n",
    "where ToWrite = @term_deposit_instruments;\n",
    "\n"
   ]
  }
 ],
 "metadata": {},
 "nbformat": 4,
 "nbformat_minor": 5
}