Search - Expired Assets.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Expired Futures\n",
    "When searching for an expired asset, you will need to specify the date range based on the ExpiryDate property."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<refinitiv.dataplatform.core.session.desktop_session.DesktopSession at 0x196f8fd0>"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import refinitiv.dataplatform as rdp\n",
    "import pandas as pd\n",
    "import datetime as dt\n",
    "import dateutil.relativedelta\n",
    "\n",
    "rdp.open_desktop_session('Your API Key here')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'1.0.0a7.post1'"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.set_option('display.max_colwidth', 140)\n",
    "rdp.__version__"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Example - Expired Vodafone futures in the month of Nov 2019."
   ]
  },
  {
   "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>RIC</th>\n",
       "      <th>UnderlyingQuoteRIC</th>\n",
       "      <th>ExchangeName</th>\n",
       "      <th>RCSAssetCategoryLeaf</th>\n",
       "      <th>DTSubjectName</th>\n",
       "      <th>ExpiryDate</th>\n",
       "      <th>AssetState</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>VODQX9^1</td>\n",
       "      <td>[VODJ.J]</td>\n",
       "      <td>Johannesburg Stock Exchange</td>\n",
       "      <td>Equity Future</td>\n",
       "      <td>Johannesburg Stock Exchange Vodacom Group SSF Equity Future Nov 2019</td>\n",
       "      <td>2019-11-21T00:00:00.000Z</td>\n",
       "      <td>DC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>VODSX9^1</td>\n",
       "      <td>[VODJ.J]</td>\n",
       "      <td>Johannesburg Stock Exchange</td>\n",
       "      <td>Equity Future</td>\n",
       "      <td>Johannesburg Stock Exchange Vodacom Group Limited Cash Settled SSF Equity Future Nov 2019</td>\n",
       "      <td>2019-11-21T00:00:00.000Z</td>\n",
       "      <td>DC</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        RIC UnderlyingQuoteRIC                 ExchangeName  \\\n",
       "0  VODQX9^1           [VODJ.J]  Johannesburg Stock Exchange   \n",
       "1  VODSX9^1           [VODJ.J]  Johannesburg Stock Exchange   \n",
       "\n",
       "  RCSAssetCategoryLeaf  \\\n",
       "0        Equity Future   \n",
       "1        Equity Future   \n",
       "\n",
       "                                                                               DTSubjectName  \\\n",
       "0                       Johannesburg Stock Exchange Vodacom Group SSF Equity Future Nov 2019   \n",
       "1  Johannesburg Stock Exchange Vodacom Group Limited Cash Settled SSF Equity Future Nov 2019   \n",
       "\n",
       "                 ExpiryDate AssetState  \n",
       "0  2019-11-21T00:00:00.000Z         DC  \n",
       "1  2019-11-21T00:00:00.000Z         DC  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rdp.search(\n",
    "    view = rdp.SearchViews.DerivativeQuotes,\n",
    "    filter = \"TickerSymbol eq 'vod' and ExpiryDate lt 2019-12-01 and ExpiryDate ge 2019-11-01\",\n",
    "    select = \"DTSubjectName, ExchangeName, RIC, RCSAssetCategoryLeaf, AssetState, ExpiryDate, UnderlyingQuoteRIC\"\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Example - Expired Crude Oil Futures in the last year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'ExpiryDate ge 2019-12-14 and ExpiryDate lt 2020-12-13'"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Build expiry date range expression\n",
    "date_1_year_ago = dt.datetime.today() - dateutil.relativedelta.relativedelta(days=365)\n",
    "date_range = f'ExpiryDate ge {date_1_year_ago.strftime(\"%Y-%m-%d\")} and ExpiryDate lt {dt.datetime.today().strftime(\"%Y-%m-%d\")}'\n",
    "date_range"
   ]
  },
  {
   "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>ExpiryDate</th>\n",
       "      <th>RCSUnderlyingProductLeaf</th>\n",
       "      <th>RIC</th>\n",
       "      <th>DTSubjectName</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-11-30T00:00:00.000Z</td>\n",
       "      <td>Crude Oil</td>\n",
       "      <td>CLCALMZ0</td>\n",
       "      <td>NYMEX WTI Light Sweet Crude Oil Calendar Swap US Monthly  Dec 2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-10-31T00:00:00.000Z</td>\n",
       "      <td>Crude Oil</td>\n",
       "      <td>CLCALMX0</td>\n",
       "      <td>NYMEX WTI Light Sweet Crude Oil Calendar Swap US Monthly  Nov 2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-09-30T00:00:00.000Z</td>\n",
       "      <td>Crude Oil</td>\n",
       "      <td>CLCALMV0</td>\n",
       "      <td>NYMEX WTI Light Sweet Crude Oil Calendar Swap US Monthly  Oct 2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020-09-30T00:00:00.000Z</td>\n",
       "      <td>Crude Oil</td>\n",
       "      <td>CLCALQZ0</td>\n",
       "      <td>NYMEX WTI Light Sweet Crude Oil Calendar Swap US Quarterly  Dec 2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020-08-31T00:00:00.000Z</td>\n",
       "      <td>Crude Oil</td>\n",
       "      <td>CLCALMU0</td>\n",
       "      <td>NYMEX WTI Light Sweet Crude Oil Calendar Swap US Monthly  Sep 2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2020-07-31T00:00:00.000Z</td>\n",
       "      <td>Crude Oil</td>\n",
       "      <td>CLCALMQ0</td>\n",
       "      <td>NYMEX WTI Light Sweet Crude Oil Calendar Swap US Monthly  Aug 2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2020-06-30T00:00:00.000Z</td>\n",
       "      <td>Crude Oil</td>\n",
       "      <td>CLCALMN0</td>\n",
       "      <td>NYMEX WTI Light Sweet Crude Oil Calendar Swap US Monthly  Jul 2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2020-06-30T00:00:00.000Z</td>\n",
       "      <td>Crude Oil</td>\n",
       "      <td>CLCALQU0</td>\n",
       "      <td>NYMEX WTI Light Sweet Crude Oil Calendar Swap US Quarterly  Sep 2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2020-05-31T00:00:00.000Z</td>\n",
       "      <td>Crude Oil</td>\n",
       "      <td>CLCALMM0</td>\n",
       "      <td>NYMEX WTI Light Sweet Crude Oil Calendar Swap US Monthly  Jun 2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2020-04-30T00:00:00.000Z</td>\n",
       "      <td>Crude Oil</td>\n",
       "      <td>CLCALMK0</td>\n",
       "      <td>NYMEX WTI Light Sweet Crude Oil Calendar Swap US Monthly  May 2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2020-03-31T00:00:00.000Z</td>\n",
       "      <td>Crude Oil</td>\n",
       "      <td>CLCALMJ0</td>\n",
       "      <td>NYMEX WTI Light Sweet Crude Oil Calendar Swap US Monthly  Apr 2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2020-03-31T00:00:00.000Z</td>\n",
       "      <td>Crude Oil</td>\n",
       "      <td>CLCALQM0</td>\n",
       "      <td>NYMEX WTI Light Sweet Crude Oil Calendar Swap US Quarterly  Jun 2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>2020-02-29T00:00:00.000Z</td>\n",
       "      <td>Crude Oil</td>\n",
       "      <td>CLCALMH0</td>\n",
       "      <td>NYMEX WTI Light Sweet Crude Oil Calendar Swap US Monthly  Mar 2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>2020-01-31T00:00:00.000Z</td>\n",
       "      <td>Crude Oil</td>\n",
       "      <td>CLCALMG0</td>\n",
       "      <td>NYMEX WTI Light Sweet Crude Oil Calendar Swap US Monthly  Feb 2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>2019-12-31T00:00:00.000Z</td>\n",
       "      <td>Crude Oil</td>\n",
       "      <td>CLCALYZ0</td>\n",
       "      <td>NYMEX WTI Crude Oil Calendar Swap US Yearly Dec 2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>2019-12-31T00:00:00.000Z</td>\n",
       "      <td>Crude Oil</td>\n",
       "      <td>CLCALMF0</td>\n",
       "      <td>NYMEX WTI Light Sweet Crude Oil Calendar Swap US Monthly  Jan 2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>2019-12-31T00:00:00.000Z</td>\n",
       "      <td>Crude Oil</td>\n",
       "      <td>CLCALQH0</td>\n",
       "      <td>NYMEX WTI Light Sweet Crude Oil Calendar Swap US Quarterly  Mar 2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>2019-12-19T00:00:00.000Z</td>\n",
       "      <td>Crude Oil</td>\n",
       "      <td>WTICRDSDQH0</td>\n",
       "      <td>WTI Future Assessments Spread Quarterly Mar 2020</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  ExpiryDate RCSUnderlyingProductLeaf          RIC  \\\n",
       "0   2020-11-30T00:00:00.000Z                Crude Oil     CLCALMZ0   \n",
       "1   2020-10-31T00:00:00.000Z                Crude Oil     CLCALMX0   \n",
       "2   2020-09-30T00:00:00.000Z                Crude Oil     CLCALMV0   \n",
       "3   2020-09-30T00:00:00.000Z                Crude Oil     CLCALQZ0   \n",
       "4   2020-08-31T00:00:00.000Z                Crude Oil     CLCALMU0   \n",
       "5   2020-07-31T00:00:00.000Z                Crude Oil     CLCALMQ0   \n",
       "6   2020-06-30T00:00:00.000Z                Crude Oil     CLCALMN0   \n",
       "7   2020-06-30T00:00:00.000Z                Crude Oil     CLCALQU0   \n",
       "8   2020-05-31T00:00:00.000Z                Crude Oil     CLCALMM0   \n",
       "9   2020-04-30T00:00:00.000Z                Crude Oil     CLCALMK0   \n",
       "10  2020-03-31T00:00:00.000Z                Crude Oil     CLCALMJ0   \n",
       "11  2020-03-31T00:00:00.000Z                Crude Oil     CLCALQM0   \n",
       "12  2020-02-29T00:00:00.000Z                Crude Oil     CLCALMH0   \n",
       "13  2020-01-31T00:00:00.000Z                Crude Oil     CLCALMG0   \n",
       "14  2019-12-31T00:00:00.000Z                Crude Oil     CLCALYZ0   \n",
       "15  2019-12-31T00:00:00.000Z                Crude Oil     CLCALMF0   \n",
       "16  2019-12-31T00:00:00.000Z                Crude Oil     CLCALQH0   \n",
       "17  2019-12-19T00:00:00.000Z                Crude Oil  WTICRDSDQH0   \n",
       "\n",
       "                                                           DTSubjectName  \n",
       "0     NYMEX WTI Light Sweet Crude Oil Calendar Swap US Monthly  Dec 2020  \n",
       "1     NYMEX WTI Light Sweet Crude Oil Calendar Swap US Monthly  Nov 2020  \n",
       "2     NYMEX WTI Light Sweet Crude Oil Calendar Swap US Monthly  Oct 2020  \n",
       "3   NYMEX WTI Light Sweet Crude Oil Calendar Swap US Quarterly  Dec 2020  \n",
       "4     NYMEX WTI Light Sweet Crude Oil Calendar Swap US Monthly  Sep 2020  \n",
       "5     NYMEX WTI Light Sweet Crude Oil Calendar Swap US Monthly  Aug 2020  \n",
       "6     NYMEX WTI Light Sweet Crude Oil Calendar Swap US Monthly  Jul 2020  \n",
       "7   NYMEX WTI Light Sweet Crude Oil Calendar Swap US Quarterly  Sep 2020  \n",
       "8     NYMEX WTI Light Sweet Crude Oil Calendar Swap US Monthly  Jun 2020  \n",
       "9     NYMEX WTI Light Sweet Crude Oil Calendar Swap US Monthly  May 2020  \n",
       "10    NYMEX WTI Light Sweet Crude Oil Calendar Swap US Monthly  Apr 2020  \n",
       "11  NYMEX WTI Light Sweet Crude Oil Calendar Swap US Quarterly  Jun 2020  \n",
       "12    NYMEX WTI Light Sweet Crude Oil Calendar Swap US Monthly  Mar 2020  \n",
       "13    NYMEX WTI Light Sweet Crude Oil Calendar Swap US Monthly  Feb 2020  \n",
       "14                  NYMEX WTI Crude Oil Calendar Swap US Yearly Dec 2020  \n",
       "15    NYMEX WTI Light Sweet Crude Oil Calendar Swap US Monthly  Jan 2020  \n",
       "16  NYMEX WTI Light Sweet Crude Oil Calendar Swap US Quarterly  Mar 2020  \n",
       "17                      WTI Future Assessments Spread Quarterly Mar 2020  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rdp.search(\n",
    "    filter = f\"RCSUnderlyingProductName xeq 'Energy' and \\\n",
    "               SearchAllCategoryv3 eq 'Commodities' and \\\n",
    "               RCSAssetCategoryName eq 'Derivative\\Future' and RCSUnderlyingProductName eq 'Crude Oil' and \\\n",
    "               ExchangeName xeq 'Source is a Refinitiv Contributor' and {date_range}\",\n",
    "    order_by = \"ExpiryDate desc\",\n",
    "    top = 30,\n",
    "    select = \"DTSubjectName, RIC, ExpiryDate, RCSUnderlyingProductLeaf\",\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Example - Expired Electricity Futures in the last year"
   ]
  },
  {
   "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>ExpiryDate</th>\n",
       "      <th>RCSUnderlyingProductLeaf</th>\n",
       "      <th>RIC</th>\n",
       "      <th>DTSubjectName</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-12-12T00:00:00.000Z</td>\n",
       "      <td>Baseload</td>\n",
       "      <td>TRFRBFVDc2</td>\n",
       "      <td>TRC France Baseload Power Fair Value Day Ahead 2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-01-31T00:00:00.000Z</td>\n",
       "      <td>Baseload</td>\n",
       "      <td>TRDEATBFVBMF0</td>\n",
       "      <td>Germany vs Austria Baseload Power Fair Value Spread Balance of Month (BOM) Jan 2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2019-12-27T00:00:00.000Z</td>\n",
       "      <td>Peakload</td>\n",
       "      <td>TRGBPMF0</td>\n",
       "      <td>TRPC Electricity Great Britain Peakload Monthly Jan 2020</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 ExpiryDate RCSUnderlyingProductLeaf            RIC  \\\n",
       "0  2020-12-12T00:00:00.000Z                 Baseload     TRFRBFVDc2   \n",
       "1  2020-01-31T00:00:00.000Z                 Baseload  TRDEATBFVBMF0   \n",
       "2  2019-12-27T00:00:00.000Z                 Peakload       TRGBPMF0   \n",
       "\n",
       "                                                                         DTSubjectName  \n",
       "0                                     TRC France Baseload Power Fair Value Day Ahead 2  \n",
       "1  Germany vs Austria Baseload Power Fair Value Spread Balance of Month (BOM) Jan 2020  \n",
       "2                             TRPC Electricity Great Britain Peakload Monthly Jan 2020  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rdp.search(\n",
    "    filter = f\"SearchAllCategoryv3 eq 'Commodities' and \\\n",
    "               RCSUnderlyingProductName eq 'Power' and \\\n",
    "               ExchangeName xeq 'Source is a Refinitiv Contributor' and {date_range}\",\n",
    "    order_by = \"ExpiryDate desc\",\n",
    "    top = 30,\n",
    "    select = \"DTSubjectName, RIC, ExpiryDate, RCSUnderlyingProductLeaf\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}