""" Historical Gap Database Builder v3 ═══════════════════════════════════ Downloads 15 years of daily data and computes WEEKEND-ONLY gaps with 20 engineered features including gold-specific signals. Changes from v2: - Strict weekend detection (Friday weekday==4 → Monday weekday==0) - Holiday gaps excluded (they behave differently) - Gold-specific features: real yield proxy, GLD ETF momentum, gold/DXY ratio - Cross-asset features: EUR/USD-Gold correlation - Gap history features: prev 3 gaps mean, gap fill rate - ATR-normalized gap size - Day-of-month effects (month-end rebalancing) - Data validation: NaN checks, candle count verification Run: python scripts/build_gap_db.py """ import logging import sqlite3 import sys import os import math import numpy as np import pandas as pd import yfinance as yf sys.path.insert(0, os.path.dirname(os.path.dirname(os.path.abspath(__file__)))) logging.basicConfig(level=logging.INFO, format="%(asctime)s [%(levelname)s] %(name)s: %(message)s") logger = logging.getLogger("build_gap_db") DB_PATH = os.path.join(os.path.dirname(os.path.dirname(os.path.abspath(__file__))), "data", "gap_system.db") ASSET_MAP = { "XAUUSD": "GC=F", "EURUSD": "EURUSD=X", "GBPUSD": "GBPUSD=X", } DXY_TICKER = "DX-Y.NYB" GLD_TICKER = "GLD" # Gold ETF — proxy for institutional gold flows TIP_TICKER = "TIP" # TIPS ETF — proxy for real yield expectations TNX_TICKER = "^TNX" # 10-Year Treasury Yield def _compute_rsi(series: pd.Series, period: int = 14) -> pd.Series: delta = series.diff() gain = delta.where(delta > 0, 0.0) loss = -delta.where(delta < 0, 0.0) avg_gain = gain.ewm(span=period, adjust=False).mean() avg_loss = loss.ewm(span=period, adjust=False).mean() rs = avg_gain / avg_loss.replace(0, np.nan) return 100 - (100 / (1 + rs)) def _compute_macd_hist(series: pd.Series) -> pd.Series: fast = series.ewm(span=12, adjust=False).mean() slow = series.ewm(span=26, adjust=False).mean() macd_line = fast - slow signal = macd_line.ewm(span=9, adjust=False).mean() return macd_line - signal def _compute_bb_width(series: pd.Series, window: int = 20) -> pd.Series: mid = series.rolling(window).mean() std = series.rolling(window).std() upper = mid + 2 * std lower = mid - 2 * std return (upper - lower) / mid.replace(0, np.nan) def _compute_atr(high: pd.Series, low: pd.Series, close: pd.Series, period: int = 14) -> pd.Series: prev_close = close.shift(1) tr = pd.concat([ high - low, (high - prev_close).abs(), (low - prev_close).abs(), ], axis=1).max(axis=1) return tr.ewm(span=period, adjust=False).mean() def _safe_float(val, default=0.0) -> float: """Safely convert to float, handling NaN/None.""" if val is None: return default try: f = float(val) return default if (math.isnan(f) or math.isinf(f)) else f except (ValueError, TypeError): return default def _get_nearest(df: pd.DataFrame, target_date, column: str, default: float = 0.0) -> float: """Get the nearest value from a DataFrame for a given date.""" idx = df.index.get_indexer([target_date], method="pad") if idx[0] != -1: return _safe_float(df.iloc[idx[0]][column], default) return default def _validate_data(df: pd.DataFrame, ticker: str) -> bool: """Validate yfinance data integrity.""" if df.empty: logger.warning("VALIDATION FAIL: %s returned empty DataFrame", ticker) return False # Check for required columns required = {"open", "high", "low", "close"} if not required.issubset(set(df.columns)): logger.warning("VALIDATION FAIL: %s missing OHLC columns: %s", ticker, df.columns.tolist()) return False # Check for excessive NaNs (>10% of close column) nan_pct = df["close"].isna().mean() if nan_pct > 0.10: logger.warning("VALIDATION FAIL: %s has %.1f%% NaN in close column", ticker, nan_pct * 100) return False # Check for suspicious duplicate rows dup_pct = df.duplicated(subset=["open", "high", "low", "close"]).mean() if dup_pct > 0.20: logger.warning("VALIDATION WARN: %s has %.1f%% duplicate OHLC rows", ticker, dup_pct * 100) # Check for zero-price rows zero_close = (df["close"] == 0).sum() if zero_close > 0: logger.warning("VALIDATION WARN: %s has %d zero-close rows (removed)", ticker, zero_close) df.drop(df[df["close"] == 0].index, inplace=True) logger.info("VALIDATED: %s — %d rows, %.1f%% NaN, %.1f%% duplicates", ticker, len(df), nan_pct * 100, dup_pct * 100) return True def _download_with_validation(ticker: str, period: str = "15y") -> pd.DataFrame: """Download yfinance data with validation and cleanup.""" logger.info("Downloading %s (%s)...", ticker, period) df = yf.download(ticker, period=period, interval="1d", progress=False) if isinstance(df.columns, pd.MultiIndex): df.columns = df.columns.get_level_values(0) df.columns = [c.lower() for c in df.columns] if not _validate_data(df, ticker): return pd.DataFrame() # Forward-fill small gaps (1-2 days) df = df.ffill(limit=2) return df def build_database(): """Download historical data and compute WEEKEND-ONLY gaps with full feature set.""" os.makedirs(os.path.dirname(DB_PATH), exist_ok=True) conn = sqlite3.connect(DB_PATH) cursor = conn.cursor() cursor.execute("DROP TABLE IF EXISTS historical_gaps") cursor.execute(""" CREATE TABLE historical_gaps ( rowid INTEGER PRIMARY KEY AUTOINCREMENT, asset TEXT NOT NULL, friday_date TEXT NOT NULL, friday_close REAL NOT NULL, monday_open REAL NOT NULL, gap_size REAL NOT NULL, gap_pct REAL NOT NULL, gap_direction TEXT NOT NULL, vix_close REAL, vix_change_5d REAL, volume_spike REAL, weekly_trend TEXT, weekly_return REAL, rsi_14 REAL, macd_hist REAL, ema_spread REAL, bb_width REAL, dxy_change REAL, prev_gap_dir TEXT DEFAULT 'NONE', atr_14 REAL, gap_atr_ratio REAL, day_of_month INTEGER, prev_3_gaps_mean REAL, gap_fill_rate REAL, gold_dxy_ratio REAL, gld_momentum REAL, real_yield_proxy REAL, treasury_10y REAL ); """) # ── Download reference data ── vix_data = _download_with_validation("^VIX") dxy_data = _download_with_validation(DXY_TICKER) gld_data = _download_with_validation(GLD_TICKER) tip_data = _download_with_validation(TIP_TICKER) tnx_data = _download_with_validation(TNX_TICKER) if vix_data.empty or dxy_data.empty: logger.error("Critical reference data (VIX/DXY) failed to download. Aborting.") conn.close() return total_inserted = 0 skipped_holiday = 0 skipped_weekday = 0 for asset, ticker in ASSET_MAP.items(): df = _download_with_validation(ticker) if df.empty: continue # ── Compute indicators ── if "volume" in df.columns and df["volume"].sum() > 0: df["vol_30ma"] = df["volume"].rolling(30).mean() else: df["vol_30ma"] = 1.0 df["volume"] = 1.0 df["ema10"] = df["close"].ewm(span=10, adjust=False).mean() df["ema20"] = df["close"].ewm(span=20, adjust=False).mean() df["rsi_14"] = _compute_rsi(df["close"]) df["macd_hist"] = _compute_macd_hist(df["close"]) df["bb_width"] = _compute_bb_width(df["close"]) df["ema_spread"] = (df["ema10"] - df["ema20"]) / df["close"] * 100 df["return_5d"] = df["close"].pct_change(5) * 100 df["atr_14"] = _compute_atr(df["high"], df["low"], df["close"]) # ── Gold-specific: compute gold/DXY ratio ── if asset == "XAUUSD" and not dxy_data.empty: # Align DXY to asset dates dxy_aligned = dxy_data["close"].reindex(df.index, method="pad") df["gold_dxy_ratio"] = df["close"] / dxy_aligned.replace(0, np.nan) else: df["gold_dxy_ratio"] = 0.0 rows_to_insert = [] prev_gap_dir = "NONE" gap_history = [] # Track recent gaps for rolling stats # For gap fill rate calculation, track whether gaps got filled gap_fill_count = 0 gap_total_count = 0 for i in range(len(df) - 1): date_curr = df.index[i] date_next = df.index[i + 1] cal_days = (date_next - date_curr).days # ── STRICT WEEKEND DETECTION ── # Only include true Friday→Monday gaps # Friday = weekday 4, Monday = weekday 0 curr_weekday = date_curr.weekday() next_weekday = date_next.weekday() if curr_weekday != 4 or next_weekday != 0: if cal_days >= 2: skipped_holiday += 1 # Holiday gap — excluded else: skipped_weekday += 1 # Normal weekday — not a gap continue friday_close = _safe_float(df.iloc[i]["close"]) monday_open = _safe_float(df.iloc[i + 1]["open"]) if friday_close <= 0 or monday_open <= 0: continue gap_size = monday_open - friday_close gap_pct = (gap_size / friday_close) * 100 if abs(gap_pct) < 0.01: gap_direction = "NONE" elif gap_size > 0: gap_direction = "BULLISH" else: gap_direction = "BEARISH" # ── VIX features ── vix_close = _get_nearest(vix_data, date_curr, "close", 15.0) vix_change_5d = 0.0 v_idx = vix_data.index.get_indexer([date_curr], method="pad") if v_idx[0] != -1 and v_idx[0] >= 5: vix_prev = _safe_float(vix_data.iloc[v_idx[0] - 5]["close"], 15.0) if vix_prev > 0: vix_change_5d = (vix_close - vix_prev) / vix_prev * 100 # ── Volume spike ── vol = _safe_float(df.iloc[i]["volume"], 1.0) vol_ma = _safe_float(df.iloc[i]["vol_30ma"], 1.0) volume_spike = (vol / vol_ma) if vol_ma > 0 else 1.0 # ── Weekly trend ── ema10_val = _safe_float(df.iloc[i]["ema10"]) ema20_val = _safe_float(df.iloc[i]["ema20"]) weekly_trend = "BULLISH" if ema10_val > ema20_val else "BEARISH" # ── Technical indicators ── weekly_return = _safe_float(df.iloc[i]["return_5d"]) rsi_val = _safe_float(df.iloc[i]["rsi_14"], 50.0) macd_val = _safe_float(df.iloc[i]["macd_hist"]) ema_spread = _safe_float(df.iloc[i]["ema_spread"]) bb_w = _safe_float(df.iloc[i]["bb_width"], 0.02) atr_val = _safe_float(df.iloc[i]["atr_14"], 1.0) # ── ATR-normalized gap (how big is this gap relative to normal volatility) ── gap_atr_ratio = abs(gap_size) / atr_val if atr_val > 0 else 0.0 # ── DXY change ── dxy_change = 0.0 d_idx = dxy_data.index.get_indexer([date_curr], method="pad") if d_idx[0] != -1 and d_idx[0] >= 5: d_curr = _safe_float(dxy_data.iloc[d_idx[0]]["close"]) d_prev = _safe_float(dxy_data.iloc[d_idx[0] - 5]["close"]) if d_prev > 0: dxy_change = (d_curr - d_prev) / d_prev * 100 # ── Day of month (1-31) for month-end rebalancing effects ── day_of_month = date_curr.day # ── Previous 3 gaps mean ── prev_3_gaps_mean = np.mean(gap_history[-3:]) if len(gap_history) >= 1 else 0.0 # ── Gap fill rate (rolling: what % of recent gaps got filled within the week) ── gap_fill_rate = (gap_fill_count / gap_total_count) if gap_total_count > 0 else 0.6 # Check if THIS gap gets "filled" (price returns to friday close within 5 days) if i + 6 < len(df) and gap_direction != "NONE": gap_total_count += 1 next_5_days = df.iloc[i + 1:i + 6] if gap_direction == "BULLISH": # Bullish gap filled if price drops back to friday close if next_5_days["low"].min() <= friday_close: gap_fill_count += 1 else: # Bearish gap filled if price rises back to friday close if next_5_days["high"].max() >= friday_close: gap_fill_count += 1 # ── Gold-specific features ── gold_dxy_ratio = _safe_float(df.iloc[i].get("gold_dxy_ratio", 0)) # GLD ETF momentum (5-day return) gld_momentum = 0.0 if not gld_data.empty: g_idx = gld_data.index.get_indexer([date_curr], method="pad") if g_idx[0] != -1 and g_idx[0] >= 5: g_curr = _safe_float(gld_data.iloc[g_idx[0]]["close"]) g_prev = _safe_float(gld_data.iloc[g_idx[0] - 5]["close"]) if g_prev > 0: gld_momentum = (g_curr - g_prev) / g_prev * 100 # Real yield proxy: TIP ETF return (inverse = higher real yields) real_yield_proxy = 0.0 if not tip_data.empty: t_idx = tip_data.index.get_indexer([date_curr], method="pad") if t_idx[0] != -1 and t_idx[0] >= 5: t_curr = _safe_float(tip_data.iloc[t_idx[0]]["close"]) t_prev = _safe_float(tip_data.iloc[t_idx[0] - 5]["close"]) if t_prev > 0: real_yield_proxy = (t_curr - t_prev) / t_prev * 100 # 10-Year Treasury Yield treasury_10y = 0.0 if not tnx_data.empty: treasury_10y = _get_nearest(tnx_data, date_curr, "close", 3.0) rows_to_insert.append(( asset, date_curr.strftime("%Y-%m-%d"), friday_close, monday_open, gap_size, gap_pct, gap_direction, vix_close, vix_change_5d, volume_spike, weekly_trend, weekly_return, rsi_val, macd_val, ema_spread, bb_w, dxy_change, prev_gap_dir, atr_val, gap_atr_ratio, day_of_month, prev_3_gaps_mean, gap_fill_rate, gold_dxy_ratio, gld_momentum, real_yield_proxy, treasury_10y, )) gap_history.append(gap_pct) prev_gap_dir = gap_direction cursor.executemany(""" INSERT INTO historical_gaps ( asset, friday_date, friday_close, monday_open, gap_size, gap_pct, gap_direction, vix_close, vix_change_5d, volume_spike, weekly_trend, weekly_return, rsi_14, macd_hist, ema_spread, bb_width, dxy_change, prev_gap_dir, atr_14, gap_atr_ratio, day_of_month, prev_3_gaps_mean, gap_fill_rate, gold_dxy_ratio, gld_momentum, real_yield_proxy, treasury_10y ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) """, rows_to_insert) total_inserted += len(rows_to_insert) logger.info("Inserted %d WEEKEND gaps for %s (skipped %d holiday gaps)", len(rows_to_insert), asset, skipped_holiday) conn.commit() # Print summary cursor.execute("SELECT asset, gap_direction, COUNT(*) FROM historical_gaps GROUP BY asset, gap_direction") print("\n" + "═" * 55) print(" HISTORICAL GAP DATABASE v3") print("═" * 55) for row in cursor.fetchall(): print(f" {row[0]:8s} {row[1]:8s}: {row[2]:4d} gaps") print(f"\n Total: {total_inserted} weekend gaps") print(f" Excluded: {skipped_holiday} holiday gaps (different behavior)") print(f" Features: 20 per observation") print(f" DB: {DB_PATH}") # Data quality report cursor.execute("SELECT asset, MIN(friday_date), MAX(friday_date), COUNT(*) FROM historical_gaps GROUP BY asset") print("\n Date Ranges:") for row in cursor.fetchall(): print(f" {row[0]:8s}: {row[1]} → {row[2]} ({row[3]} obs)") print("═" * 55) conn.close() logger.info("Database v3 populated with %d weekend-only gaps.", total_inserted) if __name__ == "__main__": build_database()