#!/usr/bin/env python3 """Fetch daily Yahoo Finance sample stock data into an approved MySQL database. Target table: daiyan.fin_shr (or the explicitly configured DB_NAME). The table generates id automatically. The loader sends ticker, trade_date, open, day_low and day_high. UNIQUE(ticker, trade_date) prevents duplicates. Examples: python load_prices.py --symbols NVDA AAPL MSFT GOOGL AMZN --days 7 --dry-run python load_prices.py --symbols NVDA AAPL MSFT GOOGL AMZN --start 2026-07-27 --end 2026-10-27 --start and --end are inclusive. --days counts calendar days including today in Asia/Dhaka; markets do not trade every day. """ import argparse import getpass import logging import os import sys import time from datetime import date, datetime, timedelta, timezone from decimal import Decimal, InvalidOperation, ROUND_HALF_UP import mysql.connector import pandas as pd import yfinance as yf LOG = logging.getLogger("finance_equity") DHAKA = timezone(timedelta(hours=6)) PRICE_PRECISION = Decimal("0.0001") # Row aliases are available in MySQL 8.0.19+ and avoid deprecated VALUES(col). UPSERT_SQL = """ INSERT INTO `fin_shr` (`ticker`, `trade_date`, `open`, `day_low`, `day_high`) VALUES {placeholders} AS incoming ON DUPLICATE KEY UPDATE `open` = incoming.`open`, `day_low` = incoming.`day_low`, `day_high` = incoming.`day_high` """ def to_price(value): """Convert valid market prices to four-decimal MySQL DECIMAL values.""" if value is None or pd.isna(value): return None try: result = Decimal(str(value)).quantize(PRICE_PRECISION, rounding=ROUND_HALF_UP) return result if result.is_finite() else None except (InvalidOperation, ValueError, TypeError): return None def fetch_stock(ticker, start, end): """Fetch market-date rows for one ticker within inclusive date bounds.""" data = yf.Ticker(ticker).history( start=start.isoformat(), end=(end + timedelta(days=1)).isoformat(), # Yahoo's end is exclusive. interval="1d", auto_adjust=False, actions=False, ) if data.empty: return [] missing = {"Open", "Low", "High"} - set(data.columns) if missing: raise ValueError(f"Missing Yahoo Finance columns: {sorted(missing)}") rows = [] for stamp, record in data.iterrows(): trading_day = stamp.date() # yfinance index is in exchange-local time. if not start <= trading_day <= end: continue opening = to_price(record["Open"]) low = to_price(record["Low"]) high = to_price(record["High"]) if None in (opening, low, high) or low > high: LOG.warning("Skipping invalid price: %s on %s", ticker, trading_day) continue rows.append((ticker, trading_day, opening, low, high)) return rows def make_batch_statement(batch): """Build a parameterized multi-row UPSERT; id is server-generated.""" if not batch: raise ValueError("Batch must contain at least one record") placeholders = ", ".join(["(%s, %s, %s, %s, %s)"] * len(batch)) parameters = tuple(column for row in batch for column in row) return UPSERT_SQL.format(placeholders=placeholders), parameters def close_quietly(resource): if resource is not None: try: resource.close() except Exception: pass def import_batches(rows, settings, batch_size, retries): """Commit idempotent batches, reconnecting if the server drops the session.""" connection = None cursor = None committed = 0 try: for offset in range(0, len(rows), batch_size): batch = rows[offset: offset + batch_size] sql, params = make_batch_statement(batch) for attempt in range(retries + 1): try: if connection is None: connection = mysql.connector.connect(**settings) cursor = connection.cursor() cursor.execute(sql, params) connection.commit() committed += len(batch) LOG.info("Committed %d/%d records to %s.fin_shr", committed, len(rows), settings["database"]) break except mysql.connector.Error as exc: if connection is not None: try: connection.rollback() except mysql.connector.Error: pass close_quietly(cursor) close_quietly(connection) cursor = None connection = None if attempt == retries: raise RuntimeError( f"Batch {offset // batch_size + 1} failed. " f"{committed}/{len(rows)} records confirmed committed. " f"Last error: {exc}" ) from exc LOG.warning("Batch %d failed: %s; reconnecting (%d/%d)", offset // batch_size + 1, exc, attempt + 1, retries) time.sleep(min(2 ** attempt, 4)) finally: close_quietly(cursor) close_quietly(connection) return committed def parse_args(): parser = argparse.ArgumentParser(description="Yahoo Finance to MySQL: Phase 1") parser.add_argument("--symbols", nargs="+", default=["NVDA", "AAPL", "MSFT", "GOOGL", "AMZN"]) parser.add_argument("--start", type=date.fromisoformat, help="inclusive start, YYYY-MM-DD; overrides --days") parser.add_argument("--end", type=date.fromisoformat, help="inclusive end, YYYY-MM-DD (default Dhaka today)") parser.add_argument("--days", type=int, default=7, help="calendar days ending on --end (default 7)") parser.add_argument("--batch-size", type=int, default=10, help="records per database statement (default 10)") parser.add_argument("--retries", type=int, default=3, help="connection/query retry attempts per batch (default 3)") parser.add_argument("--dry-run", action="store_true", help="print records; no MySQL connection or changes") return parser.parse_args() def main(): logging.basicConfig(level=logging.INFO, format="%(levelname)s: %(message)s") args = parse_args() if args.days < 1 or args.batch_size < 1 or args.retries < 0: sys.exit("ERROR: --days and --batch-size must be >= 1; --retries must be >= 0") end = args.end or datetime.now(DHAKA).date() start = args.start or end - timedelta(days=args.days - 1) if start > end: sys.exit("ERROR: --start cannot be after --end") tickers = list(dict.fromkeys(symbol.strip().upper() for symbol in args.symbols if symbol.strip())) if not tickers: sys.exit("ERROR: at least one symbol is required") all_rows = [] fetch_failed = False for ticker in tickers: try: rows = fetch_stock(ticker, start, end) LOG.info("%s: %d valid trading-day records", ticker, len(rows)) all_rows.extend(rows) except Exception as exc: fetch_failed = True LOG.error("Could not fetch %s: %s", ticker, exc) if fetch_failed: sys.exit("ERROR: one or more tickers failed; nothing written to MySQL") if not all_rows: sys.exit("ERROR: no records returned for this date range") if args.dry_run: for row in all_rows: print(row) LOG.info("DRY RUN: %d records, nothing written to MySQL", len(all_rows)) return # Explicitly require the target database so a mistaken CMD session cannot # silently write into the local development database. database = os.getenv("DB_NAME") user = os.getenv("DB_USER") host = os.getenv("DB_HOST") if not all((database, user, host)): sys.exit("ERROR: set DB_HOST, DB_USER and DB_NAME before importing") password = os.getenv("DB_PASSWORD") if not password: password = getpass.getpass("MySQL password (input hidden): ") if not password: sys.exit("ERROR: MySQL password is required") settings = { "host": host, "port": int(os.getenv("DB_PORT", "3306")), "user": user, "password": password, "database": database, "connection_timeout": 20, } # Optional query-response timeouts are only supported from Connector/Python 9.2. if getattr(mysql.connector, "__version_info__", (0, 0, 0))[:3] >= (9, 2, 0): settings.update(read_timeout=120, write_timeout=120) LOG.info("Import target: %s:%d / %s (user %s)", settings["host"], settings["port"], settings["database"], settings["user"]) try: completed = import_batches(all_rows, settings, args.batch_size, args.retries) except (mysql.connector.Error, RuntimeError) as exc: sys.exit(f"ERROR: import failed: {exc}") LOG.info("SUCCESS: %d records inserted/updated in %s.fin_shr", completed, database) if __name__ == "__main__": main()