#!/usr/bin/env python3 """ Populate DuckDB with 2 years of OHLCV data, technical indicators, and fundamentals for a curated list of tickers via yfinance. Usage: python data/generate_db.py """ import os from concurrent.futures import ThreadPoolExecutor, as_completed import duckdb import numpy as np import pandas as pd import yfinance as yf DB_PATH = os.path.join(os.path.dirname(os.path.abspath(__file__)), "finsight.db") TICKERS = [ # Technology "AAPL", "MSFT", "NVDA", "GOOGL", "META", "AMZN", "TSLA", "AMD", "INTC", "CRM", # Finance "JPM", "GS", "BAC", "V", "MA", # Healthcare "JNJ", "PFE", "UNH", "ABBV", # Energy "XOM", "CVX", # Consumer "WMT", "KO", "MCD", # Market benchmark "SPY", ] # --------------------------------------------------------------------------- # Technical indicator helpers # --------------------------------------------------------------------------- def calc_rsi(series: pd.Series, period: int = 14) -> pd.Series: delta = series.diff() gain = delta.clip(lower=0) loss = -delta.clip(upper=0) avg_gain = gain.ewm(com=period - 1, min_periods=period).mean() avg_loss = loss.ewm(com=period - 1, min_periods=period).mean() rs = avg_gain / avg_loss return 100 - (100 / (1 + rs)) def calc_macd( series: pd.Series, fast: int = 12, slow: int = 26, signal: int = 9 ) -> tuple[pd.Series, pd.Series, pd.Series]: ema_fast = series.ewm(span=fast, adjust=False).mean() ema_slow = series.ewm(span=slow, adjust=False).mean() macd = ema_fast - ema_slow macd_signal = macd.ewm(span=signal, adjust=False).mean() return macd, macd_signal, macd - macd_signal # --------------------------------------------------------------------------- # Schema # --------------------------------------------------------------------------- def create_tables(con: duckdb.DuckDBPyConnection) -> None: con.execute(""" CREATE TABLE IF NOT EXISTS prices ( ticker VARCHAR, date DATE, open DOUBLE, high DOUBLE, low DOUBLE, close DOUBLE, volume BIGINT, PRIMARY KEY (ticker, date) ) """) con.execute(""" CREATE TABLE IF NOT EXISTS indicators ( ticker VARCHAR, date DATE, rsi_14 DOUBLE, macd DOUBLE, macd_signal DOUBLE, macd_hist DOUBLE, sma_20 DOUBLE, sma_50 DOUBLE, sma_200 DOUBLE, PRIMARY KEY (ticker, date) ) """) con.execute(""" CREATE TABLE IF NOT EXISTS fundamentals ( ticker VARCHAR PRIMARY KEY, name VARCHAR, sector VARCHAR, industry VARCHAR, market_cap BIGINT, pe_ratio DOUBLE, forward_pe DOUBLE, eps DOUBLE, dividend_yield DOUBLE, beta DOUBLE, week_52_high DOUBLE, week_52_low DOUBLE ) """) # --------------------------------------------------------------------------- # Price + indicator ingestion (uses batch download) # --------------------------------------------------------------------------- def _normalize_dates(index: pd.DatetimeIndex) -> list[str]: """Return ISO date strings, stripping any timezone info.""" if index.tz is not None: index = index.tz_convert(None) return index.strftime("%Y-%m-%d").tolist() def ingest_prices_and_indicators(con: duckdb.DuckDBPyConnection, raw: pd.DataFrame) -> list[str]: """ Given the multi-ticker DataFrame from yf.download(), compute indicators and write prices + indicators tables. Returns list of successfully ingested tickers. """ # yf.download with multiple tickers returns a MultiIndex columns: (field, ticker) # With a single ticker it's flat — normalise to MultiIndex for uniform handling. if not isinstance(raw.columns, pd.MultiIndex): return [] # Detect which level holds ticker symbols (varies across yfinance versions) level0 = raw.columns.get_level_values(0).unique().tolist() ticker_level = 0 if any(t in TICKERS for t in level0) else 1 ingested = [] tickers_in_data = raw.columns.get_level_values(ticker_level).unique().tolist() for ticker in tickers_in_data: try: hist = raw.xs(ticker, axis=1, level=ticker_level).dropna(how="all") if hist.empty: print(f" {ticker} — no price data, skipped.") continue dates = _normalize_dates(hist.index) close = hist["Close"] prices_tmp = pd.DataFrame({ "ticker": ticker, "date": dates, "open": hist["Open"].values, "high": hist["High"].values, "low": hist["Low"].values, "close": close.values, "volume": hist["Volume"].fillna(0).astype("int64").values, }) con.execute("DELETE FROM prices WHERE ticker = ?", [ticker]) con.execute( "INSERT INTO prices SELECT ticker, date::DATE, open, high, low, close, volume FROM prices_tmp" ) macd_line, macd_sig, macd_hist_s = calc_macd(close) ind_tmp = pd.DataFrame({ "ticker": ticker, "date": dates, "rsi_14": calc_rsi(close).values, "macd": macd_line.values, "macd_signal": macd_sig.values, "macd_hist": macd_hist_s.values, "sma_20": close.rolling(20).mean().values, "sma_50": close.rolling(50).mean().values, "sma_200": close.rolling(200).mean().values, }) con.execute("DELETE FROM indicators WHERE ticker = ?", [ticker]) con.execute( "INSERT INTO indicators SELECT ticker, date::DATE, rsi_14, macd, macd_signal, " "macd_hist, sma_20, sma_50, sma_200 FROM ind_tmp" ) ingested.append(ticker) except Exception as e: print(f" {ticker} — error processing prices/indicators: {e}") return ingested # --------------------------------------------------------------------------- # Fundamentals ingestion (parallelised) # --------------------------------------------------------------------------- def _fetch_fundamentals(ticker: str) -> tuple[str, dict | None, str]: """Fetch .info for one ticker. Returns (ticker, info_dict, status_msg).""" try: info = yf.Ticker(ticker).info return ticker, info, "ok" except Exception as e: return ticker, None, f"failed: {e}" def ingest_fundamentals(con: duckdb.DuckDBPyConnection, tickers: list[str]) -> None: print(" Fetching fundamentals in parallel...") with ThreadPoolExecutor(max_workers=10) as executor: futures = {executor.submit(_fetch_fundamentals, t): t for t in tickers} for future in as_completed(futures): ticker, info, msg = future.result() print(f" {ticker} fundamentals — {msg}") if info is None: continue try: con.execute("DELETE FROM fundamentals WHERE ticker = ?", [ticker]) con.execute( "INSERT INTO fundamentals VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", [ ticker, info.get("longName") or info.get("shortName", ticker), info.get("sector", "Unknown"), info.get("industry", "Unknown"), info.get("marketCap"), info.get("trailingPE"), info.get("forwardPE"), info.get("trailingEps"), info.get("dividendYield"), info.get("beta"), info.get("fiftyTwoWeekHigh"), info.get("fiftyTwoWeekLow"), ], ) except Exception as e: print(f" {ticker} fundamentals DB write failed: {e}") # --------------------------------------------------------------------------- # Entry point # --------------------------------------------------------------------------- def main() -> None: print(f"Database: {DB_PATH}\n") con = duckdb.connect(DB_PATH) create_tables(con) # --- Batch price download (one request for all tickers) --- print(f"Downloading price history for {len(TICKERS)} tickers in one batch...") raw = yf.download( TICKERS, period="2y", group_by="column", auto_adjust=True, progress=False, ) print("Download complete. Processing prices and indicators...") ingested = ingest_prices_and_indicators(con, raw) print(f" {len(ingested)}/{len(TICKERS)} tickers: prices + indicators written.\n") # --- Parallel fundamentals fetch --- ingest_fundamentals(con, ingested) con.close() print(f"\nDone. {len(ingested)}/{len(TICKERS)} tickers ingested.") if __name__ == "__main__": main()