2.2 Equity Advanced.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "jupyter": {
     "outputs_hidden": true
    }
   },
   "source": [
    "# <a name=\"top_app\"></a> Bloomberg Data Analytics: <br><span style=\"color:orange\"> Using the Bloomberg Query Language with Equity Data: Advanced </span>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id='s0'></a>\n",
    "\n",
    "#### Topics\n",
    "\n",
    "1. [__Recap__](#s1)<a href='#s1'></a>\n",
    "- [1.1 Filter](#s1.1)<a href='#s1.1'></a>\n",
    "- [1.2 Custom calculations](#s1.2)<a href='#s1.2'></a>\n",
    "2. [__Aggregating and Grouping Data in BQL__](#s2)<a href='#s2'></a>\n",
    "- [2.1 Group](#s2.1)<a href='#s2.1'></a>\n",
    "- [2.2 Filter & Group](#s2.2)<a href='#s2.2'></a>\n",
    "- [2.3 Ungrouping with Groupxyz()](#s2.3)<a href='#s2.3'></a>\n",
    "3. [__Advanced Features__](#s3)<a href='#s3'></a>\n",
    "- [3.1 Quantile](#s3.1)<a href='#s3.1'></a>\n",
    "- [3.2 Rolling calculations](#s3.2)<a href='#s3.2'></a>\n",
    "- [3.3 Value](#s3.3)<a href='#s3.3'></a>\n",
    "- [3.4 Advanced filtering](#s3.4)<a href='#s3.4'></a>\n",
    "- [3.5 Matches](#s3.5)<a href='#s3.5'></a>\n",
    "4. [__Scoring model__](#s4)<a href='#s4'></a>\n",
    "- [4.1 Piotroski F-Score](#s4.1)<a href='#s4.1'></a>\n",
    "- [4.2 Beneish M-Score](#s4.2)<a href='#s4.2'></a>\n",
    "- [4.3 Scoring Practice](#s4.3)<a href='#s4.3'></a>\n",
    "\n",
    "This tutorial assumes knowledge of BQL Basics for Equities such as standardization of fields, filter(), and members()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id='s1'></a>\n",
    "\n",
    "<span style=\"color:darkorange; font-size:2em\"> 1 Recap </span><br>\n",
    "Let's import our packages and initiate the connection to BQL first"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import bql\n",
    "import pandas as pd\n",
    "from collections import OrderedDict"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "bq = bql.Service()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id='s1.1'></a>\n",
    "### 1.1 Filter\n",
    "\n",
    "- Screen your universe directly on the Bloomberg Server\n",
    "\n",
    "<img src=\"../../Visualisations/BQL Filtering.jpg\" style=\"width: 500px;\"/>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "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>GICS_SECTOR_NAME()</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ID</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>AXP UN Equity</th>\n",
       "      <td>Financials</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>JPM UN Equity</th>\n",
       "      <td>Financials</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TRV UN Equity</th>\n",
       "      <td>Financials</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GS UN Equity</th>\n",
       "      <td>Financials</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              GICS_SECTOR_NAME()\n",
       "ID                              \n",
       "AXP UN Equity         Financials\n",
       "JPM UN Equity         Financials\n",
       "TRV UN Equity         Financials\n",
       "GS UN Equity          Financials"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#lets only retreive the financials companies in the DOW Jones index\n",
    "univ = bq.univ.members('INDU Index')\n",
    "sector = bq.data.gics_sector_name() \n",
    "screen = bq.univ.filter(univ, sector == 'Financials')\n",
    "\n",
    "#Define our request\n",
    "req = bql.Request(screen, sector)\n",
    "res = bq.execute(req)\n",
    "\n",
    "data = res[0].df()\n",
    "data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "</a>[Return to Index ↑](#s0)<a href='#s0'></a>\n",
    "\n",
    "<a id='s1.2'></a>\n",
    "### 1.2 Custom Calculations\n",
    "\n",
    "\n",
    "One of the key benefits of BQL is that this gives you more flexibility with custom factors.\n",
    "- Custom combination of fields defined by yourself\n",
    "- Calculations and historical analysis of your custom fields\n",
    "- Apply parameters to metrics eg technical indicators and returns calculations such as alpha and beta\n",
    "\n",
    "See the BQL Editor for field explanations, parameters and defaults"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### <span style=\"color:darkorange\">Example </span>: Stock upside potential for stocks"
   ]
  },
  {
   "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>Upside</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>ABBN SW Equity</th>\n",
       "      <td>2020-10-30</td>\n",
       "      <td>None</td>\n",
       "      <td>10.789372</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     DATE CURRENCY     Upside\n",
       "ID                                           \n",
       "ABBN SW Equity 2020-10-30     None  10.789372"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "equity = 'ABBN SW Equity '\n",
    "# Define Upside Potential\n",
    "target = bq.data.target_price()\n",
    "px = bq.data.px_last()\n",
    "upside = (target/px - 1) * 100\n",
    "#Shift + Tab after a data item shows the available parameters \n",
    "\n",
    "req = bql.Request(equity, {'Upside':upside})\n",
    "res = bq.execute(req)\n",
    "data = res[0].df()\n",
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>DATE</th>\n",
       "      <th>CURRENCY</th>\n",
       "      <th>Upside</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>ABBN SW Equity</th>\n",
       "      <td>2020-10-23</td>\n",
       "      <td>None</td>\n",
       "      <td>3.776787</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ABBN SW Equity</th>\n",
       "      <td>2020-10-24</td>\n",
       "      <td>None</td>\n",
       "      <td>3.424069</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ABBN SW Equity</th>\n",
       "      <td>2020-10-25</td>\n",
       "      <td>None</td>\n",
       "      <td>3.488399</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ABBN SW Equity</th>\n",
       "      <td>2020-10-26</td>\n",
       "      <td>None</td>\n",
       "      <td>4.361912</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ABBN SW Equity</th>\n",
       "      <td>2020-10-27</td>\n",
       "      <td>None</td>\n",
       "      <td>5.108650</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ABBN SW Equity</th>\n",
       "      <td>2020-10-28</td>\n",
       "      <td>None</td>\n",
       "      <td>10.294995</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ABBN SW Equity</th>\n",
       "      <td>2020-10-29</td>\n",
       "      <td>None</td>\n",
       "      <td>11.440192</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ABBN SW Equity</th>\n",
       "      <td>2020-10-30</td>\n",
       "      <td>None</td>\n",
       "      <td>10.789372</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     DATE CURRENCY     Upside\n",
       "ID                                           \n",
       "ABBN SW Equity 2020-10-23     None   3.776787\n",
       "ABBN SW Equity 2020-10-24     None   3.424069\n",
       "ABBN SW Equity 2020-10-25     None   3.488399\n",
       "ABBN SW Equity 2020-10-26     None   4.361912\n",
       "ABBN SW Equity 2020-10-27     None   5.108650\n",
       "ABBN SW Equity 2020-10-28     None  10.294995\n",
       "ABBN SW Equity 2020-10-29     None  11.440192\n",
       "ABBN SW Equity 2020-10-30     None  10.789372"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#This means we can analyse this custom field historical time series\n",
    "equity = 'ABBN SW Equity'\n",
    "\n",
    "# Define Upside Potential\n",
    "target = bq.data.target_price(dates= bq.func.range('-1w','0d'), fill='prev', frq='d')\n",
    "px = bq.data.px_last(dates= bq.func.range('-1w','0d'), fill='prev', frq='d')\n",
    "upside = (target/px - 1) * 100\n",
    "\n",
    "req = bql.Request(equity, {'Upside':upside})\n",
    "res = bq.execute(req)\n",
    "data = res[0].df()\n",
    "data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Calculations of this custom time series e.g. show the percentage change of the upside potential over the past week"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>DATE</th>\n",
       "      <th>CURRENCY</th>\n",
       "      <th>Upside Change</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>ABBN SW Equity</th>\n",
       "      <td>2020-10-30</td>\n",
       "      <td>None</td>\n",
       "      <td>185.675945</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     DATE CURRENCY  Upside Change\n",
       "ID                                               \n",
       "ABBN SW Equity 2020-10-30     None     185.675945"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#This also means we can peform analysis on this custom time series\n",
    "equity = 'ABBN SW Equity'\n",
    "\n",
    "# Define Upside Potential\n",
    "target = bq.data.target_price(dates= bq.func.range('-1w','0d'), fill='prev', frq='d')\n",
    "px = bq.data.px_last(dates= bq.func.range('-1w','0d'), fill='prev', frq='d')\n",
    "upside = (target/px - 1) * 100\n",
    "#calcuate the change\n",
    "upside_chg = upside.pct_chg()\n",
    "\n",
    "req = bql.Request(equity, {'Upside Change':upside_chg})\n",
    "res = bq.execute(req)\n",
    "data = res[0].df()\n",
    "data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "</a>[Return to Index ↑](#s0)<a href='#s0'></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id='s2'></a>\n",
    "<span style=\"color:darkorange; font-size:2em\"> 2 Aggregating and Grouping Data in BQL </span>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id='s2.1'></a>\n",
    "\n",
    "### 2.1 Group\n",
    "\n",
    "- aggregating on Bloomberg Server\n",
    "- using members() to access indices and portfolios"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<img src=\"../../Visualisations/BQL Grouping.jpg\" style=\"width: 800px;\"/>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### <span style=\"color:darkorange\">Q </span>: Count the number of securities per BICS Level 1 sector of Stoxx 600"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "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>Weights</th>\n",
       "      <th>Positions</th>\n",
       "      <th>ORIG_IDS</th>\n",
       "      <th>CLASSIFICATION_NAME(CLASSIFICATION_SCHEME.BICS,CLASSIFICATION_LEVEL.1)</th>\n",
       "      <th>COUNT(GROUP(ID(),CLASSIFICATION_NAME(CLASSIFICATION_SCHEME.BICS,CLASSIFICATION_LEVEL.1)))</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>Communications</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>SXXP INDEX</td>\n",
       "      <td>Communications</td>\n",
       "      <td>39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Consumer Discretionary</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>SXXP INDEX</td>\n",
       "      <td>Consumer Discretionary</td>\n",
       "      <td>56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Consumer Staples</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>SXXP INDEX</td>\n",
       "      <td>Consumer Staples</td>\n",
       "      <td>53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Energy</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>SXXP INDEX</td>\n",
       "      <td>Energy</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Financials</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>SXXP INDEX</td>\n",
       "      <td>Financials</td>\n",
       "      <td>102</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Health Care</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>SXXP INDEX</td>\n",
       "      <td>Health Care</td>\n",
       "      <td>53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Industrials</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>SXXP INDEX</td>\n",
       "      <td>Industrials</td>\n",
       "      <td>111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Materials</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>SXXP INDEX</td>\n",
       "      <td>Materials</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Real Estate</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>SXXP INDEX</td>\n",
       "      <td>Real Estate</td>\n",
       "      <td>38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Technology</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>SXXP INDEX</td>\n",
       "      <td>Technology</td>\n",
       "      <td>38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Utilities</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>SXXP INDEX</td>\n",
       "      <td>Utilities</td>\n",
       "      <td>29</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        Weights  Positions    ORIG_IDS  \\\n",
       "ID                                                       \n",
       "Communications              NaN        NaN  SXXP INDEX   \n",
       "Consumer Discretionary      NaN        NaN  SXXP INDEX   \n",
       "Consumer Staples            NaN        NaN  SXXP INDEX   \n",
       "Energy                      NaN        NaN  SXXP INDEX   \n",
       "Financials                  NaN        NaN  SXXP INDEX   \n",
       "Health Care                 NaN        NaN  SXXP INDEX   \n",
       "Industrials                 NaN        NaN  SXXP INDEX   \n",
       "Materials                   NaN        NaN  SXXP INDEX   \n",
       "Real Estate                 NaN        NaN  SXXP INDEX   \n",
       "Technology                  NaN        NaN  SXXP INDEX   \n",
       "Utilities                   NaN        NaN  SXXP INDEX   \n",
       "\n",
       "                       CLASSIFICATION_NAME(CLASSIFICATION_SCHEME.BICS,CLASSIFICATION_LEVEL.1)  \\\n",
       "ID                                                                                              \n",
       "Communications                                             Communications                       \n",
       "Consumer Discretionary                             Consumer Discretionary                       \n",
       "Consumer Staples                                         Consumer Staples                       \n",
       "Energy                                                             Energy                       \n",
       "Financials                                                     Financials                       \n",
       "Health Care                                                   Health Care                       \n",
       "Industrials                                                   Industrials                       \n",
       "Materials                                                       Materials                       \n",
       "Real Estate                                                   Real Estate                       \n",
       "Technology                                                     Technology                       \n",
       "Utilities                                                       Utilities                       \n",
       "\n",
       "                        COUNT(GROUP(ID(),CLASSIFICATION_NAME(CLASSIFICATION_SCHEME.BICS,CLASSIFICATION_LEVEL.1)))  \n",
       "ID                                                                                                                 \n",
       "Communications                                                         39                                          \n",
       "Consumer Discretionary                                                 56                                          \n",
       "Consumer Staples                                                       53                                          \n",
       "Energy                                                                 21                                          \n",
       "Financials                                                            102                                          \n",
       "Health Care                                                            53                                          \n",
       "Industrials                                                           111                                          \n",
       "Materials                                                              60                                          \n",
       "Real Estate                                                            38                                          \n",
       "Technology                                                             38                                          \n",
       "Utilities                                                              29                                          "
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Index Members\n",
    "index = bq.univ.members('SXXP Index')\n",
    "#Count Members by sector\n",
    "security = bq.data.id()\n",
    "sector = bq.data.classification_name('BICS', '1')\n",
    "count_sector = security.group(sector).count()\n",
    "\n",
    "req = bql.Request(index, count_sector)\n",
    "res = bq.execute(req)\n",
    "data = res[0].df()\n",
    "data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id='s2.2'></a>\n",
    "\n",
    "### 2.1 Filter & Group\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### <span style=\"color:darkorange\">Q </span>: Show the members of the SX5E whose price performance was positive and was greater than their sector average?"
   ]
  },
  {
   "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>ORIG_IDS</th>\n",
       "      <th>GICS_SECTOR_NAME()</th>\n",
       "      <th>PX_PERF</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>OR FP Equity</th>\n",
       "      <td>2020-10-30</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Consumer Staples</td>\n",
       "      <td>-4.480457</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>DG FP Equity</th>\n",
       "      <td>2020-10-30</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Industrials</td>\n",
       "      <td>-2.220257</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ASML NA Equity</th>\n",
       "      <td>2020-10-30</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Information Technology</td>\n",
       "      <td>-12.526999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SAN SQ Equity</th>\n",
       "      <td>2020-10-30</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Financials</td>\n",
       "      <td>-7.161019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PHIA NA Equity</th>\n",
       "      <td>2020-10-30</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Health Care</td>\n",
       "      <td>-11.209173</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     DATE CURRENCY ORIG_IDS      GICS_SECTOR_NAME()    PX_PERF\n",
       "ID                                                                            \n",
       "OR FP Equity   2020-10-30     None     None        Consumer Staples  -4.480457\n",
       "DG FP Equity   2020-10-30     None     None             Industrials  -2.220257\n",
       "ASML NA Equity 2020-10-30     None     None  Information Technology -12.526999\n",
       "SAN SQ Equity  2020-10-30     None     None              Financials  -7.161019\n",
       "PHIA NA Equity 2020-10-30     None     None             Health Care -11.209173"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "index = bq.univ.members('SX5E Index')\n",
    "\n",
    "px_perf = bq.func.pct_chg(bq.data.px_last(dates=bq.func.range('-1m','0d'), fill='prev', ca_adj='full'))\n",
    "sector_avg = bq.func.groupavg(px_perf, [bq.data.gics_sector_name()])\n",
    "screen = bq.univ.filter(index, bq.func.and_(px_perf > sector_avg, px_perf > 0))\n",
    "\n",
    "\n",
    "req = bql.Request(index, {'PX_PERF': sector_avg})\n",
    "res = bq.execute(req)\n",
    "data = res[0].df()\n",
    "data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "</a>[Return to Index ↑](#s0)<a href='#s0'></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id='s2.3'></a>\n",
    "### 2.3 Ungrouping with Groupxyz()\n",
    "\n",
    "\n",
    "\n",
    "- Bucket level calculation with output at security level\n",
    "- eg what is the rank of each security's rank within its own sector?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<img src=\"../../Visualisations/BQL Groupxyz.jpg\" style=\"width: 800px;\"/>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- The groupxyz functions make this easier by making the manipulation in the background.\n",
    "- Common applications include grouprank(), groupzscore(), grouprank(), groupavg() and groupmedian()\n",
    "- You can stack groupxyz() functions to manage your output within BQL."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### <span style=\"color:darkorange\">Example </span>: Show the top 10 members of the SMI index who increased upside potential the most in past week"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "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>Top 10 Upside Change</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>ALC SE Equity</th>\n",
       "      <td>2020-10-30</td>\n",
       "      <td>None</td>\n",
       "      <td>1189.691133</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SGSN SE Equity</th>\n",
       "      <td>2020-10-30</td>\n",
       "      <td>None</td>\n",
       "      <td>272.878578</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CFR SE Equity</th>\n",
       "      <td>2020-10-30</td>\n",
       "      <td>None</td>\n",
       "      <td>243.558490</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ABBN SE Equity</th>\n",
       "      <td>2020-10-30</td>\n",
       "      <td>None</td>\n",
       "      <td>184.358704</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>UHR SE Equity</th>\n",
       "      <td>2020-10-30</td>\n",
       "      <td>None</td>\n",
       "      <td>137.597719</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SIKA SE Equity</th>\n",
       "      <td>2020-10-30</td>\n",
       "      <td>None</td>\n",
       "      <td>90.982134</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CSGN SE Equity</th>\n",
       "      <td>2020-10-30</td>\n",
       "      <td>None</td>\n",
       "      <td>69.915620</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SCMN SE Equity</th>\n",
       "      <td>2020-10-30</td>\n",
       "      <td>None</td>\n",
       "      <td>61.129314</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SLHN SE Equity</th>\n",
       "      <td>2020-10-30</td>\n",
       "      <td>None</td>\n",
       "      <td>52.238238</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>LHN SE Equity</th>\n",
       "      <td>2020-10-30</td>\n",
       "      <td>None</td>\n",
       "      <td>52.171409</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     DATE CURRENCY  Top 10 Upside Change\n",
       "ID                                                      \n",
       "ALC SE Equity  2020-10-30     None           1189.691133\n",
       "SGSN SE Equity 2020-10-30     None            272.878578\n",
       "CFR SE Equity  2020-10-30     None            243.558490\n",
       "ABBN SE Equity 2020-10-30     None            184.358704\n",
       "UHR SE Equity  2020-10-30     None            137.597719\n",
       "SIKA SE Equity 2020-10-30     None             90.982134\n",
       "CSGN SE Equity 2020-10-30     None             69.915620\n",
       "SCMN SE Equity 2020-10-30     None             61.129314\n",
       "SLHN SE Equity 2020-10-30     None             52.238238\n",
       "LHN SE Equity  2020-10-30     None             52.171409"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Index is our universe\n",
    "index = bq.univ.members('SMI Index')\n",
    "# Define Upside Potential\n",
    "target = bq.data.target_price(dates= bq.func.range('-1w','0d'), fill='prev', frq='d')\n",
    "px = bq.data.px_last(dates= bq.func.range('-1w','0d'), fill='prev', frq='d')\n",
    "upside = (target/px - 1) * 100\n",
    "upside_chg = upside.pct_chg()\n",
    "#Define our filter and apply\n",
    "criteria = upside_chg.grouprank() <=10\n",
    "filtered_index = bq.univ.filter(index, criteria)\n",
    "\n",
    "req = bql.Request(filtered_index, {'Top 10 Upside Change':upside_chg.groupsort()})\n",
    "res = bq.execute(req)\n",
    "data = res[0].df()\n",
    "data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "</a>[Return to Index ↑](#s0)<a href='#s0'></a>\n",
    "\n",
    "<a id='s3'></a>\n",
    "\n",
    "<span style=\"color:darkorange; font-size:2em\"> 3 Advanced features </span>\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id='s3.1'></a>\n",
    "\n",
    "### 3.1 Quantile\n",
    "\n",
    "\n",
    "\n",
    "- a value between 0 and 1\n",
    "- shows us the cut-off point for the partition value between a custom bucket"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<img src=\"../../Visualisations/Cut and Quantile.jpg\" style=\"height: 400px;\"/>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>DATE</th>\n",
       "      <th>CURRENCY</th>\n",
       "      <th>ORIG_IDS</th>\n",
       "      <th>Cut Off</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>IdentityGroup</th>\n",
       "      <td>2020-10-30</td>\n",
       "      <td>USD</td>\n",
       "      <td>None</td>\n",
       "      <td>13.368884</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    DATE CURRENCY ORIG_IDS    Cut Off\n",
       "ID                                                   \n",
       "IdentityGroup 2020-10-30      USD     None  13.368884"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Give me the cut-off point for price between 1 and 2nd quintile\n",
    "index = bq.univ.members('SASEIDX Index')\n",
    "price = bq.data.px_last(CURRENCY='USD')\n",
    "price_cutoff = price.group().quantile('0.80')\n",
    "\n",
    "req = bql.Request(index, {'Cut Off': price_cutoff}) \n",
    "res = bq.execute(req)\n",
    "data = res[0].df()\n",
    "data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 3.1.1 Quantile Practice\n",
    "##### <span style=\"color:darkorange\">Q </span>: Find the securities within the first quintile ie price > cut-off point\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "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>ARABSEA AB Equity</th>\n",
       "      <td>2020-10-30</td>\n",
       "      <td>USD</td>\n",
       "      <td>20.103986</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BAAZEEM AB Equity</th>\n",
       "      <td>2020-10-30</td>\n",
       "      <td>USD</td>\n",
       "      <td>13.491535</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ALOMRAN AB Equity</th>\n",
       "      <td>2020-10-30</td>\n",
       "      <td>USD</td>\n",
       "      <td>18.717505</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ANAAM AB Equity</th>\n",
       "      <td>2020-10-30</td>\n",
       "      <td>USD</td>\n",
       "      <td>20.957206</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CATERING AB Equity</th>\n",
       "      <td>2020-10-30</td>\n",
       "      <td>USD</td>\n",
       "      <td>20.743901</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                         DATE CURRENCY      Price\n",
       "ID                                               \n",
       "ARABSEA AB Equity  2020-10-30      USD  20.103986\n",
       "BAAZEEM AB Equity  2020-10-30      USD  13.491535\n",
       "ALOMRAN AB Equity  2020-10-30      USD  18.717505\n",
       "ANAAM AB Equity    2020-10-30      USD  20.957206\n",
       "CATERING AB Equity 2020-10-30      USD  20.743901"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "index = bq.univ.members('SASEIDX Index')\n",
    "price = bq.data.px_last(CURRENCY='USD')\n",
    "price_cutoff = price.group().quantile('0.80')\n",
    "#Get the cut-off to a security level\n",
    "price_cutoff_u = price_cutoff.ungroup()\n",
    "price_criteria = price > price_cutoff_u\n",
    "filtered_index = bq.univ.filter(index, price_criteria)\n",
    "\n",
    "req = bql.Request(filtered_index, {'Price': price}) \n",
    "res = bq.execute(req)\n",
    "data = res[0].df().head()\n",
    "data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "</a>[Return to Index ↑](#s0)<a href='#s0'></a>\n",
    "\n",
    "<a id='s3.2'></a>\n",
    "\n",
    "### 3.2. Rolling calculations\n",
    "\n",
    "- Calculate with a rolling time window over a time period\n",
    "- Rolling() shift tab to see inputs and parameters"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<img src=\"../../Visualisations/Rolling.jpg\" style=\"width: 600px;\"/>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Q: Show me the rolling Earnings momentum for an index over a year with a window of 12 weeks which rolls on a weekly basis"
   ]
  },
  {
   "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:0</th>\n",
       "      <th>ORIG_IDS:1</th>\n",
       "      <th>ITERATION_DATE</th>\n",
       "      <th>ITERATION_ID</th>\n",
       "      <th>Rolling Momentum</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>IdentityGroup</th>\n",
       "      <td>2019-10-30</td>\n",
       "      <td>2020-08-31</td>\n",
       "      <td>2019-10-30</td>\n",
       "      <td>None</td>\n",
       "      <td>IdentityGroup</td>\n",
       "      <td>2019-10-30</td>\n",
       "      <td>IdentityGroup:ITR0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>IdentityGroup</th>\n",
       "      <td>2019-11-06</td>\n",
       "      <td>2020-09-30</td>\n",
       "      <td>2019-11-06</td>\n",
       "      <td>None</td>\n",
       "      <td>IdentityGroup</td>\n",
       "      <td>2019-11-06</td>\n",
       "      <td>IdentityGroup:ITR1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>IdentityGroup</th>\n",
       "      <td>2019-11-13</td>\n",
       "      <td>2020-09-30</td>\n",
       "      <td>2019-11-13</td>\n",
       "      <td>None</td>\n",
       "      <td>IdentityGroup</td>\n",
       "      <td>2019-11-13</td>\n",
       "      <td>IdentityGroup:ITR2</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>IdentityGroup</th>\n",
       "      <td>2019-11-20</td>\n",
       "      <td>2020-09-30</td>\n",
       "      <td>2019-11-20</td>\n",
       "      <td>None</td>\n",
       "      <td>IdentityGroup</td>\n",
       "      <td>2019-11-20</td>\n",
       "      <td>IdentityGroup:ITR3</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>IdentityGroup</th>\n",
       "      <td>2019-11-27</td>\n",
       "      <td>2020-09-30</td>\n",
       "      <td>2019-11-27</td>\n",
       "      <td>None</td>\n",
       "      <td>IdentityGroup</td>\n",
       "      <td>2019-11-27</td>\n",
       "      <td>IdentityGroup:ITR4</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              REVISION_DATE PERIOD_END_DATE AS_OF_DATE ORIG_IDS:0  \\\n",
       "ID                                                                  \n",
       "IdentityGroup    2019-10-30      2020-08-31 2019-10-30       None   \n",
       "IdentityGroup    2019-11-06      2020-09-30 2019-11-06       None   \n",
       "IdentityGroup    2019-11-13      2020-09-30 2019-11-13       None   \n",
       "IdentityGroup    2019-11-20      2020-09-30 2019-11-20       None   \n",
       "IdentityGroup    2019-11-27      2020-09-30 2019-11-27       None   \n",
       "\n",
       "                  ORIG_IDS:1 ITERATION_DATE        ITERATION_ID  \\\n",
       "ID                                                                \n",
       "IdentityGroup  IdentityGroup     2019-10-30  IdentityGroup:ITR0   \n",
       "IdentityGroup  IdentityGroup     2019-11-06  IdentityGroup:ITR1   \n",
       "IdentityGroup  IdentityGroup     2019-11-13  IdentityGroup:ITR2   \n",
       "IdentityGroup  IdentityGroup     2019-11-20  IdentityGroup:ITR3   \n",
       "IdentityGroup  IdentityGroup     2019-11-27  IdentityGroup:ITR4   \n",
       "\n",
       "               Rolling Momentum  \n",
       "ID                               \n",
       "IdentityGroup               NaN  \n",
       "IdentityGroup               NaN  \n",
       "IdentityGroup               NaN  \n",
       "IdentityGroup               NaN  \n",
       "IdentityGroup               NaN  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "index = bq.univ.members('MXEU Index')\n",
    "#Momentum for all members\n",
    "up = bq.data.contributor_revisions(bq.data.is_eps(FPO='1', FPT='A'), revision_type='numup', revision_window='12w')\n",
    "up_tot = up.group().sum()\n",
    "down = bq.data.contributor_revisions(bq.data.is_eps(FPO='1', FPT='A'), revision_type='numdn', revision_window='12w')\n",
    "down_tot = down.group().sum()\n",
    "count = bq.data.contributor_count(bq.data.is_eps(FPO='0', FPT='A'))\n",
    "count_tot = count.group().sum()\n",
    "momentum_tot = (up_tot - down_tot) / count_tot\n",
    "#Round to 3 decimal places\n",
    "momentum_tot_round = momentum_tot.round('3')\n",
    "#Roll for a year on a weekly basis with a window size of 12 weeks\n",
    "momentum_roll = momentum_tot_round.rolling(ITERATIONDATES=bq.func.range('-1y','0d', frq='w'))\n",
    "\n",
    "req = bql.Request(index, {'Rolling Momentum':momentum_roll})\n",
    "res = bq.execute(req)\n",
    "data = res[0].df().head()\n",
    "data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 3.2.1 Rolling Practice\n",
    "Find the Index Median Correlation\n",
    "\n",
    "##### <span style=\"color:darkorange\">Q </span>: What was the rolling median correlation of 1yr total return of UKX members against the index itself?\n",
    "\n",
    "- Using corr() to calculate correlation of a time series of 1 yr total return against that for an index\n",
    "- Derive the median correlation for all members against the index\n",
    "- Apply rolling() to make this computation on a rolling basis between 2018-11-01 and 2018-11-30"
   ]
  },
  {
   "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>DATE</th>\n",
       "      <th>ORIG_IDS:0</th>\n",
       "      <th>ORIG_IDS:1</th>\n",
       "      <th>ITERATION_DATE</th>\n",
       "      <th>ITERATION_ID</th>\n",
       "      <th>med_corr</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>IdentityGroup</th>\n",
       "      <td>2018-11-01</td>\n",
       "      <td>None</td>\n",
       "      <td>IdentityGroup</td>\n",
       "      <td>2018-11-01</td>\n",
       "      <td>IdentityGroup:ITR0</td>\n",
       "      <td>0.617809</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>IdentityGroup</th>\n",
       "      <td>2018-11-02</td>\n",
       "      <td>None</td>\n",
       "      <td>IdentityGroup</td>\n",
       "      <td>2018-11-02</td>\n",
       "      <td>IdentityGroup:ITR1</td>\n",
       "      <td>0.616108</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>IdentityGroup</th>\n",
       "      <td>2018-11-03</td>\n",
       "      <td>None</td>\n",
       "      <td>IdentityGroup</td>\n",
       "      <td>2018-11-03</td>\n",
       "      <td>IdentityGroup:ITR2</td>\n",
       "      <td>0.616545</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>IdentityGroup</th>\n",
       "      <td>2018-11-04</td>\n",
       "      <td>None</td>\n",
       "      <td>IdentityGroup</td>\n",
       "      <td>2018-11-04</td>\n",
       "      <td>IdentityGroup:ITR3</td>\n",
       "      <td>0.616544</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>IdentityGroup</th>\n",
       "      <td>2018-11-05</td>\n",
       "      <td>None</td>\n",
       "      <td>IdentityGroup</td>\n",
       "      <td>2018-11-05</td>\n",
       "      <td>IdentityGroup:ITR4</td>\n",
       "      <td>0.616246</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    DATE ORIG_IDS:0     ORIG_IDS:1 ITERATION_DATE  \\\n",
       "ID                                                                  \n",
       "IdentityGroup 2018-11-01       None  IdentityGroup     2018-11-01   \n",
       "IdentityGroup 2018-11-02       None  IdentityGroup     2018-11-02   \n",
       "IdentityGroup 2018-11-03       None  IdentityGroup     2018-11-03   \n",
       "IdentityGroup 2018-11-04       None  IdentityGroup     2018-11-04   \n",
       "IdentityGroup 2018-11-05       None  IdentityGroup     2018-11-05   \n",
       "\n",
       "                     ITERATION_ID  med_corr  \n",
       "ID                                           \n",
       "IdentityGroup  IdentityGroup:ITR0  0.617809  \n",
       "IdentityGroup  IdentityGroup:ITR1  0.616108  \n",
       "IdentityGroup  IdentityGroup:ITR2  0.616545  \n",
       "IdentityGroup  IdentityGroup:ITR3  0.616544  \n",
       "IdentityGroup  IdentityGroup:ITR4  0.616246  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "index = bq.univ.members('SX5E Index')\n",
    "\n",
    "ttr = bq.data.day_to_day_total_return(dates=bq.func.range('-1y', '0d'), CURRENCY='EUR')\n",
    "index_ttr = bq.func.value(ttr, bq.univ.list(['SX5E Index']))\n",
    "#Calculate correlation of index member total return against index itself\n",
    "corr = bq.func.corr(ttr, index_ttr)\n",
    "corr_median = corr.group().median()\n",
    "#Roll our calculation\n",
    "corr_median_rolling = bq.func.rolling(corr_median, ITERATIONDATES=bq.func.range('2018-11-01', '2018-11-30'))\n",
    "\n",
    "req = bql.Request(index, {'med_corr': corr_median_rolling}) \n",
    "res = bq.execute(req)\n",
    "data = res[0].df().head()\n",
    "data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "</a>[Return to Index ↑](#s0)<a href='#s0'></a>\n",
    "\n",
    "\n",
    "<a id='s3.3'></a>\n",
    "\n",
    "### 3.3 Value\n",
    "\n",
    "- Introduces data from outside the universe\n",
    "- Allows us to retrieve and manipulate data from two universes in one request\n",
    "- Be aware that Value() also takes its universe as a bq.univ.list()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<img src=\"../../Visualisations/BQL Value.jpg\" style=\"width: 600px;\"/>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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>Adidas Price</th>\n",
       "      <th>Index Name</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>ADS GR Equity</th>\n",
       "      <td>258.399994</td>\n",
       "      <td>Dow Jones Industrial Average</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               Adidas Price                    Index Name\n",
       "ID                                                       \n",
       "ADS GR Equity    258.399994  Dow Jones Industrial Average"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Value allows us to reference two separate universes\n",
    "stock = 'ADS GR Equity'\n",
    "index = 'INDU Index'\n",
    "#pull price of Adidas and name of Dow Jones in the same query\n",
    "price = bq.data.px_last()\n",
    "name = bq.data.name()\n",
    "name_index = name.value(bq.univ.list([index]))\n",
    "#Put our fields in an OrderedDict()\n",
    "flds = OrderedDict()\n",
    "flds['Adidas Price'] = price\n",
    "flds['Index Name'] = name_index\n",
    "\n",
    "req = bql.Request(stock, flds)\n",
    "res = bq.execute(req)\n",
    "tbl = pd.DataFrame({r.name:r.df()[r.name] for r in res})\n",
    "tbl"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "</a>[Return to Index ↑](#s0)<a href='#s0'></a>\n",
    "\n",
    "<a id='s3.4'></a>\n",
    "### 3.4. Advanced Filtering\n",
    "\n",
    "\n",
    "- instead of using or() to evaluate multiple conditions within one field, in_() lets BQL do this much quicker\n",
    "- in_().not_() can be used for negative screening"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<img src=\"../../Visualisations/in.jpg\" style=\"width: 600px;\"/>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 3.4.1 Method 1: Get the stocks in by sector for the members of the SXXP in Energy, Healthcare, Financials and Industrials using or()"
   ]
  },
  {
   "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>Weights</th>\n",
       "      <th>Positions</th>\n",
       "      <th>ORIG_IDS</th>\n",
       "      <th>CLASSIFICATION_NAME()</th>\n",
       "      <th>Securities per Sector</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>Communications</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>SXXP INDEX</td>\n",
       "      <td>Communications</td>\n",
       "      <td>39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Consumer Discretionary</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>SXXP INDEX</td>\n",
       "      <td>Consumer Discretionary</td>\n",
       "      <td>56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Consumer Staples</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>SXXP INDEX</td>\n",
       "      <td>Consumer Staples</td>\n",
       "      <td>53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Energy</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>SXXP INDEX</td>\n",
       "      <td>Energy</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Financials</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>SXXP INDEX</td>\n",
       "      <td>Financials</td>\n",
       "      <td>102</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        Weights  Positions    ORIG_IDS  \\\n",
       "ID                                                       \n",
       "Communications              NaN        NaN  SXXP INDEX   \n",
       "Consumer Discretionary      NaN        NaN  SXXP INDEX   \n",
       "Consumer Staples            NaN        NaN  SXXP INDEX   \n",
       "Energy                      NaN        NaN  SXXP INDEX   \n",
       "Financials                  NaN        NaN  SXXP INDEX   \n",
       "\n",
       "                         CLASSIFICATION_NAME()  Securities per Sector  \n",
       "ID                                                                     \n",
       "Communications                  Communications                     39  \n",
       "Consumer Discretionary  Consumer Discretionary                     56  \n",
       "Consumer Staples              Consumer Staples                     53  \n",
       "Energy                                  Energy                     21  \n",
       "Financials                          Financials                    102  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "index = bq.univ.members('SXXP Index')\n",
    "sector = bq.data.classification_name()\n",
    "criteria = bq.func.or_(sector == 'Industrials', bq.func.or_(sector == 'Financials',bq.func.or_(sector == 'Energy', sector == 'Healthcare')))\n",
    "filtered_index = bq.univ.filter(index, criteria)\n",
    "#Count by sector\n",
    "security = bq.data.id()\n",
    "security_per_sector = security.group(sector).count()\n",
    "\n",
    "req = bql.Request(index, {'Securities per Sector': security_per_sector}) \n",
    "res = bq.execute(req)\n",
    "data = res[0].df().head()\n",
    "data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 3.4.2 Method 2: using in_()\n",
    "- Less nesting, much easier to read and change"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "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>Weights</th>\n",
       "      <th>Positions</th>\n",
       "      <th>ORIG_IDS</th>\n",
       "      <th>CLASSIFICATION_NAME()</th>\n",
       "      <th>Securities per Sector</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>Communications</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>SXXP INDEX</td>\n",
       "      <td>Communications</td>\n",
       "      <td>39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Consumer Discretionary</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>SXXP INDEX</td>\n",
       "      <td>Consumer Discretionary</td>\n",
       "      <td>56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Consumer Staples</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>SXXP INDEX</td>\n",
       "      <td>Consumer Staples</td>\n",
       "      <td>53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Energy</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>SXXP INDEX</td>\n",
       "      <td>Energy</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Financials</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>SXXP INDEX</td>\n",
       "      <td>Financials</td>\n",
       "      <td>102</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        Weights  Positions    ORIG_IDS  \\\n",
       "ID                                                       \n",
       "Communications              NaN        NaN  SXXP INDEX   \n",
       "Consumer Discretionary      NaN        NaN  SXXP INDEX   \n",
       "Consumer Staples            NaN        NaN  SXXP INDEX   \n",
       "Energy                      NaN        NaN  SXXP INDEX   \n",
       "Financials                  NaN        NaN  SXXP INDEX   \n",
       "\n",
       "                         CLASSIFICATION_NAME()  Securities per Sector  \n",
       "ID                                                                     \n",
       "Communications                  Communications                     39  \n",
       "Consumer Discretionary  Consumer Discretionary                     56  \n",
       "Consumer Staples              Consumer Staples                     53  \n",
       "Energy                                  Energy                     21  \n",
       "Financials                          Financials                    102  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "index = bq.univ.members('SXXP Index')\n",
    "sector = bq.data.classification_name()\n",
    "criteria = sector.in_(['Energy', 'Financials', 'Healthcare','Industrials'])\n",
    "filtered_index = bq.univ.filter(index, criteria)\n",
    "#Count by sector\n",
    "security = bq.data.id()\n",
    "security_per_sector = security.group(sector).count()\n",
    "\n",
    "req = bql.Request(index, {'Securities per Sector': security_per_sector}) \n",
    "res = bq.execute(req)\n",
    "data = res[0].df().head()\n",
    "data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 3.4.3 Reversing logic for negative screening\n",
    "- in_().not_()\n",
    "- Exclude securities that fulfill the following criteria in a field"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<img src=\"../../Visualisations/not in.jpg\" style=\"width: 600px;\"/>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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>CLASSIFICATION_NAME(CLASSIFICATION_SCHEME.BICS,CLASSIFICATION_LEVEL.4)</th>\n",
       "      <th>Securities per Sector</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>Advertising &amp; Marketing</th>\n",
       "      <td>2020-10-29</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>2020-10-30</td>\n",
       "      <td>None</td>\n",
       "      <td>Advertising &amp; Marketing</td>\n",
       "      <td>-15.001937</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Agricultural Chemicals</th>\n",
       "      <td>2020-10-30</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>2020-10-30</td>\n",
       "      <td>YAR NO Equity</td>\n",
       "      <td>Agricultural Chemicals</td>\n",
       "      <td>-7.575758</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Agricultural Machinery</th>\n",
       "      <td>2020-10-27</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>2020-10-30</td>\n",
       "      <td>None</td>\n",
       "      <td>Agricultural Machinery</td>\n",
       "      <td>-11.360248</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Agricultural Producers</th>\n",
       "      <td>2020-10-16</td>\n",
       "      <td>2021-06-30</td>\n",
       "      <td>2020-10-30</td>\n",
       "      <td>None</td>\n",
       "      <td>Agricultural Producers</td>\n",
       "      <td>4.104890</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Aircraft &amp; Parts</th>\n",
       "      <td>2020-10-29</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>2020-10-30</td>\n",
       "      <td>None</td>\n",
       "      <td>Aircraft &amp; Parts</td>\n",
       "      <td>-27.760577</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Airlines</th>\n",
       "      <td>2020-10-30</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>2020-10-30</td>\n",
       "      <td>None</td>\n",
       "      <td>Airlines</td>\n",
       "      <td>-63.502124</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Alcoholic Beverages</th>\n",
       "      <td>2020-10-26</td>\n",
       "      <td>2021-03-31</td>\n",
       "      <td>2020-10-30</td>\n",
       "      <td>None</td>\n",
       "      <td>Alcoholic Beverages</td>\n",
       "      <td>-2.724702</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Apparel, Footwear &amp; Acc Design</th>\n",
       "      <td>2020-10-29</td>\n",
       "      <td>2021-03-31</td>\n",
       "      <td>2020-10-30</td>\n",
       "      <td>None</td>\n",
       "      <td>Apparel, Footwear &amp; Acc Design</td>\n",
       "      <td>-13.812955</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Application Software</th>\n",
       "      <td>2020-10-27</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>2020-10-30</td>\n",
       "      <td>None</td>\n",
       "      <td>Application Software</td>\n",
       "      <td>0.943660</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Auto Parts</th>\n",
       "      <td>2020-10-28</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>2020-10-30</td>\n",
       "      <td>None</td>\n",
       "      <td>Auto Parts</td>\n",
       "      <td>-15.738833</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Automobiles</th>\n",
       "      <td>2020-10-29</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>2020-10-30</td>\n",
       "      <td>None</td>\n",
       "      <td>Automobiles</td>\n",
       "      <td>-12.899304</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Automotive Wholesalers</th>\n",
       "      <td>2020-09-23</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>2020-10-30</td>\n",
       "      <td>INCH LN Equity</td>\n",
       "      <td>Automotive Wholesalers</td>\n",
       "      <td>-26.259191</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Banks</th>\n",
       "      <td>2020-10-23</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>2020-10-30</td>\n",
       "      <td>None</td>\n",
       "      <td>Banks</td>\n",
       "      <td>-28.786579</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Base Metals</th>\n",
       "      <td>2020-10-29</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>2020-10-30</td>\n",
       "      <td>None</td>\n",
       "      <td>Base Metals</td>\n",
       "      <td>-7.958499</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Basic &amp; Diversified Chemicals</th>\n",
       "      <td>2020-10-23</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>2020-10-30</td>\n",
       "      <td>None</td>\n",
       "      <td>Basic &amp; Diversified Chemicals</td>\n",
       "      <td>-9.892749</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Biotech</th>\n",
       "      <td>2020-10-23</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>2020-10-30</td>\n",
       "      <td>None</td>\n",
       "      <td>Biotech</td>\n",
       "      <td>6.325447</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Building Construction</th>\n",
       "      <td>2020-10-30</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>2020-10-30</td>\n",
       "      <td>None</td>\n",
       "      <td>Building Construction</td>\n",
       "      <td>-9.793770</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Building Maintenance Services</th>\n",
       "      <td>2020-10-26</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>2020-10-30</td>\n",
       "      <td>None</td>\n",
       "      <td>Building Maintenance Services</td>\n",
       "      <td>-4.031674</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Building Materials</th>\n",
       "      <td>2020-10-22</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>2020-10-30</td>\n",
       "      <td>None</td>\n",
       "      <td>Building Materials</td>\n",
       "      <td>-5.629307</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Building Products</th>\n",
       "      <td>2020-10-30</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>2020-10-30</td>\n",
       "      <td>GEBN SE Equity</td>\n",
       "      <td>Building Products</td>\n",
       "      <td>-6.993415</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Casinos &amp; Gaming</th>\n",
       "      <td>2020-10-27</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>2020-10-30</td>\n",
       "      <td>None</td>\n",
       "      <td>Casinos &amp; Gaming</td>\n",
       "      <td>-1.769199</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Cement &amp; Aggregates</th>\n",
       "      <td>2020-10-28</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>2020-10-30</td>\n",
       "      <td>None</td>\n",
       "      <td>Cement &amp; Aggregates</td>\n",
       "      <td>-8.126905</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Chemicals Distribution</th>\n",
       "      <td>2020-10-28</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>2020-10-30</td>\n",
       "      <td>BNR GY Equity</td>\n",
       "      <td>Chemicals Distribution</td>\n",
       "      <td>-7.807016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Commercial Vehicles</th>\n",
       "      <td>2020-10-30</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>2020-10-30</td>\n",
       "      <td>VOLVB SS Equity</td>\n",
       "      <td>Commercial Vehicles</td>\n",
       "      <td>-21.759930</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Comml &amp; Res Bldg Equip &amp; Sys</th>\n",
       "      <td>2020-10-08</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>2020-10-30</td>\n",
       "      <td>None</td>\n",
       "      <td>Comml &amp; Res Bldg Equip &amp; Sys</td>\n",
       "      <td>-3.561198</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Communications Equipment</th>\n",
       "      <td>2020-10-30</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>2020-10-30</td>\n",
       "      <td>None</td>\n",
       "      <td>Communications Equipment</td>\n",
       "      <td>-1.979632</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Construction &amp; Mining Machinery</th>\n",
       "      <td>2020-10-27</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>2020-10-30</td>\n",
       "      <td>None</td>\n",
       "      <td>Construction &amp; Mining Machinery</td>\n",
       "      <td>-14.055645</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Consumer Electronics</th>\n",
       "      <td>2020-10-28</td>\n",
       "      <td>2021-03-31</td>\n",
       "      <td>2020-10-30</td>\n",
       "      <td>LOGN SE Equity</td>\n",
       "      <td>Consumer Electronics</td>\n",
       "      <td>36.105863</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Consumer Finance</th>\n",
       "      <td>2020-10-15</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>2020-10-30</td>\n",
       "      <td>NETW LN Equity</td>\n",
       "      <td>Consumer Finance</td>\n",
       "      <td>-16.991633</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Containers &amp; Packaging</th>\n",
       "      <td>2020-10-26</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>2020-10-30</td>\n",
       "      <td>None</td>\n",
       "      <td>Containers &amp; Packaging</td>\n",
       "      <td>-1.788594</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                REVISION_DATE PERIOD_END_DATE AS_OF_DATE  \\\n",
       "ID                                                                         \n",
       "Advertising & Marketing            2020-10-29      2020-12-31 2020-10-30   \n",
       "Agricultural Chemicals             2020-10-30      2020-12-31 2020-10-30   \n",
       "Agricultural Machinery             2020-10-27      2020-12-31 2020-10-30   \n",
       "Agricultural Producers             2020-10-16      2021-06-30 2020-10-30   \n",
       "Aircraft & Parts                   2020-10-29      2020-12-31 2020-10-30   \n",
       "Airlines                           2020-10-30      2020-12-31 2020-10-30   \n",
       "Alcoholic Beverages                2020-10-26      2021-03-31 2020-10-30   \n",
       "Apparel, Footwear & Acc Design     2020-10-29      2021-03-31 2020-10-30   \n",
       "Application Software               2020-10-27      2020-12-31 2020-10-30   \n",
       "Auto Parts                         2020-10-28      2020-12-31 2020-10-30   \n",
       "Automobiles                        2020-10-29      2020-12-31 2020-10-30   \n",
       "Automotive Wholesalers             2020-09-23      2020-12-31 2020-10-30   \n",
       "Banks                              2020-10-23      2020-12-31 2020-10-30   \n",
       "Base Metals                        2020-10-29      2020-12-31 2020-10-30   \n",
       "Basic & Diversified Chemicals      2020-10-23      2020-12-31 2020-10-30   \n",
       "Biotech                            2020-10-23      2020-12-31 2020-10-30   \n",
       "Building Construction              2020-10-30      2020-12-31 2020-10-30   \n",
       "Building Maintenance Services      2020-10-26      2020-12-31 2020-10-30   \n",
       "Building Materials                 2020-10-22      2020-12-31 2020-10-30   \n",
       "Building Products                  2020-10-30      2020-12-31 2020-10-30   \n",
       "Casinos & Gaming                   2020-10-27      2020-12-31 2020-10-30   \n",
       "Cement & Aggregates                2020-10-28      2020-12-31 2020-10-30   \n",
       "Chemicals Distribution             2020-10-28      2020-12-31 2020-10-30   \n",
       "Commercial Vehicles                2020-10-30      2020-12-31 2020-10-30   \n",
       "Comml & Res Bldg Equip & Sys       2020-10-08      2020-12-31 2020-10-30   \n",
       "Communications Equipment           2020-10-30      2020-12-31 2020-10-30   \n",
       "Construction & Mining Machinery    2020-10-27      2020-12-31 2020-10-30   \n",
       "Consumer Electronics               2020-10-28      2021-03-31 2020-10-30   \n",
       "Consumer Finance                   2020-10-15      2020-12-31 2020-10-30   \n",
       "Containers & Packaging             2020-10-26      2020-12-31 2020-10-30   \n",
       "\n",
       "                                        ORIG_IDS  \\\n",
       "ID                                                 \n",
       "Advertising & Marketing                     None   \n",
       "Agricultural Chemicals             YAR NO Equity   \n",
       "Agricultural Machinery                      None   \n",
       "Agricultural Producers                      None   \n",
       "Aircraft & Parts                            None   \n",
       "Airlines                                    None   \n",
       "Alcoholic Beverages                         None   \n",
       "Apparel, Footwear & Acc Design              None   \n",
       "Application Software                        None   \n",
       "Auto Parts                                  None   \n",
       "Automobiles                                 None   \n",
       "Automotive Wholesalers            INCH LN Equity   \n",
       "Banks                                       None   \n",
       "Base Metals                                 None   \n",
       "Basic & Diversified Chemicals               None   \n",
       "Biotech                                     None   \n",
       "Building Construction                       None   \n",
       "Building Maintenance Services               None   \n",
       "Building Materials                          None   \n",
       "Building Products                 GEBN SE Equity   \n",
       "Casinos & Gaming                            None   \n",
       "Cement & Aggregates                         None   \n",
       "Chemicals Distribution             BNR GY Equity   \n",
       "Commercial Vehicles              VOLVB SS Equity   \n",
       "Comml & Res Bldg Equip & Sys                None   \n",
       "Communications Equipment                    None   \n",
       "Construction & Mining Machinery             None   \n",
       "Consumer Electronics              LOGN SE Equity   \n",
       "Consumer Finance                  NETW LN Equity   \n",
       "Containers & Packaging                      None   \n",
       "\n",
       "                                CLASSIFICATION_NAME(CLASSIFICATION_SCHEME.BICS,CLASSIFICATION_LEVEL.4)  \\\n",
       "ID                                                                                                       \n",
       "Advertising & Marketing                                    Advertising & Marketing                       \n",
       "Agricultural Chemicals                                      Agricultural Chemicals                       \n",
       "Agricultural Machinery                                      Agricultural Machinery                       \n",
       "Agricultural Producers                                      Agricultural Producers                       \n",
       "Aircraft & Parts                                                  Aircraft & Parts                       \n",
       "Airlines                                                                  Airlines                       \n",
       "Alcoholic Beverages                                            Alcoholic Beverages                       \n",
       "Apparel, Footwear & Acc Design                      Apparel, Footwear & Acc Design                       \n",
       "Application Software                                          Application Software                       \n",
       "Auto Parts                                                              Auto Parts                       \n",
       "Automobiles                                                            Automobiles                       \n",
       "Automotive Wholesalers                                      Automotive Wholesalers                       \n",
       "Banks                                                                        Banks                       \n",
       "Base Metals                                                            Base Metals                       \n",
       "Basic & Diversified Chemicals                        Basic & Diversified Chemicals                       \n",
       "Biotech                                                                    Biotech                       \n",
       "Building Construction                                        Building Construction                       \n",
       "Building Maintenance Services                        Building Maintenance Services                       \n",
       "Building Materials                                              Building Materials                       \n",
       "Building Products                                                Building Products                       \n",
       "Casinos & Gaming                                                  Casinos & Gaming                       \n",
       "Cement & Aggregates                                            Cement & Aggregates                       \n",
       "Chemicals Distribution                                      Chemicals Distribution                       \n",
       "Commercial Vehicles                                            Commercial Vehicles                       \n",
       "Comml & Res Bldg Equip & Sys                          Comml & Res Bldg Equip & Sys                       \n",
       "Communications Equipment                                  Communications Equipment                       \n",
       "Construction & Mining Machinery                    Construction & Mining Machinery                       \n",
       "Consumer Electronics                                          Consumer Electronics                       \n",
       "Consumer Finance                                                  Consumer Finance                       \n",
       "Containers & Packaging                                      Containers & Packaging                       \n",
       "\n",
       "                                 Securities per Sector  \n",
       "ID                                                      \n",
       "Advertising & Marketing                     -15.001937  \n",
       "Agricultural Chemicals                       -7.575758  \n",
       "Agricultural Machinery                      -11.360248  \n",
       "Agricultural Producers                        4.104890  \n",
       "Aircraft & Parts                            -27.760577  \n",
       "Airlines                                    -63.502124  \n",
       "Alcoholic Beverages                          -2.724702  \n",
       "Apparel, Footwear & Acc Design              -13.812955  \n",
       "Application Software                          0.943660  \n",
       "Auto Parts                                  -15.738833  \n",
       "Automobiles                                 -12.899304  \n",
       "Automotive Wholesalers                      -26.259191  \n",
       "Banks                                       -28.786579  \n",
       "Base Metals                                  -7.958499  \n",
       "Basic & Diversified Chemicals                -9.892749  \n",
       "Biotech                                       6.325447  \n",
       "Building Construction                        -9.793770  \n",
       "Building Maintenance Services                -4.031674  \n",
       "Building Materials                           -5.629307  \n",
       "Building Products                            -6.993415  \n",
       "Casinos & Gaming                             -1.769199  \n",
       "Cement & Aggregates                          -8.126905  \n",
       "Chemicals Distribution                       -7.807016  \n",
       "Commercial Vehicles                         -21.759930  \n",
       "Comml & Res Bldg Equip & Sys                 -3.561198  \n",
       "Communications Equipment                     -1.979632  \n",
       "Construction & Mining Machinery             -14.055645  \n",
       "Consumer Electronics                         36.105863  \n",
       "Consumer Finance                            -16.991633  \n",
       "Containers & Packaging                       -1.788594  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "index = bq.univ.members('SXXP Index')\n",
    "sector = bq.data.classification_name('BICS','4')\n",
    "criteria = sector.in_(['Alcoholic Beverages', 'Tobacco Products']).not_()\n",
    "filtered_index = bq.univ.filter(index, criteria)\n",
    "#Median Sales Growth by Sector\n",
    "sales_growth = bq.data.sales_growth(FPT='A',FPO='1')\n",
    "growth_per_sector = sales_growth.group(sector).median()\n",
    "\n",
    "req = bql.Request(index, {'Securities per Sector': growth_per_sector}) \n",
    "res = bq.execute(req)\n",
    "data = res[0].df().head(30)\n",
    "data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "</a>[Return to Index ↑](#s0)<a href='#s0'></a>\n",
    "\n",
    "<a id='s3.5'></a>\n",
    "### 3.5 Matches()\n",
    "\n",
    "- Matches performs calculation on a subset of data defined by conditions\n",
    "- It doesn't screen out results which do not match your criteria, like filter does\n",
    "- Will derive different results based on the universe which is being analyzed"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "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>IBM US Equity</th>\n",
       "      <td>2020-09-30</td>\n",
       "      <td>USD</td>\n",
       "      <td>121.67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>IBM US Equity</th>\n",
       "      <td>2020-10-01</td>\n",
       "      <td>USD</td>\n",
       "      <td>121.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>IBM US Equity</th>\n",
       "      <td>2020-10-02</td>\n",
       "      <td>USD</td>\n",
       "      <td>120.57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>IBM US Equity</th>\n",
       "      <td>2020-10-05</td>\n",
       "      <td>USD</td>\n",
       "      <td>122.01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>IBM US Equity</th>\n",
       "      <td>2020-10-06</td>\n",
       "      <td>USD</td>\n",
       "      <td>121.97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>IBM US Equity</th>\n",
       "      <td>2020-10-07</td>\n",
       "      <td>USD</td>\n",
       "      <td>124.07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>IBM US Equity</th>\n",
       "      <td>2020-10-08</td>\n",
       "      <td>USD</td>\n",
       "      <td>131.49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>IBM US Equity</th>\n",
       "      <td>2020-10-09</td>\n",
       "      <td>USD</td>\n",
       "      <td>127.79</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>IBM US Equity</th>\n",
       "      <td>2020-10-12</td>\n",
       "      <td>USD</td>\n",
       "      <td>127.21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>IBM US Equity</th>\n",
       "      <td>2020-10-13</td>\n",
       "      <td>USD</td>\n",
       "      <td>125.10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>IBM US Equity</th>\n",
       "      <td>2020-10-14</td>\n",
       "      <td>USD</td>\n",
       "      <td>125.94</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>IBM US Equity</th>\n",
       "      <td>2020-10-15</td>\n",
       "      <td>USD</td>\n",
       "      <td>124.89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>IBM US Equity</th>\n",
       "      <td>2020-10-16</td>\n",
       "      <td>USD</td>\n",
       "      <td>125.93</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>IBM US Equity</th>\n",
       "      <td>2020-10-19</td>\n",
       "      <td>USD</td>\n",
       "      <td>125.52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>IBM US Equity</th>\n",
       "      <td>2020-10-20</td>\n",
       "      <td>USD</td>\n",
       "      <td>117.37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>IBM US Equity</th>\n",
       "      <td>2020-10-21</td>\n",
       "      <td>USD</td>\n",
       "      <td>115.06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>IBM US Equity</th>\n",
       "      <td>2020-10-22</td>\n",
       "      <td>USD</td>\n",
       "      <td>115.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>IBM US Equity</th>\n",
       "      <td>2020-10-23</td>\n",
       "      <td>USD</td>\n",
       "      <td>116.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>IBM US Equity</th>\n",
       "      <td>2020-10-26</td>\n",
       "      <td>USD</td>\n",
       "      <td>112.22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>IBM US Equity</th>\n",
       "      <td>2020-10-27</td>\n",
       "      <td>USD</td>\n",
       "      <td>110.56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>IBM US Equity</th>\n",
       "      <td>2020-10-28</td>\n",
       "      <td>USD</td>\n",
       "      <td>106.65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>IBM US Equity</th>\n",
       "      <td>2020-10-29</td>\n",
       "      <td>USD</td>\n",
       "      <td>108.91</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    DATE CURRENCY   Price\n",
       "ID                                       \n",
       "IBM US Equity 2020-09-30      USD  121.67\n",
       "IBM US Equity 2020-10-01      USD  121.09\n",
       "IBM US Equity 2020-10-02      USD  120.57\n",
       "IBM US Equity 2020-10-05      USD  122.01\n",
       "IBM US Equity 2020-10-06      USD  121.97\n",
       "IBM US Equity 2020-10-07      USD  124.07\n",
       "IBM US Equity 2020-10-08      USD  131.49\n",
       "IBM US Equity 2020-10-09      USD  127.79\n",
       "IBM US Equity 2020-10-12      USD  127.21\n",
       "IBM US Equity 2020-10-13      USD  125.10\n",
       "IBM US Equity 2020-10-14      USD  125.94\n",
       "IBM US Equity 2020-10-15      USD  124.89\n",
       "IBM US Equity 2020-10-16      USD  125.93\n",
       "IBM US Equity 2020-10-19      USD  125.52\n",
       "IBM US Equity 2020-10-20      USD  117.37\n",
       "IBM US Equity 2020-10-21      USD  115.06\n",
       "IBM US Equity 2020-10-22      USD  115.76\n",
       "IBM US Equity 2020-10-23      USD  116.00\n",
       "IBM US Equity 2020-10-26      USD  112.22\n",
       "IBM US Equity 2020-10-27      USD  110.56\n",
       "IBM US Equity 2020-10-28      USD  106.65\n",
       "IBM US Equity 2020-10-29      USD  108.91"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Pull Price for Vodafone over the last month\n",
    "stock = 'IBM US Equity'\n",
    "px = bq.data.px_last(dates=bq.func.range('-1m','0d')).dropna()\n",
    "\n",
    "req = bql.Request(stock, {'Price': px}) \n",
    "res = bq.execute(req)\n",
    "data = res[0].df()\n",
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "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 High</th>\n",
       "      <th>Partial Errors</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>IBM US Equity</th>\n",
       "      <td>NaT</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Error, no match found for identifier.</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              DATE CURRENCY  Price High                         Partial Errors\n",
       "ID                                                                            \n",
       "IBM US Equity  NaT     None         NaN  Error, no match found for identifier."
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Pull Price for Vodafone over the last month over a threshold\n",
    "stock = 'IBM US Equity'\n",
    "px = bq.data.px_last(dates=bq.func.range('-1m','0d')).dropna()\n",
    "px_high = px.matches(px > 150)\n",
    "\n",
    "req = bql.Request(stock, {'Price High': px_high}) \n",
    "res = bq.execute(req)\n",
    "data = res[0].df()\n",
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "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 High</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>IBM US Equity</th>\n",
       "      <td>2020-10-08</td>\n",
       "      <td>USD</td>\n",
       "      <td>131.49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>IBM US Equity</th>\n",
       "      <td>2020-10-09</td>\n",
       "      <td>USD</td>\n",
       "      <td>127.79</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>IBM US Equity</th>\n",
       "      <td>2020-10-12</td>\n",
       "      <td>USD</td>\n",
       "      <td>127.21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>IBM US Equity</th>\n",
       "      <td>2020-10-14</td>\n",
       "      <td>USD</td>\n",
       "      <td>125.94</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>IBM US Equity</th>\n",
       "      <td>2020-10-16</td>\n",
       "      <td>USD</td>\n",
       "      <td>125.93</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>IBM US Equity</th>\n",
       "      <td>2020-10-19</td>\n",
       "      <td>USD</td>\n",
       "      <td>125.52</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    DATE CURRENCY  Price High\n",
       "ID                                           \n",
       "IBM US Equity 2020-10-08      USD      131.49\n",
       "IBM US Equity 2020-10-09      USD      127.79\n",
       "IBM US Equity 2020-10-12      USD      127.21\n",
       "IBM US Equity 2020-10-14      USD      125.94\n",
       "IBM US Equity 2020-10-16      USD      125.93\n",
       "IBM US Equity 2020-10-19      USD      125.52"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#This allows you to perform functions on the data item\n",
    "\n",
    "stock = 'IBM US Equity'\n",
    "px = bq.data.px_last(dates=bq.func.range('-1m','0d')).dropna()\n",
    "px_q = px.quantile(0.75)\n",
    "px_top = px.matches(px > px_q)\n",
    "\n",
    "req = bql.Request(stock, {'Price High': px_top}) \n",
    "res = bq.execute(req)\n",
    "data = res[0].df()\n",
    "data\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Matches is perfect when you need multiple calculations performed on the same universe"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### <span style=\"color:darkorange\">Q </span>: Let's get the total return for stocks since the date of their lowest cumulative return"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "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>Total Cum Return Since Minimum</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>ABBN SE Equity</th>\n",
       "      <td>2020-10-30</td>\n",
       "      <td>1.563162</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CFR SE Equity</th>\n",
       "      <td>2020-10-30</td>\n",
       "      <td>1.184096</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CSGN SE Equity</th>\n",
       "      <td>2020-10-30</td>\n",
       "      <td>1.329279</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GEBN SE Equity</th>\n",
       "      <td>2020-10-30</td>\n",
       "      <td>1.392957</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GIVN SE Equity</th>\n",
       "      <td>2020-10-30</td>\n",
       "      <td>1.430906</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>LHN SE Equity</th>\n",
       "      <td>2020-10-30</td>\n",
       "      <td>1.427471</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>LONN SE Equity</th>\n",
       "      <td>2020-10-30</td>\n",
       "      <td>1.727071</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NESN SE Equity</th>\n",
       "      <td>2020-10-30</td>\n",
       "      <td>1.168399</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NOVN SE Equity</th>\n",
       "      <td>2020-10-30</td>\n",
       "      <td>1.017582</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ROG SE Equity</th>\n",
       "      <td>2020-10-30</td>\n",
       "      <td>1.096718</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SCMN SE Equity</th>\n",
       "      <td>2020-10-30</td>\n",
       "      <td>1.038318</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SGSN SE Equity</th>\n",
       "      <td>2020-10-30</td>\n",
       "      <td>1.194876</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SLHN SE Equity</th>\n",
       "      <td>2020-10-30</td>\n",
       "      <td>1.238968</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SREN SE Equity</th>\n",
       "      <td>2020-10-30</td>\n",
       "      <td>1.292303</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>UBSG SE Equity</th>\n",
       "      <td>2020-10-30</td>\n",
       "      <td>1.480403</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>UHR SE Equity</th>\n",
       "      <td>2020-10-30</td>\n",
       "      <td>1.244486</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ZURN SE Equity</th>\n",
       "      <td>2020-10-30</td>\n",
       "      <td>1.212937</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ALC SE Equity</th>\n",
       "      <td>2020-10-30</td>\n",
       "      <td>1.349633</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SIKA SE Equity</th>\n",
       "      <td>2020-10-30</td>\n",
       "      <td>1.702655</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PGHN SE Equity</th>\n",
       "      <td>2020-10-30</td>\n",
       "      <td>1.540096</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     DATE  Total Cum Return Since Minimum\n",
       "ID                                                       \n",
       "ABBN SE Equity 2020-10-30                        1.563162\n",
       "CFR SE Equity  2020-10-30                        1.184096\n",
       "CSGN SE Equity 2020-10-30                        1.329279\n",
       "GEBN SE Equity 2020-10-30                        1.392957\n",
       "GIVN SE Equity 2020-10-30                        1.430906\n",
       "LHN SE Equity  2020-10-30                        1.427471\n",
       "LONN SE Equity 2020-10-30                        1.727071\n",
       "NESN SE Equity 2020-10-30                        1.168399\n",
       "NOVN SE Equity 2020-10-30                        1.017582\n",
       "ROG SE Equity  2020-10-30                        1.096718\n",
       "SCMN SE Equity 2020-10-30                        1.038318\n",
       "SGSN SE Equity 2020-10-30                        1.194876\n",
       "SLHN SE Equity 2020-10-30                        1.238968\n",
       "SREN SE Equity 2020-10-30                        1.292303\n",
       "UBSG SE Equity 2020-10-30                        1.480403\n",
       "UHR SE Equity  2020-10-30                        1.244486\n",
       "ZURN SE Equity 2020-10-30                        1.212937\n",
       "ALC SE Equity  2020-10-30                        1.349633\n",
       "SIKA SE Equity 2020-10-30                        1.702655\n",
       "PGHN SE Equity 2020-10-30                        1.540096"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "index = bq.univ.members('SMI Index')\n",
    "#let's calculate cumulative return\n",
    "tot_ret_time_series = bq.data.day_to_day_tot_return_gross_dvds(start='-1y', end='0d')\n",
    "cum_ret = (1 + tot_ret_time_series).ln().cumsum().exp()\n",
    "#Find the date of the minimum Cumulative return\n",
    "min_ret = cum_ret.min()\n",
    "#This finds the date column of the min return\n",
    "min_ret_date = min_ret['DATE']\n",
    "#Find all the returns with a later date than the date of the minimum cumulative return\n",
    "cum_ret_since_min = bq.func.matches(cum_ret, cum_ret['DATE'] >= min_ret_date)\n",
    "#Calculate the latest relative return relative to the minimum cum return during this period\n",
    "tot_ret_adj = cum_ret_since_min/min_ret\n",
    "tot_ret_adj_latest = tot_ret_adj.last('1')\n",
    "\n",
    "req = bql.Request(index, {'Total Cum Return Since Minimum': tot_ret_adj_latest}) \n",
    "res = bq.execute(req)\n",
    "data = res[0].df()\n",
    "data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<img src=\"../../Visualisations/Matches 2.jpg\" style=\"width: 800px;\"/>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### <span style=\"color:darkorange\">Q </span>: What was the Z-Score of PE Ratios for the DOW Jones where the ZScore was greater than 1.5?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "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>ORIG_IDS</th>\n",
       "      <th>IDENTITYGROUP</th>\n",
       "      <th>GROUPZSCORE(PE_RATIO())</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>CRM UN Equity</th>\n",
       "      <td>2020-10-30</td>\n",
       "      <td>2020-08-28</td>\n",
       "      <td>2020-07-31</td>\n",
       "      <td>CRM UN Equity</td>\n",
       "      <td>IdentityGroup</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              AS_OF_DATE REVISION_DATE PERIOD_END_DATE       ORIG_IDS  \\\n",
       "ID                                                                      \n",
       "CRM UN Equity 2020-10-30    2020-08-28      2020-07-31  CRM UN Equity   \n",
       "\n",
       "               IDENTITYGROUP  GROUPZSCORE(PE_RATIO())  \n",
       "ID                                                     \n",
       "CRM UN Equity  IdentityGroup                      NaN  "
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "univ = bq.univ.members('indu index')\n",
    "pe = bq.data.pe_ratio()\n",
    "pe_zscore = pe.groupzscore()\n",
    "# screening with filter\n",
    "filtered_univ = bq.univ.filter(univ, pe_zscore>1.5)\n",
    "req = bql.Request(filtered_univ,pe_zscore )\n",
    "res = bq.execute(req)\n",
    "data = res[0].df()\n",
    "data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The results for our screen do not match our criteria because:\n",
    "- We screened our universe for Zscore greater than 1.5\n",
    "- The results showed us the Z-score of PE for the Z-Scores that were above 1.5 (Z-Score was calculated on two levels)\n",
    "- To see the results we asked for the first time, we need matches()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "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>ACT_EST_DATA</th>\n",
       "      <th>AS_OF_DATE</th>\n",
       "      <th>ORIG_IDS</th>\n",
       "      <th>DROPNA(MATCHES(GROUPZSCORE(PE_RATIO()),(GROUPZSCORE(PE_RATIO())&gt;1.5)),remove_id=true)</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>CRM UN Equity</th>\n",
       "      <td>2020-08-28</td>\n",
       "      <td>2020-07-31</td>\n",
       "      <td>A</td>\n",
       "      <td>2020-10-30</td>\n",
       "      <td>None</td>\n",
       "      <td>5.194217</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              REVISION_DATE PERIOD_END_DATE ACT_EST_DATA AS_OF_DATE ORIG_IDS  \\\n",
       "ID                                                                             \n",
       "CRM UN Equity    2020-08-28      2020-07-31            A 2020-10-30     None   \n",
       "\n",
       "               DROPNA(MATCHES(GROUPZSCORE(PE_RATIO()),(GROUPZSCORE(PE_RATIO())>1.5)),remove_id=true)  \n",
       "ID                                                                                                    \n",
       "CRM UN Equity                                           5.194217                                      "
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "univ = bq.univ.members('indu index')\n",
    "pe = bq.data.pe_ratio()\n",
    "pe_zscore = pe.groupzscore()\n",
    "#screening with matches\n",
    "filterd_pe = bq.func.matches(pe_zscore,pe_zscore>1.5).dropna(remove_id = True)\n",
    "req = bql.Request(univ, filterd_pe)\n",
    "res = bq.execute(req)\n",
    "data = res[0].df()\n",
    "data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "</a>[Return to Index ↑](#s0)<a href='#s0'></a>\n",
    "\n",
    "<a id='s4'></a>\n",
    "\n",
    "<span style=\"color:darkorange; font-size:2em\"> 4 Scoring model </span>\n",
    "<br>\n",
    "\n",
    "Let's apply everything we learnt and apply it to an actual situation\n",
    "- Different ways to normalize data for peer comparison\n",
    "  - Weighting of fundamental data\n",
    "  - use if_() statements directly on BQL to standardize logic"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Considering the repetition of inputs, it is helpful to create a dictionary as a shortcut for parameters:\n",
    "current = {'FPO':'0', 'FPT':'A'}\n",
    "prev = {'FPO':'-1', 'FPT':'A'}\n",
    "#shortening func and data\n",
    "f = bq.func\n",
    "d = bq.data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "</a>[Return to Index ↑](#s0)<a href='#s0'></a>\n",
    "\n",
    "<a id='s4.1'></a>\n",
    "\n",
    "### 4.1 Piotroski F-Score\n",
    "\n",
    "<img src=\"../../Visualisations/F-Score.jpg\" style=\"width: 600px;\"/>\n",
    "\n",
    "What is the distribution of quality across sectors of S&P 500 Index?\n",
    "\n",
    "- Use if_() to create scores of 1 or 0 based on custom criteria\n",
    "- Combine with group() to aggregate scoring metrics to generate an overview across the universe\n",
    "- https://en.wikipedia.org/wiki/Piotroski_F-Score"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "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>ORIG_IDS</th>\n",
       "      <th>CLASSIFICATION_NAME()</th>\n",
       "      <th>Average F-Score Per Sector</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>Communications</th>\n",
       "      <td>None</td>\n",
       "      <td>Communications</td>\n",
       "      <td>3.923077</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Consumer Discretionary</th>\n",
       "      <td>None</td>\n",
       "      <td>Consumer Discretionary</td>\n",
       "      <td>3.625000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Consumer Staples</th>\n",
       "      <td>None</td>\n",
       "      <td>Consumer Staples</td>\n",
       "      <td>4.377358</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Energy</th>\n",
       "      <td>None</td>\n",
       "      <td>Energy</td>\n",
       "      <td>3.809524</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Financials</th>\n",
       "      <td>None</td>\n",
       "      <td>Financials</td>\n",
       "      <td>3.843137</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Health Care</th>\n",
       "      <td>None</td>\n",
       "      <td>Health Care</td>\n",
       "      <td>4.471698</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Industrials</th>\n",
       "      <td>None</td>\n",
       "      <td>Industrials</td>\n",
       "      <td>4.279279</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Materials</th>\n",
       "      <td>None</td>\n",
       "      <td>Materials</td>\n",
       "      <td>3.816667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Real Estate</th>\n",
       "      <td>None</td>\n",
       "      <td>Real Estate</td>\n",
       "      <td>4.394737</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Technology</th>\n",
       "      <td>None</td>\n",
       "      <td>Technology</td>\n",
       "      <td>4.736842</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Utilities</th>\n",
       "      <td>None</td>\n",
       "      <td>Utilities</td>\n",
       "      <td>4.482759</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                       ORIG_IDS   CLASSIFICATION_NAME()  \\\n",
       "ID                                                        \n",
       "Communications             None          Communications   \n",
       "Consumer Discretionary     None  Consumer Discretionary   \n",
       "Consumer Staples           None        Consumer Staples   \n",
       "Energy                     None                  Energy   \n",
       "Financials                 None              Financials   \n",
       "Health Care                None             Health Care   \n",
       "Industrials                None             Industrials   \n",
       "Materials                  None               Materials   \n",
       "Real Estate                None             Real Estate   \n",
       "Technology                 None              Technology   \n",
       "Utilities                  None               Utilities   \n",
       "\n",
       "                        Average F-Score Per Sector  \n",
       "ID                                                  \n",
       "Communications                            3.923077  \n",
       "Consumer Discretionary                    3.625000  \n",
       "Consumer Staples                          4.377358  \n",
       "Energy                                    3.809524  \n",
       "Financials                                3.843137  \n",
       "Health Care                               4.471698  \n",
       "Industrials                               4.279279  \n",
       "Materials                                 3.816667  \n",
       "Real Estate                               4.394737  \n",
       "Technology                                4.736842  \n",
       "Utilities                                 4.482759  "
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Universe\n",
    "index = bq.univ.members('SXXP Index')\n",
    "sector = bq.data.classification_name()\n",
    "\n",
    "roa = f.if_( d.return_on_asset(**current) > 0 , 1, 0 )\n",
    "ocf = f.if_( d.cf_cash_from_oper(**current) > 0, 1, 0 )\n",
    "roa_chg = f.if_( d.return_on_asset(**current) > d.return_on_asset(**prev) , 1, 0 )\n",
    "acc = f.if_( (d.cf_cash_from_oper(**current) / d.bs_tot_asset(**current)) > d.return_on_asset(**current), 1, 0 )\n",
    "lvg_chg = f.if_( d.lt_debt_to_tot_asset(**current) < d.lt_debt_to_tot_asset(**prev), 1, 0 )\n",
    "cr_chg = f.if_( d.cur_ratio(**current) > d.cur_ratio(**prev), 1, 0 )\n",
    "shs_chg = f.if_( d.bs_sh_out(**current) > d.bs_sh_out(**prev), 1, 0 )\n",
    "gm_chg = f.if_( d.gross_margin(**current) > d.gross_margin(**prev), 1, 0 ) \n",
    "asset_turn = f.if_( d.asset_turnover(**current) > d.asset_turnover(**prev), 1, 0 )\n",
    "fscore = roa + ocf + roa_chg + acc + lvg_chg + cr_chg + shs_chg + gm_chg + asset_turn\n",
    "\n",
    "fscore_avg = fscore.group(sector).avg()\n",
    "\n",
    "req = bql.Request(index, {'Average F-Score Per Sector': fscore_avg}) \n",
    "res = bq.execute(req)\n",
    "data = res[0].df()\n",
    "data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "</a>[Return to Index ↑](#s0)<a href='#s0'></a>\n",
    "\n",
    "<a id='s4.2'></a>\n",
    "\n",
    "### 4.2 Beneish M-Score\n",
    "\n",
    "How can you analyze companies likely to be earnings manipulators based on the Beneish-M?\n",
    "- Calculate changes in calculated fundamental factors\n",
    "- M Score below -2.22 unlikely to manipulate earnings, above - 1.78 probable manipulator\n",
    "- https://en.wikipedia.org/wiki/Beneish_M-Score\n",
    "- Combine with filter() to narrow down on securities worth worth analysis"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<img src=\"../../Visualisations/M Score.jpg\" style=\"width: 600px;\"/>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "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>CURRENCY</th>\n",
       "      <th>M-Score</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>000985 CH Equity</th>\n",
       "      <td>2020-04-18</td>\n",
       "      <td>2019-12-31</td>\n",
       "      <td>2020-10-30</td>\n",
       "      <td>None</td>\n",
       "      <td>inf</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>000752 CH Equity</th>\n",
       "      <td>2020-04-28</td>\n",
       "      <td>2019-12-31</td>\n",
       "      <td>2020-10-30</td>\n",
       "      <td>None</td>\n",
       "      <td>inf</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>000567 CH Equity</th>\n",
       "      <td>2020-04-26</td>\n",
       "      <td>2019-12-31</td>\n",
       "      <td>2020-10-30</td>\n",
       "      <td>None</td>\n",
       "      <td>inf</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>000953 CH Equity</th>\n",
       "      <td>2020-04-28</td>\n",
       "      <td>2019-12-31</td>\n",
       "      <td>2020-10-30</td>\n",
       "      <td>None</td>\n",
       "      <td>4414.653865</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>000982 CH Equity</th>\n",
       "      <td>2020-04-28</td>\n",
       "      <td>2019-12-31</td>\n",
       "      <td>2020-10-30</td>\n",
       "      <td>None</td>\n",
       "      <td>206.753624</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>300029 CH Equity</th>\n",
       "      <td>2020-04-28</td>\n",
       "      <td>2019-12-31</td>\n",
       "      <td>2020-10-30</td>\n",
       "      <td>None</td>\n",
       "      <td>130.819936</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>000987 CH Equity</th>\n",
       "      <td>2020-02-28</td>\n",
       "      <td>2019-12-31</td>\n",
       "      <td>2020-10-30</td>\n",
       "      <td>None</td>\n",
       "      <td>25.994627</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>000996 CH Equity</th>\n",
       "      <td>2020-04-30</td>\n",
       "      <td>2019-12-31</td>\n",
       "      <td>2020-10-30</td>\n",
       "      <td>None</td>\n",
       "      <td>24.511451</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>002210 CH Equity</th>\n",
       "      <td>2020-06-30</td>\n",
       "      <td>2019-12-31</td>\n",
       "      <td>2020-10-30</td>\n",
       "      <td>None</td>\n",
       "      <td>23.969235</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>002507 CH Equity</th>\n",
       "      <td>2020-03-16</td>\n",
       "      <td>2019-12-31</td>\n",
       "      <td>2020-10-30</td>\n",
       "      <td>None</td>\n",
       "      <td>17.630094</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 REVISION_DATE PERIOD_END_DATE AS_OF_DATE CURRENCY  \\\n",
       "ID                                                                   \n",
       "000985 CH Equity    2020-04-18      2019-12-31 2020-10-30     None   \n",
       "000752 CH Equity    2020-04-28      2019-12-31 2020-10-30     None   \n",
       "000567 CH Equity    2020-04-26      2019-12-31 2020-10-30     None   \n",
       "000953 CH Equity    2020-04-28      2019-12-31 2020-10-30     None   \n",
       "000982 CH Equity    2020-04-28      2019-12-31 2020-10-30     None   \n",
       "300029 CH Equity    2020-04-28      2019-12-31 2020-10-30     None   \n",
       "000987 CH Equity    2020-02-28      2019-12-31 2020-10-30     None   \n",
       "000996 CH Equity    2020-04-30      2019-12-31 2020-10-30     None   \n",
       "002210 CH Equity    2020-06-30      2019-12-31 2020-10-30     None   \n",
       "002507 CH Equity    2020-03-16      2019-12-31 2020-10-30     None   \n",
       "\n",
       "                      M-Score  \n",
       "ID                             \n",
       "000985 CH Equity          inf  \n",
       "000752 CH Equity          inf  \n",
       "000567 CH Equity          inf  \n",
       "000953 CH Equity  4414.653865  \n",
       "000982 CH Equity   206.753624  \n",
       "300029 CH Equity   130.819936  \n",
       "000987 CH Equity    25.994627  \n",
       "000996 CH Equity    24.511451  \n",
       "002210 CH Equity    23.969235  \n",
       "002507 CH Equity    17.630094  "
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "index = bq.univ.members('SZCOMP Index')\n",
    "#define our factors to be used\n",
    "DSRI = f.znav( ( d.sales_rev_turn(**current) / d.bs_acct_note_rcv(**current) ) / ( d.sales_rev_turn(**prev) / d.bs_acct_note_rcv(**prev) ) )\n",
    "GMI = f.znav( ( d.gross_profit(**current) / d.sales_rev_turn(**current) ) / ( d.gross_profit(**prev) / d.sales_rev_turn(**prev) ) )\n",
    "AQI = f.znav( ( (d.bs_tot_asset(**current) - d.bs_cur_asset_report(**current) - d.bs_net_fix_asset(**current)) / d.bs_tot_asset(**current) ) / ( (d.bs_tot_asset(**prev) - d.bs_cur_asset_report(**prev) - d.bs_net_fix_asset(**prev))/d.bs_tot_asset(**prev)) )\n",
    "SGI = f.znav( d.sales_rev_turn(**current) / d.sales_rev_turn(**prev) )\n",
    "DEPI = f.znav( (d.cf_depr_amort(**prev) / (d.cf_depr_amort(**prev) + d.bs_net_fix_asset(**prev)) ) / (d.cf_depr_amort(**current) / (d.cf_depr_amort(**current) + d.bs_net_fix_asset(**current))) )\n",
    "SGAI = f.znav( (d.is_cogs_to_fe_and_pp_and_g(**current)/d.sales_rev_turn(**current))/(d.is_cogs_to_fe_and_pp_and_g(**prev)/d.sales_rev_turn(**prev)) )\n",
    "TATA = f.znav( (d.is_inc_bef_xo_item(**current) - d.cf_cash_from_oper(**current))/d.bs_tot_asset(**current) )\n",
    "LVGI = f.znav( (d.bs_tot_asset(**current) / ( d.bs_lt_borrow(**current) + d.bs_st_borrow(**current) + d.bs_cur_liab(**current) )) / ( d.bs_tot_asset(**prev) / ( d.bs_lt_borrow(**prev) + d.bs_st_borrow(**prev) + d.bs_cur_liab(**prev) )) )\n",
    "\n",
    "mscore = -4.84 + 0.92 * DSRI + 0.528 * GMI + 0.404 * AQI + 0.892 * SGI + 0.115 * DEPI - 0.172 * SGAI + 4.679 * TATA - 0.327 * LVGI\n",
    "mscore_filter = mscore > -1.78\n",
    "manipulators_likely = bq.univ.filter(index, mscore_filter)\n",
    "mscore_top = mscore.grouprank() <= 10\n",
    "manipulators_top = bq.univ.filter(manipulators_likely, mscore_top)\n",
    "\n",
    "req = bql.Request(manipulators_top, {'M-Score': mscore.groupsort()}) \n",
    "res = bq.execute(req)\n",
    "data = res[0].df()\n",
    "data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "</a>[Return to Index ↑](#s0)<a href='#s0'></a>\n",
    "\n",
    "<a id='s4.3'></a>\n",
    "\n",
    "### 4.3 Scoring Practice\n",
    "\n",
    "Create your own custom factor models using Z-Scores and Matches()\n",
    "- Z-Score normalizes datapoints by measuring no. standard deviations from the mean\n",
    "- Matches allows us to apply multiple overlapping calculations and drill down to the results"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<img src=\"../../Visualisations/Matches 3.jpg\" style=\"width: 800px;\"/>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "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>CURRENCY</th>\n",
       "      <th>Final Score</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>WMH LN Equity</th>\n",
       "      <td>None</td>\n",
       "      <td>9.454</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AGN NA Equity</th>\n",
       "      <td>None</td>\n",
       "      <td>7.741</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RNO FP Equity</th>\n",
       "      <td>None</td>\n",
       "      <td>7.534</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RMG LN Equity</th>\n",
       "      <td>None</td>\n",
       "      <td>5.578</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CCL LN Equity</th>\n",
       "      <td>None</td>\n",
       "      <td>5.154</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              CURRENCY  Final Score\n",
       "ID                                 \n",
       "WMH LN Equity     None        9.454\n",
       "AGN NA Equity     None        7.741\n",
       "RNO FP Equity     None        7.534\n",
       "RMG LN Equity     None        5.578\n",
       "CCL LN Equity     None        5.154"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "index = bq.univ.members('SXXP Index')\n",
    "#Define your own custom factors\n",
    "#BQL allows you to build custom technical analysis analysis\n",
    "momentum = bq.data.rsi(close=bq.data.px_last(), period='222').znav()\n",
    "#Use BQL's custom return calculations\n",
    "profitability = bq.data.alpha(calc_interval=bq.func.range('-252d','0d'), benchmark_ticker='SXXP Index').znav()\n",
    "volatility = bq.data.volatility(calc_interval='252d').znav()\n",
    "value = 0.5* (1/bq.data.px_to_book_ratio()) + 0.5 * (bq.func.avail(bq.data.ebitda(),bq.data.is_oper_inc())/bq.data.curr_entp_val())\n",
    "value = value.znav()\n",
    "#Calculate the Z-scores\n",
    "momentum_z = momentum.groupzscore()\n",
    "profit_z = profitability.groupzscore()\n",
    "vol_z = volatility.groupzscore()\n",
    "value_z = value.groupzscore()\n",
    "#Add up your scores\n",
    "final_score = momentum_z + profit_z + vol_z + value_z\n",
    "#Let's see the top 30% of scores\n",
    "top_30 = bq.func.matches(final_score, final_score> final_score.group().quantile('0.70').ungroup())\n",
    "#Clean our results to remove the rows which did not match our criteria\n",
    "top_30_clean = top_30.dropna(remove_id='True')\n",
    "\n",
    "req = bql.Request(index, {'Final Score': top_30_clean.round('3').groupsort()})\n",
    "res = bq.execute(req)\n",
    "data = res[0].df().head()\n",
    "data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "</a>[Return to Index ↑](#s0)<a href='#s0'></a>\n",
    "\n",
    "<a id='s5'></a>\n",
    "\n",
    "<span style=\"color:green; font-size:2em\"> 5. BQL Equity Advanced Summary </span>\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### BQL\n",
    "\n",
    "- Custom calculations: combining fields, applying BQL functions, using new BQL metrics\n",
    "- Rolling calculations to analyse momentum\n",
    "- Advanced filtering in a more efficient manner and apply negative logic\n",
    "- Apply scoring methodology to normalize combinations of fields and screen\n",
    "\n",
    "### Benefits\n",
    "- Save time and effort cleaning, manipulating, sorting data\n",
    "- Data-efficient analysis\n",
    "- Focus on generating ideas and alpha"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "    "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<span style=\"color:yellow; font-size:2em\"> Congratulations - What's Next? </span>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<img src=\"../../Visualisations/BQL Services.jpg\" style=\"width: 400px;\"/>\n",
    "\n",
    "- This is the beginning: Use BQL to dig deep and conduct in-depth cross-asset financial data analysis\n",
    "- Regular Updates for future releases: Webinars, BQNT Spotlight, BQNT Whatsnew\n",
    "- Connection to other Bloomberg services, eg Portfolio integration (PORT), order management systems (AIM, TOMS), proprietary data (CDE)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "----\n",
    "<p style=\"text-align:center;\">\n",
    "    Click on the links below to continue learning.<br>\n",
    "    <a href=\"2.1 Equity Basics.ipynb\">&larr; Back to the Equity Basics</a>&emsp;&emsp;\n",
    "    <a href=\"#s0\">&uarr; Back to Top </a>&emsp;&emsp;\n",
    "    <a href=\"../../0 Welcome.ipynb\">Back to Home &rarr;</a>\n",
    "    <br>\n",
    "\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
}