How to verify a converted bank statement in four checks

A converted bank statement looks finished the moment it opens. The columns line up, the dates parse, the amounts sit right-aligned in their cells. None of that tells you whether the numbers in the file are the numbers printed on the statement. A single misread digit produces a spreadsheet that looks exactly as tidy as a correct one.

Four checks settle the question as well as anything can, and none of them needs anything beyond the spreadsheet you already have open. Learn them properly, then apply them as a standard. Any method of getting statements into a spreadsheet, whether that is manual keying, a generic table extractor or a dedicated converter, has to survive these four checks. A method that cannot tell you whether the extraction is complete and correct has not finished the job; it has handed you an unaudited file and called it done.

What the four checks establish, and what they do not

Say this precisely, because it is the part most guidance gets wrong. The four checks below are arithmetic, and arithmetic tests consistency rather than truth. A converted file that passes all four agrees with the bank's own control totals on amounts, on count and on period. That is the strongest automatic evidence available, and it catches the error class that matters most, a wrong digit in an amount, because a misread amount breaks the balance chain arithmetically and cannot hide.

What the checks do not do is verify the fields that take no part in the arithmetic. A description read wrong, a payee truncated, a reference number mangled: none of those move a balance, and none of them will be caught here. Nor is the arithmetic proof against errors that offset one another exactly. A row dropped and a row of the same value duplicated will net to zero in the balance chain, and only the row count will notice. Two amounts misread by equal and opposite amounts leave every total untouched.

Those cases are uncommon, and they are the reason to run four checks rather than one. Treat a clean result as strong evidence that the amounts are right and the extraction is complete, then spot-check a handful of descriptions and dates against the page. That is an honest standard, and it is a far higher one than any file gets by default.

What to have in front of you

You need two things: the converted file, and the statement document itself open at the summary block. Most banks print a summary somewhere on the first or last page giving the opening balance, the closing balance, the total paid in, the total paid out, and sometimes the number of transactions in the period. Layouts vary by bank, by account type and by country, so not every field will be there, but the opening and closing balances almost always are. That block is your control total. It was typeset by the bank, not extracted by anything, so it is the reference the converted data has to agree with.

One warning before you start. If your converted file contains a running balance column that was read off the statement, do not use it as the input to your check. Comparing an extracted column against other extracted columns from the same page proves only that the extraction was internally consistent with itself. Rebuild the balance from the amounts, then compare your rebuilt figures against the extracted balance column.

Check 1: the balance chain

This is the check that matters most, and it is one line of arithmetic. Opening balance, plus all credits, minus all debits, equals closing balance.

Work an example. A statement opens at 12,480.55. Over the period it takes in 8,214.00 in credits and pays out 6,905.38 in debits. Adding the credits gives 12,480.55 plus 8,214.00, which is 20,694.55. Subtracting the debits gives 20,694.55 less 6,905.38, which is 13,789.17. If the statement prints 13,789.17 as the closing balance, the amounts in the converted file agree with the statement as a whole.

The reason this check earns its place at the top is the propagation property. Every amount in the file participates in the equation, so a single wrong digit anywhere in the column moves the result by exactly the size of the misread and cannot be absorbed. There is no version of a misread amount that leaves the chain intact on its own. Only a second, exactly offsetting error can hide the first, and that is a coincidence rather than a failure mode.

In a spreadsheet with a single signed amount column in D and 96 transactions in rows 2 to 97, the whole check is =ROUND(12480.55+SUM(D2:D97),2). Compare the result to the printed closing balance. Better still, subtract one from the other and confirm the difference is exactly zero.

The ROUND matters. Spreadsheets store decimals in binary floating point, so a long column of two-decimal amounts can sum to something like 13,789.169999999998. Rounding to two decimals before you compare stops you chasing a discrepancy that exists only in the last bit of a floating point number.

Check 2: credit and debit subtotals

The balance chain can pass while two errors cancel each other out. It is rare, but it happens most often with sign errors: a debit captured as a credit and a credit of the same size captured as a debit leave the net movement untouched. Checking the two subtotals separately against the statement's printed totals narrows that gap considerably, because an error has to survive two independent totals rather than one.

With a signed amount column, =ROUND(SUMIF(D2:D97,">0"),2) gives the credits and =ROUND(ABS(SUMIF(D2:D97,"<0")),2) gives the debits as a positive number. The ABS matters only for the comparison: the raw SUMIF returns a negative figure, while most statement summaries print the paid-out total unsigned. Put both on the same footing before you compare, then check 8,214.00 and 6,905.38 against the paid-in and paid-out figures in the statement summary. If your file uses separate debit and credit columns rather than one signed column, sum each column directly; the comparison is the same.

A sign flip is a common reason for this check to fail while check 1 passes, though the two errors have to be equal in size for check 1 to have passed at all. It also catches a whole transaction landing in the wrong column, which some layouts invite by printing debits and credits in adjacent, narrow columns.

Be clear about the limit here too. Two subtotals are harder to fool than one, but they are not impossible to fool. Two errors of the same sign that cancel within the same subtotal will pass both, as will a row dropped and a row of equal value duplicated on the same side. Check 3 is what covers that case.

Check 3: row count against the statement's own count

Many statements print the number of transactions in the period. Where they do not, the count is still recoverable: number the transaction lines on the statement pages, or use the statement's own line numbering if it has one.

Compare it to =COUNT(D2:D97), which returns 96 for our example. This is a genuinely separate check, not a duplicate of the balance chain, and it is the check that covers the offsetting-error cases the arithmetic cannot see. A row dropped and a row of the same value duplicated leave every total identical and change nothing but the count.

It also covers a second case the balance misses entirely: a row with an amount of 0.00. Depending on the bank, a waived fee, a reversed charge, a zero-value transfer or a memo line may post as a row carrying 0.00, may be shown as an offsetting pair, or may not appear as a transaction line at all. Where your bank does post them, gaining or losing one changes the row count and leaves the balance completely untouched. If you only ever check the balance, a dropped 0.00 row is invisible.

The count also catches the opposite problem, where a header or a "balance brought forward" line has been captured as if it were a transaction. If your count is one high and your balance still matches, that carried-forward line is the first thing to look at. When the count is short by more than one or two, the causes are usually structural: a page whose table was never detected, a continuation page read as a header, or a section the converter skipped outright.

Check 4: the date range

Take =MIN(A2:A97) and =MAX(A2:A97) on the date column and compare them to the period printed on the statement. The first transaction date should fall on or after the period start, and the last on or before the period end.

Two things break this check. The first is a page from a different statement mixed into the batch, which is common when a client sends a single bundle of scans. The second is a misparsed date: an entry of 03/04 read as 3 April in one file and 4 March in another puts a transaction in the wrong month without changing any amount. If the dates in your file are stored as text rather than real dates, MIN and MAX will return nothing useful, and fixing statement dates that import as text is the prerequisite step.

Putting the four checks together

1. 1

   Open the statement summary

   Find the printed opening balance, closing balance, paid in, paid out and transaction count on the statement document.
2. 2

   Rebuild the closing balance

   In an empty cell, add the opening balance to the sum of the amount column, round to two decimals, and compare it to the printed closing balance.
3. 3

   Split the subtotals

   Sum the positive and negative amounts separately and compare each to the paid-in and paid-out figures.
4. 4

   Count the rows

   Count the transaction rows and compare to the statement's stated number of transactions.
5. 5

   Bound the dates

   Take the minimum and maximum date and confirm both fall inside the statement period.

Written down as a control sheet, a clean result looks like this:

To restate those figures in plain text: opening 12,480.55, credits 8,214.00, debits 6,905.38, rebuilt closing 13,789.17 against a printed closing of 13,789.17, 96 rows against a stated 96 transactions, and every date inside the period. All six agree. The amounts reconcile in total and by direction, the population is the size the bank says it is, and nothing has strayed outside the period. Add a two minute scan of the description column against a page or two and the file is as verified as a file gets.

The check should arrive with the file

One statement is a minute of arithmetic. A client's year is twelve statements, a catch-up engagement is several years, and a practice runs that across a roster. At that point the checking is not a quick sanity pass at the end; it is the work. Building a control sheet for every file, keying in six figures from every summary block and chasing the ones that disagree is a real cost, and it is the cost that makes people quietly stop checking.

That is the wrong place to economise, and it is the reason Convert·Into computes the verdict rather than leaving it to you. The running balance is recalculated line by line on every statement and compared against the balance printed on the document, so check one is already answered when the file lands, and the rows that failed it are the rows in front of you. Extraction accuracy is 99.6%, which is why the flagged rows are the exception rather than the routine.

Judge every option this way. If a tool cannot tell you whether the numbers it produced add up, the check has not been eliminated; it has been handed to you unpriced.

When a check fails

A failure is information, not a disaster. The size of the discrepancy is the best lead you have, though it is a lead rather than a verdict, because a gap can also be the net of several errors at once. If the gap equals a single transaction amount in the file, look first at whether that row is duplicated or a row of the same value is missing. If it equals exactly twice a transaction amount, look first for a sign error on the row of half that value. If it is divisible by nine, two digits inside one number have probably been transposed. Confirm whichever lead you follow by reading the suspect row against the printed page. Working through a failure systematically is the subject of what to do when a converted balance does not reconcile.

If the gap is small, odd and does not correspond to any whole transaction, suspect a single misread character. That class of error is covered in catching OCR digit errors in statements, and it is the reason the balance chain is worth running even on statements that came from a clean digital source.

Make the four checks your acceptance criteria

Treat these four checks as a gate rather than a courtesy. Files that pass go into the ledger. Files that fail get one row investigated. Nothing that has not been through the gate reaches a reconciliation, which is where an unverified number costs the most time to find.

Applied consistently, the gate also settles arguments about method. Manual keying produces no control totals of its own, so someone has to build them afterwards. A generic table extractor pulls the rectangles it finds on the page, and unless it has been configured with statement-specific validation rules it has no concept of an opening balance, so it cannot tell a transaction from a header and cannot know when it has dropped a row. Both approaches can be checked, but only by you, one file at a time.

A statement converter should meet the standard on its own. Opening plus credits minus debits equals closing is not an optional extra feature on top of extraction; it is the minimum evidence that extraction worked. Ask any tool you are evaluating what it does when that equation fails. The answer separates finished work from a pile of rows.