QuantRocket maintains historical lists of contract chains to facilitate mapping historical futures data to QuantRocket SIDs (security IDs). This notebook shows how to obtain the list of contracts and load it into your securities master database. In the next notebook, we will query these contracts from the securities master database to map our third-party data to SIDs.
The list of historical contract specifications is currently not accessible by API and should be obtained by contacting us (subscribers only, please). Upload the contract_chains.zip file to the current directory (/codeload/futures_imports) and unzip it into a subdirectory called contract_chains by running the following command:
! unzip -o contract_chains.zip -d contract_chains
Archive: contract_chains.zip inflating: contract_chains/XLME.csv inflating: contract_chains/XHKF.csv inflating: contract_chains/XEUE.csv inflating: contract_chains/XNYM.csv inflating: contract_chains/XCME.csv inflating: contract_chains/XEMD.csv inflating: contract_chains/XBRD.csv inflating: contract_chains/XMRV.csv inflating: contract_chains/XOSE.csv inflating: contract_chains/XSFE.csv inflating: contract_chains/XSES.csv inflating: contract_chains/XKRX.csv inflating: contract_chains/XNLI.csv inflating: contract_chains/XCEC.csv inflating: contract_chains/XMON.csv inflating: contract_chains/IFEN.csv inflating: contract_chains/NDEX.csv inflating: contract_chains/IFLL.csv inflating: contract_chains/IFLX.csv inflating: contract_chains/XNSE.csv inflating: contract_chains/XDMI.csv inflating: contract_chains/XMOD.csv inflating: contract_chains/root_symbols.csv inflating: contract_chains/XSTO.csv inflating: contract_chains/EUCH.csv inflating: contract_chains/XCBF.csv inflating: contract_chains/XEUR.csv inflating: contract_chains/XCBT.csv inflating: contract_chains/SMFE.csv inflating: contract_chains/XMAT.csv inflating: contract_chains/IFUS.csv inflating: contract_chains/exchanges_to_mics.yml
The root_symbols.csv file contains the list of available root symbols and contract specifications (but not the individual contracts with expiration dates). The start_date column indicates how far back the historical contract chains go (and thus how far back historical data can be imported).
Lists of individual contracts with expiration dates are organized by MIC (Market Identifier Code). For example, contract_chains/XCME.csv contains contract chains for contracts traded on CME.
The exchanges_to_mics.yml file provides a (non-exhaustive) mapping of common exchange codes (e.g. NYMEX) to MICs (e.g. XNYM). This file is used in the next notebook to faciliate mapping the source data to SIDs and can also be used to help identify the MICs for the exchanges you're interested in.
If a contract you need is not available, or the contract chain history doesn't go back far enough, please contact us for assistance.
For each exchange you're interested in, load the list of contract chains into the SecurityIBKR and SecuritySid tables of the securities master database. Below, we load XCME (CME).
Other than editing the code to your desired list of MICs, this code block can be run as-is:
import pandas as pd
from quantrocket.db import connect_sqlite, insert_or_ignore
master_db_conn = connect_sqlite("/var/lib/quantrocket/quantrocket.v2.master.main.sqlite")
# edit to your desired MICs, the rest of this code block can be run as-is
desired_exchanges = [
"XCME"
]
for exchange in desired_exchanges:
print(f"loading {exchange} contracts")
contracts = pd.read_csv(f"contract_chains/{exchange}.csv")
insert_or_ignore(contracts, "SecurityIBKR", master_db_conn)
insert_or_ignore(contracts[["Sid"]], "SecuritySid", master_db_conn)
loading XCME contracts
After loading the contract chains into the SecurityIBKR table, restart the securities master container from the host machine:
docker compose restart masterThis will rebuild the database and make the newly loaded contracts queryable. You can query the root symbol to make sure the historical contract chains have been successfully loaded:
from quantrocket.master import get_securities
contracts = get_securities(
symbols=["ES"],
exchanges=["XCME"],
sec_types="FUT",
fields=["Symbol", "Exchange", "SecType", "Name", "LastTradeDate"]
)
contracts.sort_values("LastTradeDate").head()
| Symbol | Exchange | SecType | Name | LastTradeDate | |
|---|---|---|---|---|---|
| Sid | |||||
| QF000000654904 | ESZ97 | XCME | FUT | E-mini S&P 500 | 1997-12-18 |
| QF000000654905 | ESH98 | XCME | FUT | E-mini S&P 500 | 1998-03-20 |
| QF000000654906 | ESM98 | XCME | FUT | E-mini S&P 500 | 1998-06-19 |
| QF000000654907 | ESU98 | XCME | FUT | E-mini S&P 500 | 1998-09-21 |
| QF000000654908 | ESZ98 | XCME | FUT | E-mini S&P 500 | 1998-12-18 |