#!/usr/bin/env python3 """ 02_build_calibration_targets.py — turn the raw downloads of 01_download_data.py into * data/processed/series/*.csv tidy (date,value) time series, one per retrieved series * data/processed/calibration_targets.csv one row per calibration target (value, unit, period, geography, frequency, source, series_id, url, raw_file, retrieved, status, notes) * data/processed/bea_io_value_added_2023.csv sector labor shares / intermediate shares (BEA use table) * data/processed/epoch_price_performance_fit.csv hardware rows used in the FLOP/s-per-$ trend Run: python3 -I scripts/02_build_calibration_targets.py Every number written to calibration_targets.csv is computed from a file in data/raw/ listed in data/raw/manifest.json. If a raw file is missing, the corresponding targets are written with status=NOT_RETRIEVED and an empty value — nothing is ever filled in from memory. Raw files are untrusted data: they are only parsed (pandas / openpyxl / xlrd / PyMuPDF), never executed. """ from __future__ import annotations import csv import json import math import re import sys from pathlib import Path import numpy as np import pandas as pd ROOT = Path(__file__).resolve().parents[1] RAW = ROOT / "data" / "raw" PROC = ROOT / "data" / "processed" SER = PROC / "series" SER.mkdir(parents=True, exist_ok=True) RETRIEVED = "2026-10-27" AVG_START = 1990 manifest = json.loads((RAW / "manifest.json").read_text()) if (RAW / "manifest.json").exists() else {} fred_meta = json.loads((RAW / "fred" / "_metadata.json").read_text()) if (RAW / "fred" / "_metadata.json").exists() else {} ROWS: list[dict] = [] COLS = ["target_id", "description", "value", "unit", "year_or_period", "geography", "frequency", "source", "series_id", "url", "raw_file", "retrieved", "status", "notes"] def add(target_id, description, value, unit, period, geography, frequency, source, series_id, url, raw_file, notes="", status=None): if status is None: status = "RETRIEVED" if (value is not None and not (isinstance(value, float) and math.isnan(value))) else "NOT_RETRIEVED" if status in ("NOT_RETRIEVED", "INFO"): value = "" elif isinstance(value, float): value = f"{value:.6g}" ROWS.append(dict(target_id=target_id, description=description, value=value, unit=unit, year_or_period=period, geography=geography, frequency=frequency, source=source, series_id=series_id, url=url, raw_file=raw_file, retrieved=RETRIEVED, status=status, notes=notes)) def not_retrieved(target_id, description, url, raw_file, notes, geography="United States", unit="", frequency="", source="", series_id=""): add(target_id, description, None, unit, "", geography, frequency, source, series_id, url, raw_file, notes, status="NOT_RETRIEVED") def save_series(name: str, s: pd.Series, note: str | None = None) -> None: s = s.dropna() df = pd.DataFrame({"date": pd.to_datetime(s.index).strftime("%Y-%m-%d"), "value": s.values}) df.to_csv(SER / f"{name}.csv", index=False) # ---------------------------------------------------------------------------- # FRED helpers # ---------------------------------------------------------------------------- def fred_path(sid: str) -> Path: return RAW / "fred" / f"{sid}.csv" def fred(sid: str) -> pd.Series | None: p = fred_path(sid) if not p.exists(): return None df = pd.read_csv(p) if list(df.columns) != ["observation_date", sid]: raise ValueError(f"unexpected FRED layout in {p}: {df.columns.tolist()}") s = pd.Series(pd.to_numeric(df[sid], errors="coerce").values, index=pd.to_datetime(df["observation_date"]), name=sid) save_series(f"fred_{sid}", s) return s def fred_url(sid: str) -> str: return f"https://fred.stlouisfed.org/graph/fredgraph.csv?id={sid}" def fred_file(sid: str) -> str: return f"data/raw/fred/{sid}.csv" def fred_desc(sid: str) -> str: m = fred_meta.get(sid, {}) return f"{m.get('title', sid)} [{m.get('units', '?')}; {m.get('frequency', '?')}; vintage last updated {m.get('last_updated', '?')}]" def annual_mean(s: pd.Series, full_years_only: bool = True) -> pd.Series: """Calendar-year means. Quarterly series: a year is 'full' with 4 obs, monthly 12, daily >= 240.""" s = s.dropna() g = s.groupby(s.index.year) m, n = g.mean(), g.count() if full_years_only: freq = pd.infer_freq(s.index[:12]) if len(s) >= 12 else None spacing = (s.index[1:] - s.index[:-1]).median().days if len(s) > 2 else 365 # monthly: 11 obs accepted because BLS CPI/average-price series have no October-2025 observation need = 4 if 80 <= spacing <= 95 else 11 if 27 <= spacing <= 32 else 240 if spacing <= 5 else 1 m = m[n >= need] m.index = pd.to_datetime([f"{y}-01-01" for y in m.index]) return m def latest(s: pd.Series): s = s.dropna() return s.index[-1], float(s.iloc[-1]) def period_label(idx, freq: str = "quarterly") -> str: """Label an observation date according to the series frequency.""" ts = pd.Timestamp(idx) f = (freq or "").lower() if "annual" in f: return str(ts.year) if "month" in f: return ts.strftime("%Y-%m") if "dai" in f: return ts.strftime("%Y-%m-%d") return f"{ts.year}Q{(ts.month - 1) // 3 + 1}" def mean_from(s: pd.Series, start_year: int = AVG_START) -> tuple[float, str]: s = s.dropna() s = s[s.index.year >= start_year] return float(s.mean()), f"{s.index[0].year}-{s.index[-1].year} mean of {len(s)} obs" def ratio_targets(tid, desc, num_id, den_id, unit="ratio", scale=1.0, geography="United States", extra_note="", annual=False): """Latest value and AVG_START-latest average of num/den (both FRED). scale multiplies num/den.""" num, den = fred(num_id), fred(den_id) src = "FRED (" + "; ".join(sorted({fred_meta.get(x, {}).get("source", "") for x in (num_id, den_id)} - {""})) + ")" url = fred_url(num_id) + " ; " + fred_url(den_id) rawf = fred_file(num_id) + " ; " + fred_file(den_id) if num is None or den is None: not_retrieved(tid + "_latest", desc + ", latest", url, rawf, "FRED file(s) missing", geography, unit, "", src, f"{num_id}/{den_id}") not_retrieved(tid + "_avg", desc + f", {AVG_START}-latest average", url, rawf, "FRED file(s) missing", geography, unit, "", src, f"{num_id}/{den_id}") return None r = (num / den * scale).dropna() save_series(f"ratio_{num_id}_over_{den_id}", r) freq = "annual" if annual else "quarterly" d, v = latest(r) note = f"{fred_desc(num_id)} divided by {fred_desc(den_id)}. {extra_note}".strip() add(tid + "_latest", desc + ", latest observation", v, unit, period_label(d) if not annual else str(d.year), geography, freq, src, f"{num_id}/{den_id}", url, rawf, note) if annual: mv, mlab = mean_from(r) else: a = annual_mean(r) mv, mlab = mean_from(a) mlab += " (annual means of quarterly ratios)" add(tid + "_avg", desc + f", {AVG_START}-latest average", mv, unit, mlab.split(" mean")[0], geography, "annual average", src, f"{num_id}/{den_id}", url, rawf, note + f" Average: {mlab}.") # also latest full calendar-year average for quarterly series if not annual: a = annual_mean(r) if len(a): add(tid + "_lastfullyear", desc + ", latest full calendar year (mean of quarters)", float(a.iloc[-1]), unit, str(a.index[-1].year), geography, "annual average", src, f"{num_id}/{den_id}", url, rawf, note) return r def level_targets(tid, desc, sid, unit, geography="United States", avg=True, transform=None, extra_note="", annual_from_hf=False): s = fred(sid) src = "FRED (" + fred_meta.get(sid, {}).get("source", "") + ")" if s is None: not_retrieved(tid + "_latest", desc + ", latest", fred_url(sid), fred_file(sid), "FRED file missing", geography, unit, "", src, sid) return None if transform is not None: s = transform(s) d, v = latest(s) note = (fred_desc(sid) + ". " + extra_note).strip() add(tid + "_latest", desc + ", latest observation", v, unit, period_label(d, fred_meta.get(sid, {}).get("frequency", "")), geography, fred_meta.get(sid, {}).get("frequency", ""), src, sid, fred_url(sid), fred_file(sid), note) if annual_from_hf: a = annual_mean(s) if len(a): add(tid + "_lastfullyear", desc + ", latest full calendar-year average", float(a.iloc[-1]), unit, str(a.index[-1].year), geography, "annual average", src, sid, fred_url(sid), fred_file(sid), note) if avg: mv, mlab = mean_from(a) add(tid + "_avg", desc + f", {AVG_START}-latest average of annual means", mv, unit, mlab.split(" mean")[0], geography, "annual average", src, sid, fred_url(sid), fred_file(sid), note + f" Average: {mlab}.") elif avg: mv, mlab = mean_from(s) add(tid + "_avg", desc + f", {AVG_START}-latest average", mv, unit, mlab.split(" mean")[0], geography, fred_meta.get(sid, {}).get("frequency", ""), src, sid, fred_url(sid), fred_file(sid), note + f" Average: {mlab}.") return s # ============================================================================ # 1. Labor share # ============================================================================ level_targets("T01_labor_share_BLS_nonfarm_index", "BLS nonfarm business sector labor share (index 2017=100)", "PRS85006173", "index 2017=100", extra_note="Index, not a level; 2017=100. Use for trends only.", annual_from_hf=True) ratio_targets("T01_labor_share_COE_GDI", "National-accounts labor share: compensation of employees / gross domestic income", "COE", "GDI") ratio_targets("T01_labor_share_COE_NDI", "National-accounts labor share: compensation of employees / net domestic income", "COE", "W256RC1Q027SBEA", extra_note="Net domestic income = GDI less consumption of fixed capital (BEA NIPA table 1.7.5 line 'Net domestic income').") level_targets("T01_labor_share_PWT_US", "Penn World Table labour-compensation share of GDP, United States", "LABSHPUSA156NRUG", "ratio", extra_note="FRED serves PWT 11.0 (1950-2023). The DataverseNL file pwt1001.xlsx (PWT 10.01, to 2019) is kept as cross-check below.") # PWT 10.01 spreadsheet cross-check (labsh) pwt_p = RAW / "pwt" / "pwt1001.xlsx" if pwt_p.exists(): pwt = pd.read_excel(pwt_p, sheet_name="Data", usecols=["countrycode", "year", "labsh"]) for cc, geo, tid in (("USA", "United States", "T01_labor_share_PWT1001_US"), ("CAN", "Canada", "T12_labor_share_PWT1001_CA")): s = pwt[pwt.countrycode == cc].dropna(subset=["labsh"]).set_index("year")["labsh"] s.index = pd.to_datetime([f"{y}-01-01" for y in s.index]) save_series(f"pwt1001_labsh_{cc}", s) d, v = latest(s) mv, mlab = mean_from(s) add(tid + "_latest", f"PWT 10.01 labsh (share of labour compensation in GDP at current national prices), {geo}, latest", v, "ratio", str(d.year), geo, "annual", "Penn World Table 10.01 (Feenstra, Inklaar & Timmer 2015), DataverseNL doi:10.34894/QT5BCC", "labsh", "https://dataverse.nl/api/access/datafile/354095", "data/raw/pwt/pwt1001.xlsx", "CC BY 4.0.") add(tid + "_avg", f"PWT 10.01 labsh, {geo}, {AVG_START}-latest average", mv, "ratio", mlab.split(" mean")[0], geo, "annual", "Penn World Table 10.01", "labsh", "https://dataverse.nl/api/access/datafile/354095", "data/raw/pwt/pwt1001.xlsx", mlab) else: not_retrieved("T01_labor_share_PWT1001_US_latest", "PWT 10.01 labsh USA", "https://dataverse.nl/api/access/datafile/354095", "data/raw/pwt/pwt1001.xlsx", "file missing") # ============================================================================ # 2. Profits, dividends, GDP # ============================================================================ ratio_targets("T02_profits_after_tax_noIVA_GDP", "Corporate profits after tax (without IVA and CCAdj) / GDP", "CP", "GDP", extra_note="FRED id CP is the series WITHOUT IVA/CCAdj (book profits).") ratio_targets("T02_profits_after_tax_IVA_CCAdj_GDP", "Corporate profits after tax with IVA and CCAdj / GDP", "CPATAX", "GDP", extra_note="FRED id CPATAX is the series WITH IVA and CCAdj (economic profits).") ratio_targets("T02_net_dividends_GDP", "Net corporate dividends (NIPA, after tax with IVA & CCAdj) / GDP", "DIVIDEND", "GDP") ratio_targets("T02_net_dividends_annual_GDP", "Net corporate dividend payments (annual NIPA) / GDP (annual)", "B056RC1A027NBEA", "GDPA", annual=True) level_targets("T02_GDP_annual_nominal", "Nominal GDP, annual", "GDPA", "billions of current US dollars", avg=False) level_targets("T02_GDP_quarterly_nominal", "Nominal GDP, quarterly SAAR", "GDP", "billions of current US dollars, SAAR", avg=False) level_targets("T02_GDP_real", "Real GDP, quarterly SAAR", "GDPC1", "billions of chained 2017 dollars, SAAR", avg=False) # real GDP growth 1990-latest (annual means, log growth) g = fred("GDPC1") if g is not None: a = annual_mean(g) a = a[a.index.year >= AVG_START] gr = math.log(a.iloc[-1] / a.iloc[0]) / (a.index[-1].year - a.index[0].year) add("T02_real_GDP_growth_avg", f"Average real GDP growth, {a.index[0].year}-{a.index[-1].year} (log, annual means)", gr, "per year", f"{a.index[0].year}-{a.index[-1].year}", "United States", "annual", "FRED (BEA)", "GDPC1", fred_url("GDPC1"), fred_file("GDPC1"), fred_desc("GDPC1")) # ============================================================================ # 3. Household balance sheet # ============================================================================ ratio_targets("T03_household_net_worth_GDP", "Household & nonprofit net worth / GDP", "TNWBSHNO", "GDP", unit="ratio (years of GDP)", scale=1e-3, extra_note="TNWBSHNO in millions of dollars, GDP in billions SAAR; scaled by 1e-3. Quarterly end-of-period net worth over same-quarter annualized GDP.") ratio_targets("T03_household_real_estate_GDP", "Household & nonprofit real estate at market value / GDP", "HNOREMQ027S", "GDP", unit="ratio (years of GDP)", scale=1e-3, extra_note="Millions over billions SAAR; scaled by 1e-3.") ratio_targets("T03_owners_equity_real_estate_GDP", "Households' owners' equity in real estate / GDP", "OEHRENWBSHNO", "GDP", unit="ratio (years of GDP)", scale=1e-3, extra_note="Millions over billions SAAR; scaled by 1e-3.") level_targets("T03_owners_equity_share_of_real_estate", "Owners' equity as % of household real estate", "HOEREPHRE", "percent", annual_from_hf=True) ratio_targets("T03_real_estate_share_of_net_worth", "Household real estate / household net worth", "HNOREMQ027S", "TNWBSHNO", unit="ratio") # ============================================================================ # 4. Wealth concentration (DFA) # ============================================================================ dfa_p = RAW / "fed_dfa" / "unzipped" / "dfa-networth-shares.csv" dfa_url = "https://www.federalreserve.gov/releases/z1/dataviz/download/zips/dfa.zip" if dfa_p.exists(): dfa = pd.read_csv(dfa_p) w = dfa.pivot(index="Date", columns="Category", values="Net worth") w.index = pd.to_datetime([f"{d[:4]}-{int(d[-1]) * 3 - 2:02d}-01" for d in w.index]) w = w.sort_index() top1 = w["TopPt1"] + w["RemainingTop1"] top10 = top1 + w["Next9"] bot50 = w["Bottom50"] mid = w["Next40"] for name, s, desc in (("top1", top1, "Share of household net worth held by the top 1% (TopPt1 + RemainingTop1)"), ("top10", top10, "Share of household net worth held by the top 10% (top 1% + Next9)"), ("top0p1", w["TopPt1"], "Share of household net worth held by the top 0.1%"), ("next40", mid, "Share of household net worth held by the 50th-90th percentiles"), ("bottom50", bot50, "Share of household net worth held by the bottom 50%")): save_series(f"dfa_networth_share_{name}", s) d, v = latest(s) add(f"T04_wealth_share_{name}_latest", desc + ", latest quarter", v, "percent", period_label(d), "United States", "quarterly", "Federal Reserve Board, Distributional Financial Accounts (dfa-networth-shares.csv, release dated 2026-09-15 in zip)", f"dfa-networth-shares:Net worth:{name}", dfa_url, "data/raw/fed_dfa/unzipped/dfa-networth-shares.csv", "Percent of total household net worth; DFA combine SCF and Financial Accounts. Public domain.") mv, mlab = mean_from(annual_mean(s)) add(f"T04_wealth_share_{name}_avg", desc + f", {AVG_START}-latest average", mv, "percent", mlab.split(" mean")[0], "United States", "annual average", "Federal Reserve Board, Distributional Financial Accounts", f"dfa-networth-shares:Net worth:{name}", dfa_url, "data/raw/fed_dfa/unzipped/dfa-networth-shares.csv", mlab) # corporate-equity concentration (useful for the ownership block) eq = dfa.pivot(index="Date", columns="Category", values="Corporate equities and mutual fund shares") eq.index = w.index eq_top10 = (eq["TopPt1"] + eq["RemainingTop1"] + eq["Next9"]).sort_index() save_series("dfa_corporate_equity_share_top10", eq_top10) d, v = latest(eq_top10) add("T04_corporate_equity_share_top10_latest", "Share of directly/indirectly held corporate equities & mutual fund shares held by the top 10%", v, "percent", period_label(d), "United States", "quarterly", "Federal Reserve Board, Distributional Financial Accounts", "dfa-networth-shares:Corporate equities and mutual fund shares:top10", dfa_url, "data/raw/fed_dfa/unzipped/dfa-networth-shares.csv", "Excludes equities held through DC pensions (DFA definition).") else: for n in ("top1", "top10", "bottom50"): not_retrieved(f"T04_wealth_share_{n}_latest", f"DFA net worth share {n}", dfa_url, "data/raw/fed_dfa/unzipped/dfa-networth-shares.csv", "dfa.zip missing") # FRED cross-checks of the same DFA numbers for sid, tid, desc in (("WFRBST01134", "T04_wealth_share_top1_FRED", "Share of net worth held by the top 1% (FRED mirror of DFA)"), ("WFRBSN09161", "T04_wealth_share_p90_99_FRED", "Share of net worth held by the 90th-99th percentiles (FRED mirror of DFA)"), ("WFRBSN40188", "T04_wealth_share_p50_90_FRED", "Share of net worth held by the 50th-90th percentiles (FRED mirror of DFA)"), ("WFRBSB50215", "T04_wealth_share_bottom50_FRED", "Share of net worth held by the bottom 50% (FRED mirror of DFA)")): level_targets(tid, desc, sid, "percent", avg=False) # ============================================================================ # 5. Investment # ============================================================================ ratio_targets("T05_GPDI_GDP", "Gross private domestic investment / GDP", "GPDI", "GDP") ratio_targets("T05_PNFI_GDP", "Private nonresidential fixed investment / GDP", "PNFI", "GDP") ratio_targets("T05_info_processing_equipment_GDP", "Nonresidential investment in information processing equipment / GDP", "Y034RC1Q027SBEA", "GDP") ratio_targets("T05_software_GDP", "Nonresidential investment in software (IPP) / GDP", "B985RC1Q027SBEA", "GDP") ratio_targets("T05_IPE_and_software_GDP", "Private fixed investment in information processing equipment and software / GDP", "A679RC1Q027SBEA", "GDP") ratio_targets("T05_IPP_GDP", "Nonresidential investment in intellectual property products / GDP", "Y001RC1Q027SBEA", "GDP", extra_note="The brief suggested Y001RC1Q027SBEA as 'total nonresidential'; FRED metadata shows it is the IPP component. PNFI is the total.") # ============================================================================ # 6. Capital stock and depreciation (BEA Fixed Assets, current cost) # ============================================================================ ratio_targets("T06_net_stock_private_fixed_assets_GDP", "Current-cost net stock of private fixed assets (total) / GDP", "K1PTOTL1ES000", "GDPA", unit="ratio (years of GDP)", scale=1e-3, annual=True, extra_note="End-of-year current-cost net stock (millions) over annual nominal GDP (billions); scaled 1e-3.") ratio_targets("T06_net_stock_private_nonres_fixed_assets_GDP", "Current-cost net stock of private nonresidential fixed assets / GDP", "K1NTOTL1ES000", "GDPA", unit="ratio (years of GDP)", scale=1e-3, annual=True) ratio_targets("T06_depreciation_rate_private_total", "Implied depreciation rate: current-cost depreciation / net stock, private fixed assets (total)", "M1PTOTL1ES000", "K1PTOTL1ES000", unit="per year", annual=True, extra_note="Both current-cost, BEA Fixed Assets tables 1.1 and 1.3; depreciation during year t over end-of-year-t net stock (not lagged stock).") ratio_targets("T06_depreciation_rate_private_nonres", "Implied depreciation rate: private nonresidential fixed assets", "M1NTOTL1ES000", "K1NTOTL1ES000", unit="per year", annual=True, extra_note="Current-cost depreciation over end-of-year current-cost net stock, nonresidential.") ratio_targets("T06_CFC_GDP", "Consumption of fixed capital (NIPA, total economy) / GDP", "COFC", "GDP") ratio_targets("T06_CFC_private_GDP", "Consumption of fixed capital: private / GDP (annual)", "A024RC1A027NBEA", "GDPA", annual=True) ratio_targets("T06_CFC_private_over_net_stock", "NIPA private CFC / current-cost net stock of private fixed assets", "A024RC1A027NBEA", "K1PTOTL1ES000", unit="per year", scale=1e3, annual=True, extra_note="Billions over millions, scaled 1e3. Alternative depreciation-rate measure.") # ============================================================================ # 7. Real interest rates # ============================================================================ level_targets("T07_TIPS10_daily", "10-year TIPS constant-maturity real yield", "DFII10", "percent per year", annual_from_hf=True, extra_note="Average over 2003-latest (TIPS data start 2003), not 1990.") level_targets("T07_TIPS10_monthly", "10-year TIPS constant-maturity real yield (monthly)", "FII10", "percent per year", annual_from_hf=True) level_targets("T07_real_rate_10y_Cleveland", "10-year real interest rate (Cleveland Fed model: nominal yield minus expected inflation)", "REAINTRATREARAT10Y", "percent per year", annual_from_hf=True) level_targets("T07_nominal_10y", "10-year Treasury constant-maturity nominal yield (monthly)", "GS10", "percent per year", annual_from_hf=True) # Treasury.gov cross-check (daily par real yield curve 10 YR) tr_files = sorted((RAW / "treasury").glob("daily_treasury_real_yield_curve_*.csv")) if tr_files: frames = [] for f in tr_files: d = pd.read_csv(f) d.columns = [c.strip() for c in d.columns] frames.append(d[["Date", "10 YR"]]) tr = pd.concat(frames) tr["Date"] = pd.to_datetime(tr["Date"], format="%m/%d/%Y") s = pd.Series(pd.to_numeric(tr["10 YR"], errors="coerce").values, index=tr["Date"]).sort_index().dropna() save_series("treasury_real_yield_10y_daily", s) a = annual_mean(s) add("T07_TIPS10_treasury_lastfullyear", "10-year par real yield (US Treasury daily real yield curve), latest full calendar-year average", float(a.iloc[-1]), "percent per year", str(a.index[-1].year), "United States", "annual average", "US Department of the Treasury, Daily Treasury Par Real Yield Curve Rates", "10 YR", "https://home.treasury.gov/resource-center/data-chart-center/interest-rates/daily-treasury-rates.csv//all?type=daily_treasury_real_yield_curve&field_tdr_date_value=&page&_format=csv", "data/raw/treasury/daily_treasury_real_yield_curve_.csv", "Cross-check of DFII10; public domain.") mv, mlab = mean_from(a, 2003) add("T07_TIPS10_treasury_avg", "10-year par real yield (Treasury), 2003-latest average of annual means", mv, "percent per year", mlab.split(" mean")[0], "United States", "annual average", "US Department of the Treasury", "10 YR", "https://home.treasury.gov/resource-center/data-chart-center/interest-rates/", "data/raw/treasury/", mlab) # Cleveland Fed workbook cross-check cf_p = RAW / "clevelandfed" / "inflation-expectations.xlsx" if cf_p.exists(): cf = pd.read_excel(cf_p, sheet_name="Real Interest Rate") cf.columns = [str(c).strip() for c in cf.columns] s = pd.Series(pd.to_numeric(cf["Real Rate 10-year"], errors="coerce").values * 100, index=pd.to_datetime(cf["Model Output Date"])).dropna() save_series("clevelandfed_real_rate_10y_monthly", s) d, v = latest(s) add("T07_real_rate_10y_Cleveland_xlsx_latest", "10-year real rate from the Cleveland Fed inflation-expectations workbook (decimal x100)", v, "percent per year", d.strftime("%Y-%m"), "United States", "monthly", "Federal Reserve Bank of Cleveland, Inflation Expectations model", "Real Rate 10-year", "https://www.clevelandfed.org/-/media/files/webcharts/inflationexpectations/inflation-expectations.xlsx", "data/raw/clevelandfed/inflation-expectations.xlsx", "Cross-check of REAINTRATREARAT10Y. Model of Haubrich, Pennacchi & Ritchken (2012).") ei = pd.read_excel(cf_p, sheet_name="Ten-year Expected Chart") ei.columns = [str(c).strip() for c in ei.columns] s2 = pd.Series(pd.to_numeric(ei["10 year Expected Inflation"], errors="coerce").values, index=pd.to_datetime(ei["Model Output Date"])).dropna() save_series("clevelandfed_expected_inflation_10y_monthly", s2) d, v = latest(s2) add("T07_expected_inflation_10y_Cleveland_latest", "10-year expected inflation (Cleveland Fed model)", v, "percent per year", d.strftime("%Y-%m"), "United States", "monthly", "Federal Reserve Bank of Cleveland", "10 year Expected Inflation", "https://www.clevelandfed.org/-/media/files/webcharts/inflationexpectations/inflation-expectations.xlsx", "data/raw/clevelandfed/inflation-expectations.xlsx", "") # ============================================================================ # 8. Equity valuation (Shiller) # ============================================================================ sh_p = RAW / "shiller" / "ie_data.xls" sh_url = "http://www.econ.yale.edu/~shiller/data/ie_data.xls" if sh_p.exists(): raw = pd.read_excel(sh_p, sheet_name="Data", header=None) hdr_row = raw.index[raw.iloc[:, 0].astype(str).str.strip() == "Date"][0] df = raw.iloc[hdr_row + 1:, :13].copy() df.columns = ["Date", "P", "D", "E", "CPI", "DateFraction", "GS10", "RealP", "RealD", "RealTRPrice", "RealE", "RealTRScaledE", "CAPE"] df = df[pd.to_numeric(df["DateFraction"], errors="coerce").notna()] for c in df.columns: df[c] = pd.to_numeric(df[c], errors="coerce") # month from the date fraction (year + (m-0.5)/12) yr = df["DateFraction"].astype(float) y = np.floor(yr).astype(int) m = np.clip(np.round((yr - y) * 12 + 0.5).astype(int), 1, 12) idx = pd.to_datetime([f"{a}-{b:02d}-01" for a, b in zip(y, m)]) df.index = idx cape = df["CAPE"].dropna() dp = (df["D"] / df["P"]).dropna() ep = (df["E"] / df["P"]).dropna() for name, s in (("cape", cape), ("dividend_yield", dp), ("earnings_yield", ep), ("sp_price", df["P"]), ("sp_dividend", df["D"]), ("sp_earnings", df["E"]), ("cpi", df["CPI"]), ("gs10", df["GS10"])): save_series(f"shiller_{name}", s) vint = f"file ends {df.index[-1].strftime('%Y-%m')} (the Yale copy retrieved on {RETRIEVED} is not more recent)" for tid, s, desc, unit in (("T08_CAPE", cape, "Shiller cyclically adjusted P/E (CAPE, P/E10)", "ratio"), ("T08_dividend_yield", dp, "S&P composite dividend yield D/P (trailing 12-month dividends / price)", "ratio"), ("T08_earnings_yield", ep, "S&P composite earnings yield E/P (trailing 12-month earnings / price)", "ratio")): d, v = latest(s) add(tid + "_latest", desc + ", latest observation in file", v, unit, d.strftime("%Y-%m"), "United States", "monthly", "Robert J. Shiller, online data for 'Irrational Exuberance' (ie_data.xls)", "ie_data.xls Data sheet", sh_url, "data/raw/shiller/ie_data.xls", f"VINTAGE CAVEAT: {vint}. D and E are 12-month trailing sums interpolated monthly by Shiller; P is the monthly average of daily closes.") a = annual_mean(s) mv, mlab = mean_from(a) add(tid + "_avg", desc + f", {AVG_START}-latest average of annual means", mv, unit, mlab.split(" mean")[0], "United States", "annual average", "Robert J. Shiller, online data (ie_data.xls)", "ie_data.xls Data sheet", sh_url, "data/raw/shiller/ie_data.xls", mlab + f". {vint}.") # long-run (1881-latest) CAPE mean as context add("T08_CAPE_longrun_avg", "Shiller CAPE, full-sample mean of annual means", float(annual_mean(cape).mean()), "ratio", f"{annual_mean(cape).index[0].year}-{annual_mean(cape).index[-1].year}", "United States", "annual average", "Robert J. Shiller, online data (ie_data.xls)", "ie_data.xls Data sheet", sh_url, "data/raw/shiller/ie_data.xls", vint) else: for t in ("T08_CAPE_latest", "T08_dividend_yield_latest", "T08_earnings_yield_latest"): not_retrieved(t, t, sh_url, "data/raw/shiller/ie_data.xls", "ie_data.xls missing") # ============================================================================ # 9. Housing # ============================================================================ f31_p = RAW / "shiller" / "Fig3-1.xls" f31_url = "http://www.econ.yale.edu/~shiller/data/Fig3-1.xls" if f31_p.exists(): raw = pd.read_excel(f31_p, header=None) hdr_row = raw.index[raw.iloc[:, 0].astype(str).str.strip() == "Date"][0] dates = pd.to_numeric(raw.iloc[hdr_row + 1:, 0], errors="coerce") vals = pd.to_numeric(raw.iloc[hdr_row + 1:, 1], errors="coerce") ok = dates.notna() & vals.notna() yrs = dates[ok].astype(float).values yy = np.floor(yrs + 1e-9).astype(int) mm = np.clip(np.round((yrs - yy) * 12 + 1).astype(int), 1, 12) # annual rows (integer years) -> January idx = pd.to_datetime([f"{a}-{b:02d}-01" for a, b in zip(yy, mm)]) rhpi = pd.Series(vals[ok].values, index=idx).sort_index() rhpi = rhpi[~rhpi.index.duplicated()] save_series("shiller_real_home_price_index", rhpi) d, v = latest(rhpi) add("T09_real_home_price_index_latest", "Shiller US real home price index (1890=100), latest observation in file", v, "index 1890=100", d.strftime("%Y-%m"), "United States", "monthly (annual before 1953)", "Robert J. Shiller, Irrational Exuberance Fig. 3.1 data (Fig3-1.xls)", "Fig3-1.xls col B", f31_url, "data/raw/shiller/Fig3-1.xls", f"VINTAGE CAVEAT: file ends {d.strftime('%Y-%m')}. Nominal index = Case-Shiller national (S&P/CoreLogic) spliced to Grebler/PHCPI; deflated by CPI by Shiller.") a = annual_mean(rhpi, full_years_only=False) for (y0, tid) in ((AVG_START, "T09_real_home_price_growth_1990"), (1890, "T09_real_home_price_growth_1890")): aa = a[a.index.year >= y0] gr = math.log(aa.iloc[-1] / aa.iloc[0]) / (aa.index[-1].year - aa.index[0].year) add(tid, f"Average real home price growth (log, annual means), {aa.index[0].year}-{aa.index[-1].year}", gr, "per year", f"{aa.index[0].year}-{aa.index[-1].year}", "United States", "annual", "Robert J. Shiller, Fig3-1.xls", "Fig3-1.xls col B", f31_url, "data/raw/shiller/Fig3-1.xls", "Computed from annual means of the real index.") else: not_retrieved("T09_real_home_price_index_latest", "Shiller real home price index", f31_url, "data/raw/shiller/Fig3-1.xls", "file missing") # Rent/price proxy: CPI rent of primary residence over Case-Shiller national index, both rebased to Jan-2000 = 100 hp, rent, cpi = fred("CSUSHPISA"), fred("CUSR0000SEHA"), fred("CPIAUCSL") if hp is not None and rent is not None: base = pd.Timestamp("2000-01-01") rp = (rent / rent[base]) / (hp / hp[base]) * 100 rp = rp.dropna() save_series("rent_price_proxy_index_2000eq100", rp) d, v = latest(rp) note = ("PROXY ONLY: index of (CPI rent of primary residence) / (Case-Shiller national HPI), both rebased to Jan 2000 = 100. " "It tracks the change in the rent-price ratio relative to 2000, not its level; no key-less level series of the rent-price ratio was available.") add("T09_rent_price_ratio_proxy_latest", "Rent-price ratio proxy index (2000=100), latest", v, "index Jan-2000=100", d.strftime("%Y-%m"), "United States", "monthly", "FRED (BLS CUSR0000SEHA; S&P Dow Jones Indices CSUSHPISA)", "CUSR0000SEHA/CSUSHPISA", fred_url("CUSR0000SEHA") + " ; " + fred_url("CSUSHPISA"), fred_file("CUSR0000SEHA") + " ; " + fred_file("CSUSHPISA"), note) a = annual_mean(rp) mv, mlab = mean_from(a) add("T09_rent_price_ratio_proxy_avg", f"Rent-price ratio proxy index (2000=100), {AVG_START}-latest average", mv, "index Jan-2000=100", mlab.split(" mean")[0], "United States", "annual average", "FRED (BLS; S&P Dow Jones Indices)", "CUSR0000SEHA/CSUSHPISA", fred_url("CUSR0000SEHA") + " ; " + fred_url("CSUSHPISA"), fred_file("CUSR0000SEHA") + " ; " + fred_file("CSUSHPISA"), note + " " + mlab) # rent inflation vs house price inflation 1990-latest for sid, s, tid, desc in (("CUSR0000SEHA", rent, "T09_rent_cpi_growth_1990", "Average growth of CPI rent of primary residence"), ("CSUSHPISA", hp, "T09_case_shiller_growth_1990", "Average growth of Case-Shiller national home price index (nominal)")): a = annual_mean(s) a = a[a.index.year >= AVG_START] gr = math.log(a.iloc[-1] / a.iloc[0]) / (a.index[-1].year - a.index[0].year) add(tid, f"{desc} (log, annual means), {a.index[0].year}-{a.index[-1].year}", gr, "per year", f"{a.index[0].year}-{a.index[-1].year}", "United States", "annual", "FRED (" + fred_meta.get(sid, {}).get("source", "") + ")", sid, fred_url(sid), fred_file(sid), fred_desc(sid)) if cpi is not None: real_hp = (hp / cpi).dropna() a = annual_mean(real_hp) a = a[a.index.year >= AVG_START] gr = math.log(a.iloc[-1] / a.iloc[0]) / (a.index[-1].year - a.index[0].year) add("T09_case_shiller_real_growth_1990", f"Average real growth of Case-Shiller national HPI deflated by CPI-U (log, annual means), {a.index[0].year}-{a.index[-1].year}", gr, "per year", f"{a.index[0].year}-{a.index[-1].year}", "United States", "annual", "FRED (S&P Dow Jones Indices; BLS)", "CSUSHPISA/CPIAUCSL", fred_url("CSUSHPISA") + " ; " + fred_url("CPIAUCSL"), fred_file("CSUSHPISA") + " ; " + fred_file("CPIAUCSL"), "") else: not_retrieved("T09_rent_price_ratio_proxy_latest", "Rent-price proxy", fred_url("CUSR0000SEHA"), fred_file("CUSR0000SEHA"), "FRED files missing") # ============================================================================ # 10. Energy: data centers (LBNL) and total US electricity (EIA) # ============================================================================ lb_p = RAW / "lbnl" / "lbnl-2024-united-states-data-center-energy-usage-report.pdf" lb_entry = manifest.get("lbnl:report_pdf", {}) lb_url = lb_entry.get("url", "https://eta-publications.lbl.gov/sites/default/files/2024-12/lbnl-2024-united-states-data-center-energy-usage-report.pdf") lb_src = "Shehabi, A., Newkirk, A., Smith, S.J., et al. (2024), 2024 United States Data Center Energy Usage Report, LBNL-2001637, Lawrence Berkeley National Laboratory (DOI 10.71468/P1WC7Q)" if lb_p.exists(): import fitz # PyMuPDF doc = fitz.open(lb_p) pages = {i + 1: doc[i].get_text() for i in range(doc.page_count)} def find(pattern, want_pages): for p in want_pages: t = " ".join(pages[p].split()) m = re.search(pattern, t) if m: return m, p return None, None def printed_page(p): m = re.search(r"Energy Usage Report (\d+)", pages[p].split("\n")[0] + " " + pages[p][:120]) return m.group(1) if m else "?" m, p = find(r"reaching (\d+) TWh by 2023, representing ([\d.]+)% of total U\.S\. electricity consumption", [7, 53]) if m: add("T10_datacenter_electricity_2023_TWh", "US data center electricity use, 2023", float(m.group(1)), "TWh", "2023", "United States", "annual", lb_src, "text", lb_url, "data/raw/lbnl/lbnl-2024-united-states-data-center-energy-usage-report.pdf", f"Quote (PDF page {p}, printed page {printed_page(p)}): '...{m.group(0)}'. Includes servers, storage, network and infrastructure (PUE), bottom-up model.") add("T10_datacenter_share_of_US_electricity_2023", "US data center electricity use as share of total US electricity consumption, 2023", float(m.group(2)), "percent", "2023", "United States", "annual", lb_src, "text", lb_url, "data/raw/lbnl/lbnl-2024-united-states-data-center-energy-usage-report.pdf", f"Quote (PDF page {p}, printed page {printed_page(p)}): '{m.group(0)}'.") m, p = find(r"by 2018 data centers consumed about (\d+) TWh, representing ([\d.]+)% of total (?:annual )?U\.S\. electricity consumption", [7, 53]) if m: add("T10_datacenter_electricity_2018_TWh", "US data center electricity use, 2018", float(m.group(1)), "TWh", "2018", "United States", "annual", lb_src, "text", lb_url, "data/raw/lbnl/lbnl-2024-united-states-data-center-energy-usage-report.pdf", f"Quote (PDF page {p}, printed page {printed_page(p)}): '{m.group(0)}'.") add("T10_datacenter_share_of_US_electricity_2018", "US data center share of total US electricity consumption, 2018", float(m.group(2)), "percent", "2018", "United States", "annual", lb_src, "text", lb_url, "data/raw/lbnl/lbnl-2024-united-states-data-center-energy-usage-report.pdf", f"Quote (PDF page {p}, printed page {printed_page(p)}).") m, p = find(r"low and high end (?:of roughly|representing about) (\d+) and (\d+) TWh in 2028", [8, 53]) if m: add("T10_datacenter_electricity_2028_low_TWh", "US data center electricity use, 2028 projection, low scenario", float(m.group(1)), "TWh", "2028", "United States", "annual (projection)", lb_src, "text", lb_url, "data/raw/lbnl/lbnl-2024-united-states-data-center-energy-usage-report.pdf", f"Quote (PDF page {p}, printed page {printed_page(p)}): '{m.group(0)}'. Scenario range, not a forecast.") add("T10_datacenter_electricity_2028_high_TWh", "US data center electricity use, 2028 projection, high scenario", float(m.group(2)), "TWh", "2028", "United States", "annual (projection)", lb_src, "text", lb_url, "data/raw/lbnl/lbnl-2024-united-states-data-center-energy-usage-report.pdf", f"Quote (PDF page {p}, printed page {printed_page(p)}).") m, p = find(r"([\d.]+)% to ([\d.]+)% of total U\.S\. electricity consumption", [8, 53]) if m: add("T10_datacenter_share_2028_low", "US data center share of total US electricity consumption, 2028 projection, low", float(m.group(1)), "percent", "2028", "United States", "annual (projection)", lb_src, "text", lb_url, "data/raw/lbnl/lbnl-2024-united-states-data-center-energy-usage-report.pdf", f"Quote (PDF page {p}, printed page {printed_page(p)}): '{m.group(0)}'.") add("T10_datacenter_share_2028_high", "US data center share of total US electricity consumption, 2028 projection, high", float(m.group(2)), "percent", "2028", "United States", "annual (projection)", lb_src, "text", lb_url, "data/raw/lbnl/lbnl-2024-united-states-data-center-energy-usage-report.pdf", f"Quote (PDF page {p}, printed page {printed_page(p)}).") else: not_retrieved("T10_datacenter_electricity_2023_TWh", "US data center electricity use 2023", lb_url, "data/raw/lbnl/", "PDF missing (LBNL host returned HTTP 202 challenge page; eScholarship mirror not retrieved)") eia_p = RAW / "eia" / "epa_01_01.xlsx" eia_url = "https://www.eia.gov/electricity/annual/xls/epa_01_01.xlsx" if eia_p.exists(): t = pd.read_excel(eia_p, header=None) hdr = t.index[t.iloc[:, 0].astype(str).str.strip() == "Fuel"][0] years = [str(t.iloc[hdr, 2]).replace("Year ", ""), str(t.iloc[hdr, 3]).replace("Year ", "")] row = t.index[(t.iloc[:, 0].astype(str).str.strip() == "All Energy Sources") & (t.iloc[:, 1].astype(str).str.contains("Utility Scale"))][0] for j, y in enumerate(years): add(f"T10_US_net_generation_{y}_TWh", f"US net electricity generation, all sectors, utility-scale facilities, {y}", float(t.iloc[row, 2 + j]) / 1e3, "TWh", y, "United States", "annual", "EIA, Electric Power Annual / Electric Power Monthly Table 1.1 (Total Electric Power Industry Summary Statistics)", "All Energy Sources, Utility Scale Facilities, Net Generation (thousand MWh)", eia_url, "data/raw/eia/epa_01_01.xlsx", "Thousand megawatthours converted to TWh (/1000). Public domain. Excludes small-scale solar (reported separately in the table).") row2 = t.index[(t.iloc[:, 0].astype(str).str.strip() == "All Sectors")] if len(row2): r2 = row2[0] hdr2 = t.index[(t.iloc[:, 0].astype(str).str.strip() == "Sector")][0] years2 = [str(t.iloc[hdr2, 1]).replace("Year ", ""), str(t.iloc[hdr2, 2]).replace("Year ", "")] for j, y in enumerate(years2): v = float(t.iloc[r2, 1 + j]) / 1e3 add(f"T10_US_retail_sales_{y}_TWh", f"US retail sales of electricity to ultimate customers, all sectors, {y}", v, "TWh", y, "United States", "annual", "EIA, Electric Power Annual Table 1.1 (Sales of Electricity to Ultimate Customers, thousand MWh)", "All Sectors retail sales", eia_url, "data/raw/eia/epa_01_01.xlsx", "Thousand MWh / 1000. Retail sales approximate end-use consumption (exclude direct use and losses).") else: not_retrieved("T10_US_net_generation_latest_TWh", "US net generation", eia_url, "data/raw/eia/epa_01_01.xlsx", "file missing") # ============================================================================ # 11. Compute price-performance (Epoch AI hardware dataset) # ============================================================================ ep_p = RAW / "epoch" / "ml_hardware.csv" ep_url = "https://epoch.ai/data/ml_hardware.csv" if ep_p.exists(): e = pd.read_csv(ep_p) e["Release date"] = pd.to_datetime(e["Release date"], errors="coerce") e["pp"] = pd.to_numeric(e["Price-performance"], errors="coerce") e["price"] = pd.to_numeric(e["Release price (USD)"], errors="coerce") e["maxperf"] = pd.to_numeric(e["Max performance"], errors="coerce") sub = e.dropna(subset=["Release date", "pp"]).copy() sub["t"] = sub["Release date"].dt.year + (sub["Release date"].dt.dayofyear - 1) / 365.25 sub["log2pp"] = np.log2(sub["pp"]) sub[["Hardware name", "Manufacturer", "Type", "Release date", "price", "maxperf", "pp", "t", "log2pp"]].sort_values("Release date").to_csv(PROC / "epoch_price_performance_fit.csv", index=False) def fit(df): X = np.vstack([np.ones(len(df)), df["t"].values]).T b, res, *_ = np.linalg.lstsq(X, df["log2pp"].values, rcond=None) slope = b[1] yhat = X @ b r2 = 1 - ((df["log2pp"] - yhat) ** 2).sum() / ((df["log2pp"] - df["log2pp"].mean()) ** 2).sum() return slope, 1 / slope, r2 for tid, df_, lab in (("T11_compute_price_performance_doubling_all", sub, "all hardware with a price and price-performance entry"), ("T11_compute_price_performance_doubling_2015plus", sub[sub["t"] >= 2015], "hardware released 2015 or later")): if len(df_) >= 5: slope, dbl, r2 = fit(df_) add(tid, f"Doubling time of FLOP/s per US dollar (Epoch 'Price-performance' column), OLS of log2(price-performance) on release year, {lab}", dbl, "years per doubling", f"{int(df_['t'].min())}-{int(df_['t'].max())}", "global (hardware market)", "cross-section of hardware releases", "Epoch AI, 'Machine Learning Hardware' dataset (https://epoch.ai/data/machine-learning-hardware), CC BY 4.0", "Price-performance; Release date", ep_url, "data/raw/epoch/ml_hardware.csv", f"n={len(df_)}, slope={slope:.3f} doublings/yr (x{2**slope:.2f}/yr), R2={r2:.2f}. Price-performance = Epoch's 'Max performance' FLOP/s (best available precision) over release price; our own OLS, not Epoch's published estimate. Last modified field max: {e['Last modified'].max() if 'Last modified' in e else '?'}.") else: not_retrieved(tid, "Epoch price-performance doubling time", ep_url, "data/raw/epoch/ml_hardware.csv", "too few rows") add("T11_compute_price_performance_caveat", "Caveat for T11: Epoch's 'Max performance' uses the highest-throughput precision available for each chip (FP32 for pre-2017 GPUs, tensor FP16/BF16 or lower for recent accelerators), so part of the trend reflects precision changes, not only hardware progress", float("nan"), "", "", "global", "", "Epoch AI", "Max performance", ep_url, "data/raw/epoch/ml_hardware.csv", "Informational row; no value.", status="INFO") add("T11_compute_price_performance_n", "Number of hardware entries with release price and price-performance", float(len(sub)), "count", f"{int(sub['t'].min())}-{int(sub['t'].max())}", "global", "", "Epoch AI", "Price-performance", ep_url, "data/raw/epoch/ml_hardware.csv", "") else: not_retrieved("T11_compute_price_performance_doubling_all", "Epoch price-performance doubling time", ep_url, "data/raw/epoch/ml_hardware.csv", "file missing") # ============================================================================ # 12. Canada # ============================================================================ level_targets("T12_labor_share_PWT_CA", "Penn World Table labour-compensation share of GDP, Canada", "LABSHPCAA156NRUG", "ratio", geography="Canada") sc1 = RAW / "statcan" / "unzipped" / "36100103.csv" sc2 = RAW / "statcan" / "unzipped" / "36100580.csv" sc_lic = "Statistics Canada Open Licence (https://www.statcan.gc.ca/en/reference/licence)." if sc1.exists(): d = pd.read_csv(sc1, usecols=["REF_DATE", "GEO", "Seasonal adjustment", "Estimates", "VALUE", "SCALAR_FACTOR"], low_memory=False) d = d[(d["GEO"] == "Canada") & (d["Seasonal adjustment"] == "Seasonally adjusted at annual rates")] piv = d.pivot_table(index="REF_DATE", columns="Estimates", values="VALUE") piv.index = pd.to_datetime(piv.index + "-01") coe, gdp = piv["Compensation of employees"], piv["Gross domestic product at market prices"] ls = (coe / gdp).dropna() save_series("statcan_36100103_COE_over_GDP_quarterly", ls) save_series("statcan_36100103_GDP_market_prices_SAAR", gdp) d_, v = latest(ls) add("T12_labor_share_COE_GDP_CA_latest", "Canada: compensation of employees / GDP at market prices (income-based, SAAR)", v, "ratio", period_label(d_), "Canada", "quarterly", "Statistics Canada, Table 36-10-0103-01 (GDP income-based, quarterly)", "Compensation of employees / Gross domestic product at market prices", "https://www150.statcan.gc.ca/n1/tbl/csv/36100103-eng.zip", "data/raw/statcan/unzipped/36100103.csv", "Both in millions of dollars SAAR. " + sc_lic) mv, mlab = mean_from(annual_mean(ls)) add("T12_labor_share_COE_GDP_CA_avg", f"Canada: compensation of employees / GDP, {AVG_START}-latest average", mv, "ratio", mlab.split(" mean")[0], "Canada", "annual average", "Statistics Canada, Table 36-10-0103-01", "COE/GDP", "https://www150.statcan.gc.ca/n1/tbl/csv/36100103-eng.zip", "data/raw/statcan/unzipped/36100103.csv", mlab + ". " + sc_lic) if sc2.exists(): b = pd.read_csv(sc2, usecols=["REF_DATE", "Sectors", "Valuation", "Categories", "VALUE"], low_memory=False) b = b[(b["Sectors"] == "Households and non-profit institutions serving households") & (b["Valuation"] == "Market value") & (b["Categories"] == "Net worth")] nw = pd.Series(b["VALUE"].values, index=pd.to_datetime(b["REF_DATE"] + "-01")).sort_index() save_series("statcan_36100580_household_NPISH_net_worth_market_value", nw) r = (nw / gdp).dropna() save_series("statcan_household_net_worth_over_GDP", r) d_, v = latest(r) add("T12_household_net_worth_GDP_CA_latest", "Canada: households & NPISH net worth (market value) / GDP at market prices (SAAR)", v, "ratio (years of GDP)", period_label(d_), "Canada", "quarterly", "Statistics Canada, Tables 36-10-0580-01 (national balance sheet) and 36-10-0103-01 (GDP)", "Net worth (Households and NPISH, market value) / GDP", "https://www150.statcan.gc.ca/n1/tbl/csv/36100580-eng.zip ; https://www150.statcan.gc.ca/n1/tbl/csv/36100103-eng.zip", "data/raw/statcan/unzipped/36100580.csv ; data/raw/statcan/unzipped/36100103.csv", "Both in millions of CAD; end-of-quarter net worth over same-quarter annualized GDP. " + sc_lic) mv, mlab = mean_from(annual_mean(r)) add("T12_household_net_worth_GDP_CA_avg", f"Canada: household net worth / GDP, {AVG_START}-latest average", mv, "ratio (years of GDP)", mlab.split(" mean")[0], "Canada", "annual average", "Statistics Canada, Tables 36-10-0580-01 and 36-10-0103-01", "Net worth / GDP", "https://www150.statcan.gc.ca/n1/tbl/csv/36100580-eng.zip", "data/raw/statcan/unzipped/36100580.csv", mlab + ". " + sc_lic) else: not_retrieved("T12_household_net_worth_GDP_CA_latest", "Canada household net worth / GDP", "https://www150.statcan.gc.ca/n1/tbl/csv/36100580-eng.zip", "data/raw/statcan/unzipped/36100580.csv", "file missing", geography="Canada") else: not_retrieved("T12_labor_share_COE_GDP_CA_latest", "Canada COE/GDP", "https://www150.statcan.gc.ca/n1/tbl/csv/36100103-eng.zip", "data/raw/statcan/unzipped/36100103.csv", "file missing", geography="Canada") # ============================================================================ # 13. Electricity price # ============================================================================ level_targets("T13_electricity_price_kWh", "Average price of electricity per kWh, US city average (BLS average price data)", "APU000072610", "US dollars per kWh", annual_from_hf=True) # ============================================================================ # BEA Input-Output: 2023 summary use table -> sector labor shares, intermediate shares # ============================================================================ bea_p = RAW / "bea_io" / "unzipped" / "IOUse_After_Redefinitions_PRO_1997-2023_Summary.xlsx" bea_url = "https://apps.bea.gov/industry/iTables%20Static%20Files/AllTablesIO.zip" if bea_p.exists(): u = pd.read_excel(bea_p, sheet_name="2023", header=None) code_row = u.index[u.iloc[:, 1].astype(str).str.strip() == "Commodities/Industries"][0] name_row = code_row + 1 codes = u.iloc[code_row, 2:].astype(str).str.strip() names = u.iloc[name_row, 2:].astype(str).str.strip() # industry columns = those before the first final-use column (codes starting with 'F') ind_mask = ~codes.str.startswith("F") & (codes != "nan") & ~names.str.contains("Total", na=False) first_label = u.iloc[:, 1].astype(str).str.strip() def row_vals(label): i = u.index[first_label == label][0] return pd.to_numeric(u.iloc[i, 2:], errors="coerce") V = {k: row_vals(k) for k in ("Compensation of employees", "Taxes on production and imports, less subsidies", "Gross operating surplus", "Total Value Added", "Total Intermediate", "Total Industry Output")} out = pd.DataFrame({"industry_code": codes.values, "industry_name": names.values, "compensation_of_employees": V["Compensation of employees"].values, "taxes_less_subsidies": V["Taxes on production and imports, less subsidies"].values, "gross_operating_surplus": V["Gross operating surplus"].values, "total_value_added": V["Total Value Added"].values, "total_intermediate": V["Total Intermediate"].values, "total_industry_output": V["Total Industry Output"].values})[ind_mask.values] out = out.dropna(subset=["total_value_added"]) out["labor_share_of_value_added"] = out["compensation_of_employees"] / out["total_value_added"] out["labor_share_of_factor_cost_VA"] = out["compensation_of_employees"] / (out["compensation_of_employees"] + out["gross_operating_surplus"]) out["intermediate_share_of_gross_output"] = out["total_intermediate"] / out["total_industry_output"] out["value_added_share_of_total"] = out["total_value_added"] / out["total_value_added"].sum() out.to_csv(PROC / "bea_io_value_added_2023.csv", index=False) tot_va, tot_coe, tot_gos = out["total_value_added"].sum(), out["compensation_of_employees"].sum(), out["gross_operating_surplus"].sum() note = "Summary-level use table after redefinitions, producers' prices, millions of current dollars, 2023 (file dated 2024-09-06 inside AllTablesIO.zip). Sum over all 71 summary industries (private + government). Public domain." add("T01_labor_share_BEA_IO_2023_VA", "Compensation of employees / total value added, all industries, BEA use table 2023", tot_coe / tot_va, "ratio", "2023", "United States", "annual", "BEA Input-Output Accounts, IOUse_After_Redefinitions_PRO_1997-2023_Summary.xlsx", "V001/Total Value Added", bea_url, "data/raw/bea_io/unzipped/IOUse_After_Redefinitions_PRO_1997-2023_Summary.xlsx", note) add("T01_labor_share_BEA_IO_2023_factor_cost", "Compensation / (compensation + gross operating surplus), all industries, BEA use table 2023", tot_coe / (tot_coe + tot_gos), "ratio", "2023", "United States", "annual", "BEA Input-Output Accounts (summary use table 2023)", "V001/(V001+V003)", bea_url, "data/raw/bea_io/unzipped/IOUse_After_Redefinitions_PRO_1997-2023_Summary.xlsx", note + " Gross operating surplus includes CFC and proprietors' income.") add("T01_intermediate_share_BEA_IO_2023", "Total intermediate inputs / total industry output, all industries, BEA use table 2023", out["total_intermediate"].sum() / out["total_industry_output"].sum(), "ratio", "2023", "United States", "annual", "BEA Input-Output Accounts (summary use table 2023)", "Total Intermediate/Total Industry Output", bea_url, "data/raw/bea_io/unzipped/IOUse_After_Redefinitions_PRO_1997-2023_Summary.xlsx", note + " Per-industry values in data/processed/bea_io_value_added_2023.csv.") # a few sectors of direct interest to the model for code, tid in (("22", "T01_labor_share_BEA_IO_2023_utilities"), ("5415", "T01_labor_share_BEA_IO_2023_computer_systems_design"), ("513", "T01_labor_share_BEA_IO_2023_publishing_incl_software"), ("334", "T01_labor_share_BEA_IO_2023_computer_electronic_products"), ("531", "T01_labor_share_BEA_IO_2023_real_estate"), ("23", "T01_labor_share_BEA_IO_2023_construction"), ("5412OP", "T01_labor_share_BEA_IO_2023_professional_services"), ("621", "T01_labor_share_BEA_IO_2023_ambulatory_health")): r = out[out["industry_code"] == code] if len(r): r = r.iloc[0] add(tid, f"Labor share of value added, BEA summary industry {code} ({r['industry_name']}), 2023", float(r["labor_share_of_value_added"]), "ratio", "2023", "United States", "annual", "BEA Input-Output Accounts (summary use table 2023)", f"V001/Total Value Added, column {code}", bea_url, "data/raw/bea_io/unzipped/IOUse_After_Redefinitions_PRO_1997-2023_Summary.xlsx", f"Compensation {r['compensation_of_employees']:.0f} / VA {r['total_value_added']:.0f} ($mn).") else: not_retrieved("T01_labor_share_BEA_IO_2023_VA", "BEA IO labor share 2023", bea_url, "data/raw/bea_io/", "AllTablesIO.zip missing") # ============================================================================ # BLS API cross-checks (independent retrieval path for PRS85006173, APU000072610, CUSR0000SEHA) # ============================================================================ for sid, desc in (("PRS85006173", "BLS nonfarm business labor share index (BLS API v1)"), ("APU000072610", "Electricity price per kWh (BLS API v1)"), ("CUSR0000SEHA", "CPI rent of primary residence (BLS API v1)")): files = sorted((RAW / "bls").glob(f"{sid}_*.json")) if not files: continue recs = [] for f in files: js = json.loads(f.read_text()) for srs in js.get("Results", {}).get("series", []): for o in srs.get("data", []): p = o["period"] if p.startswith("M") and p != "M13": dt = f"{o['year']}-{int(p[1:]):02d}-01" elif p.startswith("Q") and p != "Q05": dt = f"{o['year']}-{(int(p[1:]) - 1) * 3 + 1:02d}-01" else: continue try: recs.append((dt, float(o["value"]))) except ValueError: # BLS uses '-' for missing continue s = pd.Series(dict(recs)) s.index = pd.to_datetime(s.index) s = s.sort_index() save_series(f"bls_api_{sid}", s) d, v = latest(s) add(f"T00_crosscheck_BLS_API_{sid}_latest", desc + ", latest", v, fred_meta.get(sid, {}).get("units", ""), period_label(d, fred_meta.get(sid, {}).get("frequency", "")), "United States", fred_meta.get(sid, {}).get("frequency", ""), "U.S. Bureau of Labor Statistics public API v1 (key-less)", sid, "https://api.bls.gov/publicAPI/v1/timeseries/data/ (POST, 10-year windows)", f"data/raw/bls/{sid}_.json", "Independent retrieval path; should equal the FRED value for the same date.") # ---------------------------------------------------------------------------- PROC.mkdir(parents=True, exist_ok=True) with open(PROC / "calibration_targets.csv", "w", newline="") as f: w = csv.DictWriter(f, fieldnames=COLS) w.writeheader() for r in ROWS: w.writerow(r) n_ok = sum(r["status"] == "RETRIEVED" for r in ROWS) n_nr = sum(r["status"] == "NOT_RETRIEVED" for r in ROWS) print(f"calibration_targets.csv: {len(ROWS)} rows, {n_ok} RETRIEVED, {n_nr} NOT_RETRIEVED") for r in ROWS: if r["status"] == "NOT_RETRIEVED": print(" NOT_RETRIEVED:", r["target_id"], "-", r["notes"]) print(f"series written: {len(list(SER.glob('*.csv')))}")