Get BBO of options for an underlying stock at open/close using Databento

get_options_open_close.py

#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
get_options_open_close.py
==============
Get BBO of options for an underlying stock at open/close using Databento.
"""

import databento as db
import pandas as pd
import matplotlib.pyplot as plt


def get_options_data(client, date, time, schema):
    """Fetch options data for a specific timestamp."""
    ts = pd.Timestamp(f"{date} {time}", tz="US/Eastern")
    data = client.timeseries.get_range(
        dataset="OPRA.PILLAR",
        schema=schema,
        stype_in="parent",
        symbols=["AAPL.OPT"],
        start=ts,
        end=ts + pd.Timedelta(minutes=1),
    )
    out_df = data.to_df(tz="US/Eastern")
    print(f"Usage: ${len(out_df) * 80 * 1e-9 * 2:.4f}")    # $2/GB for CBBO data, each row is 80 bytes
    return out_df


def combine_open_close_data(df_open, df_close, date):
    """Combine open and close data with proper column naming."""
    cols = ["bid_px_00", "bid_sz_00", "ask_px_00", "ask_sz_00"]
    
    o = df_open.groupby("symbol")[cols].first()
    o.columns = [f"open_{c.replace('_00', '')}" for c in o.columns]
    
    c = df_close.groupby("symbol")[cols].last()
    c.columns = [f"close_{c.replace('_00', '')}" for c in c.columns]
    
    # Note: This inner join discards illiquid strikes
    result = o.join(c, how="inner")
    result.insert(0, "date", date)
    return result.reset_index().sort_values("symbol").reset_index(drop=True)


def parse_occ_symbol(df):
    """Parse OCC symbols to extract put/call, expiration, and strike."""
    metadata = df['symbol'].str.split().str[-1]

    df['expiration'] = pd.to_datetime('20' + metadata.str[:6], format='%Y%m%d')
    df['put_call'] = metadata.str[6]
    df['strike'] = metadata.str[7:15].astype(int) / 1000
    trade_date = pd.to_datetime(df['date'].iloc[0])
    df['dte'] = (df['expiration'] - trade_date).dt.days
    
    return df


def main():
    
    DATE = "2025-11-19"
    client = db.Historical()
    
    # Snap close at 3:45 PM to avoid vol move on closing imbalance
    df_open = get_options_data(client, DATE, "09:30", "cbbo-1s")
    df_close = get_options_data(client, DATE, "15:45", "cbbo-1m")
    
    result = combine_open_close_data(df_open, df_close, DATE)
    result = parse_occ_symbol(result)
    
    print(result)
    return result


if __name__ == "__main__":
    result = main()

output.log

                     symbol        date  open_bid_px  open_bid_sz  open_ask_px  open_ask_sz  ...  close_ask_px  close_ask_sz  expiration  put_call strike  dte
0     AAPL  251121C00100000  2025-11-19       163.60            3        167.7            3  ...        170.10            50  2025-11-21         C  100.0    2
1     AAPL  251121C00105000  2025-11-19       158.55            1        162.8            1  ...        166.05           170  2025-11-21         C  105.0    2
2     AAPL  251121C00110000  2025-11-19       154.40            1        157.0            1  ...        161.00           209  2025-11-21         C  110.0    2
3     AAPL  251121C00115000  2025-11-19       148.55            1        152.8            1  ...        156.05           164  2025-11-21         C  115.0    2
4     AAPL  251121C00120000  2025-11-19       143.60            3        147.7            3  ...        151.00           165  2025-11-21         C  120.0    2
...                     ...         ...          ...          ...          ...          ...  ...           ...           ...         ...       ...    ...  ...
2509  AAPL  280121P00490000  2025-11-19       220.15            1        228.7            1  ...        222.50             2  2028-01-21         P  490.0  793
2510  AAPL  280121P00500000  2025-11-19       230.15            1        238.7            1  ...        232.50             2  2028-01-21         P  500.0  793
2511  AAPL  280121P00510000  2025-11-19       240.15            1        248.7            1  ...        242.50             2  2028-01-21         P  510.0  793
2512  AAPL  280121P00520000  2025-11-19       250.15            1        258.7            1  ...        252.50             2  2028-01-21         P  520.0  793
2513  AAPL  280121P00530000  2025-11-19       260.15            1        268.7            1  ...        262.50             2  2028-01-21         P  530.0  793

[2514 rows x 14 columns]

output_usage_spending.log

Usage: $0.0203
Usage: $0.0004