Pull futures chain (expired and current) & get all historical prices

Shadeun

Hi,

I would like to pull a futures chain, including expired contracts (say 0#C) and current contracts. And get the prices. Is there a simple way to do this that pulls them in a single dataframe once you have the list of contracts?

jonathan.legrand

Hi @Shadeun,

The Search API is what you're after. How many are you expecting to retrieve in one call? are we talking 1k or 100 million rows?

raksina.samasiri

Hi @Shadeun ,

Is this what you're looking for?

df, err = ek.get_data('0#C:',['TRDPRC_1','OPINT_1','EXPIR_DATE'])
df

where

• TRDPRC_1 is the Last trade price or value
• OPINT_1 is an Open interest
• EXPIR_DATE is the date on which the future, option or warrant expires

To find the field name to be used, Data Item Browser (DIB) in Refinitiv Workspace/Eikon Desktop can be used, here's the video regarding DIB tutorial.

Hope this helps and please let me know in case there is any further question

Shadeun

Hi,

Would be a lot, perhaps 1k rows per contract and 100 contracts historically, so 100k 'cells'. But not an unrealistically large number of datapoints i think.

So you are suggesting i use the search function to find the contracts and then pull via ek.get_data?

jonathan.legrand

Yes, I'd suggest that, although you don't have to use ek.get_data, you can keep with rd, which also has a get data function. you can find more details here (as well as many examples in CodeBook).

the search limits itself to 10k rows; for more, I'd advise bulking your search (e.g.: by maturity). There is a way to do just this as shown here and explained in the 'Building Search into your Application Workflow' article.

When it comes to expired instruments, I'd suggest reading this great article too.

Shadeun

Thanks i was hoping something that merged Pull All Expired Contracts - Forum | Refinitiv Developer Community and an active one.

I cannot seem to get the code in the link to work however. i assume rdp is always

import refinitiv.dataplatform as rdp

yeah?

jonathan.legrand

Hi @Shadeun

No, I'm afraid that this is the wrong library, its predecessor. The new one's details can be found here, including the library's install:

pip install refinitiv-dataplatform

import refinitiv.data as rd

Shadeun

Thanks so I can get the codes via (say):

response=search.Definition(
    filter = f"ExchangeCode eq 'CBT' and PrimaryChainRIC eq '0#C:'",
    order_by = "ExpiryDate desc",
    top = 1000,
    select = "DTSubjectName, ExchangeName, RIC, RCSAssetCategoryLeaf, AssetState, ExpiryDate, UnderlyingQuoteRIC, FirstNotice",
).get_data()
df  = response.data.df

and then if i wanted to import (say) the settlement prices over history with each code/ric as a column and the rows as days - is there something you could specifically point me to. (to avoid looping calls)

jonathan.legrand

Hi @Shadeun,

Yes, your Query should indeed look like something like what you have.

I'd advise looking into the search views as shown in the article:

I'd also advise looking into the fields you're after, since you might actually find that you can get exactly what you're after straight off the Search API: https://developers.refinitiv.com/en/article-catalog/article/building-search-into-your-application-workflow#Metadata

I wrote a little piece of code that did this for Gov Corp Bonds for e.g.:
https://gist.github.com/johnukfr/241c2b360a30f96371f430005c8d7738

Shadeun

Thank you,

Ill have a look through codebook.

One more thing: I cant seem to select the expiry information around the assets (TR.FOFirstNoticeDay for example) though the Expiry Date will come through correctly.

jonathan.legrand

Hi @Shadeun do you mean to say that the field 'TR.FOFirstNoticeDay' exist for the RICs you are after but returns no data? Or are you saying that you are after a field that returns Expiry Date?
If you are after a specific field to be found with the Search API, I suggest searching for the field itself as per this article.