How to check a bank statement conversion before using it in Excel

Treat the PDF as the source of truth

A converted spreadsheet is a working copy, not a replacement for the bank-issued statement. PDF layouts are designed for people, while Excel expects one transaction per row. Headers, carried balances and wrapped descriptions can therefore be mistaken for transactions unless the result is checked.

Keep the original PDF beside the review table. Work from the first transaction to the last in source order, and do not sort the spreadsheet until the reconciliation is complete. This makes omissions and duplicated rows much easier to spot.

Review checklist

• Keep the original statement available
• Review in source order
• Delay sorting and filtering until checks are complete

Confirm the statement period and opening position

Start with the account and period shown on the statement. A file name can be misleading, particularly when statements are downloaded in batches. Confirm the first and last dates represented in the extracted rows and note whether the bank displays an opening balance, brought-forward balance or pending transactions separately.

Opening balances are not usually transactions. If one appears in the Excel output, mark it for review before importing the file into accounting software. The same applies to closing balances, available balances and summary totals.

Check debit and credit signs

Banks represent money movement in different ways: separate debit and credit columns, minus signs, trailing minus symbols, parentheses, or a transaction-type marker. The converted amount should use a consistent sign convention, but you should verify at least one known debit and one known credit from every statement layout.

A quick test is to locate a card purchase or bank fee and confirm that it reduces the account, then locate a salary, transfer received or cash deposit and confirm that it increases the account. If both appear with the same sign, stop before importing the file.

Review checklist

• Verify one known debit
• Verify one known credit
• Check refunds and reversals separately

Reconcile row count, totals and balances

Compare the number of visible statement transactions with the number of extracted rows. Then total debits and credits separately where the statement provides those figures. A matching grand total alone is not enough because an omitted debit and omitted credit can cancel each other out.

Where running balances are available, test several points across the period rather than only the closing balance. A mismatch beginning midway through the statement usually points to a missing, duplicated or incorrectly signed row near that point.

Review descriptions before importing

Wrapped descriptions can contain references, beneficiary names and fee details on a second line. Confirm that these lines remain attached to the correct transaction. This matters for matching rules and audit evidence even when the amount is correct.

Only export after the exceptions have been resolved. When creating OFX, test a short date range first because accounting products differ in their duplicate detection and description handling.

Review checklist

• Check multi-line descriptions
• Look for duplicated dates and amounts
• Test a small OFX import before a full period