spb/wp7_uqo Public

1#!/usr/bin/env python32# =============================================================================3# Author: Simon-Pierre Boucher4# Contact: contact@spboucher.ai5# =============================================================================6"""Step 01 — Data extraction pipeline (raw → processed).78Extracts option-implied surface features and 5-minute realized volatility9from the raw DuckDB stores, then merges them into the master analysis panel.1011Inputs (raw, external — see data/raw/README.md):12    options.duckdb, stock_5min.duckdb, etf_5min.duckdb, index_5min.duckdb1314Outputs (data/processed/):15    options_features.parquet, realized_vol.parquet, merged_options_rv.parquet1617This step is the only producer of the processed datasets; every other script18runs from its outputs. If the raw stores are unavailable the script exits19with a clear message and the shipped parquets remain authoritative.20"""2122import sys23import warnings2425import pandas as pd2627import _bootstrap  # noqa: F40128from wp7 import config29from wp7.data_io import RawDataUnavailableError, open_raw_db, raw_db_path3031warnings.filterwarnings('ignore')323334# --------------------------------------------------------------------------35# 1A. Option-surface features per ticker per day36# --------------------------------------------------------------------------37def extract_options_features(con, tickers, batch_label=""):38    """Aggregate the daily option chain into ten implied-moment features."""39    ticker_str = ",".join([f"'{t}'" for t in tickers])40    query = f"""41    WITH base AS (42        SELECT ticker, trade_date, strike, expiry_date, call_put,43               bid_price, ask_price, bid_iv, ask_iv,44               open_interest, volume, delta, gamma, vega, theta, rho,45               (expiry_date - trade_date) AS dte,46               (bid_price + ask_price) / 2.0 AS mid_price,47               CASE WHEN bid_iv > 0 AND ask_iv > 0 THEN (bid_iv + ask_iv) / 2.048                    WHEN ask_iv > 0 THEN ask_iv49                    ELSE bid_iv END AS mid_iv50        FROM option_chain51        WHERE ticker IN ({ticker_str})52          AND (expiry_date - trade_date) BETWEEN 7 AND 18053          AND ask_price > 054    )55    SELECT56        ticker,57        trade_date,5859        -- Implied Volatility: ATM (|delta| closest to 0.5)60        AVG(CASE WHEN call_put='c' AND ABS(delta - 0.5) < 0.1 AND dte BETWEEN 20 AND 4061                 THEN mid_iv END) AS iv_atm_30d,62        AVG(CASE WHEN call_put='c' AND ABS(delta - 0.5) < 0.1 AND dte BETWEEN 80 AND 10063                 THEN mid_iv END) AS iv_atm_90d,6465        -- IV Term Structure slope (90d - 30d)66        AVG(CASE WHEN call_put='c' AND ABS(delta - 0.5) < 0.1 AND dte BETWEEN 80 AND 10067                 THEN mid_iv END) -68        AVG(CASE WHEN call_put='c' AND ABS(delta - 0.5) < 0.1 AND dte BETWEEN 20 AND 4069                 THEN mid_iv END) AS iv_term_slope,7071        -- Volatility Skew (25d put IV - 25d call IV, ~30d expiry)72        AVG(CASE WHEN call_put='p' AND ABS(delta + 0.25) < 0.07 AND dte BETWEEN 20 AND 4073                 THEN mid_iv END) -74        AVG(CASE WHEN call_put='c' AND ABS(delta - 0.25) < 0.07 AND dte BETWEEN 20 AND 4075                 THEN mid_iv END) AS iv_skew_25d,7677        -- Implied Skewness proxy (OTM put IV - OTM call IV normalized by ATM)78        (AVG(CASE WHEN call_put='p' AND ABS(delta + 0.10) < 0.05 AND dte BETWEEN 20 AND 4079                  THEN mid_iv END) -80         AVG(CASE WHEN call_put='c' AND ABS(delta - 0.10) < 0.05 AND dte BETWEEN 20 AND 4081                  THEN mid_iv END)) /82        NULLIF(AVG(CASE WHEN call_put='c' AND ABS(delta - 0.5) < 0.1 AND dte BETWEEN 20 AND 4083                        THEN mid_iv END), 0) AS implied_skewness,8485        -- Implied Kurtosis proxy (wing IV avg / ATM IV)86        (AVG(CASE WHEN ABS(delta) < 0.15 AND ABS(delta) > 0.03 AND dte BETWEEN 20 AND 4087                  THEN mid_iv END)) /88        NULLIF(AVG(CASE WHEN call_put='c' AND ABS(delta - 0.5) < 0.1 AND dte BETWEEN 20 AND 4089                        THEN mid_iv END), 0) AS implied_kurtosis_proxy,9091        -- Put-Call Volume Ratio92        SUM(CASE WHEN call_put='p' THEN volume ELSE 0 END)::DOUBLE /93        NULLIF(SUM(CASE WHEN call_put='c' THEN volume ELSE 0 END), 0) AS pc_volume_ratio,9495        -- Put-Call OI Ratio96        SUM(CASE WHEN call_put='p' THEN open_interest ELSE 0 END)::DOUBLE /97        NULLIF(SUM(CASE WHEN call_put='c' THEN open_interest ELSE 0 END), 0) AS pc_oi_ratio,9899        -- Aggregate Greeks100        SUM(CASE WHEN call_put='c' THEN gamma * open_interest ELSE 0 END) -101        SUM(CASE WHEN call_put='p' THEN gamma * open_interest ELSE 0 END) AS net_gamma_exposure,102103        AVG(CASE WHEN dte BETWEEN 20 AND 40 THEN vega END) AS avg_vega_30d,104        AVG(CASE WHEN dte BETWEEN 20 AND 40 THEN theta END) AS avg_theta_30d,105106        -- Total volume and OI107        SUM(volume) AS total_option_volume,108        SUM(open_interest) AS total_oi,109110        -- Max OI strike (price magnet proxy)111        (SELECT b2.strike FROM base b2112         WHERE b2.ticker = base.ticker AND b2.trade_date = base.trade_date113         GROUP BY b2.strike ORDER BY SUM(b2.open_interest) DESC LIMIT 1) AS max_oi_strike114115    FROM base116    GROUP BY ticker, trade_date117    HAVING COUNT(*) >= 10118    ORDER BY ticker, trade_date119    """120    print(f"  Extracting options features for {batch_label} ({len(tickers)} tickers)...")121    df = con.execute(query).fetchdf()122    print(f"  -> {len(df):,} rows extracted")123    return df124125126# --------------------------------------------------------------------------127# 1B. Realized volatility from 5-minute OHLCV128# --------------------------------------------------------------------------129def compute_realized_vol(db_name, table_name, id_col, tickers, label=""):130    """Daily realized variance, skewness and kurtosis from 5-minute bars."""131    ticker_str = ",".join([f"'{t}'" for t in tickers])132    con = open_raw_db(db_name)133134    query = f"""135    WITH bars AS (136        SELECT {id_col} AS ticker,137               CAST(datetime AS DATE) AS trade_date,138               datetime,139               close,140               LAG(close) OVER (PARTITION BY {id_col} ORDER BY datetime) AS prev_close141        FROM {table_name}142        WHERE {id_col} IN ({ticker_str})143    ),144    returns AS (145        SELECT ticker, trade_date, datetime,146               LN(close / NULLIF(prev_close, 0)) AS log_ret147        FROM bars148        WHERE prev_close IS NOT NULL AND prev_close > 0 AND close > 0149    )150    SELECT151        ticker,152        trade_date,153        SUM(log_ret * log_ret) AS rv_daily,154        SQRT(SUM(log_ret * log_ret)) AS rvol_daily,155        COUNT(*) AS n_obs,156        FIRST(log_ret) AS open_ret,157        SUM(log_ret) AS daily_return,158        (SQRT(COUNT(*)) * SUM(POWER(log_ret, 3))) /159            NULLIF(POWER(SUM(log_ret * log_ret), 1.5), 0) AS realized_skew,160        (COUNT(*) * SUM(POWER(log_ret, 4))) /161            NULLIF(POWER(SUM(log_ret * log_ret), 2), 0) AS realized_kurt162    FROM returns163    GROUP BY ticker, trade_date164    HAVING COUNT(*) >= 20165    ORDER BY ticker, trade_date166    """167    print(f"  Computing realized vol for {label} ({len(tickers)} tickers)...")168    df = con.execute(query).fetchdf()169    print(f"  -> {len(df):,} rows")170    con.close()171    return df172173174# --------------------------------------------------------------------------175# 1C. Weekly aggregates and forward targets176# --------------------------------------------------------------------------177def compute_weekly_rv(rv_df):178    """Rolling weekly RV plus forward return / forward RV targets."""179    rv_df = rv_df.sort_values(['ticker', 'trade_date'])180    rv_df['rv_weekly'] = rv_df.groupby('ticker')['rv_daily'].transform(181        lambda x: x.rolling(5, min_periods=3).sum()182    )183    rv_df['ret_1d'] = rv_df.groupby('ticker')['daily_return'].shift(-1)184    rv_df['ret_5d'] = rv_df.groupby('ticker')['daily_return'].transform(185        lambda x: x.shift(-1).rolling(5, min_periods=3).sum()186    )187    rv_df['rv_fwd_1d'] = rv_df.groupby('ticker')['rv_daily'].shift(-1)188    rv_df['rv_fwd_5d'] = rv_df.groupby('ticker')['rv_daily'].transform(189        lambda x: x.shift(-1).rolling(5, min_periods=3).sum()190    )191    return rv_df192193194# --------------------------------------------------------------------------195# 1D. HAR-RV components196# --------------------------------------------------------------------------197def compute_har_components(rv_df):198    """Daily lag, weekly mean and monthly mean of realized variance."""199    rv_df = rv_df.sort_values(['ticker', 'trade_date'])200    rv_df['rv_lag1'] = rv_df.groupby('ticker')['rv_daily'].shift(1)201    rv_df['rv_w'] = rv_df.groupby('ticker')['rv_daily'].transform(202        lambda x: x.rolling(5, min_periods=3).mean()203    )204    rv_df['rv_m'] = rv_df.groupby('ticker')['rv_daily'].transform(205        lambda x: x.rolling(22, min_periods=10).mean()206    )207    return rv_df208209210def main():211    print("=" * 70)212    print("STEP 1: EXTRACTING OPTIONS-IMPLIED MOMENTS")213    print("=" * 70)214    config.ensure_output_dirs()215216    # Options features217    opt_con = open_raw_db("options")218    opt_stocks = extract_options_features(opt_con, config.MAJOR_TICKERS, "Major Stocks")219    opt_etfs = extract_options_features(opt_con, config.ETF_TICKERS, "ETFs")220    opt_idx = extract_options_features(opt_con, config.INDEX_OPTION_TICKERS, "Indices")221    opt_con.close()222223    opt_all = pd.concat([opt_stocks, opt_etfs, opt_idx], ignore_index=True)224    opt_all.to_parquet(config.OPTIONS_FEATURES_PARQUET, index=False)225    print(f"\nOptions features saved: {len(opt_all):,} rows")226227    # Realized volatility per asset class228    rv_stocks = compute_realized_vol("stocks_5min", "ohlcv", "symbol",229                                     config.MAJOR_TICKERS, "Stocks 5min")230    rv_etfs = compute_realized_vol("etfs_5min", "ohlcv", "symbol",231                                   config.ETF_TICKERS, "ETFs 5min")232    rv_idx = compute_realized_vol("indices_5min", "ohlcv", "symbol",233                                  config.INDEX_SYMBOLS, "Indices 5min")234235    rv_all = pd.concat([rv_stocks, rv_etfs, rv_idx], ignore_index=True)236    rv_all = compute_weekly_rv(rv_all)237    rv_all = compute_har_components(rv_all)238    rv_all.to_parquet(config.REALIZED_VOL_PARQUET, index=False)239    print(f"Realized vol saved: {len(rv_all):,} rows")240241    # Merge options + realized vol into the master panel242    merged = pd.merge(opt_all, rv_all, on=['ticker', 'trade_date'], how='inner')243    merged = merged.sort_values(['ticker', 'trade_date']).reset_index(drop=True)244    merged.to_parquet(config.MERGED_PARQUET, index=False)245    print(f"\nMerged dataset saved: {len(merged):,} rows")246    print(f"Tickers: {merged['ticker'].nunique()}")247    print(f"Date range: {merged['trade_date'].min()} to {merged['trade_date'].max()}")248    print(f"Columns: {list(merged.columns)}")249    print("\nStep 1 COMPLETE.")250251252if __name__ == "__main__":253    try:254        main()255    except RawDataUnavailableError as exc:256        print(f"\n[SKIPPED] {exc}", file=sys.stderr)257        print(f"(expected stores: {[str(raw_db_path(n)) for n in config.RAW_DB_FILES]})",258              file=sys.stderr)259        sys.exit(2)260