How to Convert a Bank Statement PDF to Excel (and Prove No Line Is Missing)

To convert a bank statement PDF to Excel you have four options: download the transactions from online banking as CSV, import the PDF with Excel's own PDF connector, copy and paste, or use a converter that reads the statement for you. Whichever you choose, finish with one check that takes ten seconds: opening balance + sum of the transactions = closing balance. If it doesn't hold, a line was lost or misread.

This guide compares the four methods, explains where each one breaks, and gives you the formulas to prove the spreadsheet matches the statement.

The four ways, compared

Why bank statements are harder than they look

A statement looks like a simple table, but almost every bank adds something that breaks a naive conversion:

• Separate debit and credit columns. "Paid out / Paid in", "Soll / Haben", "Dare / Avere", "Débit / Crédit". When only one column is filled on each row, the converter has to know which one — otherwise every payment looks like income.
• Markers instead of signs. German statements often print 146,22 S for a debit and H for a credit, with no minus sign at all.
• Descriptions over two or three lines. The payee on one line, the reference on the next. A line-by-line import creates a "transaction" with no amount.
• Page breaks. "Balance carried forward" and "balance brought forward" rows repeat on every page and must not be counted as transactions.
• Separate sections. Some US statements list deposits in one table and withdrawals in another, with no running balance at all.
• Local number formats. 1.234,56, 1 234,56 and 1,234.56 are the same number.

The dangerous failures are silent: a single row dropped at a page break produces a perfectly tidy spreadsheet that is simply wrong.

The balance check: two formulas

Put the transactions in a table with the amount signed — negative for money out, positive for money in. Say amounts are in column C (rows 2 to 200), the opening balance is in F1 and the closing balance printed on the statement is in F2:

=ROUND(F1 + SUM(C2:C200) - F2, 2)

The result must be 0. Anything else is the amount you are missing — or twice the amount of a transaction whose sign is wrong.

If the statement prints a running balance on each line (column D), you can find the exact row. In E3, and copied down:

=IF(ROUND(D2 + C3 - D3, 2) = 0, "", "check")

The first "check" is where the conversion went wrong. Use D2 = the opening balance for the first row.

Doing it with a converter

Our bank statement to Excel tool does the conversion and the check in one step: upload a PDF or a photo of the statement and you get every transaction with date, description, signed amount and balance. Underneath, it tells you whether opening balance plus transactions equals the closing balance; if not, it tells you the difference and the first row where the running balance stops matching.

We tested it with statements laid out like the ones from British, American, German, Spanish, French and Italian banks — paid-in/paid-out columns, S/H markers, separate deposit and withdrawal sections, two-page statements and a phone photo — and every one balanced to the penny. It is free without an account for statements of up to two pages, three documents a day. Try it with your own statement or with the sample one.

Which method should you use?

• Your own account, this month: export CSV from online banking. It's exact.
• A client's statement that arrives as a PDF: a converter, then the balance check.
• A scanned or photographed statement: a converter with OCR, and never skip the check — that's where OCR makes mistakes.
• Year-end with twelve statements per client: batch them. With a free WhappScan account you convert them all and export one sheet with one row per transaction.

Sources

• Microsoft Learn — Power Query PDF connector
• Microsoft Support — Import data from data sources (Power Query)