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