Pulling in expired and present Futures contracts for Cotton

Asher
Asher Newcomer

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.")

Answers