DEV Community

Brandon Sooknanan for StatementReady

Posted on

Bank Statement PDF to Excel, Checked to the Cent: The One Equation That Catches Bad Extractions

Anyone who has pulled transactions out of a bank statement PDF knows the failure mode: the output looks fine, the totals are off by $212.40, and nobody notices until an underwriter does. A missed row, a credit read as a debit, a wrapped description that ate an amount.

There is one check that catches nearly all of it, and every statement already prints the numbers you need.

The equation

Every statement prints an opening balance and a closing balance. Between them, the rows have to explain the difference:

opening balance + total credits − total debits = closing balance
Enter fullscreen mode Exit fullscreen mode

If the extracted rows satisfy that to the cent, you didn't drop or flip anything that matters. If they don't, the size of the difference is a clue: a difference equal to one row's amount means a missed row; twice a row's amount means a credit read as a debit.

In code

// amounts in cents to avoid floating-point drift
function reconcile({ openingCents, closingCents, rows }) {
  const credits = rows.filter(r => r.cents > 0).reduce((s, r) => s + r.cents, 0);
  const debits  = rows.filter(r => r.cents < 0).reduce((s, r) => s - r.cents, 0);
  const expected = openingCents + credits - debits;
  return { credits, debits, expected, difference: closingCents - expected, ok: expected === closingCents };
}
Enter fullscreen mode Exit fullscreen mode

Two details that matter in practice:

  • Work in integer cents. 0.1 + 0.2 is not 0.3 in JavaScript, and statements have hundreds of rows.
  • Check running balances too. When a statement prints a balance after each row, each one should follow from the row before it. A break in that chain points at the exact row to look at.

Where it matters

Mortgage brokers and processors working bank statement loans live on this. A 12- or 24-month income worksheet built from rows that don't reconcile is built on numbers nobody has checked.

Try it without writing anything

The free StatementReady converter does this in your browser. Pick a text-based statement PDF (up to 60 pages) and it gives you the rows as Excel or CSV, plus a balance-check sheet with the opening balance, credits, debits, printed closing balance and the difference. The PDF is read on your device and isn't uploaded. Scanned or photographed statements have no text layer, so they need OCR first.

Top comments (0)