_11_pybql hist eco bds.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Welcome to BQuant\n",
    "<img src=\"../images/workflow.png\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Sheet Navigation\n",
    "\n",
    "<a href = \"#A\">Initial Examples</a> \n",
    "\n",
    "<a href = \"#B\">Next Example</a> \n",
    "\n",
    "<a href = \"#Z\">BDH Style Data Fetch</a>  \n",
    "\n",
    "<br>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import bql\n",
    "import pandas as pd\n",
    "\n",
    "bq = bql.Service()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Fetch some data  <a name=\"A\"></a>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>DATE</th>\n",
       "      <th>CURRENCY</th>\n",
       "      <th>PX_LAST</th>\n",
       "      <th>PX_VOLUME</th>\n",
       "      <th>PX_LOW</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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>ABX CN Equity</th>\n",
       "      <td>2020-06-14</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7750275.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TSLA US Equity</th>\n",
       "      <td>2020-06-06</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7811917.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TSLA US Equity</th>\n",
       "      <td>2020-04-19</td>\n",
       "      <td>USD</td>\n",
       "      <td>753.890000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>747.660000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TSLA US Equity</th>\n",
       "      <td>2020-06-07</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7811917.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TSLA US Equity</th>\n",
       "      <td>2020-06-18</td>\n",
       "      <td>USD</td>\n",
       "      <td>1000.314575</td>\n",
       "      <td>NaN</td>\n",
       "      <td>994.470825</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ABX CN Equity</th>\n",
       "      <td>2020-04-19</td>\n",
       "      <td>CAD</td>\n",
       "      <td>34.400000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>32.550000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TSLA US Equity</th>\n",
       "      <td>2020-06-16</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>14051078.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TSLA US Equity</th>\n",
       "      <td>2020-06-01</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>15085297.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TSLA US Equity</th>\n",
       "      <td>2020-05-24</td>\n",
       "      <td>USD</td>\n",
       "      <td>816.880000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>812.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ABX CN Equity</th>\n",
       "      <td>2020-05-28</td>\n",
       "      <td>CAD</td>\n",
       "      <td>33.010000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>32.800000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ABX CN Equity</th>\n",
       "      <td>2020-05-15</td>\n",
       "      <td>CAD</td>\n",
       "      <td>39.560000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>38.330000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TSLA US Equity</th>\n",
       "      <td>2020-05-01</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>32531807.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ABX CN Equity</th>\n",
       "      <td>2020-05-31</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>12507784.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TSLA US Equity</th>\n",
       "      <td>2020-05-16</td>\n",
       "      <td>USD</td>\n",
       "      <td>799.170000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>786.552000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TSLA US Equity</th>\n",
       "      <td>2020-05-17</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10518428.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     DATE CURRENCY      PX_LAST   PX_VOLUME      PX_LOW\n",
       "ID                                                                     \n",
       "ABX CN Equity  2020-06-14     None          NaN   7750275.0         NaN\n",
       "TSLA US Equity 2020-06-06     None          NaN   7811917.0         NaN\n",
       "TSLA US Equity 2020-04-19      USD   753.890000         NaN  747.660000\n",
       "TSLA US Equity 2020-06-07     None          NaN   7811917.0         NaN\n",
       "TSLA US Equity 2020-06-18      USD  1000.314575         NaN  994.470825\n",
       "ABX CN Equity  2020-04-19      CAD    34.400000         NaN   32.550000\n",
       "TSLA US Equity 2020-06-16     None          NaN  14051078.0         NaN\n",
       "TSLA US Equity 2020-06-01     None          NaN  15085297.0         NaN\n",
       "TSLA US Equity 2020-05-24      USD   816.880000         NaN  812.000000\n",
       "ABX CN Equity  2020-05-28      CAD    33.010000         NaN   32.800000\n",
       "ABX CN Equity  2020-05-15      CAD    39.560000         NaN   38.330000\n",
       "TSLA US Equity 2020-05-01     None          NaN  32531807.0         NaN\n",
       "ABX CN Equity  2020-05-31     None          NaN  12507784.0         NaN\n",
       "TSLA US Equity 2020-05-16      USD   799.170000         NaN  786.552000\n",
       "TSLA US Equity 2020-05-17     None          NaN  10518428.0         NaN"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "request = \"\"\"\n",
    "get(PX_LAST, PX_VOLUME, PX_LOW) \n",
    "for(['TSLA US Equity', 'ABX CN Equity']) \n",
    "with(start=-60d,end=0d,fill='prev')\n",
    "\"\"\"\n",
    "\n",
    "response = bq.execute(request)\n",
    "\n",
    "# create dataframe: combined_df will 'stack' multiple fields instead doing a join\n",
    "newdf =bql.combined_df(response)    \n",
    "\n",
    "newdf.sample(15)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "from functools import reduce\n",
    "\n",
    "df_l = [x.df().reset_index() for x in list(response)]\n",
    "newdf3 = reduce(lambda left,right: pd.merge(left,right,on=['ID', 'DATE']), df_l)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>DATE</th>\n",
       "      <th>PX_LAST</th>\n",
       "      <th>PX_VOLUME</th>\n",
       "      <th>PX_LOW</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>TSLA US Equity</td>\n",
       "      <td>2020-04-19</td>\n",
       "      <td>753.89</td>\n",
       "      <td>13128237.0</td>\n",
       "      <td>747.66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>TSLA US Equity</td>\n",
       "      <td>2020-04-20</td>\n",
       "      <td>746.36</td>\n",
       "      <td>14746577.0</td>\n",
       "      <td>712.21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>TSLA US Equity</td>\n",
       "      <td>2020-04-21</td>\n",
       "      <td>686.72</td>\n",
       "      <td>20209093.0</td>\n",
       "      <td>673.79</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>TSLA US Equity</td>\n",
       "      <td>2020-04-22</td>\n",
       "      <td>732.11</td>\n",
       "      <td>14224831.0</td>\n",
       "      <td>688.71</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>TSLA US Equity</td>\n",
       "      <td>2020-04-23</td>\n",
       "      <td>705.63</td>\n",
       "      <td>13236697.0</td>\n",
       "      <td>703.13</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               ID       DATE  PX_LAST   PX_VOLUME  PX_LOW\n",
       "0  TSLA US Equity 2020-04-19   753.89  13128237.0  747.66\n",
       "1  TSLA US Equity 2020-04-20   746.36  14746577.0  712.21\n",
       "2  TSLA US Equity 2020-04-21   686.72  20209093.0  673.79\n",
       "3  TSLA US Equity 2020-04-22   732.11  14224831.0  688.71\n",
       "4  TSLA US Equity 2020-04-23   705.63  13236697.0  703.13"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "newdf3.drop(['CURRENCY_y', 'CURRENCY_x'], axis=1, inplace=True)\n",
    "newdf3.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "#newdf3.set_index('DATE', inplace=True)\n",
    "#newdf4_ = newdf3.filter(like='PX', axis=1)\n",
    "#newdf4_['DATE'] = newdf3['DATE']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "#newdf4_.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"2\" halign=\"left\">PX_LAST</th>\n",
       "      <th colspan=\"2\" halign=\"left\">PX_VOLUME</th>\n",
       "      <th colspan=\"2\" halign=\"left\">PX_LOW</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ID</th>\n",
       "      <th>ABX CN Equity</th>\n",
       "      <th>TSLA US Equity</th>\n",
       "      <th>ABX CN Equity</th>\n",
       "      <th>TSLA US Equity</th>\n",
       "      <th>ABX CN Equity</th>\n",
       "      <th>TSLA US Equity</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",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2020-04-19</th>\n",
       "      <td>34.40</td>\n",
       "      <td>753.89</td>\n",
       "      <td>8156432.0</td>\n",
       "      <td>13128237.0</td>\n",
       "      <td>32.55</td>\n",
       "      <td>747.66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-04-20</th>\n",
       "      <td>35.24</td>\n",
       "      <td>746.36</td>\n",
       "      <td>7884899.0</td>\n",
       "      <td>14746577.0</td>\n",
       "      <td>34.00</td>\n",
       "      <td>712.21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-04-21</th>\n",
       "      <td>35.16</td>\n",
       "      <td>686.72</td>\n",
       "      <td>7685389.0</td>\n",
       "      <td>20209093.0</td>\n",
       "      <td>34.19</td>\n",
       "      <td>673.79</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-04-22</th>\n",
       "      <td>37.57</td>\n",
       "      <td>732.11</td>\n",
       "      <td>8036314.0</td>\n",
       "      <td>14224831.0</td>\n",
       "      <td>36.15</td>\n",
       "      <td>688.71</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-04-23</th>\n",
       "      <td>37.65</td>\n",
       "      <td>705.63</td>\n",
       "      <td>13576251.0</td>\n",
       "      <td>13236697.0</td>\n",
       "      <td>37.40</td>\n",
       "      <td>703.13</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 PX_LAST                    PX_VOLUME                 \\\n",
       "ID         ABX CN Equity TSLA US Equity ABX CN Equity TSLA US Equity   \n",
       "DATE                                                                   \n",
       "2020-04-19         34.40         753.89     8156432.0     13128237.0   \n",
       "2020-04-20         35.24         746.36     7884899.0     14746577.0   \n",
       "2020-04-21         35.16         686.72     7685389.0     20209093.0   \n",
       "2020-04-22         37.57         732.11     8036314.0     14224831.0   \n",
       "2020-04-23         37.65         705.63    13576251.0     13236697.0   \n",
       "\n",
       "                  PX_LOW                 \n",
       "ID         ABX CN Equity TSLA US Equity  \n",
       "DATE                                     \n",
       "2020-04-19         32.55         747.66  \n",
       "2020-04-20         34.00         712.21  \n",
       "2020-04-21         34.19         673.79  \n",
       "2020-04-22         36.15         688.71  \n",
       "2020-04-23         37.40         703.13  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#newdf4 = newdf3.pivot(index='DATE', columns='ID',values = 'PX_LAST')\n",
    "newdf4 = newdf3.pivot(index='DATE', columns='ID')\n",
    "newdf4.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "#newdf4.head().style.set_caption('PX_LAST')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "6ccd8b01a3574f1fbc04b8716ebe6d0f",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox(children=(Box(children=(Figure(animation_duration=500, axes=[Axis(scale=DateScale()), Axis(label_offset='…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "import bqviz as bqv\n",
    "\n",
    "# Pass the dataframe to LinePlot and apply Bloomberg styles\n",
    "ex1 = bqv.InteractiveLinePlot(newdf4['PX_LAST'])\n",
    "\n",
    "# Display the plot\n",
    "ex1.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "from functools import reduce\n",
    "\n",
    "request = \"\"\"\n",
    "get(PX_LAST, PX_VOLUME) \n",
    "for(['EEM US Equity', 'TSLA US Equity']) \n",
    "with(start=-60d,end=0d,fill='prev')\n",
    "\"\"\"\n",
    "\n",
    "response = bq.execute(request)\n",
    "\n",
    "### create dataframe: combined_df will 'stack' multiple fields instead doing a join. Use if single field for a list of tickers.\n",
    "#newdf =bql.combined_df(response)    \n",
    "\n",
    "### create dataframe: list comprehension will staple columns together. Use for multiple fields (no history)\n",
    "#newdf = pd.concat([x.df() for x in response], axis=1)\n",
    "\n",
    "### create dataframe: use 'merge' and 'reduce' to effectively join. Use for multiple fields and hisotrical data\n",
    "df_l = [x.df().reset_index() for x in list(response)]\n",
    "newdf = reduce(lambda left,right: pd.merge(left,right,on=['ID', 'DATE']), df_l)\n",
    "\n",
    "newdf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "#Function to convert the result of a BQL query to a normal pandas dataframe\n",
    "from functools import reduce\n",
    "\n",
    "def merge_dfs(response):\n",
    "    df_l = [x.df().reset_index() for x in list(response)]\n",
    "    newdf = reduce(lambda left,right: pd.merge(left,right,on=['ID', 'DATE']), df_l)\n",
    "    return newdf\n",
    "\n",
    "data = merge_dfs(response)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>ID</th>\n",
       "      <th>DATE</th>\n",
       "      <th>CURRENCY_x</th>\n",
       "      <th>PX_LAST</th>\n",
       "      <th>PX_VOLUME</th>\n",
       "      <th>CURRENCY_y</th>\n",
       "      <th>PX_LOW</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>117</th>\n",
       "      <td>ABX CN Equity</td>\n",
       "      <td>2020-05-10</td>\n",
       "      <td>CAD</td>\n",
       "      <td>38.13</td>\n",
       "      <td>6558635.0</td>\n",
       "      <td>CAD</td>\n",
       "      <td>37.960</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78</th>\n",
       "      <td>ABX CN Equity</td>\n",
       "      <td>2020-04-01</td>\n",
       "      <td>CAD</td>\n",
       "      <td>26.88</td>\n",
       "      <td>8211981.0</td>\n",
       "      <td>CAD</td>\n",
       "      <td>25.850</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>TSLA US Equity</td>\n",
       "      <td>2020-04-16</td>\n",
       "      <td>USD</td>\n",
       "      <td>745.21</td>\n",
       "      <td>20657862.0</td>\n",
       "      <td>USD</td>\n",
       "      <td>706.715</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>TSLA US Equity</td>\n",
       "      <td>2020-04-07</td>\n",
       "      <td>USD</td>\n",
       "      <td>545.45</td>\n",
       "      <td>17919784.0</td>\n",
       "      <td>USD</td>\n",
       "      <td>532.340</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>TSLA US Equity</td>\n",
       "      <td>2020-04-26</td>\n",
       "      <td>USD</td>\n",
       "      <td>725.15</td>\n",
       "      <td>13237612.0</td>\n",
       "      <td>USD</td>\n",
       "      <td>698.180</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>TSLA US Equity</td>\n",
       "      <td>2020-03-22</td>\n",
       "      <td>USD</td>\n",
       "      <td>427.53</td>\n",
       "      <td>28285502.0</td>\n",
       "      <td>USD</td>\n",
       "      <td>425.790</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>TSLA US Equity</td>\n",
       "      <td>2020-05-02</td>\n",
       "      <td>USD</td>\n",
       "      <td>701.32</td>\n",
       "      <td>32531807.0</td>\n",
       "      <td>USD</td>\n",
       "      <td>683.040</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 ID       DATE CURRENCY_x  PX_LAST   PX_VOLUME CURRENCY_y  \\\n",
       "117   ABX CN Equity 2020-05-10        CAD    38.13   6558635.0        CAD   \n",
       "78    ABX CN Equity 2020-04-01        CAD    26.88   8211981.0        CAD   \n",
       "32   TSLA US Equity 2020-04-16        USD   745.21  20657862.0        USD   \n",
       "23   TSLA US Equity 2020-04-07        USD   545.45  17919784.0        USD   \n",
       "42   TSLA US Equity 2020-04-26        USD   725.15  13237612.0        USD   \n",
       "7    TSLA US Equity 2020-03-22        USD   427.53  28285502.0        USD   \n",
       "48   TSLA US Equity 2020-05-02        USD   701.32  32531807.0        USD   \n",
       "\n",
       "      PX_LOW  \n",
       "117   37.960  \n",
       "78    25.850  \n",
       "32   706.715  \n",
       "23   532.340  \n",
       "42   698.180  \n",
       "7    425.790  \n",
       "48   683.040  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.sample(7)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(244, 5)"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "newdf.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(122, 7)"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Put dataframe in easily chartable format\n",
    "> Reindex on datetime"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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>ID</th>\n",
       "      <th>DATE</th>\n",
       "      <th>CURRENCY_x</th>\n",
       "      <th>PX_LAST</th>\n",
       "      <th>PX_VOLUME</th>\n",
       "      <th>CURRENCY_y</th>\n",
       "      <th>PX_LOW</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>111</th>\n",
       "      <td>CL US Equity</td>\n",
       "      <td>2020-02-01</td>\n",
       "      <td>USD</td>\n",
       "      <td>73.780000</td>\n",
       "      <td>13592326.0</td>\n",
       "      <td>USD</td>\n",
       "      <td>73.370</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51</th>\n",
       "      <td>TSLA US Equity</td>\n",
       "      <td>2020-02-02</td>\n",
       "      <td>USD</td>\n",
       "      <td>650.570000</td>\n",
       "      <td>15719266.0</td>\n",
       "      <td>USD</td>\n",
       "      <td>632.520</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>TSLA US Equity</td>\n",
       "      <td>2020-01-05</td>\n",
       "      <td>USD</td>\n",
       "      <td>443.010000</td>\n",
       "      <td>17794697.0</td>\n",
       "      <td>USD</td>\n",
       "      <td>436.920</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>TSLA US Equity</td>\n",
       "      <td>2020-01-13</td>\n",
       "      <td>USD</td>\n",
       "      <td>524.860000</td>\n",
       "      <td>26634547.0</td>\n",
       "      <td>USD</td>\n",
       "      <td>492.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>115</th>\n",
       "      <td>CL US Equity</td>\n",
       "      <td>2020-02-05</td>\n",
       "      <td>USD</td>\n",
       "      <td>75.730000</td>\n",
       "      <td>4282939.0</td>\n",
       "      <td>USD</td>\n",
       "      <td>74.510</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59</th>\n",
       "      <td>TSLA US Equity</td>\n",
       "      <td>2020-02-10</td>\n",
       "      <td>USD</td>\n",
       "      <td>771.280000</td>\n",
       "      <td>24689163.0</td>\n",
       "      <td>USD</td>\n",
       "      <td>752.400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>92</th>\n",
       "      <td>CL US Equity</td>\n",
       "      <td>2020-01-13</td>\n",
       "      <td>USD</td>\n",
       "      <td>70.140000</td>\n",
       "      <td>4641349.0</td>\n",
       "      <td>USD</td>\n",
       "      <td>69.800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>TSLA US Equity</td>\n",
       "      <td>2020-01-29</td>\n",
       "      <td>USD</td>\n",
       "      <td>580.990000</td>\n",
       "      <td>18216672.0</td>\n",
       "      <td>USD</td>\n",
       "      <td>567.430</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58</th>\n",
       "      <td>TSLA US Equity</td>\n",
       "      <td>2020-02-09</td>\n",
       "      <td>USD</td>\n",
       "      <td>748.070000</td>\n",
       "      <td>17063521.0</td>\n",
       "      <td>USD</td>\n",
       "      <td>730.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>TSLA US Equity</td>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>USD</td>\n",
       "      <td>418.330000</td>\n",
       "      <td>10292456.0</td>\n",
       "      <td>USD</td>\n",
       "      <td>402.080</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>TSLA US Equity</td>\n",
       "      <td>2019-12-22</td>\n",
       "      <td>USD</td>\n",
       "      <td>405.590000</td>\n",
       "      <td>14785206.0</td>\n",
       "      <td>USD</td>\n",
       "      <td>400.185</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>CL US Equity</td>\n",
       "      <td>2020-01-17</td>\n",
       "      <td>USD</td>\n",
       "      <td>71.180000</td>\n",
       "      <td>4019743.0</td>\n",
       "      <td>USD</td>\n",
       "      <td>70.430</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>85</th>\n",
       "      <td>CL US Equity</td>\n",
       "      <td>2020-01-06</td>\n",
       "      <td>USD</td>\n",
       "      <td>68.490000</td>\n",
       "      <td>4607525.0</td>\n",
       "      <td>USD</td>\n",
       "      <td>68.010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60</th>\n",
       "      <td>TSLA US Equity</td>\n",
       "      <td>2020-02-11</td>\n",
       "      <td>USD</td>\n",
       "      <td>774.380005</td>\n",
       "      <td>11638381.0</td>\n",
       "      <td>USD</td>\n",
       "      <td>758.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>TSLA US Equity</td>\n",
       "      <td>2020-01-26</td>\n",
       "      <td>USD</td>\n",
       "      <td>564.820000</td>\n",
       "      <td>14353600.0</td>\n",
       "      <td>USD</td>\n",
       "      <td>554.260</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100</th>\n",
       "      <td>CL US Equity</td>\n",
       "      <td>2020-01-21</td>\n",
       "      <td>USD</td>\n",
       "      <td>71.290000</td>\n",
       "      <td>6470769.0</td>\n",
       "      <td>USD</td>\n",
       "      <td>70.250</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>112</th>\n",
       "      <td>CL US Equity</td>\n",
       "      <td>2020-02-02</td>\n",
       "      <td>USD</td>\n",
       "      <td>73.780000</td>\n",
       "      <td>13592326.0</td>\n",
       "      <td>USD</td>\n",
       "      <td>73.370</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66</th>\n",
       "      <td>CL US Equity</td>\n",
       "      <td>2019-12-18</td>\n",
       "      <td>USD</td>\n",
       "      <td>68.270000</td>\n",
       "      <td>5846051.0</td>\n",
       "      <td>USD</td>\n",
       "      <td>67.690</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68</th>\n",
       "      <td>CL US Equity</td>\n",
       "      <td>2019-12-20</td>\n",
       "      <td>USD</td>\n",
       "      <td>68.850000</td>\n",
       "      <td>6008025.0</td>\n",
       "      <td>USD</td>\n",
       "      <td>68.760</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80</th>\n",
       "      <td>CL US Equity</td>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>USD</td>\n",
       "      <td>68.840000</td>\n",
       "      <td>1960193.0</td>\n",
       "      <td>USD</td>\n",
       "      <td>68.375</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 ID       DATE CURRENCY_x     PX_LAST   PX_VOLUME CURRENCY_y  \\\n",
       "111    CL US Equity 2020-02-01        USD   73.780000  13592326.0        USD   \n",
       "51   TSLA US Equity 2020-02-02        USD  650.570000  15719266.0        USD   \n",
       "23   TSLA US Equity 2020-01-05        USD  443.010000  17794697.0        USD   \n",
       "31   TSLA US Equity 2020-01-13        USD  524.860000  26634547.0        USD   \n",
       "115    CL US Equity 2020-02-05        USD   75.730000   4282939.0        USD   \n",
       "59   TSLA US Equity 2020-02-10        USD  771.280000  24689163.0        USD   \n",
       "92     CL US Equity 2020-01-13        USD   70.140000   4641349.0        USD   \n",
       "47   TSLA US Equity 2020-01-29        USD  580.990000  18216672.0        USD   \n",
       "58   TSLA US Equity 2020-02-09        USD  748.070000  17063521.0        USD   \n",
       "19   TSLA US Equity 2020-01-01        USD  418.330000  10292456.0        USD   \n",
       "9    TSLA US Equity 2019-12-22        USD  405.590000  14785206.0        USD   \n",
       "96     CL US Equity 2020-01-17        USD   71.180000   4019743.0        USD   \n",
       "85     CL US Equity 2020-01-06        USD   68.490000   4607525.0        USD   \n",
       "60   TSLA US Equity 2020-02-11        USD  774.380005  11638381.0        USD   \n",
       "44   TSLA US Equity 2020-01-26        USD  564.820000  14353600.0        USD   \n",
       "100    CL US Equity 2020-01-21        USD   71.290000   6470769.0        USD   \n",
       "112    CL US Equity 2020-02-02        USD   73.780000  13592326.0        USD   \n",
       "66     CL US Equity 2019-12-18        USD   68.270000   5846051.0        USD   \n",
       "68     CL US Equity 2019-12-20        USD   68.850000   6008025.0        USD   \n",
       "80     CL US Equity 2020-01-01        USD   68.840000   1960193.0        USD   \n",
       "\n",
       "      PX_LOW  \n",
       "111   73.370  \n",
       "51   632.520  \n",
       "23   436.920  \n",
       "31   492.000  \n",
       "115   74.510  \n",
       "59   752.400  \n",
       "92    69.800  \n",
       "47   567.430  \n",
       "58   730.000  \n",
       "19   402.080  \n",
       "9    400.185  \n",
       "96    70.430  \n",
       "85    68.010  \n",
       "60   758.000  \n",
       "44   554.260  \n",
       "100   70.250  \n",
       "112   73.370  \n",
       "66    67.690  \n",
       "68    68.760  \n",
       "80    68.375  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#newdf = newdf.reset_index()\n",
    "#data = data.reset_index()\n",
    "#del newdf['CURRENCY']\n",
    "#newdf\n",
    "data.sample(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>ID</th>\n",
       "      <th>CL US Equity</th>\n",
       "      <th>TSLA US Equity</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>DATE</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2019-12-13</th>\n",
       "      <td>68.69</td>\n",
       "      <td>358.39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-12-14</th>\n",
       "      <td>68.69</td>\n",
       "      <td>358.39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-12-15</th>\n",
       "      <td>68.69</td>\n",
       "      <td>358.39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-12-16</th>\n",
       "      <td>68.87</td>\n",
       "      <td>381.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-12-17</th>\n",
       "      <td>67.84</td>\n",
       "      <td>378.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-12-18</th>\n",
       "      <td>68.27</td>\n",
       "      <td>393.15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-12-19</th>\n",
       "      <td>68.34</td>\n",
       "      <td>404.04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-12-20</th>\n",
       "      <td>68.85</td>\n",
       "      <td>405.59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-12-21</th>\n",
       "      <td>68.85</td>\n",
       "      <td>405.59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-12-22</th>\n",
       "      <td>68.85</td>\n",
       "      <td>405.59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-12-23</th>\n",
       "      <td>68.72</td>\n",
       "      <td>419.22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-12-24</th>\n",
       "      <td>68.55</td>\n",
       "      <td>425.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-12-25</th>\n",
       "      <td>68.55</td>\n",
       "      <td>425.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-12-26</th>\n",
       "      <td>68.58</td>\n",
       "      <td>430.94</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-12-27</th>\n",
       "      <td>69.18</td>\n",
       "      <td>430.38</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "ID          CL US Equity  TSLA US Equity\n",
       "DATE                                    \n",
       "2019-12-13         68.69          358.39\n",
       "2019-12-14         68.69          358.39\n",
       "2019-12-15         68.69          358.39\n",
       "2019-12-16         68.87          381.50\n",
       "2019-12-17         67.84          378.99\n",
       "2019-12-18         68.27          393.15\n",
       "2019-12-19         68.34          404.04\n",
       "2019-12-20         68.85          405.59\n",
       "2019-12-21         68.85          405.59\n",
       "2019-12-22         68.85          405.59\n",
       "2019-12-23         68.72          419.22\n",
       "2019-12-24         68.55          425.25\n",
       "2019-12-25         68.55          425.25\n",
       "2019-12-26         68.58          430.94\n",
       "2019-12-27         69.18          430.38"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas\n",
    "\n",
    "data2 = data.pivot(index ='DATE', columns='ID', values = 'PX_LAST')\n",
    "data2.head(15)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "a0ef6a2bb36648d5ba9822113bb7beac",
       "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": [
    "# Import the bqviz and bqplot libraries\n",
    "import bqviz as bqv\n",
    "\n",
    "# Pass the example DataFrame to the bqviz LinePlot object and apply Bloomberg styles\n",
    "bqv_plot = bqv.LinePlot(data2).set_style()\n",
    "\n",
    "# Display the plot\n",
    "bqv_plot.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div style=\"text-align: right\"><a href=\"#Sheet-Navigation\">Return to Top ↑</a></div>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Next Example <a name=\"B\"></a>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "request2 = \"\"\"\n",
    "GET(NAME)\n",
    "FOR(filter(members('SPX Index'),GICS_SECTOR_NAME == 'Financials'))\n",
    "\"\"\"\n",
    "\n",
    "\n",
    "response = bq.execute(request2)\n",
    "\n",
    "newdf =bql.combined_df(response)    # newdf = response[0].df() if only one field\n",
    "newdf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "type(response)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Eco Example #1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "request3 = \"\"\"\n",
    "get(GDP(PO=range(-7Q,0Q)))\n",
    "for(['US Country','GB Country'])\n",
    "\"\"\"\n",
    "\n",
    "response = bq.execute(request3)\n",
    "\n",
    "df =bql.combined_df(response)    # newdf = response[0].df() if only one field\n",
    "df.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Eco Example #2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "letstring = \"\"\"\n",
    "let(\n",
    "#PMI = PMI(PO=range(-35M,0M));\n",
    "#ConConf = Consumer_Confidence(PO=range(-35M,0M));\n",
    "#BusConf = Business_Confidence(PO=range(-35M,0M));\n",
    "#CPI = CPI(PO=range(-35M,0M));\n",
    "#PPI = PPI(PO=range(-35M,0M));\n",
    "#RetailSales = Retail_Sales(PO=range(-35M,0M));\n",
    ")\n",
    "\"\"\"\n",
    "\n",
    "getstring = \"\"\"\n",
    "get(#pmi, #ConConf, #BusConf,#CPI, #PPI,#RetailSales)\n",
    "\"\"\"\n",
    "\n",
    "country = \"\\'GB Country\\'\"\n",
    "\n",
    "\n",
    "forstring = \"for\" + \"(\"+ country + \")\"\n",
    "\n",
    "request5 = letstring + getstring + forstring"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "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>PERIOD_REFERENCE_DATE</th>\n",
       "      <th>REPORTED_DATE</th>\n",
       "      <th>CONCEPT</th>\n",
       "      <th>ECO_SOURCE</th>\n",
       "      <th>PERIOD</th>\n",
       "      <th>PERIOD_END_DATE</th>\n",
       "      <th>SEASONALITY</th>\n",
       "      <th>#pmi</th>\n",
       "      <th>PERIOD_OVER_PERIOD</th>\n",
       "      <th>TRANSFORM</th>\n",
       "      <th>#ConConf</th>\n",
       "      <th>#BusConf</th>\n",
       "      <th>#CPI</th>\n",
       "      <th>#PPI</th>\n",
       "      <th>#RetailSales</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",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></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>GB Country</th>\n",
       "      <td>2019-11-30</td>\n",
       "      <td>2019-11-28</td>\n",
       "      <td>CONSUMER_CONFIDENCE</td>\n",
       "      <td>GfK NOP (UK)</td>\n",
       "      <td>2019M11</td>\n",
       "      <td>2019-11-30</td>\n",
       "      <td>NSA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>-14.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GB Country</th>\n",
       "      <td>2017-11-30</td>\n",
       "      <td>2017-12-01</td>\n",
       "      <td>PMI</td>\n",
       "      <td>Markit</td>\n",
       "      <td>2017M11</td>\n",
       "      <td>2017-11-30</td>\n",
       "      <td>SA</td>\n",
       "      <td>58.2</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GB Country</th>\n",
       "      <td>2018-01-31</td>\n",
       "      <td>NaT</td>\n",
       "      <td>BUSINESS_CONFIDENCE</td>\n",
       "      <td>European Commission</td>\n",
       "      <td>2018M1</td>\n",
       "      <td>2018-01-31</td>\n",
       "      <td>SA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-7.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GB Country</th>\n",
       "      <td>2019-05-31</td>\n",
       "      <td>2019-06-20</td>\n",
       "      <td>RETAIL_SALES</td>\n",
       "      <td>UK Office for National Statistics</td>\n",
       "      <td>2019M5</td>\n",
       "      <td>2019-05-31</td>\n",
       "      <td>SA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>YOY</td>\n",
       "      <td>PCT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GB Country</th>\n",
       "      <td>2018-01-31</td>\n",
       "      <td>2018-02-16</td>\n",
       "      <td>RETAIL_SALES</td>\n",
       "      <td>UK Office for National Statistics</td>\n",
       "      <td>2018M1</td>\n",
       "      <td>2018-01-31</td>\n",
       "      <td>SA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>YOY</td>\n",
       "      <td>PCT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GB Country</th>\n",
       "      <td>2020-04-30</td>\n",
       "      <td>2020-05-08</td>\n",
       "      <td>CONSUMER_CONFIDENCE</td>\n",
       "      <td>GfK NOP (UK)</td>\n",
       "      <td>2020M4</td>\n",
       "      <td>2020-04-30</td>\n",
       "      <td>NSA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>-34.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GB Country</th>\n",
       "      <td>2019-09-30</td>\n",
       "      <td>2019-10-16</td>\n",
       "      <td>CPI</td>\n",
       "      <td>UK Office for National Statistics</td>\n",
       "      <td>2019M9</td>\n",
       "      <td>2019-09-30</td>\n",
       "      <td>NSA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>YOY</td>\n",
       "      <td>PCT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GB Country</th>\n",
       "      <td>2018-02-28</td>\n",
       "      <td>2018-03-01</td>\n",
       "      <td>PMI</td>\n",
       "      <td>Markit</td>\n",
       "      <td>2018M2</td>\n",
       "      <td>2018-02-28</td>\n",
       "      <td>SA</td>\n",
       "      <td>55.3</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GB Country</th>\n",
       "      <td>2018-12-31</td>\n",
       "      <td>2019-01-16</td>\n",
       "      <td>PPI</td>\n",
       "      <td>UK Office for National Statistics</td>\n",
       "      <td>2018M12</td>\n",
       "      <td>2018-12-31</td>\n",
       "      <td>NSA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>YOY</td>\n",
       "      <td>PCT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.40857</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GB Country</th>\n",
       "      <td>2019-08-31</td>\n",
       "      <td>NaT</td>\n",
       "      <td>BUSINESS_CONFIDENCE</td>\n",
       "      <td>European Commission</td>\n",
       "      <td>2019M8</td>\n",
       "      <td>2019-08-31</td>\n",
       "      <td>SA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-29.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GB Country</th>\n",
       "      <td>2018-07-31</td>\n",
       "      <td>2018-08-15</td>\n",
       "      <td>CPI</td>\n",
       "      <td>UK Office for National Statistics</td>\n",
       "      <td>2018M7</td>\n",
       "      <td>2018-07-31</td>\n",
       "      <td>NSA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>YOY</td>\n",
       "      <td>PCT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GB Country</th>\n",
       "      <td>2018-05-31</td>\n",
       "      <td>2018-06-13</td>\n",
       "      <td>CPI</td>\n",
       "      <td>UK Office for National Statistics</td>\n",
       "      <td>2018M5</td>\n",
       "      <td>2018-05-31</td>\n",
       "      <td>NSA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>YOY</td>\n",
       "      <td>PCT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GB Country</th>\n",
       "      <td>2017-12-31</td>\n",
       "      <td>2017-12-20</td>\n",
       "      <td>CONSUMER_CONFIDENCE</td>\n",
       "      <td>GfK NOP (UK)</td>\n",
       "      <td>2017M12</td>\n",
       "      <td>2017-12-31</td>\n",
       "      <td>NSA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>-13.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GB Country</th>\n",
       "      <td>2018-04-30</td>\n",
       "      <td>NaT</td>\n",
       "      <td>BUSINESS_CONFIDENCE</td>\n",
       "      <td>European Commission</td>\n",
       "      <td>2018M4</td>\n",
       "      <td>2018-04-30</td>\n",
       "      <td>SA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-4.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GB Country</th>\n",
       "      <td>2020-05-31</td>\n",
       "      <td>2020-05-21</td>\n",
       "      <td>PMI</td>\n",
       "      <td>Markit</td>\n",
       "      <td>2020M5</td>\n",
       "      <td>2020-05-31</td>\n",
       "      <td>SA</td>\n",
       "      <td>40.6</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           PERIOD_REFERENCE_DATE REPORTED_DATE              CONCEPT  \\\n",
       "ID                                                                    \n",
       "GB Country            2019-11-30    2019-11-28  CONSUMER_CONFIDENCE   \n",
       "GB Country            2017-11-30    2017-12-01                  PMI   \n",
       "GB Country            2018-01-31           NaT  BUSINESS_CONFIDENCE   \n",
       "GB Country            2019-05-31    2019-06-20         RETAIL_SALES   \n",
       "GB Country            2018-01-31    2018-02-16         RETAIL_SALES   \n",
       "GB Country            2020-04-30    2020-05-08  CONSUMER_CONFIDENCE   \n",
       "GB Country            2019-09-30    2019-10-16                  CPI   \n",
       "GB Country            2018-02-28    2018-03-01                  PMI   \n",
       "GB Country            2018-12-31    2019-01-16                  PPI   \n",
       "GB Country            2019-08-31           NaT  BUSINESS_CONFIDENCE   \n",
       "GB Country            2018-07-31    2018-08-15                  CPI   \n",
       "GB Country            2018-05-31    2018-06-13                  CPI   \n",
       "GB Country            2017-12-31    2017-12-20  CONSUMER_CONFIDENCE   \n",
       "GB Country            2018-04-30           NaT  BUSINESS_CONFIDENCE   \n",
       "GB Country            2020-05-31    2020-05-21                  PMI   \n",
       "\n",
       "                                   ECO_SOURCE   PERIOD PERIOD_END_DATE  \\\n",
       "ID                                                                       \n",
       "GB Country                       GfK NOP (UK)  2019M11      2019-11-30   \n",
       "GB Country                             Markit  2017M11      2017-11-30   \n",
       "GB Country                European Commission   2018M1      2018-01-31   \n",
       "GB Country  UK Office for National Statistics   2019M5      2019-05-31   \n",
       "GB Country  UK Office for National Statistics   2018M1      2018-01-31   \n",
       "GB Country                       GfK NOP (UK)   2020M4      2020-04-30   \n",
       "GB Country  UK Office for National Statistics   2019M9      2019-09-30   \n",
       "GB Country                             Markit   2018M2      2018-02-28   \n",
       "GB Country  UK Office for National Statistics  2018M12      2018-12-31   \n",
       "GB Country                European Commission   2019M8      2019-08-31   \n",
       "GB Country  UK Office for National Statistics   2018M7      2018-07-31   \n",
       "GB Country  UK Office for National Statistics   2018M5      2018-05-31   \n",
       "GB Country                       GfK NOP (UK)  2017M12      2017-12-31   \n",
       "GB Country                European Commission   2018M4      2018-04-30   \n",
       "GB Country                             Markit   2020M5      2020-05-31   \n",
       "\n",
       "           SEASONALITY  #pmi PERIOD_OVER_PERIOD TRANSFORM  #ConConf  #BusConf  \\\n",
       "ID                                                                              \n",
       "GB Country         NSA   NaN               None      None     -14.0       NaN   \n",
       "GB Country          SA  58.2               None      None       NaN       NaN   \n",
       "GB Country          SA   NaN               None      None       NaN      -7.5   \n",
       "GB Country          SA   NaN                YOY       PCT       NaN       NaN   \n",
       "GB Country          SA   NaN                YOY       PCT       NaN       NaN   \n",
       "GB Country         NSA   NaN               None      None     -34.0       NaN   \n",
       "GB Country         NSA   NaN                YOY       PCT       NaN       NaN   \n",
       "GB Country          SA  55.3               None      None       NaN       NaN   \n",
       "GB Country         NSA   NaN                YOY       PCT       NaN       NaN   \n",
       "GB Country          SA   NaN               None      None       NaN     -29.0   \n",
       "GB Country         NSA   NaN                YOY       PCT       NaN       NaN   \n",
       "GB Country         NSA   NaN                YOY       PCT       NaN       NaN   \n",
       "GB Country         NSA   NaN               None      None     -13.0       NaN   \n",
       "GB Country          SA   NaN               None      None       NaN      -4.0   \n",
       "GB Country          SA  40.6               None      None       NaN       NaN   \n",
       "\n",
       "            #CPI     #PPI  #RetailSales  \n",
       "ID                                       \n",
       "GB Country   NaN      NaN           NaN  \n",
       "GB Country   NaN      NaN           NaN  \n",
       "GB Country   NaN      NaN           NaN  \n",
       "GB Country   NaN      NaN           2.1  \n",
       "GB Country   NaN      NaN           2.0  \n",
       "GB Country   NaN      NaN           NaN  \n",
       "GB Country   1.7      NaN           NaN  \n",
       "GB Country   NaN      NaN           NaN  \n",
       "GB Country   NaN  2.40857           NaN  \n",
       "GB Country   NaN      NaN           NaN  \n",
       "GB Country   2.5      NaN           NaN  \n",
       "GB Country   2.4      NaN           NaN  \n",
       "GB Country   NaN      NaN           NaN  \n",
       "GB Country   NaN      NaN           NaN  \n",
       "GB Country   NaN      NaN           NaN  "
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "response = bq.execute(request5)\n",
    "\n",
    "df =bql.combined_df(response)    # newdf = response[0].df() if only one field\n",
    "df.sample(15)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "#newdf4 = pd.DataFrame({r.name:r.df()[r.name] for r in response})\n",
    "newdf2 = pd.concat([x.df() for x in response], axis=1)\n",
    "#newdf2.columns\n",
    "temp = newdf2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\blp\\BQuant\\environments\\bqnt-1.281.1\\lib\\site-packages\\bqnb\\kernelapp.py:4: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  # provided solely under the terms of the BFLP license agreement.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>#pmi</th>\n",
       "      <th>#ConConf</th>\n",
       "      <th>#BusConf</th>\n",
       "      <th>#CPI</th>\n",
       "      <th>#PPI</th>\n",
       "      <th>#RetailSales</th>\n",
       "      <th>PERIOD_REFERENCE_DATE</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",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>GB Country</th>\n",
       "      <td>54.2</td>\n",
       "      <td>-10.0</td>\n",
       "      <td>3.9</td>\n",
       "      <td>2.9</td>\n",
       "      <td>3.56473</td>\n",
       "      <td>0.7</td>\n",
       "      <td>2017-06-30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GB Country</th>\n",
       "      <td>55.5</td>\n",
       "      <td>-12.0</td>\n",
       "      <td>-4.9</td>\n",
       "      <td>2.6</td>\n",
       "      <td>3.27409</td>\n",
       "      <td>2.9</td>\n",
       "      <td>2017-07-31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GB Country</th>\n",
       "      <td>57.0</td>\n",
       "      <td>-10.0</td>\n",
       "      <td>9.8</td>\n",
       "      <td>2.6</td>\n",
       "      <td>3.26797</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2017-08-31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GB Country</th>\n",
       "      <td>55.5</td>\n",
       "      <td>-9.0</td>\n",
       "      <td>-4.2</td>\n",
       "      <td>2.9</td>\n",
       "      <td>3.44827</td>\n",
       "      <td>2.4</td>\n",
       "      <td>2017-09-30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GB Country</th>\n",
       "      <td>56.6</td>\n",
       "      <td>-10.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.34572</td>\n",
       "      <td>1.6</td>\n",
       "      <td>2017-10-31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GB Country</th>\n",
       "      <td>58.2</td>\n",
       "      <td>-12.0</td>\n",
       "      <td>-5.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.86242</td>\n",
       "      <td>0.1</td>\n",
       "      <td>2017-11-30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GB Country</th>\n",
       "      <td>55.7</td>\n",
       "      <td>-13.0</td>\n",
       "      <td>9.8</td>\n",
       "      <td>3.1</td>\n",
       "      <td>3.13653</td>\n",
       "      <td>1.1</td>\n",
       "      <td>2017-12-31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GB Country</th>\n",
       "      <td>55.2</td>\n",
       "      <td>-9.0</td>\n",
       "      <td>0.6</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.12788</td>\n",
       "      <td>1.8</td>\n",
       "      <td>2018-01-31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GB Country</th>\n",
       "      <td>55.3</td>\n",
       "      <td>-10.0</td>\n",
       "      <td>-7.5</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.83623</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2018-02-28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GB Country</th>\n",
       "      <td>54.8</td>\n",
       "      <td>-7.0</td>\n",
       "      <td>2.8</td>\n",
       "      <td>2.7</td>\n",
       "      <td>2.64840</td>\n",
       "      <td>1.6</td>\n",
       "      <td>2018-03-31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GB Country</th>\n",
       "      <td>53.8</td>\n",
       "      <td>-9.0</td>\n",
       "      <td>4.9</td>\n",
       "      <td>2.5</td>\n",
       "      <td>2.45454</td>\n",
       "      <td>1.1</td>\n",
       "      <td>2018-04-30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GB Country</th>\n",
       "      <td>54.3</td>\n",
       "      <td>-7.0</td>\n",
       "      <td>-4.0</td>\n",
       "      <td>2.4</td>\n",
       "      <td>2.53853</td>\n",
       "      <td>0.8</td>\n",
       "      <td>2018-05-31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GB Country</th>\n",
       "      <td>54.0</td>\n",
       "      <td>-9.0</td>\n",
       "      <td>0.2</td>\n",
       "      <td>2.4</td>\n",
       "      <td>2.98913</td>\n",
       "      <td>4.5</td>\n",
       "      <td>2018-06-30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GB Country</th>\n",
       "      <td>53.9</td>\n",
       "      <td>-10.0</td>\n",
       "      <td>7.6</td>\n",
       "      <td>2.4</td>\n",
       "      <td>3.26087</td>\n",
       "      <td>3.1</td>\n",
       "      <td>2018-07-31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GB Country</th>\n",
       "      <td>52.9</td>\n",
       "      <td>-7.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2.5</td>\n",
       "      <td>3.07414</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2018-08-31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GB Country</th>\n",
       "      <td>53.7</td>\n",
       "      <td>-9.0</td>\n",
       "      <td>14.5</td>\n",
       "      <td>2.7</td>\n",
       "      <td>2.97298</td>\n",
       "      <td>3.4</td>\n",
       "      <td>2018-09-30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GB Country</th>\n",
       "      <td>51.1</td>\n",
       "      <td>-10.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>2.4</td>\n",
       "      <td>3.14748</td>\n",
       "      <td>3.2</td>\n",
       "      <td>2018-10-31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GB Country</th>\n",
       "      <td>53.3</td>\n",
       "      <td>-13.0</td>\n",
       "      <td>9.9</td>\n",
       "      <td>2.4</td>\n",
       "      <td>3.32136</td>\n",
       "      <td>2.7</td>\n",
       "      <td>2018-11-30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GB Country</th>\n",
       "      <td>54.3</td>\n",
       "      <td>-14.0</td>\n",
       "      <td>1.5</td>\n",
       "      <td>2.3</td>\n",
       "      <td>3.04114</td>\n",
       "      <td>3.6</td>\n",
       "      <td>2018-12-31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GB Country</th>\n",
       "      <td>52.8</td>\n",
       "      <td>-14.0</td>\n",
       "      <td>-7.5</td>\n",
       "      <td>2.1</td>\n",
       "      <td>2.40857</td>\n",
       "      <td>3.3</td>\n",
       "      <td>2019-01-31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GB Country</th>\n",
       "      <td>52.1</td>\n",
       "      <td>-13.0</td>\n",
       "      <td>-1.9</td>\n",
       "      <td>1.8</td>\n",
       "      <td>2.13523</td>\n",
       "      <td>4.4</td>\n",
       "      <td>2019-02-28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GB Country</th>\n",
       "      <td>55.1</td>\n",
       "      <td>-13.0</td>\n",
       "      <td>2.2</td>\n",
       "      <td>1.9</td>\n",
       "      <td>2.40213</td>\n",
       "      <td>4.1</td>\n",
       "      <td>2019-03-31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GB Country</th>\n",
       "      <td>53.1</td>\n",
       "      <td>-13.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1.9</td>\n",
       "      <td>2.21828</td>\n",
       "      <td>6.3</td>\n",
       "      <td>2019-04-30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GB Country</th>\n",
       "      <td>49.4</td>\n",
       "      <td>-10.0</td>\n",
       "      <td>5.1</td>\n",
       "      <td>2.1</td>\n",
       "      <td>2.12202</td>\n",
       "      <td>4.7</td>\n",
       "      <td>2019-05-31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GB Country</th>\n",
       "      <td>48.0</td>\n",
       "      <td>-13.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.93492</td>\n",
       "      <td>2.1</td>\n",
       "      <td>2019-06-30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GB Country</th>\n",
       "      <td>48.0</td>\n",
       "      <td>-11.0</td>\n",
       "      <td>-7.5</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.57895</td>\n",
       "      <td>3.5</td>\n",
       "      <td>2019-07-31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GB Country</th>\n",
       "      <td>47.4</td>\n",
       "      <td>-14.0</td>\n",
       "      <td>-11.7</td>\n",
       "      <td>2.1</td>\n",
       "      <td>1.92982</td>\n",
       "      <td>3.1</td>\n",
       "      <td>2019-08-31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GB Country</th>\n",
       "      <td>48.3</td>\n",
       "      <td>-12.0</td>\n",
       "      <td>-29.0</td>\n",
       "      <td>1.7</td>\n",
       "      <td>1.66229</td>\n",
       "      <td>2.2</td>\n",
       "      <td>2019-09-30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GB Country</th>\n",
       "      <td>49.6</td>\n",
       "      <td>-14.0</td>\n",
       "      <td>-14.0</td>\n",
       "      <td>1.7</td>\n",
       "      <td>1.22058</td>\n",
       "      <td>2.6</td>\n",
       "      <td>2019-10-31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GB Country</th>\n",
       "      <td>48.9</td>\n",
       "      <td>-14.0</td>\n",
       "      <td>-21.6</td>\n",
       "      <td>1.5</td>\n",
       "      <td>0.78193</td>\n",
       "      <td>2.8</td>\n",
       "      <td>2019-11-30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GB Country</th>\n",
       "      <td>47.5</td>\n",
       "      <td>-11.0</td>\n",
       "      <td>-13.7</td>\n",
       "      <td>1.5</td>\n",
       "      <td>0.52084</td>\n",
       "      <td>0.5</td>\n",
       "      <td>2019-12-31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GB Country</th>\n",
       "      <td>50.0</td>\n",
       "      <td>-9.0</td>\n",
       "      <td>-15.6</td>\n",
       "      <td>1.3</td>\n",
       "      <td>0.78397</td>\n",
       "      <td>0.4</td>\n",
       "      <td>2020-01-31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GB Country</th>\n",
       "      <td>51.7</td>\n",
       "      <td>-7.0</td>\n",
       "      <td>-21.8</td>\n",
       "      <td>1.8</td>\n",
       "      <td>0.95819</td>\n",
       "      <td>1.3</td>\n",
       "      <td>2020-02-29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GB Country</th>\n",
       "      <td>47.8</td>\n",
       "      <td>-9.0</td>\n",
       "      <td>-12.3</td>\n",
       "      <td>1.7</td>\n",
       "      <td>0.52128</td>\n",
       "      <td>0.6</td>\n",
       "      <td>2020-03-31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GB Country</th>\n",
       "      <td>32.6</td>\n",
       "      <td>-34.0</td>\n",
       "      <td>-12.9</td>\n",
       "      <td>1.5</td>\n",
       "      <td>0.26042</td>\n",
       "      <td>-4.2</td>\n",
       "      <td>2020-04-30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GB Country</th>\n",
       "      <td>40.6</td>\n",
       "      <td>-34.0</td>\n",
       "      <td>-39.3</td>\n",
       "      <td>0.8</td>\n",
       "      <td>-0.69264</td>\n",
       "      <td>-18.4</td>\n",
       "      <td>2020-05-31</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            #pmi  #ConConf  #BusConf  #CPI     #PPI  #RetailSales  \\\n",
       "ID                                                                  \n",
       "GB Country  54.2     -10.0       3.9   2.9  3.56473           0.7   \n",
       "GB Country  55.5     -12.0      -4.9   2.6  3.27409           2.9   \n",
       "GB Country  57.0     -10.0       9.8   2.6  3.26797           1.0   \n",
       "GB Country  55.5      -9.0      -4.2   2.9  3.44827           2.4   \n",
       "GB Country  56.6     -10.0       4.0   3.0  3.34572           1.6   \n",
       "GB Country  58.2     -12.0      -5.0   3.0  2.86242           0.1   \n",
       "GB Country  55.7     -13.0       9.8   3.1  3.13653           1.1   \n",
       "GB Country  55.2      -9.0       0.6   3.0  3.12788           1.8   \n",
       "GB Country  55.3     -10.0      -7.5   3.0  2.83623           2.0   \n",
       "GB Country  54.8      -7.0       2.8   2.7  2.64840           1.6   \n",
       "GB Country  53.8      -9.0       4.9   2.5  2.45454           1.1   \n",
       "GB Country  54.3      -7.0      -4.0   2.4  2.53853           0.8   \n",
       "GB Country  54.0      -9.0       0.2   2.4  2.98913           4.5   \n",
       "GB Country  53.9     -10.0       7.6   2.4  3.26087           3.1   \n",
       "GB Country  52.9      -7.0       4.0   2.5  3.07414           4.0   \n",
       "GB Country  53.7      -9.0      14.5   2.7  2.97298           3.4   \n",
       "GB Country  51.1     -10.0       5.0   2.4  3.14748           3.2   \n",
       "GB Country  53.3     -13.0       9.9   2.4  3.32136           2.7   \n",
       "GB Country  54.3     -14.0       1.5   2.3  3.04114           3.6   \n",
       "GB Country  52.8     -14.0      -7.5   2.1  2.40857           3.3   \n",
       "GB Country  52.1     -13.0      -1.9   1.8  2.13523           4.4   \n",
       "GB Country  55.1     -13.0       2.2   1.9  2.40213           4.1   \n",
       "GB Country  53.1     -13.0       0.5   1.9  2.21828           6.3   \n",
       "GB Country  49.4     -10.0       5.1   2.1  2.12202           4.7   \n",
       "GB Country  48.0     -13.0       3.0   2.0  1.93492           2.1   \n",
       "GB Country  48.0     -11.0      -7.5   2.0  1.57895           3.5   \n",
       "GB Country  47.4     -14.0     -11.7   2.1  1.92982           3.1   \n",
       "GB Country  48.3     -12.0     -29.0   1.7  1.66229           2.2   \n",
       "GB Country  49.6     -14.0     -14.0   1.7  1.22058           2.6   \n",
       "GB Country  48.9     -14.0     -21.6   1.5  0.78193           2.8   \n",
       "GB Country  47.5     -11.0     -13.7   1.5  0.52084           0.5   \n",
       "GB Country  50.0      -9.0     -15.6   1.3  0.78397           0.4   \n",
       "GB Country  51.7      -7.0     -21.8   1.8  0.95819           1.3   \n",
       "GB Country  47.8      -9.0     -12.3   1.7  0.52128           0.6   \n",
       "GB Country  32.6     -34.0     -12.9   1.5  0.26042          -4.2   \n",
       "GB Country  40.6     -34.0     -39.3   0.8 -0.69264         -18.4   \n",
       "\n",
       "           PERIOD_REFERENCE_DATE  \n",
       "ID                                \n",
       "GB Country            2017-06-30  \n",
       "GB Country            2017-07-31  \n",
       "GB Country            2017-08-31  \n",
       "GB Country            2017-09-30  \n",
       "GB Country            2017-10-31  \n",
       "GB Country            2017-11-30  \n",
       "GB Country            2017-12-31  \n",
       "GB Country            2018-01-31  \n",
       "GB Country            2018-02-28  \n",
       "GB Country            2018-03-31  \n",
       "GB Country            2018-04-30  \n",
       "GB Country            2018-05-31  \n",
       "GB Country            2018-06-30  \n",
       "GB Country            2018-07-31  \n",
       "GB Country            2018-08-31  \n",
       "GB Country            2018-09-30  \n",
       "GB Country            2018-10-31  \n",
       "GB Country            2018-11-30  \n",
       "GB Country            2018-12-31  \n",
       "GB Country            2019-01-31  \n",
       "GB Country            2019-02-28  \n",
       "GB Country            2019-03-31  \n",
       "GB Country            2019-04-30  \n",
       "GB Country            2019-05-31  \n",
       "GB Country            2019-06-30  \n",
       "GB Country            2019-07-31  \n",
       "GB Country            2019-08-31  \n",
       "GB Country            2019-09-30  \n",
       "GB Country            2019-10-31  \n",
       "GB Country            2019-11-30  \n",
       "GB Country            2019-12-31  \n",
       "GB Country            2020-01-31  \n",
       "GB Country            2020-02-29  \n",
       "GB Country            2020-03-31  \n",
       "GB Country            2020-04-30  \n",
       "GB Country            2020-05-31  "
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "subset = ['#pmi', '#ConConf', '#BusConf','#CPI', '#PPI','#RetailSales']\n",
    "#newdf2_ = temp[['#pmi', '#ConConf', '#BusConf','#CPI', '#PPI','#RetailSales']]\n",
    "newdf2_ = temp[subset]\n",
    "newdf2_['PERIOD_REFERENCE_DATE']= temp.iloc[:,0]\n",
    "\n",
    "newdf2_\n",
    "#subset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "newdf = df.reset_index()\n",
    "newdf.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "newdf.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Eco Example #3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [],
   "source": [
    "letstring = \"\"\"\n",
    "let(\n",
    "#CPILVL = znav(if(CPI <=3.5 AND CPI >=1.5,1,0).value);\n",
    "#GDPZ= znav(if(last(zscore(GDP(PO=Range(-39q,0q))))>1,1,0).value);\n",
    "#GDPLVL = znav(if(GDP >2.0,1,0).value);\n",
    "#PMILVL = znav(if(PMI >50,1,-1).value);\n",
    "#PMIZ= znav(if(last(zscore(PMI(PO=Range(-1m,0m))))>0,1,0).value);\n",
    "#Reserves = znav(if(International_Reserves >50000000000,1,0).value);\n",
    "#Score = (#CPILVL + #GDPLVL +#GDPZ+#PMILVL+#Reserves+#PMIZ);\n",
    ")\n",
    "\"\"\"\n",
    "\n",
    "getstring = \"\"\"\n",
    "get(groupsort(#score),ID().country_name,#cpilvl,#gdplvl,#gdpz,#PMILVL,#Reserves,#PMIZ)\n",
    "\"\"\"\n",
    "\n",
    "country = \"\\'GB Country\\'\"\n",
    "\n",
    "\n",
    "forstring = \"for\" + \"(members(\\'Asia\\',type=\\'Region\\'))\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "\"for(members('Asia',type='Region'))\""
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "forstring"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "request6 = letstring + getstring + forstring"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "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>groupsort(#score)</th>\n",
       "      <th>ID().country_name</th>\n",
       "      <th>#CPILVL</th>\n",
       "      <th>#GDPLVL</th>\n",
       "      <th>#GDPZ</th>\n",
       "      <th>#PMILVL</th>\n",
       "      <th>#Reserves</th>\n",
       "      <th>#PMIZ</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",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>VN</th>\n",
       "      <td>3.0</td>\n",
       "      <td>Vietnam</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AZ</th>\n",
       "      <td>2.0</td>\n",
       "      <td>Azerbaijan</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BD</th>\n",
       "      <td>2.0</td>\n",
       "      <td>Bangladesh</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>KH</th>\n",
       "      <td>2.0</td>\n",
       "      <td>Cambodia</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>HK</th>\n",
       "      <td>2.0</td>\n",
       "      <td>Hong Kong</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    groupsort(#score) ID().country_name  #CPILVL  #GDPLVL  #GDPZ  #PMILVL  \\\n",
       "ID                                                                          \n",
       "VN                3.0           Vietnam      1.0      1.0    1.0     -1.0   \n",
       "AZ                2.0        Azerbaijan      1.0      1.0    0.0      0.0   \n",
       "BD                2.0        Bangladesh      0.0      1.0    1.0      0.0   \n",
       "KH                2.0          Cambodia      1.0      1.0    0.0      0.0   \n",
       "HK                2.0         Hong Kong      1.0      0.0    0.0      0.0   \n",
       "\n",
       "    #Reserves  #PMIZ  \n",
       "ID                    \n",
       "VN        1.0    0.0  \n",
       "AZ        0.0    0.0  \n",
       "BD        0.0    0.0  \n",
       "KH        0.0    0.0  \n",
       "HK        1.0    0.0  "
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "response = bq.execute(request6)\n",
    "\n",
    "df =bql.combined_df(response)    # newdf = response[0].df() if only one field\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Technical Analysis Example #1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [],
   "source": [
    "request7 = \"\"\"\n",
    "get(\n",
    "RSI(PX_LAST(ca_adj=full),period=14)\n",
    ")\n",
    "for(members('SPX Index'))\n",
    "\"\"\"\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "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>DATE</th>\n",
       "      <th>RSI(PX_LAST(ca_adj=full),period=14)</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ID</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>LYB UN Equity</th>\n",
       "      <td>2020-05-26</td>\n",
       "      <td>68.885562</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AXP UN Equity</th>\n",
       "      <td>2020-05-26</td>\n",
       "      <td>57.967909</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>VZ UN Equity</th>\n",
       "      <td>2020-05-26</td>\n",
       "      <td>42.356438</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AVGO UW Equity</th>\n",
       "      <td>2020-05-26</td>\n",
       "      <td>59.859745</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BA UN Equity</th>\n",
       "      <td>2020-05-26</td>\n",
       "      <td>55.284106</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     DATE  RSI(PX_LAST(ca_adj=full),period=14)\n",
       "ID                                                            \n",
       "LYB UN Equity  2020-05-26                            68.885562\n",
       "AXP UN Equity  2020-05-26                            57.967909\n",
       "VZ UN Equity   2020-05-26                            42.356438\n",
       "AVGO UW Equity 2020-05-26                            59.859745\n",
       "BA UN Equity   2020-05-26                            55.284106"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "response = bq.execute(request7)\n",
    "\n",
    "df =bql.combined_df(response)    # newdf = response[0].df() if only one field\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div style=\"text-align: right\"><a href=\"#Sheet-Navigation\">Return to Top ↑</a></div>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## BDH-Style Time Series Fetch using `bql.util` <a name=\"Z\"></a>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import bqviz as bqv\n",
    "import bql\n",
    "bq = bql.Service()\n",
    "from bql.util import get_time_series"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Help on function get_time_series in module bql.util.get_time_series:\n",
      "\n",
      "get_time_series(tickers, fields, start, end=None, service=None)\n",
      "    Return the time series of the fields for the specified tickers.\n",
      "    \n",
      "    Parameters\n",
      "    ----------\n",
      "    tickers: str, sequence\n",
      "        A single ticker, or a sequence of tickers.\n",
      "    fields: str, sequence\n",
      "        A single field name, or a list of field names.\n",
      "    start: datetime.date, str\n",
      "        start date as :class:`datetime.date` or as a string in\n",
      "        YYYY-MM-DD format.\n",
      "    end: datetime.date, str (default: ``None``)\n",
      "        end date as :class:`datetime.date` or as a string in\n",
      "        YYYY-MM-DD format.\n",
      "    service: :class:`bql.Service` (default: ``None``)\n",
      "        the service to use to make the request, or ``None`` to use a default\n",
      "        service.\n",
      "    \n",
      "    Returns\n",
      "    -------\n",
      "    :class:`pandas.DataFrame`\n",
      "        A dataframe with a multi level index of ticker and date and\n",
      "        field names as columns.\n",
      "\n"
     ]
    }
   ],
   "source": [
    "help(get_time_series)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "tickers = ['PTON US Equity', 'IBM US Equity']\n",
    "start =  '2019-01-02'\n",
    "end = '2020-02-28'\n",
    "fields = ['PX_LAST', 'VWAP']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "#data = get_time_series(tickers, fields, start, end)\n",
    "data = get_time_series(tickers, fields, start, end)\n",
    "#?get_time_series"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "#data.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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></th>\n",
       "      <th>CURRENCY</th>\n",
       "      <th>PX_LAST</th>\n",
       "      <th>VWAP</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ID</th>\n",
       "      <th>DATE</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>IBM US Equity</th>\n",
       "      <th>2019-11-13</th>\n",
       "      <td>USD</td>\n",
       "      <td>134.48</td>\n",
       "      <td>134.433735</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PTON US Equity</th>\n",
       "      <th>2019-10-22</th>\n",
       "      <td>USD</td>\n",
       "      <td>21.16</td>\n",
       "      <td>21.387174</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">IBM US Equity</th>\n",
       "      <th>2020-01-22</th>\n",
       "      <td>USD</td>\n",
       "      <td>143.89</td>\n",
       "      <td>144.082495</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-02</th>\n",
       "      <td>USD</td>\n",
       "      <td>135.42</td>\n",
       "      <td>135.262049</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-27</th>\n",
       "      <td>USD</td>\n",
       "      <td>138.62</td>\n",
       "      <td>138.907700</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PTON US Equity</th>\n",
       "      <th>2020-01-28</th>\n",
       "      <td>USD</td>\n",
       "      <td>31.38</td>\n",
       "      <td>31.102489</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>IBM US Equity</th>\n",
       "      <th>2019-11-19</th>\n",
       "      <td>USD</td>\n",
       "      <td>134.52</td>\n",
       "      <td>134.767307</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PTON US Equity</th>\n",
       "      <th>2020-01-30</th>\n",
       "      <td>USD</td>\n",
       "      <td>32.26</td>\n",
       "      <td>31.957996</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">IBM US Equity</th>\n",
       "      <th>2019-12-04</th>\n",
       "      <td>USD</td>\n",
       "      <td>131.99</td>\n",
       "      <td>132.415554</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-05-01</th>\n",
       "      <td>USD</td>\n",
       "      <td>140.56</td>\n",
       "      <td>140.978460</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                          CURRENCY  PX_LAST        VWAP\n",
       "ID             DATE                                    \n",
       "IBM US Equity  2019-11-13      USD   134.48  134.433735\n",
       "PTON US Equity 2019-10-22      USD    21.16   21.387174\n",
       "IBM US Equity  2020-01-22      USD   143.89  144.082495\n",
       "               2020-01-02      USD   135.42  135.262049\n",
       "               2020-01-27      USD   138.62  138.907700\n",
       "PTON US Equity 2020-01-28      USD    31.38   31.102489\n",
       "IBM US Equity  2019-11-19      USD   134.52  134.767307\n",
       "PTON US Equity 2020-01-30      USD    32.26   31.957996\n",
       "IBM US Equity  2019-12-04      USD   131.99  132.415554\n",
       "               2019-05-01      USD   140.56  140.978460"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.sample(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>ID</th>\n",
       "      <th>SPTSX60 Index</th>\n",
       "      <th>SPX Index</th>\n",
       "      <th>SX5E Index</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>DATE</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2019-01-02</th>\n",
       "      <td>859.801</td>\n",
       "      <td>2510.03</td>\n",
       "      <td>2993.18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-03</th>\n",
       "      <td>851.451</td>\n",
       "      <td>2447.89</td>\n",
       "      <td>2954.66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-04</th>\n",
       "      <td>864.443</td>\n",
       "      <td>2531.94</td>\n",
       "      <td>3041.85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-07</th>\n",
       "      <td>868.547</td>\n",
       "      <td>2549.69</td>\n",
       "      <td>3033.64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-08</th>\n",
       "      <td>874.424</td>\n",
       "      <td>2574.41</td>\n",
       "      <td>3054.94</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-09</th>\n",
       "      <td>885.913</td>\n",
       "      <td>2584.96</td>\n",
       "      <td>3070.24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-10</th>\n",
       "      <td>891.718</td>\n",
       "      <td>2596.64</td>\n",
       "      <td>3075.73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-11</th>\n",
       "      <td>893.385</td>\n",
       "      <td>2596.26</td>\n",
       "      <td>3070.04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-14</th>\n",
       "      <td>895.136</td>\n",
       "      <td>2582.61</td>\n",
       "      <td>3055.18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-15</th>\n",
       "      <td>900.208</td>\n",
       "      <td>2610.30</td>\n",
       "      <td>3068.05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-16</th>\n",
       "      <td>903.912</td>\n",
       "      <td>2616.10</td>\n",
       "      <td>3077.22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-17</th>\n",
       "      <td>911.390</td>\n",
       "      <td>2635.96</td>\n",
       "      <td>3069.35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-18</th>\n",
       "      <td>917.929</td>\n",
       "      <td>2670.71</td>\n",
       "      <td>3134.92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-21</th>\n",
       "      <td>921.649</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3125.07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-22</th>\n",
       "      <td>914.158</td>\n",
       "      <td>2632.90</td>\n",
       "      <td>3112.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-23</th>\n",
       "      <td>912.362</td>\n",
       "      <td>2638.70</td>\n",
       "      <td>3112.13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-24</th>\n",
       "      <td>916.293</td>\n",
       "      <td>2642.33</td>\n",
       "      <td>3126.31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-25</th>\n",
       "      <td>919.797</td>\n",
       "      <td>2664.76</td>\n",
       "      <td>3163.24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-28</th>\n",
       "      <td>920.266</td>\n",
       "      <td>2643.85</td>\n",
       "      <td>3137.27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-29</th>\n",
       "      <td>926.769</td>\n",
       "      <td>2640.00</td>\n",
       "      <td>3153.42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-30</th>\n",
       "      <td>926.771</td>\n",
       "      <td>2681.05</td>\n",
       "      <td>3161.74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-31</th>\n",
       "      <td>929.254</td>\n",
       "      <td>2704.10</td>\n",
       "      <td>3159.43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-02-01</th>\n",
       "      <td>926.219</td>\n",
       "      <td>2706.53</td>\n",
       "      <td>3171.12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-02-04</th>\n",
       "      <td>932.023</td>\n",
       "      <td>2724.87</td>\n",
       "      <td>3165.20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-02-05</th>\n",
       "      <td>937.821</td>\n",
       "      <td>2737.70</td>\n",
       "      <td>3215.04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-02-06</th>\n",
       "      <td>938.068</td>\n",
       "      <td>2731.61</td>\n",
       "      <td>3212.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-02-07</th>\n",
       "      <td>937.612</td>\n",
       "      <td>2706.05</td>\n",
       "      <td>3150.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-02-08</th>\n",
       "      <td>932.983</td>\n",
       "      <td>2707.88</td>\n",
       "      <td>3135.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-02-11</th>\n",
       "      <td>929.151</td>\n",
       "      <td>2709.80</td>\n",
       "      <td>3165.61</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-02-12</th>\n",
       "      <td>933.154</td>\n",
       "      <td>2744.73</td>\n",
       "      <td>3190.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-20</th>\n",
       "      <td>1048.497</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3799.03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-21</th>\n",
       "      <td>1047.136</td>\n",
       "      <td>3320.79</td>\n",
       "      <td>3789.12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-22</th>\n",
       "      <td>1049.407</td>\n",
       "      <td>3321.75</td>\n",
       "      <td>3769.79</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-23</th>\n",
       "      <td>1051.691</td>\n",
       "      <td>3325.54</td>\n",
       "      <td>3736.85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-24</th>\n",
       "      <td>1047.965</td>\n",
       "      <td>3295.47</td>\n",
       "      <td>3779.16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-27</th>\n",
       "      <td>1041.188</td>\n",
       "      <td>3243.63</td>\n",
       "      <td>3677.84</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-28</th>\n",
       "      <td>1045.145</td>\n",
       "      <td>3276.24</td>\n",
       "      <td>3719.22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-29</th>\n",
       "      <td>1044.823</td>\n",
       "      <td>3273.40</td>\n",
       "      <td>3736.36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-30</th>\n",
       "      <td>1044.444</td>\n",
       "      <td>3283.66</td>\n",
       "      <td>3690.78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-31</th>\n",
       "      <td>1032.969</td>\n",
       "      <td>3225.52</td>\n",
       "      <td>3640.91</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-02-03</th>\n",
       "      <td>1036.002</td>\n",
       "      <td>3248.92</td>\n",
       "      <td>3661.27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-02-04</th>\n",
       "      <td>1045.176</td>\n",
       "      <td>3297.59</td>\n",
       "      <td>3732.28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-02-05</th>\n",
       "      <td>1052.456</td>\n",
       "      <td>3334.69</td>\n",
       "      <td>3777.84</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-02-06</th>\n",
       "      <td>1058.649</td>\n",
       "      <td>3345.78</td>\n",
       "      <td>3805.52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-02-07</th>\n",
       "      <td>1053.031</td>\n",
       "      <td>3327.71</td>\n",
       "      <td>3798.49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-02-10</th>\n",
       "      <td>1058.922</td>\n",
       "      <td>3352.09</td>\n",
       "      <td>3793.18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-02-11</th>\n",
       "      <td>1060.467</td>\n",
       "      <td>3357.75</td>\n",
       "      <td>3825.84</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-02-12</th>\n",
       "      <td>1064.594</td>\n",
       "      <td>3379.45</td>\n",
       "      <td>3854.43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-02-13</th>\n",
       "      <td>1063.573</td>\n",
       "      <td>3373.94</td>\n",
       "      <td>3846.74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-02-14</th>\n",
       "      <td>1064.875</td>\n",
       "      <td>3380.16</td>\n",
       "      <td>3840.97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-02-17</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3853.27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-02-18</th>\n",
       "      <td>1064.280</td>\n",
       "      <td>3370.29</td>\n",
       "      <td>3836.54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-02-19</th>\n",
       "      <td>1068.283</td>\n",
       "      <td>3386.15</td>\n",
       "      <td>3865.18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-02-20</th>\n",
       "      <td>1069.292</td>\n",
       "      <td>3373.23</td>\n",
       "      <td>3822.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-02-21</th>\n",
       "      <td>1062.857</td>\n",
       "      <td>3337.75</td>\n",
       "      <td>3800.38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-02-24</th>\n",
       "      <td>1046.038</td>\n",
       "      <td>3225.89</td>\n",
       "      <td>3647.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-02-25</th>\n",
       "      <td>1023.718</td>\n",
       "      <td>3128.21</td>\n",
       "      <td>3572.51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-02-26</th>\n",
       "      <td>1016.165</td>\n",
       "      <td>3116.39</td>\n",
       "      <td>3577.68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-02-27</th>\n",
       "      <td>997.730</td>\n",
       "      <td>2978.76</td>\n",
       "      <td>3455.92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-02-28</th>\n",
       "      <td>972.149</td>\n",
       "      <td>2954.22</td>\n",
       "      <td>3329.49</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>300 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "ID          SPTSX60 Index  SPX Index  SX5E Index\n",
       "DATE                                            \n",
       "2019-01-02        859.801    2510.03     2993.18\n",
       "2019-01-03        851.451    2447.89     2954.66\n",
       "2019-01-04        864.443    2531.94     3041.85\n",
       "2019-01-07        868.547    2549.69     3033.64\n",
       "2019-01-08        874.424    2574.41     3054.94\n",
       "2019-01-09        885.913    2584.96     3070.24\n",
       "2019-01-10        891.718    2596.64     3075.73\n",
       "2019-01-11        893.385    2596.26     3070.04\n",
       "2019-01-14        895.136    2582.61     3055.18\n",
       "2019-01-15        900.208    2610.30     3068.05\n",
       "2019-01-16        903.912    2616.10     3077.22\n",
       "2019-01-17        911.390    2635.96     3069.35\n",
       "2019-01-18        917.929    2670.71     3134.92\n",
       "2019-01-21        921.649        NaN     3125.07\n",
       "2019-01-22        914.158    2632.90     3112.80\n",
       "2019-01-23        912.362    2638.70     3112.13\n",
       "2019-01-24        916.293    2642.33     3126.31\n",
       "2019-01-25        919.797    2664.76     3163.24\n",
       "2019-01-28        920.266    2643.85     3137.27\n",
       "2019-01-29        926.769    2640.00     3153.42\n",
       "2019-01-30        926.771    2681.05     3161.74\n",
       "2019-01-31        929.254    2704.10     3159.43\n",
       "2019-02-01        926.219    2706.53     3171.12\n",
       "2019-02-04        932.023    2724.87     3165.20\n",
       "2019-02-05        937.821    2737.70     3215.04\n",
       "2019-02-06        938.068    2731.61     3212.75\n",
       "2019-02-07        937.612    2706.05     3150.76\n",
       "2019-02-08        932.983    2707.88     3135.62\n",
       "2019-02-11        929.151    2709.80     3165.61\n",
       "2019-02-12        933.154    2744.73     3190.75\n",
       "...                   ...        ...         ...\n",
       "2020-01-20       1048.497        NaN     3799.03\n",
       "2020-01-21       1047.136    3320.79     3789.12\n",
       "2020-01-22       1049.407    3321.75     3769.79\n",
       "2020-01-23       1051.691    3325.54     3736.85\n",
       "2020-01-24       1047.965    3295.47     3779.16\n",
       "2020-01-27       1041.188    3243.63     3677.84\n",
       "2020-01-28       1045.145    3276.24     3719.22\n",
       "2020-01-29       1044.823    3273.40     3736.36\n",
       "2020-01-30       1044.444    3283.66     3690.78\n",
       "2020-01-31       1032.969    3225.52     3640.91\n",
       "2020-02-03       1036.002    3248.92     3661.27\n",
       "2020-02-04       1045.176    3297.59     3732.28\n",
       "2020-02-05       1052.456    3334.69     3777.84\n",
       "2020-02-06       1058.649    3345.78     3805.52\n",
       "2020-02-07       1053.031    3327.71     3798.49\n",
       "2020-02-10       1058.922    3352.09     3793.18\n",
       "2020-02-11       1060.467    3357.75     3825.84\n",
       "2020-02-12       1064.594    3379.45     3854.43\n",
       "2020-02-13       1063.573    3373.94     3846.74\n",
       "2020-02-14       1064.875    3380.16     3840.97\n",
       "2020-02-17            NaN        NaN     3853.27\n",
       "2020-02-18       1064.280    3370.29     3836.54\n",
       "2020-02-19       1068.283    3386.15     3865.18\n",
       "2020-02-20       1069.292    3373.23     3822.98\n",
       "2020-02-21       1062.857    3337.75     3800.38\n",
       "2020-02-24       1046.038    3225.89     3647.98\n",
       "2020-02-25       1023.718    3128.21     3572.51\n",
       "2020-02-26       1016.165    3116.39     3577.68\n",
       "2020-02-27        997.730    2978.76     3455.92\n",
       "2020-02-28        972.149    2954.22     3329.49\n",
       "\n",
       "[300 rows x 3 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pxlast = data.reset_index().pivot(index = 'DATE', columns ='ID',values ='PX_LAST')\n",
    "pxlast"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "cfedfa0a4e4348ab8a91b359ec7932fe",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox(children=(Box(children=(Figure(animation_duration=500, axes=[Axis(scale=DateScale()), Axis(label_offset='…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "bqv.InteractiveLinePlot(pxlast).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "\u001b[0;31mInit signature:\u001b[0m \u001b[0mbqv\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mInteractiveLinePlot\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mdf\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mfig_style\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;34m'general'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mtitle\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;34m''\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcolors\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'#FFFFFF'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m'#1B84ED'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m'#CF7DFF'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m'#FF5A00'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m'#00D3D6'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m'#008616'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m'#B31D83'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m'#FF1E3E'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m'#FF9E24'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m'#63B2FF'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m'#30C030'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m'#9B016A'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m'#FD5D5D'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m'#AAAAAA'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m'#FBE360'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m'#00A5E3'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m'#35B56C'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m'#AC1BE8'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m'#007575'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m'#D20000'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m'#1E6AB4'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m'#89A4C5'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m'#626262'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mhide_controls\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mFalse\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mtick_format\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mNone\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mlegend\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;34m'default'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0my_min\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mNone\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0my_max\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mNone\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mnum_ticks\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mNone\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0my_label\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mNone\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mx_label\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mNone\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mis_subplot\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mFalse\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
       "\u001b[0;31mDocstring:\u001b[0m     \n",
       "Creates an interactive line plot for the provided dataframe.\n",
       "\n",
       "Parameters\n",
       "----------\n",
       "df: Pandas dataframe\n",
       "    The dataframe to be plotted.\n",
       "title: string (default='')\n",
       "    Optional title to be displayed on the figure.\n",
       "colors: list of strings (default=BB_CLRS)\n",
       "    Colors for the lines on the plot. Colors must be in hex format.\n",
       "hide_controls: Boolean (default=False)\n",
       "    Hides the defualt control widgets\n",
       "legend: string (values: 'default', 'outside') or None\n",
       "    Turns off the legend, or draws it outside of the figure.\n",
       "tick_format: string (default=None)\n",
       "    The tick format for the Y axis. Example: '0.2f'\n",
       "num_ticks: int (default=None)\n",
       "    Sets the number of ticks on the X axis.\n",
       "y_label: string (default=None)\n",
       "    Sets a label for the Y axis.\n",
       "x_label: string (default=None)\n",
       "    Sets a label for the X axis.\n",
       "\n",
       "Attributes\n",
       "----------\n",
       "cols: list of strings\n",
       "    Sets the list of columns that is currently displayed on the figure\n",
       "start: value from datafrane index\n",
       "    Sets the start point of the displayed values\n",
       "end:\n",
       "    Sets the end point of the displayed values\n",
       "range: list of 2 values from the dataframe index\n",
       "    Sets the start and end point of the displayed values\n",
       "width: string (default='100%')\n",
       "    Sets the figure width. Accepts values as % or px. Note: BQPlot enforces\n",
       "    some aspect ratio guidelines that may alter the provided value.\n",
       "height: string (default=None)\n",
       "    Sets the figure height. Accepts values as % or px. Note: BQPlot enforces\n",
       "    some aspect ratio guidelines that may alter the provided value.\n",
       "selected_data: Pandas dataframe\n",
       "    A dataframe of the data currently selected in the figure\n",
       "mark: bqplot.Lines\n",
       "    Returns the BQPlot Lines object\n",
       "figure: bqplot.Figure\n",
       "    Returns the BQPlot Figure object\n",
       "xscale: bqplot.Scale\n",
       "    Returns the BQPlot Scale object that corresponds to the X scale\n",
       "yscale: bqplot.Scale\n",
       "    Returns the BQPlot Scale object that corresponds to the Y scale\n",
       "xaxis: bqplot.Axis\n",
       "    Returns the BQPlot Axis object that corresponds to the X axis\n",
       "yaxis: bqplot.Axis\n",
       "    Returns the BQPlot Axis object that corresponds to the Y axis\n",
       "\n",
       "Widgets\n",
       "----------\n",
       "line_control: Lines displayed from the dataframe\n",
       "start_control: Start of displayed data\n",
       "end_control: End of displayed data\n",
       "\u001b[0;31mFile:\u001b[0m           c:\\blp\\bquant\\environments\\bqnt-1.281.1\\lib\\site-packages\\bqviz\\interactive_plots.py\n",
       "\u001b[0;31mType:\u001b[0m           ABCMeta\n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "bqv.InteractiveLinePlot?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Options Example"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [],
   "source": [
    "request8 = \"\"\"\n",
    "get(\n",
    "     OPEN_INT\n",
    ")\n",
    "for(\n",
    "     filter(\n",
    "            Options(['AAPL US EQUITY']), \n",
    "            PUT_CALL==CALL AND \n",
    "            EXPIRE_DT==2020-04-17 AND \n",
    "            OPEN_INT >0\n",
    "))\t\t\t\t\t\t\t\t\t\t\t\t\t\t\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "BQL ERROR: No symbols met filter criteria in FILTER(Options(['AAPL US EQUITY']),PUT_CALL==CALL AND EXPIRE_DT==2020-04-17)., request_id=9536f03f07af443bb9b5ad2a09296d67 payload_id=None\n"
     ]
    },
    {
     "ename": "ResponseError",
     "evalue": "No symbols met filter criteria in FILTER(Options(['AAPL US EQUITY']),PUT_CALL==CALL AND EXPIRE_DT==2020-04-17).",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mResponseError\u001b[0m                             Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-66-a7f9ec6c3ecf>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0mresponse\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mbq\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mrequest8\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m      2\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      3\u001b[0m \u001b[0mdf\u001b[0m \u001b[0;34m=\u001b[0m\u001b[0mbql\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcombined_df\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mresponse\u001b[0m\u001b[0;34m)\u001b[0m    \u001b[0;31m# newdf = response[0].df() if only one field\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      4\u001b[0m \u001b[0mdf\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mhead\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32mC:\\blp\\BQuant\\environments\\bqnt-1.281.1\\lib\\site-packages\\bqutil\\timing_utils.py\u001b[0m in \u001b[0;36mwrapper\u001b[0;34m(*args, **kwargs)\u001b[0m\n\u001b[1;32m    114\u001b[0m                               \u001b[0mpublish\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mpublish\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    115\u001b[0m                               **decorator_kwargs):\n\u001b[0;32m--> 116\u001b[0;31m                 \u001b[0;32mreturn\u001b[0m \u001b[0mfunc\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m*\u001b[0m\u001b[0margs\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    117\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    118\u001b[0m         \u001b[0;32mreturn\u001b[0m \u001b[0mwrapper\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32mC:\\blp\\BQuant\\environments\\bqnt-1.281.1\\lib\\site-packages\\bql\\service.py\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, request, callback, error_callback)\u001b[0m\n\u001b[1;32m    241\u001b[0m             \u001b[0;31m# library-internal stack frames when they get an error from the\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    242\u001b[0m             \u001b[0;31m# BQL backend.\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 243\u001b[0;31m             \u001b[0;32mraise\u001b[0m \u001b[0mex\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    244\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    245\u001b[0m     \u001b[0;34m@\u001b[0m\u001b[0mtrace_it\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0m_logger\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32mC:\\blp\\BQuant\\environments\\bqnt-1.281.1\\lib\\site-packages\\bql\\service.py\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, request, callback, error_callback)\u001b[0m\n\u001b[1;32m    235\u001b[0m                     \u001b[0mrequest\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    236\u001b[0m                     \u001b[0mcallback\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 237\u001b[0;31m                     error_callback)\n\u001b[0m\u001b[1;32m    238\u001b[0m         \u001b[0;32mexcept\u001b[0m \u001b[0mResponseError\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0mex\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    239\u001b[0m             \u001b[0;31m# Deliberately re-raise the exception here with a truncated stack\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32mC:\\blp\\BQuant\\environments\\bqnt-1.281.1\\lib\\site-packages\\bql\\request\\request_executor.py\u001b[0m in \u001b[0;36mexecute_string\u001b[0;34m(self, request_string, callback, error_callback)\u001b[0m\n\u001b[1;32m    238\u001b[0m                                      \u001b[0mitem_names\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mNone\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    239\u001b[0m                                      \u001b[0mcallback\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mcallback\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 240\u001b[0;31m                                      error_callback=error_callback)\n\u001b[0m\u001b[1;32m    241\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    242\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0msubmit\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mrequest\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mpreferences\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcallback\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mNone\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0merror_callback\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mNone\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32mC:\\blp\\BQuant\\environments\\bqnt-1.281.1\\lib\\site-packages\\bql\\request\\request_executor.py\u001b[0m in \u001b[0;36m__execute_string\u001b[0;34m(self, request_string, item_names, callback, error_callback)\u001b[0m\n\u001b[1;32m    460\u001b[0m                                                \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_execute_async_impl\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    461\u001b[0m                                                \u001b[0mcallback\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 462\u001b[0;31m                                                error_callback)\n\u001b[0m\u001b[1;32m    463\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    464\u001b[0m     def __submit_string(self,\n",
      "\u001b[0;32mC:\\blp\\BQuant\\environments\\bqnt-1.281.1\\lib\\site-packages\\bql\\request\\request_executor.py\u001b[0m in \u001b[0;36m__route_request\u001b[0;34m(action, request, execution_context, sync_function, async_function, callback, error_callback)\u001b[0m\n\u001b[1;32m    526\u001b[0m             \u001b[0mresult\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mpromise\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    527\u001b[0m         \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 528\u001b[0;31m             \u001b[0mresponse\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0msender\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0msync_send\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0msync_function\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    529\u001b[0m             \u001b[0mresult\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mresponse\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    530\u001b[0m         \u001b[0;32mreturn\u001b[0m \u001b[0mresult\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32mC:\\blp\\BQuant\\environments\\bqnt-1.281.1\\lib\\site-packages\\bql\\request\\request_executor.py\u001b[0m in \u001b[0;36msync_send\u001b[0;34m(self, function)\u001b[0m\n\u001b[1;32m    131\u001b[0m         \u001b[0;32mexcept\u001b[0m \u001b[0mException\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0mex\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    132\u001b[0m             \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m__log_failure\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mex\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 133\u001b[0;31m             \u001b[0;32mraise\u001b[0m \u001b[0mex\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    134\u001b[0m         \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    135\u001b[0m             \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m__log_success\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32mC:\\blp\\BQuant\\environments\\bqnt-1.281.1\\lib\\site-packages\\bql\\request\\request_executor.py\u001b[0m in \u001b[0;36msync_send\u001b[0;34m(self, function)\u001b[0m\n\u001b[1;32m    128\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0msync_send\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mfunction\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    129\u001b[0m         \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 130\u001b[0;31m             \u001b[0mresponse\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mfunction\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m__request\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m__execution_context\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    131\u001b[0m         \u001b[0;32mexcept\u001b[0m \u001b[0mException\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0mex\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    132\u001b[0m             \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m__log_failure\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mex\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32mC:\\blp\\BQuant\\environments\\bqnt-1.281.1\\lib\\site-packages\\bql\\request\\request_executor.py\u001b[0m in \u001b[0;36m_execute_impl\u001b[0;34m(self, request, execution_context)\u001b[0m\n\u001b[1;32m    307\u001b[0m         json_fragments = [self._demarshal_response('execute', r)\n\u001b[1;32m    308\u001b[0m                           for r in partial_responses]\n\u001b[0;32m--> 309\u001b[0;31m         \u001b[0;32mreturn\u001b[0m \u001b[0mResponse\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mfrom_execute_response\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mjson_fragments\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mexecution_context\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    310\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    311\u001b[0m     def _execute_async_impl(self,\n",
      "\u001b[0;32mC:\\blp\\BQuant\\environments\\bqnt-1.281.1\\lib\\site-packages\\bql\\request\\response.py\u001b[0m in \u001b[0;36mfrom_execute_response\u001b[0;34m(cls, json_fragments, execution_context, raw)\u001b[0m\n\u001b[1;32m     65\u001b[0m             \u001b[0mResponse\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_SUPPORTED_BQLSVC_SCHEMA_MAJOR_VERSION\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     66\u001b[0m             \u001b[0mexecution_context\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 67\u001b[0;31m             \u001b[0mraw\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     68\u001b[0m         )\n\u001b[1;32m     69\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32mC:\\blp\\BQuant\\environments\\bqnt-1.281.1\\lib\\site-packages\\bql\\request\\response.py\u001b[0m in \u001b[0;36m__from_response\u001b[0;34m(cls, json_fragments, expected_major_version, execution_context, raw)\u001b[0m\n\u001b[1;32m     88\u001b[0m         \u001b[0mjson_response\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;34m''\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mjoin\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mjson_fragments\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     89\u001b[0m         \u001b[0mresponse\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mjson\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mloads\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mjson_response\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 90\u001b[0;31m         \u001b[0m_check_response\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mresponse\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mexpected_major_version\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     91\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0;34m'ordering'\u001b[0m \u001b[0;32min\u001b[0m \u001b[0mresponse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     92\u001b[0m             \u001b[0;31m# There is ordering information in the response.  First walk\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32mC:\\blp\\BQuant\\environments\\bqnt-1.281.1\\lib\\site-packages\\bql\\request\\response_util.py\u001b[0m in \u001b[0;36m_check_response\u001b[0;34m(response, expected_major_version)\u001b[0m\n\u001b[1;32m      9\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     10\u001b[0m \u001b[0;32mdef\u001b[0m \u001b[0m_check_response\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mresponse\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mexpected_major_version\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 11\u001b[0;31m     \u001b[0m__check_exceptions\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mresponse\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     12\u001b[0m     \u001b[0m__check_version\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mresponse\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mexpected_major_version\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     13\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32mC:\\blp\\BQuant\\environments\\bqnt-1.281.1\\lib\\site-packages\\bql\\request\\response_util.py\u001b[0m in \u001b[0;36m__check_exceptions\u001b[0;34m(response)\u001b[0m\n\u001b[1;32m     28\u001b[0m     \u001b[0mresponse_exceptions\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mresponse\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mget\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'responseExceptions'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     29\u001b[0m     \u001b[0;32mif\u001b[0m \u001b[0mresponse_exceptions\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 30\u001b[0;31m         \u001b[0;32mraise\u001b[0m \u001b[0mResponseError\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mresponse_exceptions\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     31\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mResponseError\u001b[0m: No symbols met filter criteria in FILTER(Options(['AAPL US EQUITY']),PUT_CALL==CALL AND EXPIRE_DT==2020-04-17)."
     ]
    }
   ],
   "source": [
    "response = bq.execute(request8)\n",
    "\n",
    "df =bql.combined_df(response)    # newdf = response[0].df() if only one field\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div style=\"text-align: right\"><a href=\"#Sheet-Navigation\">Return to Top ↑</a></div>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Object Model"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#import bql\n",
    "#bq = bql.Service()\n",
    "\n",
    "#fields\n",
    "bql_item = bq.data.px_last()\n",
    "\n",
    "#securities\n",
    "bql_universe = 'UBER US Equity'\n",
    "\n",
    "#build & execute request\n",
    "bql_request = bql.Request(bql_universe, bql_item)\n",
    "bql_response = bq.execute(bql_request)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#display result\n",
    "df = bql.combined_df(bql_response)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "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.7.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}