""" Standalone Midnight Supabase Caching Worker (Triggered via GitHub Actions) This script is INTENTIONALLY self-contained. It does NOT import backend.app because that would trigger the entire Flask/TensorFlow/FinBERT stack which is too heavy for CI runners. Instead, it inlines only the lightweight scraping logic (yfinance + cross-sectional math) and pushes directly to Supabase. Tasks: 1. Market Movers — yfinance 2-day download for 140 movers 2. General News — GNews API (if key is set) 3. S&P 500 Screener — yfinance 90-day download, cross-sectional alpha, earnings proximity """ import os import sys import time import json import requests import numpy as np import pandas as pd import yfinance as yf from datetime import datetime, timedelta from dotenv import load_dotenv # Load .env for local testing (CI uses GitHub Secrets) load_dotenv() # --- Direct Supabase Client (avoids importing backend.database which chains to app.py) --- from supabase import create_client SUPABASE_URL = os.getenv("SUPABASE_URL") SUPABASE_KEY = os.getenv("SUPABASE_KEY") if not SUPABASE_URL or not SUPABASE_KEY: print("FATAL: SUPABASE_URL and SUPABASE_KEY must be set.") sys.exit(1) supabase = create_client(SUPABASE_URL, SUPABASE_KEY) print("[Worker] Connected to Supabase.") def set_cache(key: str, data: dict): """Push JSON payload to the api_cache table.""" try: payload = { "key": key, "data": data, "updated_at": time.strftime('%Y-%m-%d %H:%M:%S') } supabase.table("api_cache").upsert(payload, on_conflict="key").execute() print(f"[Worker] Cached '{key}' to Supabase.") return True except Exception as e: print(f"[Worker] Cache Error for '{key}': {e}") return False # --- S&P 500 Universe (cleaned — all delisted/acquired tickers replaced) --- SP500_UNIVERSE = [ "AAPL", "ABBV", "ABT", "ACN", "ADBE", "ADI", "ADM", "ADP", "ADSK", "AEE", "AEP", "AES", "AFL", "AIG", "AIZ", "AJG", "AKAM", "ALB", "ALGN", "ALK", "ALL", "ALLE", "AMAT", "AMCR", "AMD", "AME", "AMGN", "AMP", "AMT", "AMZN", "ANET", "AON", "AOS", "APA", "APD", "APH", "APTV", "ARE", "ATO", "AVB", "AVGO", "AVY", "AWK", "AXP", "AZO", "BA", "BAC", "BAX", "BBWI", "BBY", "BDX", "BEN", "BF-B", "BIO", "BIIB", "BK", "BKNG", "BKR", "BLK", "BMY", "BR", "BRK-B", "BRO", "BSX", "BWA", "BXP", "C", "CAG", "CAH", "CARR", "CAT", "CB", "CBOE", "CBRE", "CCI", "CCL", "CDNS", "CDW", "CE", "CEG", "CF", "CFG", "CHD", "CHRW", "CHTR", "CI", "CINF", "CL", "CLX", "CMA", "CMCSA", "CME", "CMG", "CMI", "CMS", "CNC", "CNP", "COF", "COO", "COP", "COST", "CPB", "CPRT", "CPT", "CRL", "CRM", "CSCO", "CSGP", "CSX", "CTAS", "CTRA", "CTSH", "CTVA", "CVS", "CVX", "CZR", "D", "DAL", "DD", "DE", "DG", "DGX", "DHI", "DHR", "DIS", "DLR", "DLTR", "DOV", "DOW", "DPZ", "DRI", "DTE", "DUK", "DVA", "DVN", "DXC", "DXCM", "EA", "EBAY", "ECL", "ED", "EFX", "EIX", "EL", "EMN", "EMR", "ENPH", "EOG", "EPAM", "EQIX", "EQR", "EQT", "ES", "ESS", "ETN", "ETR", "ETSY", "EVRG", "EW", "EXC", "EXPD", "EXPE", "EXR", "F", "FANG", "FAST", "FCX", "FDS", "FDX", "FE", "FFIV", "FIS", "FISV", "FITB", "FMC", "FOX", "FOXA", "FRT", "FTNT", "FTV", "GD", "GE", "GILD", "GIS", "GL", "GLW", "GM", "GNRC", "GOOG", "GOOGL", "GPC", "GPN", "GRMN", "GS", "GWW", "HAL", "HAS", "HBAN", "HCA", "HD", "HOLX", "HON", "HPE", "HPQ", "HRL", "HSIC", "HST", "HSY", "HUM", "HWM", "IBM", "ICE", "IDXX", "IEX", "IFF", "ILMN", "INCY", "INTC", "INTU", "INVH", "IP", "IQV", "IR", "IRM", "ISRG", "IT", "ITW", "IVZ", "J", "JBHT", "JCI", "JKHY", "JNJ", "JPM", "KDP", "KEY", "KEYS", "KHC", "KIM", "KLAC", "KMB", "KMI", "KMX", "KO", "KR", "L", "LDOS", "LEN", "LH", "LHX", "LIN", "LKQ", "LLY", "LMT", "LNC", "LNT", "LOW", "LRCX", "LUMN", "LUV", "LVS", "LW", "LYB", "LYV", "MA", "MAA", "MAR", "MAS", "MCD", "MCHP", "MCK", "MCO", "MDLZ", "MDT", "MET", "META", "MGM", "MHK", "MKC", "MKTX", "MLM", "MMM", "MNST", "MO", "MOH", "MOS", "MPC", "MPWR", "MRK", "MRNA", "MS", "MSCI", "MSFT", "MSI", "MTB", "MTCH", "MTD", "MU", "NCLH", "NDAQ", "NDSN", "NEE", "NEM", "NFLX", "NI", "NKE", "NOC", "NOW", "NRG", "NSC", "NTAP", "NTRS", "NUE", "NVDA", "NVR", "NWL", "NWS", "NWSA", "NXPI", "O", "ODFL", "OGN", "OKE", "OMC", "ON", "ORCL", "ORLY", "OTIS", "OXY", "PAYC", "PAYX", "PCAR", "PCG", "PEG", "PEP", "PFE", "PFG", "PG", "PGR", "PH", "PHM", "PKG", "PLD", "PM", "PNC", "PNR", "PNW", "POOL", "PPG", "PPL", "PRU", "PSA", "PSX", "PTC", "PVH", "PWR", "PYPL", "QCOM", "QRVO", "RCL", "REG", "REGN", "RF", "RHI", "RJF", "RL", "RMD", "ROK", "ROL", "ROP", "ROST", "RSG", "RTX", "SBAC", "SBUX", "SCHW", "SEE", "SHW", "SJM", "SLB", "SNA", "SNPS", "SO", "SPG", "SPGI", "SRE", "STE", "STT", "STX", "STZ", "SWK", "SWKS", "SYF", "SYK", "SYY", "T", "TAP", "TDG", "TDY", "TECH", "TEL", "TER", "TFC", "TFX", "TGT", "TJX", "TMO", "TMUS", "TPR", "TRGP", "TRMB", "TROW", "TRV", "TSCO", "TSLA", "TSN", "TT", "TTWO", "TXN", "TXT", "TYL", "UAL", "UDR", "UHS", "ULTA", "UNH", "UNP", "UPS", "URI", "USB", "V", "VFC", "VICI", "VLO", "VMC", "VNO", "VRSK", "VRSN", "VRTX", "VTR", "VTRS", "VZ", "WAB", "WAT", "WBD", "WDC", "WEC", "WELL", "WFC", "WHR", "WM", "WMB", "WMT", "WRB", "WST", "WTW", "WY", "WYNN", "XEL", "XOM", "XRAY", "XYL", "YUM", "ZBH", "ZBRA", "ZION", "ZTS", # Recent S&P 500 additions (replacements for delisted tickers) "PANW", "ABNB", "CRWD", "DDOG", "SNOW", "PLTR", "COIN", "MELI", "TEAM", "DASH", "TTD", "ZS", "MNDY", "NET", "OKTA", "VEEV", "WDAY", "BILL", "HUBS", "DKNG", "U", "RIVN", "LCID", "SOFI", "HOOD", "NU", "GRAB", "SE", "SHOP", "SPOT", "SNAP", "PINS", "ROKU", "RBLX", "UBER", "LYFT", "HLT", "IHG", "ELV", "ET", "EPD", "MPLX", "BX", "KKR", "APO", "ARES", "CG", "HII", "SMCI", "ARM", "MRVL", # Replacements for 24 delisted tickers "GEV", # GE Vernova (replaced ATVI) "SOLV", # Solventum (replaced SIVB) "VLTO", # Veralto (replaced FRC) "KVUE", # Kenvue (replaced SPLK) "DECK", # Deckers Outdoor (replaced CTLT) "VST", # Vistra (replaced PARA) "GDDY", # GoDaddy (replaced PXD) "AXON", # Axon Enterprise (replaced MRO) "ERIE", # Erie Indemnity (replaced DFS) "APP", # AppLovin (replaced SQ duplicate) "TPL", # Texas Pacific Land (replaced JNPR) "RVTY", # Revvity (replaced PKI) "EG", # Everest Group (replaced RE) "DELL", # Dell Technologies (replaced DISH) "FBIN", # Fortune Brands Innovations (replaced FBHS) "PODD", # Insulet (replaced ANSS) "GEHC", # GE HealthCare (replaced WRK) "NTRA", # Natera (replaced MMC) "DAY", # Dayforce (replaced CDAY) "TOST", # Toast (replaced WBA duplicate) "DOC", # Healthpeak (replaced PEAK) "CPAY", # Corpay (replaced FLT) "HUBB", # Hubbell (replaced K) "SMMT", # Summit Therapeutics (replaced IPG) ] # Remove duplicates SP500_UNIVERSE = list(dict.fromkeys(SP500_UNIVERSE)) # Movers subset (for market movers only — keep this smaller for fast daily updates) MOVERS_UNIVERSE = [ "AAPL", "MSFT", "GOOGL", "AMZN", "NVDA", "META", "BRK-B", "TSLA", "JPM", "V", "UNH", "LLY", "XOM", "JNJ", "WMT", "MA", "PG", "AVGO", "HD", "MRK", "CVX", "COST", "ABBV", "PEP", "KO", "ADBE", "CRM", "AMAT", "BMY", "ACN", "MCD", "T", "CSCO", "TMO", "ABT", "VZ", "DHR", "NKE", "AMGN", "PFE", "LIN", "TXN", "DIS", "NEE", "PM", "UNP", "HON", "RTX", "LOW", "UPS", "GS", "MS", "SCHW", "BLK", "AXP", "C", "CB", "USB", "PNC", "TFC", "CAT", "BA", "GE", "DE", "MMM", "EMR", "ITW", "CMI", "ETN", "PH", "ISRG", "SYK", "MDT", "ZTS", "CI", "HUM", "ELV", "INTU", "MDLZ", "CMCSA", "TJX", "PGR", "COP", "EOG", "OXY", "SLB", "MPC", "PSX", "VLO", "KMI", "WMB", "HAL", "DUK", "SO", "D", "AEP", "SRE", "EXC", "XEL", "ED", "AMT", "CCI", "EQIX", "PSA", "SPG", "DLR", "WELL", "O", "TRGP", "LMT", "NOC", "GD", "PLD", "TGT", "F", "GM", "DAL", "LUV", "MU", "QCOM", "PANW", "SNPS", "CDNS", "KLAC", "LRCX", "BK", "MET", "AIG", "PRU", "TRV", "ALL", "AFL" ] COMPANY_NAMES = { "AAPL": "Apple", "MSFT": "Microsoft", "GOOGL": "Alphabet", "AMZN": "Amazon", "NVDA": "NVIDIA", "META": "Meta", "BRK-B": "Berkshire Hathaway", "TSLA": "Tesla", "JPM": "JPMorgan", "V": "Visa", "UNH": "UnitedHealth", "LLY": "Eli Lilly", "XOM": "ExxonMobil", "JNJ": "Johnson & Johnson", "WMT": "Walmart", "MA": "Mastercard", "PG": "Procter & Gamble", "AVGO": "Broadcom", "HD": "Home Depot", "MRK": "Merck", "CVX": "Chevron", "COST": "Costco", "ABBV": "AbbVie", "PEP": "PepsiCo", "KO": "Coca-Cola", "ADBE": "Adobe", "CRM": "Salesforce", "MCD": "McDonald's", "CSCO": "Cisco", "TMO": "Thermo Fisher", "ABT": "Abbott", "NKE": "Nike", "DIS": "Disney", "CAT": "Caterpillar", "BA": "Boeing", "GE": "GE Aerospace", "NEE": "NextEra Energy", "DUK": "Duke Energy", "SO": "Southern Company", "LMT": "Lockheed Martin", "GS": "Goldman Sachs", "MS": "Morgan Stanley" } # Cross-sectional engine constants (matching the proven backtest) W_VAM = 0.2 # 20% Vol-Adj Momentum, 80% Raw 1M Momentum # ========================================================================= # TASK 1: Market Movers (yfinance only) # ========================================================================= def update_movers(): print("[1/3] Fetching Market Movers via yfinance...") try: data = yf.download(SP500_UNIVERSE, period="2d", group_by="ticker", progress=False, threads=True) stocks = [] for symbol in SP500_UNIVERSE: try: if symbol not in data.columns.get_level_values(0): continue ticker_data = data[symbol] if ticker_data.empty or len(ticker_data) < 2: continue prev_close = float(ticker_data["Close"].iloc[-2]) curr_close = float(ticker_data["Close"].iloc[-1]) volume = int(ticker_data["Volume"].iloc[-1]) if prev_close == 0 or pd.isna(prev_close) or pd.isna(curr_close): continue pct_change = ((curr_close - prev_close) / prev_close) * 100 if volume >= 1_000_000_000: vol_fmt = f"{volume / 1_000_000_000:.2f}B" elif volume >= 1_000_000: vol_fmt = f"{volume / 1_000_000:.2f}M" elif volume >= 1_000: vol_fmt = f"{volume / 1_000:.1f}K" else: vol_fmt = str(volume) stocks.append({ "symbol": symbol, "name": COMPANY_NAMES.get(symbol, symbol), "price": f"${curr_close:.2f}", "change": round(pct_change, 2), "raw_change": round(pct_change, 2), "volume": volume, "volume_fmt": vol_fmt }) except Exception as ex: continue if stocks: gainers = sorted(stocks, key=lambda x: x["change"], reverse=True)[:5] losers = sorted(stocks, key=lambda x: x["change"])[:5] active = sorted(stocks, key=lambda x: x["volume"], reverse=True)[:5] for item in gainers + losers + active: item.pop("volume", None) cache_data = {"gainers": gainers, "losers": losers, "active": active} set_cache("market-movers", cache_data) print(f" ✅ {len(stocks)} stocks processed.") else: print(" ⚠️ No stock data returned.") except Exception as e: print(f" ❌ Movers Error: {e}") # ========================================================================= # TASK 2: General News (lightweight GNews API) # ========================================================================= def update_news(): print("[2/3] Fetching General Market News via GNews...") gnews_key = os.getenv("GNEWS_API_KEY1") or os.getenv("GNEWS_API_KEY2") if not gnews_key: print(" ⚠️ No GNEWS_API_KEY set, skipping news update.") return try: all_articles = [] for page in range(1, 4): url = f"https://gnews.io/api/v4/search?q=stock+market&lang=en&sortby=publishedAt&token={gnews_key}&max=10&page={page}" res = requests.get(url, timeout=15) if res.status_code == 200: all_articles.extend(res.json().get("articles", [])) else: print(f" ❌ GNews returned {res.status_code} on page {page}") break time.sleep(1.5) news_list = [] for art in all_articles[:30]: pub_str = datetime.now().strftime('%Y-%m-%d') try: dt = datetime.strptime(art.get('publishedAt', ''), "%Y-%m-%dT%H:%M:%SZ") pub_str = dt.strftime('%Y-%m-%d') except: pass news_list.append({ "title": art.get("title", ""), "link": art.get("url", ""), "image": art.get("image", ""), "publisher": art.get("source", {}).get("name", "GNews"), "published": pub_str, "sentiment": 0.0 }) if news_list: set_cache("general-news", {"news": news_list}) print(f" ✅ {len(news_list)} articles cached.") else: print(" ⚠️ No articles found.") except Exception as e: print(f" ❌ News Error: {e}") # ========================================================================= # TASK 3: S&P 500 Cross-Sectional Screener (pure yfinance + math) # ========================================================================= def update_screener(): """ Computes the cross-sectional alpha score for the entire S&P 500 universe using only yfinance data. No GARCH, no FinBERT, no TwelveData. Alpha = W_VAM * Z(Vol_Adj_Mom) + (1-W_VAM) * Z(Mom_1M) Signal derived from alpha score thresholds. Volatility regime from simple percentile ranking. Earnings proximity from yfinance calendar. """ print("[3/3] Computing S&P 500 Cross-Sectional Screener...") try: # Step 1: Download 90 days of price data — use group_by="ticker" (same as movers) print(" Downloading price data for ~500 tickers...") raw = yf.download(SP500_UNIVERSE, period="90d", group_by="ticker", progress=False, threads=True) # Build a clean close price DataFrame, ticker by ticker (proven pattern from movers) close_dict = {} for ticker in SP500_UNIVERSE: try: if ticker not in raw.columns.get_level_values(0): continue ticker_data = raw[ticker] if "Close" not in ticker_data.columns: continue series = ticker_data["Close"].dropna() if len(series) >= 63: # Need 63 days for 3-month momentum close_dict[ticker] = series except: continue if len(close_dict) < 10: print(f" ❌ Only {len(close_dict)} tickers with sufficient data, aborting screener.") return df_close = pd.DataFrame(close_dict).ffill() valid_tickers = list(close_dict.keys()) print(f" {len(valid_tickers)} tickers have sufficient data.") # Step 2: Compute factors (identical to proven backtest) daily_ret = df_close.pct_change() mom_1m = (df_close / df_close.shift(21)) - 1 # 1-month momentum mom_3m = (df_close / df_close.shift(61)) - 1 # 3-month momentum vol_1m = daily_ret.rolling(window=21).std() * np.sqrt(252) # Annualized vol vol_adj_mom = mom_3m / vol_1m # Vol-adjusted momentum # Get latest values idx = -1 factors = [] for ticker in valid_tickers: try: m1 = float(mom_1m[ticker].iloc[idx]) vam = float(vol_adj_mom[ticker].iloc[idx]) v1m = float(vol_1m[ticker].iloc[idx]) price = float(df_close[ticker].iloc[idx]) if pd.isna(m1) or pd.isna(vam) or pd.isna(v1m) or pd.isna(price): continue if price <= 0: continue factors.append({ "ticker": ticker, "price": round(price, 2), "mom_1m": m1, "vol_adj_mom": vam, "vol_1m": v1m }) except: continue if not factors: print(" ❌ No valid factors computed.") return df = pd.DataFrame(factors) print(f" Computing alpha scores for {len(df)} stocks...") # Step 3: Z-Score factors cross-sectionally for col in ['mom_1m', 'vol_adj_mom']: mean = df[col].mean() std = df[col].std() df[f'z_{col}'] = (df[col] - mean) / std if std > 1e-8 else 0.0 # Step 4: Alpha Score (proven formula) df['alpha_score'] = (W_VAM * df['z_vol_adj_mom']) + ((1 - W_VAM) * df['z_mom_1m']) # Step 5: Signal from alpha score def derive_signal(alpha): if alpha >= 1.5: return "Strong Buy" elif alpha >= 0.5: return "Buy" elif alpha <= -1.5: return "Strong Sell" elif alpha <= -0.5: return "Sell" else: return "Hold" df['signal'] = df['alpha_score'].apply(derive_signal) # Step 6: Volatility regime from percentiles p25 = df['vol_1m'].quantile(0.25) p75 = df['vol_1m'].quantile(0.75) p90 = df['vol_1m'].quantile(0.90) def derive_vol_regime(vol): if vol < p25: return "Low" elif vol < p75: return "Normal" elif vol < p90: return "High" else: return "Extreme" df['volatility_regime'] = df['vol_1m'].apply(derive_vol_regime) # Step 7: Earnings proximity (batch fetch) print(" Fetching earnings dates...") earnings_map = {} today = datetime.now() # Fetch earnings in batches to avoid rate limits for ticker in df['ticker'].tolist(): try: info = yf.Ticker(ticker) cal = info.calendar if cal is not None and not (isinstance(cal, pd.DataFrame) and cal.empty): if isinstance(cal, dict): ed = cal.get('Earnings Date') if ed: if isinstance(ed, list) and len(ed) > 0: earn_date = pd.to_datetime(ed[0]) else: earn_date = pd.to_datetime(ed) days_until = (earn_date - pd.Timestamp(today)).days if days_until is not None and 0 <= days_until <= 7: earnings_map[ticker] = "This Week" elif days_until is not None and 0 <= days_until <= 30: earnings_map[ticker] = "This Month" except: pass df['earnings'] = df['ticker'].map(lambda t: earnings_map.get(t, "Any")) # Step 8: Sort by alpha score (best stocks first) and format df = df.sort_values('alpha_score', ascending=False) stocks_list = [] for _, row in df.iterrows(): stocks_list.append({ "ticker": row['ticker'], "price": row['price'], "signal": row['signal'], "volatility_regime": row['volatility_regime'], "earnings": row['earnings'], "alpha_score": round(float(row['alpha_score']), 3), "mom_1m": round(float(row['mom_1m'] * 100), 2) # Added as percentage }) set_cache("screener", {"stocks": stocks_list}) print(f" ✅ Screener cached: {len(stocks_list)} stocks ranked by alpha.") # Print top 10 for logging print("\n --- TOP 10 BY ALPHA ---") for s in stocks_list[:10]: print(f" {s['ticker']:>6} α={s['alpha_score']:+.3f} ${s['price']:.2f} {s['signal']}") except Exception as e: import traceback print(f" ❌ Screener Error: {e}") traceback.print_exc() # ========================================================================= # TASK 4: Strategy Signals (V53 / Active Strategy) # ========================================================================= def update_strategy_signals(): print("[4/4] Computing Strategy Signals...") try: # Temporarily remove local cache file so the worker forces a fresh compute cache_file = os.path.join(os.path.dirname(__file__), "strategy_cache.json") if os.path.exists(cache_file): try: os.remove(cache_file) except: pass from backend.strategy_signals import get_strategy_live_signals signals = get_strategy_live_signals() if signals and "picks" in signals: set_cache("strategy_live_signals", signals) print(f" ✅ Strategy Signals cached. Found {len(signals['picks'])} picks.") else: print(" ⚠️ Strategy Signals returned empty or invalid data.") except Exception as e: import traceback print(f" ❌ Strategy Signals Error: {e}") traceback.print_exc() # ========================================================================= # MAIN # ========================================================================= def main(): print("=" * 60) print("🚀 NIGHTLY SUPABASE CACHE GENERATION (GitHub Actions)") print(f" Time: {datetime.utcnow().strftime('%Y-%m-%d %H:%M:%S')} UTC") print(f" S&P 500 Universe: {len(SP500_UNIVERSE)} tickers") print("=" * 60) update_movers() update_news() update_screener() update_strategy_signals() print("\n" + "=" * 60) print("✅ ALL TASKS COMPLETE") print("=" * 60) if __name__ == "__main__": main()