The Cost of Honest Data: Null Handling in Financial APIs

Every financial API has to answer a specific question: what do we return when the data doesn't support the metric? The usual options are zero, null, an error, or silently-truncated nonsense. Most APIs pick the worst of these. We picked null, and in the process made some consumers unhappy. Here's the reasoning.

The trigger

Our analytics.benchmark() endpoint returns percentile ranks for a company's capital, board size, change frequency, and age against its industry peers. Internally: fetch ~2000 peers, compute each dimension's median, compute the target's rank.

In v1 a tied value at the median returned percentile: 0. An SDK customer complained: "ABB shows 0th percentile on board size even though it has 15 board members. That can't be right."

It wasn't. The bug was a naive percentile formula: count(peers_with_value_below_target) / total. When the target equals the median, count_below is 0, and with zero peers scoring strictly lower, the rank comes out to 0.0. Fine for company < median; meaningless when company == median.

The fix was a midrank percentile: (count_below + 0.5 * count_equal) / total. Ties land at ~50th percentile. One-line fix.

The second bug, which was harder

A week later the same customer reported: "UBS shows industryMedian: 0 and percentile: 0 on capital, even though UBS has CHF 385M in capital. What?"

The peer cohort for UBS was the ~2000 other Zurich companies with status=Active (we fall back to canton when industry is null for the target). Median capital of that cohort: zero. Why? Because ~90% of small Swiss GmbHs don't have their share capital recorded — the DB field is null, which at aggregation time SQL coerces to 0, which dominates the median.

The first instinct was: filter out null/zero values from the peer cohort before computing the median. That works on median. It doesn't work on percentile, because now the target (UBS, CHF 385M) is being ranked against a filtered cohort that all have positive capital, producing a more interesting percentile. Which is the correct thing to do.

But then a third case: what if the target itself has 0 / null on that dimension? Now the target is compared against only peers with real data, and we get a 0th-percentile result that looks like "UBS is the worst-capitalised company in Zurich". It's actually "we don't know UBS's capital either".

The choice

Three options, each loud:

1. Return 0. Currently the lie of least resistance. The number looks valid. The caller has no way to tell it's fabricated.
2. Return the actual computed value but ignore missing data. The median-vs-filtered-peers trick. Mathematically coherent. Still gives you a number that looks meaningful when it isn't.
3. Return null. Explicit absence. The caller knows there isn't an answer.

We picked option 3 and added a fourth field: peersWithData. If the peer cohort has fewer than 5 observed values on a dimension, percentile and industryMedian are null, and peersWithData is the small integer that tells you why.

{
  "name": "capital",
  "companyValue": 385840846.6,
  "industryMedian": null,
  "percentile": null,
  "peersWithData": 0
}

The pushback

Three kinds:

"Null breaks my dashboard." Yes. If your dashboard has a column for "percentile" and expects a number, you now need to render "insufficient data" instead of a bar-of-length-zero. That's the whole point — your dashboard was previously lying. Our SDK makes the type explicit (Optional[float]) so static analysis catches this at refactor time.

"Just default to 50th percentile when absent." Worse than null. You've now chosen a value that looks interpretable. Consumers will read "UBS is median Zurich in capital terms" and act on it. Null forces a decision: display something else, skip the row, surface the gap.

"The p99 of our users don't care about edge cases." Probably. But the edge cases in this API are specifically about large, interesting, audited companies — UBS, Novartis, Nestlé's subsidiary network — because those are the entities with inconsistent data coverage. Getting them wrong is the thing we can't afford.

What the right shape looks like

An opinionated rubric for financial-data APIs:

1. Optional everywhere, by default. If a field can be missing from the source data, its type must reflect that. No Float.NaN, no -1, no empty-string-as-null.
2. Distinguish "absent" from "zero". These are different facts. The API type system must let callers tell them apart.
3. Attach a data coverage note on aggregate endpoints where the aggregation can legitimately produce a meaningless number. Our analytics.flows() carries one explaining that dissolution counts before 2025-Q1 are under-counted because of a historical data-backfill gap. A one-line field on the response; a permanent improvement to caller trust.
4. Surface provenance. Our industry classification carries industrySource (sogc / llm / manual) and industryConfidence. Consumers can filter on those if they want ingest-verified data only. Silently auto-classifying everything as "ours-is-the-only-source" would be the dishonest default.
5. Fail loud on bad state. An LLM returning a 400 Bad Request should bubble up as a ServiceUnavailableError, not a 500 Internal Server Error with a generic message. Callers can handle a specific error type; they can't handle "something broke somewhere".

What this costs us

A support question on Slack every few weeks: "Why did benchmark return null for my company?" The honest answer: because we don't have enough peer data. Then we explain how the target's industry is null, which broadens the cohort, which has its own coverage issue.

Conversations that would never have happened if we returned fake numbers. But each one is a real feedback loop — it tells us where to invest in data coverage (we're backfilling industry via LLM classification right now, expecting coverage to triple in the next month). A dashboard that silently shows 0 would never have produced that feedback.

Links

• Benchmark API: vynco.ch/docs/benchmark
• SDK: github.com/VynCorp/vc-python