Pulling in expired and present Futures contracts for Cotton

Asher
Asher Newcomer
edited March 11 in Refinitiv Data Platform Libraries

Hi,

I am trying to pull in present and expired cotton futures contracts.

I would like to be able to graph the past and present historical data to have good accuracy.

Additionally I need to have it this way in order to have accurate calendar spread pricing.

In addition to this how can I adjust my code for the seasonality ie today will not be same day as next year or the year previously if that makes sense

Thank you

Asher

import lseg.data as ld
import pandas as pd
import calendar
from datetime import datetime
import refinitiv.data as rd
from refinitiv.data.content import historical_pricing
from refinitiv.data.content.historical_pricing import Intervals

Open sessions for both modules.

ld.open_session()
rd.open_session() # Creates a default session for refinitiv.data calls

class FuturesRICs:
MONTH_CODES = {
'F': '01', 'G': '02', 'H': '03', 'J': '04', 'K': '05', 'M': '06',
'N': '07', 'Q': '08', 'U': '09', 'V': '10', 'X': '11', 'Z': '12'
}
ASSET_CATEGORY = "Future"
DATE_FORMAT = "%Y-%m-%d"

def get_futures(self, underlying, month, year):

    # Convert the input month into numeric format and its corresponding code

    month_num, month_code = self._get_month_number_and_code(month)

    # Calculate the last day of the month (adjusted by subtracting 1 as in the original snippet)

    month_last_day = calendar.monthrange(year, int(month_num))[1] - 1


    # Define two possible year codes to account for RIC structure changes

    year_codes = [str(year)[-1], str(year)[-2:]]

    for year_code in year_codes:

        query = f'{underlying}{month_code}{year_code}*'

        filter_criteria = self._build_filter_criteria(year, month_num, month_last_day, query)

        response = self._search_futures(query, filter_criteria)

        if response is not None and not response.empty:

            return response

    print(f'No futures contract for {underlying} expiring on month {month_num} of {year}')

    return None


def _search_futures(self, query, filter_criteria):

    response = rd.discovery.search(

        view=rd.discovery.Views.DERIVATIVE_QUOTES,

        query=query,

        select="DocumentTitle, RIC, ExchangeCode, ExpiryDate, UnderlyingQuoteRIC, RCSAssetCategoryLeaf, RetireDate",

        filter=filter_criteria

    )

    return response if not response.empty else None


def _get_month_number_and_code(self, month):

    # If month is provided as an integer or numeric string

    if isinstance(month, int) or (isinstance(month, str) and month.isnumeric()):

        month_num = f'{int(month):02}'

        month_code = next((code for code, num in self.MONTH_CODES.items() if num == month_num), None)

        if month_code is None:

            raise ValueError(f"Invalid numeric month: {month}")

    else:

        # If month is provided as a letter code

        month_code = month.upper()

        month_num = self.MONTH_CODES.get(month_code)

        if month_num is None:

            raise ValueError(f"Invalid month code: {month_code}")

    return month_num, month_code


def _build_filter_criteria(self, year, month_num, month_last_day, query):

    return (

        f"RCSAssetCategoryLeaf eq '{self.ASSET_CATEGORY}' and "

        f"ExpiryDate ge {year-1}-{month_num}-{month_last_day} and "

        f"ExpiryDate le {year+1}-{month_num}-{month_last_day} and "

        f"(RIC xeq '{query[:-1]}' or RIC xeq '{query[:-1]}^{str(year)[-2]}')"

    )
Instantiate the FuturesRICs class

fr = FuturesRICs()

Define parameters for the search.

underlying = 'CT' # RIC root; change as needed.
current_year = datetime.now().year

Loop over the last 10 years and every month code defined.

dfs = [] # List to hold DataFrames for each contract
contracts_found = [] # To record the RICs retrieved

for year in range(current_year - 3, current_year):
for month in FuturesRICs.MONTH_CODES.keys():
print(f"Processing expired contract for year {year}, month {month}...")
expired = fr.get_futures(underlying, month, year)
if expired is not None:
contract_ric = expired['RIC'][0]
contracts_found.append(contract_ric)

        # Set the start date to 10 years ago from today.

        start_date = (datetime.now() - pd.DateOffset(years=10)).strftime('%Y-%m-%d')

        end_date = datetime.now().strftime('%Y-%m-%d')


        # Pull historical daily trading price data for the contract.

        df_temp = ld.get_history(

            contract_ric,

            'TRDPRC_1',

            start=start_date,

            end=end_date,

            interval='daily'

        )

        # Add columns to indicate contract, year, and month.

        df_temp['Contract'] = contract_ric

        df_temp['Year'] = year

        df_temp['Month'] = month

        dfs.append(df_temp)

    else:

        print(f"No expired futures found for year {year}, month {month}.")

if dfs:
combined_df = pd.concat(dfs, ignore_index=True)

# Save the long format DataFrame to CSV.

csv_path_long = 'a file path'

combined_df.to_csv(csv_path_long, index=False)

print("Long format CSV file saved at:", csv_path_long)


# Pivot to wide format: rows are dates, columns are contracts, values are TRDPRC_1.

# Ensure there is a date column (if not, reset the index).

if 'Date' not in combined_df.columns:

    combined_df = combined_df.reset_index().rename(columns={'index': 'Date'})


wide_df = combined_df.pivot_table(index='Date', columns='Contract', values='TRDPRC_1')


# Save the wide format DataFrame to CSV.

csv_path_wide = r"a file path

print("Wide format CSV file saved at:", csv_path_wide)


print("Contracts found:", contracts_found)

print("Combined DataFrame head (long format):")

print(combined_df.head())

else:
print("No expired futures found for any month in any year in the last 10 years.")

Tagged:

Answers

Categories