finance/collateral-shortfall-monitoring/main.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Collateral Shortfall Monitoring\n",
    "\n",
    "For more context and definitions around collateral shortfall monitoring, [check out our article on atoti.io](https://www.atoti.io/rapid-collateral-modelling-and-simulation-with-atoti/).\n",
    "\n",
    "### Introduction\n",
    "\n",
    "In this notebook, we will showcase how quickly a dashboard can be put together for a simplified use case of Collateral Shortfall monitoring with atoti libraries.  \n",
    "  \n",
    "Collateral is a form of credit risk mitigation where an asset is accepted as security for extending a loan.  \n",
    "Market value of a collateral changes over time and lender has to accomodate for it. As such, depending on the amount of risk associated, a percentage of what is known as haircut is applied to the asset's market value. This gives the value of the collateral that can be used for loan, also known as collateral value.   \n",
    "  \n",
    "Collateral Shortfall occurs when the collateral value goes below the cash out value. That meant that the value of collateral is less than what it is expected to be, due to a variety of factors such as market fluctuations, contracts enforceability etc. \n",
    "\n",
    "We will be creating a multi-dimension data cube and derive the various measures such as market value, collateral value after haircut, cash out value over account and thereafter, the Collateral shortfall for the accounts. \n",
    "\n",
    "Leveraging on the data cube and atoti's data visualization, we will put together dashboards that reflects collateral status of accounts. Cherry on top, we will perform some *What if Analysis* to demonstrate the impact on Collateral in the below scenarios:  \n",
    "\n",
    "- Haircut simulation where there is a consumption slowdown in the Consumer Cyclical sector in Italy and France\n",
    "- Technology slump where price value for the sector dipped by 30%\n",
    "- Cash out simulation where cash out increase by 10%"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Welcome to atoti 0.4.0!\n",
      "\n",
      "By using this community edition, you agree with the license available at https://www.atoti.io/eula.\n",
      "Browse the official documentation at https://docs.atoti.io.\n",
      "Join the community at https://www.atoti.io/register.\n",
      "\n",
      "You can hide this message by setting the ATOTI_HIDE_EULA_MESSAGE environment variable to True.\n"
     ]
    }
   ],
   "source": [
    "import atoti as tt\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# tell atoti to load the database containing the UI dashboards\n",
    "config = tt.config.create_config(metadata_db=\"./metadata.db\")\n",
    "\n",
    "# a session has to be created for atoti\n",
    "session = tt.create_session(config=config)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Data loading\n",
    "A session is used to read data of formats csv, parquet, pandas dataframe, numpy and spark.   \n",
    "Refer to https://docs.atoti.io/0.3.1/tutorial/08-data-sources.html \n",
    "   \n",
    "#### Loading csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "asset_positions_store = session.read_csv(\n",
    "    \"s3://data.atoti.io/notebooks/collateral-shortfall-monitoring/assets_positions.csv\",\n",
    "    keys=[\"Account\", \"Asset_Code\"],\n",
    "    store_name=\"asset_positions_store\",\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "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></th>\n",
       "      <th>Quantity</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Account</th>\n",
       "      <th>Asset_Code</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Niel</th>\n",
       "      <th>CAP.PA</th>\n",
       "      <td>100000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SAN.PA</th>\n",
       "      <td>100000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Musk</th>\n",
       "      <th>ENI.PA</th>\n",
       "      <td>100000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ENGI.PA</th>\n",
       "      <td>100000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Bezos &amp; MacKenzie</th>\n",
       "      <th>AC.PA</th>\n",
       "      <td>100000.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                              Quantity\n",
       "Account           Asset_Code          \n",
       "Niel              CAP.PA      100000.0\n",
       "                  SAN.PA      100000.0\n",
       "Musk              ENI.PA      100000.0\n",
       "                  ENGI.PA     100000.0\n",
       "Bezos & MacKenzie AC.PA       100000.0"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# use store.head(n) preview the first n rows loaded into the store where needed\n",
    "# likewise, you can use store.columns to preview the columns in the store\n",
    "# or store.shape to see the number of rows and columns loaded into the store\n",
    "asset_positions_store.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "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></th>\n",
       "      <th>Price</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Asset_Code</th>\n",
       "      <th>Date</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">BNP.PA</th>\n",
       "      <th>2020-03-10</th>\n",
       "      <td>100.553603</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-03-09</th>\n",
       "      <td>99.605505</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-03-08</th>\n",
       "      <td>98.393106</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-03-07</th>\n",
       "      <td>98.996166</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-03-06</th>\n",
       "      <td>100.594698</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                            Price\n",
       "Asset_Code Date                  \n",
       "BNP.PA     2020-03-10  100.553603\n",
       "           2020-03-09   99.605505\n",
       "           2020-03-08   98.393106\n",
       "           2020-03-07   98.996166\n",
       "           2020-03-06  100.594698"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "assets_prices = session.read_csv(\n",
    "    \"s3://data.atoti.io/notebooks/collateral-shortfall-monitoring/assets_prices.csv\",\n",
    "    keys=[\"Asset_Code\", \"Date\"],\n",
    "    store_name=\"assets_prices_store\",\n",
    ")\n",
    "\n",
    "# by default, it will display the first 5 rows of the store\n",
    "assets_prices.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Loading parquet"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "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>Sector</th>\n",
       "      <th>Country</th>\n",
       "      <th>Haircut</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Asset_Code</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>BNP.PA</th>\n",
       "      <td>Financial Services</td>\n",
       "      <td>France</td>\n",
       "      <td>0.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CA.PA</th>\n",
       "      <td>Consumer Defensive</td>\n",
       "      <td>France</td>\n",
       "      <td>0.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AC.PA</th>\n",
       "      <td>Consumer Cyclical</td>\n",
       "      <td>France</td>\n",
       "      <td>0.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ENGI.PA</th>\n",
       "      <td>Utilities</td>\n",
       "      <td>France</td>\n",
       "      <td>0.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CAP.PA</th>\n",
       "      <td>Technology</td>\n",
       "      <td>France</td>\n",
       "      <td>0.1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        Sector Country  Haircut\n",
       "Asset_Code                                     \n",
       "BNP.PA      Financial Services  France      0.1\n",
       "CA.PA       Consumer Defensive  France      0.1\n",
       "AC.PA        Consumer Cyclical  France      0.1\n",
       "ENGI.PA              Utilities  France      0.1\n",
       "CAP.PA              Technology  France      0.1"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# atoti supports loading directly from a parquet file.\n",
    "# However an issue in 0.3.1 prevents from pulling parquet file from s3 protocol directly. Using pandas meanwhile.\n",
    "assets_attributes = pd.read_parquet(\n",
    "    \"https://data.atoti.io/notebooks/collateral-shortfall-monitoring/assets_attributes.parquet\"\n",
    ")\n",
    "assets_store = session.read_pandas(\n",
    "    assets_attributes, keys=[\"Asset_Code\"], store_name=\"assets_store\"\n",
    ")\n",
    "assets_store.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Loading csv via pandas before session loads pandas dataframe   \n",
    "Being able to load pandas dataframe gives us the flexibility to manipulate dataframe before loading them or later on when we do simulations."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "loans_positions_df = pd.read_csv(\n",
    "    \"http://data.atoti.io/notebooks/collateral-shortfall-monitoring/loans_positions.csv\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "loans_positions_store = session.read_pandas(\n",
    "    loans_positions_df, keys=[\"Account\"], store_name=\"loans_positions_store\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "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>Cash_Out</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Account</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Bezos &amp; MacKenzie</th>\n",
       "      <td>16000000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Buffet</th>\n",
       "      <td>54000000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Daniel Ek</th>\n",
       "      <td>9000000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Gates &amp; Melinda</th>\n",
       "      <td>17000000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Musk</th>\n",
       "      <td>9000000.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     Cash_Out\n",
       "Account                      \n",
       "Bezos & MacKenzie  16000000.0\n",
       "Buffet             54000000.0\n",
       "Daniel Ek           9000000.0\n",
       "Gates & Melinda    17000000.0\n",
       "Musk                9000000.0"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "loans_positions_store.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Joining datastore"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "asset_positions_store.join(assets_store, mapping={\"Asset_Code\": \"Asset_Code\"})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "asset_positions_store.join(loans_positions_store, mapping={\"Account\": \"Account\"})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "asset_positions_store.join(assets_prices, mapping={\"Asset_Code\": \"Asset_Code\"})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Cube creation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "asset_positions_cube = session.create_cube(\n",
    "    asset_positions_store, \"Collateral_Management\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/svg+xml": [
       "<svg height=\"513px\" viewBox=\"0.00 0.00 570.00 512.50\" width=\"570px\" xmlns=\"http://www.w3.org/2000/svg\" xmlns:xlink=\"http://www.w3.org/1999/xlink\">\n",
       "<g class=\"graph\" id=\"graph0\" transform=\"scale(1.0 1.0) rotate(0) translate(4 508.5)\">\n",
       "<title>Collateral_Management schema</title>\n",
       "<polygon fill=\"white\" points=\"-4,4 -4,-508.5 566,-508.5 566,4 -4,4\" stroke=\"transparent\"/>\n",
       "<!-- asset_positions_store -->\n",
       "<g class=\"node\" id=\"node1\">\n",
       "<title>asset_positions_store</title>\n",
       "<polygon fill=\"none\" points=\"8.5,-271 8.5,-308 175.5,-308 175.5,-271 8.5,-271\" stroke=\"black\"/>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" font-weight=\"bold\" text-anchor=\"start\" x=\"28.5\" y=\"-286.8\">asset_positions_store</text>\n",
       "<polygon fill=\"none\" points=\"8.5,-234 8.5,-271 175.5,-271 175.5,-234 8.5,-234\" stroke=\"black\"/>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" font-weight=\"bold\" text-anchor=\"start\" x=\"19.5\" y=\"-249.8\">Key</text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"start\" x=\"44.5\" y=\"-249.8\">  Account </text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" font-style=\"italic\" text-anchor=\"start\" x=\"104.5\" y=\"-249.8\">String</text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"start\" x=\"140.5\" y=\"-249.8\"> </text>\n",
       "<polygon fill=\"none\" points=\"8.5,-197 8.5,-234 175.5,-234 175.5,-197 8.5,-197\" stroke=\"black\"/>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" font-weight=\"bold\" text-anchor=\"start\" x=\"19.5\" y=\"-212.8\">Key</text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"start\" x=\"44.5\" y=\"-212.8\">  Asset_Code </text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" font-style=\"italic\" text-anchor=\"start\" x=\"124.5\" y=\"-212.8\">String</text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"start\" x=\"160.5\" y=\"-212.8\"> </text>\n",
       "<polygon fill=\"none\" points=\"8.5,-160 8.5,-197 175.5,-197 175.5,-160 8.5,-160\" stroke=\"black\"/>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"start\" x=\"19.5\" y=\"-175.8\">Quantity </text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" font-style=\"italic\" text-anchor=\"start\" x=\"74.5\" y=\"-175.8\">double</text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"start\" x=\"114.5\" y=\"-175.8\"> </text>\n",
       "</g>\n",
       "<!-- assets_store -->\n",
       "<g class=\"node\" id=\"node2\">\n",
       "<title>assets_store</title>\n",
       "<polygon fill=\"none\" points=\"387.5,-463 387.5,-500 554.5,-500 554.5,-463 387.5,-463\" stroke=\"black\"/>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" font-weight=\"bold\" text-anchor=\"start\" x=\"435.5\" y=\"-478.8\">assets_store</text>\n",
       "<polygon fill=\"none\" points=\"387.5,-426 387.5,-463 554.5,-463 554.5,-426 387.5,-426\" stroke=\"black\"/>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" font-weight=\"bold\" text-anchor=\"start\" x=\"398.5\" y=\"-441.8\">Key</text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"start\" x=\"423.5\" y=\"-441.8\">  Asset_Code </text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" font-style=\"italic\" text-anchor=\"start\" x=\"503.5\" y=\"-441.8\">String</text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"start\" x=\"539.5\" y=\"-441.8\"> </text>\n",
       "<polygon fill=\"none\" points=\"387.5,-389 387.5,-426 554.5,-426 554.5,-389 387.5,-389\" stroke=\"black\"/>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"start\" x=\"398.5\" y=\"-404.8\">Sector </text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" font-style=\"italic\" text-anchor=\"start\" x=\"438.5\" y=\"-404.8\">String</text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"start\" x=\"474.5\" y=\"-404.8\"> </text>\n",
       "<polygon fill=\"none\" points=\"387.5,-352 387.5,-389 554.5,-389 554.5,-352 387.5,-352\" stroke=\"black\"/>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"start\" x=\"398.5\" y=\"-367.8\">Country </text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" font-style=\"italic\" text-anchor=\"start\" x=\"450.5\" y=\"-367.8\">String</text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"start\" x=\"486.5\" y=\"-367.8\"> </text>\n",
       "<polygon fill=\"none\" points=\"387.5,-315 387.5,-352 554.5,-352 554.5,-315 387.5,-315\" stroke=\"black\"/>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"start\" x=\"398.5\" y=\"-330.8\">Haircut </text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" font-style=\"italic\" text-anchor=\"start\" x=\"444.5\" y=\"-330.8\">double</text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"start\" x=\"484.5\" y=\"-330.8\"> </text>\n",
       "</g>\n",
       "<!-- asset_positions_store&#45;&gt;assets_store -->\n",
       "<g class=\"edge\" id=\"edge3\">\n",
       "<title>asset_positions_store-&gt;assets_store</title>\n",
       "<path d=\"M183.28,-275.9C239.53,-301.86 311.84,-335.23 369.7,-361.94\" fill=\"none\" stroke=\"black\"/>\n",
       "<polygon fill=\"black\" points=\"368.31,-365.15 378.85,-366.16 371.24,-358.8 368.31,-365.15\" stroke=\"black\"/>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"middle\" x=\"281\" y=\"-359.8\">Asset_Code → Asset_Code</text>\n",
       "</g>\n",
       "<!-- loans_positions_store -->\n",
       "<g class=\"node\" id=\"node3\">\n",
       "<title>loans_positions_store</title>\n",
       "<polygon fill=\"none\" points=\"394.5,-252 394.5,-289 547.5,-289 547.5,-252 394.5,-252\" stroke=\"black\"/>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" font-weight=\"bold\" text-anchor=\"start\" x=\"405.5\" y=\"-267.8\">loans_positions_store</text>\n",
       "<polygon fill=\"none\" points=\"394.5,-215 394.5,-252 547.5,-252 547.5,-215 394.5,-215\" stroke=\"black\"/>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" font-weight=\"bold\" text-anchor=\"start\" x=\"405.5\" y=\"-230.8\">Key</text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"start\" x=\"430.5\" y=\"-230.8\">  Account </text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" font-style=\"italic\" text-anchor=\"start\" x=\"490.5\" y=\"-230.8\">String</text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"start\" x=\"526.5\" y=\"-230.8\"> </text>\n",
       "<polygon fill=\"none\" points=\"394.5,-178 394.5,-215 547.5,-215 547.5,-178 394.5,-178\" stroke=\"black\"/>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"start\" x=\"405.5\" y=\"-193.8\">Cash_Out </text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" font-style=\"italic\" text-anchor=\"start\" x=\"467.5\" y=\"-193.8\">double</text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"start\" x=\"507.5\" y=\"-193.8\"> </text>\n",
       "</g>\n",
       "<!-- asset_positions_store&#45;&gt;loans_positions_store -->\n",
       "<g class=\"edge\" id=\"edge2\">\n",
       "<title>asset_positions_store-&gt;loans_positions_store</title>\n",
       "<path d=\"M183.28,-234C241.57,-234 317.09,-234 375.92,-234\" fill=\"none\" stroke=\"black\"/>\n",
       "<polygon fill=\"black\" points=\"375.95,-237.5 385.95,-234 375.95,-230.5 375.95,-237.5\" stroke=\"black\"/>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"middle\" x=\"281\" y=\"-237.8\">Account → Account</text>\n",
       "</g>\n",
       "<!-- assets_prices_store -->\n",
       "<g class=\"node\" id=\"node4\">\n",
       "<title>assets_prices_store</title>\n",
       "<polygon fill=\"none\" points=\"387.5,-115 387.5,-152 554.5,-152 554.5,-115 387.5,-115\" stroke=\"black\"/>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" font-weight=\"bold\" text-anchor=\"start\" x=\"414\" y=\"-130.8\">assets_prices_store</text>\n",
       "<polygon fill=\"none\" points=\"387.5,-78 387.5,-115 554.5,-115 554.5,-78 387.5,-78\" stroke=\"black\"/>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" font-weight=\"bold\" text-anchor=\"start\" x=\"398.5\" y=\"-93.8\">Key</text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"start\" x=\"423.5\" y=\"-93.8\">  Asset_Code </text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" font-style=\"italic\" text-anchor=\"start\" x=\"503.5\" y=\"-93.8\">String</text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"start\" x=\"539.5\" y=\"-93.8\"> </text>\n",
       "<polygon fill=\"none\" points=\"387.5,-41 387.5,-78 554.5,-78 554.5,-41 387.5,-41\" stroke=\"black\"/>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" font-weight=\"bold\" text-anchor=\"start\" x=\"398.5\" y=\"-56.8\">Key</text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"start\" x=\"423.5\" y=\"-56.8\">  Date </text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" font-style=\"italic\" text-anchor=\"start\" x=\"461.5\" y=\"-56.8\">LocalDate</text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"start\" x=\"522.5\" y=\"-56.8\"> </text>\n",
       "<polygon fill=\"none\" points=\"387.5,-4 387.5,-41 554.5,-41 554.5,-4 387.5,-4\" stroke=\"black\"/>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"start\" x=\"398.5\" y=\"-19.8\">Price </text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" font-style=\"italic\" text-anchor=\"start\" x=\"431.5\" y=\"-19.8\">double</text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"start\" x=\"471.5\" y=\"-19.8\"> </text>\n",
       "</g>\n",
       "<!-- asset_positions_store&#45;&gt;assets_prices_store -->\n",
       "<g class=\"edge\" id=\"edge1\">\n",
       "<title>asset_positions_store-&gt;assets_prices_store</title>\n",
       "<path d=\"M183.28,-196.44C239.42,-173.21 311.54,-143.36 369.34,-119.45\" fill=\"none\" stroke=\"black\" stroke-dasharray=\"5,2\"/>\n",
       "<polygon fill=\"black\" points=\"370.95,-122.57 378.85,-115.51 368.28,-116.1 370.95,-122.57\" stroke=\"black\"/>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"middle\" x=\"281\" y=\"-190.8\">Asset_Code → Asset_Code</text>\n",
       "</g>\n",
       "</g>\n",
       "</svg>"
      ],
      "text/plain": [
       "<IPython.core.display.SVG object>"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "asset_positions_cube.schema"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Quick analysis with cube.visualize"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "atoti": {
     "state": {
      "name": "",
      "type": "container",
      "value": {
       "body": {
        "configuration": {
         "tabular": {
          "addButtonFilter": "numeric",
          "columnsGroups": [
           {
            "captionProducer": "firstColumn",
            "cellFactory": "kpi-status",
            "selector": "kpi-status"
           },
           {
            "captionProducer": "firstColumn",
            "cellFactory": "lookup",
            "selector": "lookup"
           },
           {
            "captionProducer": "expiry",
            "cellFactory": "expiry",
            "selector": "kpi-expiry"
           },
           {
            "captionProducer": "columnMerge",
            "cellFactory": {
             "args": {
              "automaticExpansion": true
             },
             "key": "treeCells"
            },
            "selector": "member"
           }
          ],
          "defaultOptions": {},
          "hideAddButton": true,
          "lineNumbers": true,
          "pinnedHeaderSelector": "member",
          "sortingMode": "non-breaking",
          "statisticsShown": true
         }
        },
        "contextValues": {},
        "mdx": "SELECT NON EMPTY {[Measures].[Quantity.SUM], [Measures].[Cash_Out.VALUE]} ON COLUMNS, NON EMPTY Crossjoin(Hierarchize(DrilldownLevel([Hierarchies].[Account].[ALL].[AllMember])), Hierarchize(DrilldownLevel([Hierarchies].[Sector].[ALL].[AllMember])), Hierarchize(DrilldownLevel([Hierarchies].[Country].[ALL].[AllMember]))) ON ROWS FROM [Collateral_Management] CELL PROPERTIES VALUE, FORMATTED_VALUE, BACK_COLOR, FORE_COLOR, FONT_FLAGS",
        "ranges": {
         "column": {
          "chunkSize": 50,
          "thresholdPercentage": 0.2
         },
         "row": {
          "chunkSize": 2000,
          "thresholdPercentage": 0.1
         }
        },
        "serverUrl": "",
        "updateMode": "once"
       },
       "containerKey": "pivot-table",
       "showTitleBar": false,
       "style": {}
      }
     }
    }
   },
   "outputs": [
    {
     "data": {
      "application/vnd.atoti.v0+json": {
       "cube": "Collateral_Management",
       "name": null,
       "session": "Unnamed"
      },
      "text/plain": [
       "Install and enable the atoti JupyterLab extension to see this widget."
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# we can perform drill-down to different hierarchies in a pivot table\n",
    "asset_positions_cube.visualize()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "atoti": {
     "state": {
      "name": "times-series",
      "type": "container",
      "value": {
       "body": {
        "configuration": {
         "mapping": {
          "horizontal": [],
          "splitBy": [
           "[Hierarchies].[Asset_Code].[Asset_Code]"
          ],
          "values": [
           "[Measures].[Price.VALUE]"
          ],
          "vertical": [],
          "xAxis": [
           "[Hierarchies].[Date].[Date]"
          ]
         },
         "type": "plotly-line-chart"
        },
        "query": {
         "mdx": "SELECT NON EMPTY Crossjoin([Hierarchies].[Date].[Date].Members, [Hierarchies].[Asset_Code].[Asset_Code].Members) ON ROWS, NON EMPTY [Measures].[Price.VALUE] ON COLUMNS FROM [Collateral_Management] CELL PROPERTIES VALUE, FORMATTED_VALUE, BACK_COLOR, FORE_COLOR, FONT_FLAGS",
         "serverUrl": "",
         "updateMode": "once"
        }
       },
       "containerKey": "chart",
       "showTitleBar": false,
       "style": {}
      }
     }
    }
   },
   "outputs": [
    {
     "data": {
      "application/vnd.atoti.v0+json": {
       "cube": "Collateral_Management",
       "name": "times-series",
       "session": "Unnamed"
      },
      "text/plain": [
       "Install and enable the atoti JupyterLab extension to see this widget."
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# we can look at the price.VALUE across Date, further split the charts by Asset_Code\n",
    "asset_positions_cube.visualize(\"times-series\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "atoti": {
     "state": {
      "name": "haircut-value",
      "type": "container",
      "value": {
       "body": {
        "configuration": {
         "tabular": {
          "addButtonFilter": "numeric",
          "columnsGroups": [
           {
            "captionProducer": "firstColumn",
            "cellFactory": "kpi-status",
            "selector": "kpi-status"
           },
           {
            "captionProducer": "firstColumn",
            "cellFactory": "lookup",
            "selector": "lookup"
           },
           {
            "captionProducer": "expiry",
            "cellFactory": "expiry",
            "selector": "kpi-expiry"
           },
           {
            "captionProducer": "columnMerge",
            "cellFactory": {
             "args": {
              "automaticExpansion": true
             },
             "key": "treeCells"
            },
            "selector": "member"
           }
          ],
          "defaultOptions": {},
          "hideAddButton": true,
          "lineNumbers": true,
          "pinnedHeaderSelector": "member",
          "sortingMode": "non-breaking",
          "statisticsShown": true
         }
        },
        "contextValues": {},
        "mdx": "SELECT NON EMPTY {[Measures].[Haircut.VALUE]} ON COLUMNS, NON EMPTY Crossjoin(Hierarchize(DrilldownLevel([Hierarchies].[Country].[ALL].[AllMember])), Hierarchize(DrilldownLevel([Hierarchies].[Sector].[ALL].[AllMember])), Hierarchize(DrilldownLevel([Hierarchies].[Asset_Code].[ALL].[AllMember]))) ON ROWS FROM [Collateral_Management] CELL PROPERTIES VALUE, FORMATTED_VALUE, BACK_COLOR, FORE_COLOR, FONT_FLAGS",
        "ranges": {
         "column": {
          "chunkSize": 50,
          "thresholdPercentage": 0.2
         },
         "row": {
          "chunkSize": 2000,
          "thresholdPercentage": 0.1
         }
        },
        "serverUrl": "",
        "updateMode": "once"
       },
       "containerKey": "pivot-table",
       "showTitleBar": false,
       "style": {}
      }
     }
    }
   },
   "outputs": [
    {
     "data": {
      "application/vnd.atoti.v0+json": {
       "cube": "Collateral_Management",
       "name": "haircut-value",
       "session": "Unnamed"
      },
      "text/plain": [
       "Install and enable the atoti JupyterLab extension to see this widget."
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# give a meaningful title to the visualization. This helps to reconcile the objective of the visual and also could be the title of\n",
    "# the widget when visual is published\n",
    "asset_positions_cube.visualize(\"haircut-value\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "atoti": {
     "state": {
      "name": "explore-dataset-using-pivot-table",
      "type": "container",
      "value": {
       "body": {
        "configuration": {
         "tabular": {
          "addButtonFilter": "numeric",
          "columnsGroups": [
           {
            "captionProducer": "firstColumn",
            "cellFactory": "kpi-status",
            "selector": "kpi-status"
           },
           {
            "captionProducer": "firstColumn",
            "cellFactory": "lookup",
            "selector": "lookup"
           },
           {
            "captionProducer": "expiry",
            "cellFactory": "expiry",
            "selector": "kpi-expiry"
           },
           {
            "captionProducer": "columnMerge",
            "cellFactory": {
             "args": {
              "automaticExpansion": true
             },
             "key": "treeCells"
            },
            "selector": "member"
           }
          ],
          "defaultOptions": {},
          "hideAddButton": true,
          "lineNumbers": true,
          "pinnedHeaderSelector": "member",
          "sortingMode": "non-breaking",
          "statisticsShown": true
         }
        },
        "contextValues": {},
        "mdx": "SELECT NON EMPTY {[Measures].[Quantity.SUM]} ON COLUMNS, NON EMPTY Crossjoin(Hierarchize(DrilldownLevel([Hierarchies].[Account].[ALL].[AllMember])), Hierarchize(DrilldownLevel([Hierarchies].[Sector].[ALL].[AllMember]))) ON ROWS FROM [Collateral_Management] CELL PROPERTIES VALUE, FORMATTED_VALUE, BACK_COLOR, FORE_COLOR, FONT_FLAGS",
        "ranges": {
         "column": {
          "chunkSize": 50,
          "thresholdPercentage": 0.2
         },
         "row": {
          "chunkSize": 2000,
          "thresholdPercentage": 0.1
         }
        },
        "serverUrl": "",
        "updateMode": "once"
       },
       "containerKey": "pivot-table",
       "showTitleBar": false,
       "style": {}
      }
     }
    }
   },
   "outputs": [
    {
     "data": {
      "application/vnd.atoti.v0+json": {
       "cube": "Collateral_Management",
       "name": "explore-dataset-using-pivot-table",
       "session": "Unnamed"
      },
      "text/plain": [
       "Install and enable the atoti JupyterLab extension to see this widget."
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "asset_positions_cube.visualize(\"explore-dataset-using-pivot-table\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Cube structure\n",
    "During cube creation, numeric values are automatically created as measures. Non numeric values are automatically translated to levels under hierarchy of the same name. This can be [configured](https://www.atoti.io/documentation/lib/atoti.html#atoti.session.Session.create_cube) differently."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "m = asset_positions_cube.measures\n",
    "h = asset_positions_cube.hierarchies\n",
    "lvl = asset_positions_cube.levels"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Before we proceed with the data aggregation aspects, let's inspect the hierarchies created"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/json": {
       "Hierarchies": {
        "Account": [
         "Account"
        ],
        "Asset_Code": [
         "Asset_Code"
        ],
        "Country": [
         "Country"
        ],
        "Date": [
         "Date"
        ],
        "Sector": [
         "Sector"
        ]
       }
      },
      "text/html": [
       "<ul>\n",
       "<li>Dimensions\n",
       "  <ul>\n",
       "  <li>Hierarchies\n",
       "    <ul>\n",
       "    <li>Account\n",
       "      <ol>\n",
       "      <li>Account</li>\n",
       "      </ol>    </li>\n",
       "    <li>Asset_Code\n",
       "      <ol>\n",
       "      <li>Asset_Code</li>\n",
       "      </ol>    </li>\n",
       "    <li>Country\n",
       "      <ol>\n",
       "      <li>Country</li>\n",
       "      </ol>    </li>\n",
       "    <li>Date\n",
       "      <ol>\n",
       "      <li>Date</li>\n",
       "      </ol>    </li>\n",
       "    <li>Sector\n",
       "      <ol>\n",
       "      <li>Sector</li>\n",
       "      </ol>    </li>\n",
       "    </ul>\n",
       "  </li>\n",
       "  </ul>\n",
       "</li>\n",
       "</ul>\n"
      ],
      "text/plain": [
       "Hierarchies()"
      ]
     },
     "execution_count": 23,
     "metadata": {
      "application/json": {
       "expanded": true,
       "root": "Dimensions"
      }
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "h"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We are going to set the hierarchy *Date* as a slicing hierarchy. A slicing hierarchy will not aggregate the data on all its members.  \n",
    "This means that we always view a subset of the cube by one date by default, which is usually what is needed."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "h[\"Date\"].slicing = True"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/json": {
       "Cash_Out.VALUE": {
        "formatter": "DOUBLE[#,###.00]",
        "visible": true
       },
       "Haircut.VALUE": {
        "formatter": "DOUBLE[#,###.00]",
        "visible": true
       },
       "Price.VALUE": {
        "formatter": "DOUBLE[#,###.00]",
        "visible": true
       },
       "Quantity.MEAN": {
        "formatter": "DOUBLE[#,###.00]",
        "visible": true
       },
       "Quantity.SUM": {
        "formatter": "DOUBLE[#,###.00]",
        "visible": true
       },
       "contributors.COUNT": {
        "formatter": null,
        "visible": true
       }
      },
      "text/html": [
       "<ul>\n",
       "<li>Measures\n",
       "  <ul>\n",
       "  <li>Cash_Out.VALUE\n",
       "    <ul>\n",
       "    <li>formatter: DOUBLE[#,###.00]</li>\n",
       "    <li>visible: True</li>\n",
       "    </ul>\n",
       "  </li>\n",
       "  <li>Haircut.VALUE\n",
       "    <ul>\n",
       "    <li>formatter: DOUBLE[#,###.00]</li>\n",
       "    <li>visible: True</li>\n",
       "    </ul>\n",
       "  </li>\n",
       "  <li>Price.VALUE\n",
       "    <ul>\n",
       "    <li>formatter: DOUBLE[#,###.00]</li>\n",
       "    <li>visible: True</li>\n",
       "    </ul>\n",
       "  </li>\n",
       "  <li>Quantity.MEAN\n",
       "    <ul>\n",
       "    <li>formatter: DOUBLE[#,###.00]</li>\n",
       "    <li>visible: True</li>\n",
       "    </ul>\n",
       "  </li>\n",
       "  <li>Quantity.SUM\n",
       "    <ul>\n",
       "    <li>formatter: DOUBLE[#,###.00]</li>\n",
       "    <li>visible: True</li>\n",
       "    </ul>\n",
       "  </li>\n",
       "  <li>contributors.COUNT\n",
       "    <ul>\n",
       "    <li>formatter: None</li>\n",
       "    <li>visible: True</li>\n",
       "    </ul>\n",
       "  </li>\n",
       "  </ul>\n",
       "</li>\n",
       "</ul>\n"
      ],
      "text/plain": [
       "Measures()"
      ]
     },
     "execution_count": 25,
     "metadata": {
      "application/json": {
       "expanded": false,
       "root": "Measures"
      }
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "m"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Creating new measures  \n",
    "From the data we have, we can derive the following:   \n",
    "$Market Value = Price \\times Quantity$  \n",
    "$Collateral Value = Market Value \\times (1 - Haircut)$   \n",
    "  \n",
    "The above measures are aggregated over the Account and Asset Code levels in order to compute the Collateral Shortfall at account level:  \n",
    "$Collateral Shortfall = Collateral Value - Cash Out$   \n",
    "Where Cash Out is also aggregated at account level           "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "m[\"Market Value\"] = tt.agg.sum(\n",
    "    m[\"Price.VALUE\"] * m[\"Quantity.SUM\"],\n",
    "    scope=tt.scope.origin(lvl[\"Account\"], lvl[\"Asset_Code\"]),\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "m[\"Collateral Value\"] = tt.agg.sum(\n",
    "    m[\"Price.VALUE\"] * m[\"Quantity.SUM\"] * (1 - m[\"Haircut.VALUE\"]),\n",
    "    scope=tt.scope.origin(lvl[\"Account\"], lvl[\"Asset_Code\"]),\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "m[\"Cash Out\"] = tt.agg.sum(m[\"Cash_Out.VALUE\"], scope=tt.scope.origin(lvl[\"Account\"]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "m[\"Collateral Shortfall\"] = m[\"Collateral Value\"] - m[\"Cash Out\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/json": {
       "Cash Out": {
        "formatter": "DOUBLE[#,###.00]",
        "visible": true
       },
       "Cash_Out.VALUE": {
        "formatter": "DOUBLE[#,###.00]",
        "visible": true
       },
       "Collateral Shortfall": {
        "formatter": "DOUBLE[#,###.00]",
        "visible": true
       },
       "Collateral Value": {
        "formatter": "DOUBLE[#,###.00]",
        "visible": true
       },
       "Haircut.VALUE": {
        "formatter": "DOUBLE[#,###.00]",
        "visible": true
       },
       "Market Value": {
        "formatter": "DOUBLE[#,###.00]",
        "visible": true
       },
       "Price.VALUE": {
        "formatter": "DOUBLE[#,###.00]",
        "visible": true
       },
       "Quantity.MEAN": {
        "formatter": "DOUBLE[#,###.00]",
        "visible": true
       },
       "Quantity.SUM": {
        "formatter": "DOUBLE[#,###.00]",
        "visible": true
       },
       "contributors.COUNT": {
        "formatter": null,
        "visible": true
       }
      },
      "text/html": [
       "<ul>\n",
       "<li>Measures\n",
       "  <ul>\n",
       "  <li>Cash Out\n",
       "    <ul>\n",
       "    <li>formatter: DOUBLE[#,###.00]</li>\n",
       "    <li>visible: True</li>\n",
       "    </ul>\n",
       "  </li>\n",
       "  <li>Cash_Out.VALUE\n",
       "    <ul>\n",
       "    <li>formatter: DOUBLE[#,###.00]</li>\n",
       "    <li>visible: True</li>\n",
       "    </ul>\n",
       "  </li>\n",
       "  <li>Collateral Shortfall\n",
       "    <ul>\n",
       "    <li>formatter: DOUBLE[#,###.00]</li>\n",
       "    <li>visible: True</li>\n",
       "    </ul>\n",
       "  </li>\n",
       "  <li>Collateral Value\n",
       "    <ul>\n",
       "    <li>formatter: DOUBLE[#,###.00]</li>\n",
       "    <li>visible: True</li>\n",
       "    </ul>\n",
       "  </li>\n",
       "  <li>Haircut.VALUE\n",
       "    <ul>\n",
       "    <li>formatter: DOUBLE[#,###.00]</li>\n",
       "    <li>visible: True</li>\n",
       "    </ul>\n",
       "  </li>\n",
       "  <li>Market Value\n",
       "    <ul>\n",
       "    <li>formatter: DOUBLE[#,###.00]</li>\n",
       "    <li>visible: True</li>\n",
       "    </ul>\n",
       "  </li>\n",
       "  <li>Price.VALUE\n",
       "    <ul>\n",
       "    <li>formatter: DOUBLE[#,###.00]</li>\n",
       "    <li>visible: True</li>\n",
       "    </ul>\n",
       "  </li>\n",
       "  <li>Quantity.MEAN\n",
       "    <ul>\n",
       "    <li>formatter: DOUBLE[#,###.00]</li>\n",
       "    <li>visible: True</li>\n",
       "    </ul>\n",
       "  </li>\n",
       "  <li>Quantity.SUM\n",
       "    <ul>\n",
       "    <li>formatter: DOUBLE[#,###.00]</li>\n",
       "    <li>visible: True</li>\n",
       "    </ul>\n",
       "  </li>\n",
       "  <li>contributors.COUNT\n",
       "    <ul>\n",
       "    <li>formatter: None</li>\n",
       "    <li>visible: True</li>\n",
       "    </ul>\n",
       "  </li>\n",
       "  </ul>\n",
       "</li>\n",
       "</ul>\n"
      ],
      "text/plain": [
       "Measures()"
      ]
     },
     "execution_count": 30,
     "metadata": {
      "application/json": {
       "expanded": false,
       "root": "Measures"
      }
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "m"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Monitoring Collateral Shortfall  \n",
    "We created a pivot table for with the Collateral Shortfall, Market Value, Cash Out and Collateral Value for Accounts.  \n",
    "Negative Collateral Shortfall are highlighted in red. Feel free to click on the `>` to drill-down to other hierarchies such as Sector to account for the shortfall. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "atoti": {
     "height": 255,
     "state": {
      "name": "Collateral Shortfall",
      "type": "container",
      "value": {
       "body": {
        "configuration": {
         "tabular": {
          "addButtonFilter": "numeric",
          "columnsGroups": [
           {
            "captionProducer": "firstColumn",
            "cellFactory": "kpi-status",
            "selector": "kpi-status"
           },
           {
            "captionProducer": "firstColumn",
            "cellFactory": "lookup",
            "selector": "lookup"
           },
           {
            "captionProducer": "expiry",
            "cellFactory": "expiry",
            "selector": "kpi-expiry"
           },
           {
            "captionProducer": "columnMerge",
            "cellFactory": {
             "args": {
              "automaticExpansion": true
             },
             "key": "treeCells"
            },
            "selector": "member"
           }
          ],
          "defaultOptions": {},
          "hideAddButton": true,
          "lineNumbers": true,
          "pinnedHeaderSelector": "member",
          "sortingMode": "non-breaking",
          "statisticsShown": true
         }
        },
        "contextValues": {},
        "mdx": "WITH  Member [Measures].[Collateral Shortfall (1)] AS [Measures].[Collateral Shortfall], CAPTION = [Measures].[Collateral Shortfall].MEMBER_CAPTION, BACK_COLOR = CASE WHEN [Measures].[Collateral Shortfall (1)] >= 0 THEN \"transparent\" WHEN [Measures].[Collateral Shortfall (1)] <= 0 THEN \"transparent\" END, FORE_COLOR = CASE WHEN [Measures].[Collateral Shortfall (1)] >= 0 THEN rgb(44, 160, 44) WHEN [Measures].[Collateral Shortfall (1)] <= 0 THEN rgb(255, 0, 0) END, FORMAT_STRING = \"#,###.00\"  SELECT NON EMPTY {[Measures].[Collateral Shortfall (1)], [Measures].[Market Value], [Measures].[Cash Out], [Measures].[Collateral Value]} ON COLUMNS, NON EMPTY Hierarchize(DrilldownLevel([Hierarchies].[Account].[ALL].[AllMember])) ON ROWS FROM [Collateral_Management] CELL PROPERTIES BACK_COLOR, FONT_FLAGS, FORE_COLOR, FORMATTED_VALUE, VALUE",
        "ranges": {
         "column": {
          "chunkSize": 50,
          "thresholdPercentage": 0.2
         },
         "row": {
          "chunkSize": 2000,
          "thresholdPercentage": 0.1
         }
        },
        "serverUrl": "",
        "updateMode": "once"
       },
       "containerKey": "pivot-table",
       "showTitleBar": false,
       "style": {}
      }
     }
    }
   },
   "outputs": [
    {
     "data": {
      "application/vnd.atoti.v0+json": {
       "cube": "Collateral_Management",
       "name": "Collateral Shortfall",
       "session": "Unnamed"
      },
      "text/plain": [
       "Install and enable the atoti JupyterLab extension to see this widget."
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "asset_positions_cube.visualize(\"Collateral Shortfall\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In view of all accounts, using a Gauge chart will show us that we are not yet in shortfall and how far we are from it.   \n",
    "The red marker shows the total Market value, which is the maximum threshold before shortfall will occur."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "atoti": {
     "state": {
      "name": "total-cash-out",
      "type": "container",
      "value": {
       "body": {
        "configuration": {
         "mapping": {
          "goal": [
           "[Measures].[Collateral Value]"
          ],
          "maximum": [
           "[Measures].[Market Value]"
          ],
          "minimum": [],
          "value": [
           "[Measures].[Cash Out]"
          ]
         },
         "type": "plotly-gauge-chart"
        },
        "query": {
         "mdx": "SELECT NON EMPTY {[Measures].[Cash Out], [Measures].[Collateral Value], [Measures].[Market Value]} ON COLUMNS FROM [Collateral_Management] CELL PROPERTIES VALUE, FORMATTED_VALUE, BACK_COLOR, FORE_COLOR, FONT_FLAGS",
         "serverUrl": "",
         "updateMode": "once"
        }
       },
       "containerKey": "chart",
       "showTitleBar": false,
       "style": {}
      }
     }
    }
   },
   "outputs": [
    {
     "data": {
      "application/vnd.atoti.v0+json": {
       "cube": "Collateral_Management",
       "name": "total-cash-out",
       "session": "Unnamed"
      },
      "text/plain": [
       "Install and enable the atoti JupyterLab extension to see this widget."
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "asset_positions_cube.visualize(\"total-cash-out\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Total Cash out Bank wide  \n",
    "  \n",
    "We can use a Tree map to visualize the asset concentration. A well diversified portfolio will help to reduce the collateral risks."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {
    "atoti": {
     "state": {
      "name": "Asset_Concentration",
      "type": "container",
      "value": {
       "body": {
        "configuration": {
         "mapping": {
          "horizontal": [],
          "splitBy": [],
          "values": [
           "[Measures].[Market Value]"
          ],
          "vertical": [],
          "xAxis": [
           "[Hierarchies].[Sector].[Sector]",
           "[Hierarchies].[Country].[Country]"
          ]
         },
         "switchedTo": "plotly-tree-map",
         "type": "plotly-line-chart"
        },
        "query": {
         "mdx": "SELECT NON EMPTY Crossjoin([Hierarchies].[Sector].[Sector].Members, [Hierarchies].[Country].[Country].Members) ON ROWS, NON EMPTY [Measures].[Market Value] ON COLUMNS FROM [Collateral_Management] CELL PROPERTIES VALUE, FORMATTED_VALUE, BACK_COLOR, FORE_COLOR, FONT_FLAGS",
         "serverUrl": "",
         "updateMode": "once"
        }
       },
       "containerKey": "chart",
       "showTitleBar": false,
       "style": {}
      }
     }
    }
   },
   "outputs": [
    {
     "data": {
      "application/vnd.atoti.v0+json": {
       "cube": "Collateral_Management",
       "name": "Asset_Concentration",
       "session": "Unnamed"
      },
      "text/plain": [
       "Install and enable the atoti JupyterLab extension to see this widget."
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "asset_positions_cube.visualize(\"Asset_Concentration\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## atoti UI and Dashboard creation\n",
    "Until now, we have created a few visualizations. We can right-click on the visuals to publish them as widgets.\n",
    "These widgets can then be used to build a dashboard.  \n",
    "\n",
    "<img src=\"http://data.atoti.io/notebooks/collateral-shortfall-monitoring/collateral_dashboard.gif\" alt=\"collateral_dashboard\" style=\"zoom:40%;\" />"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'http://localhost:44869/#/dashboard/f6c'"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "session.url + \"/#/dashboard/f6c\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Click on the URL above to view the dashboard that was prepared. We can use the quick filter to select an account for viewing.  \n",
    "We can also do a right-click drillthrough to investigate the underlying data.  \n",
    "  \n",
    "To play with the UI and explore the data, [you can have a look at our UI documentation here](https://www.activeviam.com/activeui/documentation/index.html)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Simulations\n",
    "Now that we have basic monitoring on Collateral Shortfall, we can do some simulations in the data cube without having to duplicate or reload data.  \n",
    "  \n",
    "### Setup Haircut Simulation\n",
    "We are going to perform a Haircut simulation where there is a consumption slowdown in the Consumer Cyclical sector in Italy and France.  \n",
    "In this use case, we will replace the value of haircut for the mentioned sector and country to see its downstream impact on the Collaterals."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/json": {
       "Levels": [
        "Sector",
        "Country"
       ],
       "Scenarios": [
        "Current Market Condition"
       ],
       "replace": [
        "Haircut.VALUE"
       ]
      },
      "text/html": [
       "<ul>\n",
       "<li>Haircut Simulation\n",
       "  <ul>\n",
       "  <li>Levels\n",
       "    <ol>\n",
       "    <li>Sector</li>\n",
       "    <li>Country</li>\n",
       "    </ol>  </li>\n",
       "  <li>Scenarios\n",
       "    <ol>\n",
       "    <li>Current Market Condition</li>\n",
       "    </ol>  </li>\n",
       "  <li>replace\n",
       "    <ol>\n",
       "    <li>Haircut.VALUE</li>\n",
       "    </ol>  </li>\n",
       "  </ul>\n",
       "</li>\n",
       "</ul>\n"
      ],
      "text/plain": [
       "Simulation(_name='Haircut Simulation', _levels=[Level(_name='Sector', _column_name='Sector', _data_type='string', _hierarchy=Hierarchy(_name='Sector', _levels={'Sector': ...}, _dimension='Hierarchies', _slicing=False), _comparator=None), Level(_name='Country', _column_name='Country', _data_type='string', _hierarchy=Hierarchy(_name='Country', _levels={'Country': ...}, _dimension='Hierarchies', _slicing=False), _comparator=None)], _multiply=[], _replace=[NamedMeasure(_name='Haircut.VALUE', _folder=None, _formatter='DOUBLE[#,###.00]', _visible=True)], _add=[], _base_scenario='Current Market Condition')"
      ]
     },
     "execution_count": 38,
     "metadata": {
      "application/json": {
       "expanded": true,
       "root": "Haircut Simulation"
      }
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# we have to do a setup_simulation, where scenarios added to this simulation will follow the same pattern of simulation\n",
    "# E.g. same levels and method on measure, in this case, replace Haircut\n",
    "# we set the base_scenario_name to \"Current Market Condition\" - this is the original set of data currently in the cube\n",
    "haircut_simulation = asset_positions_cube.setup_simulation(\n",
    "    \"Haircut Simulation\",\n",
    "    levels=[lvl[\"Sector\"], lvl[\"Country\"]],\n",
    "    replace=[m[\"Haircut.VALUE\"]],\n",
    "    base_scenario=\"Current Market Condition\",\n",
    ")\n",
    "haircut_simulation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "# create a new scenario with a meaningful name\n",
    "consumption_slowdown = haircut_simulation.scenarios[\"Consumption Slowdown\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "# parameters in the sequence of levels defined in the \"per\" during setup_simulation\n",
    "consumption_slowdown += (\"Consumer Cyclical\", \"Italy\", 0.20)\n",
    "consumption_slowdown += (\"Consumer Cyclical\", \"France\", 0.15)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Analysing the impact of Consumption Slowdown  \n",
    "  \n",
    "We will create a chart showing Collateral Shortfall against Account, split by *Haircut Simulation* - the Measure Simulation we just created.  \n",
    "We will be able to see that with a slowdown in Italy and France, *Gates & Melinda* will go into shortfall and the shortfall amount for *Buffet* increased dramatically.   \n",
    "The Consumer Cyclical  in Italy and France does not impact *Daniel EK*, *Musk* and *Niel*."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {
    "atoti": {
     "state": {
      "name": "Consumption Slowdown",
      "type": "container",
      "value": {
       "body": {
        "configuration": {
         "mapping": {
          "horizontal": [],
          "splitBy": [
           "[Measure Simulations].[Haircut Simulation].[Haircut Simulation]"
          ],
          "values": [
           "[Measures].[Collateral Shortfall]"
          ],
          "vertical": [],
          "xAxis": [
           "[Hierarchies].[Account].[Account]"
          ]
         },
         "type": "plotly-clustered-column-chart"
        },
        "query": {
         "mdx": "SELECT NON EMPTY Crossjoin([Hierarchies].[Account].[Account].Members, [Measure Simulations].[Haircut Simulation].[Haircut Simulation].Members) ON ROWS, NON EMPTY [Measures].[Collateral Shortfall] ON COLUMNS FROM [Collateral_Management] CELL PROPERTIES VALUE, FORMATTED_VALUE, BACK_COLOR, FORE_COLOR, FONT_FLAGS",
         "serverUrl": "",
         "updateMode": "once"
        }
       },
       "containerKey": "chart",
       "showTitleBar": false,
       "style": {}
      }
     }
    }
   },
   "outputs": [
    {
     "data": {
      "application/vnd.atoti.v0+json": {
       "cube": "Collateral_Management",
       "name": "Consumption Slowdown",
       "session": "Unnamed"
      },
      "text/plain": [
       "Install and enable the atoti JupyterLab extension to see this widget."
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "asset_positions_cube.visualize(\"Consumption Slowdown\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "With a pivot table, we can easily see the amount of collateral generated by the *Consumer Cyclical* sector in Italy and France."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {
    "atoti": {
     "state": {
      "name": "Consumption Slowdown by Sector",
      "type": "container",
      "value": {
       "body": {
        "configuration": {
         "tabular": {
          "addButtonFilter": "numeric",
          "columns": [
           {
            "key": "([Measures].[Collateral Shortfall],[Measure Simulations].[Haircut Simulation].[Haircut Simulation].[Current Market Condition - Consumption Slowdown])",
            "width": 330
           }
          ],
          "columnsGroups": [
           {
            "captionProducer": "firstColumn",
            "cellFactory": "kpi-status",
            "selector": "kpi-status"
           },
           {
            "captionProducer": "firstColumn",
            "cellFactory": "lookup",
            "selector": "lookup"
           },
           {
            "captionProducer": "expiry",
            "cellFactory": "expiry",
            "selector": "kpi-expiry"
           },
           {
            "captionProducer": "columnMerge",
            "cellFactory": {
             "args": {
              "automaticExpansion": true
             },
             "key": "treeCells"
            },
            "selector": "member"
           }
          ],
          "defaultOptions": {},
          "hideAddButton": true,
          "lineNumbers": true,
          "pinnedHeaderSelector": "member",
          "sortingMode": "non-breaking",
          "statisticsShown": true
         }
        },
        "contextValues": {},
        "mdx": "WITH  Member [Measure Simulations].[Haircut Simulation].[Current Market Condition - Consumption Slowdown] AS [Measure Simulations].[Haircut Simulation].[Current Market Condition] - [Measure Simulations].[Haircut Simulation].[Consumption Slowdown], CAPTION = \"Current Market Condition - Consumption Slowdown\"  SELECT NON EMPTY Crossjoin([Measures].[Collateral Shortfall], Union([Measure Simulations].[Haircut Simulation].[Haircut Simulation].Members, [Measure Simulations].[Haircut Simulation].[Current Market Condition - Consumption Slowdown])) ON COLUMNS, NON EMPTY Crossjoin([Hierarchies].[Country].[Country].Members, [Hierarchies].[Sector].[Sector].Members) ON ROWS FROM [Collateral_Management] CELL PROPERTIES BACK_COLOR, FONT_FLAGS, FORE_COLOR, FORMATTED_VALUE, VALUE",
        "ranges": {
         "column": {
          "chunkSize": 50,
          "thresholdPercentage": 0.2
         },
         "row": {
          "chunkSize": 2000,
          "thresholdPercentage": 0.1
         }
        },
        "serverUrl": "",
        "updateMode": "once"
       },
       "containerKey": "pivot-table",
       "showTitleBar": false,
       "style": {}
      }
     }
    }
   },
   "outputs": [
    {
     "data": {
      "application/vnd.atoti.v0+json": {
       "cube": "Collateral_Management",
       "name": "Consumption Slowdown by Sector",
       "session": "Unnamed"
      },
      "text/plain": [
       "Install and enable the atoti JupyterLab extension to see this widget."
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "asset_positions_cube.visualize(\"Consumption Slowdown by Sector\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Market Value Simulation\n",
    "We are going to create a new set of simulation where technology sector goes into a slump and price value for the sector dipped by 30%.  \n",
    "We see that while the levels we will be simulating are the same as the previous simulation, now instead of replacing the Haircut value, we will multiply the Price value by 0.7.  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [],
   "source": [
    "market_value_simulation = asset_positions_cube.setup_simulation(\n",
    "    \"Market Value Simulation\",\n",
    "    levels=[lvl[\"Sector\"], lvl[\"Country\"]],\n",
    "    multiply=[m[\"Price.VALUE\"]],\n",
    "    base_scenario=\"Current Market Condition\",\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "tech_bubble = market_value_simulation.scenarios[\"Tech Bubble ?\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [],
   "source": [
    "# we are going to simulate a slump technology in all countries, therefore we pass the value `*` for country level\n",
    "tech_bubble += (\"Technology\", None, 0.70)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Analysing the impact of Technology slump  \n",
    "  \n",
    "As before, we plot a chart of Collateral Shortfall against Account. However, we are going to do a split by the *Market Simulation* that we just created.  \n",
    "We will see the technology giant such as *Gates & Melinda* suffering greatly as their shortfall goes beyond 1.5 million.  \n",
    "Likewise Buffet with 1/6 of assets in Technology also suffered."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {
    "atoti": {
     "state": {
      "name": "Tech Bubble ?",
      "type": "container",
      "value": {
       "body": {
        "configuration": {
         "mapping": {
          "horizontal": [],
          "splitBy": [
           "[Measure Simulations].[Market Value Simulation].[Market Value Simulation]"
          ],
          "values": [
           "[Measures].[Collateral Shortfall]"
          ],
          "vertical": [],
          "yAxis": [
           "[Hierarchies].[Account].[Account]"
          ]
         },
         "type": "plotly-clustered-bar-chart"
        },
        "query": {
         "mdx": "SELECT NON EMPTY [Measures].[Collateral Shortfall] ON COLUMNS, NON EMPTY Crossjoin([Hierarchies].[Account].[Account].Members, [Measure Simulations].[Market Value Simulation].[Market Value Simulation].Members) ON ROWS FROM [Collateral_Management] CELL PROPERTIES VALUE, FORMATTED_VALUE, BACK_COLOR, FORE_COLOR, FONT_FLAGS",
         "serverUrl": "",
         "updateMode": "once"
        }
       },
       "containerKey": "chart",
       "showTitleBar": false,
       "style": {}
      }
     }
    }
   },
   "outputs": [
    {
     "data": {
      "application/vnd.atoti.v0+json": {
       "cube": "Collateral_Management",
       "name": "Tech Bubble ?",
       "session": "Unnamed"
      },
      "text/plain": [
       "Install and enable the atoti JupyterLab extension to see this widget."
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "asset_positions_cube.visualize(\"Tech Bubble ?\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As we drill-in further to simulation for *Buffet* vs *Gates*, we see that *Buffet* has greater shortfall as they have a higher Cash Out value for the Technology sector than *Gates* while their market value was the same. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {
    "atoti": {
     "height": 337,
     "state": {
      "name": "Market Simulation - Gates vs Buffet",
      "type": "container",
      "value": {
       "body": {
        "configuration": {
         "tabular": {
          "addButtonFilter": "numeric",
          "columnsGroups": [
           {
            "captionProducer": "firstColumn",
            "cellFactory": "kpi-status",
            "selector": "kpi-status"
           },
           {
            "captionProducer": "firstColumn",
            "cellFactory": "lookup",
            "selector": "lookup"
           },
           {
            "captionProducer": "expiry",
            "cellFactory": "expiry",
            "selector": "kpi-expiry"
           },
           {
            "captionProducer": "columnMerge",
            "cellFactory": {
             "args": {
              "automaticExpansion": true
             },
             "key": "treeCells"
            },
            "selector": "member"
           }
          ],
          "defaultOptions": {},
          "hideAddButton": true,
          "lineNumbers": true,
          "pinnedHeaderSelector": "member",
          "sortingMode": "non-breaking",
          "statisticsShown": true
         }
        },
        "contextValues": {},
        "mdx": "WITH  Member [Measures].[Collateral Shortfall (1)] AS [Measures].[Collateral Shortfall], CAPTION = [Measures].[Collateral Shortfall].MEMBER_CAPTION, BACK_COLOR = CASE WHEN [Measures].[Collateral Shortfall (1)] >= 0 THEN \"transparent\" WHEN [Measures].[Collateral Shortfall (1)] <= 0 THEN \"transparent\" END, FORE_COLOR = CASE WHEN [Measures].[Collateral Shortfall (1)] >= 0 THEN rgb(44, 160, 44) WHEN [Measures].[Collateral Shortfall (1)] <= 0 THEN rgb(255, 0, 0) END, FORMAT_STRING = \"#,###.00\"  SELECT NON EMPTY {[Measures].[Collateral Shortfall (1)], [Measures].[Market Value], [Measures].[Cash Out], [Measures].[Collateral Value]} ON COLUMNS, NON EMPTY Hierarchize(Union(Crossjoin(DrilldownLevel([Hierarchies].[Account].[ALL].[AllMember]), [Hierarchies].[Sector].DefaultMember), Crossjoin([Hierarchies].[Account].[ALL].[AllMember].[Buffet], [Hierarchies].[Sector].[Sector].Members), Crossjoin([Hierarchies].[Account].[ALL].[AllMember].[Gates & Melinda], Descendants({[Hierarchies].[Sector].[ALL].[AllMember]}, [Hierarchies].[Sector].[Sector])))) ON ROWS FROM (SELECT {[Hierarchies].[Account].[ALL].[AllMember].[Gates & Melinda], [Hierarchies].[Account].[ALL].[AllMember].[Buffet]} ON COLUMNS FROM [Collateral_Management]) WHERE [Measure Simulations].[Market Value Simulation].[Market Value Simulation].[Tech Bubble ?] CELL PROPERTIES BACK_COLOR, FONT_FLAGS, FORE_COLOR, FORMATTED_VALUE, VALUE",
        "ranges": {
         "column": {
          "chunkSize": 50,
          "thresholdPercentage": 0.2
         },
         "row": {
          "chunkSize": 2000,
          "thresholdPercentage": 0.1
         }
        },
        "serverUrl": "",
        "updateMode": "once"
       },
       "containerKey": "pivot-table",
       "showTitleBar": false,
       "style": {}
      }
     }
    }
   },
   "outputs": [
    {
     "data": {
      "application/vnd.atoti.v0+json": {
       "cube": "Collateral_Management",
       "name": "Market Simulation - Gates vs Buffet",
       "session": "Unnamed"
      },
      "text/plain": [
       "Install and enable the atoti JupyterLab extension to see this widget."
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "asset_positions_cube.visualize(\"Market Simulation - Gates vs Buffet\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Setup Cash out Simulation\n",
    "\n",
    "The previous simulations we have done are called Measure simulations.  \n",
    "There is a different kind of simulation known as Source simulation.  \n",
    "\n",
    "In Measure simulations, we modify the value of the measures in scenarios of the simulations without duplicating data.  \n",
    "Source simulation on the other hand, is a simulation created by loading a new source of modified data to the cube.  \n",
    "\n",
    "Let's modify the loans_positions_df that was previously loaded into the cube, and increase its Cash_Out value by 10%.  \n",
    "For our *Cash out simulation*, we shall load this modified dataframe directly into the store as a scenario."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [],
   "source": [
    "loans_positions_df[\"Cash_Out\"] = loans_positions_df[\"Cash_Out\"].apply(\n",
    "    lambda x: (1 + 0.1) * x\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "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>Account</th>\n",
       "      <th>Cash_Out</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Bezos &amp; MacKenzie</td>\n",
       "      <td>17600000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Buffet</td>\n",
       "      <td>59400000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Daniel Ek</td>\n",
       "      <td>9900000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Gates &amp; Melinda</td>\n",
       "      <td>18700000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Musk</td>\n",
       "      <td>9900000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Niel</td>\n",
       "      <td>15400000.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             Account    Cash_Out\n",
       "0  Bezos & MacKenzie  17600000.0\n",
       "1             Buffet  59400000.0\n",
       "2          Daniel Ek   9900000.0\n",
       "3    Gates & Melinda  18700000.0\n",
       "4               Musk   9900000.0\n",
       "5               Niel  15400000.0"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "loans_positions_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [],
   "source": [
    "# In Source Simulation, we do not perform simulation_setup. We just load it to the store as scenarios.\n",
    "predictive_simulation = loans_positions_store.scenarios[\n",
    "    \"Cash out Increase\"\n",
    "].load_pandas(loans_positions_df, truncate=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Analysing the impact of increase in Cash Out\n",
    "\n",
    "While cash out increases but collateral value remains the same, we see that the shortfall situation is worsen."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {
    "atoti": {
     "state": {
      "name": "Cash out Increase",
      "type": "container",
      "value": {
       "body": {
        "configuration": {
         "mapping": {
          "horizontal": [],
          "splitBy": [
           "[Epoch].[Epoch].[Branch]"
          ],
          "values": [
           "[Measures].[Collateral Shortfall]"
          ],
          "vertical": [],
          "xAxis": [
           "[Hierarchies].[Account].[Account]"
          ]
         },
         "type": "plotly-clustered-column-chart"
        },
        "query": {
         "mdx": "SELECT NON EMPTY Crossjoin([Hierarchies].[Account].[Account].Members, [Epoch].[Epoch].[Branch].Members) ON ROWS, NON EMPTY [Measures].[Collateral Shortfall] ON COLUMNS FROM [Collateral_Management] CELL PROPERTIES VALUE, FORMATTED_VALUE, BACK_COLOR, FORE_COLOR, FONT_FLAGS",
         "serverUrl": "",
         "updateMode": "once"
        }
       },
       "containerKey": "chart",
       "showTitleBar": false,
       "style": {}
      }
     }
    }
   },
   "outputs": [
    {
     "data": {
      "application/vnd.atoti.v0+json": {
       "cube": "Collateral_Management",
       "name": "Cash out Increase",
       "session": "Unnamed"
      },
      "text/plain": [
       "Install and enable the atoti JupyterLab extension to see this widget."
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "asset_positions_cube.visualize(\"Cash out Increase\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Activate Source Simulation\n",
    "\n",
    "In atoti UI, we have 2 kinds of widgets for simulations:  \n",
    "\n",
    "- Measure Simulations\n",
    "  - enable user to update, create new or duplicate simulations\n",
    "  - available upon setup_simulation\n",
    "- Source Simulations\n",
    "  - enable users to upload their modified source files via UI as simulation\n",
    "  - have to explicitly enable to be accessible from UI\n",
    "  \n",
    "Next we will enable source simulation."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [],
   "source": [
    "loans_positions_store.source_simulation_enabled = True"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Once we have enabled the source simulation, we can proceed to integrate the Source Simulations widget in our dashboard.  \n",
    "<img src=\"http://data.atoti.io/notebooks/collateral-shortfall-monitoring/source_simulation.gif\" alt=\"source_simulation\" style=\"zoom:40%;\" />  \n",
    "  \n",
    "To play with the source simulation, click on the link on the next output cell."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'http://localhost:44869/#/dashboard/78f'"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "session.url + \"/#/dashboard/78f\""
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}