1 BQL Concepts.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"bqlc-top\"></a>\n",
    "<div style=\"width: 50px; height: 50px; border-radius: 50%; background: #F1C40F; margin-bottom: 0px; text-align: center;\">\n",
    "    <img src=\"../img/Welcome Page/launch.png\" style=\"max-height: 95%; max-width: 95%;\">\n",
    "</div>\n",
    "<h1 style=\"margin-top: 20px; margin-bottom: 5px;\">BQL Concepts<br><span style=\"color: orange; margin-bottom: 0px;\">Analyzing Data with BQL</span></h1>\n",
    "<h4 style=\"color: orange; margin-top: 0px;\">(20-min Read)</h4>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "By the end of this document, you will be able to:\n",
    "1. Chain BQL items to form complex queries\n",
    "1. Effectively use the `members()` and `filter()` universe handlers\n",
    "1. Understand the `group()` function for aggregating data\n",
    "1. Perform a few statistical calculations in BQL"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Contents\n",
    "- [The BQuant Project Flow](#project-flow-bqlc)\n",
    "- [BQL Refresh](#bqlc-ref)\n",
    "- [Chaining BQL Objects](#bqlc-chain)\n",
    "- [Universe Handlers](#bqlc-univ)\n",
    "- [Aggregate Data with the `group()` function](#bqlc-data)\n",
    "- [Using the BQL Editor](#bqlc-editor)\n",
    "- [The BQL Reference Guide](#bqlc-guide)\n",
    "\n",
    "<a href=\"../1. Boot Camp/4 Boot Camp Project.ipynb\">&larr; Back to Boot Camp: Final Project</a>&emsp; | &emsp;\n",
    "<a href=\"2 DataGrids.ipynb\">Continue to Launch: DataGrids &rarr;</a><br>\n",
    "<a href=\"../Welcome.ipynb#welcome-top\" style=\"font-size: 12px;\">Return to the Welcome Page</a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "<a id=\"project-flow-bqlc\"></a>\n",
    "<h1>BQuant Project Flow<br><span style=\"color: orange;\">5-Step Process for Developing Your Projects</span></h1>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The 5-step project flow for BQuant development was mentioned in the Intro to BQuant section, but it bears repeating here. Every project you develop in BQuant will follow the same basic process below.\n",
    "\n",
    "1. Setup Environment\n",
    "1. Get Data from Bloomberg using BQL\n",
    "1. Additional Data Munging (if necessary)\n",
    "1. User Interface & Visualizations\n",
    "1. Simplify, Document, & Publish\n",
    "\n",
    "In this notebook, we cover functions and universes of the Bloomberg Query Language (BQL) for use in Steps 1 and 2."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[&uarr; Return to Top](#bqlc-top)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "<a id=\"bqlc-ref\"></a>\n",
    "<h1>BQL Refresh<br><span style=\"color: orange;\">Getting Data From Bloomberg</span></h1>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The Bloomberg Query Language (BQL) is the most effective way to retrieve, manipulate, and analyze data in the BQuant environment. You can think of BQL as your access point to Bloomberg's wealth of information. Inside the BQuant environment, you will use BQL to send requests for information to Bloomberg servers and receive a packet of data in return. Bloomberg enginners have created the `bql` Python library for building queries and interacting with the BQL API. At the beginning of every project, `import bql` and establish a connection to Bloomberg servers by initializing a Service object with `bq = bql.Service()`.\n",
    "\n",
    "All BQL requests need at least two arguments: the type of data, and the universe. You can think of BQL queries as having two statements: `GET` and `FOR`. If you need daily trading volume for Apple stock, you are asking BQL to \"get\" volume data \"for\" Apple. All BQL queries will follow this basic structure: `GET <data type> FOR <universe>`. In the cell below, we retrieve closing prices of the CAC 40 Index for the last 5 days.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "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>Closing Prices</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ID</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>FR Index</th>\n",
       "      <td>2021-04-03</td>\n",
       "      <td>USD</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FR Index</th>\n",
       "      <td>2021-04-04</td>\n",
       "      <td>USD</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FR Index</th>\n",
       "      <td>2021-04-05</td>\n",
       "      <td>USD</td>\n",
       "      <td>1332.15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FR Index</th>\n",
       "      <td>2021-04-06</td>\n",
       "      <td>USD</td>\n",
       "      <td>1342.08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FR Index</th>\n",
       "      <td>2021-04-07</td>\n",
       "      <td>USD</td>\n",
       "      <td>1349.23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FR Index</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>USD</td>\n",
       "      <td>1352.16</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               DATE CURRENCY  Closing Prices\n",
       "ID                                          \n",
       "FR Index 2021-04-03      USD             NaN\n",
       "FR Index 2021-04-04      USD             NaN\n",
       "FR Index 2021-04-05      USD         1332.15\n",
       "FR Index 2021-04-06      USD         1342.08\n",
       "FR Index 2021-04-07      USD         1349.23\n",
       "FR Index 2021-04-08      USD         1352.16"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Run this cell with the Run button in the Jupyter toolbar\n",
    "# Step 1: Setup Environment\n",
    "import bql\n",
    "bq = bql.Service()\n",
    "\n",
    "# Step 2: Get Data with BQL\n",
    "universe = 'FR Index'\n",
    "date_range = bq.func.range('-5D', '0D')\n",
    "prices = bq.data.px_last(dates=date_range)\n",
    "req = bql.Request(universe, {'Closing Prices': prices})   # <-- build request; GET prices FOR universe\n",
    "res = bq.execute(req)\n",
    "\n",
    "# display result as a DataFrame\n",
    "res[0].df()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[&uarr; Return to Top](#bqlc-top)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "<a id=\"bqlc-chain\"></a>\n",
    "<h1>Chaining BQL Objects<br><span style=\"color: orange;\">Performing Functions on Queries</span></h1>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "When you initialize a BQL item, either by calling `bq.func` or `bq.data`, behind the scenes Python creates an instance of a special class called `BqlItemFactory`. You can see this yourself by clicking on `range` or `px_last` above and using the key combination `Shift + Tab` to show BQuant's Contextual Help.\n",
    "\n",
    "<h5 style=\"color: orange; margin-bottom: 0px;\">Image: BQuant Contextual Help</h5>\n",
    "<img src=\"../img/Launch/contextual_help.png\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You don't need to know all the details of how the `BqlItemFactory` class works, but it's important to keep in mind that all of these objects are of the same type. When you run `bq.func.range()`, you create the same type of object as when you run `bq.data.px_last()`. All BQL items can build on each other, meaning you don't need to explicitly call `bq.data` or `bq.func` to perform a calculation on an already existing BQL item. We can \"chain\" these items together to build complex queries.\n",
    "\n",
    "Consider the code examples below. These will return identical results, but the second example is much cleaner."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h4 style=\"color: orange;\">Code Example 1</h4>"
   ]
  },
  {
   "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>Closing Prices</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ID</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>FR Index</th>\n",
       "      <td>2021-04-05</td>\n",
       "      <td>USD</td>\n",
       "      <td>1332.15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FR Index</th>\n",
       "      <td>2021-04-06</td>\n",
       "      <td>USD</td>\n",
       "      <td>1342.08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FR Index</th>\n",
       "      <td>2021-04-07</td>\n",
       "      <td>USD</td>\n",
       "      <td>1349.23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FR Index</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>USD</td>\n",
       "      <td>1352.38</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               DATE CURRENCY  Closing Prices\n",
       "ID                                          \n",
       "FR Index 2021-04-05      USD         1332.15\n",
       "FR Index 2021-04-06      USD         1342.08\n",
       "FR Index 2021-04-07      USD         1349.23\n",
       "FR Index 2021-04-08      USD         1352.38"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Run this cell with the Run button in the Jupyter toolbar\n",
    "# Step 1: Setup Environment\n",
    "# import bql\n",
    "# bq = bql.Service()\n",
    "\n",
    "# Step 2: Get Data with BQL\n",
    "universe = 'FR Index'\n",
    "prices = bq.data.px_last('-5D', '0D')\n",
    "drop_null_values = bq.func.dropna\n",
    "drop_missing_prices = drop_null_values(prices)\n",
    "\n",
    "# build & execute Request\n",
    "req = bql.Request(universe, {'Closing Prices': drop_missing_prices})\n",
    "res = bq.execute(req)\n",
    "\n",
    "# display as DataFrame\n",
    "res[0].df()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h4 style=\"color: orange;\">Code Example 2 - Chain the BQL items</h4>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>Closing Prices</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ID</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>FR Index</th>\n",
       "      <td>2021-03-12</td>\n",
       "      <td>USD</td>\n",
       "      <td>1336.70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FR Index</th>\n",
       "      <td>2021-03-15</td>\n",
       "      <td>USD</td>\n",
       "      <td>1331.53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FR Index</th>\n",
       "      <td>2021-03-16</td>\n",
       "      <td>USD</td>\n",
       "      <td>1333.29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FR Index</th>\n",
       "      <td>2021-03-17</td>\n",
       "      <td>USD</td>\n",
       "      <td>1333.57</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               DATE CURRENCY  Closing Prices\n",
       "ID                                          \n",
       "FR Index 2021-03-12      USD         1336.70\n",
       "FR Index 2021-03-15      USD         1331.53\n",
       "FR Index 2021-03-16      USD         1333.29\n",
       "FR Index 2021-03-17      USD         1333.57"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Run this cell with the Run button in the Jupyter toolbar\n",
    "# Step 1: Setup Environment\n",
    "# import bql\n",
    "# bq = bql.Service()\n",
    "\n",
    "# Step 2: Get Data with BQL\n",
    "universe = 'FR Index'\n",
    "prices = bq.data.px_last('-5D', '0D').dropna()    # <-- chain BQL items together\n",
    "\n",
    "# build & execute Request\n",
    "req = bql.Request(universe, {'Closing Prices': drop_missing_prices})\n",
    "res = bq.execute(req)\n",
    "\n",
    "# display as DataFrame\n",
    "res[0].df()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In the above example, we chained BQL items together to perform a function on a data item. Chaining here saved us a few lines of code; that might not seem like much for a simple query like this, but get in the habit of chaining BQL items together to save yourself a lot of work for more complex queries!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div style=\"border: 1px dotted orange; padding-left: 10px; padding-bottom: 10px;\">\n",
    "    <h3>&#9998; Try it out</h3>\n",
    "    <p>\n",
    "        Practice Chaining BQL Items\n",
    "        <ol>\n",
    "            <li>Add a new Jupyter code cell below this one</li>\n",
    "            <li>Declare a <code>universe</code> variable and set it equal to the Bloomberg ticker for your favorite equity index.</li>\n",
    "            <li>Declare a second variable <code>prices</code> to retrieve the last 30 days of closing prices.</li>\n",
    "            <li>Declare a third variable named <code>avg_price</code> and chain the <code>bq.func.avg()</code> item onto the end of the <code>prices</code> variable you declared in Step 3. This will calculate the average price over the time series.</li>\n",
    "            <li>Build and execute a request for <code>avg_price</code>. Display the results as a DataFrame. If successful, the query should only return one row.</li>\n",
    "        </ol>\n",
    "    We've written one possible solution to this problem in a hidden Python cell below. When you're ready to get a peek at an answer, click the <img src=\"../img/Controls/hidden_cell.png\"> below.\n",
    "    </p>\n",
    "</div>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style=\"color: orange; font-size: 12px;\">&darr; Click below to expand answer</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "jupyter": {
     "source_hidden": true
    }
   },
   "outputs": [],
   "source": [
    "# import bql\n",
    "# bq = bql.Service()\n",
    "\n",
    "# Step 2\n",
    "universe = 'JP Index'  # <-- Bloomberg Japan Large & Mid Cap Price Return Index USD \n",
    "\n",
    "# Step 3\n",
    "prices = bq.data.px_last('-30D', '0D')\n",
    "\n",
    "# Step 4\n",
    "avg_price = prices.avg()\n",
    "\n",
    "# Step 5\n",
    "request = bql.Request(universe, {'Average 30D Price': avg_price})\n",
    "response = bq.execute(request)\n",
    "df = response[0].df()\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[&uarr; Return to Top](#bqlc-top)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "<a id=\"bqlc-univ\"></a>\n",
    "<h1>Universe Handlers<br><span style=\"color: orange;\">Refine Queries with <code style=\"color: orange; background-color: transparent;\">members()</code> and <code style=\"color: orange; background-color: transparent;\">filter()</code></span></h1>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In most of your BQL queries, the `GET` argument will be more complex than the `FOR` universe. However, there are a few special functions built for universes called \"universe handlers.\" The most common universe handlers are `members()` and `filter()`. \n",
    "\n",
    "  - `members()` allows you to access constituent data on an index, portfolio, or fund. BQL can then pull data or run functions on the underlying members.\n",
    "  - `filter()` will limit the results returned based on given criteria"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h4 style=\"color: orange;\">Members of an Index</h4>\n",
    "<p>The code below uses the <code>members()</code> handler to pull the most recent price for every member of the Bloomberg US Large Cap Index.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>Price</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ID</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>LYB UN Equity</th>\n",
       "      <td>2021-03-17</td>\n",
       "      <td>USD</td>\n",
       "      <td>108.250000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AXP UN Equity</th>\n",
       "      <td>2021-03-17</td>\n",
       "      <td>USD</td>\n",
       "      <td>144.229996</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>VZ UN Equity</th>\n",
       "      <td>2021-03-17</td>\n",
       "      <td>USD</td>\n",
       "      <td>55.790001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AVGO UW Equity</th>\n",
       "      <td>2021-03-17</td>\n",
       "      <td>USD</td>\n",
       "      <td>485.720001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BA UN Equity</th>\n",
       "      <td>2021-03-17</td>\n",
       "      <td>USD</td>\n",
       "      <td>261.470001</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     DATE CURRENCY       Price\n",
       "ID                                            \n",
       "LYB UN Equity  2021-03-17      USD  108.250000\n",
       "AXP UN Equity  2021-03-17      USD  144.229996\n",
       "VZ UN Equity   2021-03-17      USD   55.790001\n",
       "AVGO UW Equity 2021-03-17      USD  485.720001\n",
       "BA UN Equity   2021-03-17      USD  261.470001"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Run this cell with the Run button in the Jupyter toolbar\n",
    "# Step 1: Setup Environment\n",
    "# import bql\n",
    "# bq = bql.Service()\n",
    "\n",
    "# Step 2: Get Data with BQL\n",
    "b500_members = bq.univ.members('B500T Index')\n",
    "latest_price = bq.data.px_last(fill='prev')       # <-- use fill='prev' to use the most recently available price if the stock has not traded recently\n",
    "req = bql.Request(b500_members, {'Price': latest_price})\n",
    "res = bq.execute(req)\n",
    "\n",
    "# display first 5 rows of DataFrame\n",
    "res[0].df().head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h4 style=\"color: orange;\">Members of a Fund</h4>\n",
    "<p>Use <code>type='holdings'</code> and <code>bq.func.id()</code> to access holdings data of an ETF.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "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>POSITION</th>\n",
       "      <th>WEIGHTS</th>\n",
       "      <th>AS_OF_DATE</th>\n",
       "      <th>REPORTED_MKT_VAL</th>\n",
       "      <th>CURRENCY</th>\n",
       "      <th>LATEST_CHANGE</th>\n",
       "      <th>ORIG_IDS</th>\n",
       "      <th>ID()</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>AAPL US Equity</th>\n",
       "      <td>171835854.0</td>\n",
       "      <td>7.438908</td>\n",
       "      <td>2020-11-24</td>\n",
       "      <td>1.979034e+10</td>\n",
       "      <td>USD</td>\n",
       "      <td>811020.0</td>\n",
       "      <td>SPY US Equity</td>\n",
       "      <td>AAPL US Equity</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MSFT US Equity</th>\n",
       "      <td>80888141.0</td>\n",
       "      <td>6.502352</td>\n",
       "      <td>2020-11-24</td>\n",
       "      <td>1.729874e+10</td>\n",
       "      <td>USD</td>\n",
       "      <td>381780.0</td>\n",
       "      <td>SPY US Equity</td>\n",
       "      <td>MSFT US Equity</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AMZN US Equity</th>\n",
       "      <td>4550976.0</td>\n",
       "      <td>5.333902</td>\n",
       "      <td>2020-11-24</td>\n",
       "      <td>1.419022e+10</td>\n",
       "      <td>USD</td>\n",
       "      <td>21504.0</td>\n",
       "      <td>SPY US Equity</td>\n",
       "      <td>AMZN US Equity</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FB US Equity</th>\n",
       "      <td>25698610.0</td>\n",
       "      <td>2.674977</td>\n",
       "      <td>2020-11-24</td>\n",
       "      <td>7.116459e+09</td>\n",
       "      <td>USD</td>\n",
       "      <td>121296.0</td>\n",
       "      <td>SPY US Equity</td>\n",
       "      <td>FB US Equity</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GOOGL US Equity</th>\n",
       "      <td>3211411.0</td>\n",
       "      <td>2.129246</td>\n",
       "      <td>2020-11-24</td>\n",
       "      <td>5.664608e+09</td>\n",
       "      <td>USD</td>\n",
       "      <td>15120.0</td>\n",
       "      <td>SPY US Equity</td>\n",
       "      <td>GOOGL US Equity</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    POSITION   WEIGHTS AS_OF_DATE  REPORTED_MKT_VAL CURRENCY  \\\n",
       "ID                                                                             \n",
       "AAPL US Equity   171835854.0  7.438908 2020-11-24      1.979034e+10      USD   \n",
       "MSFT US Equity    80888141.0  6.502352 2020-11-24      1.729874e+10      USD   \n",
       "AMZN US Equity     4550976.0  5.333902 2020-11-24      1.419022e+10      USD   \n",
       "FB US Equity      25698610.0  2.674977 2020-11-24      7.116459e+09      USD   \n",
       "GOOGL US Equity    3211411.0  2.129246 2020-11-24      5.664608e+09      USD   \n",
       "\n",
       "                 LATEST_CHANGE       ORIG_IDS             ID()  \n",
       "ID                                                              \n",
       "AAPL US Equity        811020.0  SPY US Equity   AAPL US Equity  \n",
       "MSFT US Equity        381780.0  SPY US Equity   MSFT US Equity  \n",
       "AMZN US Equity         21504.0  SPY US Equity   AMZN US Equity  \n",
       "FB US Equity          121296.0  SPY US Equity     FB US Equity  \n",
       "GOOGL US Equity        15120.0  SPY US Equity  GOOGL US Equity  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Run this cell with the Run button in the Jupyter toolbar\n",
    "# Step 1: Setup Environment\n",
    "# import bql\n",
    "# bq = bql.Service()\n",
    "\n",
    "# Step 2: Get Data with BQL\n",
    "fund = bq.univ.members('SPY US Equity', type='holdings')\n",
    "req = bql.Request(fund, bq.data.id())\n",
    "res = bq.execute(req)\n",
    "\n",
    "# display first 5 rows of DataFrame\n",
    "res[0].df().head()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h4 style=\"color: orange;\">Filter</h4>\n",
    "<p>Use the <code>filter()</code> function in combination with <code>members()</code> to retrieve P/E ratios for stocks in the Consumer Staples sector."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>AS_OF_DATE</th>\n",
       "      <th>REVISION_DATE</th>\n",
       "      <th>PERIOD_END_DATE</th>\n",
       "      <th>P/E Ratio</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ID</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>KO UN Equity</th>\n",
       "      <td>2021-03-17</td>\n",
       "      <td>2021-02-25</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>26.329439</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PG UN Equity</th>\n",
       "      <td>2021-03-17</td>\n",
       "      <td>2021-01-20</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>22.871585</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>WMT UN Equity</th>\n",
       "      <td>2021-03-17</td>\n",
       "      <td>2021-02-18</td>\n",
       "      <td>2021-01-31</td>\n",
       "      <td>23.965787</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MO UN Equity</th>\n",
       "      <td>2021-03-17</td>\n",
       "      <td>2021-02-17</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>11.434510</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ADM UN Equity</th>\n",
       "      <td>2021-03-17</td>\n",
       "      <td>2021-02-18</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>15.498700</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              AS_OF_DATE REVISION_DATE PERIOD_END_DATE  P/E Ratio\n",
       "ID                                                               \n",
       "KO UN Equity  2021-03-17    2021-02-25      2020-12-31  26.329439\n",
       "PG UN Equity  2021-03-17    2021-01-20      2020-12-31  22.871585\n",
       "WMT UN Equity 2021-03-17    2021-02-18      2021-01-31  23.965787\n",
       "MO UN Equity  2021-03-17    2021-02-17      2020-12-31  11.434510\n",
       "ADM UN Equity 2021-03-17    2021-02-18      2020-12-31  15.498700"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Run this cell with the Run button in the Jupyter toolbar\n",
    "# Step 1: Setup Environment\n",
    "# import bql\n",
    "# bq = bql.Service()\n",
    "\n",
    "# Step 2: Get Data with BQL\n",
    "sector = bq.data.gics_sector_name()\n",
    "b500_members = bq.univ.members('B500T Index')\n",
    "pe = bq.data.pe_ratio(fill='prev')\n",
    "staples_only = b500_members.filter(sector == 'Consumer Staples')\n",
    "\n",
    "req = bql.Request(staples_only, {'P/E Ratio': pe})\n",
    "res = bq.execute(req)\n",
    "\n",
    "# display first 5 rows of DataFrame\n",
    "res[0].df().head()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div style=\"border: 1px dotted orange; padding-left: 10px; padding-bottom: 10px;\">\n",
    "    <h3>&#9998; Try it out</h3>\n",
    "    <p>\n",
    "        Practice with <code>members()</code> and <code>filter()</code>\n",
    "        <ol>\n",
    "            <li>Add a new Jupyter code cell below this one</li>\n",
    "            <li>Declare a variable named <code>universe</code> and set it equal to the members of the <code>'BWORLD Index'</code>.</li>\n",
    "            <li>Write a query to pull the most recent price for members of that index domiciled in Brazil (BR). Hint: use the BQL Editor to find the correct field</li>\n",
    "        </ol>\n",
    "    We've written one possible solution to this problem in a hidden Python cell below. When you're ready to get a peek at an answer, click the <img src=\"../img/Controls/hidden_cell.png\"> below.\n",
    "    </p>\n",
    "</div>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style=\"color: orange; font-size: 12px;\">&darr; Click below to expand answer</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "jupyter": {
     "source_hidden": true
    }
   },
   "outputs": [],
   "source": [
    "# import bql\n",
    "# bq = bql.Service()\n",
    "\n",
    "# Step 2\n",
    "universe = bq.univ.members('BWORLD Index')    # <-- get the members of the index rather than the index itself\n",
    "\n",
    "# Step 3\n",
    "last_price = bq.data.px_last(fill='PREV')   # <-- fill='PREV' will return the most recent market price, ignoring non-trading days\n",
    "country = bq.data.cntry_of_domicile()\n",
    "brazil_stocks = bq.univ.filter(universe, country == 'BR')\n",
    "\n",
    "request = bql.Request(brazil_stocks, {'Closing Price': last_price})\n",
    "response = bq.execute(request)\n",
    "df = response[0].df()\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[&uarr; Return to Top](#bqlc-top)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "<a id=\"bqlc-data\"></a>\n",
    "<h1>Aggregate Data<br><span style=\"color: orange;\">Using the <code style=\"color: orange; background-color: transparent;\">group()</code>function</span></h1>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Consider the code example below. We're trying to find the average P/E ratio for all members of the Bloomberg Europe 500 Index."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>REVISION_DATE</th>\n",
       "      <th>PERIOD_END_DATE</th>\n",
       "      <th>AS_OF_DATE</th>\n",
       "      <th>Average P/E</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ID</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>ENX FP Equity</th>\n",
       "      <td>2021-02-10</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>2021-03-17</td>\n",
       "      <td>17.237364</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BOL SS Equity</th>\n",
       "      <td>2021-03-10</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>2021-03-17</td>\n",
       "      <td>12.910320</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EVD GR Equity</th>\n",
       "      <td>2020-11-19</td>\n",
       "      <td>2020-09-30</td>\n",
       "      <td>2021-03-17</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BNR GR Equity</th>\n",
       "      <td>2021-03-10</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>2021-03-17</td>\n",
       "      <td>21.680090</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>LOGN SW Equity</th>\n",
       "      <td>2021-01-21</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>2021-03-17</td>\n",
       "      <td>14.794507</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               REVISION_DATE PERIOD_END_DATE AS_OF_DATE  Average P/E\n",
       "ID                                                                  \n",
       "ENX FP Equity     2021-02-10      2020-12-31 2021-03-17    17.237364\n",
       "BOL SS Equity     2021-03-10      2020-12-31 2021-03-17    12.910320\n",
       "EVD GR Equity     2020-11-19      2020-09-30 2021-03-17          NaN\n",
       "BNR GR Equity     2021-03-10      2020-12-31 2021-03-17    21.680090\n",
       "LOGN SW Equity    2021-01-21      2020-12-31 2021-03-17    14.794507"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Run this cell with the Run button in the Jupyter toolbar\n",
    "# Step 1: Setup Environment\n",
    "# import bql\n",
    "# bq = bql.Service()\n",
    "\n",
    "# Step 2: Get Data with BQL\n",
    "be500_members = bq.univ.members('BE500 Index')\n",
    "pe = bq.data.pe_ratio(fill='prev')\n",
    "avg_pe = pe.avg()\n",
    "\n",
    "req = bql.Request(be500_members, {'Average P/E': avg_pe})\n",
    "res = bq.execute(req)\n",
    "\n",
    "# display 5 rows of the result\n",
    "res[0].df().head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Not quite what you were expecting? The BQL request should have only returned one row since the average P/E of all members is just one number. Instead, the query as it's written above applies the `pe.avg()` function to every stock in the index. If \"Stock A\" has a P/E of 10, BQL is just taking the average of one number (10). It repeats this behavior for every member of the index. In fact, this query is exactly the same as just requesting `pe` without `avg()`.\n",
    "\n",
    "How do we fix this? Meet the `group()` function. It allows you aggregate values and perform functions on a set. Grouping the members before calculating the average will give us the results we need. Let's rewrite the query below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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>REVISION_DATE</th>\n",
       "      <th>PERIOD_END_DATE</th>\n",
       "      <th>AS_OF_DATE</th>\n",
       "      <th>ORIG_IDS</th>\n",
       "      <th>Average P/E</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>IdentityGroup</th>\n",
       "      <td>2021-03-17</td>\n",
       "      <td>2021-01-31</td>\n",
       "      <td>2021-03-17</td>\n",
       "      <td>None</td>\n",
       "      <td>50.992735</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              REVISION_DATE PERIOD_END_DATE AS_OF_DATE ORIG_IDS  Average P/E\n",
       "ID                                                                          \n",
       "IdentityGroup    2021-03-17      2021-01-31 2021-03-17     None    50.992735"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Run this cell with the Run button in the Jupyter toolbar\n",
    "# Step 1: Setup Environment\n",
    "# import bql\n",
    "# bq = bql.Service()\n",
    "\n",
    "# Step 2: Get Data with BQL\n",
    "be500_members = bq.univ.members('BE500 Index')\n",
    "pe = bq.data.pe_ratio(fill='prev')\n",
    "avg_pe = pe.group().avg()           # <-- group the constituents and calculate the average of the group\n",
    "\n",
    "req = bql.Request(be500_members, {'Average P/E': avg_pe})\n",
    "res = bq.execute(req)\n",
    "\n",
    "# display the result\n",
    "res[0].df()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h4 style=\"color: orange;\">Group By Specific Criteria</h4>\n",
    "<p>Passing no arguments to <code>group()</code> will group everything in one set. However, we can refine our groupings by passing in some conditions. The code below calculates the average P/E ratio by sector for members of the S&P 500 Index. </p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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>REVISION_DATE</th>\n",
       "      <th>PERIOD_END_DATE</th>\n",
       "      <th>AS_OF_DATE</th>\n",
       "      <th>ORIG_IDS</th>\n",
       "      <th>GICS_SECTOR_NAME()</th>\n",
       "      <th>Average P/E</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ID</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Communication Services</th>\n",
       "      <td>2021-03-11</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>2021-03-17</td>\n",
       "      <td>None</td>\n",
       "      <td>Communication Services</td>\n",
       "      <td>91.002174</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Consumer Discretionary</th>\n",
       "      <td>2021-03-16</td>\n",
       "      <td>2021-01-31</td>\n",
       "      <td>2021-03-17</td>\n",
       "      <td>None</td>\n",
       "      <td>Consumer Discretionary</td>\n",
       "      <td>70.856635</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Consumer Staples</th>\n",
       "      <td>2021-03-11</td>\n",
       "      <td>2021-01-31</td>\n",
       "      <td>2021-03-17</td>\n",
       "      <td>None</td>\n",
       "      <td>Consumer Staples</td>\n",
       "      <td>26.470104</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Energy</th>\n",
       "      <td>2021-03-11</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>2021-03-17</td>\n",
       "      <td>None</td>\n",
       "      <td>Energy</td>\n",
       "      <td>71.610512</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Financials</th>\n",
       "      <td>2021-03-15</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>2021-03-17</td>\n",
       "      <td>None</td>\n",
       "      <td>Financials</td>\n",
       "      <td>17.311391</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Health Care</th>\n",
       "      <td>2021-03-16</td>\n",
       "      <td>2021-01-31</td>\n",
       "      <td>2021-03-17</td>\n",
       "      <td>None</td>\n",
       "      <td>Health Care</td>\n",
       "      <td>47.217591</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Industrials</th>\n",
       "      <td>2021-03-16</td>\n",
       "      <td>2021-01-31</td>\n",
       "      <td>2021-03-17</td>\n",
       "      <td>None</td>\n",
       "      <td>Industrials</td>\n",
       "      <td>85.905229</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Information Technology</th>\n",
       "      <td>2021-03-09</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>2021-03-17</td>\n",
       "      <td>None</td>\n",
       "      <td>Information Technology</td>\n",
       "      <td>50.778027</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Materials</th>\n",
       "      <td>2021-03-16</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>2021-03-17</td>\n",
       "      <td>None</td>\n",
       "      <td>Materials</td>\n",
       "      <td>32.147989</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Real Estate</th>\n",
       "      <td>2021-03-16</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>2021-03-17</td>\n",
       "      <td>None</td>\n",
       "      <td>Real Estate</td>\n",
       "      <td>31.871791</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Utilities</th>\n",
       "      <td>2021-03-17</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>2021-03-17</td>\n",
       "      <td>None</td>\n",
       "      <td>Utilities</td>\n",
       "      <td>42.524809</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                       REVISION_DATE PERIOD_END_DATE AS_OF_DATE ORIG_IDS  \\\n",
       "ID                                                                         \n",
       "Communication Services    2021-03-11      2020-12-31 2021-03-17     None   \n",
       "Consumer Discretionary    2021-03-16      2021-01-31 2021-03-17     None   \n",
       "Consumer Staples          2021-03-11      2021-01-31 2021-03-17     None   \n",
       "Energy                    2021-03-11      2020-12-31 2021-03-17     None   \n",
       "Financials                2021-03-15      2020-12-31 2021-03-17     None   \n",
       "Health Care               2021-03-16      2021-01-31 2021-03-17     None   \n",
       "Industrials               2021-03-16      2021-01-31 2021-03-17     None   \n",
       "Information Technology    2021-03-09      2020-12-31 2021-03-17     None   \n",
       "Materials                 2021-03-16      2020-12-31 2021-03-17     None   \n",
       "Real Estate               2021-03-16      2020-12-31 2021-03-17     None   \n",
       "Utilities                 2021-03-17      2020-12-31 2021-03-17     None   \n",
       "\n",
       "                            GICS_SECTOR_NAME()  Average P/E  \n",
       "ID                                                           \n",
       "Communication Services  Communication Services    91.002174  \n",
       "Consumer Discretionary  Consumer Discretionary    70.856635  \n",
       "Consumer Staples              Consumer Staples    26.470104  \n",
       "Energy                                  Energy    71.610512  \n",
       "Financials                          Financials    17.311391  \n",
       "Health Care                        Health Care    47.217591  \n",
       "Industrials                        Industrials    85.905229  \n",
       "Information Technology  Information Technology    50.778027  \n",
       "Materials                            Materials    32.147989  \n",
       "Real Estate                        Real Estate    31.871791  \n",
       "Utilities                            Utilities    42.524809  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Run this cell with the Run button in the Jupyter toolbar\n",
    "# Step 1: Setup Environment\n",
    "# import bql\n",
    "# bq = bql.Service()\n",
    "\n",
    "# Step 2: Get Data with BQL\n",
    "be500_members = bq.univ.members('BE500 Index')\n",
    "sector = bq.data.gics_sector_name()\n",
    "pe = bq.data.pe_ratio(fill='prev')\n",
    "avg_pe_by_sector = pe.group(sector).avg()      # <-- pass in sector as an argument for group()\n",
    "\n",
    "req = bql.Request(be500_members, {'Average P/E': avg_pe_by_sector})\n",
    "res = bq.execute(req)\n",
    "\n",
    "# display the result\n",
    "res[0].df()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"dg-link-here\"></a>\n",
    "<div style=\"border: 1px dotted orange; padding-left: 10px; padding-bottom: 10px;\">\n",
    "    <h3>&#9998; Try it out</h3>\n",
    "    <p>\n",
    "        Practice with <code>group()</code>\n",
    "        <ol>\n",
    "            <li>Add a new Jupyter code cell below this one</li>\n",
    "            <li>Declare a variable named <code>universe</code> and set it equal to the members of the <code>'BWORLD Index'</code>.</li>\n",
    "            <li>Write a query to calcuate the average P/E by <code>country_full_name</code>.</li>\n",
    "            <li>For an extra challenge, use BQL's <code>sort()</code> function to sort the dataframe in descending order. Which country's stocks are the most expensive?<br><span style=\"color: orange;\">Hint:</span> use the <code>group()</code> function again on the results to sort the entire set.</li>\n",
    "        </ol>\n",
    "    We've written one possible solution to this problem in a hidden Python cell below. When you're ready to get a peek at an answer, click the <img src=\"../img/Controls/hidden_cell.png\"> below.\n",
    "    </p>\n",
    "</div>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style=\"color: orange; font-size: 12px;\">&darr; Click below to expand answer</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "jupyter": {
     "source_hidden": true
    }
   },
   "outputs": [],
   "source": [
    "# import bql\n",
    "# bq = bql.Service()\n",
    "\n",
    "# setup the BQL query\n",
    "universe = bq.univ.members('BWORLD Index')\n",
    "pe_ratio = bq.data.pe_ratio(fill='prev')\n",
    "country = bq.data.country_full_name()\n",
    "avg_pe_by_country = pe_ratio.group(country).avg()\n",
    "sorted_pe_by_country = avg_pe_by_country.group().sort(order='desc')   # <-- group again and sort the group\n",
    "\n",
    "# Make Request & Parse Response\n",
    "request = bql.Request(universe, {'Avg PE by Country': sorted_pe_by_country})\n",
    "response = bq.execute(request)\n",
    "df = response[0].df()\n",
    "\n",
    "# Clean the dataframe\n",
    "df.set_index('COUNTRY_FULL_NAME()', inplace=True)  # <-- set the index to the country name\n",
    "df = df[['Avg PE by Country']]    # <-- drop unnecessary columns \n",
    "df.head()     # <-- display the first five rows"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[&uarr; Return to Top](#bqlc-top)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "<a id=\"bqlc-editor\"></a>\n",
    "<h1>Using the BQL Editor Tool<span style=\"color: orange;\"><br>Search Fields, Universes, and Functions</span></h1>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "BQuant provides a useful tool called the \"BQL Editor\" to help you write and implement BQL queries. \n",
    "\n",
    "The BQL Editor allows you to:\n",
    "   1. Search for Fields, Functions, and Universes\n",
    "   1. Run a test query and get a preview of the results\n",
    "   1. Copy the query's Python code so you can paste it in a Jupyter cell\n",
    "   \n",
    "You can access the BQL Editor in the toolbar at the top of the screen. Clicking the BQL Editor button will open up a separate tab in which you can write your test query.\n",
    "\n",
    "The example below demonstrates the use of the BQL Editor in building a query for the most recent S&P 500 price."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<img src=\"../img/Boot Camp/bql_editor.gif\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[&uarr; Return to Top](#bqlc-top)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "<a id=\"bqlc-guide\"></a>\n",
    "<h1>The BQL Reference Guide<br><span style=\"color: orange;\">Leverage Existing Documentation</span></h1>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "BQL offers a suite of mathematical, statistical, and logistic functions allowing you to perform complex calculations on the server side rather than using the resources on your local computer. BQuant's Example Projects include a reference guide for commonly used BQL funcitons and universes. Find it in <a href=\"../exampleroot/1a54e18169d24618afc2226346108fb4/0 Welcome.ipynb\">Example Projects &rarr; BQL Resources &rarr; BQL Function and Universe Guide</a>. Use this guide and the BQL Editor to write the perfect BQL query for your project."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[&uarr; Return to Top](#bqlc-top)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "----\n",
    "<p style=\"text-align:center;\">\n",
    "    Click on the links below to continue learning.<br>\n",
    "    <a href=\"../1. Boot Camp/4 Boot Camp Project.ipynb\">&larr; Back to Boot Camp: Final Project</a>&emsp;&emsp;\n",
    "    <a href=\"#bqlc-top\">&uarr; Return to Top</a>&emsp;&emsp;\n",
    "    <a href=\"2 DataGrids.ipynb\">Continue to Launch: DataGrids &rarr;</a>\n",
    "    <br>\n",
    "    <br>\n",
    "    <a href=\"../Welcome.ipynb#welcome-top\" style=\"font-size: 12px;\">Return to the Welcome Page</a>\n",
    "</p>"
   ]
  },
  {
   "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
}