"""Historical daily equity price scraper & loader for fin_equity_hist. Features: - Scrapes daily historical bars (OHLCV) from Yahoo Finance chart API. - Automatically calculates previous day close, open gap diff/%, intraday range, and daily close diff/%. - Smart incremental backfill: - If data already exists for a ticker (e.g. 2026-10-20 to today), and user requests an earlier date (e.g. 2026-09-25), it only scrapes the missing window (2026-09-25 to 2026-10-20). - If requested date is prior to the company's IPO/listing date, it automatically clamps to the listing date (firstTradeDate). - Idempotent upserts via UNIQUE KEY (idfin_equity, price_dt). Usage: uv run python history.py --ticker AAPL --start 2026/10/20 uv run python history.py --tickers AAPL NVDA META --start 2026/09/25 uv run python history.py --all --start 2026/10/01 """ import argparse import logging import sys from datetime import date, datetime, timedelta from typing import Any, Dict, List, Optional, Tuple import requests from db import get_cursor logging.basicConfig( level=logging.INFO, format="%(asctime)s %(levelname)-8s %(message)s", datefmt="%H:%M:%S", handlers=[ logging.StreamHandler(sys.stdout), logging.FileHandler("history.log", encoding="utf-8"), ], ) logger = logging.getLogger(__name__) USER_AGENT = "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/124.0.0.0 Safari/537.36" def parse_date_str(d_str: str) -> date: """Parse string supporting both YYYY/MM/DD and YYYY-MM-DD formats.""" clean = d_str.strip().replace("/", "-") return datetime.strptime(clean, "%Y-%m-%d").date() def get_idfin_equity(cur, ticker: str) -> Optional[int]: """Look up idfin_equity from fin_equity table by ticker or comp_id.""" cur.execute( "SELECT idfin_equity FROM fin_equity WHERE ticker = %s OR comp_id = %s LIMIT 1", (ticker, ticker), ) row = cur.fetchone() return row["idfin_equity"] if row else None def get_existing_date_range(cur, idfin_equity: int) -> Tuple[Optional[date], Optional[date]]: """Return (min_dt, max_dt) already in fin_equity_hist for this idfin_equity.""" cur.execute( "SELECT MIN(price_dt) AS min_dt, MAX(price_dt) AS max_dt FROM fin_equity_hist WHERE idfin_equity = %s", (idfin_equity,), ) row = cur.fetchone() if row and row.get("min_dt") and row.get("max_dt"): return row["min_dt"], row["max_dt"] return None, None def get_ticker_meta(ticker: str) -> Tuple[Optional[date], Optional[str]]: """Fetch metadata (listing date / firstTradeDate and currency) from Yahoo Finance chart API.""" url = f"https://query1.finance.yahoo.com/v8/finance/chart/{ticker}?interval=1d&range=1d" headers = {"User-Agent": USER_AGENT} try: resp = requests.get(url, headers=headers, timeout=15) if resp.status_code == 200: res = resp.json().get("chart", {}).get("result", []) if res: meta = res[0].get("meta", {}) first_ts = meta.get("firstTradeDate") first_dt = datetime.fromtimestamp(first_ts).date() if first_ts else None curr = meta.get("currency", "USD") return first_dt, curr except Exception as exc: logger.warning("[%s] Failed to fetch chart metadata: %s", ticker, exc) return None, "USD" def determine_missing_windows( req_start: date, req_end: date, min_in_db: Optional[date], max_in_db: Optional[date], ) -> List[Tuple[date, date]]: """Determine what date windows need to be fetched so we never re-scrape existing days.""" if not min_in_db or not max_in_db: # DB has no records for this ticker yet return [(req_start, req_end)] windows = [] # 1. Earlier history window (e.g. req_start 2026-09-25, min_in_db 2026-10-20) if req_start < min_in_db: windows.append((req_start, min_in_db)) # 2. Forward window if new trading days elapsed past max_in_db if req_end > max_in_db: windows.append((max_in_db, req_end)) return windows def fetch_and_calculate_bars( ticker: str, idfin_equity: int, window_start: date, window_end: date, ) -> List[Dict[str, Any]]: """Fetch raw bars from Yahoo Finance for the window, calculate metrics, and return rows.""" # Look back 5 calendar days before window_start to get the previous day's closing price buffered_start_dt = window_start - timedelta(days=7) start_ts = int(datetime.combine(buffered_start_dt, datetime.min.time()).timestamp()) end_ts = int(datetime.combine(window_end + timedelta(days=1), datetime.max.time()).timestamp()) url = ( f"https://query1.finance.yahoo.com/v8/finance/chart/{ticker}" f"?period1={start_ts}&period2={end_ts}&interval=1d&events=history" ) headers = {"User-Agent": USER_AGENT} try: resp = requests.get(url, headers=headers, timeout=20) resp.raise_for_status() chart_res = resp.json().get("chart", {}).get("result", []) if not chart_res: logger.warning("[%s] No chart data returned for window %s to %s", ticker, window_start, window_end) return [] except Exception as exc: logger.error("[%s] Network error fetching historical chart: %s", ticker, exc) return [] chart = chart_res[0] timestamps = chart.get("timestamp", []) quotes = chart.get("indicators", {}).get("quote", [{}])[0] opens = quotes.get("open", []) highs = quotes.get("high", []) lows = quotes.get("low", []) closes = quotes.get("close", []) vols = quotes.get("volume", []) today = date.today() calculated_rows = [] for i in range(1, len(timestamps)): ts = timestamps[i] bar_dt = datetime.fromtimestamp(ts).date() # Only process bars strictly within [window_start, window_end] if bar_dt < window_start or bar_dt > window_end: continue pre_cls = closes[i - 1] c_open = opens[i] c_high = highs[i] c_low = lows[i] c_cls = closes[i] c_vol = vols[i] if any(v is None for v in [pre_cls, c_open, c_high, c_low, c_cls]): continue # ── Computations ────────────────────────────────────────────────────────── # Overnight / Open gap: Current Open vs Previous Close gap_amt = c_open - pre_cls gap_pcnt = (gap_amt / pre_cls) * 100.0 if pre_cls else 0.0 # Intraday High-Low range hl_diff = c_high - c_low # Full day net change: Current Close vs Previous Close cls_diff_amt = c_cls - pre_cls cls_diff_pcnt = (cls_diff_amt / pre_cls) * 100.0 if pre_cls else 0.0 calculated_rows.append({ "idfin_equity": idfin_equity, "price_dt": bar_dt, "pre_day_cls_pr": f"{pre_cls:.2f}", "curr_open_pr": f"{c_open:.2f}", "pre_curr_diff_amt": f"{gap_amt:+.2f}", "pre_curr_diff_pcnt": f"{gap_pcnt:+.2f}%", "curr_high": f"{c_high:.2f}", "curr_low": f"{c_low:.2f}", "curr_high_low_diff": f"{hl_diff:.2f}", "curr_vol": str(int(c_vol)) if c_vol is not None else "0", "curr_cls_pr": f"{c_cls:.2f}", "curr_cls_diff_amt": f"{cls_diff_amt:+.2f}", "curr_cls_diff_pcnt": f"{cls_diff_pcnt:+.2f}%", "updated": today, }) return calculated_rows def insert_hist_rows(cur, rows: List[Dict[str, Any]]) -> int: """Batch upsert rows into fin_equity_hist.""" if not rows: return 0 sql = """ INSERT INTO fin_equity_hist ( idfin_equity, price_dt, pre_day_cls_pr, curr_open_pr, pre_curr_diff_amt, pre_curr_diff_pcnt, curr_high, curr_low, curr_high_low_diff, curr_vol, curr_cls_pr, curr_cls_diff_amt, curr_cls_diff_pcnt, updated ) VALUES ( %(idfin_equity)s, %(price_dt)s, %(pre_day_cls_pr)s, %(curr_open_pr)s, %(pre_curr_diff_amt)s, %(pre_curr_diff_pcnt)s, %(curr_high)s, %(curr_low)s, %(curr_high_low_diff)s, %(curr_vol)s, %(curr_cls_pr)s, %(curr_cls_diff_amt)s, %(curr_cls_diff_pcnt)s, %(updated)s ) ON DUPLICATE KEY UPDATE pre_day_cls_pr = VALUES(pre_day_cls_pr), curr_open_pr = VALUES(curr_open_pr), pre_curr_diff_amt = VALUES(pre_curr_diff_amt), pre_curr_diff_pcnt = VALUES(pre_curr_diff_pcnt), curr_high = VALUES(curr_high), curr_low = VALUES(curr_low), curr_high_low_diff = VALUES(curr_high_low_diff), curr_vol = VALUES(curr_vol), curr_cls_pr = VALUES(curr_cls_pr), curr_cls_diff_amt = VALUES(curr_cls_diff_amt), curr_cls_diff_pcnt = VALUES(curr_cls_diff_pcnt), updated = VALUES(updated); """ cur.executemany(sql, rows) return len(rows) def scrape_history_for_ticker( ticker: str, start_date_str: str, end_date_str: Optional[str] = None, ) -> int: """Scrape and backfill historical records for a single ticker.""" req_start = parse_date_str(start_date_str) req_end = parse_date_str(end_date_str) if end_date_str else date.today() logger.info("=" * 60) logger.info("[%s] Requested scrape from %s to %s", ticker, req_start, req_end) with get_cursor(commit=True) as cur: # 1. Lookup idfin_equity idfin = get_idfin_equity(cur, ticker) if not idfin: logger.error("[%s] Not found in fin_equity table. Please insert/sync core profile first.", ticker) return 0 # 2. Check listing date (firstTradeDate) listing_date, _ = get_ticker_meta(ticker) if listing_date and req_start < listing_date: logger.info( "[%s] Requested start (%s) is before listing date (%s). Clamping start to listing date.", ticker, req_start, listing_date, ) req_start = listing_date # 3. Check existing database range min_in_db, max_in_db = get_existing_date_range(cur, idfin) if min_in_db and max_in_db: logger.info("[%s] Existing DB history: %s to %s", ticker, min_in_db, max_in_db) # 4. Resolve missing windows windows = determine_missing_windows(req_start, req_end, min_in_db, max_in_db) if not windows: logger.info("[%s] All dates between %s and %s already present in DB. No scrape needed.", ticker, req_start, req_end) return 0 total_inserted = 0 for w_start, w_end in windows: logger.info("[%s] Scraping missing window: %s to %s ...", ticker, w_start, w_end) rows = fetch_and_calculate_bars(ticker, idfin, w_start, w_end) if rows: n = insert_hist_rows(cur, rows) total_inserted += n logger.info("[%s] Inserted %d historical daily rows for window [%s - %s].", ticker, n, w_start, w_end) else: logger.info("[%s] Zero trading days found for window [%s - %s] (weekend/holiday).", ticker, w_start, w_end) logger.info("[%s] History sync complete. Total rows updated/inserted: %d", ticker, total_inserted) return total_inserted def get_all_active_tickers() -> List[str]: """Retrieve all distinct tickers currently registered in fin_equity.""" with get_cursor() as cur: cur.execute("SELECT ticker FROM fin_equity WHERE ticker IS NOT NULL ORDER BY idfin_equity") rows = cur.fetchall() return [r["ticker"] for r in rows] def main(): parser = argparse.ArgumentParser(description="FinLens historical daily equity price scraper") parser.add_argument("--ticker", help="Single ticker (e.g. AAPL)") parser.add_argument("--tickers", nargs="+", help="Multiple tickers (e.g. AAPL NVDA META)") parser.add_argument("--all", action="store_true", help="Scrape all tickers in fin_equity") parser.add_argument("--start", required=True, help="Start date (YYYY/MM/DD or YYYY-MM-DD)") parser.add_argument("--end", help="End date (defaults to today)") args = parser.parse_args() target_tickers = [] if args.ticker: target_tickers = [args.ticker.upper()] elif args.tickers: target_tickers = [t.upper() for t in args.tickers] elif args.all: target_tickers = get_all_active_tickers() else: logger.error("Please specify --ticker , --tickers , or --all") sys.exit(1) logger.info("FinLens Historical Scraper starting for %d tickers: %s", len(target_tickers), ", ".join(target_tickers)) for ticker in target_tickers: scrape_history_for_ticker(ticker, args.start, args.end) if __name__ == "__main__": main()