1.4. FI Advanced - Reference.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a name=\"learn4-top\"></a> \n",
    "# Bloomberg Python Series: <br><span style=\"color:orange\"> Using the Bloomberg Query Language with Fixed Income Data II </span>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Topics\n",
    "1. [__Recap__](#learn4-topic1)\n",
    "- [1.1 Peer Curve Analysis](#learn4-topic1.1)\n",
    "- [1.2 Index Analysis](#learn4-topic1.2)\n",
    "2. [__Tools for Cross-Asset Analysis__](#learn4-topic2)\n",
    "- [2.1 Nested Filters](#learn4-topic2.1)\n",
    "- [2.2 Data Availability Management](#learn4-topic2.2)\n",
    "- [2.3 Grouping II - Ungroup](#learn4-topic2.3)\n",
    "- [2.4 Cut](#learn4-topic2.4)\n",
    "- [2.5 Relative Evaluation (Groupxyz)](#learn4-topic2.5)\n",
    "- [2.6 Identifying Outliers](#learn4-topic2.6)\n",
    "- [2.7 Issuer](#learn4-topic2.7)\n",
    "- [2.8 Value](#learn4-topic2.8)\n",
    "3. [__Summary__](#learn4-topic3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id='learn4-topic1'></a>\n",
    "\n",
    "<span style=\"color:darkorange; font-size:2em\"> 1 Recap </span>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "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": [
    "### 1.1 Peer Curve Analysis\n",
    "\n",
    "What was the median OAS spread by year of maturity for the bonds of Novartis (NOVN SW), Roche (ROG SW) and GSK (GSK LN)?"
   ]
  },
  {
   "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>ORIG_IDS</th>\n",
       "      <th>YEAR(MATURITY())</th>\n",
       "      <th>Median Yield by Year</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>2021.0</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>None</td>\n",
       "      <td>2021</td>\n",
       "      <td>-0.393240</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022.0</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>None</td>\n",
       "      <td>2022</td>\n",
       "      <td>0.268339</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023.0</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>None</td>\n",
       "      <td>2023</td>\n",
       "      <td>-0.241071</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024.0</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>None</td>\n",
       "      <td>2024</td>\n",
       "      <td>0.191204</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2025.0</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>None</td>\n",
       "      <td>2025</td>\n",
       "      <td>-0.121881</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2026.0</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>None</td>\n",
       "      <td>2026</td>\n",
       "      <td>-0.064422</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2027.0</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>None</td>\n",
       "      <td>2027</td>\n",
       "      <td>1.379508</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2028.0</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>BJ258063 Corp</td>\n",
       "      <td>2028</td>\n",
       "      <td>1.350976</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2029.0</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>None</td>\n",
       "      <td>2029</td>\n",
       "      <td>0.081202</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2030.0</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>None</td>\n",
       "      <td>2030</td>\n",
       "      <td>0.243727</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2033.0</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>EC493621 Corp</td>\n",
       "      <td>2033</td>\n",
       "      <td>1.689104</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2035.0</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>None</td>\n",
       "      <td>2035</td>\n",
       "      <td>1.108283</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2039.0</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>EH239713 Corp</td>\n",
       "      <td>2039</td>\n",
       "      <td>1.943709</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2042.0</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>None</td>\n",
       "      <td>2042</td>\n",
       "      <td>2.456139</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2044.0</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>EK081214 Corp</td>\n",
       "      <td>2044</td>\n",
       "      <td>2.962514</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2045.0</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>None</td>\n",
       "      <td>2045</td>\n",
       "      <td>2.516859</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2050.0</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>BG020010 Corp</td>\n",
       "      <td>2050</td>\n",
       "      <td>2.925397</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             DATE       ORIG_IDS  YEAR(MATURITY())  Median Yield by Year\n",
       "ID                                                                      \n",
       "2021.0 2021-04-08           None              2021             -0.393240\n",
       "2022.0 2021-04-08           None              2022              0.268339\n",
       "2023.0 2021-04-08           None              2023             -0.241071\n",
       "2024.0 2021-04-08           None              2024              0.191204\n",
       "2025.0 2021-04-08           None              2025             -0.121881\n",
       "2026.0 2021-04-08           None              2026             -0.064422\n",
       "2027.0 2021-04-08           None              2027              1.379508\n",
       "2028.0 2021-04-08  BJ258063 Corp              2028              1.350976\n",
       "2029.0 2021-04-08           None              2029              0.081202\n",
       "2030.0 2021-04-08           None              2030              0.243727\n",
       "2033.0 2021-04-08  EC493621 Corp              2033              1.689104\n",
       "2035.0 2021-04-08           None              2035              1.108283\n",
       "2039.0 2021-04-08  EH239713 Corp              2039              1.943709\n",
       "2042.0 2021-04-08           None              2042              2.456139\n",
       "2044.0 2021-04-08  EK081214 Corp              2044              2.962514\n",
       "2045.0 2021-04-08           None              2045              2.516859\n",
       "2050.0 2021-04-08  BG020010 Corp              2050              2.925397"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Find bonds\n",
    "bonds = bq.univ.bonds(['NOVN SW Equity','ROG SW Equity','GSK LN Equity'])#Retrieve median OAS Spread             \n",
    "yld = bq.data.yield_()\n",
    "mat = bq.data.maturity().year()\n",
    "yield_by_year = yld.group(mat).median()\n",
    "\n",
    "req = bql.Request(bonds, {'Median Yield by Year':yield_by_year})\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>ORIG_IDS</th>\n",
       "      <th>CLASSIFICATION_NAME()</th>\n",
       "      <th>AVG(GROUP(SPREAD(),CLASSIFICATION_NAME()))</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></th>\n",
       "      <td>NaT</td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Communications</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>None</td>\n",
       "      <td>Communications</td>\n",
       "      <td>90.029407</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Consumer Discretionary</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>None</td>\n",
       "      <td>Consumer Discretionary</td>\n",
       "      <td>75.899700</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Consumer Staples</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>None</td>\n",
       "      <td>Consumer Staples</td>\n",
       "      <td>70.340771</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Energy</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>None</td>\n",
       "      <td>Energy</td>\n",
       "      <td>117.616881</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Financials</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>None</td>\n",
       "      <td>Financials</td>\n",
       "      <td>59.197125</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Government</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>None</td>\n",
       "      <td>Government</td>\n",
       "      <td>18.817207</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Health Care</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>None</td>\n",
       "      <td>Health Care</td>\n",
       "      <td>76.583975</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Industrials</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>None</td>\n",
       "      <td>Industrials</td>\n",
       "      <td>84.100908</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Materials</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>None</td>\n",
       "      <td>Materials</td>\n",
       "      <td>102.080557</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NullGroup</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>81.388054</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Real Estate</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>None</td>\n",
       "      <td>Real Estate</td>\n",
       "      <td>106.332749</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Technology</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>None</td>\n",
       "      <td>Technology</td>\n",
       "      <td>67.580291</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Unclassifiable</th>\n",
       "      <td>NaT</td>\n",
       "      <td>None</td>\n",
       "      <td>Unclassifiable</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Utilities</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>None</td>\n",
       "      <td>Utilities</td>\n",
       "      <td>90.152842</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                             DATE ORIG_IDS   CLASSIFICATION_NAME()  \\\n",
       "ID                                                                   \n",
       "                              NaT     None                           \n",
       "Communications         2021-04-08     None          Communications   \n",
       "Consumer Discretionary 2021-04-08     None  Consumer Discretionary   \n",
       "Consumer Staples       2021-04-08     None        Consumer Staples   \n",
       "Energy                 2021-04-08     None                  Energy   \n",
       "Financials             2021-04-08     None              Financials   \n",
       "Government             2021-04-08     None              Government   \n",
       "Health Care            2021-04-08     None             Health Care   \n",
       "Industrials            2021-04-08     None             Industrials   \n",
       "Materials              2021-04-08     None               Materials   \n",
       "NullGroup              2021-04-08     None                    None   \n",
       "Real Estate            2021-04-08     None             Real Estate   \n",
       "Technology             2021-04-08     None              Technology   \n",
       "Unclassifiable                NaT     None          Unclassifiable   \n",
       "Utilities              2021-04-08     None               Utilities   \n",
       "\n",
       "                        AVG(GROUP(SPREAD(),CLASSIFICATION_NAME()))  \n",
       "ID                                                                  \n",
       "                                                               NaN  \n",
       "Communications                                           90.029407  \n",
       "Consumer Discretionary                                   75.899700  \n",
       "Consumer Staples                                         70.340771  \n",
       "Energy                                                  117.616881  \n",
       "Financials                                               59.197125  \n",
       "Government                                               18.817207  \n",
       "Health Care                                              76.583975  \n",
       "Industrials                                              84.100908  \n",
       "Materials                                               102.080557  \n",
       "NullGroup                                                81.388054  \n",
       "Real Estate                                             106.332749  \n",
       "Technology                                               67.580291  \n",
       "Unclassifiable                                                 NaN  \n",
       "Utilities                                                90.152842  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import bql\n",
    "bq = bql.Service()\n",
    "\n",
    "bql_item = bq.func.avg(bq.func.group(bq.data.spread(), bq.data.classification_name()))\n",
    "\n",
    "bql_universe = bq.univ.members('LEGATRUU Index')\n",
    "\n",
    "bql_request = bql.Request(bql_universe, bql_item)\n",
    "bql_response = bq.execute(bql_request)\n",
    "data = bql_response[0].df()\n",
    "data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1.2 Index Analysis\n",
    "\n",
    "What was the average change in yield in the last 7 days for bonds in the Bloomberg Barclays China Treasury + Policy Bank Total Return Index CNY (I32561CN Index)?"
   ]
  },
  {
   "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>ORIG_IDS</th>\n",
       "      <th>Avg Change in Yield</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>IdentityGroup</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>None</td>\n",
       "      <td>0.00137</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    DATE ORIG_IDS  Avg Change in Yield\n",
       "ID                                                    \n",
       "IdentityGroup 2021-04-08     None              0.00137"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Define index\n",
    "index = bq.univ.members('I32561CN Index')\n",
    "#Yield Change\n",
    "yld = bq.data.yield_(dates=bq.func.range('-7d','0d'), fill='prev')\n",
    "yld_chg = yld.net_chg()\n",
    "#Average change across the index\n",
    "avg_yld_chg = yld_chg.group().avg()\n",
    "\n",
    "req = bql.Request(index, {'Avg Change in Yield':avg_yld_chg})\n",
    "res = bq.execute(req)\n",
    "data = res[0].df()\n",
    "data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1.3 New Issuance Analysis\n",
    "\n",
    "What was the breakdown of active (ie not withdrawn or amended) Syndicated(ie institutional) Loans issued in EUR by Year and Month of Issue since 2018? (Hint: Use Loan Tranche Size and Launch dates)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>CURRENCY</th>\n",
       "      <th>ORIG_IDS</th>\n",
       "      <th>(YEAR(LAUNCH_DATE())*100)+MONTH(LAUNCH_DATE())</th>\n",
       "      <th>Issuance</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>201801.0</th>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>201801.0</td>\n",
       "      <td>17020.265000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201802.0</th>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>201802.0</td>\n",
       "      <td>7585.022500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201803.0</th>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>201803.0</td>\n",
       "      <td>7114.489463</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201804.0</th>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>201804.0</td>\n",
       "      <td>6161.343891</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201805.0</th>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>201805.0</td>\n",
       "      <td>12301.060000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201806.0</th>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>201806.0</td>\n",
       "      <td>9099.305519</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201807.0</th>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>201807.0</td>\n",
       "      <td>7085.800000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201808.0</th>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>201808.0</td>\n",
       "      <td>5455.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201809.0</th>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>201809.0</td>\n",
       "      <td>5287.753007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201810.0</th>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>201810.0</td>\n",
       "      <td>8661.108392</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201811.0</th>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>201811.0</td>\n",
       "      <td>11547.294384</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201812.0</th>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>201812.0</td>\n",
       "      <td>259.200000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201901.0</th>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>201901.0</td>\n",
       "      <td>2271.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201902.0</th>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>201902.0</td>\n",
       "      <td>7918.453000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201903.0</th>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>201903.0</td>\n",
       "      <td>7872.009000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201904.0</th>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>201904.0</td>\n",
       "      <td>1419.600000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201905.0</th>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>201905.0</td>\n",
       "      <td>13045.681655</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201906.0</th>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>201906.0</td>\n",
       "      <td>10170.184334</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201907.0</th>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>201907.0</td>\n",
       "      <td>10798.090000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201908.0</th>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>201908.0</td>\n",
       "      <td>335.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201909.0</th>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>201909.0</td>\n",
       "      <td>16138.027276</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201910.0</th>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>201910.0</td>\n",
       "      <td>14453.785748</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201911.0</th>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>201911.0</td>\n",
       "      <td>7579.483891</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201912.0</th>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>201912.0</td>\n",
       "      <td>730.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>202001.0</th>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>202001.0</td>\n",
       "      <td>26915.914538</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>202002.0</th>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>202002.0</td>\n",
       "      <td>13036.826670</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>202003.0</th>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>202003.0</td>\n",
       "      <td>424.700000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>202004.0</th>\n",
       "      <td>None</td>\n",
       "      <td>BL347886 Corp</td>\n",
       "      <td>202004.0</td>\n",
       "      <td>250.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>202005.0</th>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>202005.0</td>\n",
       "      <td>4716.700000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>202006.0</th>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>202006.0</td>\n",
       "      <td>6760.300000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>202007.0</th>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>202007.0</td>\n",
       "      <td>4425.313369</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>202008.0</th>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>202008.0</td>\n",
       "      <td>2293.700000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>202009.0</th>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>202009.0</td>\n",
       "      <td>4701.778094</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>202010.0</th>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>202010.0</td>\n",
       "      <td>4097.665417</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>202011.0</th>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>202011.0</td>\n",
       "      <td>4371.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>202012.0</th>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>202012.0</td>\n",
       "      <td>3707.600000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>202101.0</th>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>202101.0</td>\n",
       "      <td>16796.008752</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>202102.0</th>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>202102.0</td>\n",
       "      <td>16586.625905</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>202103.0</th>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>202103.0</td>\n",
       "      <td>21749.125000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         CURRENCY       ORIG_IDS  \\\n",
       "ID                                 \n",
       "201801.0     None           None   \n",
       "201802.0     None           None   \n",
       "201803.0     None           None   \n",
       "201804.0     None           None   \n",
       "201805.0     None           None   \n",
       "201806.0     None           None   \n",
       "201807.0     None           None   \n",
       "201808.0     None           None   \n",
       "201809.0     None           None   \n",
       "201810.0     None           None   \n",
       "201811.0     None           None   \n",
       "201812.0     None           None   \n",
       "201901.0     None           None   \n",
       "201902.0     None           None   \n",
       "201903.0     None           None   \n",
       "201904.0     None           None   \n",
       "201905.0     None           None   \n",
       "201906.0     None           None   \n",
       "201907.0     None           None   \n",
       "201908.0     None           None   \n",
       "201909.0     None           None   \n",
       "201910.0     None           None   \n",
       "201911.0     None           None   \n",
       "201912.0     None           None   \n",
       "202001.0     None           None   \n",
       "202002.0     None           None   \n",
       "202003.0     None           None   \n",
       "202004.0     None  BL347886 Corp   \n",
       "202005.0     None           None   \n",
       "202006.0     None           None   \n",
       "202007.0     None           None   \n",
       "202008.0     None           None   \n",
       "202009.0     None           None   \n",
       "202010.0     None           None   \n",
       "202011.0     None           None   \n",
       "202012.0     None           None   \n",
       "202101.0     None           None   \n",
       "202102.0     None           None   \n",
       "202103.0     None           None   \n",
       "\n",
       "          (YEAR(LAUNCH_DATE())*100)+MONTH(LAUNCH_DATE())      Issuance  \n",
       "ID                                                                      \n",
       "201801.0                                        201801.0  17020.265000  \n",
       "201802.0                                        201802.0   7585.022500  \n",
       "201803.0                                        201803.0   7114.489463  \n",
       "201804.0                                        201804.0   6161.343891  \n",
       "201805.0                                        201805.0  12301.060000  \n",
       "201806.0                                        201806.0   9099.305519  \n",
       "201807.0                                        201807.0   7085.800000  \n",
       "201808.0                                        201808.0   5455.000000  \n",
       "201809.0                                        201809.0   5287.753007  \n",
       "201810.0                                        201810.0   8661.108392  \n",
       "201811.0                                        201811.0  11547.294384  \n",
       "201812.0                                        201812.0    259.200000  \n",
       "201901.0                                        201901.0   2271.000000  \n",
       "201902.0                                        201902.0   7918.453000  \n",
       "201903.0                                        201903.0   7872.009000  \n",
       "201904.0                                        201904.0   1419.600000  \n",
       "201905.0                                        201905.0  13045.681655  \n",
       "201906.0                                        201906.0  10170.184334  \n",
       "201907.0                                        201907.0  10798.090000  \n",
       "201908.0                                        201908.0    335.500000  \n",
       "201909.0                                        201909.0  16138.027276  \n",
       "201910.0                                        201910.0  14453.785748  \n",
       "201911.0                                        201911.0   7579.483891  \n",
       "201912.0                                        201912.0    730.000000  \n",
       "202001.0                                        202001.0  26915.914538  \n",
       "202002.0                                        202002.0  13036.826670  \n",
       "202003.0                                        202003.0    424.700000  \n",
       "202004.0                                        202004.0    250.000000  \n",
       "202005.0                                        202005.0   4716.700000  \n",
       "202006.0                                        202006.0   6760.300000  \n",
       "202007.0                                        202007.0   4425.313369  \n",
       "202008.0                                        202008.0   2293.700000  \n",
       "202009.0                                        202009.0   4701.778094  \n",
       "202010.0                                        202010.0   4097.665417  \n",
       "202011.0                                        202011.0   4371.000000  \n",
       "202012.0                                        202012.0   3707.600000  \n",
       "202101.0                                        202101.0  16796.008752  \n",
       "202102.0                                        202102.0  16586.625905  \n",
       "202103.0                                        202103.0  21749.125000  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#loans universe\n",
    "loans = bq.univ.loansuniv('All')\n",
    "#Let's construct our filters\n",
    "filter_syndicated = bq.data.institutional_loan() == 'TRUE'\n",
    "filter_active1 = bq.data.ln_issue_status() != 'Withdrawn'\n",
    "filter_active2 = bq.data.ln_issue_status() != 'Cancelled'\n",
    "filter_active = bq.func.and_(filter_active1, filter_active2)\n",
    "filter_crncy = bq.data.crncy() == 'EUR'\n",
    "filter_date = bq.data.launch_date() >= '2018-01-01'\n",
    "#Combine our filters and apply\n",
    "filters = bq.func.and_(filter_date, bq.func.and_(filter_crncy,bq.func.and_(filter_syndicated, filter_active)))\n",
    "filtered_loans = bq.univ.filter(loans, filters)\n",
    "#Get loan tranche size by year and month\n",
    "loan_size = bq.data.ln_tranche_size() / 10 ** 6\n",
    "launch_date = bq.data.launch_date()\n",
    "launch_date = launch_date.year() * 100 + launch_date.month()\n",
    "loan_size_by_date = loan_size.group(launch_date).sum()\n",
    "\n",
    "req = bql.Request(filtered_loans, {'Issuance': loan_size_by_date})\n",
    "res = bq.execute(req)\n",
    "data = res[0].df()\n",
    "data\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[&uarr; Return to Top](#learn4-top)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id='learn4-topic2'></a>\n",
    "\n",
    "<span style=\"color:darkorange; font-size:2em\"> 2 Tools for Cross-Asset Analysis </span>\n",
    "\n",
    "\n",
    "\n",
    "<img src=\"../../Visualisations/BQL Connections.jpg\" style=\"width: 600px;\"/>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id='learn4-topic2.1'></a>\n",
    "\n",
    "### 2.1 Nested Filters\n",
    "\n",
    "\n",
    "\n",
    "- Multiple filters means ability to efficiently screen your universes on BQL on multiple layers\n",
    "- One application is issuer analysis (from equity to bond)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<img src=\"../../Visualisations/BQL Nested Filter.jpg\" style=\"width: 600px;\"/>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Q: Screen for the fixed coupon bonds with a maturity greater than 10 years for members of S&P500 with a high probability of default (net debt to Ebitda> 3) and find the average yield by Moody's credit rating\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Let's screen our Equities\n",
    "eq_index = bq.univ.members('SPX Index')\n",
    "filter_lev = bq.data.net_debt_to_ebitda(FPT='A') >= 3\n",
    "filter_sector = bq.data.classification_name('BICS','1') != 'Financials'\n",
    "criteria = bq.func.and_(filter_lev, filter_sector)\n",
    "#apply our screening criteria to our Equity Index\n",
    "filtered_eq_index = bq.univ.filter(eq_index, criteria)\n",
    "#Let's find the bonds for these screened issuers\n",
    "bonds = bq.univ.bonds(filtered_eq_index)\n",
    "#apply our bond criteria\n",
    "filter_cpn = bq.data.cpn_typ() == 'FIXED'\n",
    "filter_mat = bq.data.maturity() > '10Y'\n",
    "bond_criteria = bq.func.and_(filter_cpn, filter_mat)\n",
    "#let's find our ultimate list of securities based on our equity screen and our fixed income screen\n",
    "filtered_bonds = bq.univ.filter(bonds, bond_criteria)\n",
    "#For these bonds, let's get average median by Moody rating\n",
    "yld = bq.data.yield_()\n",
    "rating = bq.data.credit_rating(CREDIT_RATING_SOURCE='MOODY')\n",
    "avg_yld = yld.group(rating).median()\n",
    "\n",
    "req = bql.Request(filtered_bonds, {'Avg Yield by Rating':avg_yld})\n",
    "res = bq.execute(req)\n",
    "data = res[0].df()\n",
    "data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id='learn4-topic2.2'></a>\n",
    "\n",
    "### 2.2 Data availability management\n",
    "\n",
    "\n",
    "\n",
    "- alternatives for unavailable data avail()"
   ]
  },
  {
   "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>REVISION_DATE</th>\n",
       "      <th>PERIOD_END_DATE</th>\n",
       "      <th>AS_OF_DATE</th>\n",
       "      <th>CURRENCY</th>\n",
       "      <th>Ebitda Yield</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>ABBN SE Equity</th>\n",
       "      <td>2021-02-26</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>None</td>\n",
       "      <td>0.035386</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CFR SE Equity</th>\n",
       "      <td>2020-11-06</td>\n",
       "      <td>2020-09-30</td>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>None</td>\n",
       "      <td>0.071243</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CSGN SE Equity</th>\n",
       "      <td>2021-03-18</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GEBN SE Equity</th>\n",
       "      <td>2021-03-10</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>None</td>\n",
       "      <td>0.039429</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GIVN SE Equity</th>\n",
       "      <td>2021-01-29</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>None</td>\n",
       "      <td>0.035201</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>LHN SE Equity</th>\n",
       "      <td>2021-02-26</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>None</td>\n",
       "      <td>0.169304</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>LONN SE Equity</th>\n",
       "      <td>2021-03-25</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>None</td>\n",
       "      <td>0.031208</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NESN SE Equity</th>\n",
       "      <td>2021-03-25</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>None</td>\n",
       "      <td>0.052233</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NOVN SE Equity</th>\n",
       "      <td>2021-01-26</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>None</td>\n",
       "      <td>0.067508</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ROG SE Equity</th>\n",
       "      <td>2021-02-04</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>None</td>\n",
       "      <td>0.081858</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SCMN SE Equity</th>\n",
       "      <td>2021-02-04</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>None</td>\n",
       "      <td>0.127130</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SGSN SE Equity</th>\n",
       "      <td>2021-02-23</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>None</td>\n",
       "      <td>0.053381</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SLHN SE Equity</th>\n",
       "      <td>2021-03-02</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>None</td>\n",
       "      <td>0.122373</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SREN SE Equity</th>\n",
       "      <td>2021-03-18</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>None</td>\n",
       "      <td>-0.011605</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>UBSG SE Equity</th>\n",
       "      <td>2021-03-05</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>None</td>\n",
       "      <td>0.324995</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>UHR SE Equity</th>\n",
       "      <td>2021-03-18</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>None</td>\n",
       "      <td>0.040524</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ZURN SE Equity</th>\n",
       "      <td>2021-02-11</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>None</td>\n",
       "      <td>0.080529</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ALC SE Equity</th>\n",
       "      <td>2021-02-23</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>None</td>\n",
       "      <td>0.029791</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SIKA SE Equity</th>\n",
       "      <td>2021-02-19</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PGHN SE Equity</th>\n",
       "      <td>2021-03-16</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>None</td>\n",
       "      <td>0.026545</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               REVISION_DATE PERIOD_END_DATE AS_OF_DATE CURRENCY  Ebitda Yield\n",
       "ID                                                                            \n",
       "ABBN SE Equity    2021-02-26      2020-12-31 2021-04-08     None      0.035386\n",
       "CFR SE Equity     2020-11-06      2020-09-30 2021-04-08     None      0.071243\n",
       "CSGN SE Equity    2021-03-18      2020-12-31 2021-04-08     None           NaN\n",
       "GEBN SE Equity    2021-03-10      2020-12-31 2021-04-08     None      0.039429\n",
       "GIVN SE Equity    2021-01-29      2020-12-31 2021-04-08     None      0.035201\n",
       "LHN SE Equity     2021-02-26      2020-12-31 2021-04-08     None      0.169304\n",
       "LONN SE Equity    2021-03-25      2020-12-31 2021-04-08     None      0.031208\n",
       "NESN SE Equity    2021-03-25      2020-12-31 2021-04-08     None      0.052233\n",
       "NOVN SE Equity    2021-01-26      2020-12-31 2021-04-08     None      0.067508\n",
       "ROG SE Equity     2021-02-04      2020-12-31 2021-04-08     None      0.081858\n",
       "SCMN SE Equity    2021-02-04      2020-12-31 2021-04-08     None      0.127130\n",
       "SGSN SE Equity    2021-02-23      2020-12-31 2021-04-08     None      0.053381\n",
       "SLHN SE Equity    2021-03-02      2020-12-31 2021-04-08     None      0.122373\n",
       "SREN SE Equity    2021-03-18      2020-12-31 2021-04-08     None     -0.011605\n",
       "UBSG SE Equity    2021-03-05      2020-12-31 2021-04-08     None      0.324995\n",
       "UHR SE Equity     2021-03-18      2020-12-31 2021-04-08     None      0.040524\n",
       "ZURN SE Equity    2021-02-11      2020-12-31 2021-04-08     None      0.080529\n",
       "ALC SE Equity     2021-02-23      2020-12-31 2021-04-08     None      0.029791\n",
       "SIKA SE Equity    2021-02-19      2020-12-31 2021-04-08     None           NaN\n",
       "PGHN SE Equity    2021-03-16      2020-12-31 2021-04-08     None      0.026545"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Getting either Ebitda or Operating Income per Member of SMI\n",
    "index = bq.univ.members('SMI Index')\n",
    "ebitda = bq.data.ebitda(CURRENCY='USD')\n",
    "op_inc = bq.data.is_oper_inc(CURRENCY='USD')\n",
    "#Take Ebitda if available, otherwise Operating Income\n",
    "avail_ebitda = bq.func.avail(ebitda, op_inc)\n",
    "ev = bq.data.enterprise_value(CURRENCY='USD')\n",
    "ebitda_yield = avail_ebitda/ev\n",
    "\n",
    "req = bql.Request(index, {'Ebitda Yield':ebitda_yield})\n",
    "res = bq.execute(req)\n",
    "data = res[0].df()\n",
    "data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id='learn4-topic2.3'></a>\n",
    "\n",
    "### 2.3 Grouping II - Ungroup\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?\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<img src=\"../../Visualisations/BQL Groupxyz.jpg\" style=\"width: 600px;\"/>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id='learn4-topic2.4'></a>\n",
    "\n",
    "### 2.4 Cut\n",
    "\n",
    "\n",
    "\n",
    "- Cut() allows us to slice and dice our data in percentiles/quartiles/quintiles\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>DATE</th>\n",
       "      <th>Percentile of ASW Spread</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>AS779476 Corp</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>11.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AX916964 Corp</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>99.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AS779475 Corp</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>33.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EJ552316 Corp</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>21.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>JK203775 Corp</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>9.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EJ372541 Corp</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>16.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>QZ698193 Corp</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>14.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ZO136566 Corp</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>97.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EI109926 Corp</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AU654481 Corp</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>82.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AU654357 Corp</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>94.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ZO136474 Corp</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>90.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AM660337 Corp</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ZR549306 Corp</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>73.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>JK189339 Corp</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ZS775942 Corp</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>23.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EG212194 Corp</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>78.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AQ340827 Corp</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>63.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EG517295 Corp</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ZS775944 Corp</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>38.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AS779498 Corp</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>50.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AS779472 Corp</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>34.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AS779551 Corp</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>84.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AQ340826 Corp</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>39.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>JK203796 Corp</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>24.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AU654475 Corp</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>89.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AU692498 Corp</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>95.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AO425153 Corp</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>26.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AX519924 Corp</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AQ049903 Corp</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>55.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AQ049789 Corp</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>19.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>QZ018665 Corp</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>41.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>QZ092685 Corp</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>75.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EJ552321 Corp</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>72.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AQ049869 Corp</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>36.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AZ135175 Corp</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>80.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EC765722 Corp</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>65.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ZS775945 Corp</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>58.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AM696807 Corp</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>29.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ED242631 Corp</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>12.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AZ135179 Corp</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>85.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EK480772 Corp</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>17.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>QZ161787 Corp</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>77.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>QZ486656 Corp</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>31.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AQ353394 Corp</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>43.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>LW170803 Corp</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>28.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EK613198 Corp</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>60.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AM418639 Corp</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AO169677 Corp</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>62.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AX730274 Corp</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EI025933 Corp</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>46.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EC225480 Corp</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>67.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AN845719 Corp</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>QJ878733 Corp</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>87.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>QZ019348 Corp</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>92.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AM734452 Corp</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>48.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AN993733 Corp</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>53.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AO182411 Corp</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>51.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>QJ884807 Corp</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>45.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EC766795 Corp</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>56.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>62 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                    DATE  Percentile of ASW Spread\n",
       "ID                                                \n",
       "AS779476 Corp 2021-04-08                      11.0\n",
       "AX916964 Corp 2021-04-08                      99.0\n",
       "AS779475 Corp 2021-04-08                      33.0\n",
       "EJ552316 Corp 2021-04-08                      21.0\n",
       "JK203775 Corp 2021-04-08                       9.0\n",
       "...                  ...                       ...\n",
       "AM734452 Corp 2021-04-08                      48.0\n",
       "AN993733 Corp 2021-04-08                      53.0\n",
       "AO182411 Corp 2021-04-08                      51.0\n",
       "QJ884807 Corp 2021-04-08                      45.0\n",
       "EC766795 Corp 2021-04-08                      56.0\n",
       "\n",
       "[62 rows x 2 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Find the percentile of Vodafone bonds based on ASW Spread\n",
    "bonds = bq.univ.bonds('VOD LN Equity')\n",
    "sprd = bq.data.spread(SPREAD_TYPE='ASW')\n",
    "sprd_pctl = sprd.group().cut(100).ungroup()\n",
    "\n",
    "req = bql.Request(bonds, {'Percentile of ASW Spread':sprd_pctl})\n",
    "res = bq.execute(req)\n",
    "data = res[0].df()\n",
    "data"
   ]
  },
  {
   "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>ORIG_IDS</th>\n",
       "      <th>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>IdentityGroup</th>\n",
       "      <td>None</td>\n",
       "      <td>62</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              ORIG_IDS  Name\n",
       "ID                          \n",
       "IdentityGroup     None    62"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Analyze the top 20th percentile of bonds by ASW Spread for Vodafone bonds\n",
    "bonds = bq.univ.bonds('VOD LN Equity')\n",
    "sprd = bq.data.spread(SPREAD_TYPE='ASW')\n",
    "sprd_pctl = sprd.group().cut(100).ungroup()\n",
    "criteria = sprd_pctl >= 80.0\n",
    "filtered_bonds = bq.univ.filter(bonds, criteria)\n",
    "name = bq.data.name().group().count()\n",
    "\n",
    "req = bql.Request(bonds, {'Name':name})\n",
    "res = bq.execute(req)\n",
    "data = res[0].df()\n",
    "data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 2.4.1 Ungroup & Cut Practice\n",
    "\n",
    "Show me the bottom quintile (1/5) of members of the EM USD High Yield Index ('I31732US Index') with the lowest maturity relative to today?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Relative Maturity</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ID</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>AM715788 Corp</th>\n",
       "      <td>1.944</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AO063629 Corp</th>\n",
       "      <td>2.218</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AR725801 Corp</th>\n",
       "      <td>1.933</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AX957438 Corp</th>\n",
       "      <td>0.999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BK517726 Corp</th>\n",
       "      <td>2.281</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EJ419529 Corp</th>\n",
       "      <td>1.599</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AS239074 Corp</th>\n",
       "      <td>2.026</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EJ636932 Corp</th>\n",
       "      <td>2.045</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AZ097567 Corp</th>\n",
       "      <td>1.180</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AQ734256 Corp</th>\n",
       "      <td>1.774</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EJ047681 Corp</th>\n",
       "      <td>1.541</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EJ219407 Corp</th>\n",
       "      <td>1.158</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EJ237939 Corp</th>\n",
       "      <td>1.780</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EJ263276 Corp</th>\n",
       "      <td>1.248</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EJ289605 Corp</th>\n",
       "      <td>1.292</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EJ364850 Corp</th>\n",
       "      <td>1.448</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AO624241 Corp</th>\n",
       "      <td>1.336</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EJ416516 Corp</th>\n",
       "      <td>1.555</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EJ419920 Corp</th>\n",
       "      <td>1.563</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AZ169488 Corp</th>\n",
       "      <td>1.213</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EJ450058 Corp</th>\n",
       "      <td>1.799</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EJ455800 Corp</th>\n",
       "      <td>1.802</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EJ463361 Corp</th>\n",
       "      <td>1.656</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EJ465282 Corp</th>\n",
       "      <td>1.659</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EJ506925 Corp</th>\n",
       "      <td>1.952</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EI462911 Corp</th>\n",
       "      <td>1.487</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EJ394063 Corp</th>\n",
       "      <td>1.522</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EJ411775 Corp</th>\n",
       "      <td>1.555</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AQ609055 Corp</th>\n",
       "      <td>1.780</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ZP604776 Corp</th>\n",
       "      <td>1.541</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>JK286363 Corp</th>\n",
       "      <td>1.930</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AQ850524 Corp</th>\n",
       "      <td>1.815</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AQ852328 Corp</th>\n",
       "      <td>1.815</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AS209414 Corp</th>\n",
       "      <td>2.026</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AW940358 Corp</th>\n",
       "      <td>1.823</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EK771592 Corp</th>\n",
       "      <td>1.892</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AR210764 Corp</th>\n",
       "      <td>1.848</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ZR672547 Corp</th>\n",
       "      <td>1.462</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>QJ220693 Corp</th>\n",
       "      <td>1.539</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EJ483211 Corp</th>\n",
       "      <td>1.697</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>QJ113777 Corp</th>\n",
       "      <td>1.396</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>QJ114341 Corp</th>\n",
       "      <td>2.396</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>LW659690 Corp</th>\n",
       "      <td>2.240</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AO603678 Corp</th>\n",
       "      <td>1.331</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AO608315 Corp</th>\n",
       "      <td>1.914</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BJ918473 Corp</th>\n",
       "      <td>1.687</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AQ927926 Corp</th>\n",
       "      <td>1.815</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AR670467 Corp</th>\n",
       "      <td>1.947</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AZ094757 Corp</th>\n",
       "      <td>1.191</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AR065441 Corp</th>\n",
       "      <td>1.834</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AO080775 Corp</th>\n",
       "      <td>1.232</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AO080861 Corp</th>\n",
       "      <td>1.224</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ZS234018 Corp</th>\n",
       "      <td>1.544</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EJ713588 Corp</th>\n",
       "      <td>2.311</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EJ775396 Corp</th>\n",
       "      <td>2.324</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BG230209 Corp</th>\n",
       "      <td>1.886</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AP726912 Corp</th>\n",
       "      <td>2.059</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AN232826 Corp</th>\n",
       "      <td>1.032</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EJ352957 Corp</th>\n",
       "      <td>1.741</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EJ847418 Corp</th>\n",
       "      <td>2.464</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>163 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               Relative Maturity\n",
       "ID                              \n",
       "AM715788 Corp              1.944\n",
       "AO063629 Corp              2.218\n",
       "AR725801 Corp              1.933\n",
       "AX957438 Corp              0.999\n",
       "BK517726 Corp              2.281\n",
       "...                          ...\n",
       "BG230209 Corp              1.886\n",
       "AP726912 Corp              2.059\n",
       "AN232826 Corp              1.032\n",
       "EJ352957 Corp              1.741\n",
       "EJ847418 Corp              2.464\n",
       "\n",
       "[163 rows x 1 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Enter answer here\n",
    "index = bq.univ.members('I31732US Index')\n",
    "mat = bq.data.maturity()\n",
    "today = bq.func.today()\n",
    "relative_mat = (mat - today) / 365.25\n",
    "rel_mat_qtl = relative_mat.group().cut(5).ungroup()\n",
    "#Create the filter\n",
    "filter_rel_mat = rel_mat_qtl == 1\n",
    "filtered_index = bq.univ.filter(index, filter_rel_mat)\n",
    "\n",
    "req = bql.Request(filtered_index, {'Relative Maturity':relative_mat.round(3)})\n",
    "res = bq.execute(req)\n",
    "data = res[0].df()\n",
    "data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id='learn4-topic2.5'></a>\n",
    "\n",
    "### 2.5 Relative Valuation (Groupxyz)\n",
    "\n",
    "\n",
    "\n",
    "- Using group().func().ungroup() can be unwieldy.\n",
    "- 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": "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>DATE</th>\n",
       "      <th>Top 10 Bonds to Watch</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>EJ460599 Corp</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>19915.376408</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ZO531871 Corp</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>15759.882126</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AZ202088 Corp</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>5248.738708</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AQ023271 Corp</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>3968.414337</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AT540923 Corp</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>1268.206238</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AP093654 Corp</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>1049.025513</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AM342846 Corp</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>958.050293</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AL501984 Corp</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>847.110413</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AP841309 Corp</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>643.797943</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>JK025356 Corp</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>528.097198</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BH133354 Corp</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>359.922188</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AP288382 Corp</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>354.185944</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AQ107007 Corp</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>303.827698</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AN038329 Corp</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>294.136324</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>QZ634066 Corp</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>251.760361</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AW882434 Corp</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>236.575500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AM225189 Corp</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>206.829308</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AN111023 Corp</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>193.721893</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AU476215 Corp</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>184.784119</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ZS684879 Corp</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>165.361328</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    DATE  Top 10 Bonds to Watch\n",
       "ID                                             \n",
       "EJ460599 Corp 2021-04-08           19915.376408\n",
       "ZO531871 Corp 2021-04-08           15759.882126\n",
       "AZ202088 Corp 2021-04-08            5248.738708\n",
       "AQ023271 Corp 2021-04-08            3968.414337\n",
       "AT540923 Corp 2021-04-08            1268.206238\n",
       "AP093654 Corp 2021-04-08            1049.025513\n",
       "AM342846 Corp 2021-04-08             958.050293\n",
       "AL501984 Corp 2021-04-08             847.110413\n",
       "AP841309 Corp 2021-04-08             643.797943\n",
       "JK025356 Corp 2021-04-08             528.097198\n",
       "BH133354 Corp 2021-04-08             359.922188\n",
       "AP288382 Corp 2021-04-08             354.185944\n",
       "AQ107007 Corp 2021-04-08             303.827698\n",
       "AN038329 Corp 2021-04-08             294.136324\n",
       "QZ634066 Corp 2021-04-08             251.760361\n",
       "AW882434 Corp 2021-04-08             236.575500\n",
       "AM225189 Corp 2021-04-08             206.829308\n",
       "AN111023 Corp 2021-04-08             193.721893\n",
       "AU476215 Corp 2021-04-08             184.784119\n",
       "ZS684879 Corp 2021-04-08             165.361328"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Assess the top 10 bonds in the US Corporate High Yield Index (1900 Members) with the greatest I-Spread widening in the last month\n",
    "\n",
    "index = bq.univ.members('LF98TRUU Index')\n",
    "#Define spread change\n",
    "sprd = bq.data.spread(SPREAD_TYPE='I', dates=bq.func.range('-1m','0d'), fill='prev')\n",
    "sprd_chg = sprd.net_chg()\n",
    "sprd_chg_rank = sprd_chg.grouprank(order='desc')\n",
    "#Filter by rank\n",
    "criteria = sprd_chg_rank <= 20\n",
    "filtered_index = bq.univ.filter(index, criteria)\n",
    "\n",
    "#Get results\n",
    "req = bql.Request(filtered_index, {'Top 10 Bonds to Watch':sprd_chg.groupsort()})\n",
    "res = bq.execute(req)\n",
    "data = res[0].df()\n",
    "data"
   ]
  },
  {
   "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>CPN_TYP</th>\n",
       "      <th>MULTIPLIER</th>\n",
       "      <th>ORIG_IDS</th>\n",
       "      <th>YEAR(MATURITY())</th>\n",
       "      <th>Avg Coupon by Year of Maturity</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>2021.0</th>\n",
       "      <td>FIXED</td>\n",
       "      <td>1.0</td>\n",
       "      <td>ZS107285 Corp</td>\n",
       "      <td>2021</td>\n",
       "      <td>12.850000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022.0</th>\n",
       "      <td>FIXED</td>\n",
       "      <td>1.0</td>\n",
       "      <td>None</td>\n",
       "      <td>2022</td>\n",
       "      <td>6.783333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023.0</th>\n",
       "      <td>FIXED</td>\n",
       "      <td>1.0</td>\n",
       "      <td>ZQ435931 Corp</td>\n",
       "      <td>2023</td>\n",
       "      <td>5.750000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024.0</th>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "      <td>None</td>\n",
       "      <td>2024</td>\n",
       "      <td>4.605069</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2025.0</th>\n",
       "      <td>FIXED</td>\n",
       "      <td>1.0</td>\n",
       "      <td>AZ744309 Corp</td>\n",
       "      <td>2025</td>\n",
       "      <td>5.550000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2026.0</th>\n",
       "      <td>FIXED</td>\n",
       "      <td>1.0</td>\n",
       "      <td>AZ744286 Corp</td>\n",
       "      <td>2026</td>\n",
       "      <td>5.950000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2028.0</th>\n",
       "      <td>FIXED</td>\n",
       "      <td>1.0</td>\n",
       "      <td>ZQ911288 Corp</td>\n",
       "      <td>2028</td>\n",
       "      <td>4.750000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2029.0</th>\n",
       "      <td>FIXED</td>\n",
       "      <td>1.0</td>\n",
       "      <td>None</td>\n",
       "      <td>2029</td>\n",
       "      <td>4.019714</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2030.0</th>\n",
       "      <td>FIXED</td>\n",
       "      <td>1.0</td>\n",
       "      <td>ZQ259068 Corp</td>\n",
       "      <td>2030</td>\n",
       "      <td>3.932500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2034.0</th>\n",
       "      <td>FIXED</td>\n",
       "      <td>1.0</td>\n",
       "      <td>None</td>\n",
       "      <td>2034</td>\n",
       "      <td>3.900000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2035.0</th>\n",
       "      <td>FIXED</td>\n",
       "      <td>1.0</td>\n",
       "      <td>None</td>\n",
       "      <td>2035</td>\n",
       "      <td>2.436667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2038.0</th>\n",
       "      <td>FIXED</td>\n",
       "      <td>1.0</td>\n",
       "      <td>None</td>\n",
       "      <td>2038</td>\n",
       "      <td>2.627500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2039.0</th>\n",
       "      <td>FIXED</td>\n",
       "      <td>1.0</td>\n",
       "      <td>ZR955537 Corp</td>\n",
       "      <td>2039</td>\n",
       "      <td>4.625000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2049.0</th>\n",
       "      <td>FIXED</td>\n",
       "      <td>1.0</td>\n",
       "      <td>None</td>\n",
       "      <td>2049</td>\n",
       "      <td>4.220833</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2050.0</th>\n",
       "      <td>FIXED</td>\n",
       "      <td>1.0</td>\n",
       "      <td>None</td>\n",
       "      <td>2050</td>\n",
       "      <td>3.187500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2079.0</th>\n",
       "      <td>VARIABLE</td>\n",
       "      <td>1.0</td>\n",
       "      <td>ZR938329 Corp</td>\n",
       "      <td>2079</td>\n",
       "      <td>6.350000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         CPN_TYP  MULTIPLIER       ORIG_IDS  YEAR(MATURITY())  \\\n",
       "ID                                                              \n",
       "2021.0     FIXED         1.0  ZS107285 Corp              2021   \n",
       "2022.0     FIXED         1.0           None              2022   \n",
       "2023.0     FIXED         1.0  ZQ435931 Corp              2023   \n",
       "2024.0      None         1.0           None              2024   \n",
       "2025.0     FIXED         1.0  AZ744309 Corp              2025   \n",
       "2026.0     FIXED         1.0  AZ744286 Corp              2026   \n",
       "2028.0     FIXED         1.0  ZQ911288 Corp              2028   \n",
       "2029.0     FIXED         1.0           None              2029   \n",
       "2030.0     FIXED         1.0  ZQ259068 Corp              2030   \n",
       "2034.0     FIXED         1.0           None              2034   \n",
       "2035.0     FIXED         1.0           None              2035   \n",
       "2038.0     FIXED         1.0           None              2038   \n",
       "2039.0     FIXED         1.0  ZR955537 Corp              2039   \n",
       "2049.0     FIXED         1.0           None              2049   \n",
       "2050.0     FIXED         1.0           None              2050   \n",
       "2079.0  VARIABLE         1.0  ZR938329 Corp              2079   \n",
       "\n",
       "        Avg Coupon by Year of Maturity  \n",
       "ID                                      \n",
       "2021.0                       12.850000  \n",
       "2022.0                        6.783333  \n",
       "2023.0                        5.750000  \n",
       "2024.0                        4.605069  \n",
       "2025.0                        5.550000  \n",
       "2026.0                        5.950000  \n",
       "2028.0                        4.750000  \n",
       "2029.0                        4.019714  \n",
       "2030.0                        3.932500  \n",
       "2034.0                        3.900000  \n",
       "2035.0                        2.436667  \n",
       "2038.0                        2.627500  \n",
       "2039.0                        4.625000  \n",
       "2049.0                        4.220833  \n",
       "2050.0                        3.187500  \n",
       "2079.0                        6.350000  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Show me the average coupon by year of maturity of USD green bonds issued in 2019 with a higher yield than the median of their BCLASS sector peers (BCLASS, 1)\n",
    "\n",
    "active_bonds = bq.univ.bondsuniv('Active')\n",
    "#Filter Criteria\n",
    "criteria_green = bq.data.green_bond_loan_indicator() == 'True'\n",
    "criteria_date = bq.data.issue_dt().year() == '2019'\n",
    "criteria_crncy = bq.data.crncy() == 'USD'\n",
    "#Combine our filter criteria and apply\n",
    "green_criteria = bq.func.and_(criteria_crncy, bq.func.and_(criteria_green, criteria_date))\n",
    "filtered_green_bonds = bq.univ.filter(active_bonds, green_criteria)\n",
    "#Yield higher than sector peers\n",
    "yld = bq.data.yield_()\n",
    "sector = bq.data.classification_name('BCLASS','1')\n",
    "criteria_yld = yld > yld.groupmedian(sector)\n",
    "#Filter the green bonds\n",
    "green_and_yield = bq.univ.filter(filtered_green_bonds, criteria_yld)\n",
    "#Average Coupon by Maturity Year\n",
    "coupon = bq.data.cpn()\n",
    "mat = bq.data.maturity()\n",
    "mat_year = mat.year()\n",
    "avg_coupon_by_year = coupon.group(mat_year).avg()\n",
    "\n",
    "req = bql.Request(green_and_yield, {'Avg Coupon by Year of Maturity':avg_coupon_by_year})\n",
    "res = bq.execute(req)\n",
    "data = res[0].df()\n",
    "data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 2.5.1 Groupxyz Practice: Falling Angels\n",
    "\n",
    "Which were the top 20 bonds, whose spread widened the most (in %) in the past 2 weeks relative to the average of their sector peers whose spread widened, as defined by bonds in the same BCLASS Sector Level 3 in the Bloomberg Barclays APAC AAA Total Return Unhedged JPY (LP06TREU Index)?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Enter answer here\n",
    "index = bq.univ.members('LP06TREU Index')\n",
    "spread_chg = bq.data.spread(SPREAD_TYPE='OAS', dates=bq.func.range('-2w','0d'), fill='prev').pct_chg()\n",
    "sector = bq.data.classification_name('BCLASS','3')\n",
    "sector_chg = spread_chg.groupavg(sector)\n",
    "relative_chg = spread_chg/sector_chg\n",
    "#Create filters and apply\n",
    "filter_relative = relative_chg > 0\n",
    "filter_widen = spread_chg > 0\n",
    "filters_movement = bq.func.and_(filter_relative, filter_widen)\n",
    "filtered_bonds = bq.univ.filter(index, filters_movement)\n",
    "#get the top 20\n",
    "filter_relative_rank = relative_chg.grouprank() <= 20\n",
    "top_20_bonds = bq.univ.filter(filtered_bonds, filter_relative_rank)\n",
    "securityid = bq.data.id()\n",
    "\n",
    "req = bql.Request(top_20_bonds, securityid)\n",
    "res = bq.execute(req)\n",
    "data = res[0].df()\n",
    "data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id='learn4-topic2.6'></a>\n",
    "\n",
    "### 2.6 Identifying outliers \n",
    "\n",
    "\n",
    "\n",
    "- Z-score calculations on Bloomberg Server \n",
    "- identify, locate and manage"
   ]
  },
  {
   "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>Z-Spread Outliers</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>EK016520 Corp</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>1.119082</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BH786060 Corp</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>1.029236</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BK877830 Corp</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>-0.404529</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EJ462516 Corp</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>-0.825608</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EJ370495 Corp</th>\n",
       "      <td>2021-04-08</td>\n",
       "      <td>-0.918181</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    DATE  Z-Spread Outliers\n",
       "ID                                         \n",
       "EK016520 Corp 2021-04-08           1.119082\n",
       "BH786060 Corp 2021-04-08           1.029236\n",
       "BK877830 Corp 2021-04-08          -0.404529\n",
       "EJ462516 Corp 2021-04-08          -0.825608\n",
       "EJ370495 Corp 2021-04-08          -0.918181"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Find the top 5 outliers for the percentage spread change in bonds in the energy sector of an index in the past week\n",
    "#Let's get our universe of Energy bonds\n",
    "index = bq.univ.members('LBUSTRUU Index')\n",
    "sector = bq.data.classification_name('BCLASS','3')\n",
    "filter_sector = sector == 'Energy'\n",
    "filtered_index = bq.univ.filter(index, filter_sector)\n",
    "#Spread Change Z-Score\n",
    "spread_chg = bq.data.spread(dates=bq.func.range('-1w','0d'), fill='prev').pct_chg()\n",
    "spread_zscore = spread_chg.groupzscore()\n",
    "spread_zscore_abs = spread_zscore.abs()\n",
    "spread_zscore_abs = spread_zscore_abs.grouprank()\n",
    "#Put in place our second filter\n",
    "filter_zscore = spread_zscore_abs <= 5\n",
    "filtered_index2 = bq.univ.filter(filtered_index, filter_zscore)\n",
    "\n",
    "req = bql.Request(filtered_index2, {'Z-Spread Outliers':spread_zscore.groupsort()})\n",
    "res = bq.execute(req)\n",
    "data = res[0].df()\n",
    "data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 2.6.1 Groupxyz Practice: Screening out Outliers\n",
    "\n",
    "What was my average OAS spread move for financial bonds in the US Corporate (LBUSTRUU Index) for the last 120 days if we remove the top 10% of outliers in our dataset? (Hint: For Sector - use BICS Level 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Enter answer here\n",
    "index = bq.univ.members('LBUSTRUU Index')\n",
    "sprd_chg = bq.data.spread(SPREAD_TYPE='OAS', dates=bq.func.range('-120d','0d'), fill='prev').pct_chg()\n",
    "sector = bq.data.classification_name('BICS','1')\n",
    "filter_financials = sector == 'Financials'\n",
    "filtered_financial_bonds = bq.univ.filter(index, filter_financials)\n",
    "#getting the top 10% outliers\n",
    "sprd_chg_zscore = sprd_chg.groupzscore().abs()\n",
    "zscore_pctl = sprd_chg_zscore.group().cut(10).ungroup()\n",
    "filter_pct = zscore_pctl <= 9\n",
    "filtered_bonds = bq.univ.filter(filtered_financial_bonds, filter_pct)\n",
    "#Retrieve the avg spread move\n",
    "sprd_chg_avg = sprd_chg.group().avg()\n",
    "\n",
    "req = bql.Request(filtered_bonds, {'Average Spread Change':sprd_chg_avg})\n",
    "res = bq.execute(req)\n",
    "data = res[0].df()\n",
    "data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id='learn4-topic2.7'></a>\n",
    "\n",
    "### 2.7 Issuer\n",
    "\n",
    "\n",
    "\n",
    "- From bond level, we can navigate aggregate and manipulate data on the issuing entity\n",
    "- issuerof()\n",
    "- parent(type= )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<img src=\"../../Visualisations/Issuer and Parent.jpg\" style=\"width: 600px;\"/>"
   ]
  },
  {
   "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>ORIG_IDS</th>\n",
       "      <th>Total Securities</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>IdentityGroup</th>\n",
       "      <td>None</td>\n",
       "      <td>347</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              ORIG_IDS  Total Securities\n",
       "ID                                      \n",
       "IdentityGroup     None               347"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Count the number of issuers in Bloomberg Barclays Pan Europe HY Index (LP01TREU Index)\n",
    "index = bq.univ.members('LP01TREU Index')\n",
    "issuers = bq.univ.parent(index, type='Ultimate')\n",
    "#count names\n",
    "name = bq.data.name()\n",
    "name_count = name.group().count()\n",
    "\n",
    "req = bql.Request(issuers, {'Total Securities':name_count})\n",
    "res = bq.execute(req)\n",
    "data = res[0].df()\n",
    "data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 2.7.1 Issuer Practice: Probability of default\n",
    "- What is the average 1yr probability of default for the ultimate parents of bonds in the Bloomberg Barclays Pan Europe HY Index ('LP01TREU Index')?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Please enter answer here\n",
    "index = bq.univ.members('LP01TREU Index')\n",
    "ult_parents = bq.univ.parent(index, type='Ultimate')\n",
    "prob_default = bq.data.probability_of_default(time_horizon='3Y')\n",
    "avg_pd = prob_default.group().avg()\n",
    "\n",
    "req = bql.Request(ult_parents, {'Avg Probability of Default':avg_pd})\n",
    "res = bq.execute(req)\n",
    "data = res[0].df().head()\n",
    "data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id='learn4-topic2.8'></a>\n",
    "\n",
    "### 2.8 Value\n",
    "\n",
    "\n",
    "\n",
    "- introduces data from outside the universe\n",
    "- can specify a relationship if it exists e.g. mapby=lineage()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<img src=\"../../Visualisations/BQL Value.jpg\" style=\"width: 600px;\"/>"
   ]
  },
  {
   "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>ORIG_IDS:0</th>\n",
       "      <th>Issuer 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>AM2044296 Corp</th>\n",
       "      <td>0533805D LN Equity</td>\n",
       "      <td>Virgin Media Secured Finance P</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        ORIG_IDS:0                     Issuer Name\n",
       "ID                                                                \n",
       "AM2044296 Corp  0533805D LN Equity  Virgin Media Secured Finance P"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Getting Issuer Name for a bond\n",
    "bond = 'AM2044296 Corp'\n",
    "#Data 1\n",
    "name = bq.data.name()\n",
    "#Data 2\n",
    "issuer_name = name.value(bq.univ.issuerof(), mapby='lineage')\n",
    "\n",
    "req = bql.Request(bond, {'Issuer Name': issuer_name})\n",
    "res = bq.execute(req)\n",
    "data = res[0].df()\n",
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Yield (Bond Data)</th>\n",
       "      <th>Name (Equity Data)</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>AM2044296 Corp</th>\n",
       "      <td>2.556445</td>\n",
       "      <td>Virgin Media Secured Finance P</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                Yield (Bond Data)              Name (Equity Data)\n",
       "ID                                                               \n",
       "AM2044296 Corp           2.556445  Virgin Media Secured Finance P"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Getting Yield and Issuer Name for a bond\n",
    "yld = bq.data.yield_()\n",
    "name = bq.data.name()\n",
    "issuer_name = name.value(bq.univ.issuerof(), mapby='lineage')\n",
    "bond = 'AM2044296 Corp'\n",
    "#Put our fields in an OrderedDict()\n",
    "flds = OrderedDict()\n",
    "flds['Yield (Bond Data)'] = yld\n",
    "flds['Name (Equity Data)'] = issuer_name\n",
    "\n",
    "req = bql.Request(bond, flds)\n",
    "res = bq.execute(req)\n",
    "tbl = pd.DataFrame({r.name:r.df()[r.name] for r in res})\n",
    "tbl.head(20)"
   ]
  },
  {
   "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>ORIG_IDS:0</th>\n",
       "      <th>ORIG_IDS:1</th>\n",
       "      <th>VALUE(NAME(),ISSUEROF(),MAPBY=MAPBY.LINEAGE)</th>\n",
       "      <th>Green Bonds by Issuer</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>United States International De</th>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "      <td>United States International De</td>\n",
       "      <td>22.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Credit Agricole Corporate &amp; In</th>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "      <td>Credit Agricole Corporate &amp; In</td>\n",
       "      <td>12.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>International Finance Corp</th>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "      <td>International Finance Corp</td>\n",
       "      <td>9.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>International Bank for Reconst</th>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "      <td>International Bank for Reconst</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Modern Land China Co Ltd</th>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "      <td>Modern Land China Co Ltd</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Vasakronan AB</th>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "      <td>Vasakronan AB</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Credit Suisse AG/New York NY</th>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "      <td>Credit Suisse AG/New York NY</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Industrial &amp; Commercial Bank o</th>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "      <td>Industrial &amp; Commercial Bank o</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AC Energy Finance Internationa</th>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "      <td>AC Energy Finance Internationa</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Hong Kong Government Internati</th>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "      <td>Hong Kong Government Internati</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Northern States Power Co/MN</th>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "      <td>Northern States Power Co/MN</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Zhenro Properties Group Ltd</th>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "      <td>Zhenro Properties Group Ltd</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>China Construction Bank Corp/H</th>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "      <td>China Construction Bank Corp/H</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Citigroup Global Markets Holdi</th>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "      <td>Citigroup Global Markets Holdi</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>DTE Electric Co</th>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "      <td>DTE Electric Co</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>European Bank for Reconstructi</th>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "      <td>European Bank for Reconstructi</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Greenko Solar Mauritius Ltd</th>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "      <td>Greenko Solar Mauritius Ltd</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>HAT Holdings I LLC / HAT Holdi</th>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "      <td>HAT Holdings I LLC / HAT Holdi</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MidAmerican Energy Co</th>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "      <td>MidAmerican Energy Co</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AES Corp/The</th>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "      <td>AES Corp/The</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ASE Technology Holding Co Ltd</th>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "      <td>ASE Technology Holding Co Ltd</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Alexandria Real Estate Equitie</th>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "      <td>Alexandria Real Estate Equitie</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Aozora Bank Ltd</th>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "      <td>Aozora Bank Ltd</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ardagh Metal Packaging Finance</th>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "      <td>Ardagh Metal Packaging Finance</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Asian Development Bank</th>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "      <td>Asian Development Bank</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Avangrid Inc</th>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "      <td>Avangrid Inc</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BNG Bank NV</th>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "      <td>BNG Bank NV</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Banco BTG Pactual SA/Cayman Is</th>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "      <td>Banco BTG Pactual SA/Cayman Is</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Banco de Credito e Inversiones</th>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "      <td>Banco de Credito e Inversiones</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Boston Properties LP</th>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "      <td>Boston Properties LP</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                               ORIG_IDS:0 ORIG_IDS:1  \\\n",
       "ID                                                     \n",
       "United States International De       None              \n",
       "Credit Agricole Corporate & In       None              \n",
       "International Finance Corp           None              \n",
       "International Bank for Reconst       None              \n",
       "Modern Land China Co Ltd             None              \n",
       "Vasakronan AB                        None              \n",
       "Credit Suisse AG/New York NY         None              \n",
       "Industrial & Commercial Bank o       None              \n",
       "AC Energy Finance Internationa       None              \n",
       "Hong Kong Government Internati       None              \n",
       "Northern States Power Co/MN          None              \n",
       "Zhenro Properties Group Ltd          None              \n",
       "China Construction Bank Corp/H       None              \n",
       "Citigroup Global Markets Holdi       None              \n",
       "DTE Electric Co                      None              \n",
       "European Bank for Reconstructi       None              \n",
       "Greenko Solar Mauritius Ltd          None              \n",
       "HAT Holdings I LLC / HAT Holdi       None              \n",
       "MidAmerican Energy Co                None              \n",
       "AES Corp/The                         None              \n",
       "ASE Technology Holding Co Ltd        None              \n",
       "Alexandria Real Estate Equitie       None              \n",
       "Aozora Bank Ltd                      None              \n",
       "Ardagh Metal Packaging Finance       None              \n",
       "Asian Development Bank               None              \n",
       "Avangrid Inc                         None              \n",
       "BNG Bank NV                          None              \n",
       "Banco BTG Pactual SA/Cayman Is       None              \n",
       "Banco de Credito e Inversiones       None              \n",
       "Boston Properties LP                 None              \n",
       "\n",
       "                               VALUE(NAME(),ISSUEROF(),MAPBY=MAPBY.LINEAGE)  \\\n",
       "ID                                                                            \n",
       "United States International De               United States International De   \n",
       "Credit Agricole Corporate & In               Credit Agricole Corporate & In   \n",
       "International Finance Corp                       International Finance Corp   \n",
       "International Bank for Reconst               International Bank for Reconst   \n",
       "Modern Land China Co Ltd                           Modern Land China Co Ltd   \n",
       "Vasakronan AB                                                 Vasakronan AB   \n",
       "Credit Suisse AG/New York NY                   Credit Suisse AG/New York NY   \n",
       "Industrial & Commercial Bank o               Industrial & Commercial Bank o   \n",
       "AC Energy Finance Internationa               AC Energy Finance Internationa   \n",
       "Hong Kong Government Internati               Hong Kong Government Internati   \n",
       "Northern States Power Co/MN                     Northern States Power Co/MN   \n",
       "Zhenro Properties Group Ltd                     Zhenro Properties Group Ltd   \n",
       "China Construction Bank Corp/H               China Construction Bank Corp/H   \n",
       "Citigroup Global Markets Holdi               Citigroup Global Markets Holdi   \n",
       "DTE Electric Co                                             DTE Electric Co   \n",
       "European Bank for Reconstructi               European Bank for Reconstructi   \n",
       "Greenko Solar Mauritius Ltd                     Greenko Solar Mauritius Ltd   \n",
       "HAT Holdings I LLC / HAT Holdi               HAT Holdings I LLC / HAT Holdi   \n",
       "MidAmerican Energy Co                                 MidAmerican Energy Co   \n",
       "AES Corp/The                                                   AES Corp/The   \n",
       "ASE Technology Holding Co Ltd                 ASE Technology Holding Co Ltd   \n",
       "Alexandria Real Estate Equitie               Alexandria Real Estate Equitie   \n",
       "Aozora Bank Ltd                                             Aozora Bank Ltd   \n",
       "Ardagh Metal Packaging Finance               Ardagh Metal Packaging Finance   \n",
       "Asian Development Bank                               Asian Development Bank   \n",
       "Avangrid Inc                                                   Avangrid Inc   \n",
       "BNG Bank NV                                                     BNG Bank NV   \n",
       "Banco BTG Pactual SA/Cayman Is               Banco BTG Pactual SA/Cayman Is   \n",
       "Banco de Credito e Inversiones               Banco de Credito e Inversiones   \n",
       "Boston Properties LP                                   Boston Properties LP   \n",
       "\n",
       "                                Green Bonds by Issuer  \n",
       "ID                                                     \n",
       "United States International De                   22.0  \n",
       "Credit Agricole Corporate & In                   12.0  \n",
       "International Finance Corp                        9.0  \n",
       "International Bank for Reconst                    6.0  \n",
       "Modern Land China Co Ltd                          6.0  \n",
       "Vasakronan AB                                     6.0  \n",
       "Credit Suisse AG/New York NY                      5.0  \n",
       "Industrial & Commercial Bank o                    5.0  \n",
       "AC Energy Finance Internationa                    4.0  \n",
       "Hong Kong Government Internati                    4.0  \n",
       "Northern States Power Co/MN                       4.0  \n",
       "Zhenro Properties Group Ltd                       4.0  \n",
       "China Construction Bank Corp/H                    3.0  \n",
       "Citigroup Global Markets Holdi                    3.0  \n",
       "DTE Electric Co                                   3.0  \n",
       "European Bank for Reconstructi                    3.0  \n",
       "Greenko Solar Mauritius Ltd                       3.0  \n",
       "HAT Holdings I LLC / HAT Holdi                    3.0  \n",
       "MidAmerican Energy Co                             3.0  \n",
       "AES Corp/The                                      2.0  \n",
       "ASE Technology Holding Co Ltd                     2.0  \n",
       "Alexandria Real Estate Equitie                    2.0  \n",
       "Aozora Bank Ltd                                   2.0  \n",
       "Ardagh Metal Packaging Finance                    2.0  \n",
       "Asian Development Bank                            2.0  \n",
       "Avangrid Inc                                      2.0  \n",
       "BNG Bank NV                                       2.0  \n",
       "Banco BTG Pactual SA/Cayman Is                    2.0  \n",
       "Banco de Credito e Inversiones                    2.0  \n",
       "Boston Properties LP                              2.0  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Count the number of USD green bonds issued by issuer since 2019 \n",
    "all_bonds = bq.univ.bondsuniv('All')\n",
    "#Set out criteria\n",
    "criteria_issue_dt = bq.data.issue_dt() >= '2019-01-01' \n",
    "criteria_green = bq.data.green_bond_loan_indicator() == 'True'\n",
    "criteria_crncy = bq.data.crncy() == 'USD'\n",
    "#Combine our filter criteria\n",
    "green_criteria = bq.func.and_(criteria_crncy, bq.func.and_(criteria_green, criteria_issue_dt))\n",
    "green_filter = bq.univ.filter(all_bonds, green_criteria)\n",
    "#count the bonds in our filtered universe by issuer\n",
    "security = bq.data.id()\n",
    "name = bq.data.name()\n",
    "issuer_name = name.value(bq.univ.issuerof(), mapby='lineage')\n",
    "count_by_issuer = security.group(issuer_name).count()\n",
    "\n",
    "req = bql.Request(green_filter, {'Green Bonds by Issuer':count_by_issuer.groupsort()})\n",
    "res = bq.execute(req)\n",
    "data = res[0].df().head(30)\n",
    "data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 2.8.1 Value Practice: Comparable Bonds\n",
    "- Using AW8986837 Corp, what are the top 5 most similar bonds using a custom factor?\n",
    "- Please use the bonds for AMZN US, MSFT US and ORCL US as starting universe\n",
    "- Custom factor: 0.40 Yield to Worst, 0.60 spread"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ORIG_IDS</th>\n",
       "      <th>ID()</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>BJ808897 Corp</th>\n",
       "      <td>AMZN US Equity</td>\n",
       "      <td>BJ808897 Corp</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AQ133599 Corp</th>\n",
       "      <td>AMZN US Equity</td>\n",
       "      <td>AQ133599 Corp</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>QZ095291 Corp</th>\n",
       "      <td>MSFT US Equity</td>\n",
       "      <td>QZ095291 Corp</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AM332115 Corp</th>\n",
       "      <td>MSFT US Equity</td>\n",
       "      <td>AM332115 Corp</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AP914138 Corp</th>\n",
       "      <td>ORCL US Equity</td>\n",
       "      <td>AP914138 Corp</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     ORIG_IDS           ID()\n",
       "ID                                          \n",
       "BJ808897 Corp  AMZN US Equity  BJ808897 Corp\n",
       "AQ133599 Corp  AMZN US Equity  AQ133599 Corp\n",
       "QZ095291 Corp  MSFT US Equity  QZ095291 Corp\n",
       "AM332115 Corp  MSFT US Equity  AM332115 Corp\n",
       "AP914138 Corp  ORCL US Equity  AP914138 Corp"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Please enter answer here\n",
    "bond = 'AW8986837 Corp'\n",
    "y = bq.data.yield_()\n",
    "sprd = bq.data.spread()\n",
    "factor = y * 0.40 + sprd * 0.60\n",
    "bond_factor = factor.value(bq.univ.list(bond))\n",
    "#find my peer bonds\n",
    "peer_bonds = bq.univ.bonds(['AMZN US Equity','MSFT US Equity','ORCL US Equity'])\n",
    "#take the difference between factor for the bonds\n",
    "peer_factors = factor - bond_factor\n",
    "peer_factors_abs = peer_factors.abs()\n",
    "#Let's rank them from lowest to highest\n",
    "factor_rank = peer_factors_abs.grouprank(order='asc')\n",
    "filter_rank = factor_rank <= 5\n",
    "filtered_bonds = bq.univ.filter(peer_bonds, filter_rank)\n",
    "#retrieve the securities\n",
    "securityid = bq.data.id()\n",
    "\n",
    "req = bql.Request(filtered_bonds, securityid)\n",
    "res = bq.execute(req)\n",
    "data = res[0].df()\n",
    "data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[&uarr; Return to Top](#learn4-top)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id='learn4-topic3'></a>\n",
    "\n",
    "<span style=\"color:green; font-size:2em\"> 3. BQL Advanced Summary </span>\n",
    "\n",
    "\n",
    "\n",
    "<img src=\"../../Visualisations/BQL Services.jpg\" style=\"width: 600px;\"/>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- This is the beginning: Use BQL to dig deep and conduct in-depth cross-asset financial data analysis\n",
    "- Regular Updates: 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=\"1.3. FI Advanced - Workbook.ipynb\">&larr; Back to the FI Advanced Workbook</a>&emsp;&emsp;\n",
    "    <a href=\"#learn4-top\">&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
}