""" Download the JKP CTF competition tables from WRDS -> ctff parquet files. WHAT THIS IS ============ This pulls the **official Common Task Framework (CTF) competition tables** of Hellum, Jensen, Kelly & Pedersen (2025) straight from WRDS, exactly as the "Getting CTF Data from WRDS" guide shows. The three tables live in the contributed schema **contrib_global_factor**: contrib_global_factor.ctff_features the characteristic list contrib_global_factor.ctff_chars stock-month characteristics contrib_global_factor.ctff_daily_ret daily excess returns (optional) and are written out unchanged as the three "ctff" parquet files the strategy reads. Because these are the pre-built competition tables, the universe screen (JKP's non-micro universe), the official feature list, and the `ctff_test` flag and `eom_ret` column all come straight from the source -- this is the dataset the challenge actually grades against. NOTE: an earlier version of this script RECONSTRUCTED a proxy of these tables from the raw `contrib.global_factor` table, applying its own universe filter and a META_COLS feature heuristic. That proxy did NOT match the competition data (it over-included microcaps/nanocaps and ~9 non-characteristic price fields). Querying contrib_global_factor.ctff_* directly fixes that. WHAT YOU NEED ============= 1. A WRDS account (https://wrds-www.wharton.upenn.edu) with access to the contributed dataset *Global Factor Data* and its CTF tables (the underlying inputs are CRSP Monthly/Daily and Compustat North America). 2. Python packages: pip install wrds pandas pyarrow python-dotenv OUTPUT (the format the strategy reads) ====================================== ctff_chars.parquet one row per stock-month: id, eom, sic, size_grp, ret_exc_lead1m, ctff_test, eom_ret + one column per characteristic (~400). ret_exc_lead1m is NEXT month's excess return (the prediction target). ctff_test marks the graded evaluation period. All taken verbatim from contrib_global_factor.ctff_chars. ctff_features.parquet the `features` column listing the characteristics, from contrib_global_factor.ctff_features. ctff_daily_ret.parquet (optional, --daily) id, date, ret_exc daily returns, from contrib_global_factor.ctff_daily_ret. CREDENTIALS =========== Provide your WRDS login via the course's root .env file (the same one used by the other modules). A template, env-template.txt, sits next to this script: WRDS_USERNAME=your_username_here WRDS_PASSWORD=your_password_here Copy it to a file named `.env` in the CANVAS root folder and fill in your real WRDS login. This script loads that root .env automatically. If no credentials are found, wrds.Connection() falls back to prompting you interactively and offers to save a .pgpass file so you are not asked again. Note: a .env stores your password in PLAINTEXT on disk. It is not committed anywhere by the course, but restrict or delete it once the download is done. USAGE ===== python 02-DownloadJKPdata.py --out-dir . # full table, 1951-2023 python 02-DownloadJKPdata.py --out-dir . --daily # also pull daily returns python 02-DownloadJKPdata.py --start 1951 --end 2023 --out-dir . CAVEATS (tell students up front) ================================ - These ARE the competition tables, so there is no universe/feature decision to make here -- whatever screen the organizers applied is what you get. - The `id` in the ctff tables is JKP's own identifier; the CRSP `permno` used for daily returns only matches it for US-CRSP-sourced rows. The official ctff_daily_ret table already carries the matching `id`, so prefer --daily over building daily returns yourself. - ctff_chars is several GB. The annual-chunk loop below avoids WRDS query timeouts. Expect roughly 15-45 minutes. """ import argparse import os import sys from pathlib import Path import pandas as pd # Official CTF tables in the WRDS contributed schema. SCHEMA = "contrib_global_factor" def load_credentials(): """ Read WRDS_USERNAME / WRDS_PASSWORD from the course root .env file (the same one the other modules use), then from the process environment. Returns (username, password); either may be None, in which case wrds.Connection() falls back to its interactive prompt. """ try: from dotenv import load_dotenv # Root .env sits at the CANVAS root: two levels up from this script # (06-Quant Modelling/ -> CANVAS/). Matches 04-Spreadsheets/03-FetchData.py. load_dotenv(Path(__file__).resolve().parent.parent / ".env") except ImportError: print(" (python-dotenv not installed; relying on environment / prompt)") return os.environ.get("WRDS_USERNAME"), os.environ.get("WRDS_PASSWORD") def pull_features(db): """Pull the official characteristic list from ctff_features.""" try: feats = db.raw_sql(f"SELECT * FROM {SCHEMA}.ctff_features;") except Exception as exc: # noqa: BLE001 _fail_table(db, "ctff_features", exc) # The strategy expects a single column named `features`. The official table # already uses that name; rename defensively if a future schema differs. if "features" not in feats.columns: name_col = next( (c for c in feats.columns if c.lower() in ("features", "characteristic", "char", "name", "variable")), feats.columns[0]) feats = feats.rename(columns={name_col: "features"}) return feats def pull_chars(db, country, start_year, end_year): """ Pull contrib_global_factor.ctff_chars verbatim, in annual chunks to avoid query timeouts. ctff_test and eom_ret already exist in the source table, so nothing is recomputed here. An optional --country narrows by `excntry`. """ where_country = f"AND excntry = '{country}'" if country else "" frames = [] for year in range(start_year, end_year + 1): q = f""" SELECT * FROM {SCHEMA}.ctff_chars WHERE eom BETWEEN '{year}-01-01' AND '{year}-12-31' {where_country} """ try: chunk = db.raw_sql(q, date_cols=["eom", "eom_ret"]) except Exception as exc: # noqa: BLE001 _fail_table(db, "ctff_chars", exc) frames.append(chunk) print(f" {year}: {len(chunk):,} rows") chars = pd.concat(frames, ignore_index=True) chars["eom"] = pd.to_datetime(chars["eom"]) if "eom_ret" in chars.columns: chars["eom_ret"] = pd.to_datetime(chars["eom_ret"]) return chars def pull_daily(db, out_dir): """OPTIONAL: official daily excess returns from ctff_daily_ret.""" print("\n Pulling daily excess returns from ctff_daily_ret (optional)...") try: dsf = db.raw_sql(f"SELECT * FROM {SCHEMA}.ctff_daily_ret;", date_cols=["date"]) except Exception as exc: # noqa: BLE001 _fail_table(db, "ctff_daily_ret", exc) out = f"{out_dir}/ctff_daily_ret.parquet" dsf.to_parquet(out, index=False) print(f" saved {len(dsf):,} daily rows -> {out}") def _fail_table(db, table, exc): """Explain a missing/renamed CTF table and list relevant libraries.""" print(f"\nERROR: could not query {SCHEMA}.{table}: {exc}\n") print("Available libraries that look relevant:") try: for lib in sorted(db.list_libraries()): if "contrib" in lib or "factor" in lib or "jkp" in lib: print(f" - {lib}") except Exception as exc2: # noqa: BLE001 print(f" (could not list libraries: {exc2})") print("\nConfirm your WRDS subscription includes the contributed Global " "Factor Data CTF tables, then re-run.") sys.exit(1) def sanity_report(chars, char_cols): print("\n SANITY CHECKS") print(f" rows .............. {len(chars):,}") print(f" unique stocks ..... {chars['id'].nunique():,}") print(f" months ............ {chars['eom'].nunique()}") print(f" date range ........ {chars['eom'].min().date()} -> " f"{chars['eom'].max().date()}") print(f" features .......... {len(char_cols)}") if "size_grp" in chars.columns: grps = chars["size_grp"].value_counts(dropna=False).to_dict() print(f" size groups ....... {grps}") if "ctff_test" in chars.columns: print(f" ctff_test rows .... {int(chars['ctff_test'].sum()):,}") present = [c for c in char_cols if c in chars.columns] if present: miss = chars[present].isna().mean().sort_values(ascending=False) print(" most-missing features (% NaN):") for name, frac in miss.head(5).items(): print(f" {name:<28} {frac*100:5.1f}%") if "ret_exc_lead1m" in chars.columns: lead_na = chars["ret_exc_lead1m"].isna().mean() * 100 print(f" ret_exc_lead1m NaN {lead_na:5.1f}% (expected > 0: last " f"month of each stock has no forward return)") def main(): ap = argparse.ArgumentParser(description=__doc__) ap.add_argument("--out-dir", default=".", help="where to write the parquet files (default: .)") ap.add_argument("--country", default=None, help="optional JKP excntry filter (default: none -- pull " "the table as-is, matching the official guide)") ap.add_argument("--start", type=int, default=1951, help="first year, inclusive (default: 1951)") ap.add_argument("--end", type=int, default=2023, help="last year, inclusive (default: 2023)") ap.add_argument("--daily", action="store_true", help="also pull ctff_daily_ret (optional)") args = ap.parse_args() import wrds # imported here so --help works offline print("Connecting to WRDS...") user, passwd = load_credentials() if user and passwd: print(f" Using credentials from .env / environment for '{user}'.") db = wrds.Connection(wrds_username=user, wrds_password=passwd) else: print(" No stored credentials found; wrds will prompt interactively.") db = wrds.Connection() print(f"\nPulling {SCHEMA}.ctff_features ...") feats = pull_features(db) char_cols = feats["features"].tolist() print(f" {len(char_cols)} characteristics listed.") scope = f" (excntry={args.country})" if args.country else "" print(f"\nPulling {SCHEMA}.ctff_chars {args.start}-{args.end}{scope} " f"in annual chunks:") chars = pull_chars(db, args.country, args.start, args.end) chars_path = f"{args.out_dir}/ctff_chars.parquet" feats_path = f"{args.out_dir}/ctff_features.parquet" chars.to_parquet(chars_path, index=False) feats.to_parquet(feats_path, index=False) sanity_report(chars, char_cols) print(f"\n Saved:") print(f" {chars_path}") print(f" {feats_path}") if args.daily: try: pull_daily(db, args.out_dir) except SystemExit: raise except Exception as exc: # noqa: BLE001 print(f" daily pull failed (non-fatal): {exc}") db.close() print("\nDone. Point the strategy at this folder:") print(f" python 04-StrategyTemplate.py --data \"{args.out_dir}\"") if __name__ == "__main__": main()