US-China Trade War.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Bloomberg BQuant Webinar Series: <br> US-China Trade War\n",
    "This is a companion notebook to the `US-China Trade War: Analyzing the winners and losers using BQuant` webinar."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import bql\n",
    "import pandas as pd  #Dataframe analytics\n",
    "from collections import OrderedDict  #Ordered dictionary\n",
    "from IPython.display import display  #display function\n",
    "\n",
    "import ipywidgets as ipw  #open source widgets\n",
    "import bqviz as bqv   #easy to use chart library\n",
    "import bqplot as bqp  #exhaustive chart library\n",
    "import bqwidgets as bqw   #bloomberg widgets"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "bq = bql.Service()  #opening BQL service"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Downloading latest GDP level for the top 20 countries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "univ = bq.univ.members('World', type='Region')   #new universe function for regions\n",
    "gdp = bq.data.gdp(transform = 'LVL',PERIOD_TYPE='A')  #specify field GDP annual level\n",
    "G20_univ = bq.univ.filter(univ, gdp.grouprank() <= 20)  #filter top 20 countries by GDP level\n",
    "\n",
    "request = bql.Request(G20_univ, {'gdp':gdp.groupsort()})\n",
    "response = bq.execute(request)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "gdp_df = response.get('gdp').to_dataframe()  #transform single 'gdp' response into a dataframe\n",
    "chart_obj = bqv.BarPlot(gdp_df[['gdp']])    #feed dataframe into a chart via bqviz"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "d495bdf0f7a04ef7a909c2e59a989feb",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "GridBox(children=(Figure(animation_duration=500, axes=[Axis(color='white', grid_color='#3c3c3c', grid_lines='d…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "chart_obj.set_style().show()   #display the bqviz chart"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Use an ordered dictionary to retrieve various values for G20 countries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "fields = OrderedDict()  #Same as dictionary, however it preserves the order of the keys\n",
    "fields['GDP'] = bq.data.gdp(transform = 'PCT')\n",
    "fields['CPI Chg'] = bq.data.cpi(transform ='PCT', period_reference = bq.func.range('2018M1', '2019M9'), fill='prev').net_chg()\n",
    "fields['Unemployment Chg']  = bq.data.unemployment(period_reference = bq.func.range('2018M1', '2019M9'), fill='prev').net_chg()\n",
    "fields['REER Chg'] = bq.data.reer(period_reference = bq.func.range('2018M1', '2019M8')).pct_chg()\n",
    "fields['Name'] = bq.data.id()['COUNTRY_NAME']\n",
    "\n",
    "req = bql.Request(G20_univ, fields)\n",
    "\n",
    "resp_names = bq.execute(req)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>GDP</th>\n",
       "      <th>CPI Chg</th>\n",
       "      <th>Unemployment Chg</th>\n",
       "      <th>REER Chg</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Name</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Australia</th>\n",
       "      <td>1.744580</td>\n",
       "      <td>-0.20000</td>\n",
       "      <td>-0.323949</td>\n",
       "      <td>-9.579288</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Brazil</th>\n",
       "      <td>1.190000</td>\n",
       "      <td>0.03000</td>\n",
       "      <td>-0.400000</td>\n",
       "      <td>-9.334931</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Canada</th>\n",
       "      <td>1.651590</td>\n",
       "      <td>0.17102</td>\n",
       "      <td>-0.400000</td>\n",
       "      <td>-2.691218</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>China</th>\n",
       "      <td>6.000000</td>\n",
       "      <td>1.55508</td>\n",
       "      <td>-0.290000</td>\n",
       "      <td>-2.798734</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>France</th>\n",
       "      <td>1.354810</td>\n",
       "      <td>-0.40000</td>\n",
       "      <td>-0.600000</td>\n",
       "      <td>-0.788063</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Germany</th>\n",
       "      <td>0.500000</td>\n",
       "      <td>-0.20000</td>\n",
       "      <td>-0.400000</td>\n",
       "      <td>0.134478</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>India</th>\n",
       "      <td>4.500000</td>\n",
       "      <td>-1.07093</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>-1.528278</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Indonesia</th>\n",
       "      <td>5.017900</td>\n",
       "      <td>0.14000</td>\n",
       "      <td>-0.220000</td>\n",
       "      <td>1.851852</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Italy</th>\n",
       "      <td>0.317590</td>\n",
       "      <td>-0.60000</td>\n",
       "      <td>-1.146273</td>\n",
       "      <td>-1.840239</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Japan</th>\n",
       "      <td>1.300000</td>\n",
       "      <td>-1.20000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>9.248634</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mexico</th>\n",
       "      <td>-0.300000</td>\n",
       "      <td>-2.54832</td>\n",
       "      <td>0.214858</td>\n",
       "      <td>1.627620</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Netherlands</th>\n",
       "      <td>1.917720</td>\n",
       "      <td>1.10000</td>\n",
       "      <td>-0.700000</td>\n",
       "      <td>1.601297</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Russia</th>\n",
       "      <td>1.700000</td>\n",
       "      <td>1.80000</td>\n",
       "      <td>-0.700000</td>\n",
       "      <td>-3.289702</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SWITZERLAND</th>\n",
       "      <td>1.001050</td>\n",
       "      <td>-0.60187</td>\n",
       "      <td>-0.700000</td>\n",
       "      <td>4.391628</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Saudi Arabia</th>\n",
       "      <td>2.214969</td>\n",
       "      <td>-3.70215</td>\n",
       "      <td>-0.381790</td>\n",
       "      <td>2.379036</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>South Korea</th>\n",
       "      <td>2.047210</td>\n",
       "      <td>-1.20000</td>\n",
       "      <td>-0.200000</td>\n",
       "      <td>-7.847435</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Spain</th>\n",
       "      <td>1.955400</td>\n",
       "      <td>-0.50000</td>\n",
       "      <td>-2.200000</td>\n",
       "      <td>-1.294166</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Turkey</th>\n",
       "      <td>0.946006</td>\n",
       "      <td>-1.08354</td>\n",
       "      <td>4.400000</td>\n",
       "      <td>-9.903106</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>United Kingdom</th>\n",
       "      <td>1.000000</td>\n",
       "      <td>-1.30000</td>\n",
       "      <td>-0.500000</td>\n",
       "      <td>-4.133720</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>United States of America</th>\n",
       "      <td>2.100000</td>\n",
       "      <td>-0.35920</td>\n",
       "      <td>-0.600000</td>\n",
       "      <td>7.878953</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                               GDP  CPI Chg  Unemployment Chg  REER Chg\n",
       "Name                                                                   \n",
       "Australia                 1.744580 -0.20000         -0.323949 -9.579288\n",
       "Brazil                    1.190000  0.03000         -0.400000 -9.334931\n",
       "Canada                    1.651590  0.17102         -0.400000 -2.691218\n",
       "China                     6.000000  1.55508         -0.290000 -2.798734\n",
       "France                    1.354810 -0.40000         -0.600000 -0.788063\n",
       "Germany                   0.500000 -0.20000         -0.400000  0.134478\n",
       "India                     4.500000 -1.07093          0.000000 -1.528278\n",
       "Indonesia                 5.017900  0.14000         -0.220000  1.851852\n",
       "Italy                     0.317590 -0.60000         -1.146273 -1.840239\n",
       "Japan                     1.300000 -1.20000          0.000000  9.248634\n",
       "Mexico                   -0.300000 -2.54832          0.214858  1.627620\n",
       "Netherlands               1.917720  1.10000         -0.700000  1.601297\n",
       "Russia                    1.700000  1.80000         -0.700000 -3.289702\n",
       "SWITZERLAND               1.001050 -0.60187         -0.700000  4.391628\n",
       "Saudi Arabia              2.214969 -3.70215         -0.381790  2.379036\n",
       "South Korea               2.047210 -1.20000         -0.200000 -7.847435\n",
       "Spain                     1.955400 -0.50000         -2.200000 -1.294166\n",
       "Turkey                    0.946006 -1.08354          4.400000 -9.903106\n",
       "United Kingdom            1.000000 -1.30000         -0.500000 -4.133720\n",
       "United States of America  2.100000 -0.35920         -0.600000  7.878953"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "eco_data = pd.concat([x.df()[x.name] for x in resp_names], axis=1)\n",
    "eco_data.set_index('Name', inplace=True)\n",
    "eco_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "chart_obj = bqv.GridPlot(eco_data.sort_values(by=['GDP']),colors=['#FFFFFF', '#1B84ED', '#CF7DFF', '#FF5A00'],plots = bqv.BarPlot,cols=2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "for chart in chart_obj.figure:\n",
    "    chart.axes[0].tick_rotate = -45\n",
    "    chart.axes[0].tick_style = {'text-anchor': 'end'}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "4d414c519d744621813c6799ece53617",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "HBox(children=(VBox(children=(GridBox(children=(Figure(animation_duration=500, axes=[Axis(color='white', grid_…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "chart_obj.set_style().show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# HEATMAP ECO + sector returns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Downloading Eco data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>AU Country</th>\n",
       "      <td>Australia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BR Country</th>\n",
       "      <td>Brazil</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CA Country</th>\n",
       "      <td>Canada</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CN Country</th>\n",
       "      <td>China</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FR Country</th>\n",
       "      <td>France</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 Name\n",
       "AU Country  Australia\n",
       "BR Country     Brazil\n",
       "CA Country     Canada\n",
       "CN Country      China\n",
       "FR Country     France"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Get name of the G20 countries and \n",
    "names = resp_names.get('Name').df()\n",
    "names.index = names.index+' Country'\n",
    "names.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "dict_eco_fields = {'NEER':bq.data.neer().dropna(),\n",
    "                   'Exports':bq.data.exports().dropna(),\n",
    "                   'GDP':(1+bq.data.gdp().dropna()/100/4).cumprod()-1,\n",
    "                   'PMI':bq.data.pmi().dropna(),\n",
    "                   'Trade_Balance':bq.data.trade_balance().dropna(),\n",
    "                   'CPI':bq.data.cpi()}\n",
    "\n",
    "date_range = bq.func.range('2016M1,2019M10')\n",
    "\n",
    "request = bql.Request(['US Country',  #US\n",
    "                       'FR Country',  #France\n",
    "                       'GB Country',  #UK\n",
    "                       'CA Country',  \n",
    "                       'DE Country',\n",
    "                       'IN Country',\n",
    "                       'AU Country',\n",
    "                       'CN Country',\n",
    "                       'JP Country'],\n",
    "                      dict_eco_fields,\n",
    "                      with_params={'PERIOD_REFERENCE':date_range})\n",
    "\n",
    "response = bq.execute(request)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'CPI': ID                     Australia   Canada    China  Germany  France  \\\n",
       " PERIOD_REFERENCE_DATE                                                 \n",
       " 2016-01-31                   NaN  2.01126  1.75182      0.5     0.2   \n",
       " 2016-02-29                   NaN  1.35566  2.28433      0.1    -0.2   \n",
       " 2016-03-31                   1.3  1.26682  2.30139      0.3    -0.1   \n",
       " 2016-04-30                   NaN  1.66403  2.32787     -0.1    -0.2   \n",
       " 2016-05-31                   NaN  1.49724  2.03900      0.2     0.0   \n",
       " 2016-06-30                   1.0  1.49372  1.87950      0.3     0.2   \n",
       " 2016-07-31                   NaN  1.25687  1.80000      0.5     0.2   \n",
       " 2016-08-31                   NaN  1.09976  1.33978      0.4     0.2   \n",
       " 2016-09-30                   1.3  1.33753  1.90000      0.6     0.4   \n",
       " 2016-10-31                   NaN  1.49372  2.09595      0.8     0.4   \n",
       " 2016-11-30                   NaN  1.18018  2.25226      0.8     0.5   \n",
       " 2016-12-31                   1.5  1.50197  2.07655      1.5     0.6   \n",
       " 2017-01-31                   NaN  2.12934  2.54906      1.6     1.3   \n",
       " 2017-02-28                   NaN  2.04563  0.80201      1.9     1.2   \n",
       " 2017-03-31                   2.1  1.56372  0.91714      1.4     1.1   \n",
       " 2017-04-30                   NaN  1.63678  1.16579      1.7     1.2   \n",
       " 2017-05-31                   NaN  1.31987  1.51707      1.2     0.8   \n",
       " 2017-06-30                   1.9  1.00696  1.49582      1.4     0.7   \n",
       " 2017-07-31                   NaN  1.16369  1.40674      1.4     0.7   \n",
       " 2017-08-31                   NaN  1.39860  1.76721      1.6     0.9   \n",
       " 2017-09-30                   1.8  1.55279  1.63200      1.7     1.0   \n",
       " 2017-10-31                   NaN  1.39426  1.86839      1.3     1.1   \n",
       " 2017-11-30                   NaN  2.09953  1.73456      1.6     1.2   \n",
       " 2017-12-31                   1.9  1.86917  1.84822      1.4     1.2   \n",
       " 2018-01-31                   NaN  1.69884  1.46796      1.4     1.3   \n",
       " 2018-02-28                   NaN  2.15883  2.90388      1.1     1.2   \n",
       " 2018-03-31                   1.9  2.30947  2.05666      1.5     1.6   \n",
       " 2018-04-30                   NaN  2.22393  1.80235      1.3     1.6   \n",
       " 2018-05-31                   NaN  2.22222  1.75050      2.1     2.0   \n",
       " 2018-06-30                   2.1  2.45400  1.85363      1.9     2.0   \n",
       " 2018-07-31                   NaN  2.99080  2.05942      1.9     2.3   \n",
       " 2018-08-31                   NaN  2.83525  2.30314      1.9     2.3   \n",
       " 2018-09-30                   1.9  2.21712  2.47240      1.9     2.2   \n",
       " 2018-10-31                   NaN  2.44462  2.54315      2.3     2.2   \n",
       " 2018-11-30                   NaN  1.67555  2.17504      2.1     1.9   \n",
       " 2018-12-31                   1.8  1.98776  1.86069      1.6     1.6   \n",
       " 2019-01-31                   NaN  1.44268  1.74041      1.4     1.2   \n",
       " 2019-02-28                   NaN  1.50943  1.48676      1.5     1.3   \n",
       " 2019-03-31                   1.3  1.88111  2.27641      1.3     1.1   \n",
       " 2019-04-30                   NaN  2.02550  2.53989      2.0     1.3   \n",
       " 2019-05-31                   NaN  2.39881  2.74282      1.4     0.9   \n",
       " 2019-06-30                   1.6  2.02096  2.68144      1.6     1.2   \n",
       " 2019-07-31                   NaN  2.01042  2.78264      1.7     1.1   \n",
       " 2019-08-31                   NaN  1.93741  2.84347      1.4     1.0   \n",
       " 2019-09-30                   1.7  1.86986  3.02304      1.2     0.9   \n",
       " 2019-10-31                   NaN  1.86428  3.75510      1.1     0.8   \n",
       " \n",
       " ID                     United Kingdom    India  Japan  \\\n",
       " PERIOD_REFERENCE_DATE                                   \n",
       " 2016-01-31                        0.3  5.69038   -0.1   \n",
       " 2016-02-29                        0.3  5.26316    0.2   \n",
       " 2016-03-31                        0.5  4.82529    0.0   \n",
       " 2016-04-30                        0.3  5.46811   -0.3   \n",
       " 2016-05-31                        0.3  5.75659   -0.5   \n",
       " 2016-06-30                        0.5  5.77236   -0.4   \n",
       " 2016-07-31                        0.6  6.06797   -0.4   \n",
       " 2016-08-31                        0.6  5.04808   -0.5   \n",
       " 2016-09-30                        1.0  4.38596   -0.5   \n",
       " 2016-10-31                        0.9  4.20301    0.1   \n",
       " 2016-11-30                        1.2  3.63349    0.5   \n",
       " 2016-12-31                        1.6  3.40999    0.3   \n",
       " 2017-01-31                        1.8  3.16706    0.4   \n",
       " 2017-02-28                        2.3  3.65080    0.3   \n",
       " 2017-03-31                        2.3  3.88888    0.2   \n",
       " 2017-04-30                        2.7  2.98508    0.4   \n",
       " 2017-05-31                        2.9  2.17728    0.4   \n",
       " 2017-06-30                        2.6  1.46041    0.4   \n",
       " 2017-07-31                        2.6  2.36460    0.4   \n",
       " 2017-08-31                        2.9  3.27993    0.7   \n",
       " 2017-09-30                        3.0  3.28495    0.7   \n",
       " 2017-10-31                        3.0  3.57687    0.2   \n",
       " 2017-11-30                        3.1  4.87806    0.6   \n",
       " 2017-12-31                        3.0  5.21473    1.0   \n",
       " 2018-01-31                        3.0  5.06523    1.4   \n",
       " 2018-02-28                        2.7  4.44103    1.5   \n",
       " 2018-03-31                        2.5  4.27808    1.1   \n",
       " 2018-04-30                        2.4  4.57666    0.6   \n",
       " 2018-05-31                        2.4  4.87063    0.7   \n",
       " 2018-06-30                        2.4  4.92424    0.7   \n",
       " 2018-07-31                        2.5  4.17288    0.9   \n",
       " 2018-08-31                        2.7  3.69276    1.3   \n",
       " 2018-09-30                        2.4  3.69823    1.2   \n",
       " 2018-10-31                        2.4  3.37986    1.4   \n",
       " 2018-11-30                        2.3  2.32558    0.8   \n",
       " 2018-12-31                        2.1  2.11371    0.3   \n",
       " 2019-01-31                        1.8  1.97225    0.2   \n",
       " 2019-02-28                        1.9  2.56598    0.2   \n",
       " 2019-03-31                        1.9  2.85714    0.5   \n",
       " 2019-04-30                        2.1  2.99051    0.9   \n",
       " 2019-05-31                        2.0  3.04789    0.7   \n",
       " 2019-06-30                        2.0  3.17689    0.7   \n",
       " 2019-07-31                        2.1  3.14735    0.5   \n",
       " 2019-08-31                        1.7  3.27636    0.3   \n",
       " 2019-09-30                        1.7  3.99430    0.2   \n",
       " 2019-10-31                        1.5  4.61976    0.2   \n",
       " \n",
       " ID                     United States of America  \n",
       " PERIOD_REFERENCE_DATE                            \n",
       " 2016-01-31                              1.37309  \n",
       " 2016-02-29                              1.01780  \n",
       " 2016-03-31                              0.85254  \n",
       " 2016-04-30                              1.12511  \n",
       " 2016-05-31                              1.01933  \n",
       " 2016-06-30                              0.99733  \n",
       " 2016-07-31                              0.82714  \n",
       " 2016-08-31                              1.06288  \n",
       " 2016-09-30                              1.46378  \n",
       " 2016-10-31                              1.63599  \n",
       " 2016-11-30                              1.69254  \n",
       " 2016-12-31                              2.07463  \n",
       " 2017-01-31                              2.50004  \n",
       " 2017-02-28                              2.73796  \n",
       " 2017-03-31                              2.38061  \n",
       " 2017-04-30                              2.19969  \n",
       " 2017-05-31                              1.87488  \n",
       " 2017-06-30                              1.63349  \n",
       " 2017-07-31                              1.72797  \n",
       " 2017-08-31                              1.93897  \n",
       " 2017-09-30                              2.23297  \n",
       " 2017-10-31                              2.04112  \n",
       " 2017-11-30                              2.20259  \n",
       " 2017-12-31                              2.10908  \n",
       " 2018-01-31                              2.07051  \n",
       " 2018-02-28                              2.21180  \n",
       " 2018-03-31                              2.35971  \n",
       " 2018-04-30                              2.46275  \n",
       " 2018-05-31                              2.80101  \n",
       " 2018-06-30                              2.87155  \n",
       " 2018-07-31                              2.94952  \n",
       " 2018-08-31                              2.69918  \n",
       " 2018-09-30                              2.27697  \n",
       " 2018-10-31                              2.52247  \n",
       " 2018-11-30                              2.17660  \n",
       " 2018-12-31                              1.91016  \n",
       " 2019-01-31                              1.55124  \n",
       " 2019-02-28                              1.52014  \n",
       " 2019-03-31                              1.86252  \n",
       " 2019-04-30                              1.99644  \n",
       " 2019-05-31                              1.79023  \n",
       " 2019-06-30                              1.64849  \n",
       " 2019-07-31                              1.81147  \n",
       " 2019-08-31                              1.74979  \n",
       " 2019-09-30                              1.71131  \n",
       " 2019-10-31                              1.76405  ,\n",
       " 'Exports': ID                        Australia        Canada         China       Germany  \\\n",
       " PERIOD_REFERENCE_DATE                                                           \n",
       " 2016-01-31             2.391000e+11  4.074000e+11  2.253000e+12  1.316400e+12   \n",
       " 2016-02-29             2.355000e+11  4.046000e+11  2.209900e+12  1.317500e+12   \n",
       " 2016-03-31             2.340000e+11  4.018000e+11  2.226000e+12  1.319600e+12   \n",
       " 2016-04-30             2.346000e+11  3.994000e+11  2.222500e+12  1.329200e+12   \n",
       " 2016-05-31             2.336000e+11  3.961000e+11  2.208900e+12  1.332100e+12   \n",
       " 2016-06-30             2.324000e+11  3.918000e+11  2.196900e+12  1.333200e+12   \n",
       " 2016-07-31             2.330000e+11  3.894000e+11  2.184900e+12  1.321800e+12   \n",
       " 2016-08-31             2.341000e+11  3.894000e+11  2.177900e+12  1.331700e+12   \n",
       " 2016-09-30             2.359000e+11  3.895000e+11  2.156800e+12  1.331600e+12   \n",
       " 2016-10-31             2.382000e+11  3.899000e+11  2.142500e+12  1.324200e+12   \n",
       " 2016-11-30             2.433000e+11  3.920000e+11  2.139400e+12  1.330200e+12   \n",
       " 2016-12-31             2.502000e+11  3.941000e+11  2.125600e+12  1.332700e+12   \n",
       " 2017-01-31             2.568000e+11  3.975000e+11  2.128900e+12  1.341300e+12   \n",
       " 2017-02-28             2.637000e+11  4.012000e+11  2.121700e+12  1.340900e+12   \n",
       " 2017-03-31             2.692000e+11  4.047000e+11  2.140300e+12  1.348600e+12   \n",
       " 2017-04-30             2.722000e+11  4.075000e+11  2.145300e+12  1.338600e+12   \n",
       " 2017-05-31             2.771000e+11  4.112000e+11  2.157900e+12  1.351300e+12   \n",
       " 2017-06-30             2.821000e+11  4.138000e+11  2.175100e+12  1.352500e+12   \n",
       " 2017-07-31             2.863000e+11  4.152000e+11  2.186000e+12  1.365300e+12   \n",
       " 2017-08-31             2.904000e+11  4.158000e+11  2.195000e+12  1.379400e+12   \n",
       " 2017-09-30             2.947000e+11  4.178000e+11  2.208800e+12  1.392300e+12   \n",
       " 2017-10-31             2.974000e+11  4.197000e+11  2.218800e+12  1.407900e+12   \n",
       " 2017-11-30             2.977000e+11  4.216000e+11  2.241100e+12  1.428300e+12   \n",
       " 2017-12-31             2.971000e+11  4.240000e+11  2.263300e+12  1.445000e+12   \n",
       " 2018-01-31             3.001000e+11  4.255000e+11  2.282500e+12  1.470600e+12   \n",
       " 2018-02-28             3.024000e+11  4.275000e+11  2.334200e+12  1.490900e+12   \n",
       " 2018-03-31             3.042000e+11  4.299000e+11  2.328900e+12  1.507900e+12   \n",
       " 2018-04-30             3.075000e+11  4.331000e+11  2.350100e+12  1.535400e+12   \n",
       " 2018-05-31             3.099000e+11  4.354000e+11  2.372600e+12  1.542200e+12   \n",
       " 2018-06-30             3.130000e+11  4.397000e+11  2.393400e+12  1.556500e+12   \n",
       " 2018-07-31             3.154000e+11  4.441000e+11  2.415700e+12  1.567300e+12   \n",
       " 2018-08-31             3.175000e+11  4.483000e+11  2.434600e+12  1.567300e+12   \n",
       " 2018-09-30             3.187000e+11  4.513000e+11  2.462100e+12  1.563300e+12   \n",
       " 2018-10-31             3.212000e+11  4.542000e+11  2.488900e+12  1.571000e+12   \n",
       " 2018-11-30             3.250000e+11  4.540000e+11  2.497400e+12  1.566500e+12   \n",
       " 2018-12-31             3.272000e+11  4.515000e+11  2.486700e+12  1.556600e+12   \n",
       " 2019-01-31             3.285000e+11  4.507000e+11  2.505000e+12  1.550200e+12   \n",
       " 2019-02-28             3.296000e+11  4.499000e+11  2.469600e+12  1.544500e+12   \n",
       " 2019-03-31             3.302000e+11  4.503000e+11  2.493600e+12  1.534900e+12   \n",
       " 2019-04-30             3.323000e+11  4.498000e+11  2.488000e+12  1.522900e+12   \n",
       " 2019-05-31             3.344000e+11  4.513000e+11  2.490200e+12  1.521800e+12   \n",
       " 2019-06-30             3.363000e+11  4.500000e+11  2.487300e+12  1.506900e+12   \n",
       " 2019-07-31             3.389000e+11  4.488000e+11  2.494400e+12  1.506500e+12   \n",
       " 2019-08-31             3.399000e+11  4.480000e+11  2.492200e+12  1.497800e+12   \n",
       " 2019-09-30             3.422000e+11  4.469000e+11  2.484900e+12  1.496200e+12   \n",
       " 2019-10-31             3.426000e+11  4.464000e+11  2.483100e+12           NaN   \n",
       " \n",
       " ID                           France  United Kingdom         India  \\\n",
       " PERIOD_REFERENCE_DATE                                               \n",
       " 2016-01-31             5.045000e+11    8.077000e+11  2.644000e+11   \n",
       " 2016-02-29             5.028000e+11    8.052000e+11  2.632000e+11   \n",
       " 2016-03-31             5.028000e+11    8.022000e+11  2.622000e+11   \n",
       " 2016-04-30             5.038000e+11    8.024000e+11  2.606000e+11   \n",
       " 2016-05-31             5.036000e+11    8.000000e+11  2.603000e+11   \n",
       " 2016-06-30             5.001000e+11    7.954000e+11  2.605000e+11   \n",
       " 2016-07-31             4.996000e+11    7.901000e+11  2.589000e+11   \n",
       " 2016-08-31             5.001000e+11    7.851000e+11  2.589000e+11   \n",
       " 2016-09-30             4.996000e+11    7.806000e+11  2.599000e+11   \n",
       " 2016-10-31             4.974000e+11    7.760000e+11  2.620000e+11   \n",
       " 2016-11-30             4.984000e+11    7.718000e+11  2.624000e+11   \n",
       " 2016-12-31             4.999000e+11    7.673000e+11  2.639000e+11   \n",
       " 2017-01-31             4.976000e+11    7.654000e+11  2.652000e+11   \n",
       " 2017-02-28             4.968000e+11    7.652000e+11  2.691000e+11   \n",
       " 2017-03-31             4.985000e+11    7.657000e+11  2.755000e+11   \n",
       " 2017-04-30             4.971000e+11    7.655000e+11  2.795000e+11   \n",
       " 2017-05-31             4.992000e+11    7.659000e+11  2.814000e+11   \n",
       " 2017-06-30             5.019000e+11    7.671000e+11  2.823000e+11   \n",
       " 2017-07-31             5.059000e+11    7.742000e+11  2.832000e+11   \n",
       " 2017-08-31             5.105000e+11    7.806000e+11  2.850000e+11   \n",
       " 2017-09-30             5.173000e+11    7.897000e+11  2.907000e+11   \n",
       " 2017-10-31             5.238000e+11    7.979000e+11  2.901000e+11   \n",
       " 2017-11-30             5.293000e+11    8.043000e+11  2.964000e+11   \n",
       " 2017-12-31             5.365000e+11    8.110000e+11  3.001000e+11   \n",
       " 2018-01-31             5.459000e+11    8.218000e+11  3.031000e+11   \n",
       " 2018-02-28             5.544000e+11    8.311000e+11  3.044000e+11   \n",
       " 2018-03-31             5.624000e+11    8.403000e+11  3.043000e+11   \n",
       " 2018-04-30             5.717000e+11    8.455000e+11  3.056000e+11   \n",
       " 2018-05-31             5.736000e+11    8.474000e+11  3.105000e+11   \n",
       " 2018-06-30             5.775000e+11    8.518000e+11  3.146000e+11   \n",
       " 2018-07-31             5.811000e+11    8.546000e+11  3.178000e+11   \n",
       " 2018-08-31             5.816000e+11    8.555000e+11  3.222000e+11   \n",
       " 2018-09-30             5.810000e+11    8.551000e+11  3.216000e+11   \n",
       " 2018-10-31             5.837000e+11    8.571000e+11  3.254000e+11   \n",
       " 2018-11-30             5.837000e+11    8.584000e+11  3.256000e+11   \n",
       " 2018-12-31             5.821000e+11    8.570000e+11  3.257000e+11   \n",
       " 2019-01-31             5.815000e+11    8.549000e+11  3.267000e+11   \n",
       " 2019-02-28             5.808000e+11    8.541000e+11  3.274000e+11   \n",
       " 2019-03-31             5.794000e+11    8.547000e+11  3.306000e+11   \n",
       " 2019-04-30             5.766000e+11    8.497000e+11  3.308000e+11   \n",
       " 2019-05-31             5.793000e+11    8.467000e+11  3.319000e+11   \n",
       " 2019-06-30             5.787000e+11    8.432000e+11  3.292000e+11   \n",
       " 2019-07-31             5.769000e+11    8.409000e+11  3.298000e+11   \n",
       " 2019-08-31             5.774000e+11    8.384000e+11  3.282000e+11   \n",
       " 2019-09-30             5.752000e+11    8.359000e+11  3.263000e+11   \n",
       " 2019-10-31             5.730000e+11             NaN  3.260000e+11   \n",
       " \n",
       " ID                            Japan  United States of America  \n",
       " PERIOD_REFERENCE_DATE                                          \n",
       " 2016-01-31             6.183000e+11              2.253700e+12  \n",
       " 2016-02-29             6.180000e+11              2.244200e+12  \n",
       " 2016-03-31             6.176000e+11              2.233000e+12  \n",
       " 2016-04-30             6.166000e+11              2.223100e+12  \n",
       " 2016-05-31             6.158000e+11              2.214900e+12  \n",
       " 2016-06-30             6.204000e+11              2.208200e+12  \n",
       " 2016-07-31             6.214000e+11              2.204100e+12  \n",
       " 2016-08-31             6.262000e+11              2.204700e+12  \n",
       " 2016-09-30             6.314000e+11              2.205500e+12  \n",
       " 2016-10-31             6.334000e+11              2.206400e+12  \n",
       " 2016-11-30             6.396000e+11              2.207700e+12  \n",
       " 2016-12-31             6.450000e+11              2.215800e+12  \n",
       " 2017-01-31             6.470000e+11              2.229000e+12  \n",
       " 2017-02-28             6.534000e+11              2.241100e+12  \n",
       " 2017-03-31             6.603000e+11              2.253500e+12  \n",
       " 2017-04-30             6.640000e+11              2.262900e+12  \n",
       " 2017-05-31             6.694000e+11              2.271800e+12  \n",
       " 2017-06-30             6.718000e+11              2.281600e+12  \n",
       " 2017-07-31             6.745000e+11              2.290300e+12  \n",
       " 2017-08-31             6.792000e+11              2.297900e+12  \n",
       " 2017-09-30             6.821000e+11              2.307300e+12  \n",
       " 2017-10-31             6.848000e+11              2.319500e+12  \n",
       " 2017-11-30             6.913000e+11              2.336900e+12  \n",
       " 2017-12-31             6.984000e+11              2.352500e+12  \n",
       " 2018-01-31             7.060000e+11              2.362900e+12  \n",
       " 2018-02-28             7.098000e+11              2.375900e+12  \n",
       " 2018-03-31             7.153000e+11              2.393600e+12  \n",
       " 2018-04-30             7.212000e+11              2.410900e+12  \n",
       " 2018-05-31             7.268000e+11              2.432000e+12  \n",
       " 2018-06-30             7.313000e+11              2.448700e+12  \n",
       " 2018-07-31             7.340000e+11              2.462700e+12  \n",
       " 2018-08-31             7.371000e+11              2.474900e+12  \n",
       " 2018-09-30             7.356000e+11              2.486500e+12  \n",
       " 2018-10-31             7.405000e+11              2.497300e+12  \n",
       " 2018-11-30             7.403000e+11              2.502300e+12  \n",
       " 2018-12-31             7.383000e+11              2.501300e+12  \n",
       " 2019-01-31             7.346000e+11              2.505800e+12  \n",
       " 2019-02-28             7.325000e+11              2.509200e+12  \n",
       " 2019-03-31             7.277000e+11              2.510200e+12  \n",
       " 2019-04-30             7.239000e+11              2.508200e+12  \n",
       " 2019-05-31             7.193000e+11              2.506800e+12  \n",
       " 2019-06-30             7.161000e+11              2.503900e+12  \n",
       " 2019-07-31             7.169000e+11              2.504100e+12  \n",
       " 2019-08-31             7.145000e+11              2.505700e+12  \n",
       " 2019-09-30             7.138000e+11              2.503500e+12  \n",
       " 2019-10-31             7.104000e+11              2.500500e+12  ,\n",
       " 'GDP': ID                     Australia    Canada     China   Germany    France  \\\n",
       " PERIOD_REFERENCE_DATE                                                      \n",
       " 2016-03-31              0.006731  0.001762  0.016750  0.004750  0.003062   \n",
       " 2016-06-30              0.015001  0.002972  0.033781  0.009523  0.005506   \n",
       " 2016-09-30              0.020956  0.005885  0.051096  0.014823  0.007480   \n",
       " 2016-12-31              0.028037  0.010043  0.068965  0.019643  0.010482   \n",
       " 2017-03-31              0.033747  0.015758  0.087137  0.024996  0.013938   \n",
       " 2017-06-30              0.039259  0.025866  0.105619  0.030634  0.019924   \n",
       " 2017-09-30              0.047088  0.034294  0.124138  0.038363  0.027130   \n",
       " 2017-12-31              0.053643  0.042456  0.142967  0.047189  0.034750   \n",
       " 2018-03-31              0.061742  0.049065  0.162398  0.053211  0.040866   \n",
       " 2018-06-30              0.070150  0.053620  0.181868  0.058740  0.045694   \n",
       " 2018-09-30              0.076710  0.058867  0.201073  0.061652  0.049579   \n",
       " 2018-12-31              0.082465  0.063625  0.220290  0.063244  0.052687   \n",
       " 2019-03-31              0.087127  0.067510  0.239815  0.065902  0.055994   \n",
       " 2019-06-30              0.091511  0.072673  0.259032  0.066702  0.059743   \n",
       " 2019-09-30              0.096271  0.077102  0.277918  0.068035  0.063333   \n",
       " \n",
       " ID                     United Kingdom     India     Japan  \\\n",
       " PERIOD_REFERENCE_DATE                                       \n",
       " 2016-03-31                   0.005250  0.022722  0.001000   \n",
       " 2016-06-30                   0.010025  0.046670  0.001751   \n",
       " 2016-09-30                   0.014823  0.069868  0.003003   \n",
       " 2016-12-31                   0.019389  0.090051  0.006012   \n",
       " 2017-03-31                   0.024996  0.109242  0.009784   \n",
       " 2017-06-30                   0.029865  0.125880  0.014076   \n",
       " 2017-09-30                   0.034499  0.145020  0.019653   \n",
       " 2017-12-31                   0.038637  0.167062  0.025771   \n",
       " 2018-03-31                   0.041493  0.190695  0.029105   \n",
       " 2018-06-30                   0.044878  0.214509  0.032964   \n",
       " 2018-09-30                   0.049058  0.235763  0.033223   \n",
       " 2018-12-31                   0.052992  0.256153  0.033997   \n",
       " 2019-03-31                   0.058520  0.274367  0.036324   \n",
       " 2019-06-30                   0.061960  0.290297  0.038656   \n",
       " 2019-09-30                   0.064615  0.304813  0.042031   \n",
       " \n",
       " ID                     United States of America  \n",
       " PERIOD_REFERENCE_DATE                            \n",
       " 2016-03-31                             0.004000  \n",
       " 2016-06-30                             0.007263  \n",
       " 2016-09-30                             0.011292  \n",
       " 2016-12-31                             0.016349  \n",
       " 2017-03-31                             0.021684  \n",
       " 2017-06-30                             0.027304  \n",
       " 2017-09-30                             0.033467  \n",
       " 2017-12-31                             0.040702  \n",
       " 2018-03-31                             0.048247  \n",
       " 2018-06-30                             0.056633  \n",
       " 2018-09-30                             0.064822  \n",
       " 2018-12-31                             0.071477  \n",
       " 2019-03-31                             0.078709  \n",
       " 2019-06-30                             0.084912  \n",
       " 2019-09-30                             0.090608  ,\n",
       " 'NEER': ID                     Australia  Canada   China  Germany  France  \\\n",
       " PERIOD_REFERENCE_DATE                                               \n",
       " 2016-01-31                 85.46   77.54  124.00    98.02   97.63   \n",
       " 2016-02-29                 86.01   79.57  123.18    98.77   98.27   \n",
       " 2016-03-31                 89.48   82.46  122.24    98.11   97.82   \n",
       " 2016-04-30                 90.33   84.53  120.66    98.50   98.19   \n",
       " 2016-05-31                 86.83   84.27  120.49    98.79   98.36   \n",
       " 2016-06-30                 87.87   84.72  118.99    98.52   98.17   \n",
       " 2016-07-31                 89.90   84.05  117.47    98.30   98.04   \n",
       " 2016-08-31                 90.30   84.03  116.67    98.50   98.27   \n",
       " 2016-09-30                 90.05   83.58  116.56    98.65   98.39   \n",
       " 2016-10-31                 91.47   83.03  116.78    98.47   98.28   \n",
       " 2016-11-30                 91.98   82.61  117.10    98.32   98.09   \n",
       " 2016-12-31                 91.09   83.86  117.88    97.80   97.63   \n",
       " 2017-01-31                 92.07   84.70  117.87    97.99   97.85   \n",
       " 2017-02-28                 93.98   84.98  116.77    97.55   97.53   \n",
       " 2017-03-31                 93.48   82.93  116.04    97.77   97.74   \n",
       " 2017-04-30                 91.96   82.43  115.36    97.55   97.56   \n",
       " 2017-05-31                 90.19   81.06  114.73    98.81   98.63   \n",
       " 2017-06-30                 90.80   82.45  115.29    99.29   99.06   \n",
       " 2017-07-31                 93.00   85.90  115.26   100.31   99.90   \n",
       " 2017-08-31                 93.36   86.16  115.82   101.23  100.67   \n",
       " 2017-09-30                 93.35   88.12  117.14   101.26  100.68   \n",
       " 2017-10-31                 92.09   86.58  117.34   101.14  100.58   \n",
       " 2017-11-30                 90.11   85.45  117.29   101.20  100.64   \n",
       " 2017-12-31                 89.88   85.28  117.23   101.38  100.80   \n",
       " 2018-01-31                 91.44   86.66  118.05   101.77  101.18   \n",
       " 2018-02-28                 89.69   85.26  119.37   101.97  101.37   \n",
       " 2018-03-31                 88.34   82.96  119.34   102.10  101.45   \n",
       " 2018-04-30                 87.65   84.22  120.29   102.11  101.43   \n",
       " 2018-05-31                 87.55   84.38  121.57   101.44  100.81   \n",
       " 2018-06-30                 88.10   83.25  120.95   101.44  100.78   \n",
       " 2018-07-31                 88.38   83.47  117.17   102.25  101.45   \n",
       " 2018-08-31                 88.36   84.50  115.76   102.50  101.69   \n",
       " 2018-09-30                 86.89   84.53  115.98   103.19  102.25   \n",
       " 2018-10-31                 86.43   85.10  115.47   102.55  101.71   \n",
       " 2018-11-30                 88.28   84.20  115.40   102.01  101.25   \n",
       " 2018-12-31                 87.00   82.54  116.15   102.03  101.28   \n",
       " 2019-01-31                 85.77   82.71  116.77   101.65  100.98   \n",
       " 2019-02-28                 85.60   83.40  117.87   101.27  100.64   \n",
       " 2019-03-31                 85.02   82.48  118.76   101.06  100.47   \n",
       " 2019-04-30                 85.68   82.46  119.09   101.00  100.43   \n",
       " 2019-05-31                 84.46   82.35  117.51   101.43  100.76   \n",
       " 2019-06-30                 84.13   83.32  115.89   101.65  101.00   \n",
       " 2019-07-31                 84.50   84.49  116.17   101.18  100.63   \n",
       " 2019-08-31                 83.05   84.05  114.32   101.70  100.99   \n",
       " 2019-09-30                 83.82   84.36  113.63   101.27  100.62   \n",
       " 2019-10-31                 83.38   84.55  113.58   101.27  100.64   \n",
       " \n",
       " ID                     United Kingdom  India  Japan  United States of America  \n",
       " PERIOD_REFERENCE_DATE                                                          \n",
       " 2016-01-31                     110.72  76.95  81.40                    121.96  \n",
       " 2016-02-29                     108.35  75.27  83.32                    120.96  \n",
       " 2016-03-31                     106.94  75.97  83.71                    118.65  \n",
       " 2016-04-30                     105.86  75.46  85.35                    116.65  \n",
       " 2016-05-31                     108.23  75.43  86.59                    117.85  \n",
       " 2016-06-30                     106.06  75.16  89.72                    118.38  \n",
       " 2016-07-31                      99.12  75.69  91.32                    119.08  \n",
       " 2016-08-31                      97.70  75.41  93.27                    117.99  \n",
       " 2016-09-30                      98.19  75.80  92.94                    118.91  \n",
       " 2016-10-31                      93.17  76.60  91.98                    120.04  \n",
       " 2016-11-30                      95.75  76.81  89.53                    123.12  \n",
       " 2016-12-31                      97.98  77.69  84.46                    125.20  \n",
       " 2017-01-31                      96.32  77.10  84.93                    125.18  \n",
       " 2017-02-28                      96.90  77.76  85.84                    123.30  \n",
       " 2017-03-31                      95.61  79.06  85.73                    122.76  \n",
       " 2017-04-30                      97.52  80.31  87.64                    121.80  \n",
       " 2017-05-31                      98.09  79.83  85.50                    121.23  \n",
       " 2017-06-30                      96.01  79.20  85.84                    119.57  \n",
       " 2017-07-31                      96.02  78.64  84.09                    117.99  \n",
       " 2017-08-31                      94.15  78.34  85.25                    116.55  \n",
       " 2017-09-30                      96.03  77.13  83.86                    115.43  \n",
       " 2017-10-31                      96.33  77.12  82.93                    117.55  \n",
       " 2017-11-30                      96.71  77.39  82.91                    117.80  \n",
       " 2017-12-31                      97.45  77.68  82.35                    117.39  \n",
       " 2018-01-31                      97.91  76.89  82.20                    114.80  \n",
       " 2018-02-28                      98.15  75.34  83.88                    113.76  \n",
       " 2018-03-31                      98.40  74.63  85.42                    114.07  \n",
       " 2018-04-30                      99.57  74.11  84.32                    114.00  \n",
       " 2018-05-31                      98.08  73.52  84.14                    117.21  \n",
       " 2018-06-30                      97.83  74.04  84.79                    119.16  \n",
       " 2018-07-31                      97.66  73.97  85.12                    119.76  \n",
       " 2018-08-31                      96.82  73.83  86.38                    120.72  \n",
       " 2018-09-30                      97.97  71.22  85.72                    121.03  \n",
       " 2018-10-31                      98.47  70.27  85.61                    121.80  \n",
       " 2018-11-30                      98.06  72.29  85.40                    123.13  \n",
       " 2018-12-31                      96.16  73.18  86.03                    123.00  \n",
       " 2019-01-31                      97.13  72.57  87.80                    121.12  \n",
       " 2019-02-28                      98.33  71.94  86.40                    120.93  \n",
       " 2019-03-31                      99.86  73.88  85.86                    121.27  \n",
       " 2019-04-30                      99.27  74.18  85.67                    121.43  \n",
       " 2019-05-31                      98.58  74.52  88.08                    122.72  \n",
       " 2019-06-30                      96.59  74.65  89.44                    122.26  \n",
       " 2019-07-31                      95.17  75.30  89.12                    121.76  \n",
       " 2019-08-31                      93.81  73.75  92.37                    124.03  \n",
       " 2019-09-30                      96.06  73.76  91.51                    124.44  \n",
       " 2019-10-31                      97.90  73.90  90.59                    123.85  ,\n",
       " 'PMI': ID                     Australia  Canada  China  Germany  France  \\\n",
       " PERIOD_REFERENCE_DATE                                              \n",
       " 2016-12-31                  57.2    51.8   51.9     55.6    53.5   \n",
       " 2017-01-31                  55.3    53.5   51.0     56.4    53.6   \n",
       " 2017-02-28                  55.7    54.7   51.7     56.8    52.2   \n",
       " 2017-03-31                  56.3    55.5   51.2     58.3    53.3   \n",
       " 2017-04-30                  55.5    55.9   50.3     58.2    55.1   \n",
       " 2017-05-31                  55.9    55.1   49.6     59.5    53.8   \n",
       " 2017-06-30                  56.2    54.7   50.4     59.6    54.8   \n",
       " 2017-07-31                  54.4    55.5   51.1     58.1    54.9   \n",
       " 2017-08-31                  53.5    54.6   51.6     59.3    55.8   \n",
       " 2017-09-30                  53.8    55.0   51.0     60.6    56.1   \n",
       " 2017-10-31                  55.5    54.3   51.0     60.6    56.1   \n",
       " 2017-11-30                  56.3    54.4   50.8     62.5    57.7   \n",
       " 2017-12-31                  57.1    54.7   51.5     63.3    58.8   \n",
       " 2018-01-31                  55.4    55.9   51.5     61.1    58.4   \n",
       " 2018-02-28                  55.6    55.6   51.6     60.6    55.9   \n",
       " 2018-03-31                  54.3    55.7   51.0     58.2    53.7   \n",
       " 2018-04-30                  55.5    55.5   51.1     58.1    53.8   \n",
       " 2018-05-31                  53.2    56.2   51.1     56.9    54.4   \n",
       " 2018-06-30                  55.0    57.1   51.0     55.9    52.5   \n",
       " 2018-07-31                  52.4    56.9   50.8     56.9    53.3   \n",
       " 2018-08-31                  53.2    56.8   50.6     55.9    53.5   \n",
       " 2018-09-30                  54.0    54.8   50.0     53.7    52.5   \n",
       " 2018-10-31                  54.5    53.9   50.1     52.2    51.2   \n",
       " 2018-11-30                  54.6    54.9   50.2     51.8    50.8   \n",
       " 2018-12-31                  54.0    53.6   49.7     51.5    49.7   \n",
       " 2019-01-31                  53.9    53.0   48.3     49.7    51.2   \n",
       " 2019-02-28                  52.9    52.6   49.9     47.6    51.5   \n",
       " 2019-03-31                  52.0    50.5   50.8     44.1    49.7   \n",
       " 2019-04-30                  50.9    49.7   50.2     44.4    50.0   \n",
       " 2019-05-31                  51.0    49.1   50.2     44.3    50.6   \n",
       " 2019-06-30                  52.0    49.2   49.4     45.0    51.9   \n",
       " 2019-07-31                  51.6    50.2   49.9     43.2    49.7   \n",
       " 2019-08-31                  50.9    49.1   50.4     43.5    51.1   \n",
       " 2019-09-30                  50.3    51.0   51.4     41.7    50.1   \n",
       " 2019-10-31                  50.0    51.2   51.7     42.1    50.7   \n",
       " \n",
       " ID                     United Kingdom  India  Japan  United States of America  \n",
       " PERIOD_REFERENCE_DATE                                                          \n",
       " 2016-12-31                       55.5   49.6   52.4                      54.3  \n",
       " 2017-01-31                       55.3   50.4   52.7                      55.0  \n",
       " 2017-02-28                       54.9   50.7   53.3                      54.2  \n",
       " 2017-03-31                       54.1   52.5   52.4                      53.3  \n",
       " 2017-04-30                       57.7   52.5   52.7                      52.8  \n",
       " 2017-05-31                       56.5   51.6   53.1                      52.7  \n",
       " 2017-06-30                       54.2   50.9   52.4                      52.0  \n",
       " 2017-07-31                       55.5   47.9   52.1                      53.3  \n",
       " 2017-08-31                       57.0   51.2   52.2                      52.8  \n",
       " 2017-09-30                       55.5   51.2   52.9                      53.1  \n",
       " 2017-10-31                       56.6   50.3   52.8                      54.6  \n",
       " 2017-11-30                       58.2   52.6   53.6                      53.9  \n",
       " 2017-12-31                       55.7   54.7   54.0                      55.1  \n",
       " 2018-01-31                       55.2   52.4   54.8                      55.5  \n",
       " 2018-02-28                       55.3   52.1   54.1                      55.3  \n",
       " 2018-03-31                       54.8   51.0   53.1                      55.6  \n",
       " 2018-04-30                       53.8   51.6   53.8                      56.5  \n",
       " 2018-05-31                       54.3   51.2   52.8                      56.4  \n",
       " 2018-06-30                       54.0   53.1   53.0                      55.4  \n",
       " 2018-07-31                       53.9   52.3   52.3                      55.3  \n",
       " 2018-08-31                       52.9   51.7   52.5                      54.7  \n",
       " 2018-09-30                       53.7   52.2   52.5                      55.6  \n",
       " 2018-10-31                       51.1   53.1   52.9                      55.7  \n",
       " 2018-11-30                       53.3   54.0   52.2                      55.3  \n",
       " 2018-12-31                       54.3   53.2   52.6                      53.8  \n",
       " 2019-01-31                       52.8   53.9   50.3                      54.9  \n",
       " 2019-02-28                       52.1   54.3   48.9                      53.0  \n",
       " 2019-03-31                       55.1   52.6   49.2                      52.4  \n",
       " 2019-04-30                       53.1   51.8   50.2                      52.6  \n",
       " 2019-05-31                       49.4   52.7   49.8                      50.5  \n",
       " 2019-06-30                       48.0   52.1   49.3                      50.6  \n",
       " 2019-07-31                       48.0   52.5   49.4                      50.4  \n",
       " 2019-08-31                       47.4   51.4   49.3                      50.3  \n",
       " 2019-09-30                       48.3   51.4   48.9                      51.1  \n",
       " 2019-10-31                       49.6   50.6   48.4                      51.3  ,\n",
       " 'Trade_Balance': ID                        Australia        Canada         China       Germany  \\\n",
       " PERIOD_REFERENCE_DATE                                                           \n",
       " 2016-01-31            -2.870000e+10 -1.820000e+10  5.914000e+11  2.673000e+11   \n",
       " 2016-02-29            -2.970000e+10 -1.920000e+10  5.591000e+11  2.667000e+11   \n",
       " 2016-03-31            -3.020000e+10 -1.890000e+10  5.815000e+11  2.701000e+11   \n",
       " 2016-04-30            -2.840000e+10 -1.940000e+10  5.882000e+11  2.756000e+11   \n",
       " 2016-05-31            -2.730000e+10 -2.000000e+10  5.759000e+11  2.774000e+11   \n",
       " 2016-06-30            -2.680000e+10 -2.260000e+10  5.760000e+11  2.780000e+11   \n",
       " 2016-07-31            -2.600000e+10 -2.330000e+10  5.825000e+11  2.720000e+11   \n",
       " 2016-08-31            -2.520000e+10 -2.330000e+10  5.729000e+11  2.770000e+11   \n",
       " 2016-09-30            -2.350000e+10 -2.480000e+10  5.537000e+11  2.786000e+11   \n",
       " 2016-10-31            -2.170000e+10 -2.400000e+10  5.406000e+11  2.749000e+11   \n",
       " 2016-11-30            -1.720000e+10 -2.080000e+10  5.297000e+11  2.766000e+11   \n",
       " 2016-12-31            -1.080000e+10 -1.910000e+10  5.097000e+11  2.760000e+11   \n",
       " 2017-01-31            -7.500000e+09 -1.820000e+10  5.015000e+11  2.774000e+11   \n",
       " 2017-02-28            -2.800000e+09 -1.680000e+10  4.624000e+11  2.777000e+11   \n",
       " 2017-03-31             1.000000e+09 -1.460000e+10  4.602000e+11  2.764000e+11   \n",
       " 2017-04-30             2.200000e+09 -1.300000e+10  4.569000e+11  2.671000e+11   \n",
       " 2017-05-31             5.300000e+09 -1.130000e+10  4.520000e+11  2.683000e+11   \n",
       " 2017-06-30             8.400000e+09 -1.090000e+10  4.478000e+11  2.663000e+11   \n",
       " 2017-07-31             1.000000e+10 -1.220000e+10  4.444000e+11  2.674000e+11   \n",
       " 2017-08-31             1.170000e+10 -1.340000e+10  4.343000e+11  2.697000e+11   \n",
       " 2017-09-30             1.280000e+10 -1.290000e+10  4.212000e+11  2.719000e+11   \n",
       " 2017-10-31             1.380000e+10 -1.320000e+10  4.100000e+11  2.736000e+11   \n",
       " 2017-11-30             1.220000e+10 -1.630000e+10  4.053000e+11  2.779000e+11   \n",
       " 2017-12-31             7.500000e+09 -1.880000e+10  4.196000e+11  2.804000e+11   \n",
       " 2018-01-31             7.400000e+09 -2.010000e+10  3.892000e+11  2.857000e+11   \n",
       " 2018-02-28             6.300000e+09 -2.090000e+10  4.323000e+11  2.872000e+11   \n",
       " 2018-03-31             5.200000e+09 -2.320000e+10  4.038000e+11  2.910000e+11   \n",
       " 2018-04-30             6.200000e+09 -2.360000e+10  3.935000e+11  2.969000e+11   \n",
       " 2018-05-31             5.400000e+09 -2.490000e+10  3.770000e+11  2.962000e+11   \n",
       " 2018-06-30             5.900000e+09 -2.290000e+10  3.767000e+11  2.967000e+11   \n",
       " 2018-07-31             7.000000e+09 -2.070000e+10  3.594000e+11  2.939000e+11   \n",
       " 2018-08-31             8.200000e+09 -1.870000e+10  3.456000e+11  2.903000e+11   \n",
       " 2018-09-30             9.300000e+09 -1.690000e+10  3.485000e+11  2.826000e+11   \n",
       " 2018-10-31             1.100000e+10 -1.620000e+10  3.446000e+11  2.817000e+11   \n",
       " 2018-11-30             1.280000e+10 -1.550000e+10  3.480000e+11  2.766000e+11   \n",
       " 2018-12-31             1.700000e+10 -1.700000e+10  3.509000e+11  2.707000e+11   \n",
       " 2019-01-31             1.940000e+10 -1.850000e+10  3.716000e+11  2.659000e+11   \n",
       " 2019-02-28             2.180000e+10 -1.910000e+10  3.425000e+11  2.631000e+11   \n",
       " 2019-03-31             2.440000e+10 -1.800000e+10  3.801000e+11  2.574000e+11   \n",
       " 2019-04-30             2.690000e+10 -1.750000e+10  3.672000e+11  2.523000e+11   \n",
       " 2019-05-31             3.050000e+10 -1.490000e+10  3.854000e+11  2.518000e+11   \n",
       " 2019-06-30             3.460000e+10 -1.480000e+10  3.950000e+11  2.448000e+11   \n",
       " 2019-07-31             3.850000e+10 -1.550000e+10  4.118000e+11  2.499000e+11   \n",
       " 2019-08-31             4.150000e+10 -1.620000e+10  4.202000e+11  2.478000e+11   \n",
       " 2019-09-30             4.440000e+10 -1.630000e+10  4.291000e+11  2.500000e+11   \n",
       " 2019-10-31             4.550000e+10 -1.650000e+10  4.390000e+11           NaN   \n",
       " \n",
       " ID                           France  United Kingdom         India  \\\n",
       " PERIOD_REFERENCE_DATE                                               \n",
       " 2016-01-31            -4.700000e+10   -3.560000e+10 -1.255000e+11   \n",
       " 2016-02-29            -4.880000e+10   -3.370000e+10 -1.254000e+11   \n",
       " 2016-03-31            -4.800000e+10   -3.500000e+10 -1.184000e+11   \n",
       " 2016-04-30            -4.890000e+10   -3.540000e+10 -1.118000e+11   \n",
       " 2016-05-31            -4.630000e+10   -3.500000e+10 -1.077000e+11   \n",
       " 2016-06-30            -4.730000e+10   -3.740000e+10 -1.050000e+11   \n",
       " 2016-07-31            -4.780000e+10   -3.690000e+10 -9.970000e+10   \n",
       " 2016-08-31            -4.980000e+10   -3.990000e+10 -9.500000e+10   \n",
       " 2016-09-30            -5.020000e+10   -4.480000e+10 -9.390000e+10   \n",
       " 2016-10-31            -5.100000e+10   -3.920000e+10 -9.540000e+10   \n",
       " 2016-11-30            -5.200000e+10   -3.970000e+10 -9.840000e+10   \n",
       " 2016-12-31            -5.080000e+10   -4.350000e+10 -9.750000e+10   \n",
       " 2017-01-31            -5.580000e+10   -4.540000e+10 -9.970000e+10   \n",
       " 2017-02-28            -5.700000e+10   -4.360000e+10 -1.027000e+11   \n",
       " 2017-03-31            -5.720000e+10   -4.240000e+10 -1.089000e+11   \n",
       " 2017-04-30            -5.830000e+10   -3.960000e+10 -1.173000e+11   \n",
       " 2017-05-31            -6.050000e+10   -4.130000e+10 -1.249000e+11   \n",
       " 2017-06-30            -6.160000e+10   -4.180000e+10 -1.297000e+11   \n",
       " 2017-07-31            -6.390000e+10   -4.030000e+10 -1.334000e+11   \n",
       " 2017-08-31            -6.380000e+10   -3.740000e+10 -1.384000e+11   \n",
       " 2017-09-30            -6.360000e+10   -3.150000e+10 -1.388000e+11   \n",
       " 2017-10-31            -6.380000e+10   -3.300000e+10 -1.422000e+11   \n",
       " 2017-11-30            -6.570000e+10   -3.340000e+10 -1.439000e+11   \n",
       " 2017-12-31            -6.630000e+10   -3.230000e+10 -1.476000e+11   \n",
       " 2018-01-31            -6.610000e+10   -3.220000e+10 -1.534000e+11   \n",
       " 2018-02-28            -6.650000e+10   -3.060000e+10 -1.561000e+11   \n",
       " 2018-03-31            -6.690000e+10   -3.110000e+10 -1.590000e+11   \n",
       " 2018-04-30            -6.820000e+10   -3.730000e+10 -1.595000e+11   \n",
       " 2018-05-31            -7.200000e+10   -4.100000e+10 -1.602000e+11   \n",
       " 2018-06-30            -7.370000e+10   -4.010000e+10 -1.639000e+11   \n",
       " 2018-07-31            -7.230000e+10   -4.100000e+10 -1.711000e+11   \n",
       " 2018-08-31            -7.390000e+10   -4.290000e+10 -1.763000e+11   \n",
       " 2018-09-30            -7.590000e+10   -4.400000e+10 -1.818000e+11   \n",
       " 2018-10-31            -7.530000e+10   -4.570000e+10 -1.852000e+11   \n",
       " 2018-11-30            -7.400000e+10   -4.740000e+10 -1.868000e+11   \n",
       " 2018-12-31            -7.500000e+10   -5.020000e+10 -1.857000e+11   \n",
       " 2019-01-31            -7.260000e+10   -5.730000e+10 -1.847000e+11   \n",
       " 2019-02-28            -7.150000e+10   -6.660000e+10 -1.820000e+11   \n",
       " 2019-03-31            -7.260000e+10   -7.180000e+10 -1.794000e+11   \n",
       " 2019-04-30            -7.170000e+10   -7.300000e+10 -1.810000e+11   \n",
       " 2019-05-31            -6.760000e+10   -7.210000e+10 -1.818000e+11   \n",
       " 2019-06-30            -6.750000e+10   -7.010000e+10 -1.804000e+11   \n",
       " 2019-07-31            -6.800000e+10   -6.810000e+10 -1.752000e+11   \n",
       " 2019-08-31            -6.680000e+10   -6.490000e+10 -1.708000e+11   \n",
       " 2019-09-30            -6.630000e+10   -6.640000e+10 -1.667000e+11   \n",
       " 2019-10-31            -6.640000e+10             NaN -1.597000e+11   \n",
       " \n",
       " ID                            Japan  United States of America  \n",
       " PERIOD_REFERENCE_DATE                                          \n",
       " 2016-01-31            -1.890000e+10             -4.997000e+11  \n",
       " 2016-02-29            -1.330000e+10             -5.095000e+11  \n",
       " 2016-03-31            -8.600000e+09             -4.984000e+11  \n",
       " 2016-04-30            -7.000000e+08             -4.961000e+11  \n",
       " 2016-05-31             7.000000e+08             -4.979000e+11  \n",
       " 2016-06-30             7.700000e+09             -4.995000e+11  \n",
       " 2016-07-31             1.470000e+10             -5.000000e+11  \n",
       " 2016-08-31             1.890000e+10             -4.973000e+11  \n",
       " 2016-09-30             2.470000e+10             -4.941000e+11  \n",
       " 2016-10-31             2.850000e+10             -4.949000e+11  \n",
       " 2016-11-30             3.300000e+10             -5.008000e+11  \n",
       " 2016-12-31             3.730000e+10             -5.030000e+11  \n",
       " 2017-01-31             3.330000e+10             -5.074000e+11  \n",
       " 2017-02-28             3.830000e+10             -5.053000e+11  \n",
       " 2017-03-31             3.710000e+10             -5.114000e+11  \n",
       " 2017-04-30             3.400000e+10             -5.199000e+11  \n",
       " 2017-05-31             3.260000e+10             -5.262000e+11  \n",
       " 2017-06-30             3.000000e+10             -5.279000e+11  \n",
       " 2017-07-31             2.880000e+10             -5.307000e+11  \n",
       " 2017-08-31             3.000000e+10             -5.327000e+11  \n",
       " 2017-09-30             3.110000e+10             -5.373000e+11  \n",
       " 2017-10-31             2.900000e+10             -5.407000e+11  \n",
       " 2017-11-30             2.860000e+10             -5.432000e+11  \n",
       " 2017-12-31             2.620000e+10             -5.501000e+11  \n",
       " 2018-01-31             2.720000e+10             -5.558000e+11  \n",
       " 2018-02-28             1.990000e+10             -5.665000e+11  \n",
       " 2018-03-31             2.200000e+10             -5.692000e+11  \n",
       " 2018-04-30             2.340000e+10             -5.700000e+11  \n",
       " 2018-05-31             1.990000e+10             -5.677000e+11  \n",
       " 2018-06-30             2.260000e+10             -5.695000e+11  \n",
       " 2018-07-31             1.700000e+10             -5.778000e+11  \n",
       " 2018-08-31             1.210000e+10             -5.890000e+11  \n",
       " 2018-09-30             7.300000e+09             -6.015000e+11  \n",
       " 2018-10-31             8.000000e+08             -6.127000e+11  \n",
       " 2018-11-30            -6.700000e+09             -6.172000e+11  \n",
       " 2018-12-31            -1.030000e+10             -6.277000e+11  \n",
       " 2019-01-31            -1.470000e+10             -6.287000e+11  \n",
       " 2019-02-28            -1.150000e+10             -6.256000e+11  \n",
       " 2019-03-31            -1.420000e+10             -6.310000e+11  \n",
       " 2019-04-30            -1.950000e+10             -6.339000e+11  \n",
       " 2019-05-31            -2.310000e+10             -6.439000e+11  \n",
       " 2019-06-30            -2.420000e+10             -6.502000e+11  \n",
       " 2019-07-31            -2.450000e+10             -6.504000e+11  \n",
       " 2019-08-31            -2.190000e+10             -6.491000e+11  \n",
       " 2019-09-30            -2.420000e+10             -6.441000e+11  \n",
       " 2019-10-31            -2.000000e+10             -6.346000e+11  }"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Create dictionary of ECO Field >> Country time series\n",
    "dict_dfs = {x : response.get(x).df().reset_index().pivot(columns = 'ID',values = x,index = 'PERIOD_REFERENCE_DATE').rename_axis(names.to_dict()['Name'],axis=1) for x in dict_eco_fields.keys()}\n",
    "dict_dfs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Normalize  all time series\n",
    "dict_dfs['Trade_Balance'] = dict_dfs['Trade_Balance']-dict_dfs['Trade_Balance'].iloc[0]\n",
    "dict_dfs['NEER'] = dict_dfs['NEER']/dict_dfs['NEER'].iloc[0]\n",
    "dict_dfs['Exports'] = dict_dfs['Exports']-dict_dfs['Exports'].iloc[0]\n",
    "dict_dfs['PMI'] = dict_dfs['PMI']/dict_dfs['PMI'].iloc[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "0e5786051c2949e7b608f8e2d2fa3525",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Figure(axes=[Axis(scale=DateScale()), Axis(orientation='vertical', scale=LinearScale())], fig_margin={'bottom'…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "#Create scales and axis\n",
    "x_scale = bqp.DateScale()\n",
    "y_scale = bqp.LinearScale()\n",
    "x_axis = bqp.Axis(orientation='horizontal',scale=x_scale)\n",
    "y_axis = bqp.Axis(orientation='vertical',scale=y_scale)\n",
    "\n",
    "#Create Line plots for Eco indicators and start with GDP\n",
    "line_mark = bqp.Lines(x = dict_dfs['Trade_Balance'].index,\n",
    "                      y=dict_dfs['Trade_Balance'].values.T,\n",
    "                      scales={'x': x_scale, 'y': y_scale},\n",
    "                      display_legend=True,\n",
    "                      labels = list(dict_dfs['Trade_Balance'].columns)\n",
    "                      )\n",
    "\n",
    "#Assemble the figure that will old Lines and axis together\n",
    "bqplot_figure = bqp.Figure(marks=[line_mark],\n",
    "                           axes=[x_axis,y_axis],\n",
    "                           title = 'Trade_Balance',\n",
    "                           legend_location='top-left')\n",
    "\n",
    "#Change width layout for the figure\n",
    "bqplot_figure.layout.width='1500px'\n",
    "bqplot_figure"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Create interaction layer for date interval selector\n",
    "brush_main = bqp.interacts.BrushIntervalSelector(scale = bqplot_figure.scale_x,\n",
    "                                            marks = [bqplot_figure.marks[0]])\n",
    "bqplot_figure.interaction=brush_main"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Downloading Sector Total Returns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "country = bq.data.cntry_of_risk()\n",
    "sector = bq.data.classification_name('GICS','1')\n",
    "sector_string = sector.format_for_query_string().replace(' ','').upper()\n",
    "list_countries = ['US','CN','FR','JP','GB','CH','CA','DE','IN','HK','AU']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Creating exclusion for sector/country groups that have less than 7 securities strictly\n",
    "count = bq.func.ungroup(bq.func.count(bq.func.group(bq.data.id(),\n",
    "                                                    [country,sector])\n",
    "                                     ),UNGROUPALLLEVELS='TRUE')\n",
    "\n",
    "#Filter large Bloomberg bond index\n",
    "univ = bq.univ.filter(bq.univ.members('BWORLD Index'),\n",
    "                      country.in_(list_countries).and_(count>7)\n",
    "                     )\n",
    "\n",
    "#Use the monthly return field for given time range\n",
    "ret = bq.data.day_to_day_total_return(DATES=bq.func.range('2016-01-01','2019-10-01'),\n",
    "                                      per='M',\n",
    "                                      fill='prev')\n",
    "\n",
    "#Get market cap for the monthly return dates (date alignment is paramount)\n",
    "mkt_cap = bq.func.applypreferences(bq.data.cur_mkt_cap(CURRENCY='USD',\n",
    "                                                       fill='prev'),\n",
    "                                   aligndatesby=ret['DATE'])\n",
    "\n",
    "#Perform weighted average return by market cap for every date by country/sector grouping\n",
    "wavg_ret = bq.func.wavg(bq.func.group(ret,[ret['DATE'],country,sector]),\n",
    "                        bq.func.group(mkt_cap,[mkt_cap['DATE'],country,sector]))\n",
    "\n",
    "request = bql.Request(univ,{'wavg_ret':wavg_ret})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "response = bq.execute(request)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>DATE</th>\n",
       "      <th>ORIG_IDS</th>\n",
       "      <th>DAY_TO_DAY_TOTAL_RETURN(PER=PER.M,FILL=FILL.PREV,DATES=RANGE(2016-01-01,2019-10-01)).DATE</th>\n",
       "      <th>CNTRY_OF_RISK()</th>\n",
       "      <th>CLASSIFICATION_NAME(CLASSIFICATION_SCHEME.GICS,CLASSIFICATION_LEVEL.1)</th>\n",
       "      <th>wavg_ret</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ID</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2016-01-29T00-00-00Z:AU:Consumer Discretionary</th>\n",
       "      <td>2016-01-29</td>\n",
       "      <td>None</td>\n",
       "      <td>2016-01-29</td>\n",
       "      <td>AU</td>\n",
       "      <td>Consumer Discretionary</td>\n",
       "      <td>0.007244</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-01-29T00-00-00Z:AU:Financials</th>\n",
       "      <td>2016-01-29</td>\n",
       "      <td>None</td>\n",
       "      <td>2016-01-29</td>\n",
       "      <td>AU</td>\n",
       "      <td>Financials</td>\n",
       "      <td>-0.091320</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-01-29T00-00-00Z:AU:Industrials</th>\n",
       "      <td>2016-01-29</td>\n",
       "      <td>None</td>\n",
       "      <td>2016-01-29</td>\n",
       "      <td>AU</td>\n",
       "      <td>Industrials</td>\n",
       "      <td>-0.064083</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-01-29T00-00-00Z:AU:Materials</th>\n",
       "      <td>2016-01-29</td>\n",
       "      <td>None</td>\n",
       "      <td>2016-01-29</td>\n",
       "      <td>AU</td>\n",
       "      <td>Materials</td>\n",
       "      <td>-0.115078</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-01-29T00-00-00Z:AU:Real Estate</th>\n",
       "      <td>2016-01-29</td>\n",
       "      <td>None</td>\n",
       "      <td>2016-01-29</td>\n",
       "      <td>AU</td>\n",
       "      <td>Real Estate</td>\n",
       "      <td>0.008328</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-01-29T00-00-00Z:CA:Communication Services</th>\n",
       "      <td>2016-01-29</td>\n",
       "      <td>None</td>\n",
       "      <td>2016-01-29</td>\n",
       "      <td>CA</td>\n",
       "      <td>Communication Services</td>\n",
       "      <td>0.019575</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-01-29T00-00-00Z:CA:Consumer Discretionary</th>\n",
       "      <td>2016-01-29</td>\n",
       "      <td>None</td>\n",
       "      <td>2016-01-29</td>\n",
       "      <td>CA</td>\n",
       "      <td>Consumer Discretionary</td>\n",
       "      <td>-0.091691</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-01-29T00-00-00Z:CA:Consumer Staples</th>\n",
       "      <td>2016-01-29</td>\n",
       "      <td>None</td>\n",
       "      <td>2016-01-29</td>\n",
       "      <td>CA</td>\n",
       "      <td>Consumer Staples</td>\n",
       "      <td>0.012993</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-01-29T00-00-00Z:CA:Energy</th>\n",
       "      <td>2016-01-29</td>\n",
       "      <td>None</td>\n",
       "      <td>2016-01-29</td>\n",
       "      <td>CA</td>\n",
       "      <td>Energy</td>\n",
       "      <td>0.002211</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-01-29T00-00-00Z:CA:Financials</th>\n",
       "      <td>2016-01-29</td>\n",
       "      <td>None</td>\n",
       "      <td>2016-01-29</td>\n",
       "      <td>CA</td>\n",
       "      <td>Financials</td>\n",
       "      <td>-0.011261</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-01-29T00-00-00Z:CA:Industrials</th>\n",
       "      <td>2016-01-29</td>\n",
       "      <td>None</td>\n",
       "      <td>2016-01-29</td>\n",
       "      <td>CA</td>\n",
       "      <td>Industrials</td>\n",
       "      <td>-0.023726</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-01-29T00-00-00Z:CA:Materials</th>\n",
       "      <td>2016-01-29</td>\n",
       "      <td>None</td>\n",
       "      <td>2016-01-29</td>\n",
       "      <td>CA</td>\n",
       "      <td>Materials</td>\n",
       "      <td>0.024905</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-01-29T00-00-00Z:CA:Real Estate</th>\n",
       "      <td>2016-01-29</td>\n",
       "      <td>None</td>\n",
       "      <td>2016-01-29</td>\n",
       "      <td>CA</td>\n",
       "      <td>Real Estate</td>\n",
       "      <td>0.023793</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-01-29T00-00-00Z:CH:Financials</th>\n",
       "      <td>2016-01-29</td>\n",
       "      <td>None</td>\n",
       "      <td>2016-01-29</td>\n",
       "      <td>CH</td>\n",
       "      <td>Financials</td>\n",
       "      <td>-0.108812</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-01-29T00-00-00Z:CH:Health Care</th>\n",
       "      <td>2016-01-29</td>\n",
       "      <td>None</td>\n",
       "      <td>2016-01-29</td>\n",
       "      <td>CH</td>\n",
       "      <td>Health Care</td>\n",
       "      <td>-0.062785</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-01-29T00-00-00Z:CH:Industrials</th>\n",
       "      <td>2016-01-29</td>\n",
       "      <td>None</td>\n",
       "      <td>2016-01-29</td>\n",
       "      <td>CH</td>\n",
       "      <td>Industrials</td>\n",
       "      <td>-0.025967</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-01-29T00-00-00Z:CH:Materials</th>\n",
       "      <td>2016-01-29</td>\n",
       "      <td>None</td>\n",
       "      <td>2016-01-29</td>\n",
       "      <td>CH</td>\n",
       "      <td>Materials</td>\n",
       "      <td>-0.047310</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-01-29T00-00-00Z:CN:Communication Services</th>\n",
       "      <td>2016-01-29</td>\n",
       "      <td>None</td>\n",
       "      <td>2016-01-29</td>\n",
       "      <td>CN</td>\n",
       "      <td>Communication Services</td>\n",
       "      <td>-0.080530</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-01-29T00-00-00Z:CN:Consumer Discretionary</th>\n",
       "      <td>2016-01-29</td>\n",
       "      <td>None</td>\n",
       "      <td>2016-01-29</td>\n",
       "      <td>CN</td>\n",
       "      <td>Consumer Discretionary</td>\n",
       "      <td>-0.193981</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-01-29T00-00-00Z:CN:Consumer Staples</th>\n",
       "      <td>2016-01-29</td>\n",
       "      <td>None</td>\n",
       "      <td>2016-01-29</td>\n",
       "      <td>CN</td>\n",
       "      <td>Consumer Staples</td>\n",
       "      <td>-0.133882</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-01-29T00-00-00Z:CN:Energy</th>\n",
       "      <td>2016-01-29</td>\n",
       "      <td>None</td>\n",
       "      <td>2016-01-29</td>\n",
       "      <td>CN</td>\n",
       "      <td>Energy</td>\n",
       "      <td>-0.096377</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-01-29T00-00-00Z:CN:Financials</th>\n",
       "      <td>2016-01-29</td>\n",
       "      <td>None</td>\n",
       "      <td>2016-01-29</td>\n",
       "      <td>CN</td>\n",
       "      <td>Financials</td>\n",
       "      <td>-0.160682</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-01-29T00-00-00Z:CN:Health Care</th>\n",
       "      <td>2016-01-29</td>\n",
       "      <td>None</td>\n",
       "      <td>2016-01-29</td>\n",
       "      <td>CN</td>\n",
       "      <td>Health Care</td>\n",
       "      <td>-0.193518</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-01-29T00-00-00Z:CN:Industrials</th>\n",
       "      <td>2016-01-29</td>\n",
       "      <td>None</td>\n",
       "      <td>2016-01-29</td>\n",
       "      <td>CN</td>\n",
       "      <td>Industrials</td>\n",
       "      <td>-0.234078</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-01-29T00-00-00Z:CN:Information Technology</th>\n",
       "      <td>2016-01-29</td>\n",
       "      <td>None</td>\n",
       "      <td>2016-01-29</td>\n",
       "      <td>CN</td>\n",
       "      <td>Information Technology</td>\n",
       "      <td>-0.233428</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-01-29T00-00-00Z:CN:Materials</th>\n",
       "      <td>2016-01-29</td>\n",
       "      <td>None</td>\n",
       "      <td>2016-01-29</td>\n",
       "      <td>CN</td>\n",
       "      <td>Materials</td>\n",
       "      <td>-0.207647</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-01-29T00-00-00Z:CN:Real Estate</th>\n",
       "      <td>2016-01-29</td>\n",
       "      <td>None</td>\n",
       "      <td>2016-01-29</td>\n",
       "      <td>CN</td>\n",
       "      <td>Real Estate</td>\n",
       "      <td>-0.223190</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-01-29T00-00-00Z:CN:Utilities</th>\n",
       "      <td>2016-01-29</td>\n",
       "      <td>None</td>\n",
       "      <td>2016-01-29</td>\n",
       "      <td>CN</td>\n",
       "      <td>Utilities</td>\n",
       "      <td>-0.191128</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-01-29T00-00-00Z:DE:Communication Services</th>\n",
       "      <td>2016-01-29</td>\n",
       "      <td>None</td>\n",
       "      <td>2016-01-29</td>\n",
       "      <td>DE</td>\n",
       "      <td>Communication Services</td>\n",
       "      <td>-0.040023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-01-29T00-00-00Z:DE:Consumer Discretionary</th>\n",
       "      <td>2016-01-29</td>\n",
       "      <td>None</td>\n",
       "      <td>2016-01-29</td>\n",
       "      <td>DE</td>\n",
       "      <td>Consumer Discretionary</td>\n",
       "      <td>-0.123786</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-10-01T00-00-00Z:GB:Materials</th>\n",
       "      <td>2019-10-01</td>\n",
       "      <td>None</td>\n",
       "      <td>2019-10-01</td>\n",
       "      <td>GB</td>\n",
       "      <td>Materials</td>\n",
       "      <td>-0.020263</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-10-01T00-00-00Z:GB:Real Estate</th>\n",
       "      <td>2019-10-01</td>\n",
       "      <td>None</td>\n",
       "      <td>2019-10-01</td>\n",
       "      <td>GB</td>\n",
       "      <td>Real Estate</td>\n",
       "      <td>-0.004485</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-10-01T00-00-00Z:HK:Industrials</th>\n",
       "      <td>2019-10-01</td>\n",
       "      <td>JS SP Equity</td>\n",
       "      <td>2019-10-01</td>\n",
       "      <td>HK</td>\n",
       "      <td>Industrials</td>\n",
       "      <td>0.002677</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-10-01T00-00-00Z:HK:Real Estate</th>\n",
       "      <td>2019-10-01</td>\n",
       "      <td>HKL SP Equity</td>\n",
       "      <td>2019-10-01</td>\n",
       "      <td>HK</td>\n",
       "      <td>Real Estate</td>\n",
       "      <td>-0.008897</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-10-01T00-00-00Z:IN:Consumer Discretionary</th>\n",
       "      <td>2019-10-01</td>\n",
       "      <td>None</td>\n",
       "      <td>2019-10-01</td>\n",
       "      <td>IN</td>\n",
       "      <td>Consumer Discretionary</td>\n",
       "      <td>-0.004839</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-10-01T00-00-00Z:IN:Financials</th>\n",
       "      <td>2019-10-01</td>\n",
       "      <td>None</td>\n",
       "      <td>2019-10-01</td>\n",
       "      <td>IN</td>\n",
       "      <td>Financials</td>\n",
       "      <td>-0.006344</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-10-01T00-00-00Z:IN:Health Care</th>\n",
       "      <td>2019-10-01</td>\n",
       "      <td>None</td>\n",
       "      <td>2019-10-01</td>\n",
       "      <td>IN</td>\n",
       "      <td>Health Care</td>\n",
       "      <td>-0.010809</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-10-01T00-00-00Z:IN:Industrials</th>\n",
       "      <td>2019-10-01</td>\n",
       "      <td>None</td>\n",
       "      <td>2019-10-01</td>\n",
       "      <td>IN</td>\n",
       "      <td>Industrials</td>\n",
       "      <td>-0.006899</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-10-01T00-00-00Z:IN:Materials</th>\n",
       "      <td>2019-10-01</td>\n",
       "      <td>None</td>\n",
       "      <td>2019-10-01</td>\n",
       "      <td>IN</td>\n",
       "      <td>Materials</td>\n",
       "      <td>-0.013370</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-10-01T00-00-00Z:JP:Communication Services</th>\n",
       "      <td>2019-10-01</td>\n",
       "      <td>None</td>\n",
       "      <td>2019-10-01</td>\n",
       "      <td>JP</td>\n",
       "      <td>Communication Services</td>\n",
       "      <td>0.008799</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-10-01T00-00-00Z:JP:Consumer Discretionary</th>\n",
       "      <td>2019-10-01</td>\n",
       "      <td>None</td>\n",
       "      <td>2019-10-01</td>\n",
       "      <td>JP</td>\n",
       "      <td>Consumer Discretionary</td>\n",
       "      <td>0.006420</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-10-01T00-00-00Z:JP:Consumer Staples</th>\n",
       "      <td>2019-10-01</td>\n",
       "      <td>None</td>\n",
       "      <td>2019-10-01</td>\n",
       "      <td>JP</td>\n",
       "      <td>Consumer Staples</td>\n",
       "      <td>0.006296</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-10-01T00-00-00Z:JP:Financials</th>\n",
       "      <td>2019-10-01</td>\n",
       "      <td>None</td>\n",
       "      <td>2019-10-01</td>\n",
       "      <td>JP</td>\n",
       "      <td>Financials</td>\n",
       "      <td>0.009260</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-10-01T00-00-00Z:JP:Health Care</th>\n",
       "      <td>2019-10-01</td>\n",
       "      <td>None</td>\n",
       "      <td>2019-10-01</td>\n",
       "      <td>JP</td>\n",
       "      <td>Health Care</td>\n",
       "      <td>-0.000396</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-10-01T00-00-00Z:JP:Industrials</th>\n",
       "      <td>2019-10-01</td>\n",
       "      <td>None</td>\n",
       "      <td>2019-10-01</td>\n",
       "      <td>JP</td>\n",
       "      <td>Industrials</td>\n",
       "      <td>0.011282</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-10-01T00-00-00Z:JP:Information Technology</th>\n",
       "      <td>2019-10-01</td>\n",
       "      <td>None</td>\n",
       "      <td>2019-10-01</td>\n",
       "      <td>JP</td>\n",
       "      <td>Information Technology</td>\n",
       "      <td>0.014326</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-10-01T00-00-00Z:JP:Materials</th>\n",
       "      <td>2019-10-01</td>\n",
       "      <td>None</td>\n",
       "      <td>2019-10-01</td>\n",
       "      <td>JP</td>\n",
       "      <td>Materials</td>\n",
       "      <td>0.016208</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-10-01T00-00-00Z:JP:Real Estate</th>\n",
       "      <td>2019-10-01</td>\n",
       "      <td>None</td>\n",
       "      <td>2019-10-01</td>\n",
       "      <td>JP</td>\n",
       "      <td>Real Estate</td>\n",
       "      <td>-0.002548</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-10-01T00-00-00Z:JP:Utilities</th>\n",
       "      <td>2019-10-01</td>\n",
       "      <td>None</td>\n",
       "      <td>2019-10-01</td>\n",
       "      <td>JP</td>\n",
       "      <td>Utilities</td>\n",
       "      <td>0.016657</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-10-01T00-00-00Z:US:Communication Services</th>\n",
       "      <td>2019-10-01</td>\n",
       "      <td>None</td>\n",
       "      <td>2019-10-01</td>\n",
       "      <td>US</td>\n",
       "      <td>Communication Services</td>\n",
       "      <td>-0.009942</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-10-01T00-00-00Z:US:Consumer Discretionary</th>\n",
       "      <td>2019-10-01</td>\n",
       "      <td>None</td>\n",
       "      <td>2019-10-01</td>\n",
       "      <td>US</td>\n",
       "      <td>Consumer Discretionary</td>\n",
       "      <td>-0.007407</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-10-01T00-00-00Z:US:Consumer Staples</th>\n",
       "      <td>2019-10-01</td>\n",
       "      <td>None</td>\n",
       "      <td>2019-10-01</td>\n",
       "      <td>US</td>\n",
       "      <td>Consumer Staples</td>\n",
       "      <td>-0.002682</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-10-01T00-00-00Z:US:Energy</th>\n",
       "      <td>2019-10-01</td>\n",
       "      <td>None</td>\n",
       "      <td>2019-10-01</td>\n",
       "      <td>US</td>\n",
       "      <td>Energy</td>\n",
       "      <td>-0.021877</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-10-01T00-00-00Z:US:Financials</th>\n",
       "      <td>2019-10-01</td>\n",
       "      <td>None</td>\n",
       "      <td>2019-10-01</td>\n",
       "      <td>US</td>\n",
       "      <td>Financials</td>\n",
       "      <td>-0.020053</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-10-01T00-00-00Z:US:Health Care</th>\n",
       "      <td>2019-10-01</td>\n",
       "      <td>None</td>\n",
       "      <td>2019-10-01</td>\n",
       "      <td>US</td>\n",
       "      <td>Health Care</td>\n",
       "      <td>-0.010794</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-10-01T00-00-00Z:US:Industrials</th>\n",
       "      <td>2019-10-01</td>\n",
       "      <td>None</td>\n",
       "      <td>2019-10-01</td>\n",
       "      <td>US</td>\n",
       "      <td>Industrials</td>\n",
       "      <td>-0.023487</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-10-01T00-00-00Z:US:Information Technology</th>\n",
       "      <td>2019-10-01</td>\n",
       "      <td>None</td>\n",
       "      <td>2019-10-01</td>\n",
       "      <td>US</td>\n",
       "      <td>Information Technology</td>\n",
       "      <td>-0.008264</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-10-01T00-00-00Z:US:Materials</th>\n",
       "      <td>2019-10-01</td>\n",
       "      <td>None</td>\n",
       "      <td>2019-10-01</td>\n",
       "      <td>US</td>\n",
       "      <td>Materials</td>\n",
       "      <td>-0.020897</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-10-01T00-00-00Z:US:Real Estate</th>\n",
       "      <td>2019-10-01</td>\n",
       "      <td>None</td>\n",
       "      <td>2019-10-01</td>\n",
       "      <td>US</td>\n",
       "      <td>Real Estate</td>\n",
       "      <td>-0.009535</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-10-01T00-00-00Z:US:Utilities</th>\n",
       "      <td>2019-10-01</td>\n",
       "      <td>None</td>\n",
       "      <td>2019-10-01</td>\n",
       "      <td>US</td>\n",
       "      <td>Utilities</td>\n",
       "      <td>-0.003567</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3474 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                     DATE       ORIG_IDS  \\\n",
       "ID                                                                         \n",
       "2016-01-29T00-00-00Z:AU:Consumer Discretionary 2016-01-29           None   \n",
       "2016-01-29T00-00-00Z:AU:Financials             2016-01-29           None   \n",
       "2016-01-29T00-00-00Z:AU:Industrials            2016-01-29           None   \n",
       "2016-01-29T00-00-00Z:AU:Materials              2016-01-29           None   \n",
       "2016-01-29T00-00-00Z:AU:Real Estate            2016-01-29           None   \n",
       "2016-01-29T00-00-00Z:CA:Communication Services 2016-01-29           None   \n",
       "2016-01-29T00-00-00Z:CA:Consumer Discretionary 2016-01-29           None   \n",
       "2016-01-29T00-00-00Z:CA:Consumer Staples       2016-01-29           None   \n",
       "2016-01-29T00-00-00Z:CA:Energy                 2016-01-29           None   \n",
       "2016-01-29T00-00-00Z:CA:Financials             2016-01-29           None   \n",
       "2016-01-29T00-00-00Z:CA:Industrials            2016-01-29           None   \n",
       "2016-01-29T00-00-00Z:CA:Materials              2016-01-29           None   \n",
       "2016-01-29T00-00-00Z:CA:Real Estate            2016-01-29           None   \n",
       "2016-01-29T00-00-00Z:CH:Financials             2016-01-29           None   \n",
       "2016-01-29T00-00-00Z:CH:Health Care            2016-01-29           None   \n",
       "2016-01-29T00-00-00Z:CH:Industrials            2016-01-29           None   \n",
       "2016-01-29T00-00-00Z:CH:Materials              2016-01-29           None   \n",
       "2016-01-29T00-00-00Z:CN:Communication Services 2016-01-29           None   \n",
       "2016-01-29T00-00-00Z:CN:Consumer Discretionary 2016-01-29           None   \n",
       "2016-01-29T00-00-00Z:CN:Consumer Staples       2016-01-29           None   \n",
       "2016-01-29T00-00-00Z:CN:Energy                 2016-01-29           None   \n",
       "2016-01-29T00-00-00Z:CN:Financials             2016-01-29           None   \n",
       "2016-01-29T00-00-00Z:CN:Health Care            2016-01-29           None   \n",
       "2016-01-29T00-00-00Z:CN:Industrials            2016-01-29           None   \n",
       "2016-01-29T00-00-00Z:CN:Information Technology 2016-01-29           None   \n",
       "2016-01-29T00-00-00Z:CN:Materials              2016-01-29           None   \n",
       "2016-01-29T00-00-00Z:CN:Real Estate            2016-01-29           None   \n",
       "2016-01-29T00-00-00Z:CN:Utilities              2016-01-29           None   \n",
       "2016-01-29T00-00-00Z:DE:Communication Services 2016-01-29           None   \n",
       "2016-01-29T00-00-00Z:DE:Consumer Discretionary 2016-01-29           None   \n",
       "...                                                   ...            ...   \n",
       "2019-10-01T00-00-00Z:GB:Materials              2019-10-01           None   \n",
       "2019-10-01T00-00-00Z:GB:Real Estate            2019-10-01           None   \n",
       "2019-10-01T00-00-00Z:HK:Industrials            2019-10-01   JS SP Equity   \n",
       "2019-10-01T00-00-00Z:HK:Real Estate            2019-10-01  HKL SP Equity   \n",
       "2019-10-01T00-00-00Z:IN:Consumer Discretionary 2019-10-01           None   \n",
       "2019-10-01T00-00-00Z:IN:Financials             2019-10-01           None   \n",
       "2019-10-01T00-00-00Z:IN:Health Care            2019-10-01           None   \n",
       "2019-10-01T00-00-00Z:IN:Industrials            2019-10-01           None   \n",
       "2019-10-01T00-00-00Z:IN:Materials              2019-10-01           None   \n",
       "2019-10-01T00-00-00Z:JP:Communication Services 2019-10-01           None   \n",
       "2019-10-01T00-00-00Z:JP:Consumer Discretionary 2019-10-01           None   \n",
       "2019-10-01T00-00-00Z:JP:Consumer Staples       2019-10-01           None   \n",
       "2019-10-01T00-00-00Z:JP:Financials             2019-10-01           None   \n",
       "2019-10-01T00-00-00Z:JP:Health Care            2019-10-01           None   \n",
       "2019-10-01T00-00-00Z:JP:Industrials            2019-10-01           None   \n",
       "2019-10-01T00-00-00Z:JP:Information Technology 2019-10-01           None   \n",
       "2019-10-01T00-00-00Z:JP:Materials              2019-10-01           None   \n",
       "2019-10-01T00-00-00Z:JP:Real Estate            2019-10-01           None   \n",
       "2019-10-01T00-00-00Z:JP:Utilities              2019-10-01           None   \n",
       "2019-10-01T00-00-00Z:US:Communication Services 2019-10-01           None   \n",
       "2019-10-01T00-00-00Z:US:Consumer Discretionary 2019-10-01           None   \n",
       "2019-10-01T00-00-00Z:US:Consumer Staples       2019-10-01           None   \n",
       "2019-10-01T00-00-00Z:US:Energy                 2019-10-01           None   \n",
       "2019-10-01T00-00-00Z:US:Financials             2019-10-01           None   \n",
       "2019-10-01T00-00-00Z:US:Health Care            2019-10-01           None   \n",
       "2019-10-01T00-00-00Z:US:Industrials            2019-10-01           None   \n",
       "2019-10-01T00-00-00Z:US:Information Technology 2019-10-01           None   \n",
       "2019-10-01T00-00-00Z:US:Materials              2019-10-01           None   \n",
       "2019-10-01T00-00-00Z:US:Real Estate            2019-10-01           None   \n",
       "2019-10-01T00-00-00Z:US:Utilities              2019-10-01           None   \n",
       "\n",
       "                                               DAY_TO_DAY_TOTAL_RETURN(PER=PER.M,FILL=FILL.PREV,DATES=RANGE(2016-01-01,2019-10-01)).DATE  \\\n",
       "ID                                                                                                                                         \n",
       "2016-01-29T00-00-00Z:AU:Consumer Discretionary                                         2016-01-29                                          \n",
       "2016-01-29T00-00-00Z:AU:Financials                                                     2016-01-29                                          \n",
       "2016-01-29T00-00-00Z:AU:Industrials                                                    2016-01-29                                          \n",
       "2016-01-29T00-00-00Z:AU:Materials                                                      2016-01-29                                          \n",
       "2016-01-29T00-00-00Z:AU:Real Estate                                                    2016-01-29                                          \n",
       "2016-01-29T00-00-00Z:CA:Communication Services                                         2016-01-29                                          \n",
       "2016-01-29T00-00-00Z:CA:Consumer Discretionary                                         2016-01-29                                          \n",
       "2016-01-29T00-00-00Z:CA:Consumer Staples                                               2016-01-29                                          \n",
       "2016-01-29T00-00-00Z:CA:Energy                                                         2016-01-29                                          \n",
       "2016-01-29T00-00-00Z:CA:Financials                                                     2016-01-29                                          \n",
       "2016-01-29T00-00-00Z:CA:Industrials                                                    2016-01-29                                          \n",
       "2016-01-29T00-00-00Z:CA:Materials                                                      2016-01-29                                          \n",
       "2016-01-29T00-00-00Z:CA:Real Estate                                                    2016-01-29                                          \n",
       "2016-01-29T00-00-00Z:CH:Financials                                                     2016-01-29                                          \n",
       "2016-01-29T00-00-00Z:CH:Health Care                                                    2016-01-29                                          \n",
       "2016-01-29T00-00-00Z:CH:Industrials                                                    2016-01-29                                          \n",
       "2016-01-29T00-00-00Z:CH:Materials                                                      2016-01-29                                          \n",
       "2016-01-29T00-00-00Z:CN:Communication Services                                         2016-01-29                                          \n",
       "2016-01-29T00-00-00Z:CN:Consumer Discretionary                                         2016-01-29                                          \n",
       "2016-01-29T00-00-00Z:CN:Consumer Staples                                               2016-01-29                                          \n",
       "2016-01-29T00-00-00Z:CN:Energy                                                         2016-01-29                                          \n",
       "2016-01-29T00-00-00Z:CN:Financials                                                     2016-01-29                                          \n",
       "2016-01-29T00-00-00Z:CN:Health Care                                                    2016-01-29                                          \n",
       "2016-01-29T00-00-00Z:CN:Industrials                                                    2016-01-29                                          \n",
       "2016-01-29T00-00-00Z:CN:Information Technology                                         2016-01-29                                          \n",
       "2016-01-29T00-00-00Z:CN:Materials                                                      2016-01-29                                          \n",
       "2016-01-29T00-00-00Z:CN:Real Estate                                                    2016-01-29                                          \n",
       "2016-01-29T00-00-00Z:CN:Utilities                                                      2016-01-29                                          \n",
       "2016-01-29T00-00-00Z:DE:Communication Services                                         2016-01-29                                          \n",
       "2016-01-29T00-00-00Z:DE:Consumer Discretionary                                         2016-01-29                                          \n",
       "...                                                                                           ...                                          \n",
       "2019-10-01T00-00-00Z:GB:Materials                                                      2019-10-01                                          \n",
       "2019-10-01T00-00-00Z:GB:Real Estate                                                    2019-10-01                                          \n",
       "2019-10-01T00-00-00Z:HK:Industrials                                                    2019-10-01                                          \n",
       "2019-10-01T00-00-00Z:HK:Real Estate                                                    2019-10-01                                          \n",
       "2019-10-01T00-00-00Z:IN:Consumer Discretionary                                         2019-10-01                                          \n",
       "2019-10-01T00-00-00Z:IN:Financials                                                     2019-10-01                                          \n",
       "2019-10-01T00-00-00Z:IN:Health Care                                                    2019-10-01                                          \n",
       "2019-10-01T00-00-00Z:IN:Industrials                                                    2019-10-01                                          \n",
       "2019-10-01T00-00-00Z:IN:Materials                                                      2019-10-01                                          \n",
       "2019-10-01T00-00-00Z:JP:Communication Services                                         2019-10-01                                          \n",
       "2019-10-01T00-00-00Z:JP:Consumer Discretionary                                         2019-10-01                                          \n",
       "2019-10-01T00-00-00Z:JP:Consumer Staples                                               2019-10-01                                          \n",
       "2019-10-01T00-00-00Z:JP:Financials                                                     2019-10-01                                          \n",
       "2019-10-01T00-00-00Z:JP:Health Care                                                    2019-10-01                                          \n",
       "2019-10-01T00-00-00Z:JP:Industrials                                                    2019-10-01                                          \n",
       "2019-10-01T00-00-00Z:JP:Information Technology                                         2019-10-01                                          \n",
       "2019-10-01T00-00-00Z:JP:Materials                                                      2019-10-01                                          \n",
       "2019-10-01T00-00-00Z:JP:Real Estate                                                    2019-10-01                                          \n",
       "2019-10-01T00-00-00Z:JP:Utilities                                                      2019-10-01                                          \n",
       "2019-10-01T00-00-00Z:US:Communication Services                                         2019-10-01                                          \n",
       "2019-10-01T00-00-00Z:US:Consumer Discretionary                                         2019-10-01                                          \n",
       "2019-10-01T00-00-00Z:US:Consumer Staples                                               2019-10-01                                          \n",
       "2019-10-01T00-00-00Z:US:Energy                                                         2019-10-01                                          \n",
       "2019-10-01T00-00-00Z:US:Financials                                                     2019-10-01                                          \n",
       "2019-10-01T00-00-00Z:US:Health Care                                                    2019-10-01                                          \n",
       "2019-10-01T00-00-00Z:US:Industrials                                                    2019-10-01                                          \n",
       "2019-10-01T00-00-00Z:US:Information Technology                                         2019-10-01                                          \n",
       "2019-10-01T00-00-00Z:US:Materials                                                      2019-10-01                                          \n",
       "2019-10-01T00-00-00Z:US:Real Estate                                                    2019-10-01                                          \n",
       "2019-10-01T00-00-00Z:US:Utilities                                                      2019-10-01                                          \n",
       "\n",
       "                                               CNTRY_OF_RISK()  \\\n",
       "ID                                                               \n",
       "2016-01-29T00-00-00Z:AU:Consumer Discretionary              AU   \n",
       "2016-01-29T00-00-00Z:AU:Financials                          AU   \n",
       "2016-01-29T00-00-00Z:AU:Industrials                         AU   \n",
       "2016-01-29T00-00-00Z:AU:Materials                           AU   \n",
       "2016-01-29T00-00-00Z:AU:Real Estate                         AU   \n",
       "2016-01-29T00-00-00Z:CA:Communication Services              CA   \n",
       "2016-01-29T00-00-00Z:CA:Consumer Discretionary              CA   \n",
       "2016-01-29T00-00-00Z:CA:Consumer Staples                    CA   \n",
       "2016-01-29T00-00-00Z:CA:Energy                              CA   \n",
       "2016-01-29T00-00-00Z:CA:Financials                          CA   \n",
       "2016-01-29T00-00-00Z:CA:Industrials                         CA   \n",
       "2016-01-29T00-00-00Z:CA:Materials                           CA   \n",
       "2016-01-29T00-00-00Z:CA:Real Estate                         CA   \n",
       "2016-01-29T00-00-00Z:CH:Financials                          CH   \n",
       "2016-01-29T00-00-00Z:CH:Health Care                         CH   \n",
       "2016-01-29T00-00-00Z:CH:Industrials                         CH   \n",
       "2016-01-29T00-00-00Z:CH:Materials                           CH   \n",
       "2016-01-29T00-00-00Z:CN:Communication Services              CN   \n",
       "2016-01-29T00-00-00Z:CN:Consumer Discretionary              CN   \n",
       "2016-01-29T00-00-00Z:CN:Consumer Staples                    CN   \n",
       "2016-01-29T00-00-00Z:CN:Energy                              CN   \n",
       "2016-01-29T00-00-00Z:CN:Financials                          CN   \n",
       "2016-01-29T00-00-00Z:CN:Health Care                         CN   \n",
       "2016-01-29T00-00-00Z:CN:Industrials                         CN   \n",
       "2016-01-29T00-00-00Z:CN:Information Technology              CN   \n",
       "2016-01-29T00-00-00Z:CN:Materials                           CN   \n",
       "2016-01-29T00-00-00Z:CN:Real Estate                         CN   \n",
       "2016-01-29T00-00-00Z:CN:Utilities                           CN   \n",
       "2016-01-29T00-00-00Z:DE:Communication Services              DE   \n",
       "2016-01-29T00-00-00Z:DE:Consumer Discretionary              DE   \n",
       "...                                                        ...   \n",
       "2019-10-01T00-00-00Z:GB:Materials                           GB   \n",
       "2019-10-01T00-00-00Z:GB:Real Estate                         GB   \n",
       "2019-10-01T00-00-00Z:HK:Industrials                         HK   \n",
       "2019-10-01T00-00-00Z:HK:Real Estate                         HK   \n",
       "2019-10-01T00-00-00Z:IN:Consumer Discretionary              IN   \n",
       "2019-10-01T00-00-00Z:IN:Financials                          IN   \n",
       "2019-10-01T00-00-00Z:IN:Health Care                         IN   \n",
       "2019-10-01T00-00-00Z:IN:Industrials                         IN   \n",
       "2019-10-01T00-00-00Z:IN:Materials                           IN   \n",
       "2019-10-01T00-00-00Z:JP:Communication Services              JP   \n",
       "2019-10-01T00-00-00Z:JP:Consumer Discretionary              JP   \n",
       "2019-10-01T00-00-00Z:JP:Consumer Staples                    JP   \n",
       "2019-10-01T00-00-00Z:JP:Financials                          JP   \n",
       "2019-10-01T00-00-00Z:JP:Health Care                         JP   \n",
       "2019-10-01T00-00-00Z:JP:Industrials                         JP   \n",
       "2019-10-01T00-00-00Z:JP:Information Technology              JP   \n",
       "2019-10-01T00-00-00Z:JP:Materials                           JP   \n",
       "2019-10-01T00-00-00Z:JP:Real Estate                         JP   \n",
       "2019-10-01T00-00-00Z:JP:Utilities                           JP   \n",
       "2019-10-01T00-00-00Z:US:Communication Services              US   \n",
       "2019-10-01T00-00-00Z:US:Consumer Discretionary              US   \n",
       "2019-10-01T00-00-00Z:US:Consumer Staples                    US   \n",
       "2019-10-01T00-00-00Z:US:Energy                              US   \n",
       "2019-10-01T00-00-00Z:US:Financials                          US   \n",
       "2019-10-01T00-00-00Z:US:Health Care                         US   \n",
       "2019-10-01T00-00-00Z:US:Industrials                         US   \n",
       "2019-10-01T00-00-00Z:US:Information Technology              US   \n",
       "2019-10-01T00-00-00Z:US:Materials                           US   \n",
       "2019-10-01T00-00-00Z:US:Real Estate                         US   \n",
       "2019-10-01T00-00-00Z:US:Utilities                           US   \n",
       "\n",
       "                                               CLASSIFICATION_NAME(CLASSIFICATION_SCHEME.GICS,CLASSIFICATION_LEVEL.1)  \\\n",
       "ID                                                                                                                      \n",
       "2016-01-29T00-00-00Z:AU:Consumer Discretionary                             Consumer Discretionary                       \n",
       "2016-01-29T00-00-00Z:AU:Financials                                                     Financials                       \n",
       "2016-01-29T00-00-00Z:AU:Industrials                                                   Industrials                       \n",
       "2016-01-29T00-00-00Z:AU:Materials                                                       Materials                       \n",
       "2016-01-29T00-00-00Z:AU:Real Estate                                                   Real Estate                       \n",
       "2016-01-29T00-00-00Z:CA:Communication Services                             Communication Services                       \n",
       "2016-01-29T00-00-00Z:CA:Consumer Discretionary                             Consumer Discretionary                       \n",
       "2016-01-29T00-00-00Z:CA:Consumer Staples                                         Consumer Staples                       \n",
       "2016-01-29T00-00-00Z:CA:Energy                                                             Energy                       \n",
       "2016-01-29T00-00-00Z:CA:Financials                                                     Financials                       \n",
       "2016-01-29T00-00-00Z:CA:Industrials                                                   Industrials                       \n",
       "2016-01-29T00-00-00Z:CA:Materials                                                       Materials                       \n",
       "2016-01-29T00-00-00Z:CA:Real Estate                                                   Real Estate                       \n",
       "2016-01-29T00-00-00Z:CH:Financials                                                     Financials                       \n",
       "2016-01-29T00-00-00Z:CH:Health Care                                                   Health Care                       \n",
       "2016-01-29T00-00-00Z:CH:Industrials                                                   Industrials                       \n",
       "2016-01-29T00-00-00Z:CH:Materials                                                       Materials                       \n",
       "2016-01-29T00-00-00Z:CN:Communication Services                             Communication Services                       \n",
       "2016-01-29T00-00-00Z:CN:Consumer Discretionary                             Consumer Discretionary                       \n",
       "2016-01-29T00-00-00Z:CN:Consumer Staples                                         Consumer Staples                       \n",
       "2016-01-29T00-00-00Z:CN:Energy                                                             Energy                       \n",
       "2016-01-29T00-00-00Z:CN:Financials                                                     Financials                       \n",
       "2016-01-29T00-00-00Z:CN:Health Care                                                   Health Care                       \n",
       "2016-01-29T00-00-00Z:CN:Industrials                                                   Industrials                       \n",
       "2016-01-29T00-00-00Z:CN:Information Technology                             Information Technology                       \n",
       "2016-01-29T00-00-00Z:CN:Materials                                                       Materials                       \n",
       "2016-01-29T00-00-00Z:CN:Real Estate                                                   Real Estate                       \n",
       "2016-01-29T00-00-00Z:CN:Utilities                                                       Utilities                       \n",
       "2016-01-29T00-00-00Z:DE:Communication Services                             Communication Services                       \n",
       "2016-01-29T00-00-00Z:DE:Consumer Discretionary                             Consumer Discretionary                       \n",
       "...                                                                                           ...                       \n",
       "2019-10-01T00-00-00Z:GB:Materials                                                       Materials                       \n",
       "2019-10-01T00-00-00Z:GB:Real Estate                                                   Real Estate                       \n",
       "2019-10-01T00-00-00Z:HK:Industrials                                                   Industrials                       \n",
       "2019-10-01T00-00-00Z:HK:Real Estate                                                   Real Estate                       \n",
       "2019-10-01T00-00-00Z:IN:Consumer Discretionary                             Consumer Discretionary                       \n",
       "2019-10-01T00-00-00Z:IN:Financials                                                     Financials                       \n",
       "2019-10-01T00-00-00Z:IN:Health Care                                                   Health Care                       \n",
       "2019-10-01T00-00-00Z:IN:Industrials                                                   Industrials                       \n",
       "2019-10-01T00-00-00Z:IN:Materials                                                       Materials                       \n",
       "2019-10-01T00-00-00Z:JP:Communication Services                             Communication Services                       \n",
       "2019-10-01T00-00-00Z:JP:Consumer Discretionary                             Consumer Discretionary                       \n",
       "2019-10-01T00-00-00Z:JP:Consumer Staples                                         Consumer Staples                       \n",
       "2019-10-01T00-00-00Z:JP:Financials                                                     Financials                       \n",
       "2019-10-01T00-00-00Z:JP:Health Care                                                   Health Care                       \n",
       "2019-10-01T00-00-00Z:JP:Industrials                                                   Industrials                       \n",
       "2019-10-01T00-00-00Z:JP:Information Technology                             Information Technology                       \n",
       "2019-10-01T00-00-00Z:JP:Materials                                                       Materials                       \n",
       "2019-10-01T00-00-00Z:JP:Real Estate                                                   Real Estate                       \n",
       "2019-10-01T00-00-00Z:JP:Utilities                                                       Utilities                       \n",
       "2019-10-01T00-00-00Z:US:Communication Services                             Communication Services                       \n",
       "2019-10-01T00-00-00Z:US:Consumer Discretionary                             Consumer Discretionary                       \n",
       "2019-10-01T00-00-00Z:US:Consumer Staples                                         Consumer Staples                       \n",
       "2019-10-01T00-00-00Z:US:Energy                                                             Energy                       \n",
       "2019-10-01T00-00-00Z:US:Financials                                                     Financials                       \n",
       "2019-10-01T00-00-00Z:US:Health Care                                                   Health Care                       \n",
       "2019-10-01T00-00-00Z:US:Industrials                                                   Industrials                       \n",
       "2019-10-01T00-00-00Z:US:Information Technology                             Information Technology                       \n",
       "2019-10-01T00-00-00Z:US:Materials                                                       Materials                       \n",
       "2019-10-01T00-00-00Z:US:Real Estate                                                   Real Estate                       \n",
       "2019-10-01T00-00-00Z:US:Utilities                                                       Utilities                       \n",
       "\n",
       "                                                wavg_ret  \n",
       "ID                                                        \n",
       "2016-01-29T00-00-00Z:AU:Consumer Discretionary  0.007244  \n",
       "2016-01-29T00-00-00Z:AU:Financials             -0.091320  \n",
       "2016-01-29T00-00-00Z:AU:Industrials            -0.064083  \n",
       "2016-01-29T00-00-00Z:AU:Materials              -0.115078  \n",
       "2016-01-29T00-00-00Z:AU:Real Estate             0.008328  \n",
       "2016-01-29T00-00-00Z:CA:Communication Services  0.019575  \n",
       "2016-01-29T00-00-00Z:CA:Consumer Discretionary -0.091691  \n",
       "2016-01-29T00-00-00Z:CA:Consumer Staples        0.012993  \n",
       "2016-01-29T00-00-00Z:CA:Energy                  0.002211  \n",
       "2016-01-29T00-00-00Z:CA:Financials             -0.011261  \n",
       "2016-01-29T00-00-00Z:CA:Industrials            -0.023726  \n",
       "2016-01-29T00-00-00Z:CA:Materials               0.024905  \n",
       "2016-01-29T00-00-00Z:CA:Real Estate             0.023793  \n",
       "2016-01-29T00-00-00Z:CH:Financials             -0.108812  \n",
       "2016-01-29T00-00-00Z:CH:Health Care            -0.062785  \n",
       "2016-01-29T00-00-00Z:CH:Industrials            -0.025967  \n",
       "2016-01-29T00-00-00Z:CH:Materials              -0.047310  \n",
       "2016-01-29T00-00-00Z:CN:Communication Services -0.080530  \n",
       "2016-01-29T00-00-00Z:CN:Consumer Discretionary -0.193981  \n",
       "2016-01-29T00-00-00Z:CN:Consumer Staples       -0.133882  \n",
       "2016-01-29T00-00-00Z:CN:Energy                 -0.096377  \n",
       "2016-01-29T00-00-00Z:CN:Financials             -0.160682  \n",
       "2016-01-29T00-00-00Z:CN:Health Care            -0.193518  \n",
       "2016-01-29T00-00-00Z:CN:Industrials            -0.234078  \n",
       "2016-01-29T00-00-00Z:CN:Information Technology -0.233428  \n",
       "2016-01-29T00-00-00Z:CN:Materials              -0.207647  \n",
       "2016-01-29T00-00-00Z:CN:Real Estate            -0.223190  \n",
       "2016-01-29T00-00-00Z:CN:Utilities              -0.191128  \n",
       "2016-01-29T00-00-00Z:DE:Communication Services -0.040023  \n",
       "2016-01-29T00-00-00Z:DE:Consumer Discretionary -0.123786  \n",
       "...                                                  ...  \n",
       "2019-10-01T00-00-00Z:GB:Materials              -0.020263  \n",
       "2019-10-01T00-00-00Z:GB:Real Estate            -0.004485  \n",
       "2019-10-01T00-00-00Z:HK:Industrials             0.002677  \n",
       "2019-10-01T00-00-00Z:HK:Real Estate            -0.008897  \n",
       "2019-10-01T00-00-00Z:IN:Consumer Discretionary -0.004839  \n",
       "2019-10-01T00-00-00Z:IN:Financials             -0.006344  \n",
       "2019-10-01T00-00-00Z:IN:Health Care            -0.010809  \n",
       "2019-10-01T00-00-00Z:IN:Industrials            -0.006899  \n",
       "2019-10-01T00-00-00Z:IN:Materials              -0.013370  \n",
       "2019-10-01T00-00-00Z:JP:Communication Services  0.008799  \n",
       "2019-10-01T00-00-00Z:JP:Consumer Discretionary  0.006420  \n",
       "2019-10-01T00-00-00Z:JP:Consumer Staples        0.006296  \n",
       "2019-10-01T00-00-00Z:JP:Financials              0.009260  \n",
       "2019-10-01T00-00-00Z:JP:Health Care            -0.000396  \n",
       "2019-10-01T00-00-00Z:JP:Industrials             0.011282  \n",
       "2019-10-01T00-00-00Z:JP:Information Technology  0.014326  \n",
       "2019-10-01T00-00-00Z:JP:Materials               0.016208  \n",
       "2019-10-01T00-00-00Z:JP:Real Estate            -0.002548  \n",
       "2019-10-01T00-00-00Z:JP:Utilities               0.016657  \n",
       "2019-10-01T00-00-00Z:US:Communication Services -0.009942  \n",
       "2019-10-01T00-00-00Z:US:Consumer Discretionary -0.007407  \n",
       "2019-10-01T00-00-00Z:US:Consumer Staples       -0.002682  \n",
       "2019-10-01T00-00-00Z:US:Energy                 -0.021877  \n",
       "2019-10-01T00-00-00Z:US:Financials             -0.020053  \n",
       "2019-10-01T00-00-00Z:US:Health Care            -0.010794  \n",
       "2019-10-01T00-00-00Z:US:Industrials            -0.023487  \n",
       "2019-10-01T00-00-00Z:US:Information Technology -0.008264  \n",
       "2019-10-01T00-00-00Z:US:Materials              -0.020897  \n",
       "2019-10-01T00-00-00Z:US:Real Estate            -0.009535  \n",
       "2019-10-01T00-00-00Z:US:Utilities              -0.003567  \n",
       "\n",
       "[3474 rows x 6 columns]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "returns_df = response.get('wavg_ret').df()\n",
    "returns_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Charts and other widgets"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>CLASSIFICATION_NAME(CLASSIFICATION_SCHEME.GICS,CLASSIFICATION_LEVEL.1)</th>\n",
       "      <th>Consumer Discretionary</th>\n",
       "      <th>Financials</th>\n",
       "      <th>Industrials</th>\n",
       "      <th>Materials</th>\n",
       "      <th>Real Estate</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>DATE</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2016-01-29</th>\n",
       "      <td>0.007244</td>\n",
       "      <td>-0.091320</td>\n",
       "      <td>-0.064083</td>\n",
       "      <td>-0.115078</td>\n",
       "      <td>0.008328</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-02-29</th>\n",
       "      <td>-0.018357</td>\n",
       "      <td>-0.067592</td>\n",
       "      <td>0.135358</td>\n",
       "      <td>0.101793</td>\n",
       "      <td>0.029877</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-03-31</th>\n",
       "      <td>0.057408</td>\n",
       "      <td>0.061872</td>\n",
       "      <td>0.033044</td>\n",
       "      <td>0.072711</td>\n",
       "      <td>0.027869</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-04-29</th>\n",
       "      <td>0.009275</td>\n",
       "      <td>0.014900</td>\n",
       "      <td>0.019643</td>\n",
       "      <td>0.186648</td>\n",
       "      <td>0.034043</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-05-31</th>\n",
       "      <td>-0.002294</td>\n",
       "      <td>0.064128</td>\n",
       "      <td>0.019498</td>\n",
       "      <td>-0.096497</td>\n",
       "      <td>0.020444</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "CLASSIFICATION_NAME(CLASSIFICATION_SCHEME.GICS,CLASSIFICATION_LEVEL.1)  Consumer Discretionary  \\\n",
       "DATE                                                                                             \n",
       "2016-01-29                                                                            0.007244   \n",
       "2016-02-29                                                                           -0.018357   \n",
       "2016-03-31                                                                            0.057408   \n",
       "2016-04-29                                                                            0.009275   \n",
       "2016-05-31                                                                           -0.002294   \n",
       "\n",
       "CLASSIFICATION_NAME(CLASSIFICATION_SCHEME.GICS,CLASSIFICATION_LEVEL.1)  Financials  \\\n",
       "DATE                                                                                 \n",
       "2016-01-29                                                               -0.091320   \n",
       "2016-02-29                                                               -0.067592   \n",
       "2016-03-31                                                                0.061872   \n",
       "2016-04-29                                                                0.014900   \n",
       "2016-05-31                                                                0.064128   \n",
       "\n",
       "CLASSIFICATION_NAME(CLASSIFICATION_SCHEME.GICS,CLASSIFICATION_LEVEL.1)  Industrials  \\\n",
       "DATE                                                                                  \n",
       "2016-01-29                                                                -0.064083   \n",
       "2016-02-29                                                                 0.135358   \n",
       "2016-03-31                                                                 0.033044   \n",
       "2016-04-29                                                                 0.019643   \n",
       "2016-05-31                                                                 0.019498   \n",
       "\n",
       "CLASSIFICATION_NAME(CLASSIFICATION_SCHEME.GICS,CLASSIFICATION_LEVEL.1)  Materials  \\\n",
       "DATE                                                                                \n",
       "2016-01-29                                                              -0.115078   \n",
       "2016-02-29                                                               0.101793   \n",
       "2016-03-31                                                               0.072711   \n",
       "2016-04-29                                                               0.186648   \n",
       "2016-05-31                                                              -0.096497   \n",
       "\n",
       "CLASSIFICATION_NAME(CLASSIFICATION_SCHEME.GICS,CLASSIFICATION_LEVEL.1)  Real Estate  \n",
       "DATE                                                                                 \n",
       "2016-01-29                                                                 0.008328  \n",
       "2016-02-29                                                                 0.029877  \n",
       "2016-03-31                                                                 0.027869  \n",
       "2016-04-29                                                                 0.034043  \n",
       "2016-05-31                                                                 0.020444  "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Create dictionary of countries >> Total return by sector\n",
    "dict_countries = {}\n",
    "for country in returns_df['CNTRY_OF_RISK()'].unique():\n",
    "    temp_df = returns_df[returns_df['CNTRY_OF_RISK()']==country]\n",
    "    dict_countries[country] = temp_df.pivot(index = 'DATE',\n",
    "                                            columns = sector_string, \n",
    "                                            values = 'wavg_ret')\n",
    "#Visualize first rows for Australia\n",
    "dict_countries['AU'].head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Create user defined function to calculate cumulative return between two input dates\n",
    "def return_cumprod_returns_by_country(start,end):\n",
    "    df = pd.concat([((1+dict_countries[x].dropna().loc[start:end]).cumprod()-1).iloc[-1] for x in list_countries],axis=1,keys=list_countries)\n",
    "    return df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Creating Heatmap figure and mark"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "45d2a1c5f0e741f2a92cd27434c48904",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Figure(axes=[Axis(grid_lines='none', scale=OrdinalScale(), side='top'), Axis(grid_lines='none', orientation='v…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "#Prepare scale and data for the heatmap\n",
    "col_sc = bqp.ColorScale(min=-0.2,max=0.4,mid=0.00)\n",
    "\n",
    "#calculate initial cumulative return dataframe with start,end dates\n",
    "df = return_cumprod_returns_by_country('2017-01-01','2019-10-01')\n",
    "\n",
    "#Create the heatmap bqplot.mark and feed the above dataframe as wakeup data\n",
    "gridheatmap = bqp.GridHeatMap(color=df.values ,\n",
    "                              scales={'color':col_sc},\n",
    "                              row = df.index,\n",
    "                              column = df.columns,\n",
    "                              tooltip=bqp.Tooltip(fields=['row','column','color'],\n",
    "                                                  show_labels=False,\n",
    "                                                  formats=['','','%.2f']))\n",
    "\n",
    "#Add additional interactions and style features to the bqplot.mark heatmap\n",
    "gridheatmap.interactions = {'click':'select', 'hover':'tooltip'}\n",
    "gridheatmap.selected_style = {'opacity': '0.4'} \n",
    "gridheatmap.unselected_style={'opacity': '1.0'}\n",
    "\n",
    "#Assemble scales, axis together for the heatmap\n",
    "x_heatmap_scale,y_heatmap_scale = bqp.OrdinalScale(reverse=False),bqp.OrdinalScale(reverse=True)\n",
    "x_heatmap_axis = bqp.Axis(scale=x_heatmap_scale,grid_lines='none',side='top')\n",
    "y_heatmap_axis = bqp.Axis(scale=y_heatmap_scale,orientation='vertical',grid_lines='none')\n",
    "color_axis = bqp.ColorAxis(scale=col_sc, num_ticks=6)\n",
    "\n",
    "#Create X/Y Axis for the heatmap\n",
    "heatmap_tile_labels = bqp.Label(x=df.columns,y=df.index,scales={'x': x_heatmap_scale, 'y': y_heatmap_scale},align='middle',colors=['black'])\n",
    "\n",
    "#Assemble heatmap mark into a bqplot figure\n",
    "heatmap_figure = bqp.Figure(title = 'Return matrix',marks=[gridheatmap,heatmap_tile_labels])\n",
    "heatmap_figure.axes = [x_heatmap_axis,\n",
    "                        y_heatmap_axis,\n",
    "                        color_axis]\n",
    "heatmap_figure.fig_margin= {'top':80, 'bottom':60, 'left':160, 'right':60}\n",
    "heatmap_figure"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "7e29e3e28c5f474c8a00a2a2b8ec0d85",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Dropdown(index=5, options=('PMI', 'CPI', 'Exports', 'NEER', 'GDP', 'Trade_Balance'), value='Trade_Balance')"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "selector = ipw.Dropdown(options=dict_eco_fields.keys(),value='Trade_Balance')\n",
    "selector"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Creating Callback functions for interactivity logic"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "#User defined function to calculate cumulative return and display proper return table to the heatmap\n",
    "def on_index_select(evt):\n",
    "    new = evt\n",
    "    dates = (dict_dfs[selector.value].iloc[new['new']['selected']].index[0].strftime('%Y-%m-%d'),\n",
    "             dict_dfs[selector.value].iloc[new['new']['selected']].index[-1].strftime('%Y-%m-%d'))\n",
    "    #Calculate new cumulative returns\n",
    "    temp_df = return_cumprod_returns_by_country(dates[0],dates[1])\n",
    "    gridheatmap.color = temp_df\n",
    "    gridheatmap.row = temp_df.index\n",
    "    gridheatmap.column = temp_df.columns\n",
    "    heatmap_figure.title = 'Total Return {dates}'.format(dates=str(dates))\n",
    "    #Push 14 top country/sector in a table\n",
    "    grid_data = pd.DataFrame(temp_df.stack()).reset_index().nlargest(14,0).set_index(['level_1','level_0']).rename_axis({0:'TR'},axis=1).reset_index()\n",
    "    grid_data['TR'] = round(grid_data['TR']*100,2)\n",
    "    DGrid_top_14.data=grid_data\n",
    "\n",
    "#Attach the callback function to the ECO line mark\n",
    "line_mark.observe(on_index_select)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "#User defined function for the ECO dropdown to feed the appropriate dataset to the Line chart\n",
    "def callback_change_data(dd):\n",
    "    line_mark.unobserve_all()\n",
    "    line_mark.x = dict_dfs[selector.value].index\n",
    "    line_mark.y = dict_dfs[selector.value].values.T\n",
    "    bqplot_figure.title = selector.value\n",
    "    line_mark.observe(on_index_select)\n",
    "\n",
    "#Attach the callback function to the ECO dropdown\n",
    "selector.observe(callback_change_data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "e8410a2127f941a48013ae2abdd2e9d8",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Button(description='Retrieve Equities', style=ButtonStyle())"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "#Button for Equity transparency request\n",
    "button_equities = ipw.Button(description = 'Retrieve Equities')\n",
    "button_equities"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "#User defined function to retrieve the company transparency for the selected country/sector tile from the heatmap, upon button click\n",
    "def callback_bql_company(btn):\n",
    "    selected_sector = gridheatmap.row[gridheatmap.selected[0][0]]\n",
    "    selected_country = gridheatmap.column[gridheatmap.selected[0][1]]\n",
    "    label_status.value = 'Requesting {sector} & {country}'.format(sector=selected_sector,country=selected_country)\n",
    "    dates = (dict_dfs[selector.value].iloc[line_mark.selected].index[0].strftime('%Y-%m-%d'),dict_dfs[selector.value].iloc[line_mark.selected].index[-1].strftime('%Y-%m-%d'))\n",
    "    univ = bq.univ.members('BWORLD Index',DATES=dates[1])\n",
    "    mkt_cap = bq.data.cur_mkt_cap(CURRENCY='USD',fill='prev',DATES=dates[1])\n",
    "    filtered_univ = bq.univ.filter(bq.univ.filter(univ,(bq.data.gics_sector_name()==selected_sector).and_(bq.data.cntry_of_risk()==selected_country)),mkt_cap.grouprank()<=10)\n",
    "    total_return = bq.data.total_return(calc_interval=bq.func.range(dates[0],dates[1]))\n",
    "    name = bq.data.name()\n",
    "    request = bql.Request(filtered_univ,{'name':name,'TR':total_return,'MCap':mkt_cap})\n",
    "    response = bq.execute(request)\n",
    "    df = pd.concat([x.df()[x.name] for x in response],axis=1).reset_index()\n",
    "    df_to_push = df.set_index('ID').sort_values('MCap',ascending=False).reset_index()\n",
    "    df_to_push['TR'] = round(df_to_push['TR']*100,2)\n",
    "    df_to_push['MCap'] = round(df_to_push['MCap']/1e9,2)\n",
    "    DGrid_equities.data = df_to_push\n",
    "    label_status.value = 'Request Done'\n",
    "\n",
    "#Attach the callback function to the Equity request button\n",
    "button_equities.on_click(callback_bql_company)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Creating Datagrid to display data tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "58b4f493d9f64c6aaa38554b32ad492b",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "DataGrid(column_defs=[{'width': 80, 'field': 'level_1', 'headerName': 'Country', 'filter': 'text'}, {'width': …"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "488aaf7e5ba74875bf957309bfdb4202",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "DataGrid(column_defs=[{'width': 100, 'field': 'ID', 'headerName': 'Ticker', 'filter': 'text'}, {'width': 80, '…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "#Create columns for both datagrids\n",
    "DGrid_top_14 = bqw.DataGrid(column_defs = [{'field': 'level_1', 'filter': 'text', 'headerName': 'Country', 'width': 80},\n",
    "                                           {'field': 'level_0', 'filter': 'text', 'headerName': 'Sector', 'width': 120},\n",
    "                                           {'field': 'TR', 'filter': 'text', 'headerName': 'TR %', 'width': 80}])\n",
    "\n",
    "DGrid_equities = bqw.DataGrid(column_defs = [{'field': 'ID', 'filter': 'text', 'headerName': 'Ticker', 'width': 100},\n",
    "                                             {'field': 'TR', 'filter': 'text', 'headerName': 'TR %', 'width': 80},\n",
    "                                             {'field': 'MCap', 'filter': 'text', 'headerName': 'MCap', 'width': 100},\n",
    "                                             {'field': 'name', 'filter': 'text', 'headerName': 'Name', 'width': 120}])\n",
    "display(DGrid_top_14,DGrid_equities)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Label status when requesting equity transparency\n",
    "label_status = ipw.Label(value='')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Title for the top 14 datagrid\n",
    "lbl = ipw.Label(value = 'Top 14 tiles')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Leveraging on HBox/VBox/GridBox to create dashboard of the above widgets"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Create Vertical and Horizontal boxes to hold the datagrid widgets\n",
    "box_top_14 = ipw.VBox([lbl,DGrid_top_14],layout=ipw.Layout(width='auto',height='auto'))\n",
    "box_equities = ipw.VBox([ipw.HBox([button_equities,label_status]),DGrid_equities],layout=ipw.Layout(width='auto',height='auto'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Layout changes for figures to fit properly into the Ipywidgets.Gridbox\n",
    "bqplot_figure.layout.width='auto'\n",
    "bqplot_figure.layout.height='auto'\n",
    "\n",
    "heatmap_figure.layout.width='auto'\n",
    "heatmap_figure.layout.height='auto'\n",
    "\n",
    "selector.layout.grid_area='selector'\n",
    "bqplot_figure.layout.grid_area='bqplot_figure'\n",
    "heatmap_figure.layout.grid_area='heatmap_figure'\n",
    "box_top_14.layout.grid_area='box_top_14'\n",
    "box_equities.layout.grid_area='box_equities'\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "0a25e45129554a60ac5a26c5d93beb42",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "GridBox(children=(Dropdown(index=5, layout=Layout(grid_area='selector'), options=('PMI', 'CPI', 'Exports', 'NE…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "#Create Gridbox to hold all the widget into a dashboard. see following link to learn more \n",
    "#https://ipywidgets.readthedocs.io/en/latest/examples/Widget%20Styling.html#The-Grid-layout\n",
    "gbox = ipw.GridBox([selector,bqplot_figure,heatmap_figure, box_top_14,box_equities],layout=ipw.Layout(grid_template_rows = '30px 500px 500px',\n",
    "                                                                                     grid_template_columns = '300px 300px 300px 400px',\n",
    "                                                                                     grid_template_areas = '''\"selector . . .\"\n",
    "                                                                                     \"bqplot_figure bqplot_figure bqplot_figure bqplot_figure\"\n",
    "                                                                                     \"heatmap_figure heatmap_figure box_top_14 box_equities\"\n",
    "                                                                                     '''))\n",
    "gbox"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (sandboxed)",
   "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.5.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}