What I learned building a bank statement PDF to Excel converter

A PDF bank statement looks like a table. It isn't one. There are no rows or columns inside the file, only pieces of text placed at x/y positions on a page. Everything that makes it a "table" is in your head.

I spent the last few months building BankStatementHub, a free tool that turns PDF bank and credit card statements into Excel, CSV and QuickBooks files. Here is what broke along the way, and the one check that caught most of it.

1. Columns are a guess

To rebuild rows you group text by its vertical position, then split it into columns by horizontal position. That works until:

• a description is long enough to run into the amount column
• one transaction's description wraps onto two or three lines
• the page header (bank name, account number, "continued") repeats on every page and lands in the middle of your data
• a "Balance forward" or "Subtotal" line looks exactly like a transaction

Every bank does this a little differently, and the same bank changes its layout every few years.

2. The sign of an amount lives in different places

"Is this money in or money out?" has no single answer:

• separate Withdrawals and Deposits columns (most US checking accounts)
• one Amount column with a minus sign
• a CR or DR suffix, or a trailing minus like 45.00-
• credit card statements, where a positive number is a charge and a payment is negative, the opposite of a checking account

Get this wrong and the totals still look plausible, which is the dangerous part.

3. Numbers and dates are not what you expect

• 1,234.56 in the US, 1.234,56 in much of Europe and Latin America
• amounts with no leading zero, like .15, which a careless regex reads as 15
• dates printed without a year (Dec 28, Jan 03), so a statement that runs from December into January needs the year worked out from the statement period

4. The check that saved me: the balance

The most useful thing I built was not a better parser. It was a check.

A statement gives you an opening balance, a closing balance, and usually a running balance on every row. So after reading the transactions you can test:

opening balance + money in − money out = closing balance

and, row by row:

previous row balance ± this amount = this row balance

If either fails, something was misread, dropped or double counted, and you know which row to look at.

A real example: on a 14-page BBVA México statement, one whole page of transactions was skipped. Every row that was read looked perfect. The only sign of trouble was that the totals didn't add up to the closing balance, and that check flagged it.

Credit card statements usually have no running balance, so there the check falls back to the statement totals (previous balance, payments, purchases, new balance). It's weaker, but it still catches a missing page.

The lesson I'd pass on: don't trust extracted data you can't reconcile. If the numbers can't be checked against something on the page, show the user that clearly instead of handing them a clean-looking spreadsheet.

5. Export formats have their own traps

CSV and Excel are easy. QuickBooks and Quicken files (QBO/OFX) are not:

• each transaction needs a FITID, an ID that should be unique within the account
• QuickBooks silently skips a transaction if it has already seen that FITID, so two genuine $4.50 coffees on the same day can turn into one if the IDs collide
• the account type (checking vs credit card) changes how the signs are read

I ran into the duplicate FITID problem often enough that I made a small free tool just for that: Fix duplicate FITIDs.

What it does now

• PDF to Excel, CSV, QuickBooks (QBO), Xero and Quicken (OFX)
• every row checked against the balance, with mismatches flagged
• bulk conversion of up to 12 statements at once
• password-protected PDFs
• free, no signup

If you've worked with bank statement PDFs, I'd like to hear about the strangest layout you've come across. And if you try BankStatementHub on a statement it gets wrong, tell me in the comments; that's how most of these fixes happened.