1#!/usr/bin/env python32"""Phase 0 — audit of the FinData OS data inventory for WP46 (writes docs/findata_audit.md + results/manifests/findata_audit.json).34Every number in the markdown comes from a live API call recorded in results/logs/findata_audit_provenance.jsonl.5No bar values are printed beyond ratios used to verify adjustment conventions (split ratio, roll scaling)."""6from __future__ import annotations78import json9import os10import sys11import time12from datetime import datetime, timezone13from pathlib import Path1415import pyarrow as pa16import yaml1718ROOT = Path(__file__).resolve().parents[1]19sys.path.insert(0, str(ROOT / "src"))20from wp46.findata_client import FinDataClient, FinDataError # noqa: E4022122CAND = yaml.safe_load(open(ROOT / "configs" / "instruments.yaml"))23OUT_JSON = ROOT / "results" / "manifests" / "findata_audit.json"24OUT_MD = ROOT / "docs" / "findata_audit.md"25LOG = ROOT / "results" / "logs" / "findata_audit_provenance.jsonl"2627VIEW = { # asset class -> (daily view, 1m view, 5m view)28 "etf": ("market_bars_etf_1d_split_div", "market_bars_etf_1m_raw", "market_bars_etf_5m_split"),29 "stock": ("market_bars_stock_1d_split_div", "market_bars_stock_1m_raw", "market_bars_stock_5m_split"),30 "futures": ("market_bars_futures_1d_continuous_ratio", "market_bars_futures_1m_continuous_ratio", "market_bars_futures_5m_continuous_ratio"),31 "fx": (None, "market_bars_fx_1m_raw", "market_bars_fx_5m_raw"),32 "crypto": ("market_bars_crypto_1d_raw", "market_bars_crypto_1m_raw", "market_bars_crypto_5m_raw"),33 "index": ("market_bars_index_1d_raw", "market_bars_index_1m_raw", "market_bars_index_5m_raw"),34}353637def rows(t: pa.Table) -> list[dict]:38 return t.to_pylist()394041def main() -> None:42 fd = FinDataClient(log_path=LOG)43 A: dict = {"audited_at": datetime.now(timezone.utc).isoformat(), "base_url": fd.base, "errors": []}4445 def safe(name, fn):46 try:47 A[name] = fn()48 except FinDataError as e:49 A["errors"].append({"section": name, "error": str(e)})50 A[name] = None5152 # 1. health / contract53 safe("health", fd.health)54 oa = fd.openapi()55 A["openapi"] = {"version": oa.get("openapi"), "n_paths": len(oa["paths"]), "paths": sorted(oa["paths"])}56 safe("bars_schema", fd.bars_schema)57 # 2. catalog58 ds = fd.datasets()59 keep = [d for d in ds if d["id"].startswith("market_bars.") or d["id"].startswith("options") or d["id"].startswith("corporate")]60 A["datasets"] = [{k: d.get(k) for k in ("id", "status", "row_count", "first_session", "last_session", "current_version")} for d in keep]61 det = {}62 for i in ["market_bars.etf.1m.raw", "market_bars.etf.1d.split_div", "market_bars.stock.1m.raw", "market_bars.stock.1d.split_div",63 "market_bars.futures.1m.continuous_ratio", "market_bars.futures.1d.continuous_ratio", "market_bars.futures.1d.continuous_absolute",64 "market_bars.fx.1m.raw", "market_bars.crypto.1m.raw", "market_bars.index.1d.raw", "corporate_actions.stock"]:65 try:66 d = fd.dataset(i)["data"]67 det[i] = {k: d.get(k) for k in ("name", "description", "timestamp_convention", "timezone", "notes", "license_class", "license_text", "current_version", "status")}68 except FinDataError as e:69 det[i] = {"error": str(e)}70 A["dataset_details"] = det71 # 3. candidate coverage72 cov = []73 for cls, syms in CAND["candidates"].items():74 d1, m1, _ = VIEW[cls]75 for s in syms:76 rec = {"asset_class": cls, "symbol": s}77 try:78 inst = fd.instrument(s)79 if not inst.get("instrument"):80 raise FinDataError(404, "no_instrument_record", f"/instruments/{s} returned no instrument object")81 rec["instrument_id"] = inst["instrument"]["instrument_id"]82 rec["name"] = inst["instrument"].get("name")83 rec["first_date"] = inst["instrument"].get("first_date")84 rec["last_date"] = inst["instrument"].get("last_date")85 rec["status"] = inst["instrument"].get("status")86 rec["coverage_api"] = inst.get("coverage")87 rec["n_corporate_actions"] = len(inst.get("corporate_actions") or [])88 except FinDataError as e:89 rec["instrument_error"] = str(e)90 if d1:91 try:92 t = rows(fd.sql(f"SELECT count(*) n, min(session_date) a, max(session_date) b, "93 f"sum(CASE WHEN close IS NULL OR close<=0 THEN 1 ELSE 0 END) bad_close, "94 f"sum(CASE WHEN volume=0 THEN 1 ELSE 0 END) zero_vol "95 f"FROM {d1} WHERE symbol='{s}'"))[0]96 rec["daily"] = t97 except FinDataError as e:98 rec["daily_error"] = str(e)99 try:100 t = rows(fd.sql(f"SELECT count(*) n, count(DISTINCT session_date) sessions, min(session_date) a, max(session_date) b "101 f"FROM {m1} WHERE symbol='{s}'"))[0]102 rec["minute"] = t103 except FinDataError as e:104 rec["minute_error"] = str(e)105 cov.append(rec)106 print(f"{cls:8s} {s:8s} daily={rec.get('daily',{}).get('n')} 1m_sessions={rec.get('minute',{}).get('sessions')}", flush=True)107 A["coverage"] = cov108 # 4. session structure per class (2024, ET times)109 sess = {}110 for cls, sym in [("etf", "SPY"), ("stock", "AAPL"), ("futures", "ES"), ("fx", "EURUSD"), ("crypto", "BTC")]:111 _, m1, _ = VIEW[cls]112 try:113 t = rows(fd.sql(f"SELECT count(*) n_sessions, min(n) min_bars, approx_quantile(n,0.5) med_bars, max(n) max_bars, "114 f"min(t0) first_bar_utc_min, max(t1) last_bar_utc_max FROM (SELECT session_date, count(*) n, min(timestamp_utc) t0, max(timestamp_utc) t1 "115 f"FROM {m1} WHERE symbol='{sym}' AND year=2024 GROUP BY 1)"))[0]116 t2 = rows(fd.sql(f"SELECT session_date, count(*) n, min(timestamp_utc) t0, max(timestamp_utc) t1 FROM {m1} "117 f"WHERE symbol='{sym}' AND year=2024 AND session_date IN (DATE '2024-03-08', DATE '2024-03-11', DATE '2024-07-03', DATE '2024-11-29', DATE '2024-07-07') GROUP BY 1 ORDER BY 1"))118 sess[cls] = {"symbol": sym, "summary": t, "sample_days": t2}119 except FinDataError as e:120 sess[cls] = {"symbol": sym, "error": str(e)}121 A["sessions_2024"] = sess122 # regular-hours count for SPY 2024 (09:30-16:00 ET incl. closing print) using 5m-adjusted view too123 try:124 t = rows(fd.sql("SELECT count(*) n_sessions, min(n) min_bars, approx_quantile(n,0.5) med_bars, max(n) max_bars FROM ("125 "SELECT session_date, count(*) n FROM market_bars_etf_1m_raw WHERE symbol='SPY' AND year=2024 AND "126 "((timestamp_utc AT TIME ZONE 'America/New_York')::TIME BETWEEN TIME '09:30' AND TIME '16:00') GROUP BY 1)"))[0]127 A["spy_regular_hours_2024"] = t128 except FinDataError as e:129 A["spy_regular_hours_2024"] = {"error": str(e)}130 # 5. calendar reconciliation SPY vs XNYS (2000-2026)131 try:132 import exchange_calendars as xc133 import pandas as pd134 cal = xc.get_calendar("XNYS", start="1999-01-01")135 t = rows(fd.sql("SELECT session_date FROM market_bars_etf_1d_split_div WHERE symbol='SPY' ORDER BY 1", max_rows=20000))136 have = pd.to_datetime([r["session_date"] for r in t])137 last = have.max()138 exp = cal.sessions_in_range(pd.Timestamp("2000-01-03"), last)139 missing = sorted(set(exp.strftime("%Y-%m-%d")) - set(have.strftime("%Y-%m-%d")))140 extra = sorted(set(have.strftime("%Y-%m-%d")) - set(exp.strftime("%Y-%m-%d")))141 A["calendar_spy_vs_xnys"] = {"vendor_sessions": len(have), "xnys_sessions": len(exp), "missing_in_vendor": missing[:50],142 "n_missing": len(missing), "extra_in_vendor": extra[:50], "n_extra": len(extra),143 "exchange_calendars_version": xc.__version__}144 except Exception as e: # noqa: BLE001145 A["calendar_spy_vs_xnys"] = {"error": repr(e)}146 # 6. adjustment conventions147 adj = {}148 try:149 r = rows(fd.sql("SELECT a.session_date, a.close/b.close ratio_raw_over_split FROM market_bars_stock_1d_raw a JOIN market_bars_stock_1d_split b "150 "ON a.session_date=b.session_date AND a.symbol=b.symbol WHERE a.symbol='AAPL' AND a.year=2020 AND b.year=2020 AND a.session_date IN (DATE '2020-08-28', DATE '2020-08-31') ORDER BY 1"))151 adj["aapl_split_2020_08_31"] = r152 r = rows(fd.sql("SELECT a.session_date, a.close/b.close ratio_split_over_splitdiv FROM market_bars_etf_1d_split a JOIN market_bars_etf_1d_split_div b "153 "ON a.session_date=b.session_date AND a.symbol=b.symbol WHERE a.symbol='SPY' AND a.year=2024 AND b.year=2024 AND a.session_date IN (DATE '2024-03-14', DATE '2024-03-15', DATE '2024-03-18') ORDER BY 1"))154 adj["spy_exdiv_2024_03_15"] = r155 r = rows(fd.sql("SELECT a.session_date, a.close/b.close ratio_ratioadj_over_raw, c.close - b.close diff_absadj_minus_raw FROM market_bars_futures_1d_continuous_ratio a "156 "JOIN market_bars_futures_1d_continuous_raw b ON a.session_date=b.session_date AND a.symbol=b.symbol "157 "JOIN market_bars_futures_1d_continuous_absolute c ON a.session_date=c.session_date AND a.symbol=c.symbol "158 "WHERE a.symbol='ES' AND a.year=2024 AND b.year=2024 AND c.year=2024 AND a.session_date BETWEEN DATE '2024-03-11' AND DATE '2024-03-19' ORDER BY 1"))159 adj["es_roll_march_2024"] = r160 r = rows(fd.sql("SELECT session_date, close FROM market_bars_futures_1d_continuous_ratio WHERE symbol='ES' AND year=2026 ORDER BY 1 DESC", max_rows=3))161 r2 = rows(fd.sql("SELECT session_date, close FROM market_bars_futures_1d_continuous_raw WHERE symbol='ES' AND year=2026 ORDER BY 1 DESC", max_rows=3))162 adj["es_latest_ratio_vs_raw_equal"] = [{"d": a["session_date"], "ratio_close_over_raw_close": a["close"] / b["close"]} for a, b in zip(r, r2)]163 except FinDataError as e:164 adj["error"] = str(e)165 A["adjustments"] = adj166 # 7. quality flags on daily data (candidates)167 try:168 syms = ",".join(f"'{s}'" for s in CAND["candidates"]["etf"] + CAND["candidates"]["stock"])169 A["quality_flags_daily"] = rows(fd.sql(f"SELECT unnest(quality_flags) flag, count(*) n FROM market_bars_etf_1d_split_div WHERE symbol IN ({syms}) GROUP BY 1"))170 A["revision_ids_daily"] = rows(fd.sql(f"SELECT revision_id, count(*) n FROM market_bars_etf_1d_split_div WHERE symbol IN ({syms}) GROUP BY 1"))171 except FinDataError as e:172 A["quality_flags_daily"] = {"error": str(e)}173 # 8. export smoke test (small)174 try:175 dest = ROOT / "data" / "interim" / "audit_export_spy_1d_2024.parquet"176 ex = fd.export("market_bars.etf.1d.split_div", ["SPY"], "2024-01-01", "2024-12-31", dest)177 A["export_smoke"] = {k: ex[k] for k in ("export_id", "dataset_id", "dataset_version", "row_count", "bytes", "sha256")}178 dest.unlink(missing_ok=True)179 except FinDataError as e:180 A["export_smoke"] = {"error": str(e)}181 # 9. rate-limit headers182 r = fd.request("GET", "/health")183 A["headers_sample"] = {k: v for k, v in r.headers.items() if k.lower().startswith("x-") or "limit" in k.lower()}184 OUT_JSON.parent.mkdir(parents=True, exist_ok=True)185 OUT_JSON.write_text(json.dumps(A, indent=1, default=str))186 write_md(A)187 print("written", OUT_JSON, OUT_MD, "errors:", len(A["errors"]))188189190def write_md(A: dict) -> None:191 L = []192 L.append(f"# FinData OS audit for WP46 (Phase 0)\n\nAudited {A['audited_at']} against `{A['base_url']}` by `scripts/audit_findata.py`. "193 f"Machine-readable evidence: `results/manifests/findata_audit.json`; call log: `results/logs/findata_audit_provenance.jsonl`. "194 "The hostname `findata.spboucher.ai` (not `indata.`) is the one configured in the owner's FinData repository and its `docs/CLAUDE-API-USAGE.md`; "195 "the API contract is the live OpenAPI document (`/api/v1/openapi.json`) — no endpoint below was guessed.\n")196 h = A.get("health") or {}197 L.append("## 1. Service and contract\n")198 L.append(f"- Health status: `{h.get('status')}`; alerts: {[a.get('code') for a in h.get('alerts', [])]}; components: `{ {k: (v if not isinstance(v, dict) else '…') for k, v in h.get('components', {}).items()} }`.")199 L.append(f"- OpenAPI {A['openapi']['version']}, {A['openapi']['n_paths']} paths. Used by WP46: `/catalog/datasets`, `/catalog/datasets/{{id}}`, `/catalog/schema/bars`, `/instruments/{{symbol}}`, `/market/bars`, `/query` (DuckDB SQL over the lake views), `/exports` (+ `/exports/{{id}}`, `/download`), `/health`.")200 bs = A.get("bars_schema") or {}201 L.append(f"- Bars schema: `{json.dumps(bs)[:600]}`")202 L.append(f"- Response headers observed: `{A.get('headers_sample')}`. Rate limit (documented): 600 requests/min per key; the client uses a 5 req/s token bucket with Retry-After/backoff.")203 L.append("- Authentication: bearer API key read from the owner's secret file; key scopes used: read:market, query, export. Never logged.\n")204 L.append("## 2. Datasets (catalog)\n\n| dataset | status | rows | first session | last session | version |\n|---|---|---:|---|---|---:|")205 for d in A["datasets"]:206 L.append(f"| `{d['id']}` | {d['status']} | {d['row_count'] or 0:,} | {d['first_session'] or ''} | {d['last_session'] or ''} | {d['current_version']} |")207 L.append("\n### Dataset conventions (descriptor text)\n")208 for i, d in A["dataset_details"].items():209 L.append(f"- `{i}`: {d}")210 L.append("\n## 3. Candidate universe coverage\n\n| class | symbol | name | daily sessions | daily first | daily last | bad close | 1-min sessions | 1-min first | 1-min last | 1-min bars |\n|---|---|---|---:|---|---|---:|---:|---|---|---:|")211 for r in A["coverage"]:212 d = r.get("daily") or {}213 m = r.get("minute") or {}214 L.append(f"| {r['asset_class']} | {r['symbol']} | {r.get('name') or ''} | {d.get('n') or ''} | {d.get('a') or ''} | {d.get('b') or ''} | {d.get('bad_close') if d else ''} | {m.get('sessions') or ''} | {m.get('a') or ''} | {m.get('b') or ''} | {m.get('n') or ''} |")215 L.append("\nFX has no daily dataset in the lake (`market_bars.fx.1d.raw` is `planned`, 0 rows): daily FX returns are built from the 1-minute bars (last bar of each vendor session). Futures daily rows come from the vendor continuous series.\n")216 L.append("## 4. Session structure (2024, one representative symbol per class)\n")217 for cls, s in A["sessions_2024"].items():218 L.append(f"- **{cls} / {s['symbol']}**: {s.get('summary')}; sample days: {s.get('sample_days')}")219 L.append(f"- SPY regular hours 09:30–16:00 ET (incl. the 16:00 closing print) 2024: {A.get('spy_regular_hours_2024')}\n")220 L.append("## 5. Calendar reconciliation\n")221 L.append(f"- SPY daily sessions vs `exchange_calendars` XNYS: {A.get('calendar_spy_vs_xnys')}\n")222 L.append("## 6. Adjustment conventions (ratios, no price levels disclosed)\n")223 for k, v in A["adjustments"].items():224 L.append(f"- {k}: {v}")225 L.append("\n## 7. Quality flags and revisions (daily ETF/stock candidates)\n")226 L.append(f"- flags: {A.get('quality_flags_daily')}; revision ids: {A.get('revision_ids_daily')}\n")227 L.append("## 8. Export smoke test\n")228 L.append(f"- {A.get('export_smoke')}\n")229 L.append("## 9. Errors during the audit\n")230 L.append(f"- {A['errors'] or 'none'}\n")231 L.append("## 10. Facts, consequences and unresolved questions\n")232 L.append("""- **Timestamps** are bar OPEN in UTC; stocks/ETFs/futures/FX carry an ET `session_date`, crypto a UTC date. Stocks/ETFs: extended hours 04:00–20:00 ET are present in the 1-minute data; regular session 09:30–16:00 ET (391 bars on a full day, 211 on a 13:00 early close). Zero-volume minutes are absent (missingness is a mask, never filled).233- **Futures** continuous series: vendor-fixed roll dates (not documented per contract by the vendor); three variants (raw = unadjusted splice, ratio = ratio back-adjusted, absolute = difference back-adjusted). WP46 uses the **ratio-adjusted** series, whose log returns are free of roll jumps, and the vendor `session_date` (a calendar day in ET, 00:00–23:59 ET), not the CME trading day (18:00–17:00 ET). This is disclosed in the data section; daily futures returns are therefore 'ET-day close-to-close'.234- **FX**: 1-minute mid bars aggregated from four money-center banks (vendor description), ET session_date, 24h except the weekend gap (Friday ~17:00 ET → Sunday 17:00 ET); Sunday partial sessions exist and are dropped by the preprocessing rule (session must have ≥ 60 % of the modal bar count).235- **Crypto**: UTC days, 7 days a week, 1,440 bars per day.236- **Adjustments**: stocks/ETFs daily returns use the vendor split- and dividend-adjusted closes (`split_div`, price-return convention with dividends reinvested at the ex-date = total-return approximation); intraday returns within a session are unaffected by adjustments except on split days (handled by using the `split` 1-minute view for RV).237- **Licence**: FirstRate Data private use; raw bars are never redistributed; aggregate forecasts/losses and derived statistics are publishable with attribution.238- **Survivorship**: the universe is fixed ex ante to instruments alive on 2026-11-06; all candidates have been continuously listed over their sample, so there is no within-sample delisting, but the selection of liquid, surviving instruments is a documented selection bias (results describe liquid benchmarks, not the cross-section).239- **Unresolved**: (i) exact vendor roll rule for continuous futures (vendor does not expose it in the API; the ratio series preserves returns, so forecasts are unaffected, but RV on roll days mixes two contracts' intraday paths); (ii) corporate-actions table is empty in the lake (adjustments come from vendor-adjusted series only); (iii) 1-minute FX/crypto bars are not consolidated trade data — RV from them is a mid-quote/aggregate measure and is documented as such.240""")241 OUT_MD.write_text("\n".join(L))242243244if __name__ == "__main__":245 main()246