414 lines
15 KiB
Python
414 lines
15 KiB
Python
"""
|
|
FRED historical bootstrap — fetches monthly/quarterly/weekly release data
|
|
from FRED's public CSV endpoint (no API key required) and stores it in
|
|
the economic_events table with rolling z-score surprises.
|
|
"""
|
|
import logging
|
|
from datetime import date
|
|
from typing import Any, Dict, List, Optional, Tuple
|
|
|
|
import httpx
|
|
import pandas as pd
|
|
|
|
logger = logging.getLogger(__name__)
|
|
|
|
# ── Series catalog ────────────────────────────────────────────────────────────
|
|
#
|
|
# transform options:
|
|
# None — store raw FRED value as-is
|
|
# "yoy_pct" — compute year-over-year %: (val / val_12m_ago - 1) * 100
|
|
# "qoq_annualized"— compute annualized QoQ growth: ((val/val_prev)^4 - 1)*100
|
|
# "div1000" — divide by 1000 (FRED gives raw count, display in K)
|
|
#
|
|
# delta_absolute: if True, surprise_pct = val - prev (absolute pp change)
|
|
# if False, surprise_pct = (val-prev)/|prev| * 100 (% change)
|
|
|
|
FRED_SERIES: Dict[str, Dict[str, Any]] = {
|
|
"PAYEMS": {
|
|
"name": "Non-Farm Payrolls",
|
|
"unit": "K",
|
|
"freq": "monthly",
|
|
"category": "employment",
|
|
"ff_currency": "USD",
|
|
"higher_is_bullish": True,
|
|
"assets": ["SPY", "QQQ", "EURUSD=X", "TLT"],
|
|
"zscore_window": 12,
|
|
"transform": None,
|
|
"delta_absolute": False,
|
|
},
|
|
"ADP_NFP": {
|
|
"name": "ADP Non-Farm Employment",
|
|
"unit": "K",
|
|
"freq": "monthly",
|
|
"category": "employment",
|
|
"ff_currency": "USD",
|
|
"higher_is_bullish": True,
|
|
"assets": ["SPY", "QQQ"],
|
|
"zscore_window": 12,
|
|
"transform": None,
|
|
"delta_absolute": False,
|
|
"ff_only": True,
|
|
},
|
|
"UNRATE": {
|
|
"name": "Unemployment Rate",
|
|
"unit": "%",
|
|
"freq": "monthly",
|
|
"category": "employment",
|
|
"ff_currency": "USD",
|
|
"higher_is_bullish": False,
|
|
"assets": ["SPY", "QQQ", "EURUSD=X"],
|
|
"zscore_window": 12,
|
|
"transform": None,
|
|
"delta_absolute": True,
|
|
},
|
|
"CPIAUCSL": {
|
|
"name": "CPI All Items (YoY %)",
|
|
"unit": "%",
|
|
"freq": "monthly",
|
|
"category": "inflation",
|
|
"ff_currency": "USD",
|
|
"higher_is_bullish": False,
|
|
"assets": ["TLT", "GLD", "EURUSD=X", "SPY"],
|
|
"zscore_window": 12,
|
|
"transform": "yoy_pct",
|
|
"delta_absolute": True,
|
|
},
|
|
"CPILFESL": {
|
|
"name": "Core CPI (YoY %)",
|
|
"unit": "%",
|
|
"freq": "monthly",
|
|
"category": "inflation",
|
|
"ff_currency": "USD",
|
|
"higher_is_bullish": False,
|
|
"assets": ["TLT", "GLD", "EURUSD=X"],
|
|
"zscore_window": 12,
|
|
"transform": "yoy_pct",
|
|
"delta_absolute": True,
|
|
},
|
|
"PCEPILFE": {
|
|
"name": "Core PCE (YoY %)",
|
|
"unit": "%",
|
|
"freq": "monthly",
|
|
"category": "inflation",
|
|
"ff_currency": "USD",
|
|
"higher_is_bullish": False,
|
|
"assets": ["TLT", "GLD", "SPY"],
|
|
"zscore_window": 12,
|
|
"transform": "yoy_pct",
|
|
"delta_absolute": True,
|
|
},
|
|
"FEDFUNDS": {
|
|
"name": "Fed Funds Rate (FOMC)",
|
|
"unit": "%",
|
|
"freq": "monthly",
|
|
"category": "monetary",
|
|
"ff_currency": "USD",
|
|
"higher_is_bullish": False,
|
|
"assets": ["TLT", "SPY", "EURUSD=X", "GLD"],
|
|
"zscore_window": 12,
|
|
"transform": None,
|
|
"delta_absolute": True,
|
|
},
|
|
"ICSA": {
|
|
"name": "Initial Jobless Claims",
|
|
"unit": "K",
|
|
"freq": "weekly",
|
|
"category": "employment",
|
|
"ff_currency": "USD",
|
|
"higher_is_bullish": False,
|
|
"assets": ["SPY", "QQQ"],
|
|
"zscore_window": 52,
|
|
"transform": "div1000",
|
|
"delta_absolute": False,
|
|
},
|
|
"GDPC1": {
|
|
"name": "GDP Growth (QoQ Ann. %)",
|
|
"unit": "%",
|
|
"freq": "quarterly",
|
|
"category": "growth",
|
|
"ff_currency": "USD",
|
|
"higher_is_bullish": True,
|
|
"assets": ["SPY", "QQQ", "EURUSD=X", "TLT"],
|
|
"zscore_window": 8,
|
|
"transform": "qoq_annualized",
|
|
"delta_absolute": True,
|
|
},
|
|
"BAMLH0A0HYM2": {
|
|
"name": "HY Credit Spread (OAS)",
|
|
"unit": "pp",
|
|
"freq": "weekly",
|
|
"category": "credit",
|
|
"higher_is_bullish": False,
|
|
"assets": ["HYG", "LQD", "SPY"],
|
|
"zscore_window": 52,
|
|
"transform": None,
|
|
"delta_absolute": True,
|
|
},
|
|
"T10Y2Y": {
|
|
"name": "Yield Spread 10Y-2Y",
|
|
"unit": "pp",
|
|
"freq": "weekly",
|
|
"category": "rates",
|
|
"higher_is_bullish": True,
|
|
"assets": ["TLT", "IEF", "SPY", "HYG"],
|
|
"zscore_window": 52,
|
|
"transform": None,
|
|
"delta_absolute": True,
|
|
},
|
|
"T10Y3M": {
|
|
"name": "Yield Spread 10Y-3M",
|
|
"unit": "pp",
|
|
"freq": "weekly",
|
|
"category": "rates",
|
|
"higher_is_bullish": True,
|
|
"assets": ["TLT", "IEF", "SPY"],
|
|
"zscore_window": 52,
|
|
"transform": None,
|
|
"delta_absolute": True,
|
|
},
|
|
}
|
|
|
|
# Series that were renamed/replaced — will be purged from DB on bootstrap
|
|
DEPRECATED_SERIES = ["A191RL1Q225SBEA"]
|
|
|
|
CATEGORIES = sorted({v["category"] for v in FRED_SERIES.values()})
|
|
|
|
|
|
# ── FRED fetch (official JSON API — bypasses CloudFlare) ──────────────────────
|
|
#
|
|
# The public CSV endpoint (fred.stlouisfed.org/graph/fredgraph.csv) is blocked
|
|
# by CloudFlare on server IPs. Use the official REST API instead:
|
|
# https://api.stlouisfed.org/fred/series/observations
|
|
# Free API key: https://fred.stlouisfed.org/docs/api/api_key.html
|
|
|
|
_FRED_API_URL = "https://api.stlouisfed.org/fred/series/observations"
|
|
|
|
|
|
def _fetch_fred_api(series_id: str, from_date: str, api_key: str) -> Optional[pd.DataFrame]:
|
|
params = {
|
|
"series_id": series_id,
|
|
"api_key": api_key,
|
|
"observation_start": from_date,
|
|
"file_type": "json",
|
|
"sort_order": "asc",
|
|
}
|
|
print(f"[FRED API] Fetching {series_id} from {from_date}", flush=True)
|
|
try:
|
|
resp = httpx.get(_FRED_API_URL, params=params, timeout=30, follow_redirects=True)
|
|
print(f"[FRED API] {series_id} HTTP {resp.status_code}", flush=True)
|
|
resp.raise_for_status()
|
|
data = resp.json()
|
|
|
|
if "error_message" in data:
|
|
print(f"[FRED API] {series_id} API error: {data['error_message']}", flush=True)
|
|
return None
|
|
|
|
records = []
|
|
for obs in data.get("observations", []):
|
|
val_str = obs.get("value", ".")
|
|
if val_str == ".": # FRED uses "." for missing/unreleased values
|
|
continue
|
|
try:
|
|
records.append({"date": pd.Timestamp(obs["date"]), "value": float(val_str)})
|
|
except (ValueError, KeyError):
|
|
continue
|
|
|
|
if not records:
|
|
print(f"[FRED API] {series_id}: no valid observations", flush=True)
|
|
return None
|
|
|
|
df = pd.DataFrame(records).set_index("date").sort_index()
|
|
print(f"[FRED API] {series_id}: {len(df)} rows OK", flush=True)
|
|
return df
|
|
except Exception as e:
|
|
print(f"[FRED API] ERROR {series_id}: {type(e).__name__}: {e}", flush=True)
|
|
logger.warning(f"[FRED API] Failed {series_id}: {e}")
|
|
return None
|
|
|
|
|
|
# ── Transforms ────────────────────────────────────────────────────────────────
|
|
|
|
def _apply_transform(df: pd.DataFrame, transform: str) -> pd.DataFrame:
|
|
"""Apply a series-level transform before storing. Drops NaN rows introduced."""
|
|
if transform == "yoy_pct":
|
|
# Year-over-year % change from index level
|
|
df = df.copy()
|
|
df["value"] = (df["value"] / df["value"].shift(12) - 1) * 100
|
|
df = df.dropna()
|
|
elif transform == "qoq_annualized":
|
|
# Annualized quarter-over-quarter growth rate
|
|
df = df.copy()
|
|
ratio = df["value"] / df["value"].shift(1)
|
|
df["value"] = (ratio ** 4 - 1) * 100
|
|
df = df.dropna()
|
|
elif transform == "div1000":
|
|
df = df.copy()
|
|
df["value"] = df["value"] / 1000.0
|
|
return df
|
|
|
|
|
|
# ── Z-score ───────────────────────────────────────────────────────────────────
|
|
|
|
def _compute_zscore_series(values: pd.Series, window: int) -> Tuple[pd.Series, pd.Series, pd.Series]:
|
|
"""
|
|
Z-score using previous-window only (no look-ahead).
|
|
Z = (value - rolling_mean_prev_N) / rolling_std_prev_N
|
|
"""
|
|
roll_mean = values.shift(1).rolling(window, min_periods=max(4, window // 3)).mean()
|
|
roll_std = values.shift(1).rolling(window, min_periods=max(4, window // 3)).std()
|
|
z = (values - roll_mean) / roll_std.replace(0, float("nan"))
|
|
return z.round(2), roll_mean.round(4), roll_std.round(4)
|
|
|
|
|
|
def _direction(zscore: float, higher_is_bullish: bool) -> str:
|
|
if abs(zscore) < 0.5:
|
|
return "neutral"
|
|
return "bullish" if (zscore > 0) == higher_is_bullish else "bearish"
|
|
|
|
|
|
def _resample_weekly(df: pd.DataFrame) -> pd.DataFrame:
|
|
return df.resample("W-FRI").last().dropna()
|
|
|
|
|
|
# ── Main bootstrap ────────────────────────────────────────────────────────────
|
|
|
|
def bootstrap_fred(
|
|
from_date: str = "2020-01-01",
|
|
series_ids: Optional[List[str]] = None,
|
|
force: bool = False,
|
|
api_key: Optional[str] = None,
|
|
) -> Dict[str, Any]:
|
|
"""
|
|
Fetch and store FRED data from from_date to today.
|
|
Requires a free FRED API key (fred.stlouisfed.org/docs/api/api_key.html).
|
|
If api_key is not passed, reads from DB config key 'fred_api_key'.
|
|
"""
|
|
from services.database import get_conn, get_config
|
|
import json
|
|
|
|
# Resolve API key
|
|
key = api_key or get_config("fred_api_key") or ""
|
|
if not key:
|
|
return {
|
|
"_error": (
|
|
"Clé API FRED manquante. "
|
|
"Inscris-toi gratuitement sur fred.stlouisfed.org/docs/api/api_key.html "
|
|
"puis saisis la clé dans le panel Bootstrap."
|
|
)
|
|
}
|
|
|
|
target_series = series_ids or list(FRED_SERIES.keys())
|
|
results: Dict[str, Any] = {}
|
|
conn = get_conn()
|
|
|
|
# Purge deprecated series from DB
|
|
for dep in DEPRECATED_SERIES:
|
|
try:
|
|
conn.execute("DELETE FROM economic_events WHERE series_id=?", (dep,))
|
|
except Exception:
|
|
pass
|
|
conn.commit()
|
|
|
|
for sid in target_series:
|
|
meta = FRED_SERIES.get(sid)
|
|
if not meta:
|
|
logger.warning(f"[FRED bootstrap] Unknown series: {sid}")
|
|
continue
|
|
if meta.get("ff_only"):
|
|
results[sid] = {"skipped": "ff_calendar only — no FRED fetch"}
|
|
continue
|
|
|
|
# Warm-up period: extra years before from_date for transform + z-score
|
|
transform = meta.get("transform")
|
|
extra_years = 3 if transform == "qoq_annualized" else (2 if transform == "yoy_pct" else 1)
|
|
fetch_from = str(date(int(from_date[:4]) - extra_years, 1, 1))
|
|
|
|
df = _fetch_fred_api(sid, from_date=fetch_from, api_key=key)
|
|
if df is None or df.empty:
|
|
results[sid] = {"status": "fetch_failed", "count": 0}
|
|
continue
|
|
|
|
# Apply transform on full (warm-up included) dataset
|
|
if transform:
|
|
df = _apply_transform(df, transform)
|
|
if df.empty:
|
|
results[sid] = {"status": "transform_empty", "count": 0}
|
|
continue
|
|
|
|
# Downsample continuous daily/weekly series to weekly
|
|
if meta["freq"] == "weekly" and len(df) > 200:
|
|
df = _resample_weekly(df)
|
|
|
|
# Z-score on transformed values
|
|
window = meta["zscore_window"]
|
|
z_series, _, _ = _compute_zscore_series(df["value"], window)
|
|
|
|
delta_absolute = meta.get("delta_absolute", False)
|
|
inserted = 0
|
|
skipped = 0
|
|
|
|
for dt, row in df.iterrows():
|
|
ev_date = dt.strftime("%Y-%m-%d")
|
|
if ev_date < from_date:
|
|
continue # warm-up only, don't store
|
|
|
|
val = float(row["value"])
|
|
z = float(z_series.get(dt) or 0.0)
|
|
if pd.isna(z):
|
|
z = 0.0
|
|
|
|
prev_raw = df["value"].shift(1).get(dt)
|
|
prev = float(prev_raw) if prev_raw is not None and not pd.isna(prev_raw) else None
|
|
|
|
if delta_absolute:
|
|
# pp / absolute change (for rates, spreads, YoY series)
|
|
surprise_pct = round(val - prev, 4) if prev is not None else None
|
|
else:
|
|
# % change (for levels: NFP, ICSA in K, etc.)
|
|
surprise_pct = round((val - prev) / abs(prev) * 100, 2) if prev and prev != 0 else None
|
|
|
|
direction = _direction(z, meta["higher_is_bullish"])
|
|
|
|
try:
|
|
conn.execute(
|
|
"""INSERT INTO economic_events
|
|
(event_name, series_id, event_date, actual_value, actual_unit,
|
|
forecast_value, previous_value, surprise_pct, surprise_zscore,
|
|
surprise_direction, assets_impacted, source)
|
|
VALUES (?,?,?,?,?,?,?,?,?,?,?,?)
|
|
ON CONFLICT(series_id, event_date) DO """ + (
|
|
"UPDATE SET actual_value=excluded.actual_value, "
|
|
"previous_value=excluded.previous_value, "
|
|
"surprise_pct=excluded.surprise_pct, "
|
|
"surprise_zscore=excluded.surprise_zscore, "
|
|
"surprise_direction=excluded.surprise_direction, "
|
|
"fetched_at=datetime('now')"
|
|
if force else "NOTHING"
|
|
),
|
|
(
|
|
meta["name"], sid, ev_date, round(val, 4), meta["unit"],
|
|
None, prev, surprise_pct, z, direction,
|
|
json.dumps(meta["assets"]),
|
|
"FRED/bootstrap",
|
|
),
|
|
)
|
|
if conn.execute("SELECT changes()").fetchone()[0]:
|
|
inserted += 1
|
|
else:
|
|
skipped += 1
|
|
except Exception as e:
|
|
logger.debug(f"[FRED bootstrap] {sid} {ev_date}: {e}")
|
|
|
|
conn.commit()
|
|
results[sid] = {
|
|
"status": "ok",
|
|
"name": meta["name"],
|
|
"inserted": inserted,
|
|
"skipped": skipped,
|
|
"total_fetched": len(df[df.index >= pd.Timestamp(from_date)]),
|
|
}
|
|
logger.info(f"[FRED bootstrap] {sid}: {inserted} inserted, {skipped} skipped")
|
|
|
|
conn.close()
|
|
return results
|