What makes a bank statement converter trustworthy: the balance check

 A bank statement converter is trustworthy when it proves its own output: the opening balance plus every deposit, minus every withdrawal, has to equal the closing balance the bank printed, to the cent, before you download anything. Without that check, a converter can read 49 lines perfectly and swap two digits in the 50th, and the file will look exactly as clean as a correct one. If a tool doesn't run the check, you have to.

Here's why that one equation does so much work, and what it can't do.

What does the check catch?

Dovetail Joinery is a fictional cabinet shop. Its June statement prints:

  • Opening balance: 11,275.90
  • Total deposits: 14,620.00
  • Total withdrawals: 12,947.35
  • Closing balance: 12,948.55

11,275.90 + 14,620.00 - 12,947.35 = 12,948.55. The statement ties to itself. Now suppose a converter reads its 38 lines and makes one of the classic mistakes. The gap left behind tells you what happened:

What went wrongExampleResult against the printed closing
Two digits swappedA 1,836.40 lumber payment read as 1,386.40450.00 too high
A line droppedA 62.00 bank fee missed at a page break62.00 too high
A sign flippedA 300.00 refund read as a withdrawal600.00 too low
A line read twiceA 2,100.00 deposit repeated across a page break2,100.00 too high
A total read as a lineThe printed "Total deposits" picked up as a deposit14,620.00 too high

Every one of those files would open in Excel looking fine. The numbers are formatted, the dates are in order and the descriptions make sense. The only thing that gives them away is the arithmetic against the paper.

The size of the gap is a clue. 450.00 divides evenly by 9, which is the mark of two swapped digits: 1,836.40 - 1,386.40 = 450.00, and swapping any two digits always produces a difference divisible by 9. A gap of exactly one line's amount means that line is missing or doubled. A gap of twice a line means a flipped sign.

What about credit card statements?

The same idea, turned around. Previous balance plus new charges, fees and interest, minus payments and credits, must equal the new balance. Cards with several cardholders add a second layer: each cardholder's charges must match the subtotal printed for them. That catches a charge that was read correctly but given to the wrong person, which a single total would miss.

What can't the balance check catch?

Be honest about the limits:

  • Two errors that cancel. A 50.00 line missed and a different 50.00 line doubled leave the total untouched. Rare, but possible.
  • A wrong date. A line put on the 13th instead of the 31st still adds up. It matters if you're cutting the year off at a date.
  • A wrong description. The amounts tie and the payee is garbled.
  • A missing statement. Each statement proves itself. Nothing in March's statement knows that February was never uploaded. For that, check that each opening balance equals the previous closing balance.

So the balance check isn't the only check. It's the first one, and it catches the errors that change totals, which are the ones that end up in a tax return or a loan file.

How do you test any converter in five minutes?

Pick a statement with a page break in the middle of the transactions, a few checks and at least one refund. Convert it. Then:

  1. Add up the Amount column, or deposits and withdrawals separately.
  2. Add the result to the printed opening balance.
  3. Compare with the printed closing balance.
  4. If it's off, read the size of the gap against the table above.

A converter that tells you the result of step 3 before you download, and tells you which account is off and by how much when it isn't, is doing the job. One that only shows you a tidy spreadsheet is leaving the job to you.

How ZeraBooks Convert does it

Disclosure: I'm Damin and I run ZeraBooks, so weigh this part accordingly. ZeraBooks Convert, our bank statement converter, runs that proof on every account in every statement: opening plus transactions equals closing for a bank account, and previous balance plus charges minus payments equals the new balance for a card, with each cardholder's charges checked against their printed total. A statement is marked Proven only when every figure matches to the cent. If it doesn't, Convert reads it again automatically, and if it still doesn't tie it shows Needs a look with the account that's off and by how much. You can correct a row or add a missing line before you download Excel or CSV. The details are in how Convert proves a statement. Convert proves each statement on its own, so the check between statements stays with you. It's $79 a month billed annually for 1,000 pages a month, with the General Ledger included, after a 7-day trial.

Run the five-minute test on whatever you use now. If the gap is zero, good. If you didn't know how to find out, that's an answer too.

Comments

Post a Comment

Popular posts from this blog

How to clean up messy books without redoing the same month twice

Image
Clean up books in this order: collect every statement , set opening balances from the last period your accountant signed off, then reconcile each account one month at a time, oldest first . Only once a month ties do you pair transfers, remove duplicates, place uncategorized lines and move owner spending out. A different order means categorizing lines you later delete. A month that doesn't tie, with the gap shown. During a cleanup you stay on that month until the gap is zero. The worked figures below belong to a fictional design studio whose books stopped being kept in February. They are illustrative only. What has to happen before any categorizing? Step 1: Build a statement grid Accounts down the side, months across the top. Tick a cell when you hold that month's statement as a PDF from the bank. Our studio has checking, savings and one card across eight months, so the grid has 24 cells. Any blank cell is a month you can't prove yet, so request it today. Activity...
Read more

How to convert a credit card statement PDF to Excel, step by step

 You need last quarter's card charges in a spreadsheet by Friday, and all you have is three PDFs from the issuer's website. To convert a credit card statement to Excel, download the PDF statement (not the activity export), run it through a converter or copy the tables out by hand, then fix three things: the signs on payments, the foreign currency detail lines, and the summary blocks. Finish by checking that your rows rebuild the printed new balance. Here's each step. Step 1: Download the statement PDF Go to the card's statements page on the issuer's site and download the monthly statement. Many issuers also offer a "download activity" CSV. Skip it for this job. It usually has no previous or new balance printed anywhere, and those two numbers are how you'll prove the spreadsheet is complete at the end. Watch the dates. A card statement ends on a closing date, not the end of the month. If you need the full quarter, you may need the statement after it too...
Read more

How to scan a bank statement so it converts to Excel cleanly

Image
To scan a bank statement so it converts to Excel cleanly, use 300 dpi and grayscale , keep pages straight, save each account's pages into a single PDF , and include the last page even if it looks empty. Then convert it and check that the opening balance plus the rows equals the printed closing balance. Most conversion problems with scanned statements are decided before the conversion starts, at the scanner. Here is how to get it right, step by step. Where a scanned PDF goes in ZeraBooks Convert: the Upload button, or drop the file on the page. Do you need to scan the statement at all? Step 1: Check online banking first Log in and look at the statements section. If the month is there, download the PDF. A downloaded statement carries the bank's own text and will always beat a scan. What scanner settings work best for bank statements? Step 2: Set the scanner Setting Use Why Resolution 300 dpi Lower loses faint digits; higher only makes bigger files Colo...
Read more