Parsing SEC 13F filings: the CUSIP problem nobody warns you about

Every quarter, roughly 45 days after quarter end, thousands of institutional investors file Form 13F with the SEC. The filing lists holdings by CUSIP, not by ticker. If you build anything on top of 13F data, the first real problem you hit is not parsing the XML. It is what happens to CUSIPs you have never seen before.

I run 13Foresight, a 13F tracker built on FastAPI and PostgreSQL. Here is what broke for us after the Q2 2026 season, and how we now handle it.

The symptom: holders that quietly disappear

Our holdings table maps each CUSIP to a ticker when a filing is processed. A CUSIP we cannot map gets ticker = 'N/A'. After Q2 2026 that had two visible effects:

• On Honeywell's page the holder count fell from 2,773 to 172, because most funds reported a new CUSIP for the same company and the new one had no ticker yet.
• SpaceX, held by 1,724 filers that quarter, was missing entirely.

Nothing crashed. The data was simply incomplete, and every "who owns this stock" number was wrong in a way that is hard to notice.

Where new CUSIPs come from

A stock does not keep one CUSIP forever. In our data the unmapped ones came from:

• IPOs: a brand new security, no history to reuse.
• Spin-offs: a new CUSIP for a company that did not exist as a separate holding the quarter before.
• Re-domiciles and share-class changes: the same company, a new CUSIP.

What we do about it

1. Extract unmapped CUSIPs after each season from the holdings table into a work queue.
2. Resolve them through OpenFIGI to a ticker, and store the result with a status so a failed lookup is not retried blindly.
3. Keep a fallback on the first 8 characters of the CUSIP (the issuer and issue part, without the check digit), because the same security sometimes appears with variations.
4. Check by hand. For a short list of new securities we compared the OpenFIGI answer with the previous ticker of the same company before trusting it.

Setting the ticker is not enough

The part we underestimated: once a ticker is set, everything derived from it is stale. Prices have to be fetched, then quarterly returns, cumulative returns and ranking metrics recomputed for every affected fund.

Two lessons from getting this wrong on a first attempt:

• Recompute each fund once, not once per CUSIP. Fixing 22 securities one at a time means a fund holding several of them is recalculated over and over. Set all tickers and prices first, collect the affected fund and quarter pairs, then recompute each fund a single time from its earliest affected quarter.
• Do not treat a spin-off as a purchase. A fund that receives spun-off shares did not decide to buy them. If you compute "most bought this quarter", spin-offs need a corporate-action flag or an exclusion list, or they will top the chart for the wrong reason.

Also note that the latest quarter of every fund is special: its end price does not exist yet, so a recalculation must not write a real return into the placeholder row, or the next season's processing will be corrupted.

A related trap: values scaled by 1000

About 4% of the funds in our database report the value field multiplied by 1000. Do not build anything on absolute values without checking. Percent of portfolio is stable, so we use that for comparisons.

Checklist for your own 13F pipeline

• Run a CUSIP mapping cycle after every filing deadline, plus a week or two for late filers.
• Alert when the share of N/A holdings by value grows, not only when a job fails.
• Add a database index on the CUSIP column before you need to remap anything at scale.
• Keep corporate actions out of "bought" and "sold" statistics.
• Compare holder counts for a few large names before and after every update.

If you want to see the end result, every fund and stock page on 13foresight.com uses this pipeline, and the fund rankings are built from a backtest that buys a portfolio only after its filing became public.

Disclosure: research and drafting assisted by AI; data, backtests and final analysis by 13Foresight.