Case Study: Hedging A Long-Only SPX Portfolio With Costless Collars#
import warnings
from pathlib import Path
import numpy as np
import pandas as pd
import plotly.io as pio
import pull_options_data
import spx_hedging_functions
from settings import config
pd.set_option("display.max_columns", None)
pio.templates.default = "plotly_white"
warnings.filterwarnings("ignore")
# Load environment variables
DATA_DIR = Path(config("DATA_DIR"))
OUTPUT_DIR = Path(config("OUTPUT_DIR"))
WRDS_USERNAME = config("WRDS_USERNAME")
START_DATE = config("START_DATE")
END_DATE = config("END_DATE")
asof_date = "2023-08-02"
interpolation_args = {"method": "cubic", "order": 3}
# enter the range of costless collar to construct
d = 0.80
contracts_to_display = 15
r = 0.045
N = 1000
days_in_year = 365.0
strike_range = (1000, 6000)
expiry_range = (0.01, 2.0)
dt = 1 / days_in_year
option_chain = pull_options_data.load_spx_option_data(
asof_date=asof_date, start_date=START_DATE, end_date=END_DATE
)
# do we need to check if forward prices for calls and puts are the same? no, because the sql_query() function is pulling the forward price from a single other dataset and joining tables
# Build market predictions
delta_map, prediction_range = spx_hedging_functions.build_market_predictions(
option_chain=option_chain,
asof_date=asof_date,
contracts_to_display=contracts_to_display,
interpolation_args=interpolation_args,
)
# Display prediction range table
prediction_range_reformat = prediction_range.copy()
prediction_range_reformat.index = prediction_range_reformat.index.strftime("%Y-%m-%d")
prediction_range_reformat.index.name = "Expiration Date"
prediction_range_reformat = prediction_range_reformat.T
prediction_range_reformat = prediction_range_reformat.iloc[:, :contracts_to_display]
prediction_range_reformat = prediction_range_reformat.style.format("{:.2f}")
prediction_range_reformat
| Expiration Date | 2023-08-03 | 2023-08-04 | 2023-08-07 | 2023-08-08 | 2023-08-09 | 2023-08-10 | 2023-08-11 | 2023-08-14 | 2023-08-15 | 2023-08-16 | 2023-08-17 | 2023-08-18 | 2023-08-21 | 2023-08-22 | 2023-08-23 |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 80% Below | 4545.00 | 4560.00 | 4560.00 | 4565.00 | 4570.00 | 4580.00 | 4585.00 | 4595.00 | 4600.00 | 4605.00 | 4605.00 | 4610.00 | 4620.00 | 4625.00 | 4625.00 |
| 50/50 | 4515.00 | 4515.00 | 4515.00 | 4520.00 | 4520.00 | 4520.00 | 4520.00 | 4520.00 | 4520.00 | 4525.00 | 4525.00 | 4525.00 | 4525.00 | 4525.00 | 4530.00 |
| 80% Above | 4475.00 | 4460.00 | 4455.00 | 4450.00 | 4440.00 | 4430.00 | 4425.00 | 4420.00 | 4415.00 | 4410.00 | 4405.00 | 4400.00 | 4395.00 | 4395.00 | 4390.00 |
| Fwd Prices 2023-08-02 | 4513.99 | 4514.59 | 4516.40 | 4517.01 | 4517.61 | 4518.21 | 4518.81 | 4520.64 | 4521.25 | 4521.86 | 4522.47 | 4523.09 | 4524.94 | 4525.56 | 4526.18 |
# Create pareto chart visualization
pareto_data = spx_hedging_functions.build_pareto_chart(
ticker="SPX",
asof_date=asof_date,
prediction_range=prediction_range,
delta_map=delta_map,
contracts_to_display=contracts_to_display,
)
# Display pareto chart
pareto_data['figure'].show()
# Display prediction range table
pareto_data['prediction_range'].style.format("{:.2f}")
| Expiration Date | 2023-08-03 | 2023-08-04 | 2023-08-07 | 2023-08-08 | 2023-08-09 | 2023-08-10 | 2023-08-11 | 2023-08-14 | 2023-08-15 | 2023-08-16 | 2023-08-17 | 2023-08-18 | 2023-08-21 | 2023-08-22 | 2023-08-23 |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 80% Below | 4545.00 | 4560.00 | 4560.00 | 4565.00 | 4570.00 | 4580.00 | 4585.00 | 4595.00 | 4600.00 | 4605.00 | 4605.00 | 4610.00 | 4620.00 | 4625.00 | 4625.00 |
| 50/50 | 4515.00 | 4515.00 | 4515.00 | 4520.00 | 4520.00 | 4520.00 | 4520.00 | 4520.00 | 4520.00 | 4525.00 | 4525.00 | 4525.00 | 4525.00 | 4525.00 | 4530.00 |
| 80% Above | 4475.00 | 4460.00 | 4455.00 | 4450.00 | 4440.00 | 4430.00 | 4425.00 | 4420.00 | 4415.00 | 4410.00 | 4405.00 | 4400.00 | 4395.00 | 4395.00 | 4390.00 |
| Fwd Prices 2023-08-02 | 4513.99 | 4514.59 | 4516.40 | 4517.01 | 4517.61 | 4518.21 | 4518.81 | 4520.64 | 4521.25 | 4521.86 | 4522.47 | 4523.09 | 4524.94 | 4525.56 | 4526.18 |
# Create delta heatmap visualization
delta_heatmap = spx_hedging_functions.plot_delta_heatmap(
ticker="SPX",
asof_date=asof_date,
delta_map=delta_map,
contracts_to_display=contracts_to_display,
heatmap_step=3,
)
| Expiration Date | 2023-08-03 | 2023-08-04 | 2023-08-07 | 2023-08-08 | 2023-08-09 | 2023-08-10 | 2023-08-11 | 2023-08-14 | 2023-08-15 | 2023-08-16 | 2023-08-17 | 2023-08-18 | 2023-08-21 | 2023-08-22 | 2023-08-23 |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Price >= | |||||||||||||||
| $4,615.00 | 1% | 2% | 4% | 7% | 8% | 11% | 7% | 12% | 14% | 16% | 17% | 19% | 21% | 22% | 23% |
| $4,600.00 | 1% | 4% | 8% | 10% | 12% | 10% | 14% | 17% | 19% | 21% | 22% | 24% | 26% | 27% | 28% |
| $4,585.00 | 2% | 8% | 12% | 9% | 13% | 17% | 20% | 23% | 25% | 26% | 28% | 29% | 30% | 31% | 32% |
| $4,570.00 | 5% | 14% | 13% | 18% | 21% | 25% | 27% | 29% | 31% | 32% | 33% | 34% | 36% | 36% | 37% |
| $4,555.00 | 12% | 14% | 24% | 27% | 29% | 32% | 34% | 36% | 37% | 38% | 39% | 39% | 41% | 41% | 42% |
| $4,540.00 | 24% | 29% | 34% | 36% | 38% | 40% | 41% | 42% | 43% | 44% | 44% | 45% | 46% | 46% | 46% |
| $4,525.00 | 36% | 42% | 45% | 46% | 46% | 47% | 48% | 49% | 49% | 49% | 50% | 50% | 51% | 51% | 51% |
| $4,510.00 | 55% | 54% | 54% | 54% | 54% | 54% | 54% | 55% | 55% | 55% | 55% | 55% | 55% | 55% | 55% |
| $4,495.00 | 69% | 64% | 63% | 62% | 62% | 61% | 60% | 60% | 60% | 60% | 59% | 59% | 59% | 59% | 59% |
| $4,480.00 | 79% | 72% | 71% | 69% | 68% | 66% | 65% | 65% | 65% | 64% | 64% | 64% | 64% | 63% | 63% |
| $4,465.00 | 85% | 79% | 77% | 75% | 73% | 71% | 70% | 70% | 69% | 68% | 68% | 67% | 67% | 67% | 66% |
| $4,450.00 | 88% | 83% | 81% | 79% | 78% | 76% | 74% | 74% | 73% | 72% | 71% | 71% | 71% | 70% | 70% |
| $4,435.00 | 90% | 87% | 85% | 83% | 82% | 79% | 78% | 77% | 76% | 75% | 75% | 74% | 74% | 73% | 73% |
| $4,420.00 | 91% | 89% | 88% | 86% | 85% | 82% | 81% | 80% | 79% | 78% | 77% | 77% | 76% | 76% | 75% |
| $4,405.00 | 92% | 91% | 90% | 88% | 87% | 85% | 83% | 83% | 82% | 81% | 80% | 79% | 79% | 78% | 78% |
| $4,390.00 | 93% | 92% | 91% | 90% | 89% | 87% | 86% | 85% | 84% | 83% | 82% | 81% | 81% | 80% | 80% |
| $4,375.00 | 94% | 93% | 92% | 91% | 91% | 88% | 87% | 87% | 86% | 85% | 84% | 83% | 83% | 82% | 82% |
| $4,360.00 | 94% | 93% | 93% | 92% | 92% | 90% | 89% | 88% | 87% | 87% | 86% | 85% | 85% | 84% | 83% |
| $4,345.00 | 94% | 94% | 94% | 93% | 93% | 91% | 90% | 90% | 89% | 88% | 87% | 87% | 86% | 85% | 85% |
| $4,330.00 | 95% | 94% | 94% | 94% | 93% | 92% | 91% | 91% | 90% | 89% | 88% | 88% | 87% | 87% | 86% |
| $4,315.00 | 95% | 95% | 95% | 94% | 94% | 93% | 92% | 92% | 91% | 90% | 90% | 89% | 89% | 88% | 88% |
| $4,300.00 | 95% | 95% | 95% | 95% | 94% | 93% | 93% | 92% | 92% | 91% | 90% | 90% | 90% | 89% | 89% |
| $4,285.00 | 96% | 95% | 95% | 95% | 95% | 94% | 93% | 93% | 93% | 92% | 91% | 91% | 91% | 90% | 90% |
| $4,270.00 | 96% | 96% | 96% | 95% | 95% | 94% | 94% | 94% | 93% | 93% | 92% | 92% | 91% | 91% | 90% |
| $4,255.00 | 96% | 96% | 96% | 96% | 95% | 94% | 94% | 94% | 94% | 93% | 93% | 92% | 92% | 92% | 91% |
| $4,240.00 | 96% | 96% | 96% | 96% | 96% | 95% | 95% | 95% | 94% | 94% | 93% | 93% | 93% | 92% | 92% |
| $4,225.00 | 96% | 96% | 96% | 96% | 96% | 95% | 95% | 95% | 95% | 94% | 94% | 93% | 93% | 93% | 93% |
| $4,210.00 | 96% | 96% | 97% | 96% | 96% | 95% | 95% | 95% | 95% | 95% | 94% | 94% | 94% | 93% | 93% |
| $4,195.00 | 97% | 96% | 97% | 97% | 96% | 96% | 95% | 96% | 95% | 95% | 95% | 94% | 94% | 94% | 94% |
| $4,180.00 | 97% | 97% | 97% | 97% | 96% | 96% | 96% | 96% | 96% | 95% | 95% | 95% | 95% | 94% | 94% |
| $4,165.00 | 97% | 97% | 97% | 97% | 97% | 96% | 96% | 96% | 96% | 96% | 95% | 95% | 95% | 95% | 94% |
| $4,150.00 | 97% | 97% | 97% | 97% | 97% | 96% | 96% | 96% | 96% | 96% | 95% | 95% | 95% | 95% | 95% |
| $4,135.00 | 97% | 97% | 97% | 97% | 97% | 96% | 96% | 96% | 96% | 96% | 96% | 96% | 96% | 95% | 95% |
| $4,120.00 | 97% | 97% | 97% | 97% | 97% | 96% | 96% | 97% | 96% | 96% | 96% | 96% | 96% | 96% | 95% |
| $4,100.00 | 97% | 97% | 97% | 97% | 97% | 97% | 97% | 97% | 97% | 97% | 96% | 96% | 96% | 96% | 96% |
| $4,075.00 | 97% | 97% | 98% | 97% | 97% | 97% | 97% | 97% | 97% | 97% | 96% | 96% | 96% | 96% | 96% |
| $4,050.00 | 97% | 97% | 98% | 98% | 98% | 97% | 97% | 97% | 97% | 97% | 97% | 97% | 97% | 97% | 96% |
| $4,025.00 | 98% | 98% | 98% | 98% | 98% | 97% | 97% | 97% | 97% | 97% | 97% | 97% | 97% | 97% | 97% |
| $4,000.00 | 98% | 98% | 98% | 98% | 98% | 97% | 97% | 98% | 97% | 97% | 97% | 97% | 97% | 97% | 97% |
| $3,975.00 | 98% | 98% | 98% | 98% | 98% | 97% | 97% | 98% | 98% | 98% | 97% | 97% | 97% | 97% | 97% |
| $3,950.00 | 98% | 98% | 98% | 98% | 98% | 98% | 98% | 98% | 98% | 98% | 97% | 97% | 98% | 97% | 97% |
| $3,925.00 | 98% | 98% | 98% | 98% | 98% | 98% | 98% | 98% | 98% | 98% | 98% | 98% | 98% | 98% | 98% |
| $3,900.00 | 98% | 98% | 98% | 98% | 98% | 98% | 98% | 98% | 98% | 98% | 98% | 98% | 98% | 98% | 98% |
| $3,875.00 | 98% | 98% | 98% | 98% | 98% | 98% | 98% | 98% | 98% | 98% | 98% | 98% | 98% | 98% | 98% |
| $3,850.00 | 98% | 98% | 98% | 98% | 98% | 98% | 98% | 98% | 98% | 98% | 98% | 98% | 98% | 98% | 98% |
| $3,825.00 | 98% | 98% | 98% | 98% | 98% | 98% | 98% | 98% | 98% | 98% | 98% | 98% | 98% | 98% | 98% |
| $3,800.00 | 98% | 98% | 98% | 98% | 98% | 98% | 98% | 98% | 98% | 98% | 98% | 98% | 98% | 98% | 98% |
| $3,775.00 | 98% | 98% | 99% | 98% | 98% | 98% | 98% | 98% | 98% | 98% | 98% | 98% | 98% | 98% | 98% |
| $3,750.00 | 98% | 98% | 99% | 99% | 99% | 98% | 98% | 98% | 99% | 99% | 98% | 98% | 99% | 98% | 98% |
| $3,725.00 | 98% | 98% | 99% | 99% | 99% | 98% | 98% | 98% | 99% | 99% | 98% | 98% | 99% | 99% | 99% |
| $3,700.00 | 98% | 98% | 99% | 99% | 99% | 98% | 98% | 98% | 99% | 99% | 99% | 99% | 99% | 99% | 99% |
| $3,675.00 | 98% | 99% | 99% | 99% | 99% | 98% | 98% | 99% | 99% | 99% | 99% | 98% | 99% | 99% | 99% |
| $3,650.00 | 98% | 99% | 99% | 99% | 99% | 99% | 98% | 99% | 99% | 99% | 99% | 99% | 99% | 99% | 99% |
| $3,625.00 | 99% | 99% | 99% | 99% | 99% | 99% | 99% | 99% | 99% | 99% | 99% | 99% | 99% | 99% | 99% |
| $3,600.00 | 99% | 99% | 99% | 99% | 99% | 99% | 99% | 99% | 99% | 99% | 99% | 99% | 99% | 99% | 99% |
| $3,575.00 | 99% | 99% | 99% | 99% | 99% | 99% | 99% | 99% | 99% | 99% | 99% | 99% | 99% | 99% | 99% |
| $3,550.00 | 99% | 99% | 99% | 99% | 99% | 99% | 99% | 99% | 99% | 99% | 99% | 99% | 99% | 99% | 99% |
| $3,525.00 | 99% | 99% | 99% | 99% | 99% | 99% | 99% | 99% | 99% | 99% | 99% | 99% | 99% | 99% | 99% |
| $3,500.00 | 99% | 99% | 99% | 99% | 99% | 99% | 99% | 99% | 99% | 99% | 99% | 99% | 99% | 99% | 99% |
| $3,450.00 | 99% | 99% | 99% | 99% | 99% | 99% | 99% | 99% | 99% | 99% | 99% | 99% | 99% | 99% | 99% |
| $3,375.00 | 99% | 99% | 99% | 99% | 99% | 99% | 99% | 99% | 99% | 99% | 99% | 99% | 99% | 99% | 99% |
| $3,300.00 | 99% | 99% | 99% | 99% | 99% | 99% | 99% | 99% | 99% | 99% | 99% | 99% | 99% | 99% | 99% |
| $3,225.00 | 99% | 99% | 99% | 99% | 99% | 99% | 99% | 99% | 99% | 99% | 99% | 99% | 99% | 99% | 99% |
# get the forward prices
fwd_prices = spx_hedging_functions.get_fwd_prices(asof_date, option_chain, contracts_to_display)
iv_surface, price_surface = spx_hedging_functions.build_iv_and_price_surface(
option_chain,
fwd_prices,
asof_date,
strike_range,
expiry_range,
smoothing=True,
smoothing_params={"sigma": 2, "clip_bounds": (0.05, 0.75)},
)
spx_hedging_functions.plot_vol_price_charts(
iv_surface=iv_surface,
price_surface=price_surface,
strike_range=[np.floor(iv_surface.index.min()), np.ceil(iv_surface.index.max())],
expiry_range=[iv_surface.columns.min(), iv_surface.columns.max()],
iv_surface_title="SPX Implied Volatility",
price_surface_title="Price Surface as of " + asof_date,
)
default_percentiles = np.unique(
np.round(np.sort(np.append(np.arange(0.1, 1.0, 0.1), np.array([d, 1 - d]))), 3)
)
print(f"Constructing a {d:.0%} / {(1 - d):.0%} collar...")
Constructing a 80% / 20% collar...
# build the delta map
delta_map = spx_hedging_functions.build_delta_map(
option_chain,
asof_date,
contracts_to_display,
show_detail=False,
weighted=False,
interpolate_missing=True,
interpolation_args=interpolation_args,
)
# simulate a range of futures, including the collar strike %iles
futures_data = spx_hedging_functions.simulate_futures(
asof_date, fwd_prices, iv_surface, r, N, dt, default_percentiles, random_seed=123
)
# Display simulation results
futures_data['figure'].show()
futures_data['display_data'].T.style.format("{:.2f}")
| 2023-08-07 | 2023-08-08 | 2023-08-09 | 2023-08-10 | 2023-08-11 | 2023-08-14 | 2023-08-15 | 2023-08-16 | 2023-08-17 | 2023-08-18 | 2023-08-21 | 2023-08-22 | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 10% | 4449.50 | 4450.67 | 4438.45 | 4428.40 | 4421.81 | 4413.16 | 4403.29 | 4400.49 | 4400.64 | 4390.04 | 4385.70 | 4379.96 |
| 20% | 4474.00 | 4475.09 | 4467.02 | 4465.16 | 4458.16 | 4453.21 | 4451.22 | 4453.14 | 4453.47 | 4443.70 | 4443.10 | 4438.91 |
| 30% | 4492.66 | 4493.71 | 4490.18 | 4487.57 | 4486.09 | 4484.65 | 4485.24 | 4485.20 | 4485.94 | 4484.93 | 4485.61 | 4481.93 |
| 40% | 4506.44 | 4507.57 | 4509.32 | 4506.90 | 4512.25 | 4511.35 | 4515.92 | 4514.30 | 4515.03 | 4514.33 | 4519.59 | 4514.83 |
| 50% | 4521.48 | 4521.89 | 4528.31 | 4525.12 | 4530.28 | 4532.85 | 4539.55 | 4538.36 | 4539.39 | 4541.23 | 4549.80 | 4551.04 |
| 60% | 4537.04 | 4537.69 | 4544.16 | 4543.29 | 4548.85 | 4559.11 | 4564.88 | 4566.87 | 4567.77 | 4570.09 | 4576.72 | 4578.88 |
| 70% | 4554.38 | 4554.53 | 4561.46 | 4566.46 | 4570.92 | 4583.42 | 4590.72 | 4596.62 | 4597.01 | 4604.42 | 4613.82 | 4611.79 |
| 80% | 4572.37 | 4572.06 | 4585.45 | 4594.78 | 4596.27 | 4611.94 | 4623.78 | 4631.77 | 4632.83 | 4640.59 | 4654.08 | 4652.53 |
| 90% | 4599.37 | 4598.36 | 4613.98 | 4619.32 | 4627.56 | 4648.92 | 4664.74 | 4675.08 | 4676.61 | 4682.99 | 4703.60 | 4709.54 |
| Strip 2023-08-02 | 4516.40 | 4517.01 | 4517.61 | 4518.21 | 4518.81 | 4520.64 | 4521.25 | 4521.86 | 4522.47 | 4523.09 | 4524.94 | 4525.56 |
simulated_futures = futures_data['simulated_futures']
delta_map.columns = spx_hedging_functions.dates_to_time_remaining(delta_map.columns, asof_date)
delta_map = delta_map.loc[:, simulated_futures.index]
# Build collar strikes data and visualization
collar_data = spx_hedging_functions.build_collar_strikes(
d,
delta_map,
simulated_futures=simulated_futures,
contracts_to_display=contracts_to_display,
asof_date=asof_date,
option_chain=option_chain,
)
# Display the figure
collar_data['figure'].show()
# Display formatted collar strikes table
display_collar_strikes = collar_data['collar_strikes'].copy()
display_collar_strikes.index = display_collar_strikes.index.strftime("%Y-%m-%d")
display_collar_strikes.T.style.format("{:.2f}")
| 2023-08-07 | 2023-08-08 | 2023-08-09 | 2023-08-10 | 2023-08-11 | 2023-08-14 | 2023-08-15 | 2023-08-16 | 2023-08-17 | 2023-08-18 | 2023-08-21 | 2023-08-22 | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 80% Above | 4455.00 | 4450.00 | 4440.00 | 4430.00 | 4425.00 | 4420.00 | 4415.00 | 4410.00 | 4405.00 | 4400.00 | 4395.00 | 4395.00 |
| 80% Below | 4560.00 | 4565.00 | 4570.00 | 4580.00 | 4585.00 | 4595.00 | 4600.00 | 4605.00 | 4605.00 | 4610.00 | 4620.00 | 4625.00 |
| Sim. 80% Below | 4572.37 | 4572.06 | 4585.45 | 4594.78 | 4596.27 | 4611.94 | 4623.78 | 4631.77 | 4632.83 | 4640.59 | 4654.08 | 4652.53 |
| Sim. 80% Above | 4474.00 | 4475.09 | 4467.02 | 4465.16 | 4458.16 | 4453.21 | 4451.22 | 4453.14 | 4453.47 | 4443.70 | 4443.10 | 4438.91 |
| Strip 2023-08-02 | 4516.40 | 4517.01 | 4517.61 | 4518.21 | 4518.81 | 4520.64 | 4521.25 | 4521.86 | 4522.47 | 4523.09 | 4524.94 | 4525.56 |
| Costless Ceiling | 4750.00 | 4630.00 | 4615.00 | 4620.00 | 4620.00 | 4625.00 | 4620.00 | 4620.00 | 4620.00 | 4620.00 | 4620.00 | 4620.00 |
# Display net cost
print(f"Net cost to hedge this set of collars: ${collar_data['net_cost']:.4f} per unit")
Net cost to hedge this set of collars: $-0.3750 per unit
Part 2: Closing the Gap with Databento – E-mini S&P 500 Options#
Everything above ran on WRDS OptionMetrics data – and that is a problem
if you are actually hedging. OptionMetrics arrives with a long release
lag: our parquet is named spx_options_2022-01_2023-12.parquet, but the
data inside actually ends 2023-08-31. A production desk cannot set
collar strikes off a chain that is years stale.
Databento can close the gap. The course’s
Databento plan is a flat-rate subscription to CME Globex MDP 3.0
(dataset GLBX.MDP3): futures and options on futures, at $0.00 marginal
cost to us.
CME lists a deep S&P 500 options complex: options on the E-mini S&P 500 future. Same index, strikes quoted in the same index points – and covered by the subscription. Two details make the substitution clean:
Which roots? Our WRDS pull filtered
am_settlement = 0(PM-settled). The same split exists on CME: quarterly ES options are AM-settled American contracts (like the classic SPX monthlies), while the end-of-month (EW) and Friday-weekly (EW1-EW4) options are European-style and PM-settled at 4:00 p.m. ET – direct analogs of the SPXW family. Those five roots continue our panel.Options on a future, not on the index. The underlying is the ES future, so put-call parity recovers the futures price and Black-76 is the exact pricing model, not an approximation. Under deterministic carry the volatility of the future equals the volatility of the index forward, so implied volatilities are directly comparable to OptionMetrics’ – a claim we do not have to take on faith. Alongside the recent window set in
.env, the pull always grabs a fixed one-month sample of August 2023 – the last month OptionMetrics covers – precisely so the head-to-head comparison below reproduces on every rebuild, with no custom date configuration.
import plotly.graph_objects as go
import pull_databento_options
chain_db = pull_databento_options.load_es_options_databento()
full_wrds = pull_options_data.load_all_optm_data(
start_date=START_DATE, end_date=END_DATE
)
OVERLAP_START = pd.Timestamp(pull_databento_options.OVERLAP_SAMPLE_START)
OVERLAP_END = min(
full_wrds["date"].max(),
pd.Timestamp(pull_databento_options.OVERLAP_SAMPLE_END),
)
recent_start = chain_db.loc[chain_db["date"] > OVERLAP_END, "date"].min()
print(f"WRDS OptionMetrics coverage: {full_wrds['date'].min():%Y-%m-%d} to {full_wrds['date'].max():%Y-%m-%d}")
print(f"Databento overlap sample: {OVERLAP_START:%Y-%m-%d} to {OVERLAP_END:%Y-%m-%d} (always pulled)")
print(f"Databento recent window: {recent_start:%Y-%m-%d} to {chain_db['date'].max():%Y-%m-%d}")
print(f"Rows in the Databento panel: {len(chain_db):,}")
print(f"Roots: {sorted(chain_db['root'].unique())}")
WRDS OptionMetrics coverage: 2022-01-03 to 2023-08-31
Databento overlap sample: 2023-08-01 to 2023-08-31 (always pulled)
Databento recent window: 2026-09-17 to 2026-09-29
Rows in the Databento panel: 63,887
Roots: ['EW', 'EW1', 'EW2', 'EW3', 'EW4']
# Coverage exhibit: daily option volume from each source. The shaded band
# is where BOTH vendors have data -- the test bed for the validation below.
vol_wrds = full_wrds.groupby("date")["volume"].sum()
vol_db = chain_db.groupby("date")["volume"].sum().reset_index()
# Break the line between the overlap sample and the recent window
# (months we deliberately did not pull).
gap_starts = vol_db.loc[vol_db["date"].diff().dt.days > 45, ["date"]] - pd.Timedelta(days=1)
gap_starts["volume"] = np.nan
vol_db = pd.concat([vol_db, gap_starts]).sort_values("date")
fig = go.Figure()
fig.add_trace(go.Scatter(x=vol_wrds.index, y=vol_wrds.values, name="WRDS OptionMetrics (SPXW)"))
fig.add_trace(go.Scatter(x=vol_db["date"], y=vol_db["volume"], name="Databento CME (EW family)"))
fig.add_vrect(
x0=OVERLAP_START, x1=OVERLAP_END,
fillcolor="lightgray", opacity=0.4, line_width=0,
annotation_text="overlap: both vendors", annotation_position="top left",
)
fig.update_layout(
title="Daily PM-settled S&P 500 option volume, by data source",
yaxis_title="Total contracts traded",
width=1200, height=500,
)
fig.show()
How the two panels line up#
The Databento pull is harmonized to the exact prepared schema of the OptionMetrics parquet, so every charting function above runs unmodified on either source. The differences worth knowing:
Column |
OptionMetrics |
Databento CME (ours) |
|---|---|---|
|
EOD NBBO quotes |
NaN (daily bars carry no quotes) |
|
NBBO midpoint |
daily close (last trade of the UTC day, which on Globex can print hours after the 4:00 p.m. settle) |
|
vendor |
parity-implied price of the underlying ES future |
|
vendor-computed |
computed with our |
|
OptionMetrics id |
Databento |
|
100 (SPX) |
50 (E-mini) |
provenance |
– |
|
The parity trick is worth internalizing: for options on a future, European put-call parity gives
where \(F\) is the price of the underlying future today – exactly the
input Black-76 wants. We median over the five nearest-the-money pairs,
where noise from non-synchronous closes is smallest. The implied
volatilities then come from inverting Black-Scholes on the close,
feeding \(S^* = F e^{-rT}\) (the Black-76 identity) – the same functions
you unit-tested in black_scholes.py.
One subtlety is which future. Weekly and EOM options exercise into the nearest quarterly ES future that expires strictly after the option does (a July option’s underlying is the September future; a week-3 option in a quarterly month already belongs to the next quarter, because the same-date future dies at 9:30 a.m. while the option lives to 4:00 p.m.). So the parity forward should sit a touch above OptionMetrics’ index forward at the option’s own expiry – carry between the option’s expiry and the future’s. We verify exactly that below.
# Sanity checks on the harmonized panel
summary = chain_db[["strike", "midprice", "implied_volatility", "delta", "forwardprice"]].describe()
print(f"All PM-settled: {(chain_db['amsettlement'] == 0).all()}")
print(f"Put deltas all <= 0: {(chain_db.loc[chain_db['type'] == 'Put', 'delta'].dropna() <= 0).all()}")
print(f"IV within (0, 2): {chain_db['implied_volatility'].dropna().between(0, 2).mean():.1%} of rows")
print(f"IV computed for: {chain_db['implied_volatility'].notna().mean():.1%} of rows")
summary
All PM-settled: True
Put deltas all <= 0: True
IV within (0, 2): 100.0% of rows
IV computed for: 86.3% of rows
| strike | midprice | implied_volatility | delta | forwardprice | |
|---|---|---|---|---|---|
| count | 63887.000000 | 63887.000000 | 55121.000000 | 55121.000000 | 60613.000000 |
| mean | 5180.974924 | 61.760459 | 0.218692 | 0.012723 | 5517.237833 |
| std | 1612.629476 | 112.572917 | 0.145579 | 0.368278 | 1520.909518 |
| min | 100.000000 | 0.050000 | 0.031192 | -0.998872 | 4380.768571 |
| 25% | 4170.000000 | 2.600000 | 0.127485 | -0.130669 | 4483.085223 |
| 50% | 4525.000000 | 18.500000 | 0.167203 | -0.011322 | 4538.799249 |
| 75% | 6900.000000 | 78.500000 | 0.255750 | 0.110293 | 7723.503022 |
| max | 11000.000000 | 7599.500000 | 2.514992 | 0.999697 | 8082.268672 |
Does the E-mini chain really track SPX? Testing on the overlap#
The August 2023 sample exists in both panels: every trading day of
the month, OptionMetrics carries the SPXW chain and our Databento pull
carries the EW-family chain. Both sources quote strikes in index points
and share expiration dates (Fridays and month-ends), so we can join
matched contracts on (date, expiration_date, strike, type) and compare
implied volatilities head-to-head: OptionMetrics’ vendor-computed IV
against the IV we backed out of CME closes with our own Black-76 code.
overlap_db = chain_db[chain_db["date"].between(OVERLAP_START, OVERLAP_END)]
matched = overlap_db.merge(
full_wrds[full_wrds["date"] >= OVERLAP_START],
on=["date", "expiration_date", "strike", "type"],
suffixes=("_db", "_om"),
how="inner",
)
matched = matched.dropna(subset=["implied_volatility_db", "implied_volatility_om"])
matched["iv_diff"] = matched["implied_volatility_db"] - matched["implied_volatility_om"]
matched["dte"] = (matched["expiration_date"] - matched["date"]).dt.days
matched["moneyness"] = matched["strike"] / matched["forwardprice_om"] - 1
def iv_match_stats(d, label):
"""One row of comparison statistics, IVs in vol points (x100)."""
return {
"subset": label,
"contracts": len(d),
"median bias (pts)": 100 * d["iv_diff"].median(),
"median |diff| (pts)": 100 * d["iv_diff"].abs().median(),
"p90 |diff| (pts)": 100 * d["iv_diff"].abs().quantile(0.9),
}
liquid = matched[(matched["volume_db"] >= 10) & (matched["volume_om"] >= 10)]
stats = pd.DataFrame(
[
iv_match_stats(matched, "all matched contracts"),
iv_match_stats(liquid, "liquid (volume >= 10 on both)"),
iv_match_stats(liquid[liquid["moneyness"].abs() <= 0.03], "liquid, ATM (within 3% of forward)"),
iv_match_stats(liquid[liquid["dte"].between(7, 60)], "liquid, 7-60 days to expiry"),
iv_match_stats(liquid[liquid["dte"] < 7], "liquid, < 7 days to expiry"),
]
).set_index("subset")
print(f"Matched contracts: {len(matched):,} across "
f"{matched['date'].nunique()} days and {matched['expiration_date'].nunique()} expirations")
stats.style.format("{:,.2f}", subset=stats.columns[1:]).format("{:,.0f}", subset=["contracts"])
Matched contracts: 34,954 across 23 days and 22 expirations
| contracts | median bias (pts) | median |diff| (pts) | p90 |diff| (pts) | |
|---|---|---|---|---|
| subset | ||||
| all matched contracts | 34,954 | 0.43 | 0.64 | 2.38 |
| liquid (volume >= 10 on both) | 17,006 | 0.41 | 0.61 | 2.01 |
| liquid, ATM (within 3% of forward) | 8,207 | 0.36 | 0.63 | 2.20 |
| liquid, 7-60 days to expiry | 11,945 | 0.42 | 0.57 | 1.69 |
| liquid, < 7 days to expiry | 3,277 | 0.47 | 1.12 | 3.45 |
# The same comparison, visually: each point is one matched contract.
sample = liquid[liquid["dte"] >= 7].sample(min(8000, len(liquid)), random_state=0)
fig = go.Figure()
fig.add_trace(go.Scattergl(
x=sample["implied_volatility_om"], y=sample["implied_volatility_db"],
mode="markers", marker=dict(size=3, opacity=0.35),
name="matched contracts (7+ DTE, liquid)",
))
lo, hi = 0.05, 0.45
fig.add_trace(go.Scatter(x=[lo, hi], y=[lo, hi], mode="lines",
line=dict(dash="dash"), name="45-degree line"))
fig.update_layout(
title="Implied volatility, matched contracts: CME E-mini (computed) vs OptionMetrics SPXW (vendor)",
xaxis_title="OptionMetrics implied volatility",
yaxis_title="E-mini implied volatility (Black-76 on CME closes)",
xaxis_range=[lo, hi], yaxis_range=[lo, hi],
width=700, height=700,
)
fig.show()
Matched implied volatilities agree to a fraction of a vol point at the median, with no meaningful bias. The residual scatter has three honest sources, none of which is “the E-mini market disagrees with the SPX market”:
Timing. Our
midpriceis the last trade of the UTC day – on Globex that can print as late as 8:00 p.m. ET – while OptionMetrics snaps NBBO at the 4:00 p.m. close. Four hours of index drift moves short-dated fixed-strike vols the most, which is why the under-a-week bucket is the noisy one.Trade prices vs quote midpoints. A stale close on an illiquid strike can sit a tick or two off the mid.
A constant risk-free rate. The pull inverts IVs with a single
RISK_FREE_RATEwhile the true short rate moved between 2023 and 2026; near the money that is worth a few tenths of a vol point at most.
The carry story from the harmonization table also shows up exactly as predicted: the parity forward (the futures price) sits above the OptionMetrics index forward, and the gap grows with the time between the option’s expiry and its underlying future’s – June-underlying options show essentially no gap, December-underlying options the largest.
# Forward check: parity-implied ES future price vs OptionMetrics index
# forward, by underlying future. The gap is carry, not disagreement.
fwd = matched.drop_duplicates(["date", "expiration_date"]).copy()
fwd["forward gap (%)"] = (fwd["forwardprice_db"] / fwd["forwardprice_om"] - 1) * 100
fwd.groupby("underlying")["forward gap (%)"].agg(["mean", "count"]).round(3)
| mean | count | |
|---|---|---|
| underlying | ||
| ESH4 | 0.834 | 43 |
| ESM4 | 0.981 | 9 |
| ESU3 | 0.210 | 110 |
| ESU4 | 1.261 | 4 |
| ESZ3 | 0.679 | 183 |
The payoff: the vendor’s series, checked and extended#
With the substitution validated, we can treat the two sources as one
panel. The ~30-day at-the-money implied volatility series below shows
all of it: the OptionMetrics history, the August 2023 sample landing on
top of the vendor’s line (the seam check), and the recent window
extending the series past the vendor’s edge to the present. The gap in
between is simply data we chose not to pull – under the course’s
flat-rate subscription you could fill it for $0.00 by widening
DATABENTO_START_DATE in .env; it costs only pull time.
def atm_30d_iv(df, source_name):
"""Per date: nearest-to-30d expiry, then the strikes nearest the forward."""
rows = []
for date, day in df.dropna(subset=["implied_volatility", "forwardprice"]).groupby("date"):
day = day.assign(days_to_exp=(day["expiration_date"] - date).dt.days)
day = day[day["days_to_exp"] > 7]
if day.empty:
continue
target_exp = day.loc[(day["days_to_exp"] - 30).abs().idxmin(), "expiration_date"]
chain = day[day["expiration_date"] == target_exp]
chain = chain.assign(moneyness=(chain["strike"] - chain["forwardprice"]).abs())
atm = chain.nsmallest(4, "moneyness")
rows.append({"date": date, "atm_iv": atm["implied_volatility"].mean()})
out = pd.DataFrame(rows)
out["source"] = source_name
return out
iv_series = pd.concat(
[
atm_30d_iv(full_wrds, "WRDS OptionMetrics (vendor IV)"),
atm_30d_iv(chain_db, "Databento CME E-mini (computed IV)"),
],
ignore_index=True,
)
fig = go.Figure()
for source_name, grp in iv_series.groupby("source"):
grp = grp.sort_values("date")
# Insert a NaN before each long gap so plotly breaks the line there
# (otherwise the overlap sample and the recent window would be joined
# by a meaningless straight line across the un-pulled months).
gap_starts = grp.loc[grp["date"].diff().dt.days > 45, ["date"]] - pd.Timedelta(days=1)
gap_starts["atm_iv"] = np.nan
grp = pd.concat([grp, gap_starts]).sort_values("date")
fig.add_trace(go.Scatter(x=grp["date"], y=grp["atm_iv"], name=source_name, mode="lines"))
fig.add_vrect(
x0=OVERLAP_START, x1=OVERLAP_END,
fillcolor="lightgray", opacity=0.4, line_width=0,
)
fig.update_layout(
title="~30-day at-the-money S&P 500 implied volatility, stitched across sources",
yaxis_title="Implied volatility (annualized)",
yaxis_tickformat=".0%",
width=1200, height=500,
)
fig.show()
Re-running the machinery on a current chain#
Because the Databento panel matches the prepared schema, the exact same functions from Part 1 run on the most recent trading day. The chain is sparser than OptionMetrics (a daily bar exists only where a contract traded), so we display fewer contracts.
asof_recent = chain_db["date"].max()
recent_chain = chain_db[chain_db["date"] == asof_recent].reset_index(drop=True)
recent_contracts = 10
delta_map_recent, prediction_range_recent = spx_hedging_functions.build_market_predictions(
option_chain=recent_chain,
asof_date=asof_recent,
contracts_to_display=recent_contracts,
interpolation_args=interpolation_args,
)
pareto_recent = spx_hedging_functions.build_pareto_chart(
ticker="S&P 500 (E-mini options via Databento)",
asof_date=asof_recent.strftime("%Y-%m-%d"),
prediction_range=prediction_range_recent,
delta_map=delta_map_recent,
contracts_to_display=recent_contracts,
)
pareto_recent["figure"].show()
Discussion questions#
CME symbols like
EW1N3 C4580do not contain an expiration date –pull_databento_options.series_expirationderives it from the exchange listing rule (n-th Friday / last business day, holiday adjusted). Why do we still pull a one-daydefinitionsnapshot, and what does the test suite do with it? What did the exchange records reveal about weeklies whose Friday falls on Good Friday?Under parent symbology, 58% of the daily bars we downloaded are user-defined spreads (
UD:symbols) rather than outright options – some even print negative “prices”. Why do spreads have negative prices, and what would they do to the parity forward if we forgot to drop them?contract_sizeis 50 for E-mini options but 100 for SPX. If the Part 1 collar program had to be executed in E-mini options for the same portfolio notional, what changes – strikes, number of contracts, or both?Our
midpriceis the last trade of the UTC day, while OptionMetrics marks at 4:00 p.m. The GLBXstatisticsschema carries the exchange’s official daily settlement price for every listed contract, traded or not. What would switching to settlements buy us, and what does it cost in data volume?The IV inversion uses one constant
RISK_FREE_RATEacross a window in which the true short rate moved by more than a percentage point. Using the matched-overlap panel above, how would you measure the IV error this causes, and at which maturities does it bite hardest?