Data

The search that found nothing

Accountants are increasingly data analysts. They should borrow the laboratory's oldest habit: proving that a test can say yes before believing it when it says no.

LPLP
Data
In this article

The short version

  • An empty search result is not a fact until you have shown the search can find something.
  • Run the same search for an item you know exists, close in date and amount, before you believe a “no such transaction.”
  • Extracted data has to foot back to the source’s own totals before you use it.
  • Two identical charges on the same day are often two real charges. Count rendered rows, not a deduplicated set.

In a biology lab, a careful researcher does not report a negative result without a positive control. If a test for a protein comes back empty, the first question is whether the test would have detected the protein had it been there. So alongside the real sample runs a second one known to contain it. If the control lights up and the sample does not, the negative means something. If neither lights up, the experiment has measured nothing except its own failure.

Accounting has no equivalent habit, and it needs one. Much of modern bookkeeping consists of asking software questions and believing the answers. Is there a payment of $368 in March? Search returns nothing. Has this transaction been edited since it was reconciled? The audit log for the period is empty. Are there any deposits from this processor after May? The filtered register shows none. Each empty result gets treated as a fact. Very often it is a filter that did not work.

How often does accounting software return nothing by mistake?

Over a single reconciliation engagement this year I kept a tally of the times a search or filter in a widely used accounting package returned nothing when the thing I wanted was there. There were eight. Among them: a search box that matched only when the amount was typed with a dollar sign. A register date filter that silently ignored the range I entered. An audit log whose custom date range returned nothing at all. A reconciliation screen that loaded transactions lazily and stopped at 150 rows, so the 151st, however clearly present, could not be found by scrolling.

None of these is a scandal. Every piece of software has quirks. The danger is in the asymmetry of the results. A filter that wrongly returns something gets noticed immediately, because the something is visibly wrong. A filter that wrongly returns nothing looks exactly like the truth. In a reconciliation, “no such transaction” leads straight to a conclusion: the item is missing, post it. Post it on the strength of a broken filter and the item is now in the books twice.

The remedy is cheap. Before believing a search that returns nothing, run the same search for something known to exist, ideally something similar in date and amount. If that comes back, the empty result has earned some credibility. If it does not, the tool is what needs investigating, not the ledger.

What happens when a script drops the decimal?

The same logic applies to the code accountants increasingly write themselves. A short script that extracts transaction lines from a stack of credit-card statements is reasonable automation. It turns a day of typing into a minute of processing. One such script, run on four months of statements, reconciled to the penny in two months and missed by a cent in one and by exactly a dollar in another.

The cause was a pattern that expected at least one digit before the decimal point. The card issuer printed amounts under a dollar as “.01” and “.99”, with no leading zero, so every sub-dollar charge was invisible to the extraction. The books were right. The extracted data was wrong, and the script reported it with complete confidence. The fix took a minute. Finding the problem took the habit of footing the extracted lines back to the statement’s own printed totals before using them for anything.

When are duplicate transactions real?

The instinct to clean data can be as dangerous as the failure to check it. Analysts are trained to deduplicate. In transaction data, though, two charges on the same card, on the same day, for the same amount, from the same merchant are frequently two genuine charges. A car-sharing account in one engagement produced thirteen such pairs in four months. Deduplicating them would have deleted real expenses and created a reconciliation difference nobody could explain.

My working rule is to count rendered rows, as they appear in the source, and never a deduplicated set. Where two lines look identical, match them one-to-one against an independent record, such as the card statement, instead of collapsing them.

A short field guide

For finance teams doing more of their own data work, four habits matter:

  • Give an empty result a positive control before you believe it.
  • Foot extracted data back to the source’s own totals before you use it.
  • Count what is rendered, not what survives deduplication.
  • Read a report’s full output rather than what fits on the screen, and check that a running balance lands on the stated total.

Fraud examiners will recognize the principle underneath. Absence of evidence is evidence of absence only when the search could have found the evidence. An accountant who analyzes data without that rule is asking the software for reassurance, and the software is always happy to oblige.