Every advisor who works with credit unions has a version of the same spreadsheet. It pulls a dozen ratios from the NCUA 5300 Call Report for the institutions on a shortlist, colors the cells, and ranks them on some composite the partners trust. It is the firm’s screening heat map, it has been right often enough to keep, and it is slow enough that nobody enjoys the quarter it has to be refreshed.
The regulator publishes everything needed to replace it. We have built that replacement twice. Here is what changes when you do, and what to watch for in the data.
The data is bigger than the spreadsheet admits
The NCUA releases call report data quarterly as a zip of roughly twenty wide tables, one row per credit union, with about 3,400 account codes across them. A shortlist spreadsheet uses perhaps thirty of those codes for perhaps fifty institutions. The full release covers every federally insured credit union, which today is a little over 4,300 active institutions and more than 5,200 once you include the ones that have merged or closed over the last five years.
That gap is the point. A shortlist only contains institutions someone already thought of. A full-universe screen surfaces the ones nobody did: the $400 million institution two states over with the ratio profile of a merger candidate, or the peer group that makes your client’s numbers look different than the partners assumed.
What a real screen looks like
Three things separate a platform from a spreadsheet with more rows.
A long-form fact store. Twenty wide tables melted into one table of facts: charter, account code, quarter, value. About fourteen million facts per quarter. Account definitions are versioned, because the regulator changes them, and every ingestion is recorded so a renamed column never silently changes a number.
Your metrics, computed once. The ratios from the regulator’s Financial Performance Report, plus the partners’ composite score, calculated in code and percentile-ranked across the whole universe. The score finally becomes consistent because it is computed in one place instead of retyped in forty cells.
History that is cheap to query. Twenty quarters of the full universe is close to 300 million facts. Loaded into a warehouse and clustered by institution, a single credit union’s drill-down scans a fraction of a percent of the table and storage costs under a dollar a month. The history stops being a stack of old spreadsheets and becomes a question you can ask.
The traps
Three things in the data will bite anyone who builds this without having done it before.
The schema jumped in March 2022. The regulator added three tables that quarter and the account count went from about 2,600 to about 3,200. A pipeline that assumes a fixed column set breaks there, and a pipeline that silently drops the new columns reports wrong numbers without knowing it.
Blanks and zeros are different. Some account codes are blank for every institution in a quarter. Those should be dropped. Reported zeros are real values and should be kept. Confusing the two either inflates your fact count with noise or deletes real data.
Quarterly ratios are not annualized. Return on assets in the call report is the discrete quarter unless you annualize it yourself. Firms differ on which convention they use. Match the convention your partners already use, or every number will look wrong to the people who need to trust it.
What the firm gets
Coverage of every institution instead of a shortlist. A quarterly refresh that takes minutes. A composite score that is finally consistent. And a system the firm owns, running on its own cloud project, that can be extended when the next question comes up.
The case study for the first build is on our work page. A white-labeled version is available as a private preview for other advisors and institutions, and we are glad to walk through it.