Keeping converted financial records defensible

Yes, the other side can challenge the accuracy of a spreadsheet you built from bank statements, and the challenge is cheap to make. Someone pulls one page of the production, compares four rows against your workbook, finds one figure that does not match, and argues that a workbook with one error in four rows should not be relied on for anything. Whether that argument lands depends almost entirely on decisions you made before anyone looked at the file.

Three practices carry the weight: the original statement documents survive unaltered, the method that produced the rows is recorded and repeatable, and the arithmetic check you ran is documented along with what it can and cannot detect. The third is where most work falls down, because it is tempting to overstate what a reconciling balance proves.

The spreadsheet is a derivative, and it needs its parent

The statement document the bank issued is the record. The spreadsheet is something you made from it. Both need to exist at the end, and the second must always be reproducible from the first.

That has practical consequences on day one. Never edit a source statement to fix a formatting problem in the output. Never save over an original. Keep the file in the format it arrived in, including the scan if what you received was a scan, and store each converted workbook beside the file it came from with a naming convention that makes the pairing obvious to someone who has never seen your folders. If the documents have been stamped for production, the workbook should also carry the page reference for each row, which is the point of producing statements as numbered exhibits rather than as a loose bundle.

Where a converted file forms part of a formal production, the same chain-of-custody logic that governs financial records converted for discovery review applies: the derived file is logged, not just the exhibit.

What the running balance check actually catches

Most bank statements print a running balance beside each transaction. That column is the strongest automatic check available on converted financial data, and it is worth being exact about why.

Take four lines of an account opening at 6,240.00:

Now suppose an extraction reads 1,890.00 as 1,390.00. Nothing about that row looks wrong: the format is right, the magnitude is plausible, and a reviewer scanning the column would not pause on it. But the arithmetic no longer holds. Adding 1,390.00 to the opening balance of 6,240.00 gives 7,630.00, and the statement prints 8,130.00 on that line. The chain is out by 500.00 at exactly the row that caused it.

This is the point of the check. A misread digit in an amount cannot hide, because the amount is used in a calculation whose answer is printed on the page beside it. It localises the error to a specific row rather than telling you the total is off by some figure somewhere. Single-digit misreads are the dangerous class precisely because they are plausible and small, which is the whole subject of catching OCR digit errors in statements.

What it does not catch, stated plainly

A reconciling chain does not prove the converted figures are right. Claiming otherwise in front of someone who understands the arithmetic is worse than claiming nothing, because it invites exactly the cross-examination you do not want.

Start by separating the check just described from the weaker one it is routinely confused with, because they answer for very different things and only one of them is worth much under questioning. Adding the movements to the opening balance and comparing the result against the printed closing figure is a totals check. Anything that cancels walks straight through it. Take the account above running across a full month: lose one 412.35 card payment, count another of the same value and sign twice, and the period still closes on the figure the bank printed. Two amounts read wrong in opposite directions by the same figure pass just as quietly. And when a totals check does fail, what it hands you is a period and a discrepancy, not a row.

Testing each transaction against the balance printed beside it is a different instrument, and it catches that pair outright. A row lost in conversion leaves the row beneath it unable to reach its own printed balance, out by exactly the value of what went missing. A row counted twice claims to move the balance and leaves it where it already stood, so the copy contradicts the figure printed against the row it came from. Rebuild the column forward from the opening balance instead and the same pair reads as a run of wrong balances that begins at the omission and closes at the duplicate. Either way it is found, and it is found at a row number. So do not describe an offsetting pair as something the arithmetic cannot see. It is something the totals check cannot see, which is the reason not to settle for a totals check on a statement that prints the column to do better.

Three classes of error survive even the row-level version.

• Wrong dates. The balance chain uses amounts. A date read as 03/04 when the statement means 04/03 leaves every balance intact and every timeline built from the workbook wrong. Date order ambiguity is unresolvable from a figure below the thirteenth of the month without knowing the statement's locale.
• Wrong descriptions. Counterparty names, cheque numbers, references and any categorisation built on top of them contribute nothing to the arithmetic. A garbled or truncated description passes the check untouched, and in a dispute the description is often the field carrying the meaning.
• Amounts the equation cannot see. A row converted with an amount of 0.00 satisfies its equation whatever balance sits beside it, and a waived fee or a reversed charge can post exactly that way, so gaining or losing one moves nothing and is caught by nothing here. The other case is a balance column misread in a way that agrees with the amount misread beside it, which leaves the equation true on two wrong figures.

There is also a case where the row-level check does not exist. Some banks print no running balance in the transaction table at all, and on those statements there is no per-row figure to test against; the totals comparison is what is left, with every weakness set out above. Write that into the method record rather than glossing it, because "every row reconciles against the printed balance" and "the closing figure agrees" are different sentences, and only the second one is available there.

Saying this out loud is the defensible position. A row-level chain is powerful evidence about the amount column, silent about the rest of the line, and the workbook needs other checks for the rest.

Closing the gaps the arithmetic leaves

1. 1

   Verify each statement against its own source

   Confirm the converted closing balance matches the figure printed on the statement, and that each row's running balance follows from the row above. This is the ordinary discipline of verifying a converted bank statement, applied before anything downstream.
2. 2

   Count the rows against the pages

   Compare the number of converted rows against the transactions visible on the source pages. Page boundaries and wrapped narration are where rows go missing, and the count covers the two cases the row-level arithmetic cannot reach: a row carrying 0.00, and any statement that prints no running balance to test against.
3. 3

   Sample descriptions and dates against the pages

   Pick rows at random, including a few from the densest pages, and read them against the source. Amounts are covered by the arithmetic; this pass covers the fields that are not.
4. 4

   Confirm the date format across every bank

   Check one unambiguous date per account, one above the thirteenth of the month, so a day and month swap on one institution's statements cannot misalign a combined timeline.
5. 5

   Chain the statements, then check the calendar

   Confirm each closing balance equals the next opening balance for the same account across the whole period, and separately confirm the statement periods run end to end with no gap between one period's end and the next period's start. The balances show that the statements you hold join up. Only the period dates address whether a statement is missing between them, because a dormant month closing at the balance it opened on will chain onto the wrong neighbour without breaking any arithmetic.
6. 6

   Record every manual correction

   Mark corrected rows in their own column with what the page showed and what was entered, and keep the uncorrected output too.

Recording the method

If the accuracy of the workbook is questioned, the answer that works is a process someone else can repeat. That means a short, boring record kept with the file: which source documents went in, what produced the rows, on what date, run by whom, which verification checks were run and what they returned, and which rows carry a human correction.

Nobody enjoys writing that down, and its value is entirely retrospective. The alternative is trying to reconstruct months later how a particular figure got into a particular cell, in front of someone whose job is to suggest it got there by carelessness or design. A converted set that reconciles to its source documents, points back at the pages it came from, and comes with a method someone can rerun does not stop the challenge from being made. It just means the challenge ends with the source page rather than with your credibility.