Converting bank statements in Excel natively: where Power Query stops

You have a statement document and a copy of Excel, and you would rather not add another tool. Excel for Windows on a Microsoft 365 subscription includes a Power Query connector called From PDF, and it genuinely does import tables out of statement documents. It works on one particular kind of statement: a text based document with a single consistent table layout, no wrapped description lines, no scanned pages, and one signed amount column. Plenty of real statements do not look like that, and the gap between the two is what this page is about.

Before any of it, check whether the bank will hand you the data directly. Look in online banking for a CSV, OFX or QBO download covering the period you need, because a file the bank generated beats anything extracted from a printed page. The reason people end up in Power Query anyway is that the export often is not available for the period that matters: download windows are commonly capped at ninety days or eighteen months while the statement archive runs back years, raw exports frequently omit the running balance so there is nothing to reconcile against, descriptions arrive truncated to a fixed width, and a closed account usually offers no export at all. When the data only exists as statement documents, this is the native procedure, followed by the four layouts that defeat it and the check it never performs.

The native import, in full

Open the Data tab, choose Get Data, then From File, and look for From PDF. Availability has moved between versions and platforms, so trust your own Data menu rather than a list: if the entry is not there, the native route is closed on that machine and the working alternatives are pasting the text in by hand or converting outside Excel.

Then check the document itself. Open the statement in any viewer and try to select a transaction line with the cursor. If the text highlights, Power Query has something to work with. If the cursor draws a box over a picture, the page is an image and the connector will find nothing. The distinction between a scanned statement and a text based statement decides the outcome before you begin.

1. 1

   Start the import

   Data, Get Data, From File, From PDF, then select the statement document.
2. 2

   Select tables, not pages

   In the Navigator, tick Select multiple items and choose the Table object from each page. The Page objects return the whole page as loose text and are not usable.
3. 3

   Open the editor

   Click Transform Data rather than Load, so you can clean the detection before it reaches a sheet.
4. 4

   Append the pages

   Home, Append Queries, Append Queries as New, adding every page table in order to produce one query for the statement.
5. 5

   Remove the repeated headers

   Use First Row as Headers on the combined query, then filter the date column to exclude the header text, which repeats once per page and arrives as data.
6. 6

   Set the column types

   Date column to Date with the bank's locale, amounts and balance to Decimal Number. A currency symbol or thousands separator will keep a column stuck as text.
7. 7

   Load to the sheet

   Close and Load.

Two details sink most attempts. Selecting Page objects instead of Table objects returns unstructured text with no columns. Promoting headers on each page before appending produces mismatched column names, and the append then invents extra columns filled with nulls rather than failing.

The four layouts that defeat it

Power Query performs generic table detection on a page. It has no concept of a bank statement, and every limit below follows from that.

• Scanned and photographed statements. No text layer, no tables, nothing returned by the connector. Power Query has no optical character recognition of its own, so this is not a settings problem. Excel does ship a Data from Picture feature that reads a table out of an image, but it is built around a single captured picture of a small table, not a multi page statement, and it gives you no completeness check either. Anything that arrived as a scan, a fax or a phone photograph is effectively out of scope for the query route.
• Multi page tables with repeating headers. Each page becomes its own detected table. Column headers, the account summary block and the carried forward balance line repeat on every page and land as transaction rows after the append. Filtering them out is ordinary query work, and it is work you write once per bank, because the header wording differs and the filter you built for one institution does not recognise another's.
• Wrapped description lines. A payee that runs past the column width prints on two lines, and Power Query emits two rows: one carrying the date, amount and balance, and an orphan carrying the tail of the description. This is fixable in the query when the layout cooperates: continuation rows can often be identified by a null date and a null amount, then filled down and grouped back onto the row above. It stops being reliable where a genuine transaction is also missing a field, where a wrap runs to three lines, or where the column detection put the tail somewhere else on one page. That is when the repairs become manual.
• Debit and credit column pairs. Statements printing debits and credits in separate columns leave one of the pair blank on every row. Blanks, parentheses for negatives and CR or DR suffixes typically arrive as text. Power Query can parse all of it, and none of it is parsed for you: you write the conditional that turns a debit column, a credit column and a CR suffix into one signed number, and you write it again for the next bank's conventions. Turning amounts that arrive as text, brackets and CR suffixes included, into signed numbers is work Excel leaves entirely to you.

A fifth problem is quieter and worse. Column boundary detection runs per page, so a page where one description happens to run long can be detected with a different column count than its neighbours. The append then produces a table where a handful of rows carry the balance in the amount field. Nothing errors. The sheet simply contains a few wrong rows in the middle of several hundred correct ones, and you will find them at reconciliation or not at all.

The check Power Query never performs

This is the part that matters more than any layout quirk. Power Query reports success whether it captured every transaction or two thirds of them. It has no idea what a complete bank statement looks like, so it cannot tell you when it fell short.

The check the document itself supports is arithmetic: opening balance, plus every credit, minus every debit, against the printed closing balance. Be exact about what that proves. It is not a certificate that the file is right, because it says nothing about whether the dates parsed correctly or a description lost half its text, and two errors that cancel each other out will pass it. What it catches is the error class that costs the most here, a wrong or missing amount, because a wrong amount changes a total and a changed total breaks the chain. When it does break, the difference is frequently the exact value of the row responsible, which turns a search into a lookup.

Excel will not run that test for you. You build it yourself, every time, for every statement, and you compare row counts against the transaction count on the page as well, because a page skipped in the Navigator produces a smaller table with no warning at all. You can automate the comparison in the workbook, and it is worth doing. What you cannot automate away is remembering to look at it before the numbers go somewhere that matters.

The full method for verifying a converted bank statement applies whatever produced the file. The difference is whether the verdict comes with the output or is homework.

Where the native route actually lands

For one clean statement from one bank, converted once, Power Query is a reasonable fifteen minutes and the file never leaves your machine. That case is real and worth knowing about.

The economics turn immediately after it. A query can be written defensively and parameterised, and a well built one tolerates some variation. What it cannot do is generalise past the shape it was built against: column positions, header wording, date formats and sign conventions differ per institution, so a second bank means real query work rather than a new file path, and a bank redesigning its statement breaks the query you spent an afternoon on, quietly, in the middle of the rows. Every scanned statement is a stop with no path forward inside the query. Pasting and splitting instead is no escape either: Text to Columns on a statement fails on variable width description text for precisely the same reason.

Twelve statements from three banks, two of them scans, is not fifteen minutes. It is query work per layout, manual repair on the rows that did not cooperate, and a reconciliation you run yourself at the end, with the result depending on whether you remembered to run it. The native route is not wrong. It is narrow, and the statements that cost you time are the ones that sit outside it.