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 OPRA#
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 sells the OPRA feed (the consolidated tape for all US listed options) on demand, which lets us extend the panel to the present. Two details matter:
Which root? Our WRDS pull filtered
am_settlement = 0(PM-settled). The classic SPX monthly root is AM-settled; the PM-settled family (Mon/Wed/Fri weeklys, end-of-month, PM monthlies) trades under the SPXW root. So the continuation of our panel is parent symbolSPXW.OPTonOPRA.PILLAR.Cost. OPRA is the largest US market-data feed. The pull script (
pull_databento_options.py) prices every query with a freemetadata.get_costcall and refuses to download if the estimate exceedsDATABENTO_MAX_COST. Never bypass that guard: the full SPXW daily-bar chain costs roughly $0.18 per calendar day pulled.
import pull_databento_options
chain_db = pull_databento_options.load_spx_options_databento()
wrds_max_date = option_chain["date"].max() # single asof-date slice loaded above
full_wrds = pull_options_data.load_all_optm_data(
start_date=START_DATE, end_date=END_DATE
)
print(f"WRDS OptionMetrics coverage ends: {full_wrds['date'].max():%Y-%m-%d}")
print(f"Databento OPRA coverage: {chain_db['date'].min():%Y-%m-%d} "
f"to {chain_db['date'].max():%Y-%m-%d}")
print(f"Rows in the Databento panel: {len(chain_db):,}")
WRDS OptionMetrics coverage ends: 2023-08-31
Databento OPRA coverage: 2026-07-24 to 2026-07-28
Rows in the Databento panel: 22,184
# The lag exhibit: daily SPXW volume from each source, with the vendor gap shaded.
import plotly.graph_objects as go
vol_wrds = full_wrds.groupby("date")["volume"].sum()
vol_db = chain_db.groupby("date")["volume"].sum()
fig = go.Figure()
fig.add_trace(go.Scatter(x=vol_wrds.index, y=vol_wrds.values, name="WRDS OptionMetrics"))
fig.add_trace(go.Scatter(x=vol_db.index, y=vol_db.values, name="Databento OPRA"))
fig.add_vrect(
x0=vol_wrds.index.max(), x1=vol_db.index.min(),
fillcolor="lightgray", opacity=0.4, line_width=0,
annotation_text="OptionMetrics release lag (no vendor data)",
annotation_position="top left",
)
fig.update_layout(
title="Daily PM-settled SPX (SPXW) 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 OPRA (ours) |
|---|---|---|
|
EOD NBBO quotes |
NaN (daily bars carry no quotes) |
|
NBBO midpoint |
daily close (noisier on illiquid strikes) |
|
vendor |
implied from put-call parity |
|
vendor-computed |
computed with our |
|
OptionMetrics id |
Databento |
The parity trick is worth internalizing: for any strike with both a call and a put close, European put-call parity gives
\[ F = K + e^{rT}\,(C - P), \]
so the chain itself pins down the forward – no external spot or forward
source needed. 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), and the greeks follow from
the same functions you unit-tested in black_scholes.py.
# 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: 93.8% of rows
| strike | midprice | implied_volatility | delta | forwardprice | |
|---|---|---|---|---|---|
| count | 22184.000000 | 22184.000000 | 20816.000000 | 20816.000000 | 22184.000000 |
| mean | 7172.591507 | 90.515457 | 0.214272 | -0.018049 | 7443.303283 |
| std | 713.201587 | 181.250565 | 0.150688 | 0.383432 | 38.608942 |
| min | 1400.000000 | 0.010000 | 0.029387 | -0.997853 | 7410.526497 |
| 25% | 7000.000000 | 5.300000 | 0.137407 | -0.241525 | 7419.978608 |
| 50% | 7350.000000 | 40.000000 | 0.171047 | -0.016163 | 7434.789456 |
| 75% | 7545.000000 | 114.400000 | 0.230471 | 0.161756 | 7449.277866 |
| max | 12400.000000 | 4835.780000 | 4.885579 | 0.999856 | 7691.893209 |
# Stitched ~30-day at-the-money implied volatility across the seam.
def atm_30d_iv(df, source_name):
"""Per date: nearest-to-30d expiry, then the strike 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 OPRA (computed IV)"),
],
ignore_index=True,
)
fig = go.Figure()
for source_name, grp in iv_series.groupby("source"):
fig.add_trace(go.Scatter(x=grp["date"], y=grp["atm_iv"], name=source_name, mode="lines"))
fig.update_layout(
title="~30-day at-the-money SPX 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="SPX (SPXW 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#
Why does the definition-schema snapshot join on
instrument_idrather than on the OSI symbol string? Under what circumstances could the OSI parse and the definition record disagree?A full-chain
bbo-1mpull over our window would deliver true NBBO quotes. Useclient.metadata.get_costto price it. Why did we accept daily closes instead, and what does that choice do to the implied volatilities on illiquid strikes?If you paid for one day of overlap between OptionMetrics and OPRA, how would you validate our computed IVs against the vendor’s? Which rows would you expect to disagree most?