spb/wp7_uqo Public

1#!/usr/bin/env python32# =============================================================================3# Author: Simon-Pierre Boucher4# Contact: contact@spboucher.ai5# =============================================================================6"""Step 05 — RQ4: Greeks information decay by DTE + OI-concentration price7magnets.89Part A (raw-dependent): aggregate Greeks by days-to-expiry bucket and test10their predictive content for next-day returns and realized variance.11Part B: does the underlying price gravitate toward the maximum-open-interest12strike? Built from the raw stores when available; otherwise the shipped13``price_magnet_data.parquet`` reproduces the summary table (the auxiliary14LPM regression needs raw-only columns and is skipped in fallback mode).1516Inputs : options.duckdb + stock_5min.duckdb (parts A and B, optional),17         data/processed/realized_vol.parquet (part A),18         data/processed/price_magnet_data.parquet (part B fallback)19Outputs: results/rq4_greeks_decay.csv, results/rq4_price_magnet.csv,20         data/processed/price_magnet_data.parquet (part B, raw mode)21"""2223import warnings2425import numpy as np26import pandas as pd2728import _bootstrap  # noqa: F40129from wp7 import config30from wp7.data_io import (RawDataUnavailableError, load_price_magnet,31                         load_realized_vol, open_raw_db)32from wp7.econometrics import add_constant, hc1_tstats, ols, r_squared, standardize3334warnings.filterwarnings('ignore')3536TICKERS = config.RQ4_TICKERS37TICKER_STR = ",".join([f"'{t}'" for t in TICKERS])383940# --------------------------------------------------------------------------41# Part A — Greeks information decay by DTE bucket (raw-dependent)42# --------------------------------------------------------------------------43def greeks_decay() -> None:44    print("\n--- PART A: GREEKS INFORMATION DECAY ---")45    opt_con = open_raw_db("options")46    print("  Extracting Greeks by DTE buckets...")47    greeks_by_dte = opt_con.execute(f"""48        WITH base AS (49            SELECT ticker, trade_date, expiry_date, call_put,50                   (expiry_date - trade_date) AS dte,51                   delta, gamma, vega, theta,52                   open_interest, volume,53                   CASE WHEN bid_iv > 0 AND ask_iv > 0 THEN (bid_iv + ask_iv)/2.054                        WHEN ask_iv > 0 THEN ask_iv ELSE bid_iv END AS mid_iv55            FROM option_chain56            WHERE ticker IN ({TICKER_STR})57              AND ask_price > 058              AND (expiry_date - trade_date) BETWEEN 1 AND 18059        )60        SELECT61            ticker, trade_date,62            CASE63                WHEN dte BETWEEN 1 AND 7 THEN '01_1w'64                WHEN dte BETWEEN 8 AND 14 THEN '02_2w'65                WHEN dte BETWEEN 15 AND 30 THEN '03_1m'66                WHEN dte BETWEEN 31 AND 60 THEN '04_2m'67                WHEN dte BETWEEN 61 AND 90 THEN '05_3m'68                WHEN dte BETWEEN 91 AND 180 THEN '06_6m'69            END AS dte_bucket,7071            AVG(ABS(delta)) AS avg_abs_delta,72            AVG(gamma) AS avg_gamma,73            AVG(vega) AS avg_vega,74            AVG(ABS(theta)) AS avg_abs_theta,75            AVG(mid_iv) AS avg_iv,76            SUM(volume) AS total_vol,77            SUM(open_interest) AS total_oi,7879            SUM(CASE WHEN call_put='c' THEN gamma * open_interest ELSE 0 END) AS call_gamma_oi,80            SUM(CASE WHEN call_put='p' THEN gamma * open_interest ELSE 0 END) AS put_gamma_oi,81            SUM(CASE WHEN call_put='c' THEN delta * open_interest ELSE 0 END) AS call_delta_oi,82            SUM(CASE WHEN call_put='p' THEN delta * open_interest ELSE 0 END) AS put_delta_oi8384        FROM base85        GROUP BY ticker, trade_date, dte_bucket86        HAVING dte_bucket IS NOT NULL AND COUNT(*) >= 587        ORDER BY ticker, trade_date, dte_bucket88    """).fetchdf()89    opt_con.close()90    print(f"  Greeks by DTE: {len(greeks_by_dte):,} rows")9192    rv = load_realized_vol()93    rv = rv[rv['ticker'].isin(TICKERS)]9495    greeks_by_dte['trade_date'] = pd.to_datetime(greeks_by_dte['trade_date'])96    gm = pd.merge(greeks_by_dte,97                  rv[['ticker', 'trade_date', 'ret_1d', 'rv_fwd_1d', 'daily_return']],98                  on=['ticker', 'trade_date'], how='inner')99100    features = ['avg_gamma', 'avg_vega', 'avg_abs_theta', 'avg_iv',101                'call_gamma_oi', 'put_gamma_oi']102    decay_results = []103    for dte_bucket in sorted(gm['dte_bucket'].dropna().unique()):104        sub = gm[gm['dte_bucket'] == dte_bucket].copy()105        for target, target_name in [('ret_1d', 'Return'), ('rv_fwd_1d', 'RV')]:106            data = sub[features + [target]].dropna()107            if len(data) < 100:108                continue109110            X = add_constant(standardize(data[features].values))111            y = data[target].values112            coefs = ols(X, y)113            r2 = r_squared(y, X @ coefs)114            _, t_stats = hc1_tstats(X, y, coefs, len(features))115116            decay_results.append({117                'dte_bucket': dte_bucket,118                'target': target_name,119                'r_squared': r2,120                'n_obs': len(data),121                'n_significant': np.sum(np.abs(t_stats[1:]) > 1.96),122                'gamma_t': t_stats[1],123                'vega_t': t_stats[2],124                'theta_t': t_stats[3],125                'iv_t': t_stats[4],126            })127128    decay_df = pd.DataFrame(decay_results)129    decay_df.to_csv(config.RESULTS_DIR / "rq4_greeks_decay.csv", index=False)130    print("\n  GREEKS PREDICTIVE POWER BY DTE BUCKET:")131    print(decay_df[['dte_bucket', 'target', 'r_squared', 'n_significant', 'n_obs']]132          .to_string(index=False))133134135# --------------------------------------------------------------------------136# Part B — Price magnets137# --------------------------------------------------------------------------138def build_magnet_data() -> pd.DataFrame:139    """Assemble the filtered price-magnet panel from the raw stores."""140    opt_con = open_raw_db("options")141    print("  Extracting OI concentration data...")142    oi_data = opt_con.execute(f"""143        WITH daily_oi AS (144            SELECT ticker, trade_date, strike, call_put,145                   SUM(open_interest) AS oi,146                   SUM(volume) AS vol147            FROM option_chain148            WHERE ticker IN ({TICKER_STR})149              AND (expiry_date - trade_date) BETWEEN 1 AND 30150              AND open_interest > 0151            GROUP BY ticker, trade_date, strike, call_put152        ),153        max_oi AS (154            SELECT ticker, trade_date,155                   MAX(oi) AS max_oi_val156            FROM daily_oi157            GROUP BY ticker, trade_date158        ),159        top_strikes AS (160            SELECT d.ticker, d.trade_date, d.strike, d.call_put, d.oi,161                   m.max_oi_val,162                   ROW_NUMBER() OVER (PARTITION BY d.ticker, d.trade_date163                                      ORDER BY d.oi DESC) AS rn164            FROM daily_oi d165            JOIN max_oi m ON d.ticker = m.ticker AND d.trade_date = m.trade_date166        )167        SELECT ticker, trade_date,168               MAX(CASE WHEN rn = 1 THEN strike END) AS top1_strike,169               MAX(CASE WHEN rn = 1 THEN oi END) AS top1_oi,170               MAX(CASE WHEN rn = 2 THEN strike END) AS top2_strike,171               MAX(CASE WHEN rn = 2 THEN oi END) AS top2_oi,172               SUM(CASE WHEN call_put='c' THEN oi ELSE 0 END) AS total_call_oi,173               SUM(CASE WHEN call_put='p' THEN oi ELSE 0 END) AS total_put_oi,174               SUM(oi) AS total_oi175        FROM top_strikes176        WHERE rn <= 5177        GROUP BY ticker, trade_date178        ORDER BY ticker, trade_date179    """).fetchdf()180    print(f"  OI concentration data: {len(oi_data):,} rows")181    opt_con.close()182183    stk_con = open_raw_db("stocks_5min")184    print("  Loading intraday close prices...")185    intraday_close = stk_con.execute(f"""186        SELECT symbol AS ticker,187               CAST(datetime AS DATE) AS trade_date,188               FIRST(open) AS day_open,189               LAST(close) AS day_close,190               MIN(low) AS day_low,191               MAX(high) AS day_high192        FROM ohlcv193        WHERE symbol IN ({TICKER_STR})194          AND datetime >= '2010-01-01'195        GROUP BY symbol, CAST(datetime AS DATE)196        ORDER BY symbol, trade_date197    """).fetchdf()198    stk_con.close()199    print(f"  Intraday aggregated: {len(intraday_close):,} rows")200201    oi_data['trade_date'] = pd.to_datetime(oi_data['trade_date'])202    intraday_close['trade_date'] = pd.to_datetime(intraday_close['trade_date'])203    magnet = pd.merge(oi_data, intraday_close, on=['ticker', 'trade_date'], how='inner')204205    magnet['dist_open_to_strike'] = np.abs(magnet['day_open'] - magnet['top1_strike']) / magnet['day_open']206    magnet['dist_close_to_strike'] = np.abs(magnet['day_close'] - magnet['top1_strike']) / magnet['day_close']207    magnet['moved_toward_strike'] = (magnet['dist_close_to_strike'] <208                                     magnet['dist_open_to_strike']).astype(int)209    magnet['oi_concentration'] = magnet['top1_oi'] / magnet['total_oi']210211    magnet_clean = magnet[(magnet['dist_open_to_strike'] < 0.10) &212                          (magnet['dist_open_to_strike'] > 0.001)].copy()213214    magnet_clean[['ticker', 'trade_date', 'oi_concentration', 'dist_open_to_strike',215                  'dist_close_to_strike', 'moved_toward_strike']].to_parquet(216        config.PRICE_MAGNET_PARQUET, index=False)217    return magnet_clean218219220def magnet_analysis(magnet_clean: pd.DataFrame, has_raw_columns: bool) -> None:221    """Summary by OI-concentration quintile (+ LPM regression in raw mode)."""222    print(f"\n  Filtered magnet data: {len(magnet_clean):,} rows")223    print(f"  Fraction moved toward max-OI strike: "224          f"{magnet_clean['moved_toward_strike'].mean():.4f}")225226    magnet_clean['oi_conc_quintile'] = pd.qcut(227        magnet_clean['oi_concentration'], 5,228        labels=['Q1_Low', 'Q2', 'Q3', 'Q4', 'Q5_High'], duplicates='drop')229230    magnet_summary = magnet_clean.groupby('oi_conc_quintile', observed=True).agg(231        pct_moved_toward=('moved_toward_strike', 'mean'),232        avg_dist_open=('dist_open_to_strike', 'mean'),233        avg_dist_close=('dist_close_to_strike', 'mean'),234        n_obs=('moved_toward_strike', 'count'),235    ).round(4)236237    print("\n  PRICE MAGNET EFFECT BY OI CONCENTRATION:")238    print(magnet_summary.to_string())239    magnet_summary.to_csv(config.RESULTS_DIR / "rq4_price_magnet.csv")240241    if not has_raw_columns:242        print("\n  [LPM regression skipped — needs 'total_oi' from the raw build]")243        return244245    features = ['oi_concentration', 'dist_open_to_strike', 'total_oi']246    sub = magnet_clean[features + ['moved_toward_strike']].dropna()247    X = add_constant(standardize(sub[features].values))248    y = sub['moved_toward_strike'].values249    coefs = ols(X, y)250    _, t = hc1_tstats(X, y, coefs, len(features))251252    print("\n  LOGISTIC (LPM) REGRESSION: moved_toward_strike ~")253    for i, name in enumerate(['const'] + features):254        sig = "**" if abs(t[i]) > 2.576 else ("*" if abs(t[i]) > 1.96 else "")255        print(f"    {name:25s}: β={coefs[i]:8.5f}, t={t[i]:7.3f} {sig}")256257258def main():259    print("=" * 70)260    print("RQ4: GREEKS INFORMATION DECAY & PRICE MAGNETS")261    print("=" * 70)262    config.ensure_output_dirs()263264    try:265        greeks_decay()266    except RawDataUnavailableError as exc:267        print(f"\n[Part A skipped — raw stores unavailable]\n{exc}")268269    print("\n--- PART B: OI CONCENTRATION AS PRICE MAGNETS ---")270    try:271        magnet_clean = build_magnet_data()272        magnet_analysis(magnet_clean, has_raw_columns=True)273    except RawDataUnavailableError as exc:274        print(f"\n[Raw build skipped — reproducing summary from shipped parquet]\n{exc}\n")275        magnet_analysis(load_price_magnet(), has_raw_columns=False)276277    print("\nRQ4 COMPLETE.")278279280if __name__ == "__main__":281    main()282