5 Checks Before You Trust Extracted Bank Statement Data

Technology | November 16, 2026

5 Checks Before You Trust Extracted Bank Statement Data

Two of these checks catch most problems. The other three tell you exactly where the problem is.

Damin Mutti

A client sends 14 months of bank statements as PDFs. Someone, or some software, turns them into a spreadsheet. Then the spreadsheet becomes the books or an exhibit in a dispute, and nobody looks at the PDFs again.

That’s the step I’d never skip. It doesn’t matter whether the rows came from an extraction tool or a staff member keying them in at 9 p.m. Extracted data is a copy, and copies drift. A line gets read twice at a page break. A refund comes through as a withdrawal.

The good news is that a bank statement checks itself. It prints its own opening and closing balance, and usually its totals. If you know what to compare, you can prove a statement’s data in a few minutes in Excel.

Set the sheet up like this: date in column A, description in B, amount in C (deposits positive, withdrawals negative), the printed running balance in D if the statement shows one, and the page number in E. Then run these five checks.

1. Tie the statement to its own printed balances

Opening balance plus deposits minus withdrawals has to equal the printed closing balance. Exactly, to the cent.

In Excel: =ROUND(Opening + SUM(C:C) – Closing, 2). The answer should be 0.00. Use ROUND, because Excel’s floating point math will sometimes show a difference of 0.0000001 that isn’t real.

Here’s a worked example with made-up numbers. A March statement shows:

  • Opening balance: $12,480.15
  • 9 deposits totaling $18,640.00
  • 41 withdrawals totaling $21,312.47
  • Closing balance: $9,807.68

The printed figures agree: 12,480.15 + 18,640.00 = 31,120.15, and 31,120.15 minus 21,312.47 is 9,807.68.

Now the extracted data. The deposits add up to $18,640.00. The withdrawals add up to $21,762.47. So the spreadsheet says March closed at 31,120.15 minus 21,762.47, which is $9,357.68. That’s $450.00 short of the bank.

Don’t fudge it with a “difference” line. The size of the gap tells you where to look. A gap equal to one transaction usually means a missing or duplicated row. A gap equal to twice a transaction usually means a flipped sign: an $85.20 refund booked as a withdrawal throws the total off by $170.40. A gap that divides evenly by 9 often points to transposed digits, like $1,540 keyed as $1,450.

Our $450.00 gap matched one check exactly. Hold that thought.

2. Chain every month to the next

Each statement’s closing balance must equal the next statement’s opening balance. April opens at $9,807.68 or something is wrong.

Lay the months out in a small table, one row per statement, and add a column: =ROUND(Prior closing – This opening, 2). Every row should be zero, and the statement dates should run end to end with no gap.

This is the check that catches the statement nobody handed over. Say you have January through April and June. April closes at $14,215.90. June opens at $8,930.12. You don’t need to guess what happened in May. You need the May statement.

It also catches quieter problems. If the dates line up but the balances don’t, you may have a statement from a second account with a similar number, or a document that was edited before it reached you. In litigation and fraud work that question matters more than any single transaction, and a balance chain is the fastest way to raise it.

3. Walk the running balance line by line

Checks 1 and 2 tell you a statement is off. Many statements print a balance after each transaction, and that lets you find the exact line.

In a helper column, starting on the second transaction row: =ROUND(D2 + C3 – D3, 2). Each row takes the previous printed balance, applies this row’s amount, and compares it to this row’s printed balance. Fill it down. Every result should be zero.

Back to March. Most of the column is zeros. Then one row shows -450.00. It’s check #2207 for $450.00, and the row right above it is the same check. It sat at the bottom of page 2, and the extraction picked it up again at the top of page 3. Delete the duplicate and checks 1 and 3 both go to zero.

If a statement has no running balance, use the daily balance summary some banks print at the end, and check each day’s closing figure instead.

4. Count rows and test dates, signs, and duplicates

Most statements print a count next to the totals: “9 deposits, 41 withdrawals.” Compare: =COUNTIF(C:C,”>0″) for deposits and =COUNTIF(C:C,”<0″) for withdrawals. In March the spreadsheet had 42 withdrawals against 41 printed. That’s the duplicate again, found a different way.

Count pages too. If the PDF footer says “Page 3 of 6,” make sure column E has rows from all six pages, or that the pages with none are really just notices.

Then three quick formula tests. Dates outside the statement period: =OR(A2<Start, A2>End) should be FALSE everywhere. Possible duplicates: =COUNTIFS(A:A,A2,B:B,B2,C:C,C2)>1, and look at every TRUE by hand, because two identical coffee charges on the same day can be real. Signs: sort by description and scan for words like “refund,” “reversal,” or “deposit” sitting on a negative amount.

5. Make every row traceable to its page

Every row in the spreadsheet should say which page of which statement it came from. That’s column E, plus the file name if you’re working across many statements.

This sounds like housekeeping. It isn’t. When a reviewer, a partner, or opposing counsel asks where a $3,800 transfer came from, you want to open the PDF to the right page in seconds, not search 14 months of statements. Traceability is also what makes checks 3 and 4 fast to fix. A flagged row with a page number is a two-minute job. Without one, it’s an afternoon.

If your tool doesn’t give you page numbers, add them while you’re checking. It’s worth the time once.

The takeaway

Don’t let extracted data near the books until each statement ties to its printed opening and closing balances, and each month chains to the next. Those two checks catch most problems. The other three tell you exactly where the problem is.

We build these checks into software at ZeraBooks, and a statement that doesn’t tie never gets used. But you don’t need any particular tool to do this. You need the PDF and the habit of believing the bank’s numbers over the copy.

ABOUT THE AUTHOR:

Damin Mutti is the founder and CEO of ZeraBooks, AI bookkeeping software that runs your books completely automated. Its bank statement converter checks every statement against the bank’s printed opening and closing balances before the data is used.

Photo credit: d3images/Freepik

Sign in to get access to this free resource, and all of our whitepapers and reports.

Download this content today!

Register to get free access to this content, as well as newsletters, continuing education, podcasts, and more…

Leave a Reply