Commodity seasonality charts with Eikon Data API

Leonid Sopotnitskiy
Director, Eikon Platform, Emerging & Frontier Markets Director, Eikon Platform, Emerging & Frontier Markets


The cmdty_seasonality.ipynb is a python model that uses Eikon Data API for seasonality charting of commodity futures. This file is designed to be imported as a module into other jupyter notebooks using the ipynb python library and used by calling the main calculation function:

cmdty_seasonality.get_data(ric, lookback_years, month, rebase)

The input descriptions are given below:

Parameter Description Possible argument
ric A futures contract RIC. NOTE: The function does not support continuous futures RICs. string, e.g. LGOK0
lookback_years The number of expired contracts that the method will loop up. Default value = 5. int
month The month of the year, which will be plotted on the chart. Default value = current month. int
rebase Performs rebasing of the timeseries to 0. Default value = False bool

The function returns a plotly chart as well as a pandas dataframe with the historical data.


The model uses Eikon Data API as a data source, and will be performing date manipulations, therefore we will reference the following libraries: 


import eikon as ek

import numpy as np

import calendar

import cufflinks as cf

import pandas as pd

from pandas.tseries.offsets import BMonthEnd

from datetime import datetime




app_key = 'YOUR_APP_KEY'


This script contains several auxilary functions, which will be covered in the next section, as we walk through main method get_data().


Let's look at the step-by-step code execution for getting the seasonality data on a crude oil futures contract. Let's define our input parameters as follows:


ric = 'LCOK0'

lookback_years = 10

month = 3

rebase = True


df = get_data(ric, lookback_years, month, rebase)

The RIC LCOK0 stands for a Brent Crude Futures [LCO] + delivery month [K] (May) + delivery year [0] (2020) - we will be using this structure to create the identifiers for the expired contracts from previous years. Since lookback_years is 10 and month is 3, we will be comparing the timeseries for the May futures contracts within the 3rd month (i.e. March) during the past 10 years. And we will be looking at rebasing the chart to capture the percent change for every period - hence we set rebase to True. Now let's start the calculation by plugging these into get_data().

When the function is called, it will construct a list of RICs for futures contracts (including the current one) and retrieve the timeseries for the given month in previous years:


def get_data(ric, lookback_years = 5, month = current_month(), rebase = False):

    if 'c' in ric:

        return 'Please enter a futures delivery month contract code (e.g. LCOK0).'


    frames = gen_contracts(ric, lookback_years, month)

    hist = get_timeseries_seq(frames)

    .  .  .

The first auxilary function gen_contracts() creates a list of lists, that stores the futures RICs, start and end dates for the timeseries:


def gen_contracts(r, d, m):

    xd, err = ek.get_data(r, 'EXPIR_DATE')

    xd = xd['EXPIR_DATE'][0]

    xd = xd.split('-')

    y = xd[0]

    _m = int(xd[1]) if m is None else m

    _y = int(y)

    _x = []

    x = []

    root = contract_data(r)

    while d >= 0:

        if d == 0:

            _x = ('{}{}'.format(root, str(_y - d)[-1:]))

            _ed = eom(_y-d, _m)


            dec = str(_y - d)[-2:][0]

            _x = '{}{}^{}'.format(root, str(_y - d)[-1], dec)

            _ed = eom(_y-d, _m)

        _sd = som(_y-d, _m)

        x.append([_x, _sd, _ed])

        d -= 1

    return x

The way this function works is that we first get the expiration date of the input futures contract from the field EXPIR_DATE with Eikon Data API. For example, LCOK0 will expire on 2020-03-31. This string value is then parsed to form a list with the year, month and date of expiration. We then combine the root of the contract ticker (i.e. LCOK) with a suffix, that indicated the year and decade of expiration. The RIC structure for expired future contracts follows this logic:

[futures ticker] + [month of delivery code] + [year of delivery] + [ ^ ] + [decade of delivery],

where decade of delivery is given as a single digit: 2020s = 2, 2010s = 1, 2000s = 0, 1990s = 9, etc. In this example, the returned list of lists looks as follows:

In [2]:

gen_contracts('LCOK0', 10, 3)

Out [2]:


[['LCOK0^1', '2010-03-01', '2010-03-31'],

 ['LCOK1^1', '2011-03-01', '2011-03-31'],

 ['LCOK2^1', '2012-03-01', '2012-03-30'],

 ['LCOK3^1', '2013-03-01', '2013-03-29'],

 ['LCOK4^1', '2014-03-01', '2014-03-31'],

 ['LCOK5^1', '2015-03-01', '2015-03-31'],

 ['LCOK6^1', '2016-03-01', '2016-03-31'],

 ['LCOK7^1', '2017-03-01', '2017-03-31'],

 ['LCOK8^1', '2018-03-01', '2018-03-30'],

 ['LCOK9^1', '2019-03-01', '2019-03-29'],

 ['LCOK0', '2020-03-01', '2020-03-31']]

The root of the contact ticker is parsed from the input RIC, by the auxilary function contract_data():


def contract_data(r):

    k = list(r)

    root = ''.join(k[:-1])

    return root

As we can see, it simply trims the last digit denoting the year of delivery for the futures RIC. In our example, the root will be LCOK.

The start and end date of the month are calculated using the auxilary functions som() and eom():


def last_workday(d):

    last = BMonthEnd()

    return last.rollforward(d)


def eom(y, m):

    ed = last_workday(datetime(year = int(y), month=m, day=1)).strftime('%Y-%m-%d')

    return ed


def som(y, m):

    sd = datetime(year = int(y), month=m, day=1).strftime('%Y-%m-%d')

    return sd

The eom() function uses the BMonthEnd method to get the last business day of the given month to avoid getting data for the unnecessary weekends.

Now that the list of contract RICs and dates has been created, we move on to retrieving the historical prices for these instruments with get_timeseries_seq(). This function cycles through the list of lists displayed earlier and uses the get_timeseries() method of Eikon Data API to retrieve the history and store result in a dictionary with a structure like: {RIC : time_series}.


def get_timeseries_seq(l):

    d = {}

    for i in l:

        d[i[0]] = ek.get_timeseries(i[0], 'CLOSE', start_date=i[1], end_date=i[2], interval='daily')

    return d

At this point we have all the data we need, and the remining portion of get_data() focuses on aligning the dates so that we have a direct comparison between the contracts ignoring the year values:


    .  .  .

    for k in hist.keys():

        _idx = []

        for i in range(len(hist[k])):


        hist[k]['Dates'] = _idx


    seasonal_df = pd.DataFrame(columns = ['Dates'])

    for k in hist.keys():

        seasonal_df = pd.merge(seasonal_df, hist[k], on='Dates', how = 'outer')

    .  .  .

The first loop re-formats each dataframe index by dropping the year, whereas the second loop merges all dataframes by mapping the historical values to the same index intries. The last snippet backfills any data gaps in the aggregated dataframe and adds the necessary labels to the columns, so that the each series would be named accordingly on the plotly chart:


    .  .  .

    seasonal_df = seasonal_df.sort_values(by='Dates')

    seasonal_df = seasonal_df.fillna(method='backfill')

    seasonal_df = seasonal_df.reset_index(drop = True)

    seasonal_df.index = seasonal_df['Dates']

    seasonal_df = seasonal_df.drop(columns='Dates')

    seasonal_df.columns = list(hist.keys())

    title = 'Seasonality chart'

    ytitle = 'Price'

    if rebase:

        seasonal_df = pd.DataFrame((np.log(seasonal_df)-np.log(seasonal_df.iloc[0])))*100

        title = 'Rebased seasonality chart'

        ytitle = 'Change, %'

    seasonal_df.iplot(title=title, yTitle = ytitle)

    return seasonal_df

Since we have chosen to rebase the values and capture the percent change of each series, the script gets the log returns in the last if-statement, alternatively it would be ignored. The output is stored in a pandas dataframe, and we build a plotly chart using cufflinks, which will look similar to the ne below (NOTE: the sharp decline in LCOK0 vs previous years caused by the failed OPEC agreement to reduce oil production in March 2020):