Y-9C / Yahoo Bank Panel

Repository layout

tracks/y9c-panel/
  README.md
  requirements.txt
  core_items.yaml
  y9c/                          # download, panel, forecast modules
  scripts/                      # thin command-line entry points
  notebooks/y9c_nii_forecast.ipynb
  tests/
  data/raw/                     # ignored runtime ZIP cache
  data/processed/               # ignored panel and coverage outputs
docs/tracks/y9c-panel/results/   # generated aggregate results

1. FR Y-9C (NIC bulk)

Official source: NIC Financial Data Download

Scripted ZIP URL pattern (no auth):

https://www.ffiec.gov/npw/FinancialReport/ReturnBHCFZipFiles?zipfilename=BHCF{YYYYMMDD}.zip

Example: BHCF20241231.zip → one ^-delimited BHCF20241231.txt (latin-1). Chicago Fed historical page still lists older quarters but redirects current window to NIC. This pipeline uses NIC only.

Refresh: 2018Q1 through the latest available quarter

make y9c-venv
make y9c-data

From the repository root, make y9c also executes the notebook and renders the results page. The pipeline lives in tracks/y9c-panel/; see its README and code.

Outputs under tracks/y9c-panel/data/processed/:

panel_long.parquet — reported and de-cumulated quarterly values by RSSD/date/item
panel_wide.parquet — one row per RSSD/date, including assets and NII
coverage.csv and vintage.json — coverage, de-cumulation drops and provenance

The small tracks/y9c-panel/core_items.yaml corrects the reference mapping: BHCK4074 is net interest income, BHCK4107 total interest income and BHCK4073 total interest expense. Income items are de-cumulated only when the immediately prior quarter in the same calendar year is present. Values are thousands of USD. Raw data and panels stay in the ignored runtime cache.

Manual fallback

If NIC returns a redirect, HTML, or a captcha, the downloader fails clearly for required quarters:

Open the NIC download page and download the named BHCF*.zip.
Drop it into tracks/y9c-panel/data/raw/.
Rerun make y9c-data.

2. Yahoo bank quarterly

Historical off-repository notes only; the pipeline and results in this repository use FR Y-9C alone.

cd /workspace/research/forma_adhoc
source .venv/bin/activate

python scripts/yahoo_bank_quarterly.py --write-csv
# or custom list:
python scripts/yahoo_bank_quarterly.py --tickers JPM,BAC,WFC --write-csv

Outputs under yahoo_bank_quarterly/processed/:

yahoo_bank_quarterly_long.parquet — ticker, period_end, statement, line, line_raw, value
coverage_by_ticker.csv

Seed tickers: yahoo_bank_quarterly/tickers/seed_us_banks.txt.

Alignment limits (YTD vs quarterly, labels ≠ MDRM): see yahoo_bank_quarterly/YAHOO_VS_Y9C.md.

Known quirks

Yahoo ticker BK often returns empty quarterly statements via yfinance; seed list uses BNY (Bank of New York Mellon).
Y-9C HI items are typically YTD; Yahoo quarterly IS lines are usually quarterly — see YAHOO_VS_Y9C.md.
NIC BHCF values are generally in thousands of USD.

Historical Yahoo environment (off-repository)

source /workspace/research/forma_adhoc/.venv/bin/activate
# already has: pandas pyarrow requests yfinance pyyaml tqdm

Related tracks

Statement Forecast — methods that use this panel.
Teaching note — how to build on the panel.
Lit memo — literature context for Y-9C forecasting.