This notebook looks at how wide the typical spread is between different CL contracts.
First, load the last 30 days of prices. For the BID_ASK bar type, the Open field contains the average bid and the Close field contains the average ask:
from quantrocket import get_prices
import pandas as pd
start_date = pd.Timestamp.today() - pd.Timedelta(days=30)
prices = get_prices("cl-1min-bbo", universes="cl-fut", start_date=start_date, fields=["Open","Close"])
bids = prices.loc["Open"]
asks = prices.loc["Close"]
midpoints = (bids+asks) / 2
Using a DataFrame of prices, we can use the function get_contract_nums_reindexed_like to obtain a similarly indexed DataFrame showing each contract's numerical sequence in the contract chain as of any given date. Using the limit parameter, we ask the function to sequence the next 15 contracts.
from quantrocket.master import get_contract_nums_reindexed_like
limit = 15
contract_nums = get_contract_nums_reindexed_like(midpoints, limit=limit)
contract_nums.tail()
| Sid | QF000000023507 | QF000000023508 | QF000000023535 | QF000000023536 | QF000000023564 | QF000000023565 | QF000000023599 | QF000000023601 | QF000000023603 | QF000000023605 | ... | QF000000025052 | QF000000025053 | QF000000025054 | QF000000025055 | QF000000025057 | QF000000025076 | QF000000025085 | QF000000025105 | QF000000025356 | QF000000026350 | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Date | Time | |||||||||||||||||||||
| 2019-09-06 | 23:55:00 | 3.0 | 15.0 | 9.0 | NaN | 1.0 | 2.0 | 4.0 | 8.0 | 14.0 | 13.0 | ... | 11.0 | 10.0 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
| 23:56:00 | 3.0 | 15.0 | 9.0 | NaN | 1.0 | 2.0 | 4.0 | 8.0 | 14.0 | 13.0 | ... | 11.0 | 10.0 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | |
| 23:57:00 | 3.0 | 15.0 | 9.0 | NaN | 1.0 | 2.0 | 4.0 | 8.0 | 14.0 | 13.0 | ... | 11.0 | 10.0 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | |
| 23:58:00 | 3.0 | 15.0 | 9.0 | NaN | 1.0 | 2.0 | 4.0 | 8.0 | 14.0 | 13.0 | ... | 11.0 | 10.0 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | |
| 23:59:00 | 3.0 | 15.0 | 9.0 | NaN | 1.0 | 2.0 | 4.0 | 8.0 | 14.0 | 13.0 | ... | 11.0 | 10.0 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
5 rows × 24 columns
Next we get a Series of midpoints for each contract num by masking the midpoints DataFrame with the contract num DataFrame and taking the mean of each row. In taking the mean, we rely on the fact that the mask leaves only one non-null observation per row, thus the mean simply gives us that observation.
midpoints_by_contract_num = {}
for i in range(1,limit+1):
midpoints_by_contract_num[i] = midpoints.where(contract_nums == i).mean(axis=1)
We loop through the contract months to generate a matrix of dollar spreads between contract months:
data = {}
for col_i in range(1,limit+1):
data[col_i] = []
for row_i in range(1, limit+1):
if col_i == row_i:
data[col_i].append(None)
continue
spreads = (midpoints_by_contract_num[col_i] - midpoints_by_contract_num[row_i]).abs()
data[col_i].append(spreads.median())
pct_spreads = pd.DataFrame(data, index=range(1,limit+1))
pct_spreads.index.name = "contract month"
pct_spreads.columns.name = "contract month"
The matrix can be used to generate a heat map, which reveals that the closer the contract months, the tighter the spreads:
import seaborn as sns
ax = sns.heatmap(pct_spreads, annot=True)
ax.set_title("Average dollar spread between CL contracts")
Text(0.5,1,'Average dollar spread between CL contracts')
