A status report on closing the gap between a raw 13F extract and what a client evaluating us against FactSet actually asked for: a CUSIP that can be trusted this quarter, and traced to the same economic position next quarter through a split, a merger, or a CUSIP change.
The client's framing, in their own words: “FactSet’s edge is that layer, not the raw XML parse.” Everything below is built against that standard — not against “can we parse the filing,” which we already could.
Every CUSIP in holdings was stored exactly as a filer typed it: no case normalization, no zero-padding, no check-digit validation, and — because the join between last quarter and this quarter keys on the raw string — no way to tell a real trade from a corporate action. A merger, a reverse split, or a reticker read as a full exit on the old CUSIP and a brand-new position on the new one. We had a downstream statistical flag (corporate_action_suspect) that could tell you something happened to roughly 80–150 CUSIP clusters a quarter; it could not tell you what, or link the two sides.
That is the exact gap FactSet's CUSIP Global Services relationship fills, and the exact gap this client is pricing us against.
Phases 0–4 of the plan · additive only · zero changes to the live quarter-over-quarter join
cusip_validate.py implements the ANSI X9.6 / CGS modulus-10 check digit — public arithmetic, no CUSIP Global Services license required — plus 9-character normalization (uppercase, zero-pad the classic “leading zero dropped” case). Two new columns ride alongside the original, unmutated cusip so the audit trail the client asked for (reported → normalized → canonical → event) exists from the first column, not bolted on later. Wired into the live parser so every new filing gets it for free; the 77.2M existing rows were backfilled in one pass.
| Pos | Char | Value | ×2 (even pos) | Digit sum |
|---|---|---|---|---|
| 1 | 0 | 0 | — | 0 |
| 2 | 3 | 3 | 6 | 6 |
| 3 | 7 | 7 | — | 7 |
| 4 | 8 | 8 | 16 | 7 |
| 5 | 3 | 3 | — | 3 |
| 6 | 3 | 3 | 6 | 6 |
| 7 | 1 | 1 | — | 1 |
| 8 | 0 | 0 | 0 | 0 |
This isn't a toy example — it's Apple's actual CUSIP, hand-verified before anything was trusted to run against 77 million rows. The same routine was independently verified against Berkshire Hathaway B, Microsoft, and a real CINS-format identifier pulled from our own 13D data (G13311108, discussed below) before it touched production.
cusip_mapping, the table every ticker lookup depends on, existed only in the live database — it was never in schema.sql. A clean rebuild from source could not have reproduced it. Fixed; no shape change.
Ingested the SEC's Official List of Section 13(f) Securities for every quarter back to our data's start — the list SEC itself publishes for exactly the validation use the client is asking for (“on_13f_list_for_period”, “list_status”). Three real obstacles surfaced and were resolved before trusting the pipeline, detailed below.
cusip_succession_events is the append-only evidence ledger (old CUSIP, new CUSIP, event type, effective date, split ratio, source) — the exact shape the client's spec asks for. cusip_canonical is the flat, always-one-hop lookup a join would actually use, derived from the ledger by chain-collapsing so a security that changed labels twice still resolves in one step. Both mirror cik_canonical, the manager-identity table this pipeline already trusts for the same class of problem on the filer side.
detect_cusip_successions.py proposes candidates from three angles — a paired mass-exit/mass-new CUSIP under one issuer in the same quarter, corroborated against SEC name-change history where available; and a separate review list of issuers whose 13D/13G filings carry more than one CUSIP over time. It writes nothing. Every candidate is a starting point for a human to verify, the same discipline the existing manager-identity detector already uses.
Five things that only surfaced by testing against real filings, not by reading the spec
Everything through 2021Q1 lives under one path; 2021Q2 onward moved to another. A hardcoded template would have silently 404’d every quarter for the last five years. Fixed by discovering URLs from the SEC's own index page instead of guessing a pattern.
CALL/PUT rows on the SEC's own list fail our check digit ~99% of the time — not a bug. They're OCC-style option symbols sharing the 9-character slot, not ANSI X9.6 CUSIPs. Confirmed against 19,022 real rows before writing this off as noise; the health check now excludes options from the check-digit alert so it never cries wolf on data that's supposed to look this way.
~2–8% of rows per quarter repeat the same CUSIP, almost always identical option contract rows. A handful disagree only on whether the security was flagged ADDED that quarter. Resolved by keeping the more informative copy; logged, not hidden.
Synthetic test: CUSIP A renamed to B, B renamed back to A. The first version of the chain-resolver detected no problem and pointed each one at the other — wrong, and wrong silently. Caught in testing before any real data touched it; the resolver now recognizes the cycle and excludes both sides for manual review instead of guessing.
While rebuilding the manager-performance price layer for speed (an unrelated but adjacent fix), a boundary check let a quarter-end's mark fall on the next day's close. SPY's 2019-03-31 mark came back as $285.83 instead of the correct $282.48. Caught by comparing against a known-good run before trusting the rebuild; fixed and reverified across all 30 quarters.
The client's spec warns that a CUSIP can go through multiple, overlapping, non-sequential changes — not a clean A → B cutover. We didn't have to invent a test case for this: it's already in our data.
Three distinct CUSIPs, all filed against the same issuer, in overlapping windows — not sequential replacement:
| CUSIP | First seen | Last seen | Filings |
|---|---|---|---|
| G13311108 | 2026-04-07 | 2026-08-21 | 9 |
| G13311116 | 2026-05-15 | 2026-08-14 | 3 |
| G13311132 | 2026-05-29 | 2026-05-29 | 2 |
This is exactly why the detector reports candidates rather than auto-pairing “earliest” against “latest.” detect_cusip_successions.py surfaces all three under one issuer for a human to resolve — the honest output, given the real data doesn't cooperate with a simple sequential-chain assumption.
The SEC's own published list carries this notice on every page: “CUSIP Numbers and descriptions are used with permission by Standard & Poor's CUSIP Service Bureau… No redistribution without permission.” Using the list to validate our own already-filed CUSIPs — which is all Phases 0–4 do — is exactly the use the SEC publishes it for. Re-exposing the raw issuer names and descriptions as a bulk feed to an external consumer is a different question, and one this report is flagging rather than deciding.
Section numbers match the client's own document
| Spec section | Ask | Status | Note |
|---|---|---|---|
| §1 | Extraction fields (CUSIP, value, shares, filing metadata) | done | Pre-existing; this was never the gap. |
| §2 | CUSIP validation — check digit, 13(f)-list status, exception fields | done | Check digit + normalization live on every row. List-status join is data-ready; the consumer-facing exception view is the one piece of §2 still in Phase 5. |
| §3 | Succession semantics (name change / split / merger / reorg) | done | Ledger schema covers every event type the client listed, including the “CUSIP usually stays” vs. “new CUSIP assigned” distinction. |
| §4A | Security master (issuer, type, linked identifiers) | in queue | Deliberately thin: we reuse the existing ticker/CIK table rather than a second master, per the client's own “could Claude+web produce this” test for what's worth building new. |
| §4B | CUSIP succession / event table | done | cusip_succession_events + cusip_canonical, live, empty pending curation of the 9,837 candidates. |
| §4C | Holdings joined to a permanent security key | in queue | Phase 5 — wiring cusip_canonical into the live quarter-over-quarter join. Not started on purpose: this touches every downstream consumer (Qdrant, MCP, the mars mirror, Raul's front end) and should wait for a human to curate real candidates first. |
| §4D | QoQ logic that doesn't invent trades on a split/merger | in queue | Same Phase 5 dependency, plus split-ratio share adjustment specifically for reverse splits. |
| §5 | CGS / paid data sources | out of scope | By design. Built the free-source event pipeline instead; a licensing decision if the client needs recall a free pipeline can't reach. |
| §6 | Pipeline: ingest → validate → resolve → adjust → publish exceptions | in queue | Steps 1–2 (ingest, validate) live. Steps 3–6 depend on Phase 5. |
| §7 | “Done” = one time series across a CUSIP change, audit trail intact | in queue | The audit trail (reported → normalized → canonical → event) already exists end to end. The time-series continuity itself is exactly what Phase 5 delivers. |
Curate a first batch of the 297 highest-confidence candidate pairs (corroborated against SEC name-change history, unambiguous mass-exit/mass-new signature) into cusip_succession_events. Re-run the chain resolver. Then — and only then — wire cusip_canonical into the live join, the same order cik_canonical itself was built in: detector first, curated seed second, production wiring last.