Spreadsheets

Scanned statement PDF to Excel with OCR: what to check afterwards

Scanned bank statement PDF to Excel with OCR: what the software gets wrong, three checks that catch it and a balance reconciliation at the end.

Editorial illustration for Scanned statement PDF to Excel with OCR: what to check afterwards

OCR turns a picture of a statement into text, and it often misreads similar-looking characters and small marks. After converting a scanned or photographed statement, run three checks: that every value is a number, that each running balance follows from the one before, and that the totals match the statement. Do not trust unchecked OCR output.

What does OCR typically get wrong?

PrintedOCR may read it asEffect
0 (zero)O (letter)The amount becomes text and will not add
1 (one)l or IThe amount becomes text, or a digit is lost
5SA wrong amount or text
8BA wrong digit
Decimal pointDropped or read as a comma450.00 becomes 45000
Minus or Dr/Cr marksMissedA debit looks like a credit

The dropped decimal point is the most dangerous, because ₹450.00 turning into ₹45,000 still looks like a perfectly good number.

How do you prepare the scan so OCR does better?

  • Photograph or scan the page flat, straight on, in even light, with no shadow across the figures.
  • Use the highest sensible resolution and avoid creases or folded corners.
  • One page per image works better than a long collage.
  • Prefer the bank's own PDF or CSV where you can, because a scan is the last resort.

Because a statement holds your name and spending, be cautious about which OCR tool you use. Prefer software on your own device or one whose privacy terms you have read, and do not upload to random free converters.

Which three checks catch the errors?

  1. Is every amount a number? Select the amount and balance columns and look at the status bar. If it shows Count but not Sum, some cells are text. Use =ISNUMBER(D2) down the column and filter for FALSE.
  2. Does the running balance follow? With debits in C, credits in D and balances in E, enter =ROUND(E2-C3+D3-E3,2) in F3 and fill down. Every row should return 0.
  3. Do the totals match? Compare the sum of debits, the sum of credits and the row count with the statement's own summary.

The second check is the strongest because it tests each row against the previous one, so a single misread digit shows up at exactly the row where it happened.

What does a failed check look like?

Say a balance of 41,550.00 is followed by a debit of 1,200.00, so the next balance should be 40,350.00. OCR reads the new balance as 40,850.00. Your check returns 41,550 - 1,200 - 40,850 = -500, which is not 0. The row is flagged. You open the scan at that line, see the real balance is 40,350.00, and correct the 8 that was really a 3. Without the check, every later balance would look fine on its own.

Dates need a check as well. OCR can turn a 3 into an 8 and push a date out of order. If your dates are real dates and the statement runs oldest to newest, =IF(A3<A2,"check","") in a spare column flags any date earlier than the one above it.

If the scan came from a phone camera, retake a blurry page instead of editing the output. A sharp second photo costs a minute, while hunting for one blurred digit across a sheet costs much longer. Keep each page's image beside its rows so you can compare them side by side.

Finish with a reconciliation

The last step is the closing balance. Opening balance plus total credits minus total debits must equal the closing balance printed on the statement. If it does, the extraction is sound. If it does not, the difference is a clue: a small, round difference hints at a single misread digit, while a difference equal to one transaction suggests a row was missed or counted twice.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports