Bank statement PDF to Excel: why it breaks

The client sends a bank statement as a PDF. Twelve months, forty pages. You need it in Excel so you can tick it against the cash book, sort it, filter it, and find the lines with no receipt behind them.

So someone copies the PDF and pastes it into a sheet. Or runs it through a generic converter. The result looks fine for the first screen. Then you scroll down and find descriptions split over three rows, amounts sitting in the date column, a page header in the middle of March, and a balance that is off by RM2,340 for reasons nobody can see.

Then the junior spends the afternoon fixing it by hand, and nobody is fully sure it is right. This post covers why bank statements break when they go into Excel, how to clean up the usual faults, and the one check that tells you the sheet is complete.

Why bank statements are hard to convert

A PDF is not a table. It is text and lines placed at positions on a page. What looks like a column to you is just words that happen to line up. Copy-paste and generic converters guess where the columns are, and bank statements give them plenty of ways to guess wrong.

• Descriptions that wrap. A DuitNow or IBG transfer often carries the payee name, a reference and a note, printed over two or three lines. In Excel that becomes two or three rows, and only one of them has an amount.
• Repeated page headers. Every page repeats the bank name, account number, column headings, and sometimes a balance brought forward. Pasted together, they land in the middle of your transactions.
• Amounts that are not numbers. Commas, a “CR” or “DR” after the figure, or a minus sign at the end instead of the front. Excel treats these as text, so they don’t add up and SUM quietly ignores them.
• Dates that flip. Malaysian statements usually print day first. If the PC or the converter expects month first, 03/04 becomes 4 March instead of 3 April, and anything after the 12th stays as text. Some rows end up as real dates, some don’t.
• Long reference numbers. Excel drops leading zeros and turns long numbers into scientific notation, so a reference like 000123456789012 becomes 1.23457E+11.
• Scanned statements. If the client printed the statement and scanned it back, the PDF is a picture. There is no text to copy at all.
• Debit and credit in the wrong column. When a column is empty on one row, the converter can shift the amount left, so a payment lands under deposits.

None of these errors announce themselves. The sheet looks tidy, and it is wrong.

How to clean it up

If you are doing it in Excel yourself, work in this order. Keep the original PDF open alongside.

1. Delete the page furniture first. Filter the date column and remove every row that is a page header, a footer, a “balance b/f” line repeated per page, or a page total. Keep the very first opening balance and the very last closing balance somewhere separate.
2. Join the wrapped descriptions. Any row with text in the description but no date and no amount belongs to the row above. Merge it into that description, then delete it.
3. Make the amounts numbers. Remove commas and any “CR”/“DR” text, move a trailing minus to the front, then convert with VALUE. Check one or two by eye against the PDF.
4. Fix the dates. Use Data > Text to Columns with the date format set to DMY, or rebuild the date with DATE(year, month, day) from the pieces. Then sort by date and look for any row that jumps out of order.
5. Keep reference numbers as text. Format that column as Text before pasting, so leading zeros survive.
6. Use one sign convention. Either separate Debit and Credit columns, or one Amount column with payments negative. Not a mix.

The one check that proves it: opening + movements = closing

However the sheet was made, this is the test. It is the same arithmetic the bank used to print the statement.

Opening balance + total credits − total debits = closing balance.

If it agrees to the sen, no transaction is missing, doubled or sitting in the wrong column. If it doesn’t, something is wrong, and the size of the difference often tells you what.

A made-up example, one month for a client:

	RM
Opening balance, 1 April	12,480.50
Add: total credits (deposits)	31,215.00
Less: total debits (payments)	(29,870.25)
Should equal closing balance	13,825.25
Closing balance printed on the statement	13,825.25

They agree, so the sheet is complete. Now say the closing balance had been RM2,340.00 different. Look for a single line of RM2,340.00 that is missing, or one of RM1,170.00 sitting on the wrong side (a figure in the wrong column moves the total by twice its value).

Two more checks catch what the totals can miss:

• Running balance column. Add a column that takes the previous balance, adds the credit and subtracts the debit. Compare it to the balance the bank printed on each line. The first row where they differ is where the problem starts.
• Line count. Count the transactions on a couple of pages of the PDF and compare with the same date range in Excel. Wrapped descriptions that were never joined show up straight away.

Do the opening-plus-movements check for every month, not just the year. A year that agrees can still hide two errors that cancel out.

What the sheet is for

Converting the statement is rarely the goal. The goal is the reconciliation: matching each line to an entry and a document, and finding the lines with nothing behind them. If you need a refresher on the full process, see the bank reconciliation statement guide.

That second part, the lines with no document, is where most of the time goes. A clean sheet tells you there was a RM86.40 card payment on the 14th. It doesn’t tell you where the receipt is.

Where AccPilot fits

AccPilot is not a general PDF-to-Excel tool, and if all you need is the statement as a spreadsheet, the steps above are the way to do it. What AccPilot does is the job the spreadsheet was for. Upload the client’s bank statement PDF along with their receipts and invoices. AccPilot reads the statement lines, matches each one to a document, and lists the lines with no document as a chase list you can send the client, with dates and amounts (more on bank statement matching). The documents come back Ready or Check with the reason, and the firm exports one clean Excel that imports into your accounting software.

Your staff still review the matches and decide what an unexplained transfer was. The copy-paste, the column fixing and the list-making are what go away. Collecting the receipts monthly makes the matching work better, see how to get client receipts without the monthly chase. Bank matching is in every plan.

To see it on one of your own clients’ statements, book a free demo.