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": "markdown",
   "metadata": {},
   "source": [
    "#### NuGet Packages"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "\r\n",
       "<div>\r\n",
       "    <div id='dotnet-interactive-this-cell-11288.Microsoft.DotNet.Interactive.Http.HttpPort' style='display: none'>\r\n",
       "        The below script needs to be able to find the current output cell; this is an easy method to get it.\r\n",
       "    </div>\r\n",
       "    <script type='text/javascript'>\r\n",
       "async function probeAddresses(probingAddresses) {\r\n",
       "    function timeout(ms, promise) {\r\n",
       "        return new Promise(function (resolve, reject) {\r\n",
       "            setTimeout(function () {\r\n",
       "                reject(new Error('timeout'))\r\n",
       "            }, ms)\r\n",
       "            promise.then(resolve, reject)\r\n",
       "        })\r\n",
       "    }\r\n",
       "\r\n",
       "    if (Array.isArray(probingAddresses)) {\r\n",
       "        for (let i = 0; i < probingAddresses.length; i++) {\r\n",
       "\r\n",
       "            let rootUrl = probingAddresses[i];\r\n",
       "\r\n",
       "            if (!rootUrl.endsWith('/')) {\r\n",
       "                rootUrl = `${rootUrl}/`;\r\n",
       "            }\r\n",
       "\r\n",
       "            try {\r\n",
       "                let response = await timeout(1000, fetch(`${rootUrl}discovery`, {\r\n",
       "                    method: 'POST',\r\n",
       "                    cache: 'no-cache',\r\n",
       "                    mode: 'cors',\r\n",
       "                    timeout: 1000,\r\n",
       "                    headers: {\r\n",
       "                        'Content-Type': 'text/plain'\r\n",
       "                    },\r\n",
       "                    body: probingAddresses[i]\r\n",
       "                }));\r\n",
       "\r\n",
       "                if (response.status == 200) {\r\n",
       "                    return rootUrl;\r\n",
       "                }\r\n",
       "            }\r\n",
       "            catch (e) { }\r\n",
       "        }\r\n",
       "    }\r\n",
       "}\r\n",
       "\r\n",
       "function loadDotnetInteractiveApi() {\r\n",
       "    probeAddresses([\"http://192.168.0.6:2048/\", \"http://127.0.0.1:2048/\"])\r\n",
       "        .then((root) => {\r\n",
       "        // use probing to find host url and api resources\r\n",
       "        // load interactive helpers and language services\r\n",
       "        let dotnetInteractiveRequire = require.config({\r\n",
       "        context: '11288.Microsoft.DotNet.Interactive.Http.HttpPort',\r\n",
       "                paths:\r\n",
       "            {\r\n",
       "                'dotnet-interactive': `${root}resources`\r\n",
       "                }\r\n",
       "        }) || require;\r\n",
       "\r\n",
       "            window.dotnetInteractiveRequire = dotnetInteractiveRequire;\r\n",
       "\r\n",
       "            window.configureRequireFromExtension = function(extensionName, extensionCacheBuster) {\r\n",
       "                let paths = {};\r\n",
       "                paths[extensionName] = `${root}extensions/${extensionName}/resources/`;\r\n",
       "                \r\n",
       "                let internalRequire = require.config({\r\n",
       "                    context: extensionCacheBuster,\r\n",
       "                    paths: paths,\r\n",
       "                    urlArgs: `cacheBuster=${extensionCacheBuster}`\r\n",
       "                    }) || require;\r\n",
       "\r\n",
       "                return internalRequire\r\n",
       "            };\r\n",
       "        \r\n",
       "            dotnetInteractiveRequire([\r\n",
       "                    'dotnet-interactive/dotnet-interactive'\r\n",
       "                ],\r\n",
       "                function (dotnet) {\r\n",
       "                    dotnet.init(window);\r\n",
       "                },\r\n",
       "                function (error) {\r\n",
       "                    console.log(error);\r\n",
       "                }\r\n",
       "            );\r\n",
       "        })\r\n",
       "        .catch(error => {console.log(error);});\r\n",
       "    }\r\n",
       "\r\n",
       "// ensure `require` is available globally\r\n",
       "if ((typeof(require) !==  typeof(Function)) || (typeof(require.config) !== typeof(Function))) {\r\n",
       "    let require_script = document.createElement('script');\r\n",
       "    require_script.setAttribute('src', 'https://cdnjs.cloudflare.com/ajax/libs/require.js/2.3.6/require.min.js');\r\n",
       "    require_script.setAttribute('type', 'text/javascript');\r\n",
       "    \r\n",
       "    \r\n",
       "    require_script.onload = function() {\r\n",
       "        loadDotnetInteractiveApi();\r\n",
       "    };\r\n",
       "\r\n",
       "    document.getElementsByTagName('head')[0].appendChild(require_script);\r\n",
       "}\r\n",
       "else {\r\n",
       "    loadDotnetInteractiveApi();\r\n",
       "}\r\n",
       "\r\n",
       "    </script>\r\n",
       "</div>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div><div></div><div></div><div><strong>Installed Packages</strong><ul><li><span>Microsoft.Data.Analysis, 0.20.1</span></li><li><span>Refinitiv.Data.Content, 1.0.0-beta4</span></li></ul></div></div>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "Loading extensions from `C:\\Users\\U8007876\\.nuget\\packages\\microsoft.data.analysis\\0.20.1\\interactive-extensions\\dotnet\\Microsoft.Data.Analysis.Interactive.dll`"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "#r \"nuget:Refinitiv.Data.Content, 1.0.0-beta4\"\n",
    "#r \"nuget:Microsoft.Data.Analysis\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "using Newtonsoft.Json.Linq;\n",
    "using Refinitiv.Data.Content.SearchService;\n",
    "using Refinitiv.Data.Core;\n",
    "using Microsoft.Data.Analysis;\n",
    "using Microsoft.AspNetCore.Html;\n",
    "using System.Data;\n",
    "using System;\n",
    "using Microsoft.DotNet.Interactive.Formatting;\n",
    "using static Microsoft.DotNet.Interactive.Formatting.PocketViewTags;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Table Output\n",
    "Helper routine to output data in a table format."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "Formatter.Register<DataTable>((dt, writer) =>\n",
    "{\n",
    "    if (dt.Columns.Count > 0)\n",
    "    {\n",
    "        var rows = new List<ICollection<IHtmlContent>>();\n",
    "\n",
    "        // Process Each data row\n",
    "        foreach(var row in dt.Rows.Cast<DataRow>())\n",
    "        {\n",
    "            var cells = new List<IHtmlContent>();\n",
    "            foreach (var cell in row.ItemArray)\n",
    "            {\n",
    "                switch (cell)\n",
    "                {\n",
    "                    case null:\n",
    "                        break;\n",
    "                    case JValue val:\n",
    "                        cells.Add(td(val.ToString()));\n",
    "                        break;\n",
    "                    case IEnumerable<JToken> list:\n",
    "                        cells.Add(td($\"[{string.Join(\", \", list.Select(node => $\"{node}\"))}]\"));\n",
    "                        break;\n",
    "                    default:\n",
    "                        cells.Add(td(cell));\n",
    "                        break;\n",
    "                }                \n",
    "            }\n",
    "            rows.Add(cells);\n",
    "        }\n",
    "        \n",
    "        // Header\n",
    "        var headers = new List<IHtmlContent>();\n",
    "        foreach (DataColumn col in dt.Columns)\n",
    "            headers.Add(th(col.ColumnName) as IHtmlContent);\n",
    "\n",
    "        var t = table(thead(headers), tbody(rows.Select(r => tr(r))));\n",
    "        writer.Write(t);\n",
    "    }\n",
    "}, \"text/html\");"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2023-02-21 3:58:42 PM:DesktopSession. (State: DesktopSession is Pending)\n",
      "2023-02-21 3:58:44 PM:DesktopSession. (Event: {\n",
      "  \"Contents\": \"Desktop Session Successfully Authenticated\"\n",
      "})\n",
      "2023-02-21 3:58:44 PM:DesktopSession. (State: DesktopSession is Opened)\n"
     ]
    }
   ],
   "source": [
    "// Create a session into the desktop\n",
    "DesktopSession.Definition().GetSession().OnState((s, state, msg) => Console.WriteLine($\"{DateTime.Now}:{msg}. (State: {state})\"))\n",
    "                                        .OnEvent((s, eventCode, msg) => Console.WriteLine($\"{DateTime.Now}:{msg}. (Event: {eventCode})\"))\n",
    "                                        .Open();"
   ]
  },
  {
   "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": [
       "<table><thead><th>DTSubjectName</th><th>ExchangeName</th><th>RIC</th><th>RCSAssetCategoryLeaf</th><th>AssetState</th><th>ExpiryDate</th><th>UnderlyingQuoteRIC</th></thead><tbody><tr><td>Johannesburg Stock Exchange Vodacom Group SSF Equity Future Nov 2019</td><td>Johannesburg Stock Exchange</td><td>VODQX9^1</td><td>Equity Future</td><td>DC</td><td>2019-11-21 12:00:00 AM</td><td>[VODJ.J]</td></tr><tr><td>Johannesburg Stock Exchange Vodacom Group Limited Cash Settled SSF Equity Future Nov 2019</td><td>Johannesburg Stock Exchange</td><td>VODSX9^1</td><td>Equity Future</td><td>DC</td><td>2019-11-21 12:00:00 AM</td><td>[VODJ.J]</td></tr></tbody></table>"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "var filter = \"TickerSymbol eq 'vod' and ExpiryDate lt 2019-12-01 and ExpiryDate ge 2019-11-01\";\n",
    "var select = \"DTSubjectName, ExchangeName, RIC, RCSAssetCategoryLeaf, AssetState, ExpiryDate, UnderlyingQuoteRIC\";\n",
    "\n",
    "var response = Search.Definition(Search.View.DerivativeQuotes).Filter(filter)\n",
    "                                                              .Select(select)\n",
    "                                                              .GetData();\n",
    "response.Data.Table"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Example - Expired Crude Oil Futures in the last year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "// Build expiry date range expression\n",
    "var dateOneYearAgo = DateTime.Now.AddYears(-1);\n",
    "var dateRange = $\"ExpiryDate ge {dateOneYearAgo:yyyy-MM-dd} and ExpiryDate lt {DateTime.Now:yyyy-MM-dd}\";"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table><thead><th>DTSubjectName</th><th>RIC</th><th>ExpiryDate</th><th>RCSUnderlyingProductLeaf</th></thead><tbody><tr><td>NYMEX WTI Light Sweet Crude Oil Calendar Swap US Quarterly  Mar 2023</td><td>CLCALQH3</td><td>2022-12-31 12:00:00 AM</td><td>Crude Oil</td></tr><tr><td>ICE Brent Crude Electronic Energy Fair Value Future Nov 2022</td><td>LCOFVX2</td><td>2022-09-30 12:00:00 AM</td><td>Brent Crude</td></tr><tr><td>Shanghai International Energy Exchange Medium Sour Crude Oil Fair Value Future Oct 2022</td><td>ISCFVV2</td><td>2022-09-23 12:00:00 AM</td><td>Crude Oil</td></tr><tr><td>NYMEX WTI Light Sweet Crude Oil Calendar Swap US Monthly  Aug 2022</td><td>CLCALMQ2</td><td>2022-07-31 12:00:00 AM</td><td>Crude Oil</td></tr><tr><td>ICE WTI Crude Electronic Energy Fair Value Future May 2022</td><td>WTCLFVK2</td><td>2022-04-19 12:00:00 AM</td><td>US Light Sweet Crude</td></tr></tbody></table>"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "filter = $@\"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 {dateRange}\";\n",
    "\n",
    "response = Search.Definition(Search.View.SearchAll).Filter(filter)\n",
    "                                                   .OrderBy(\"ExpiryDate desc\")\n",
    "                                                   .Top(30)\n",
    "                                                   .Select(\"DTSubjectName, RIC, ExpiryDate, RCSUnderlyingProductLeaf\")\n",
    "                                                   .GetData();\n",
    "response.Data.Table"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Example - Expired Electricity Futures in the last year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table><thead><th>DTSubjectName</th><th>RIC</th><th>ExpiryDate</th><th>RCSUnderlyingProductLeaf</th></thead><tbody><tr><td>Power Nordic Tallinn Fair Value Continuation 45</td><td>TRTALBFVDc45</td><td>2023-02-20 12:00:00 AM</td><td>Power</td></tr><tr><td>TR TAL FV W7</td><td>TRTALBFVWc7</td><td>2023-02-19 12:00:00 AM</td><td>Power</td></tr><tr><td>Power Nordic Tallinn Fair Value Continuation 44</td><td>TRTALBFVDc44</td><td>2023-02-19 12:00:00 AM</td><td>Power</td></tr><tr><td>Power Nordic Tallinn Fair Value Continuation 43</td><td>TRTALBFVDc43</td><td>2023-02-18 12:00:00 AM</td><td>Power</td></tr><tr><td>Power Nordic Tallinn Fair Value Weekly Continuation 1</td><td>TRTALBFVWc1</td><td>2023-01-08 12:00:00 AM</td><td>Power</td></tr><tr><td>Power Nordic  Tallinn Fair Value Weekly Continuation 0</td><td>TRTALBFVWc0</td><td>2023-01-08 12:00:00 AM</td><td>Power</td></tr><tr><td>ICE-US FCOJ-A Futures Electronic Commodity Fair Value Future Nov 2022</td><td>OJFVX2</td><td>2022-11-09 12:00:00 AM</td><td>Power</td></tr></tbody></table>"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "filter = $@\"SearchAllCategoryv3 eq 'Commodities' and\n",
    "            RCSUnderlyingProductName eq 'Power' and\n",
    "            ExchangeName xeq 'Source is a Refinitiv Contributor' and {dateRange}\";\n",
    "\n",
    "response = Search.Definition(Search.View.SearchAll).Filter(filter)\n",
    "                                                   .OrderBy(\"ExpiryDate desc\")\n",
    "                                                   .Top(30)\n",
    "                                                   .Select(\"DTSubjectName, RIC, ExpiryDate, RCSUnderlyingProductLeaf\")\n",
    "                                                   .GetData();\n",
    "response.Data.Table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": ".NET (C#)",
   "language": "C#",
   "name": ".net-csharp"
  },
  "language_info": {
   "file_extension": ".cs",
   "mimetype": "text/x-csharp",
   "name": "C#",
   "pygments_lexer": "csharp",
   "version": "11.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}