finsight / src /data /generate_db.py
margotbtn's picture
Refinement
1766315
Raw History Blame Contribute Delete
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()