examples/use-cases/valuation/SRS csv Example.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "c629076e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": "<IPython.core.display.HTML object>",
      "text/html": "\n    <form action=\"javascript:code_toggle()\"><input type=\"submit\" id=\"toggleButton\" value=\"Toggle Docstring\"></form>\n    \n         <script>\n         function code_toggle() {\n             if ($('div.cell.code_cell.rendered.selected div.input').css('display')!='none'){\n                 $('div.cell.code_cell.rendered.selected div.input').hide();\n             } else {\n                 $('div.cell.code_cell.rendered.selected div.input').show();\n             }\n         }\n         </script>\n\n     "
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "from lusidtools.jupyter_tools import toggle_code\n",
    "\n",
    "\"\"\"Structured Results Store for storage of Portfolio data\n",
    "\n",
    "Attributes\n",
    "----------\n",
    "structured_results_store\n",
    "virtual_document\n",
    "luminesce\n",
    "\"\"\"\n",
    "\n",
    "toggle_code(\"Toggle Docstring\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5d8b0dba",
   "metadata": {},
   "source": [
    "# Structured Results Store Example\n",
    "\n",
    "This notebook demostrates loading of a custom Portfolio dataset into the LUSID Structured Results Store.\n",
    "\n",
    "For more context on what a Structure Result Store/Data is see KB Article <a ref=https://support.lusid.com/knowledgebase/article/KA-01893/en-us>KA-01893</a>\n",
    "\n",
    "Once loaded, the dataset can be retrieved as one document, or the individual fields accessed"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "50382822",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": "<IPython.core.display.HTML object>",
      "text/html": "<style>.container { width:90% !important; }</style>"
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": "  api_version build_version excel_version  \\\n0          v0   0.6.10790.0      0.5.3172   \n\n                                               links  \n0  {'relation': 'RequestLogs', 'href': 'http://fb...  ",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>api_version</th>\n      <th>build_version</th>\n      <th>excel_version</th>\n      <th>links</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>v0</td>\n      <td>0.6.10790.0</td>\n      <td>0.5.3172</td>\n      <td>{'relation': 'RequestLogs', 'href': 'http://fb...</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "import os\n",
    "import pandas as pd\n",
    "from datetime import datetime, timezone\n",
    "import io\n",
    "import json\n",
    "from IPython.core.display import HTML\n",
    "from itertools import chain\n",
    "\n",
    "# Then import the key modules from the LUSID package (i.e. The LUSID SDK)\n",
    "import lusid as lu\n",
    "import lusid.models as lm\n",
    "from lusidjam import RefreshingToken\n",
    "\n",
    "# And use absolute imports to import key functions from Lusid-Python-Tools and other helper package\n",
    "from lusid.utilities import ApiClientFactory\n",
    "\n",
    "# Set DataFrame display formats\n",
    "pd.set_option(\"display.max_columns\", None)\n",
    "pd.set_option(\"display.max_rows\", None)\n",
    "pd.options.display.float_format = \"{:,.2f}\".format\n",
    "display(HTML(\"<style>.container { width:90% !important; }</style>\"))\n",
    "\n",
    "# Authenticate our user and create our API client\n",
    "secrets_path = os.getenv(\"FBN_SECRETS_PATH\")\n",
    "\n",
    "if secrets_path is None:\n",
    "    secrets_path = os.path.join(os.path.dirname(os.getcwd()), \"secrets.json\")\n",
    "\n",
    "api_factory = ApiClientFactory(\n",
    "    token=RefreshingToken(),\n",
    "    api_secrets_filename=secrets_path,\n",
    "    app_name=\"LusidJupyterNotebook\",\n",
    ")\n",
    "\n",
    "api_status = pd.DataFrame(\n",
    "    api_factory.build(lu.ApplicationMetadataApi).get_lusid_versions().to_dict()\n",
    ")\n",
    "\n",
    "display(api_status)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "d415a4ec",
   "metadata": {},
   "outputs": [],
   "source": [
    "srs_api = api_factory.build(lu.StructuredResultDataApi)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dc7e2694",
   "metadata": {},
   "source": [
    "<h2>Scope, Effective Date, Result Type</h2>\n",
    "\n",
    "<ul>\n",
    "<li>Scope: The scope in which to create or update data maps.</li>\n",
    "<li>Effective Date: Date at which the structured result is effective from.</li>\n",
    "<li>Result Type: The Class of the Result, has to be one of [UnitResult/Analytic, UnitResult/Grouped, UnitResult/Holding]</li>\n",
    "</ul>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "71284305",
   "metadata": {},
   "outputs": [],
   "source": [
    "scope = \"srs\" \n",
    "effective_date = datetime(2021, 1, 27, tzinfo=timezone.utc)\n",
    "sample_data_result_type = \"UnitResult/Custom\" "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0d15535c",
   "metadata": {},
   "source": [
    "## Define functions to create the Data Map"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d9f54a36",
   "metadata": {},
   "source": [
    "\n",
    "<h3>Data Mapping</h3>\n",
    "\n",
    "The Structured Result Store allows us to upsert structured results, but to give them structure we must first define a Data Mapping,\n",
    "\n",
    "You can think of a Data Mapping as defining the columns and the column types for each row that gets upserted into the SRS using a specific Data Map\n",
    "\n",
    "Each \"row\" can have multiple keys and when difining the data map there are 4 different key types, Unique, PartOfUnique, Leaf, and CompositeLeaf\n",
    "\n",
    "<h4>Unique</h4>\n",
    "<blockquote>A primary key that will throw a unique key constraint if multiple equal values are upserted for a single specific result type</blockquote>\n",
    "<h4>Part of Unique</h4>\n",
    "<blockquote>A key that will be considered as part of the compoisite primary key for an entity</blockquote>\n",
    "<h4>Leaf</h4>\n",
    "<blockquote>Leaf define a value that wont be considered as part of the primary or primary composite key.</blockquote>\n",
    "<h4>Composite Leaf</h4>\n",
    "<blockquote>\n",
    "CompositeLeaf is an abstraction that allows the user to specify which Leafs should be connected, they define data types rather than names of entities as they represent a group of entities.\n",
    "E.g. If we have an Accural we can define a composite leaf as two leafs of amount and currency\n",
    "<p></p>\n",
    "<blockquote>\n",
    "\"UnitResult/Accrual\"\n",
    "\n",
    "DataDefinition(address=\"UnitResult/Accrual\", dataType= \"Result0D\", keyType=\"CompositeLeaf\") \n",
    "</blockquote>\n",
    "\n",
    "<blockquote>\n",
    "\"UnitResult/Accrual/Amount\"\n",
    "\n",
    "DataDefinition(address=\"UnitResult/Accrual/Amount\", name=\"Accrual\", dataType= \"decimal\", keyType=\"Leaf\")\n",
    "</blockquote>\n",
    "<blockquote>\n",
    "\"UnitResult/Accrual/Ccy\"\n",
    "\n",
    "DataDefinition(address=\"UnitResult/Accrual/AmountCcy\", name=\"AccrualCcy\", dataType= \"string\", keyType=\"Leaf\")\n",
    "</blockquote>\n",
    "</blockquote>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "477f0b07",
   "metadata": {},
   "outputs": [],
   "source": [
    "def gen_srs_data_map(leaf_columns, unique_columns, data_types):\n",
    "    def define_data_definitions(columns, key_type):\n",
    "        return [lm.DataDefinition(address=f\"UnitResult/{scope}/{column}\", name=column, data_type=data_types[column], key_type=key_type) \n",
    "                      for column in columns]\n",
    "    data_map = [define_data_definitions(columns, key_type) for columns, key_type in zip((leaf_columns, unique_columns), (\"Leaf\", \"PartOfUnique\"))]\n",
    "\n",
    "    return lm.DataMapping(data_definitions = list(chain(*data_map)))\n",
    "\n",
    "def create_data_map(df, code, version, leaf_columns, unique_columns, data_types):\n",
    "    srs_data_map = gen_srs_data_map(leaf_columns, unique_columns, data_types)\n",
    "    srs_data_map_key = lm.DataMapKey(version=version, code=code)\n",
    "    \n",
    "    try:    \n",
    "        srs_api.create_data_map(\n",
    "            scope=scope, \n",
    "            request_body={\n",
    "                code: lm.CreateDataMapRequest(\n",
    "                    id = srs_data_map_key,\n",
    "                    data = srs_data_map\n",
    "                )\n",
    "            }\n",
    "        )\n",
    "    except lu.ApiException as e:\n",
    "        detail = json.loads(e.body)\n",
    "        if detail['code'] not in [461]: \n",
    "            raise e\n",
    "    return srs_data_map_key"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "eac39670",
   "metadata": {},
   "source": [
    "## Read in sample data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "0f2b18ad",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": "    Port Currency      Date  Sys  Weight         Amount Class\n0  PortA      AUD  20210713  FSC      10  50,000,000.00     C\n1  PortA      CAD  20210713  FSC       7 138,234,286.00     C\n2  PortA      CHF  20210713  FSC       3     203,402.00     C\n3  PortA      EUR  20210713  FSC       2 235,402,950.00     C\n4  PortA      EUR  20210715  FTC       3  22,342,054.00     C",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>Port</th>\n      <th>Currency</th>\n      <th>Date</th>\n      <th>Sys</th>\n      <th>Weight</th>\n      <th>Amount</th>\n      <th>Class</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>PortA</td>\n      <td>AUD</td>\n      <td>20210713</td>\n      <td>FSC</td>\n      <td>10</td>\n      <td>50,000,000.00</td>\n      <td>C</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>PortA</td>\n      <td>CAD</td>\n      <td>20210713</td>\n      <td>FSC</td>\n      <td>7</td>\n      <td>138,234,286.00</td>\n      <td>C</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>PortA</td>\n      <td>CHF</td>\n      <td>20210713</td>\n      <td>FSC</td>\n      <td>3</td>\n      <td>203,402.00</td>\n      <td>C</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>PortA</td>\n      <td>EUR</td>\n      <td>20210713</td>\n      <td>FSC</td>\n      <td>2</td>\n      <td>235,402,950.00</td>\n      <td>C</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>PortA</td>\n      <td>EUR</td>\n      <td>20210715</td>\n      <td>FTC</td>\n      <td>3</td>\n      <td>22,342,054.00</td>\n      <td>C</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "df = pd.read_csv(\"data/srs_custom_data.csv\")\n",
    "df = df.round(0)\n",
    "display(df.head(5))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0081660b",
   "metadata": {},
   "source": [
    "## Upsert data into the Structured Results Store"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "699d9c90",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": "       0                                1\n0  PortA 2023-02-09 14:02:28.999968+00:00",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>0</th>\n      <th>1</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>PortA</td>\n      <td>2023-02-09 14:02:28.999968+00:00</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": "       0                                1\n0  PortB 2023-02-09 14:02:29.309194+00:00",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>0</th>\n      <th>1</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>PortB</td>\n      <td>2023-02-09 14:02:29.309194+00:00</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": "       0                                1\n0  PortC 2023-02-09 14:02:29.690339+00:00",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>0</th>\n      <th>1</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>PortC</td>\n      <td>2023-02-09 14:02:29.690339+00:00</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "def insert_and_load_data(dataFrame):\n",
    "    srs_ids=[]\n",
    "    \n",
    "    version = \"1.01\"\n",
    "    portfolios = dataFrame.groupby(\"Port\")\n",
    "    unique_columns = [\"Currency\", \"Date\", \"Sys\", \"Class\"]\n",
    "    leaf_columns = [column for column in dataFrame.columns if column not in unique_columns]\n",
    "    \n",
    "    sample_data_map_key = create_data_map(\n",
    "        df = df,\n",
    "        code = 'sample_data_map',\n",
    "        version = \"1.03\",\n",
    "        leaf_columns = leaf_columns,\n",
    "        unique_columns = unique_columns,\n",
    "        data_types = { \"RowId\": \"string\", \n",
    "                       \"Port\": \"string\",\n",
    "                       \"Currency\": \"string\",\n",
    "                       \"Date\": \"string\",\n",
    "                       \"Sys\": \"string\",\n",
    "                       \"Weight\": \"decimal\",\n",
    "                       \"Amount\": \"decimal\",\n",
    "                       \"Class\": \"string\"}\n",
    "    )\n",
    "\n",
    "    for portfolio_id, pf_df in portfolios:    \n",
    "        srs_id = lm.StructuredResultDataId(\n",
    "            source = \"Client\", \n",
    "            code = portfolio_id, \n",
    "            effective_at = effective_date,\n",
    "            result_type = sample_data_result_type)\n",
    "    \n",
    "        srs_ids.append(srs_id) \n",
    "    \n",
    "        csv_data = io.StringIO()\n",
    "        pf_df.to_csv(csv_data)   \n",
    "        \n",
    "        request_body = {\n",
    "            portfolio_id: lm.UpsertStructuredResultDataRequest(\n",
    "                id = srs_id,\n",
    "                data = lm.StructuredResultData(\n",
    "                    document_format = \"csv\",\n",
    "                    version = version,\n",
    "                    name = \"Data file\",\n",
    "                    document = csv_data.getvalue(),\n",
    "                    data_map_key = sample_data_map_key\n",
    "                )\n",
    "            )\n",
    "        }\n",
    "        \n",
    "        result = srs_api.upsert_structured_result_data(\n",
    "            scope=scope,\n",
    "            request_body=request_body)\n",
    "\n",
    "        display(pd.DataFrame(result.values.items()))\n",
    "        \n",
    "    return srs_ids\n",
    "\n",
    "\n",
    "srs_ids = insert_and_load_data(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b606dcde",
   "metadata": {},
   "source": [
    "## Retrieve the raw data from the Structured Results Store"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "c36d418f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": "'PortA'"
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": "    Unnamed: 0   Port Currency      Date  Sys  Weight             Amount Class\n0            0  PortA      AUD  20210713  FSC      10      50,000,000.00     C\n1            1  PortA      CAD  20210713  FSC       7     138,234,286.00     C\n2            2  PortA      CHF  20210713  FSC       3         203,402.00     C\n3            3  PortA      EUR  20210713  FSC       2     235,402,950.00     C\n4            4  PortA      EUR  20210715  FTC       3      22,342,054.00     C\n5            5  PortA      EUR  20210727  FTC       5   2,349,130,942.00     C\n6            6  PortA      EUR  20210812  FTC       4      23,489,234.00     C\n7            7  PortA      GBP  20210713  FSC       6      83,425,656.00     C\n8            8  PortA      GBP  20210812  FTC       9      54,354,564.00     C\n9            9  PortA      JPY  20210713  FSC       9         654,659.00     C\n10          10  PortA      JPY  20210812  FTC       9         150,000.00     C\n11          11  PortA      NOK  20210713  FSC       8          45,645.00     C\n12          12  PortA      NZD  20210713  FSC       5          65,454.00     C\n13          13  PortA      USD  20210713  FSC       2        -174,328.00     C\n14          14  PortA      USD  20210714  FTC       6          16,875.00     C\n15          15  PortA      USD  20210715  FTC       6         454,654.00     C\n16          16  PortA      USD  20210716  FTC       4 454,534,000,000.00     C\n17          17  PortA      USD  20210719  FTC       8         534,343.00     C\n18          18  PortA      USD  20210723  FTC       4         101,001.00     C\n19          19  PortA      USD  20210726  FTC      10           4,534.00     C\n20          20  PortA      USD  20210727  FTC      10         544,343.00     C\n21          21  PortA      USD  20210729  FTC       4       3,535,454.00     C\n22          22  PortA      USD  20210730  FTC       8          12,116.00     C\n23          23  PortA      USD  20210806  FTC       1      45,453,435.00     C\n24          24  PortA      USD  20210810  FTC      10          18,406.00     C\n25          25  PortA      USD  20210812  FTC      10     545,343,543.00     B",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>Unnamed: 0</th>\n      <th>Port</th>\n      <th>Currency</th>\n      <th>Date</th>\n      <th>Sys</th>\n      <th>Weight</th>\n      <th>Amount</th>\n      <th>Class</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>0</td>\n      <td>PortA</td>\n      <td>AUD</td>\n      <td>20210713</td>\n      <td>FSC</td>\n      <td>10</td>\n      <td>50,000,000.00</td>\n      <td>C</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>1</td>\n      <td>PortA</td>\n      <td>CAD</td>\n      <td>20210713</td>\n      <td>FSC</td>\n      <td>7</td>\n      <td>138,234,286.00</td>\n      <td>C</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>2</td>\n      <td>PortA</td>\n      <td>CHF</td>\n      <td>20210713</td>\n      <td>FSC</td>\n      <td>3</td>\n      <td>203,402.00</td>\n      <td>C</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>3</td>\n      <td>PortA</td>\n      <td>EUR</td>\n      <td>20210713</td>\n      <td>FSC</td>\n      <td>2</td>\n      <td>235,402,950.00</td>\n      <td>C</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>4</td>\n      <td>PortA</td>\n      <td>EUR</td>\n      <td>20210715</td>\n      <td>FTC</td>\n      <td>3</td>\n      <td>22,342,054.00</td>\n      <td>C</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>5</td>\n      <td>PortA</td>\n      <td>EUR</td>\n      <td>20210727</td>\n      <td>FTC</td>\n      <td>5</td>\n      <td>2,349,130,942.00</td>\n      <td>C</td>\n    </tr>\n    <tr>\n      <th>6</th>\n      <td>6</td>\n      <td>PortA</td>\n      <td>EUR</td>\n      <td>20210812</td>\n      <td>FTC</td>\n      <td>4</td>\n      <td>23,489,234.00</td>\n      <td>C</td>\n    </tr>\n    <tr>\n      <th>7</th>\n      <td>7</td>\n      <td>PortA</td>\n      <td>GBP</td>\n      <td>20210713</td>\n      <td>FSC</td>\n      <td>6</td>\n      <td>83,425,656.00</td>\n      <td>C</td>\n    </tr>\n    <tr>\n      <th>8</th>\n      <td>8</td>\n      <td>PortA</td>\n      <td>GBP</td>\n      <td>20210812</td>\n      <td>FTC</td>\n      <td>9</td>\n      <td>54,354,564.00</td>\n      <td>C</td>\n    </tr>\n    <tr>\n      <th>9</th>\n      <td>9</td>\n      <td>PortA</td>\n      <td>JPY</td>\n      <td>20210713</td>\n      <td>FSC</td>\n      <td>9</td>\n      <td>654,659.00</td>\n      <td>C</td>\n    </tr>\n    <tr>\n      <th>10</th>\n      <td>10</td>\n      <td>PortA</td>\n      <td>JPY</td>\n      <td>20210812</td>\n      <td>FTC</td>\n      <td>9</td>\n      <td>150,000.00</td>\n      <td>C</td>\n    </tr>\n    <tr>\n      <th>11</th>\n      <td>11</td>\n      <td>PortA</td>\n      <td>NOK</td>\n      <td>20210713</td>\n      <td>FSC</td>\n      <td>8</td>\n      <td>45,645.00</td>\n      <td>C</td>\n    </tr>\n    <tr>\n      <th>12</th>\n      <td>12</td>\n      <td>PortA</td>\n      <td>NZD</td>\n      <td>20210713</td>\n      <td>FSC</td>\n      <td>5</td>\n      <td>65,454.00</td>\n      <td>C</td>\n    </tr>\n    <tr>\n      <th>13</th>\n      <td>13</td>\n      <td>PortA</td>\n      <td>USD</td>\n      <td>20210713</td>\n      <td>FSC</td>\n      <td>2</td>\n      <td>-174,328.00</td>\n      <td>C</td>\n    </tr>\n    <tr>\n      <th>14</th>\n      <td>14</td>\n      <td>PortA</td>\n      <td>USD</td>\n      <td>20210714</td>\n      <td>FTC</td>\n      <td>6</td>\n      <td>16,875.00</td>\n      <td>C</td>\n    </tr>\n    <tr>\n      <th>15</th>\n      <td>15</td>\n      <td>PortA</td>\n      <td>USD</td>\n      <td>20210715</td>\n      <td>FTC</td>\n      <td>6</td>\n      <td>454,654.00</td>\n      <td>C</td>\n    </tr>\n    <tr>\n      <th>16</th>\n      <td>16</td>\n      <td>PortA</td>\n      <td>USD</td>\n      <td>20210716</td>\n      <td>FTC</td>\n      <td>4</td>\n      <td>454,534,000,000.00</td>\n      <td>C</td>\n    </tr>\n    <tr>\n      <th>17</th>\n      <td>17</td>\n      <td>PortA</td>\n      <td>USD</td>\n      <td>20210719</td>\n      <td>FTC</td>\n      <td>8</td>\n      <td>534,343.00</td>\n      <td>C</td>\n    </tr>\n    <tr>\n      <th>18</th>\n      <td>18</td>\n      <td>PortA</td>\n      <td>USD</td>\n      <td>20210723</td>\n      <td>FTC</td>\n      <td>4</td>\n      <td>101,001.00</td>\n      <td>C</td>\n    </tr>\n    <tr>\n      <th>19</th>\n      <td>19</td>\n      <td>PortA</td>\n      <td>USD</td>\n      <td>20210726</td>\n      <td>FTC</td>\n      <td>10</td>\n      <td>4,534.00</td>\n      <td>C</td>\n    </tr>\n    <tr>\n      <th>20</th>\n      <td>20</td>\n      <td>PortA</td>\n      <td>USD</td>\n      <td>20210727</td>\n      <td>FTC</td>\n      <td>10</td>\n      <td>544,343.00</td>\n      <td>C</td>\n    </tr>\n    <tr>\n      <th>21</th>\n      <td>21</td>\n      <td>PortA</td>\n      <td>USD</td>\n      <td>20210729</td>\n      <td>FTC</td>\n      <td>4</td>\n      <td>3,535,454.00</td>\n      <td>C</td>\n    </tr>\n    <tr>\n      <th>22</th>\n      <td>22</td>\n      <td>PortA</td>\n      <td>USD</td>\n      <td>20210730</td>\n      <td>FTC</td>\n      <td>8</td>\n      <td>12,116.00</td>\n      <td>C</td>\n    </tr>\n    <tr>\n      <th>23</th>\n      <td>23</td>\n      <td>PortA</td>\n      <td>USD</td>\n      <td>20210806</td>\n      <td>FTC</td>\n      <td>1</td>\n      <td>45,453,435.00</td>\n      <td>C</td>\n    </tr>\n    <tr>\n      <th>24</th>\n      <td>24</td>\n      <td>PortA</td>\n      <td>USD</td>\n      <td>20210810</td>\n      <td>FTC</td>\n      <td>10</td>\n      <td>18,406.00</td>\n      <td>C</td>\n    </tr>\n    <tr>\n      <th>25</th>\n      <td>25</td>\n      <td>PortA</td>\n      <td>USD</td>\n      <td>20210812</td>\n      <td>FTC</td>\n      <td>10</td>\n      <td>545,343,543.00</td>\n      <td>B</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": "'PortB'"
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": "   Unnamed: 0   Port Currency      Date  Sys  Weight             Amount Class\n0          26  PortB      AUD  20210713  FSC      10       5,590,075.00     B\n1          27  PortB      AUD  20210812  FTC      10       5,453,435.00     B\n2          28  PortB      SGD  20210713  FSC       8      43,545,554.00     B\n3          29  PortB      USD  20210713  FSC       9 543,543,000,000.00     B\n4          30  PortB      EUR  20210713  FSC       8      54,545,435.00     B",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>Unnamed: 0</th>\n      <th>Port</th>\n      <th>Currency</th>\n      <th>Date</th>\n      <th>Sys</th>\n      <th>Weight</th>\n      <th>Amount</th>\n      <th>Class</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>26</td>\n      <td>PortB</td>\n      <td>AUD</td>\n      <td>20210713</td>\n      <td>FSC</td>\n      <td>10</td>\n      <td>5,590,075.00</td>\n      <td>B</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>27</td>\n      <td>PortB</td>\n      <td>AUD</td>\n      <td>20210812</td>\n      <td>FTC</td>\n      <td>10</td>\n      <td>5,453,435.00</td>\n      <td>B</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>28</td>\n      <td>PortB</td>\n      <td>SGD</td>\n      <td>20210713</td>\n      <td>FSC</td>\n      <td>8</td>\n      <td>43,545,554.00</td>\n      <td>B</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>29</td>\n      <td>PortB</td>\n      <td>USD</td>\n      <td>20210713</td>\n      <td>FSC</td>\n      <td>9</td>\n      <td>543,543,000,000.00</td>\n      <td>B</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>30</td>\n      <td>PortB</td>\n      <td>EUR</td>\n      <td>20210713</td>\n      <td>FSC</td>\n      <td>8</td>\n      <td>54,545,435.00</td>\n      <td>B</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": "'PortC'"
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": "    Unnamed: 0   Port Currency      Date  Sys  Weight             Amount Class\n0           31  PortC      AUD  20210713  FSC       9      75,174,369.00     C\n1           32  PortC      AUD  20210715  FTC       1         261,361.00     C\n2           33  PortC      AUD  20210716  FTC       3          73,055.00     C\n3           34  PortC      AUD  20210719  FTC       2       4,161,646.00     C\n4           35  PortC      AUD  20210720  FTC       2     787,878,787.00     C\n5           36  PortC      AUD  20210722  FTC       5     878,787,878.00     C\n6           37  PortC      AUD  20210723  FTC       3       3,349,533.00     C\n7           38  PortC      AUD  20210728  FTC       7       8,748,787.00     C\n8           39  PortC      AUD  20210812  FTC       4   4,864,848,616.00     C\n9           40  PortC      CAD  20210713  FSC      10     854,684,848.00     C\n10          41  PortC      CAD  20210810  FTC       1         175,973.00     C\n11          42  PortC      CAD  20210812  FTC       1         298,530.00     C\n12          43  PortC      CHF  20210713  FSC      10           1,063.00     C\n13          44  PortC      DKK  20210713  FSC       8           1,042.00     C\n14          45  PortC      EUR  20210713  FSC       5    -711,367,480.00     C\n15          46  PortC      EUR  20210809  FTC       8          77,925.00     C\n16          47  PortC      EUR  20210812  FTC       1         106,940.00     C\n17          48  PortC      GBP  20210713  FSC       8     -51,488,218.00     C\n18          49  PortC      GBP  20210730  FTC       9          56,250.00     C\n19          50  PortC      HKD  20210713  FSC       2             599.00     C\n20          51  PortC      JPY  20210713  FSC       4 -17,775,991,002.00     C\n21          52  PortC      JPY  20210812  FTC       9      26,122,500.00     C\n22          53  PortC      NOK  20210713  FSC       1             715.00     C\n23          54  PortC      NZD  20210713  FSC       7           1,091.00     C\n24          55  PortC      SEK  20210713  FSC       1           1,452.00     C\n25          56  PortC      SGD  20210713  FSC       8           1,167.00     C\n26          57  PortC      USD  20210713  FSC       9   1,374,469,992.00     C",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>Unnamed: 0</th>\n      <th>Port</th>\n      <th>Currency</th>\n      <th>Date</th>\n      <th>Sys</th>\n      <th>Weight</th>\n      <th>Amount</th>\n      <th>Class</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>31</td>\n      <td>PortC</td>\n      <td>AUD</td>\n      <td>20210713</td>\n      <td>FSC</td>\n      <td>9</td>\n      <td>75,174,369.00</td>\n      <td>C</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>32</td>\n      <td>PortC</td>\n      <td>AUD</td>\n      <td>20210715</td>\n      <td>FTC</td>\n      <td>1</td>\n      <td>261,361.00</td>\n      <td>C</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>33</td>\n      <td>PortC</td>\n      <td>AUD</td>\n      <td>20210716</td>\n      <td>FTC</td>\n      <td>3</td>\n      <td>73,055.00</td>\n      <td>C</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>34</td>\n      <td>PortC</td>\n      <td>AUD</td>\n      <td>20210719</td>\n      <td>FTC</td>\n      <td>2</td>\n      <td>4,161,646.00</td>\n      <td>C</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>35</td>\n      <td>PortC</td>\n      <td>AUD</td>\n      <td>20210720</td>\n      <td>FTC</td>\n      <td>2</td>\n      <td>787,878,787.00</td>\n      <td>C</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>36</td>\n      <td>PortC</td>\n      <td>AUD</td>\n      <td>20210722</td>\n      <td>FTC</td>\n      <td>5</td>\n      <td>878,787,878.00</td>\n      <td>C</td>\n    </tr>\n    <tr>\n      <th>6</th>\n      <td>37</td>\n      <td>PortC</td>\n      <td>AUD</td>\n      <td>20210723</td>\n      <td>FTC</td>\n      <td>3</td>\n      <td>3,349,533.00</td>\n      <td>C</td>\n    </tr>\n    <tr>\n      <th>7</th>\n      <td>38</td>\n      <td>PortC</td>\n      <td>AUD</td>\n      <td>20210728</td>\n      <td>FTC</td>\n      <td>7</td>\n      <td>8,748,787.00</td>\n      <td>C</td>\n    </tr>\n    <tr>\n      <th>8</th>\n      <td>39</td>\n      <td>PortC</td>\n      <td>AUD</td>\n      <td>20210812</td>\n      <td>FTC</td>\n      <td>4</td>\n      <td>4,864,848,616.00</td>\n      <td>C</td>\n    </tr>\n    <tr>\n      <th>9</th>\n      <td>40</td>\n      <td>PortC</td>\n      <td>CAD</td>\n      <td>20210713</td>\n      <td>FSC</td>\n      <td>10</td>\n      <td>854,684,848.00</td>\n      <td>C</td>\n    </tr>\n    <tr>\n      <th>10</th>\n      <td>41</td>\n      <td>PortC</td>\n      <td>CAD</td>\n      <td>20210810</td>\n      <td>FTC</td>\n      <td>1</td>\n      <td>175,973.00</td>\n      <td>C</td>\n    </tr>\n    <tr>\n      <th>11</th>\n      <td>42</td>\n      <td>PortC</td>\n      <td>CAD</td>\n      <td>20210812</td>\n      <td>FTC</td>\n      <td>1</td>\n      <td>298,530.00</td>\n      <td>C</td>\n    </tr>\n    <tr>\n      <th>12</th>\n      <td>43</td>\n      <td>PortC</td>\n      <td>CHF</td>\n      <td>20210713</td>\n      <td>FSC</td>\n      <td>10</td>\n      <td>1,063.00</td>\n      <td>C</td>\n    </tr>\n    <tr>\n      <th>13</th>\n      <td>44</td>\n      <td>PortC</td>\n      <td>DKK</td>\n      <td>20210713</td>\n      <td>FSC</td>\n      <td>8</td>\n      <td>1,042.00</td>\n      <td>C</td>\n    </tr>\n    <tr>\n      <th>14</th>\n      <td>45</td>\n      <td>PortC</td>\n      <td>EUR</td>\n      <td>20210713</td>\n      <td>FSC</td>\n      <td>5</td>\n      <td>-711,367,480.00</td>\n      <td>C</td>\n    </tr>\n    <tr>\n      <th>15</th>\n      <td>46</td>\n      <td>PortC</td>\n      <td>EUR</td>\n      <td>20210809</td>\n      <td>FTC</td>\n      <td>8</td>\n      <td>77,925.00</td>\n      <td>C</td>\n    </tr>\n    <tr>\n      <th>16</th>\n      <td>47</td>\n      <td>PortC</td>\n      <td>EUR</td>\n      <td>20210812</td>\n      <td>FTC</td>\n      <td>1</td>\n      <td>106,940.00</td>\n      <td>C</td>\n    </tr>\n    <tr>\n      <th>17</th>\n      <td>48</td>\n      <td>PortC</td>\n      <td>GBP</td>\n      <td>20210713</td>\n      <td>FSC</td>\n      <td>8</td>\n      <td>-51,488,218.00</td>\n      <td>C</td>\n    </tr>\n    <tr>\n      <th>18</th>\n      <td>49</td>\n      <td>PortC</td>\n      <td>GBP</td>\n      <td>20210730</td>\n      <td>FTC</td>\n      <td>9</td>\n      <td>56,250.00</td>\n      <td>C</td>\n    </tr>\n    <tr>\n      <th>19</th>\n      <td>50</td>\n      <td>PortC</td>\n      <td>HKD</td>\n      <td>20210713</td>\n      <td>FSC</td>\n      <td>2</td>\n      <td>599.00</td>\n      <td>C</td>\n    </tr>\n    <tr>\n      <th>20</th>\n      <td>51</td>\n      <td>PortC</td>\n      <td>JPY</td>\n      <td>20210713</td>\n      <td>FSC</td>\n      <td>4</td>\n      <td>-17,775,991,002.00</td>\n      <td>C</td>\n    </tr>\n    <tr>\n      <th>21</th>\n      <td>52</td>\n      <td>PortC</td>\n      <td>JPY</td>\n      <td>20210812</td>\n      <td>FTC</td>\n      <td>9</td>\n      <td>26,122,500.00</td>\n      <td>C</td>\n    </tr>\n    <tr>\n      <th>22</th>\n      <td>53</td>\n      <td>PortC</td>\n      <td>NOK</td>\n      <td>20210713</td>\n      <td>FSC</td>\n      <td>1</td>\n      <td>715.00</td>\n      <td>C</td>\n    </tr>\n    <tr>\n      <th>23</th>\n      <td>54</td>\n      <td>PortC</td>\n      <td>NZD</td>\n      <td>20210713</td>\n      <td>FSC</td>\n      <td>7</td>\n      <td>1,091.00</td>\n      <td>C</td>\n    </tr>\n    <tr>\n      <th>24</th>\n      <td>55</td>\n      <td>PortC</td>\n      <td>SEK</td>\n      <td>20210713</td>\n      <td>FSC</td>\n      <td>1</td>\n      <td>1,452.00</td>\n      <td>C</td>\n    </tr>\n    <tr>\n      <th>25</th>\n      <td>56</td>\n      <td>PortC</td>\n      <td>SGD</td>\n      <td>20210713</td>\n      <td>FSC</td>\n      <td>8</td>\n      <td>1,167.00</td>\n      <td>C</td>\n    </tr>\n    <tr>\n      <th>26</th>\n      <td>57</td>\n      <td>PortC</td>\n      <td>USD</td>\n      <td>20210713</td>\n      <td>FSC</td>\n      <td>9</td>\n      <td>1,374,469,992.00</td>\n      <td>C</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "def retrieve_data():\n",
    "    portfolio_ids = df['Port'].unique()\n",
    "\n",
    "    for portfolio_id in portfolio_ids:\n",
    "        srs_id = lm.StructuredResultDataId(\n",
    "            source = \"Client\", \n",
    "            code = portfolio_id, \n",
    "            effective_at = effective_date,\n",
    "            result_type = sample_data_result_type)\n",
    "        \n",
    "        result = srs_api.get_structured_result_data(\n",
    "            scope = scope, \n",
    "            request_body = {\n",
    "                \"key\": srs_id\n",
    "            }\n",
    "        )\n",
    "    \n",
    "        csv_data = io.StringIO(result.values[\"key\"].document)\n",
    "        doc = pd.read_csv(csv_data)\n",
    "        display(srs_id.code)\n",
    "        display(doc)\n",
    "        \n",
    "retrieve_data()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4f8d58de",
   "metadata": {},
   "source": [
    "## Extract a 'Virtual Document' from the Structured Results Store"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "71723d76",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": "  UnitResult/srs/Port UnitResult/srs/Weight UnitResult/srs/Amount  \\\n0               PortA                 10.00             18,406.00   \n0               PortA                  6.00            454,654.00   \n0               PortA                  3.00         22,342,054.00   \n0               PortA                  8.00            534,343.00   \n0               PortA                  2.00           -174,328.00   \n\n  UnitResult/srs/Class UnitResult/srs/Currency UnitResult/srs/Date  \\\n0                    C                     USD            20210810   \n0                    C                     USD            20210715   \n0                    C                     EUR            20210715   \n0                    C                     USD            20210719   \n0                    C                     USD            20210713   \n\n  UnitResult/srs/Sys  \n0                FTC  \n0                FTC  \n0                FTC  \n0                FTC  \n0                FSC  ",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>UnitResult/srs/Port</th>\n      <th>UnitResult/srs/Weight</th>\n      <th>UnitResult/srs/Amount</th>\n      <th>UnitResult/srs/Class</th>\n      <th>UnitResult/srs/Currency</th>\n      <th>UnitResult/srs/Date</th>\n      <th>UnitResult/srs/Sys</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>PortA</td>\n      <td>10.00</td>\n      <td>18,406.00</td>\n      <td>C</td>\n      <td>USD</td>\n      <td>20210810</td>\n      <td>FTC</td>\n    </tr>\n    <tr>\n      <th>0</th>\n      <td>PortA</td>\n      <td>6.00</td>\n      <td>454,654.00</td>\n      <td>C</td>\n      <td>USD</td>\n      <td>20210715</td>\n      <td>FTC</td>\n    </tr>\n    <tr>\n      <th>0</th>\n      <td>PortA</td>\n      <td>3.00</td>\n      <td>22,342,054.00</td>\n      <td>C</td>\n      <td>EUR</td>\n      <td>20210715</td>\n      <td>FTC</td>\n    </tr>\n    <tr>\n      <th>0</th>\n      <td>PortA</td>\n      <td>8.00</td>\n      <td>534,343.00</td>\n      <td>C</td>\n      <td>USD</td>\n      <td>20210719</td>\n      <td>FTC</td>\n    </tr>\n    <tr>\n      <th>0</th>\n      <td>PortA</td>\n      <td>2.00</td>\n      <td>-174,328.00</td>\n      <td>C</td>\n      <td>USD</td>\n      <td>20210713</td>\n      <td>FSC</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def retrieve_virtual_document():\n",
    "    portfolio_ids = df['Port'].unique()\n",
    "\n",
    "    for portfolio_id in portfolio_ids:\n",
    "        # Retrieve document from SRS\n",
    "        srs_id = lm.StructuredResultDataId(\n",
    "            source = \"Client\", \n",
    "            code = portfolio_id, \n",
    "            effective_at = effective_date,\n",
    "            result_type = sample_data_result_type)\n",
    "        \n",
    "        result = srs_api.get_virtual_document(\n",
    "            scope = scope, \n",
    "            request_body = {\n",
    "                \"key\": srs_id\n",
    "            }\n",
    "        )\n",
    "        \n",
    "        # Convert to DataFrame\n",
    "        result_dfs = []\n",
    "\n",
    "        for item in result.values[\"key\"].data:\n",
    "            columns = item.row_data.columns\n",
    "            values = [i.value for i in item.row_data.values]\n",
    "    \n",
    "            row_df = pd.DataFrame(values).T\n",
    "            row_df.columns = columns\n",
    "            \n",
    "            for row in item.row_id.items():            \n",
    "                row_df[row[0]] = [row[1]]\n",
    "\n",
    "            result_dfs.append(row_df)\n",
    "    \n",
    "        all_dfs = pd.concat(result_dfs)\n",
    "        \n",
    "        return all_dfs\n",
    "        \n",
    "all_dfs = retrieve_virtual_document()\n",
    "\n",
    "all_dfs.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1d6b13b1",
   "metadata": {},
   "source": [
    "<h2>Using Luminesce to access data in the Structured Result Store</h2>\n",
    "\n",
    "To access data in the SRS via Luminesce we use the Lusid.UnitResult.AtomisedResult provider which takes a scope, code, source, resultType, and effectiveAt and asAt parameters are mandatory values.\n",
    "\n",
    "<ul>\n",
    "<li>Scope will determine where to look for the document e.g UnitResult/scope/...</li>\n",
    "<li>Code + Source will uniquely define a document inside the specified scope</li>\n",
    "<li>ResultType indicates the type of document</li>\n",
    "<li>EffectiveAt and asAt are used to give time context to reterieving the document.</li>\n",
    "</ul>\n",
    "\n",
    "It's important to note that the SRS does not store information in a tabular form, and thus in order to massage it into tabular data we need to pass the result from the AtomisedResult provider through a pivot, to achieve this we use the Tools.Pivot provider."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "fc5cc689",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": "  UnitResult/srs/Class UnitResult/srs/Currency  UnitResult/srs/Date  \\\n0                    B                     USD             20210812   \n1                    C                     USD             20210713   \n2                    C                     USD             20210714   \n3                    C                     USD             20210715   \n4                    C                     USD             20210716   \n\n  UnitResult/srs/Sys  UnitResult/srs/Amount UnitResult/srs/Port  \\\n0                FTC              545343543               PortA   \n1                FSC                -174328               PortA   \n2                FTC                  16875               PortA   \n3                FTC                 454654               PortA   \n4                FTC           454534000000               PortA   \n\n   UnitResult/srs/Weight  \n0                     10  \n1                      2  \n2                      6  \n3                      6  \n4                      4  ",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>UnitResult/srs/Class</th>\n      <th>UnitResult/srs/Currency</th>\n      <th>UnitResult/srs/Date</th>\n      <th>UnitResult/srs/Sys</th>\n      <th>UnitResult/srs/Amount</th>\n      <th>UnitResult/srs/Port</th>\n      <th>UnitResult/srs/Weight</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>B</td>\n      <td>USD</td>\n      <td>20210812</td>\n      <td>FTC</td>\n      <td>545343543</td>\n      <td>PortA</td>\n      <td>10</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>C</td>\n      <td>USD</td>\n      <td>20210713</td>\n      <td>FSC</td>\n      <td>-174328</td>\n      <td>PortA</td>\n      <td>2</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>C</td>\n      <td>USD</td>\n      <td>20210714</td>\n      <td>FTC</td>\n      <td>16875</td>\n      <td>PortA</td>\n      <td>6</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>C</td>\n      <td>USD</td>\n      <td>20210715</td>\n      <td>FTC</td>\n      <td>454654</td>\n      <td>PortA</td>\n      <td>6</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>C</td>\n      <td>USD</td>\n      <td>20210716</td>\n      <td>FTC</td>\n      <td>454534000000</td>\n      <td>PortA</td>\n      <td>4</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import lumipy\n",
    "import luminesce\n",
    "from fbnsdkutilities import ApiClientFactory\n",
    "\n",
    "api_factory = ApiClientFactory(\n",
    "    luminesce,\n",
    "    token=RefreshingToken(),\n",
    "    api_secrets_filename=secrets_path,\n",
    "    app_name=\"LusidJupyterNotebook\",\n",
    ")\n",
    "\n",
    "lumi_sql_exe_api = api_factory.build(luminesce.SqlExecutionApi)\n",
    "\n",
    "res = lumi_sql_exe_api.put_by_query_csv(body=\"\"\"\n",
    "-- lumipy \n",
    "@@effAt = select datetime('now');\n",
    "@portfolio_data = use Lusid.UnitResult.AtomisedResult with @@effAt\n",
    "  --scope=srs\n",
    "  --code=PortA\n",
    "  --source=Client\n",
    "  --resultType=UnitResult/Custom\n",
    "  --effectiveAt={@@effAt:s}\n",
    "  --asAt={@@effAt:s}\n",
    "  enduse;\n",
    "\n",
    "@input = SELECT * FROM @portfolio_data WHERE `UnitResult/srs/Currency`=='USD';\n",
    "\n",
    "\n",
    "@x =\n",
    "select \n",
    "    DataKey,\n",
    "    [UnitResult/srs/Class],\n",
    "    [UnitResult/srs/Currency],\n",
    "    [UnitResult/srs/Date],\n",
    "    [UnitResult/srs/Sys],\n",
    "    max(coalesce(ValueReal, ValueText)) as Value\n",
    "from\n",
    "    @input\n",
    "group by\n",
    "    1, 2, 3, 4, 5\n",
    "    ;\n",
    "\n",
    "@pivoted = \n",
    "use Tools.Pivot with @x\n",
    "--aggregateColumns=Value\n",
    "enduse;\n",
    "\n",
    "select * from @pivoted;\"\"\",\n",
    "query_name=\"query\",\n",
    "timeout_seconds=30)\n",
    "\n",
    "buffer_result = io.StringIO(res)\n",
    "srs_client_filtered = pd.read_csv(buffer_result, encoding='utf-8')\n",
    "\n",
    "srs_client_filtered.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7fef24e3",
   "metadata": {},
   "source": [
    "## Cleanup - delete the data from the Structured Results Store"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "077622bf",
   "metadata": {},
   "outputs": [],
   "source": [
    "def delete_data():\n",
    "    portfolio_ids = df['Port'].unique()\n",
    "\n",
    "    for portfolio_id in portfolio_ids:\n",
    "        # Retrieve document from SRS\n",
    "        srs_id = lm.StructuredResultDataId(\n",
    "            source = \"Client\", \n",
    "            code = portfolio_id, \n",
    "            effective_at = effective_date,\n",
    "            result_type = sample_data_result_type)\n",
    "        \n",
    "        result = srs_api.delete_structured_result_data(\n",
    "            scope = scope, \n",
    "            request_body = {\n",
    "                \"key\": srs_id\n",
    "            }\n",
    "        )\n",
    "\n",
    "delete_data()"
   ]
  }
 ],
 "metadata": {
  "interpreter": {
   "hash": "6e2fdb6824b58df245d8c0f69db6a91fea5662329742c5abc6a03ace099cccbe"
  },
  "kernelspec": {
   "display_name": "Python 3.9.12 ('fbn-ci')",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}