Download src/data/generate_db.py from margotbtn/finsight: direct link, hf CLI and curl.
- Browser
- Download file 9.41 kB
-
https://huggingface.co/spaces/margotbtn/finsight/resolve/main/src/data/generate_db.py
- Command line
-
hf download hf://spaces/margotbtn/finsight/src/data/generate_db.py
-
curl -L -o generate_db.py https://huggingface.co/spaces/margotbtn/finsight/resolve/main/src/data/generate_db.py
9.41 kB
| #!/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() | |