TeX 69.3%
Python 30.6%
1"""202_build_panel.py3Merge the semantic factor panel (annual_final) with realized stock performance.45Timing convention (avoids look-ahead bias):6 A 10-K for fiscal year Y is public by ~Feb-Mar of Y+1. We therefore form7 signals at the END OF JUNE of year Y+1 and measure the FORWARD 12-month8 return over July(Y+1) -> June(Y+2). This is the Fama-French portfolio9 convention: the signal is strictly known before the holding period starts.1011Outputs:12 data/processed/monthly_returns.parquet ticker, date, ret, mktcap_proxy13 data/processed/merged_panel.parquet firm-year panel: AI scores (FY=Y) + fwd returns + controls14"""15import numpy as np16import pandas as pd1718D = "data/processed"19px = pd.read_parquet(f"{D}/prices_monthly.parquet")20spy = pd.read_parquet(f"{D}/spy_monthly.parquet")21mc = pd.read_parquet(f"{D}/marketcap.parquet")22panel = pd.read_parquet("data/parquet/annual_final/annual_final.parquet")2324# ---- monthly simple returns ----25px = px.sort_values(["ticker", "date"]).copy()26px["date"] = pd.to_datetime(px["date"]).dt.to_period("M").dt.to_timestamp("M") # month-end stamp27px["ret"] = px.groupby("ticker")["adj_close"].pct_change()2829spy["date"] = pd.to_datetime(spy["date"]).dt.to_period("M").dt.to_timestamp("M")30spy = spy.sort_values("date")31spy["spy_ret"] = spy["spy_adj_close"].pct_change()3233# market-cap proxy = current shares * historical price (shares held constant approximation)34mc_map = mc.set_index("ticker")["shares"].to_dict()35px["shares"] = px["ticker"].map(mc_map)36px["mktcap_proxy"] = px["shares"] * px["adj_close"]37px.to_parquet(f"{D}/monthly_returns.parquet", index=False)3839# ---- helper: cumulative return over [start, end] inclusive month-ends ----40def cum_ret(df_t, start, end):41 m = df_t[(df_t.date > start) & (df_t.date <= end)]42 if m.empty or m["ret"].isna().all():43 return np.nan44 r = (1 + m["ret"].fillna(0)).prod() - 145 # require reasonable coverage46 return r if m["ret"].notna().sum() >= 6 else np.nan4748spy_ix = spy.set_index("date")["spy_ret"]49def cum_spy(start, end):50 m = spy_ix[(spy_ix.index > start) & (spy_ix.index <= end)]51 return (1 + m.fillna(0)).prod() - 1 if len(m) else np.nan5253rows = []54px_by = {t: d.sort_values("date") for t, d in px.groupby("ticker")}55for _, r in panel.iterrows():56 t = r["ticker"]; Y = int(r["fiscal_year"])57 d = px_by.get(t)58 if d is None:59 continue60 form = pd.Timestamp(year=Y+1, month=6, day=30) + pd.offsets.MonthEnd(0)61 end1 = pd.Timestamp(year=Y+2, month=6, day=30) + pd.offsets.MonthEnd(0)62 # controls measured AT formation (strictly known):63 mom_start = pd.Timestamp(year=Y, month=6, day=30) + pd.offsets.MonthEnd(0) # 12m momentum to formation64 # forward 12m return65 fwd = cum_ret(d, form, end1)66 spy_fwd = cum_spy(form, end1)67 # momentum (past 12m, formation-12 .. formation, skip last month)68 mom = cum_ret(d, mom_start, form - pd.offsets.MonthEnd(1))69 # trailing vol & size at formation70 win = d[(d.date > mom_start) & (d.date <= form)]71 vol = win["ret"].std() * np.sqrt(12) if win["ret"].notna().sum() >= 6 else np.nan72 cap = d.loc[d.date <= form, "mktcap_proxy"].dropna()73 cap = cap.iloc[-1] if len(cap) else np.nan74 rows.append({"ticker": t, "fiscal_year": Y,75 "fwd_ret": fwd, "spy_fwd": spy_fwd,76 "fwd_ret_mktadj": fwd - spy_fwd if pd.notna(fwd) and pd.notna(spy_fwd) else np.nan,77 "mom_12m": mom, "vol_12m": vol, "mktcap": cap,78 "log_mktcap": np.log(cap) if pd.notna(cap) and cap > 0 else np.nan})7980ret_panel = pd.DataFrame(rows)81merged = panel.merge(ret_panel, on=["ticker", "fiscal_year"], how="left")82merged.to_parquet(f"{D}/merged_panel.parquet", index=False)8384n_ret = merged["fwd_ret"].notna().sum()85print(f"merged: {merged.shape}, firm-years with fwd return: {n_ret}")86print("fwd_ret by year (count):")87print(merged[merged.fwd_ret.notna()].groupby("fiscal_year").size().to_string())88print("\nfwd_ret describe:\n", merged["fwd_ret"].describe())89