docs/Abor.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "md11",
   "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": "cc11",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import general modules\n",
    "import logging\n",
    "import lusid\n",
    "import lusid.models as models\n",
    "import argparse\n",
    "\n",
    "# Import LUSID Drive modules\n",
    "from lusid.utilities import ApiClientFactory as LusidApiClientFactory\n",
    "from lusidjam import RefreshingToken\n",
    "\n",
    "# Create loggers\n",
    "logging.basicConfig(level=logging.INFO)\n",
    "logger = logging.getLogger()\n",
    "\n",
    "def create_recipe(api_factory):\n",
    "    recipes_api = api_factory.build(lusid.api.ConfigurationRecipeApi)\n",
    "\n",
    "    scope = \"luminesce-examples\"\n",
    "    recipe_code = \"marketValue\"\n",
    "\n",
    "    configuration_recipe = models.ConfigurationRecipe(\n",
    "        scope=scope,\n",
    "        code=recipe_code,\n",
    "        market=models.MarketContext(\n",
    "            market_rules=[\n",
    "                models.MarketDataKeyRule(\n",
    "                    key=\"Quote.ClientInternal.*\",\n",
    "                    supplier=\"Lusid\",\n",
    "                    data_scope=scope,\n",
    "                    quote_type=\"Price\",\n",
    "                    field=\"mid\",\n",
    "                    quote_interval=\"100D.0D\",\n",
    "                ),\n",
    "                models.MarketDataKeyRule(\n",
    "                    key=\"FX.*.*\",\n",
    "                    supplier=\"Lusid\",\n",
    "                    data_scope=scope,\n",
    "                    quote_type=\"Rate\",\n",
    "                    field=\"mid\",\n",
    "                    quote_interval=\"100D.0D\",\n",
    "                ),\n",
    "            ],\n",
    "            suppliers=models.MarketContextSuppliers(\n",
    "                commodity=\"Client\",\n",
    "                credit=\"Client\",\n",
    "                equity=\"Client\",\n",
    "                fx=\"Client\",\n",
    "                rates=\"Client\",\n",
    "            ),\n",
    "            options=models.MarketOptions(\n",
    "                default_supplier=\"Lusid\",\n",
    "                default_instrument_code_type=\"ClientInternal\",\n",
    "                default_scope=scope,\n",
    "                attempt_to_infer_missing_fx=True,\n",
    "            ),\n",
    "        ),\n",
    "        pricing=models.PricingContext(\n",
    "            model_rules=[\n",
    "                models.VendorModelRule(\n",
    "                    supplier=\"Lusid\", model_name=\"SimpleStatic\", instrument_type=\"Bond\"\n",
    "                )\n",
    "            ]\n",
    "        ),\n",
    "    )\n",
    "\n",
    "    upsert_configuration_recipe_response = recipes_api.upsert_configuration_recipe(\n",
    "        upsert_recipe_request=models.UpsertRecipeRequest(\n",
    "            configuration_recipe=configuration_recipe\n",
    "        )\n",
    "    )\n",
    "\n",
    "    print(upsert_configuration_recipe_response)\n",
    "\n",
    "\n",
    "def create_txn_types(api_factory):\n",
    "\n",
    "    scope = \"luminesce-examples\"\n",
    "    \n",
    "    system_configuration = api_factory.build(lusid.api.SystemConfigurationApi)\n",
    "\n",
    "    response = system_configuration.set_transaction_configuration_source(\n",
    "        source=\"abor\",\n",
    "        set_transaction_configuration_source_request=[\n",
    "            models.SetTransactionConfigurationSourceRequest(\n",
    "                aliases=[\n",
    "                    models.TransactionConfigurationTypeAlias(\n",
    "                        type=\"FundsIn\",\n",
    "                        description=\"Deposit New Funds\",\n",
    "                        transaction_class=\"CashTransfers\",\n",
    "                        transaction_group=\"abor\",\n",
    "                        transaction_roles=\"Longer\",\n",
    "                    )\n",
    "                ],\n",
    "                movements=[\n",
    "                    models.TransactionConfigurationMovementDataRequest(\n",
    "                        name=\"Subscription\",\n",
    "                        movement_types=\"CashReceivable\",\n",
    "                        side=\"Side1\",\n",
    "                        direction=1,\n",
    "                        movement_options=[\"Capital\"],\n",
    "                        mappings=[\n",
    "                            models.TransactionPropertyMappingRequest(\n",
    "                            property_key=f\"Transaction/{scope}/CashType\",\n",
    "                            set_to=\"CashAtBank\"\n",
    "                        \n",
    "                        )\n",
    "                        ]\n",
    "                    )\n",
    "                ],\n",
    "            ),\n",
    "            models.SetTransactionConfigurationSourceRequest(\n",
    "                aliases=[\n",
    "                    models.TransactionConfigurationTypeAlias(\n",
    "                        type=\"FundsOut\",\n",
    "                        description=\"Deposit New Funds\",\n",
    "                        transaction_class=\"CashTransfers\",\n",
    "                        transaction_group=\"abor\",\n",
    "                        transaction_roles=\"Shorter\",\n",
    "                    )\n",
    "                ],\n",
    "                movements=[\n",
    "                    models.TransactionConfigurationMovementDataRequest(\n",
    "                        name=\"Redemption\",\n",
    "                        movement_types=\"CashReceivable\",\n",
    "                        side=\"Side1\",\n",
    "                        direction=-1,\n",
    "                        movement_options=[\"Capital\"],\n",
    "                        mappings=[\n",
    "                            models.TransactionPropertyMappingRequest(\n",
    "                            property_key=f\"Transaction/{scope}/CashType\",\n",
    "                            set_to=\"CashAtBank\"\n",
    "                        \n",
    "                        )]),\n",
    "                \n",
    "                ],\n",
    "            ),\n",
    "            models.SetTransactionConfigurationSourceRequest(\n",
    "                aliases=[\n",
    "                    models.TransactionConfigurationTypeAlias(\n",
    "                        type=\"CashIn\",\n",
    "                        description=\"New cash into portfolio\",\n",
    "                        transaction_class=\"CashTransfers\",\n",
    "                        transaction_group=\"abor\",\n",
    "                        transaction_roles=\"Longer\",\n",
    "                    )\n",
    "                ],\n",
    "                movements=[\n",
    "                    models.TransactionConfigurationMovementDataRequest(\n",
    "                        name=\"CashIn\",\n",
    "                        movement_types=\"CashReceivable\",\n",
    "                        movement_options=[\"Capital\"],\n",
    "                        side=\"Side1\",\n",
    "                        direction=1\n",
    "                    )\n",
    "                ],\n",
    "            ),\n",
    "            models.SetTransactionConfigurationSourceRequest(\n",
    "                aliases=[\n",
    "                    models.TransactionConfigurationTypeAlias(\n",
    "                        type=\"Buy\",\n",
    "                        description=\"Buy\",\n",
    "                        transaction_class=\"Buy\",\n",
    "                        transaction_group=\"abor\",\n",
    "                        transaction_roles=\"AllRoles\",\n",
    "                    )\n",
    "                ],\n",
    "                movements=[\n",
    "                    models.TransactionConfigurationMovementDataRequest(\n",
    "                        name=\"Bought\",\n",
    "                        movement_types=\"StockMovement\",\n",
    "                        side=\"Side1\",\n",
    "                        direction=1,\n",
    "                        movement_options=[],\n",
    "                        mappings=[\n",
    "                            models.TransactionPropertyMappingRequest(\n",
    "                            property_key=f\"Transaction/{scope}/CashType\",\n",
    "                            set_to=\"NonCashInvestments\"\n",
    "                        \n",
    "                        )]\n",
    "                    ),\n",
    "                    models.TransactionConfigurationMovementDataRequest(\n",
    "                        name=\"CashInvested\",\n",
    "                        movement_types=\"CashCommitment\",\n",
    "                        side=\"Side2\",\n",
    "                        direction=-1,\n",
    "                        movement_options=[],\n",
    "                        mappings=[\n",
    "                            models.TransactionPropertyMappingRequest(\n",
    "                            property_key=f\"Transaction/{scope}/CashType\",\n",
    "                            set_to=\"CashAtBank\"\n",
    "                        \n",
    "                        )]\n",
    "                        ,\n",
    "                        \n",
    "                        \n",
    "                    ),\n",
    "                ],\n",
    "            ),\n",
    "            models.SetTransactionConfigurationSourceRequest(\n",
    "                aliases=[\n",
    "                    models.TransactionConfigurationTypeAlias(\n",
    "                        type=\"Sell\",\n",
    "                        description=\"Sell\",\n",
    "                        transaction_class=\"Sell\",\n",
    "                        transaction_group=\"abor\",\n",
    "                        transaction_roles=\"AllRoles\",\n",
    "                    )\n",
    "                ],\n",
    "                movements=[\n",
    "                    models.TransactionConfigurationMovementDataRequest(\n",
    "                        name=\"Sale\",\n",
    "                        movement_types=\"StockMovement\",\n",
    "                        side=\"Side1\",\n",
    "                        direction=-1,\n",
    "                        movement_options=[],\n",
    "                         mappings=[\n",
    "                            models.TransactionPropertyMappingRequest(\n",
    "                            property_key=f\"Transaction/{scope}/CashType\",\n",
    "                            set_to=\"NonCashInvestments\"\n",
    "                        \n",
    "                        )]\n",
    "                    ),\n",
    "                    models.TransactionConfigurationMovementDataRequest(\n",
    "                        name=\"CashProceeds\",\n",
    "                        movement_types=\"CashCommitment\",\n",
    "                        side=\"Side2\",\n",
    "                        direction=1,\n",
    "                        movement_options=[],\n",
    "                        mappings=[\n",
    "                            models.TransactionPropertyMappingRequest(\n",
    "                            property_key=f\"Transaction/{scope}/CashType\",\n",
    "                            set_to=\"CashAtBank\"\n",
    "                        \n",
    "                        )]\n",
    "                    ),\n",
    "                ],\n",
    "            ),\n",
    "            models.SetTransactionConfigurationSourceRequest(\n",
    "                aliases=[\n",
    "                    models.TransactionConfigurationTypeAlias(\n",
    "                        type=\"FxSpotBuy\",\n",
    "                        description=\"FxSpotBuy\",\n",
    "                        transaction_class=\"FxSpotBuy\",\n",
    "                        transaction_group=\"abor\",\n",
    "                        transaction_roles=\"AllRoles\",\n",
    "                    )\n",
    "                ],\n",
    "                movements=[\n",
    "                    models.TransactionConfigurationMovementDataRequest(\n",
    "                        name=\"FxSpotBuyLeg\",\n",
    "                        movement_types=\"CashCommitment\",\n",
    "                        side=\"Side1\",\n",
    "                        direction=1,\n",
    "                        movement_options=[],\n",
    "                        mappings=[\n",
    "                            models.TransactionPropertyMappingRequest(\n",
    "                            property_key=f\"Transaction/{scope}/CashType\",\n",
    "                            set_to=\"CashAtBank\"\n",
    "                        \n",
    "                        )]\n",
    "                    ),\n",
    "                    models.TransactionConfigurationMovementDataRequest(\n",
    "                        name=\"FxSpotSellLeg\",\n",
    "                        movement_types=\"CashCommitment\",\n",
    "                        side=\"Side2\",\n",
    "                        direction=-1,\n",
    "                        movement_options=[],\n",
    "                        mappings=[\n",
    "                            models.TransactionPropertyMappingRequest(\n",
    "                            property_key=f\"Transaction/{scope}/CashType\",\n",
    "                            set_to=\"CashAtBank\"\n",
    "                        \n",
    "                        )]\n",
    "                    ),\n",
    "                ],\n",
    "            ),\n",
    "        ],\n",
    "    )\n",
    "    \n",
    "    return response\n",
    "\n",
    "\n",
    "if __name__ == \"__main__\":\n",
    "    token = RefreshingToken()\n",
    "\n",
    "    if token is not None:\n",
    "        lusid_api_factory = LusidApiClientFactory(token=token)\n",
    "\n",
    "    else:\n",
    "        ap = argparse.ArgumentParser()\n",
    "        ap.add_argument(\"-s\", \"--secrets\", type=str, help=\"full path to json file\")\n",
    "        args = ap.parse_args()\n",
    "        secrets_file = args.secrets\n",
    "        lusid_api_factory = LusidApiClientFactory(api_secrets_filename=secrets_file)\n",
    "\n",
    "    create_recipe(lusid_api_factory)\n",
    "    create_txn_types(lusid_api_factory)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "mdt11",
   "metadata": {},
   "source": [
    "# Abor\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": "mds110",
   "metadata": {},
   "source": [
    "#### Step 01: Create a portfolio"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ccs110",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%luminesce\n",
    "\n",
    "/*\n",
    "\n",
    "------------------------------\n",
    "Create a Transaction portfolio\n",
    "------------------------------\n",
    "\n",
    "In this snippet we load a Transaction portfolio into LUSID.\n",
    "\n",
    "For more details on LUSID providers, see this page:\n",
    "\n",
    "https://support.lusid.com/knowledgebase/category/?id=CAT-01099\n",
    "\n",
    "*/\n",
    "\n",
    "-- Step 1: Define the portfolio details\n",
    "\n",
    "@@scope = select 'luminesce-examples';\n",
    "@@portfolioCode = select 'aborPortfolio';\n",
    "@@writeAction = select 'Upsert';\n",
    "\n",
    "@createPortfolio = select 'Transaction' as PortfolioType,\n",
    "@@scope as PortfolioScope,\n",
    "@@portfolioCode as PortfolioCode,\n",
    "@@scope  as InstrumentScopes,\n",
    "@@portfolioCode as DisplayName,\n",
    "@@portfolioCode  as Description,\n",
    "#2000-01-01# as Created,\n",
    "'' as SubHoldingKeys,\n",
    "'GBP' as BaseCurrency,\n",
    "@@writeAction as WriteAction\n",
    ";\n",
    "\n",
    "-- Step 2: Load portfolio into LUSID\n",
    "\n",
    "select *\n",
    "from Lusid.Portfolio.Writer\n",
    "where ToWrite = @createPortfolio;\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "mds111",
   "metadata": {},
   "source": [
    "#### Step 02: Create equity instruments"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ccs111",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%luminesce\n",
    "\n",
    "/*\n",
    "\n",
    "------------------------------\n",
    "Create some equity instruments\n",
    "------------------------------\n",
    "\n",
    "In this snippet we create some Equity instruments into LUSID.\n",
    "\n",
    "For more details on LUSID providers, see this page:\n",
    "\n",
    "https://support.lusid.com/knowledgebase/category/?id=CAT-01099\n",
    "\n",
    "*/\n",
    "\n",
    "-- Step 1: Define the equity instruments\n",
    "\n",
    "@@scope = select 'luminesce-examples';\n",
    "\n",
    "@instrumentsData= \n",
    "values\n",
    "('Tesco', 'FBNABOR001', 'GBP'),\n",
    "('Sainsburys', 'FBNABOR002', 'GBP'),\n",
    "('Walmart', 'FBNABOR003', 'USD'),\n",
    "('Wholefoods', 'FBNABOR004', 'USD'),\n",
    "('Waitrose', 'FBNABOR005', 'GBP');\n",
    "\n",
    "@instrumentsForUpload = select\n",
    "column1 as DisplayName,\n",
    "column2 as ClientInternal,\n",
    "column3 as DomCcy,\n",
    "@@scope as Scope\n",
    "from @instrumentsData;\n",
    "\n",
    "-- Step 2: Upload the transformed data into LUSID\n",
    "\n",
    "select *\n",
    "from Lusid.Instrument.Equity.Writer\n",
    "where ToWrite = @instrumentsForUpload;\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "mds112",
   "metadata": {},
   "source": [
    "#### Step 03: Create bond instruments"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ccs112",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%luminesce\n",
    "\n",
    "/*\n",
    "\n",
    "-----------------------\n",
    "Create Bond instruments\n",
    "-----------------------\n",
    "\n",
    "In this snippet we create Bond instruments.\n",
    "\n",
    "For more details on LUSID providers, see this page:\n",
    "\n",
    "https://support.lusid.com/knowledgebase/category/?id=CAT-01099\n",
    "\n",
    "*/\n",
    "\n",
    "@@scope = select 'luminesce-examples';\n",
    "\n",
    "-- Step 1: Define the bond instruments\n",
    "\n",
    "@bondsData= \n",
    "values\n",
    "('US BOND 4% 01/01/2033', 'FBNBND001',  #2023-01-01#,  0.04, 'USD', '1Y', 'ActAct', #2033-01-01#),\n",
    "('US BOND 7% 01/01/2028', 'FBNBND002',  #2023-01-01#,  0.07, 'USD', '1Y', 'ActAct', #2028-01-01#),\n",
    "('UK BOND 3% 01/01/2033', 'FBNBND003',  #2023-01-01#,  0.03, 'GBP', '1Y', 'ActAct', #2033-01-01#),\n",
    "('UK BOND 8% 01/01/2028', 'FBNBND004',  #2023-01-01#,  0.08, 'GBP', '1Y', 'ActAct', #2028-01-01#);\n",
    "\n",
    "\n",
    "@bondsDataForUpload = \n",
    "select\n",
    "Column1 as DisplayName,\n",
    "column2 as ClientInternal,\n",
    "column3 as StartDate,\n",
    "column4 as CouponRate,\n",
    "column5 as DomCcy,\n",
    "column5 as FlowConventionsCurrency,\n",
    "column6 as FlowConventionsPaymentFrequency,\n",
    "column7 as FlowConventionsDayCountConvention,\n",
    "'MF' as FlowConventionsRollConvention,\n",
    "column5 as FlowConventionsPaymentCalendars,\n",
    "column5 as FlowConventionsResetCalendars,\n",
    "0 as FlowConventionsSettleDays,\n",
    "0 as FlowConventionsResetDays,\n",
    "1 as Principal,\n",
    "column8 as MaturityDate,\n",
    "@@scope as Scope\n",
    "from @bondsData;\n",
    "\n",
    "-- Step 2: Upload the transformed data into LUSID\n",
    "\n",
    "select *\n",
    "from Lusid.Instrument.Bond.Writer\n",
    "where ToWrite = @bondsDataForUpload;\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "mds113",
   "metadata": {},
   "source": [
    "#### Step 04: Create instrument properties"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ccs113",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%luminesce\n",
    "\n",
    "/*\n",
    "\n",
    "----------------------------\n",
    "Create instrument properties\n",
    "----------------------------\n",
    "\n",
    "In this snippet we create some instrument properties.\n",
    "\n",
    "For more details on LUSID providers, see this page:\n",
    "\n",
    "https://support.lusid.com/knowledgebase/category/?id=CAT-01099\n",
    "\n",
    "*/\n",
    "\n",
    "@@scope = select 'luminesce-examples';\n",
    "\n",
    "-- Step 1: Define the property definitions\n",
    "\n",
    "@newProperties =\n",
    "values\n",
    "('Instrument', @@scope, 'Sector', 'string'),\n",
    "('Instrument', @@scope, 'AssetClass', 'string'),\n",
    "('Instrument', @@scope, 'InternalRating', 'number'),\n",
    "('Transaction', @@scope, 'CashType', 'string');\n",
    "\n",
    "@propertyDefinitions =\n",
    "select \n",
    "Column1 as [Domain], \n",
    "Column2 as [PropertyScope], \n",
    "Column3 as [PropertyCode], \n",
    "Column3 as [DisplayName], \n",
    "'Property' as [ConstraintStyle],\n",
    "'system' as [DataTypeScope],\n",
    "column4 as [DataTypeCode]\n",
    "from @newProperties;\n",
    "\n",
    "-- Step 2: Load property definitions\n",
    "\n",
    "select *\n",
    "from Lusid.Property.Definition.Writer\n",
    "where ToWrite = @propertyDefinitions;\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "mds114",
   "metadata": {},
   "source": [
    "#### Step 05: Assign properties to instrument"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ccs114",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%luminesce\n",
    "\n",
    "/*\n",
    "\n",
    "--------------------------------\n",
    "Assign properties to instruments\n",
    "--------------------------------\n",
    "\n",
    "In this snippet we assign properties to instruments.\n",
    "\n",
    "For more details on LUSID providers, see this page:\n",
    "\n",
    "https://support.lusid.com/knowledgebase/category/?id=CAT-01099\n",
    "\n",
    "Prerequisite setup steps:\n",
    "\n",
    "    1. Setup the property definitions referenced below \n",
    "    2. Setup the instruments referenced below\n",
    "\n",
    "*/\n",
    "\n",
    "@@scope = select 'luminesce-examples';\n",
    "\n",
    "-- Step 1: Define the property definitions\n",
    "\n",
    "@newProperties =\n",
    "values\n",
    "\n",
    "-- Sectors\n",
    "('FBNABOR001', @@scope, 'Sector', 'Consumer'),\n",
    "('FBNABOR002', @@scope, 'Sector', 'Consumer'),\n",
    "('FBNABOR003', @@scope, 'Sector', 'Consumer'),\n",
    "('FBNABOR004', @@scope, 'Sector', 'Consumer'),\n",
    "('FBNABOR005', @@scope, 'Sector', 'Consumer'),\n",
    "('FBNBND001', @@scope, 'Sector', 'Government'),\n",
    "('FBNBND002', @@scope, 'Sector', 'Government'),\n",
    "('FBNBND003', @@scope, 'Sector', 'Government'),\n",
    "('FBNBND004', @@scope, 'Sector', 'Government'),\n",
    "\n",
    "-- Country\n",
    "('FBNABOR001', @@scope, 'AssetClass', 'Common Stock'),\n",
    "('FBNABOR002', @@scope, 'AssetClass', 'Common Stock'),\n",
    "('FBNABOR003', @@scope, 'AssetClass', 'Common Stock'),\n",
    "('FBNABOR004', @@scope, 'AssetClass', 'Common Stock'),\n",
    "('FBNABOR005', @@scope, 'AssetClass', 'Common Stock'),\n",
    "('FBNBND001', @@scope, 'AssetClass', 'Government Bond'),\n",
    "('FBNBND002', @@scope, 'AssetClass', 'Government Bond'),\n",
    "('FBNBND003', @@scope, 'AssetClass', 'Government Bond'),\n",
    "('FBNBND004', @@scope, 'AssetClass', 'Government Bond'),\n",
    "\n",
    "-- Internal ratings\n",
    "('FBNABOR001', @@scope, 'InternalRating', 8),\n",
    "('FBNABOR002', @@scope, 'InternalRating', 8),\n",
    "('FBNABOR003', @@scope, 'InternalRating', 9),\n",
    "('FBNABOR004', @@scope, 'InternalRating', 7),\n",
    "('FBNABOR005', @@scope, 'InternalRating', 7),\n",
    "('FBNBND001', @@scope, 'InternalRating', 8),\n",
    "('FBNBND002', @@scope, 'InternalRating', 8),\n",
    "('FBNBND003', @@scope, 'InternalRating', 6),\n",
    "('FBNBND004', @@scope, 'InternalRating', 7);\n",
    "\n",
    "@instProperties =\n",
    "select \n",
    "column1 as EntityId, \n",
    "'ClientInternal' as EntityIdType, \n",
    "'Instrument' as Domain,\n",
    "Column2 as PropertyScope, \n",
    "Column3 as PropertyCode,\n",
    "Column4 as Value,\n",
    "@@scope as EntityScope\n",
    "from @newProperties;\n",
    "\n",
    "-- Upload the transformed data into LUSID\n",
    "\n",
    "select *\n",
    "from Lusid.Property.Writer\n",
    "where ToWrite = @instProperties\n",
    "limit 5;\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "mds115",
   "metadata": {},
   "source": [
    "#### Step 06: Create transactions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ccs115",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%luminesce\n",
    "\n",
    "/*\n",
    "\n",
    "------------------\n",
    "Create transactions\n",
    "------------------\n",
    "\n",
    "In this snippet we create some Transactions.\n",
    "\n",
    "For more details on LUSID providers, see this page:\n",
    "\n",
    "https://support.lusid.com/knowledgebase/category/?id=CAT-01099\n",
    "\n",
    "Prerequisite setup steps:\n",
    "\n",
    "    1. Setup a portfolio with scope/code per below\n",
    "    2. Setup Buy and Sell Transaction Types\n",
    "    3. Setup instruments with the ClientInternal instrument IDs referenced below\n",
    "\n",
    "*/\n",
    "\n",
    "\n",
    "@@scope = select 'luminesce-examples';\n",
    "@@portfolioCode = select 'aborPortfolio';\n",
    "\n",
    "-- Step 1: Define some transactions\n",
    "\n",
    "@transactions = \n",
    "values\n",
    "\n",
    "-- Equity Transactions\n",
    "(@@scope, @@portfolioCode, 'txn_001', 'Buy', '2023-01-01', '2023-01-03', 1000, 10, 10000, 'GBP', 'FBNABOR001', 1),\n",
    "(@@scope, @@portfolioCode, 'txn_002', 'Buy', '2023-01-01', '2023-01-03', 2000, 12, 20000, 'GBP', 'FBNABOR002', 1),\n",
    "(@@scope, @@portfolioCode, 'txn_003', 'Buy', '2023-01-01', '2023-01-03', 3000, 13, 30000, 'USD', 'FBNABOR003', 0.8),\n",
    "(@@scope, @@portfolioCode, 'txn_004', 'Buy', '2023-01-01', '2023-01-03', 4000, 14, 40000, 'USD', 'FBNABOR004', 0.8),\n",
    "(@@scope, @@portfolioCode, 'txn_005', 'Buy', '2023-01-01', '2023-01-03', 5000, 15, 50000, 'GBP', 'FBNABOR005', 1),\n",
    "(@@scope, @@portfolioCode, 'txn_006', 'Sell', '2023-02-01', '2023-02-03', 1000, 21, 21000, 'GBP', 'FBNABOR001', 1),\n",
    "(@@scope, @@portfolioCode, 'txn_007', 'Sell', '2023-02-01', '2023-02-03', 1000, 22, 22000, 'GBP', 'FBNABOR002', 1),\n",
    "(@@scope, @@portfolioCode, 'txn_008', 'Sell', '2023-02-01', '2023-02-03', 1000, 23, 23000, 'USD', 'FBNABOR003', 0.78),\n",
    "(@@scope, @@portfolioCode, 'txn_009', 'Sell', '2023-02-01', '2023-02-03', 1000, 24, 24000, 'USD', 'FBNABOR004', 0.78),\n",
    "(@@scope, @@portfolioCode, 'txn_010', 'Sell', '2023-02-01', '2023-02-03', 1000, 25, 25000, 'GBP', 'FBNABOR005', 1),\n",
    "\n",
    "--Bond transactions\n",
    "(@@scope, @@portfolioCode, 'txn_011', 'Buy', '2023-01-01', '2023-01-03', 100000, 100, 100000, 'USD', 'FBNBND001', 0.8),\n",
    "(@@scope, @@portfolioCode, 'txn_012', 'Buy', '2023-01-01', '2023-01-03', 200000, 98, 200000, 'GBP', 'FBNBND003', 1),\n",
    "(@@scope, @@portfolioCode, 'txn_013', 'Sell', '2023-02-01', '2023-02-03', 50000, 100, 50000, 'USD', 'FBNBND001', 0.78),\n",
    "(@@scope, @@portfolioCode, 'txn_014', 'Sell', '2023-02-01', '2023-02-03', 100000, 102, 100000, 'GBP', 'FBNBND003', 1)\n",
    ";\n",
    "\n",
    "\n",
    "-- Step 2: Load transactions into LUSID\n",
    "\n",
    "@createTransactions = \n",
    "select\n",
    "column1 as PortfolioScope,\n",
    "column2 as PortfolioCode,\n",
    "column3 as TxnId,\n",
    "column4 as Type,\n",
    "column5 as TransactionDate,\n",
    "column6 as SettlementDate,\n",
    "column7 as Units,\n",
    "column8 as TradePrice,\n",
    "column9 as TotalConsideration,\n",
    "column10 as SettlementCurrency,\n",
    "column11 as ClientInternal,\n",
    "column12 as TradeToPortfolioRate,\n",
    "'abor' as Source\n",
    "from @transactions;\n",
    "\n",
    "-- Upload the transformed data into LUSID\n",
    "\n",
    "select *\n",
    "from Lusid.Portfolio.Txn.Writer\n",
    "where ToWrite = @createTransactions;\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "mds116",
   "metadata": {},
   "source": [
    "#### Step 07: Upload instrument prices"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ccs116",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%luminesce\n",
    "\n",
    "/*\n",
    "\n",
    "------------------------\n",
    "Upload instrument prices\n",
    "------------------------\n",
    "\n",
    "In this snippet we upload instrument prices into LUSID.\n",
    "\n",
    "For more details on LUSID providers, see this page:\n",
    "\n",
    "https://support.lusid.com/knowledgebase/category/?id=CAT-01099\n",
    "\n",
    "Prerequisite setup steps:\n",
    "\n",
    "    1. Setup instruments with the ClientInternal instrument IDs referenced below\n",
    "\n",
    "\n",
    "*/\n",
    "\n",
    "@@scope = select 'luminesce-examples';\n",
    "@@quoteDateStart = select #2023-01-02#;\n",
    "@@quoteDateEnd = select #2023-03-03#;\n",
    "\n",
    "-- Step 1: Define some instrument prices\n",
    "\n",
    "@instrumentPriceData= \n",
    "values\n",
    "\n",
    "-- 2nd Jan 2023\n",
    "('FBNABOR001', @@quoteDateStart, 31, 'GBP', 1),\n",
    "('FBNABOR002', @@quoteDateStart, 32, 'GBP', 1),\n",
    "('FBNABOR003', @@quoteDateStart, 33, 'USD', 1),\n",
    "('FBNABOR004', @@quoteDateStart, 34, 'USD', 1),\n",
    "('FBNABOR005', @@quoteDateStart, 34, 'GBP', 1),\n",
    "('FBNBND001', @@quoteDateStart, 105, 'USD', 100),\n",
    "('FBNBND002', @@quoteDateStart, 105, 'USD', 100),\n",
    "('FBNBND003', @@quoteDateStart, 107, 'GBP', 100),\n",
    "('FBNBND004', @@quoteDateStart, 98, 'GBP', 100),\n",
    "\n",
    "-- 3rd March 2023\n",
    "('FBNABOR001', @@quoteDateEnd, 35, 'GBP', 1),\n",
    "('FBNABOR002', @@quoteDateEnd, 34, 'GBP', 1),\n",
    "('FBNABOR003', @@quoteDateEnd, 30, 'USD', 1),\n",
    "('FBNABOR004', @@quoteDateEnd, 39, 'USD', 1),\n",
    "('FBNABOR005', @@quoteDateEnd, 45, 'GBP', 1),\n",
    "('FBNBND001', @@quoteDateEnd, 102, 'USD', 100),\n",
    "('FBNBND002', @@quoteDateEnd, 101, 'USD', 100),\n",
    "('FBNBND003', @@quoteDateEnd, 112, 'GBP', 100),\n",
    "('FBNBND004', @@quoteDateEnd, 104, 'GBP', 100);\n",
    "\n",
    "-- Step 2: Load prices into LUSID\n",
    "\n",
    "@pricesForUpload = select\n",
    "'ClientInternal' as InstrumentIdType,\n",
    "column1 as Instrumentid,\n",
    "@@scope as QuoteScope,\n",
    "'Price' as QuoteType,\n",
    "'Lusid' as Provider,\n",
    "'mid' as Field,\n",
    "column2 as QuoteEffectiveAt,\n",
    "column3 as Value,\n",
    "column4 as Unit,\n",
    "column5 as ScaleFactor\n",
    "from @instrumentPriceData;\n",
    "\n",
    "select * from Lusid.Instrument.Quote.Writer\n",
    "where ToWrite = @pricesForUpload;\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "mds117",
   "metadata": {},
   "source": [
    "#### Step 08: Upload fx rates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ccs117",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%luminesce\n",
    "\n",
    "/*\n",
    "\n",
    "---------------\n",
    "Upload FX rates\n",
    "---------------\n",
    "\n",
    "In this snippet we upload FX rates into LUSID.\n",
    "\n",
    "For more details on LUSID providers, see this page:\n",
    "\n",
    "https://support.lusid.com/knowledgebase/category/?id=CAT-01099\n",
    "\n",
    "*/\n",
    "\n",
    "@@scope = select 'luminesce-examples';\n",
    "@@quoteDate = select #2023-03-03#;\n",
    "\n",
    "-- Step 1: Define FX rates\n",
    "\n",
    "@fxRatesData= \n",
    "values\n",
    "('GBP/USD', #2023-01-02#, 1.2344),\n",
    "('GBP/EUR', #2023-01-02#, 1.1444),\n",
    "('GBP/USD', #2023-03-03#, 1.2823),\n",
    "('GBP/EUR', #2023-03-03#, 1.1732);\n",
    "\n",
    "-- Step 2: Load FX rates into LUSID\n",
    "\n",
    "@quotesForUpload = select\n",
    "'CurrencyPair' as InstrumentIdType,\n",
    "column1 as Instrumentid,\n",
    "@@scope as QuoteScope,\n",
    "'Rate' as QuoteType,\n",
    "'Lusid' as Provider,\n",
    "'mid' as Field,\n",
    "column2 as QuoteEffectiveAt,\n",
    "column3 as Value,\n",
    "substr(column1, 5, 7) as Unit\n",
    "from @fxRatesData;\n",
    "\n",
    "select * from Lusid.Instrument.Quote.Writer\n",
    "where ToWrite = @quotesForUpload;\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "mds118",
   "metadata": {},
   "source": [
    "#### Step 09: Create cash transactions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ccs118",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%luminesce\n",
    "\n",
    "/*\n",
    "\n",
    "------------------\n",
    "Create transaction\n",
    "------------------\n",
    "\n",
    "In this snippet we create some cash Transactions.\n",
    "\n",
    "For more details on LUSID providers, see this page:\n",
    "\n",
    "https://support.lusid.com/knowledgebase/category/?id=CAT-01099\n",
    "\n",
    "Prerequisite setup steps:\n",
    "\n",
    "    1. Setup a portfolio with scope/code per below\n",
    "    2. Setup FundsIn Transaction Types\n",
    "\n",
    "*/\n",
    "\n",
    "\n",
    "@@scope = select 'luminesce-examples';\n",
    "@@portfolioCode = select 'aborPortfolio';\n",
    "\n",
    "\n",
    "\n",
    "/*  \n",
    "    Step 1: Define some cash transactions\n",
    "    \n",
    "    In the step below, we create a transaction type called AborFundsIn which is configured to create\n",
    "    Capital movements in the Journal Entry\n",
    "\n",
    "*/\n",
    "\n",
    "@transactions = \n",
    "values\n",
    "(@@scope, @@portfolioCode, 'csh_001', 'FundsIn', '2023-01-01', '2023-01-03', 1000000, 1, 1000000, 'GBP', 'CCY_GBP', 1, 1),\n",
    "(@@scope, @@portfolioCode, 'csh_002', 'FundsIn', '2023-01-01', '2023-01-03', 1000000, 1, 1000000, 'USD', 'CCY_USD', 0.79, 1),\n",
    "(@@scope, @@portfolioCode, 'csh_003', 'FundsOut', '2023-02-01', '2023-02-03', 200000, 1, 200000, 'GBP', 'CCY_GBP', 1, 1),\n",
    "(@@scope, @@portfolioCode, 'csh_004', 'FundsOut', '2023-02-01', '2023-02-03', 200000, 1, 200000, 'USD', 'CCY_USD', 0.81, 1),\n",
    "\n",
    "--Fx Spots\n",
    "(@@scope, @@portfolioCode, 'txn_015', 'FxSpotBuy', '2023-02-15', '2023-02-17', 100000, 1, 80000, 'GBP', 'CCY_USD', 0.8, 0.8),\n",
    "(@@scope, @@portfolioCode, 'txn_016', 'FxSpotBuy', '2023-02-16', '2023-02-18', 50000, 1, 40000, 'GBP', 'CCY_USD', 0.8, 0.8),\n",
    "\n",
    "--Fees, Collateral and other cash\n",
    "(@@scope, @@portfolioCode, 'txn_017', 'CashIn', '2023-02-15', '2023-02-17', 10000, 1, 10000, 'GBP', 'CCY_GBP', 1, 1),\n",
    "(@@scope, @@portfolioCode, 'txn_018', 'CashIn', '2023-02-16', '2023-02-18', 20000, 1, 30000, 'GBP', 'CCY_GBP', 1, 1),\n",
    "(@@scope, @@portfolioCode, 'txn_019', 'CashIn', '2023-02-15', '2023-02-17', 10000, 1, 10000, 'GBP', 'CCY_GBP', 1, 1),\n",
    "(@@scope, @@portfolioCode, 'txn_020', 'CashIn', '2023-02-16', '2023-02-18', 40000, 1, 40000, 'GBP', 'CCY_GBP', 1, 1),\n",
    "(@@scope, @@portfolioCode, 'txn_021', 'CashIn', '2023-02-16', '2023-02-18', 40000, 1, 40000, 'USD', 'CCY_USD', 1, 1)\n",
    ";\n",
    "\n",
    "\n",
    "@createTransactions = \n",
    "select\n",
    "column1 as PortfolioScope,\n",
    "column2 as PortfolioCode,\n",
    "column3 as TxnId,\n",
    "column4 as Type,\n",
    "column5 as TransactionDate,\n",
    "column6 as SettlementDate,\n",
    "column7 as Units,\n",
    "column8 as TradePrice,\n",
    "column9 as TotalConsideration,\n",
    "column10 as SettlementCurrency,\n",
    "column11 as LusidInstrumentId,\n",
    "Column12 as TradeToPortfolioRate,\n",
    "column13 as ExchangeRate,\n",
    "'abor' as Source\n",
    "from @transactions;\n",
    "\n",
    "-- Step 2: Load transactions into LUSID\n",
    "\n",
    "select *\n",
    "from Lusid.Portfolio.Txn.Writer\n",
    "where ToWrite = @createTransactions;\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "mds119",
   "metadata": {},
   "source": [
    "#### Step 10: Add transaction properties"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ccs119",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%luminesce\n",
    "\n",
    "/*\n",
    "\n",
    "---------------------------------\n",
    "Assign properties to Transactions\n",
    "---------------------------------\n",
    "\n",
    "In this snippet we assign properties to the Transactions.\n",
    "\n",
    "For more details on LUSID providers, see this page:\n",
    "\n",
    "https://support.lusid.com/knowledgebase/category/?id=CAT-01099\n",
    "\n",
    "Prerequisite setup steps:\n",
    "\n",
    "    1. Setup the property definitions referenced below \n",
    "    2. Setup the transactions referenced below\n",
    "\n",
    "*/\n",
    "\n",
    "@@scope = select 'luminesce-examples';\n",
    "@@portfolioCode = select 'aborPortfolio';\n",
    "\n",
    "@newProperties =\n",
    "values\n",
    "('txn_017', 'CashType', 'Collateral'),\n",
    "('txn_018', 'CashType', 'TaxRebate'),\n",
    "('txn_019', 'CashType', 'Collateral'),\n",
    "('txn_020', 'CashType', 'ManagementFees'),\n",
    "('txn_021', 'CashType', 'Collateral');\n",
    "\n",
    "@tableOfData = \n",
    "select @@scope as PortfolioScope, \n",
    "@@portfolioCode as PortfolioCode,\n",
    "@@scope as PropertyScope, \n",
    "column1 as TxnId, \n",
    "column2 as PropertyCode, \n",
    "column3 as Value\n",
    "from @newProperties\n",
    ";\n",
    "\n",
    "select * from Lusid.Portfolio.Txn.Property.Writer where ToWrite = @tableOfData;\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "mds1110",
   "metadata": {},
   "source": [
    "#### Step 10: Create a chart of accounts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ccs1110",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%luminesce\n",
    "\n",
    "/*\n",
    "\n",
    "--------------------------\n",
    "Create a Chart of Accounts\n",
    "--------------------------\n",
    "\n",
    "In this snippet we create a Chart of Accounts.\n",
    "\n",
    "For more details on LUSID providers, see this page:\n",
    "\n",
    "https://support.lusid.com/knowledgebase/category/?id=CAT-01099\n",
    "\n",
    "*/\n",
    "\n",
    "-- Step 1: Define the Chart of Accounts\n",
    "\n",
    "@@scope = select 'luminesce-examples';\n",
    "@@code = select 'standardChartOfAccounts';\n",
    "@@name = select 'Standard Chart Of Accounts';\n",
    "@@writeAction = select 'Upsert';\n",
    "\n",
    "@chartOfAccounts =\n",
    "select\n",
    "@@scope as ChartOfAccountsScope,\n",
    "@@code as ChartOfAccountsCode,\n",
    "@@name as DisplayName,\n",
    "@@name as Description,\n",
    "@@writeAction as WriteAction;\n",
    "\n",
    "-- Step 2: Upload Chart of Account into LUSID\n",
    "\n",
    "select * from Lusid.ChartOfAccounts.Writer where ToWrite = @chartOfAccounts;\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "mds1111",
   "metadata": {},
   "source": [
    "#### Step 11: Add accounts to chart of accounts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ccs1111",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%luminesce\n",
    "\n",
    "/*\n",
    "\n",
    "---------------------------------\n",
    "Add Accounts to Chart of Accounts\n",
    "---------------------------------\n",
    "\n",
    "In this snippet we add Accounts to a Chart of Accounts.\n",
    "\n",
    "For more details on LUSID providers, see this page:\n",
    "\n",
    "https://support.lusid.com/knowledgebase/category/?id=CAT-01099\n",
    "\n",
    "Prerequisite setup steps:\n",
    "\n",
    "    1. Setup a Chart of Accounts with the scope/code referenced below\n",
    "\n",
    "*/\n",
    "\n",
    "-- Step 1: Define some accounts\n",
    "\n",
    "@@scope = select 'luminesce-examples';\n",
    "@@code = select 'standardChartOfAccounts';\n",
    "\n",
    "@accounts = values\n",
    "-- Investments\n",
    "('A0001-Investments', 'Asset'),\n",
    "\n",
    "-- Cash, commitments\n",
    "('A0002-Settled-Cash', 'Asset'),\n",
    "\n",
    "-- Sales and purchases for settlement\n",
    "('A0003-Sales-To-Settle', 'Asset'),\n",
    "('A0004-Purchases-To-Settle', 'Asset'),\n",
    "('A0005-Long-FX-To-Settle', 'Asset'),\n",
    "('A0006-Short-FX-To-Settle', 'Asset'),\n",
    "\n",
    "--Capital\n",
    "('A0007-Capital', 'Capital'),\n",
    "\n",
    "-- Gains and Losses\n",
    "('A0008-Realised-Market-Gains', 'Income'),\n",
    "('A0009-Realised-Fx-Gains', 'Income'),\n",
    "('A0010-UnrealisedGains', 'Income'),\n",
    "\n",
    "-- Subs, reds and accruals\n",
    "('A0011-Accruals', 'Income'),\n",
    "('A0012-Subscriptions', 'Asset'),\n",
    "('A0013-Redemptions', 'Asset'),\n",
    "\n",
    "-- Unknown catch alls\n",
    "('A0101-Unknown-NA',  'Asset'),\n",
    "('A0102-Unknown-PL',  'Revenue'),\n",
    "('A0103-Unknown-CA', 'Capital');\n",
    "\n",
    "@chartsOfAccountsAccounts = select\n",
    "@@scope as ChartOfAccountsScope,\n",
    "@@code as ChartOfAccountsCode,\n",
    "column1 as AccountCode,\n",
    "column1 as Description,\n",
    "column2 as Type,\n",
    "'Manual' as Control,\n",
    "'Active' as Status\n",
    "from @accounts;\n",
    "\n",
    "-- Step 2: Assign Accounts onto a ChartOfAccount\n",
    "\n",
    "select * from Lusid.ChartOfAccounts.Account.Writer \n",
    "where ToWrite = @chartsOfAccountsAccounts;\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "mds1112",
   "metadata": {},
   "source": [
    "#### Step 12: Create posting module"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ccs1112",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%luminesce\n",
    "\n",
    "/*\n",
    "\n",
    "----------------------\n",
    "Create posting modules\n",
    "----------------------\n",
    "\n",
    "In this snippet we create a posting module.\n",
    "\n",
    "For more details on LUSID providers, see this page:\n",
    "\n",
    "https://support.lusid.com/knowledgebase/category/?id=CAT-01099\n",
    "\n",
    "Prerequisite setup steps:\n",
    "\n",
    "    1. Setup a Chart of Accounts with the scope/code referenced below\n",
    "\n",
    "*/\n",
    "\n",
    "-- Step 1: Define a posting module\n",
    "\n",
    "@@scope = select 'luminesce-examples';\n",
    "@@coaCode = select 'standardChartOfAccounts';\n",
    "@@postingModuleCode = select 'standardPostingModule';\n",
    "@@writeAction = select 'Upsert';\n",
    "\n",
    "@postingModule = \n",
    "select\n",
    "@@postingModuleCode as PostingModuleCode,\n",
    "@@coaCode as ChartOfAccountsCode,\n",
    "@@scope as ChartOfAccountsScope, \n",
    "'Active' as Status,\n",
    "'Daily NAV' as DisplayName,\n",
    "'Posting module for daily NAV' as Description,\n",
    "@@writeAction as WriteAction;\n",
    "\n",
    "-- Step 2: Upload posting module into LUSID\n",
    "\n",
    "select * from Lusid.PostingModule.Writer where ToWrite = @postingModule;\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "mds1113",
   "metadata": {},
   "source": [
    "#### Step 13: Create posting rules"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ccs1113",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%luminesce\n",
    "\n",
    "/*\n",
    "\n",
    "--------------------\n",
    "Create posting rules\n",
    "--------------------\n",
    "\n",
    "In this snippet we create some posting rules.\n",
    "\n",
    "For more details on LUSID providers, see this page:\n",
    "\n",
    "https://support.lusid.com/knowledgebase/category/?id=CAT-01099\n",
    "\n",
    "Prerequisite setup steps:\n",
    "\n",
    "    1. Setup a Posting Module wuth the scope/code referenced below\n",
    "\n",
    "\n",
    "*/\n",
    "\n",
    "-- Step 1: Define posting rules\n",
    "\n",
    "@@scope = select 'luminesce-examples';\n",
    "@@coaCode = select 'standardChartOfAccounts';\n",
    "@@postingModuleCode = select 'standardPostingModule';\n",
    "\n",
    "@rules_filters = values\n",
    "(\n",
    "    'Rule-001', \n",
    "    'EconomicBucket startswith ''NA'' \n",
    "        and HoldType eq ''P''',\n",
    "    'A0001-Investments',\n",
    "    1\n",
    "),\n",
    "(\n",
    "    'Rule-002',\n",
    "    'EconomicBucket startswith ''NA'' and HoldType eq ''B''',\n",
    "    'A0002-Settled-Cash',\n",
    "    2\n",
    "),\n",
    "(\n",
    "    'Rule-003', \n",
    "    'EconomicBucket startswith ''NA_'' \n",
    "        and MovementName eq ''CashProceeds'' \n",
    "        and HoldType neq ''B''',\n",
    "    'A0003-Sales-To-Settle',\n",
    "    3\n",
    "),\n",
    "(\n",
    "    'Rule-004', \n",
    "    'EconomicBucket startswith ''NA_'' \n",
    "        and MovementName eq ''CashInvested'' \n",
    "        and HoldType neq ''B''',\n",
    "    'A0004-Purchases-To-Settle',\n",
    "    4\n",
    "),\n",
    "(\n",
    "    'Rule-005', \n",
    "    'EconomicBucket startswith ''NA_'' \n",
    "        and MovementName eq ''FxSpotBuyLeg'' \n",
    "        and HoldType neq ''B''',\n",
    "    'A0005-Long-FX-To-Settle',\n",
    "    5\n",
    "),\n",
    "(\n",
    "    'Rule-006', \n",
    "    'EconomicBucket startswith ''NA_'' \n",
    "        and MovementName eq ''FxSpotSellLeg'' \n",
    "        and HoldType neq ''B''',\n",
    "    'A0006-Short-FX-To-Settle',\n",
    "    6\n",
    "),\n",
    "(\n",
    "    'Rule-007',\n",
    "    'EconomicBucket eq ''CA_Capital''', \n",
    "    'A0007-Capital',\n",
    "    7\n",
    "),\n",
    "(\n",
    "    'Rule-008', \n",
    "    'EconomicBucket eq ''PL_RealPriceGL'' and HoldType eq ''P''', \n",
    "    'A0008-Realised-Market-Gains',\n",
    "    8\n",
    "),\n",
    "(\n",
    "    'Rule-009', \n",
    "    'EconomicBucket eq ''PL_RealFXGL'' and HoldType in ''P'', ''B''', \n",
    "    'A0009-Realised-Fx-Gains',\n",
    "    9\n",
    "),\n",
    "(\n",
    "    'Rule-010', \n",
    "    'EconomicBucket startswith ''PL_Unreal''',\n",
    "    'A0010-UnrealisedGains',\n",
    "    10\n",
    "),\n",
    "(\n",
    "    'Rule-011', \n",
    "    'EconomicBucket startswith ''PL_Accrued''',\n",
    "    'A0011-Accruals',\n",
    "    11\n",
    "),\n",
    "(\n",
    "    'Rule-012', \n",
    "    'MovementName eq ''Subscription'' and HoldType neq ''B''',\n",
    "    'A0012-Subscriptions',\n",
    "    12\n",
    "),\n",
    "(\n",
    "    'Rule-013', \n",
    "    'MovementName eq ''Redemption'' and HoldType neq ''B''',\n",
    "    'A0013-Redemptions',\n",
    "    13\n",
    "),\n",
    "(\n",
    "    'Rule-101', \n",
    "    'EconomicBucket startswith ''NA''',\n",
    "    'A0101-Unknown-NA',\n",
    "    14\n",
    "),\n",
    "(\n",
    "    'Rule-102', \n",
    "    'EconomicBucket startswith ''PL''',\n",
    "    'A0102-Unknown-PL',\n",
    "    15\n",
    "),\n",
    "(\n",
    "    'Rule-103', \n",
    "    'EconomicBucket startswith ''CA''',\n",
    "    'A0103-Unknown-CA',\n",
    "    16\n",
    ")\n",
    ";\n",
    "\n",
    "\n",
    "-- Step 2: Add posting rules to posting module\n",
    "\n",
    "@postingRules = select \n",
    "@@scope as ChartOfAccountsScope,\n",
    "@@coaCode as ChartOfAccountsCode,\n",
    "@@postingModuleCode as PostingModuleCode,\n",
    "column1 as RuleId,\n",
    "column2 as RuleFilter,\n",
    "column3 as AccountCode,\n",
    "column4 as RulePriority\n",
    "from @rules_filters;\n",
    "\n",
    "select * from Lusid.PostingModule.Rule.Writer where ToWrite = @postingRules;\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "mds1114",
   "metadata": {},
   "source": [
    "#### Step 14: Create an abor configuration"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ccs1114",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%luminesce\n",
    "\n",
    "/*\n",
    "\n",
    "----------------------------\n",
    "Create an ABOR configuration\n",
    "----------------------------\n",
    "\n",
    "In this snippet we create an ABOR Configurtion.\n",
    "\n",
    "For more details on LUSID providers, see this page:\n",
    "\n",
    "https://support.lusid.com/knowledgebase/category/?id=CAT-01099\n",
    "\n",
    "Prerequisite setup steps:\n",
    "\n",
    "    1. Setup Chart of Accounts referenced below\n",
    "    2. Setup a Posting Module wuth the scope/code referenced below\n",
    "    3. Setup the recipe referenced below\n",
    "\n",
    "\n",
    "*/\n",
    "\n",
    "@@scope = select 'luminesce-examples';\n",
    "@@chartOfAccountsCode = select 'standardChartOfAccounts';\n",
    "@@code = select 'standardAborConfiguration';\n",
    "@@writeAction = select 'Insert';\n",
    "@@PostingModuleCodes = select 'standardPostingModule';\n",
    "\n",
    "-- Step 1: Create an ABOR configuration\n",
    "\n",
    "@aborConfigurationForUpload = select\n",
    "@@code as AborConfigurationCode,\n",
    "@@scope as AborConfigurationScope,\n",
    "@@code as Description,\n",
    "@@chartOfAccountsCode as ChartOfAccountsCode,\n",
    "@@scope as ChartOfAccountsScope,\n",
    "@@PostingModuleCodes as PostingModuleCodes,\n",
    "@@code as DisplayName,\n",
    "@@scope as RecipeScope,\n",
    "'marketValue' as RecipeCode,\n",
    "@@writeAction as WriteAction;\n",
    "\n",
    "-- Step 2: Load ABOR configurtion into LUSID\n",
    "\n",
    "select * from Lusid.AborConfiguration.Writer\n",
    "where ToWrite = @aborConfigurationForUpload;\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "mds1115",
   "metadata": {},
   "source": [
    "#### Step 15: Create abor"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ccs1115",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%luminesce\n",
    "\n",
    "/*\n",
    "\n",
    "--------------\n",
    "Create an ABOR\n",
    "--------------\n",
    "\n",
    "In this snippet we create an ABOR.\n",
    "\n",
    "For more details on LUSID providers, see this page:\n",
    "\n",
    "https://support.lusid.com/knowledgebase/category/?id=CAT-01099\n",
    "\n",
    "Prerequisite setup steps:\n",
    "\n",
    "    1. Setup Abor Configuration referenced below\n",
    "    2. Setup portfolio referenced below\n",
    "\n",
    "\n",
    "*/\n",
    "\n",
    "@@scope = select 'luminesce-examples';\n",
    "@@code = select 'standardAbor';\n",
    "@@aborConfigCode = select 'standardAborConfiguration';\n",
    "@@portfolioCode = select 'aborPortfolio';\n",
    "@@writeAction = select 'Upsert';\n",
    "\n",
    "-- Step 1: Define an ABOR\n",
    "\n",
    "@aborForUpload = select\n",
    "@@scope as AborScope,\n",
    "@@code as AborCode,\n",
    "@@code as DisplayName,\n",
    "'SinglePortfolio' as PortfolioEntityType,\n",
    "@@scope  as PortfolioScope,\n",
    "@@portfolioCode as PortfolioCode,\n",
    "@@portfolioCode as Description,\n",
    "@@scope  as AborConfigurationScope,\n",
    "@@aborConfigCode as AborConfigurationCode,\n",
    "@@writeAction as WriteAction;\n",
    "\n",
    "-- Step 2: Load ABOR into LUSID\n",
    "\n",
    "select * from Lusid.Abor.Writer \n",
    "where ToWrite = @aborForUpload\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "mds1116",
   "metadata": {},
   "source": [
    "#### Step 16: Create journal entry lines"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ccs1116",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%luminesce\n",
    "\n",
    "/*\n",
    "\n",
    "-------------------------------\n",
    "Create Journal Entry (JE) Lines\n",
    "-------------------------------\n",
    "\n",
    "In this snippet we create an ABOR.\n",
    "\n",
    "For more details on LUSID providers, see this page:\n",
    "\n",
    "https://support.lusid.com/knowledgebase/category/?id=CAT-01099\n",
    "\n",
    "Prerequisite setup steps:\n",
    "\n",
    "    1. Create the ABOR referenced below\n",
    "\n",
    "\n",
    "*/\n",
    "\n",
    "@@scope = select 'luminesce-examples';\n",
    "@@code = select 'standardAbor';\n",
    "\n",
    "--Step 1: Generate JE Lines\n",
    "\n",
    "select * from Lusid.Abor.JELine\n",
    "where AborScope = @@scope \n",
    "and AborCode = @@code\n",
    "and StartDate = '2023-01-02'\n",
    "and EndDate = '2023-03-03';\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "mds1117",
   "metadata": {},
   "source": [
    "#### Step 17: Create general ledger profile"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ccs1117",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%luminesce\n",
    "\n",
    "/*\n",
    "\n",
    "--------------------------\n",
    "Create GL profile mappings\n",
    "--------------------------\n",
    "\n",
    "In this snippet we create a GL Profile mappings.\n",
    "\n",
    "For more details on LUSID providers, see this page:\n",
    "\n",
    "https://support.lusid.com/knowledgebase/category/?id=CAT-01099\n",
    "\n",
    "*/\n",
    "\n",
    "\n",
    "@@scope = select 'luminesce-examples';\n",
    "@@chartOfAccountsCode = select 'standardChartOfAccounts';\n",
    "@@generalLedgerProfileCode = select 'standardGeneralLedgerProfile';\n",
    "\n",
    "@glProfile = select\n",
    "@@scope as ChartOfAccountsScope,\n",
    "@@chartOfAccountsCode as ChartOfAccountsCode,\n",
    "@@chartOfAccountsCode as DisplayName,\n",
    "@@generalLedgerProfileCode as GeneralLedgerProfileCode,\n",
    "'Insert' as WriteAction\n",
    ";\n",
    "\n",
    "select * from Lusid.GeneralLedgerProfile.Writer\n",
    "where ToWrite = @glProfile;\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "mds1118",
   "metadata": {},
   "source": [
    "#### Step 18: Create general ledger profile mappings"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ccs1118",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%luminesce\n",
    "\n",
    "/*\n",
    "\n",
    "-----------------\n",
    "Create GL profile\n",
    "-----------------\n",
    "\n",
    "In this snippet we create a GL Profile.\n",
    "\n",
    "For more details on LUSID providers, see this page:\n",
    "\n",
    "https://support.lusid.com/knowledgebase/category/?id=CAT-01099\n",
    "\n",
    "*/\n",
    "\n",
    "\n",
    "@@scope = select 'luminesce-examples';\n",
    "@@chartOfAccountsCode = select 'standardChartOfAccounts';\n",
    "@@generalLedgerProfileCode = select 'standardGeneralLedgerProfile';\n",
    "\n",
    "\n",
    "@mappings = values\n",
    "(\n",
    "    'GeneralLedgerAccountCode eq ''A0001-Investments''', \n",
    "    'DefaultCurrency',\n",
    "    'Properties[Instrument/luminesce-examples/AssetClass]',\n",
    "    '',\n",
    "    1\n",
    "),\n",
    "(\n",
    "    'GeneralLedgerAccountCode not in ''A0101-Unknown-NA'', ''A0102-Unknown-PL'', ''A0103-Unknown-CA''',\n",
    "    'DefaultCurrency',\n",
    "    Null,\n",
    "    '',\n",
    "    2\n",
    ");\n",
    "\n",
    "@mappingsToWrite = select  \n",
    "@@chartOfAccountsCode as ChartOfAccountsCode,\n",
    "@@scope as ChartOfAccountsScope,\n",
    "@@generalLedgerProfileCode as GeneralLedgerProfileCode,\n",
    "column1 as MappingFilter,\n",
    "column2 as Level1,\n",
    "column3 as Level2,\n",
    "column5 as MappingPriority\n",
    "from @mappings;\n",
    "\n",
    "select * from Lusid.GeneralLedgerProfile.Mapping.Writer\n",
    "where ToWrite = @mappingsToWrite;\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "mds1119",
   "metadata": {},
   "source": [
    "#### Step 19: Generate trial balance"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ccs1119",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%luminesce\n",
    "\n",
    "/*\n",
    "\n",
    "--------------------\n",
    "Create Trial Balance\n",
    "--------------------\n",
    "\n",
    "In this snippet we create Trial Balance mappings.\n",
    "\n",
    "For more details on LUSID providers, see this page:\n",
    "\n",
    "https://support.lusid.com/knowledgebase/category/?id=CAT-01099\n",
    "\n",
    "*/\n",
    "\n",
    "\n",
    "select \n",
    "GeneralLedgerProfileAccountCode,\n",
    "Level1,\n",
    "Level2,\n",
    "AccountType,\n",
    "Opening,\n",
    "Closing,\n",
    "Debit,\n",
    "Credit\n",
    "from Lusid.Abor.TrialBalance\n",
    "where StartDate = '2023-01-02'\n",
    "and EndDate = '2023-03-03'\n",
    "and AborScope = 'luminesce-examples'\n",
    "and GeneralLedgerProfileCode = 'standardGeneralLedgerProfile'\n",
    "order by GeneralLedgerProfileAccountCode\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "mds1120",
   "metadata": {},
   "source": [
    "#### Step 20: Run a trial balance check"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ccs1120",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%luminesce\n",
    "\n",
    "/*\n",
    "\n",
    "-----------------------\n",
    "Run Trial Balance Check\n",
    "-----------------------\n",
    "\n",
    "In this snippet we run a Trial Balance check.\n",
    "\n",
    "For more details on LUSID providers, see this page:\n",
    "\n",
    "https://support.lusid.com/knowledgebase/category/?id=CAT-01099\n",
    "\n",
    "*/\n",
    "\n",
    "\n",
    "@tb = select sum(Debit) as SumDebits, sum(Credit) as SumCredits from Lusid.Abor.TrialBalance\n",
    "where GeneralLedgerProfileCode = 'standardGeneralLedgerProfile'\n",
    "and StartDate = '2023-01-02'\n",
    "and EndDate = '2023-03-03'\n",
    "and AborScope = 'luminesce-examples';\n",
    "\n",
    "select SumDebits, SumCredits, round((SumDebits + SumCredits), 2) as [Check] from @tb;\n"
   ]
  }
 ],
 "metadata": {},
 "nbformat": 4,
 "nbformat_minor": 5
}