1# DATA_AUDIT.md — WP39 `wp39_uqo`23Phase 0 discovery and feasibility audit of the **FinData OS** API (`https://findata.spboucher.ai/api/v1`) as the primary4source for *The Invisible Balance Sheet*. All numbers below were measured on **2026-11-02** (America/Toronto) with the5owner's Claude Code key (admin scope, laptop) and then with a dedicated least-privilege key `wp39_uqo`6(scopes `read:market, read:fundamentals, query, export`; stored outside git in `~/findata-secrets/api-key-wp39.txt` on7M3U96a and `~/Desktop/Cluster/secrets/findata/api-key-wp39.txt` on the laptop, mode 600). No credential appears in8this repository, in logs or in artifacts. Probe transcripts: `artifacts/logs/phase0_probe.json` (produced by9`scripts/00_probe_findata.py`).1011## 0. Viability conclusion1213**YES — viable, with two structural caveats that shape the paper.**14151. A licensed, point-in-time usable cohort exists: **5,451 US-listed entities** with quarterly fundamentals16 (**67 metrics**, `filing_date` 2012-01-03 → 2026-10-30, median 45 quarterly periods per firm), **373,326 earnings-release17 events** with release date and (for 65 %) before-open/after-close timing, and **6.70 billion raw 1-minute bars** for18 16,195 stock symbols (2000-01-03 → 2026-10-30) including 8,069 `-DELISTED` symbols. Exports of 1-minute history are19 fast (≈ 1 s per symbol-decade, see §6).202. **Caveat A — survivorship in fundamentals, not in prices.** The fundamentals/earnings tables contain **zero delisted21 entities** (ingestion ran for *active* tickers only). Minute bars do cover delisted names, so the price side is22 survivorship-free, but the accounting panel is conditioned on being listed on 2026-11-01. The vendor does serve23 fundamentals for at least some delisted tickers (probe: `FMD` → HTTP 200 with data; `CWZ` → 404), so a scoped24 delisted-fundamentals backfill is *possible* but requires owner approval and a capacity plan (not run; see §8).253. **Caveat B — PIT is a proxy.** `available_at = filing_date + 1 day` is a policy, `revision_id = 1` for every fact26 (`observed_at` = first ingestion, 2026-11-01/02). Values are the vendor's *current* history, so restatements that27 happened before ingestion are invisible. The design therefore (i) uses only prior-quarter fundamentals as inputs,28 (ii) uses the **earnings-release date** (not the filing date) as the reveal date, and (iii) reserves a stringent29 specification using SEC-filing-level reconstruction for a subset (Phase 5, conditional on time).3031## 1. API contract — documented vs observed3233| Purpose | Route (observed in `openapi.json`, OpenAPI 3.1.0, API 1.0.0) | Observed behaviour |34|---|---|---|35| Datasets | `GET /catalog/datasets` | 64 datasets; 62 `active`, `fundamentals.sec_companyfacts` = `pending_configuration`, `market_ticks` = `unsupported` |36| Dataset detail | `GET /catalog/datasets/{id}` | version, `license_class = frd-private` |37| Bars | `GET /market/bars` | `interval ∈ {1m, 5m, 1d}` only (no 30m/1h on REST); `adjustment ∈ {raw, split, split_div}`; `limit ≤ 10 000`; `format ∈ {json, arrow, csv}`; `version` pin |38| Bars schema | `GET /catalog/schema/bars` | schema v1, `timestamp_convention = bar_open`, `timezone_storage = UTC`, 20 fields (see §3) |39| Fundamentals | `GET /fundamentals/{entity}` | `as_of`, `metrics`, `period_type ∈ {q,a}`, `include_superseded`, **`limit ≤ 20 000`** (default 2 000) |40| Fundamental periods | `GET /fundamentals/{entity}/periods` | one row per fiscal period with `filing_date`, `available_at`, `max_revision`, `restated` |41| SQL | `POST /query` | one read-only statement, `max_rows ≤ 100 000`, 30 s; views = dataset ids with `.`→`_` |42| Exports | `POST /exports` → `GET /exports/{id}` → `/download` | body `{dataset_id, symbols ≤ 500, start, end, adjustment, format}`; **range ≤ 12 years per request (HTTP 413 otherwise)**; Parquet with schema metadata and pinned `X-Dataset-Version` |43| Coverage | `GET /coverage`, `/coverage/symbols` (`limit ≤ 5 000`, cursor), `/coverage/heatmap` | per-session bar counts vs `expected_bars` (391 regular) |44| Instruments | `GET /instruments`, `/instruments/{symbol}` | status `active`/`delisted`, first/last date |45| Quality | `GET /quality`, `/quality/anomalies` | row-level flags (`price_jump`, `outside_session`, …) |46| **Earnings events** | **no route** | dataset `fundamentals.earnings` is listed (0 rows reported) but rows live in Postgres `earnings_events` → read-only extraction on the node (documented exception, §7) |47| Corporate actions | **no route** | Postgres `corporate_actions`: 6,131 splits (2000-01-03 → 2026-10-15), 243,209 cash dividends; applied at query time via `adjustment=` |4849Reconciliation notes: the catalog reports `row_count = 0` for Postgres-backed datasets (`fundamentals.*`, `corporate_actions`,50`reference.instruments`); that is a catalog limitation, not an absence of data. The REST `interval` enum is narrower than the51lake (30m/1h exist only as vendor-adjusted datasets); WP39 rebuilds 5m/30m/1d bars **from the raw 1m export** so that every52frequency shares one source and one adjustment path.5354## 2. Market bars — coverage5556Counts of distinct stock symbols with at least one bar on a mid-June session (SQL playground, `session_date =` predicate):5758| Session | 1m symbols | 1m bars | 1d symbols | of which `-DELISTED` in 1d |59|---|---:|---:|---:|---:|60| 2001-06-15 | — | — | 1,088 | 0 |61| 2005-06-15 | 3,498 (340,665 bars delisted) | 631,260 | 1,504 | 0 |62| 2010-06-15 | — | — | 2,309 | 0 |63| 2015-06-15 | 6,489 | 1,207,920 | 2,985 | 0 |64| 2020-06-15 | — | — | 4,144 | 0 |65| 2025-06-16 | 6,918 | 1,317,343 | 6,485 | 0 |6667Finding: the vendor **1d dataset is incomplete** relative to the 1m dataset (about half the symbols in 2005–2015 and no68delisted names). Decision D-003: daily bars are *derived* from the 1m export (regular session 09:30–16:00 ET) — never taken69from `market_bars.stock.1d.raw`. Delisted symbols are present in the 1m lake (e.g. `AA.B-DELISTED` 531 sessions702014–2016; `CWZ-DELISTED` 2000–2014).7172Bar conventions verified on `XOM` and `SPY` exports: timestamps are bar **open** in UTC (`2012-01-03 09:01Z` = 04:01 ET),73extended hours 04:00–19:59 ET are present, zero-volume minutes are absent (bar counts per session 405–798 vs 391 expected74regular bars), `quality_flags` list is empty on normal rows, `revision_id = 1`.7576## 3. Bars schema (Arrow, schema v1)7778`instrument_id, symbol_as_reported, venue (null), asset_class, interval, timestamp_utc (timestamp[us, UTC], bar open),79session_date (date32, exchange tz), open, high, low, close, volume (double), currency, trade_count (int32?, derived only),80vwap (double?, derived only), adjustment_type, source_id, ingested_at, quality_flags (list<string>), revision_id (int32)`.81Parquet exports carry metadata `findata.schema = bars`, `findata.schema_version = 1`, `findata.timestamp_convention = bar_open`.8283## 4. Fundamentals — coverage and PIT fields8485- Entities: **5,451** (`frd:<TICKER>`, `security_id = firstrate:stock:<TICKER>`, none delisted, none with `security_id` null).86- Metrics observed per period: **67** (the vendor documents 76; 9 are never populated in the API output). Names:87 `acc_oci, assets_c, assets_nc, assets_tot, capex, cash, cash_business_acq, cash_change, cash_debt_sec, cash_div,88 cash_equity, cash_fin, cash_inv, cash_inv_acq, cash_op, cogs, cos_inc, debt, debt_c, debt_nc, def_rev, depr, dps, ebit,89 ebitda, ebt, eps, eps_d, equity, fx_impact_cash, gm, gp, intangibles, interest_exp, intr_cover, inv_c, inv_nc, inventory,90 investments, liab_c, liab_nc, liab_tot, net_inc, net_inc_com, net_inc_non_control, net_loss_discontinued, om, op_ex,91 op_inc, payables, ppe, pref_divs, receivables, research_dev, ret_earnings, rev, roa, roe, roic, sgna, share_comp, shares,92 shares_av, shares_av_diluted, tax_asset, tax_expense, tax_liability`.93- Quarterly `rev` facts by fiscal year (entities / facts): 2011: 1,798 / 1,812 · 2012: 2,461 / 8,725 · 2013: 2,623 / 9,868 ·94 2014: 2,771 / 10,560 · 2015: 2,916 / 11,145 · 2016: 3,076 / 11,656 · 2017: 3,276 / 12,340 · 2018: 3,505 / 13,209 ·95 2019: 3,763 / 14,034 · 2020: 4,309 / 15,488 · 2021: 4,663 / 17,458 · 2022: 4,851 / 18,289 · 2023: 5,043 / 18,904 ·96 2024: 5,244 / 19,467 · 2025: 5,370 / 19,625 · 2026: 4,928 / 10,915. `filing_date` is never null (no `pit_fallback`).97- Firms by number of quarterly periods: ≥ 8: 5,018 · ≥ 20: 4,331 · ≥ 40: 2,891. First quarter year: 2011: 2,141 ·98 2012: 317 · 2013–2019: 148–256 per year · 2020: 551 · 2021: 353 · 2022–2025: 164–215 per year.99- Filing lag (filing_date − fiscal_period_end, quarterly): p10 = 30 d, median = 40 d, p90 = 77 d.100- Q4 values are genuine quarterly flows (AAPL FY2019 revenue 260.17 bn = 84.31 + 58.02 + 53.81 + 64.04 bn).101- PIT fields per fact: `filing_date`, `published_at` (null), `available_at = filing_date + 1 d 00:00 ET`, `observed_at`102 (= 2026-11-01 18:24 ET for the first ingestion), `revision_id = 1`, `restatement_flag = false`, `superseded_at = null`.103 The API `as_of` semantics (`available_at ≤ as_of` and (`revision_id = 1` or `observed_at ≤ as_of`)) were exercised:104 AAPL `as_of = 2020-01-01` returns 2,000 facts (limit) and reports `facts_hidden_by_as_of = 2211`.105- Entity master: `cik`, `sector`, `industry` are **null for all 5,451 entities** → external SEC crosswalk needed (§7).106107## 5. Earnings-release events (reveal dates)108109Postgres `earnings_events` (`entity_id, event_date, eps_estimate, eps_reported, release_time, source_id, observed_at`):110373,326 rows, 5,451 entities, 2000-01-01 → 2027-04-29, one row per (entity, date). `release_time`: `after_close` 124,832 ·111`before_open` 120,586 · null 127,908. For 2012–2024 the null share is 35–36 %; `eps_estimate` is null for 24–27 % of112events (vendor estimate; vintage unknown → exploratory only). Row counts for 2025–2026 are inflated by forward-dated113placeholders (2026: 35,361 events), which are dropped at the sample end (2026-10-30).114115Matching quarterly filings to the first release event in `[period_end, filing_date + 10 d]` (213,954 filings 2012→):11696.3 % matched; 1,493 (0.7 %) have `filing_date < event_date`; 16,868 (7.9 %) have a lag > 30 d (likely mismatches or117late filers); median `filing_date − event_date` = **1 day**. Rule (PIT_PROTOCOL §3): reveal date `A_q` = matched release118date; a firm-quarter is **excluded** when no release is matched, when `filing_date < event_date`, or when the lag exceeds11930 days; the prediction cutoff always satisfies `c < min(A_q, F_q)` in trading days.120121## 6. Throughput and capacity (measured on M3U96a, API on localhost)122123| Operation | Measured |124|---|---|125| Export 1m raw, 1 symbol, 2012-01-01→2019-12-31 (`XOM`) | ready in 1.0 s, 859,354 rows, 13.6 MB Parquet, dataset version 17375 |126| Export 1m raw, 1 symbol, 2020-01-01→2026-10-30 | ready in 1.0 s, 884,983 rows, 15.4 MB |127| Full-range export (2012→2026, 14.8 years) | **HTTP 413** — limit 12 years per request → two requests per symbol |128| SQL cross-section (`session_date =` on 1m) | ≈ 3 s |129| Coverage per symbol (`/coverage/symbols`) | ms, cursor-paged |130| Fundamentals, one entity, all quarterly facts | one call (≤ 20 000 rows) |131132Capacity model for the main sample (≈ 4,300 firms with ≥ 8 quarters and 1m coverage): 2 exports × ~1.5 s ≈ 3.6 h of133export wall-time (sequential; 4 concurrent streams bring it under 1 h), ≈ 30 MB raw Parquet per firm (≈ 130 GB if kept).134Decision D-004: raw extracts are processed **streaming** (export → daily feature rows at 1m/5m/30m/1d → delete raw file,135keep SHA-256 + row count in the manifest), so steady-state scratch usage stays below 5 GB. The storage node M3U96a136(28 cores, 96 GB RAM, 319 GB free on the internal SSD, hard floor 60 GB for FinData) hosts the computation; the feature137panel (≈ 4,300 firms × ~3,700 sessions × 4 frequencies) is < 2 GB.138139## 7. External sources and exceptions140141| Need | Source | Status | Why not FinData |142|---|---|---|---|143| Reveal dates / release timing | FinData Postgres `earnings_events` (read-only `psql` on M3U96a) | available | not exposed by the API (gap logged for the FinData backlog) |144| CIK, SIC industry | SEC `company_tickers.json` (10,423 rows) + `submissions/CIK##########.json` (`sic`, `sicDescription`, `exchanges`, `formerNames`) | verified reachable (fair-access UA) | entity master has no CIK/sector |145| Daily factors (H5 only) | Kenneth French library, FF5 2×3 daily + momentum daily | verified (HTTP 200, updated 2026-10-26) | not a FinData dataset |146| Analyst consensus | vendor `eps_estimate` in `earnings_events` (24–27 % missing, vintage unknown) | exploratory only | no point-in-time consensus history available |147| TAQ / quotes / order flow | none | **not available** (vendor supplies bars only) | bar-based proxies are *never* called order flow, spreads or informed trading |148149## 8. Gaps, risks and recommendations to the owner1501511. **Delisted fundamentals backfill (recommended, not run).** A one-off `fundamentals` job for the 8,069 `-DELISTED`152 tickers (vendor returns data for at least part of them) would remove the survivorship conditioning of the accounting153 panel. Estimated cost: ≤ 8,069 vendor calls (1 req/s policy ≈ 2.3 h), a few hundred MB in Postgres. Requires: owner154 approval, `findata ingest fundamentals --delisted` (does not exist yet; connector change on M3U96a `~/apps/findata`).1552. **Earnings events API route** — expose `fundamentals.earnings` through `/api/v1/earnings/{entity}` so that WP39 (and156 future projects) stop reading Postgres directly.1573. **SEC EDGAR connector** (`fundamentals.sec_companyfacts`, pending a User-Agent setting) would give filing-level158 as-originally-reported values for the stringent PIT specification.1594. API bug found: `POST /admin/keys` returns HTTP 500 (`api_keys_user_id_fkey` violation) when the caller is an API key160 whose `user_id` is not in `users`; the CLI `findata create-key` works. Logged for the FinData backlog, not fixed here.161162## 9. License and privacy constraints applied163164FirstRate Data: perpetual private use, research and derived statistics allowed with attribution, **no redistribution of raw165records**. Consequently: no raw bars or fundamentals are committed; `data/raw` stays empty; the replication package ships166code, manifests (hashes, versions, counts), derived aggregate tables and figures only; the manuscript attributes FirstRate167Data via FinData OS. SEC and Kenneth French data are public.168