Pulling in expired and present Futures contracts for Cotton

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
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):Instantiate the FuturesRICs class
# 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]}')"
)
fr = FuturesRICs()
Define parameters for the search.underlying = 'CT' # RIC root; change as needed.
current_year = datetime.now().year
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
-
Thank you for reaching out to us.
You can refer to the Reconstructing RICs for expired futures contracts article that provides the FuturesRICs code.
Please clarify the problem in the code. Moreover, please paste the code in the Code Block for readability.
0 -
0
-
0
-
0
-
Can you see the code now
I just need some help as to how I can pull in the data in such as way to have the previous expired RIC codes and current futures contracts.
I need to do seasonality analysis on Future Calendar Spreads0 -
The month should be a number between 1 and 12.
0 -
Correct, I can fix that, well I suppose the question is how do I have code that can pull both the expired futures contracts and existing active futures contracts?
Because I need to be able to do Calendar Spread analysis with adjustments for seasonality0 -
The sample code shows the way to construct RICs based on RIC rules. For example:
You need to know RICs to request historical data.
You may use the Search API to search for the current RICs. For example:
ld.discovery.search( view = ld.discovery.Views.SEARCH_ALL, filter = "RCSAssetCategoryLeaf eq 'Commodity Future' and TickerSymbol eq 'CT' and ExpiryDate ne null and PrimaryChainRIC eq '0#CT:'", select = "RIC,DocumentTitle,ExpiryDate", top = 1000 )
The output is:
The examples are on GitHub. For more information regarding the Search API, please refer to this Building Search into your Application Workflow article.
If the code doesn't meet your requirements, you may contact the helpdesk team via MyAccount and ask for RICs that can provide the required data. Then, use the RICs with the get_history method to get historical data.
0 -
Thank you very much for this. I just have a question regarding metadata of the contract, is it possible to pull in metadata such as options expiry, first notice date? As I need this so that when I construct the roll over rule I know the date at which I will roll over my time series to the next contract
0 -
I have provided the response on this discussion.
0
Categories
- All Categories
- 3 Polls
- 6 AHS
- 36 Alpha
- 166 App Studio
- 6 Block Chain
- 4 Bot Platform
- 18 Connected Risk APIs
- 47 Data Fusion
- 34 Data Model Discovery
- 685 Datastream
- 1.4K DSS
- 615 Eikon COM
- 5.2K Eikon Data APIs
- 10 Electronic Trading
- Generic FIX
- 7 Local Bank Node API
- 3 Trading API
- 2.9K Elektron
- 1.4K EMA
- 252 ETA
- 556 WebSocket API
- 38 FX Venues
- 14 FX Market Data
- 1 FX Post Trade
- 1 FX Trading - Matching
- 12 FX Trading – RFQ Maker
- 5 Intelligent Tagging
- 2 Legal One
- 23 Messenger Bot
- 3 Messenger Side by Side
- 9 ONESOURCE
- 7 Indirect Tax
- 60 Open Calais
- 275 Open PermID
- 44 Entity Search
- 2 Org ID
- 1 PAM
- PAM - Logging
- 6 Product Insight
- Project Tracking
- ProView
- ProView Internal
- 22 RDMS
- 1.9K Refinitiv Data Platform
- 650 Refinitiv Data Platform Libraries
- 4 LSEG Due Diligence
- LSEG Due Diligence Portal API
- 4 Refinitiv Due Dilligence Centre
- Rose's Space
- 1.2K Screening
- 18 Qual-ID API
- 13 Screening Deployed
- 23 Screening Online
- 12 World-Check Customer Risk Screener
- 1K World-Check One
- 46 World-Check One Zero Footprint
- 45 Side by Side Integration API
- 2 Test Space
- 3 Thomson One Smart
- 10 TR Knowledge Graph
- 151 Transactions
- 143 REDI API
- 1.8K TREP APIs
- 4 CAT
- 27 DACS Station
- 121 Open DACS
- 1.1K RFA
- 104 UPA
- 193 TREP Infrastructure
- 228 TRKD
- 917 TRTH
- 5 Velocity Analytics
- 9 Wealth Management Web Services
- 90 Workspace SDK
- 11 Element Framework
- 5 Grid
- 18 World-Check Data File
- 1 Yield Book Analytics
- 46 中文论坛