spb/wp17_uqo

1"""201_download_prices.py3Download monthly + daily adjusted prices from Yahoo Finance for all S&P 5004tickers present in the semantic panel, plus the SPY market benchmark and the5risk-free proxy (^IRX, 13-week T-bill). Produces:6  data/processed/prices_monthly.parquet   (long: ticker, date, adj_close)7  data/processed/marketcap.parquet        (ticker, sharesOutstanding, marketCap snapshot)8  data/processed/spy_monthly.parquet9"""10import time, json, warnings11import pandas as pd12import numpy as np13import yfinance as yf1415warnings.filterwarnings("ignore")16PANEL = "data/parquet/annual_final/annual_final.parquet"17OUT = "data/processed"1819panel = pd.read_parquet(PANEL)20tickers = sorted(panel.ticker.dropna().unique().tolist())21print(f"{len(tickers)} tickers to download")2223START, END = "2009-06-01", "2026-06-17"2425def dl_batch(tks, **kw):26    for attempt in range(3):27        try:28            df = yf.download(tks, start=START, end=END, auto_adjust=True,29                             progress=False, group_by="ticker", threads=True, **kw)30            return df31        except Exception as e:32            print("  retry", attempt, e); time.sleep(3)33    return None3435# --- monthly prices, batched ---36all_monthly = []37BATCH = 5038for i in range(0, len(tickers), BATCH):39    batch = tickers[i:i+BATCH]40    print(f"batch {i//BATCH+1}: {batch[0]}..{batch[-1]}")41    df = dl_batch(batch, interval="1mo")42    if df is None:43        continue44    for t in batch:45        try:46            sub = df[t] if isinstance(df.columns, pd.MultiIndex) else df47            s = sub["Close"].dropna()48            if len(s) == 0:49                continue50            tmp = s.reset_index()51            tmp.columns = ["date", "adj_close"]52            tmp["ticker"] = t53            all_monthly.append(tmp)54        except Exception:55            pass56    time.sleep(1)5758monthly = pd.concat(all_monthly, ignore_index=True)59monthly["date"] = pd.to_datetime(monthly["date"])60monthly = monthly.sort_values(["ticker", "date"])61monthly.to_parquet(f"{OUT}/prices_monthly.parquet", index=False)62got = monthly.ticker.nunique()63print(f"monthly: {len(monthly)} rows, {got}/{len(tickers)} tickers")6465# --- benchmark + risk free ---66spy = dl_batch(["SPY"], interval="1mo")67spy_close = (spy["SPY"]["Close"] if isinstance(spy.columns, pd.MultiIndex) else spy["Close"]).dropna()68spy_df = spy_close.reset_index(); spy_df.columns = ["date", "spy_adj_close"]69spy_df.to_parquet(f"{OUT}/spy_monthly.parquet", index=False)70print("spy rows:", len(spy_df))7172# --- market cap snapshot (best effort) ---73rows = []74for i, t in enumerate(tickers):75    try:76        fi = yf.Ticker(t).fast_info77        rows.append({"ticker": t,78                     "market_cap": getattr(fi, "market_cap", None),79                     "shares": getattr(fi, "shares", None)})80    except Exception:81        rows.append({"ticker": t, "market_cap": None, "shares": None})82    if i % 50 == 0:83        print("  mktcap", i)84pd.DataFrame(rows).to_parquet(f"{OUT}/marketcap.parquet", index=False)85print("done")86