Your Converted Bank Statement Doesn't Tie: How to Find What the PDF Import Lost

How-to · Updated July 2026 · ~16 min read

The converted file looks right. 412 rows, four clean columns, the dates all in order. Then you take the opening balance, add the movements, and you are out by 1,240.50 against the closing balance the bank printed on the last page.

The file that ties is worse. A page header imported as a transaction and a "balance brought forward" line counted as a movement can cancel each other to the penny. A day-first statement read as month-first only mangles the dates where the day is 12 or lower, so most of the column is untouched and the damage hides among the rows that are fine. A PDF import does not fail loudly. It fails plausibly, and every reconciliation, VAT return and set of accounts built on top of it inherits the error in silence.

So this page is the control, not the converter. First the two arithmetic proofs, which need no product and no add-in and take about a minute. Then the five specific ways a statement extraction goes wrong, and exactly what each one looks like in those proofs. Then — second half, for when you still have to do the extraction yourself — Excel's own PDF connector and the cleaning steps, kept local, because that file is a client's account history and not yours to upload. The matching side is in reconciling a bank statement in Excel with AI; the worksheet is the bank reconciliation template for Excel.

The two-minute proof: run this before you clean anything

Most guides stop at "your data is in Excel". That is where the risk starts: a mis-extracted statement looks entirely normal. No product feature required, just arithmetic.

Run it on the raw extraction, before you touch anything. Cleaning is filtering, filling down and grouping — every one of those steps can hide the thing the check is looking for, and one or two can create it. The proof is cheap enough to run twice: once on what came out of the PDF, once on the table you finished with.

Lay the sheet out so it does the bookkeeping: row 2 is the opening-balance row, carrying a balance in F2 but no amount in E2, with transactions from row 3. Signed amounts in E, the printed balance in F, the statement's closing balance in H2. Lock the header row with freeze panes first, so the columns stay labelled while you scroll a few hundred lines.

Whole statement. Opening balance, plus money in, minus money out, equals the printed closing balance. Starting the sum at E3 is what keeps the opening balance from being counted twice:

=IF(ROUND($F$2+SUM($E$3:$E$501)-$H$2,2)=0,"EXTRACTION OK","OUT BY "&TEXT(ROUND($F$2+SUM($E$3:$E$501)-$H$2,2),"#,##0.00"))

Row by row tells you where it broke: each row's printed balance equals the previous row's balance plus that row's signed amount. In G3, filled down — the F3="" guard stops empty rows below your data reporting a false failure:

=IF(F3="","",IF(ROUND(F3-F2-E3,2)=0,"ok","CHECK"))

Then =COUNTIF($G$3:$G$501,"CHECK"). Zero means every row agrees with the balance the bank printed beside it. Non-zero, and the first CHECK is where extraction went wrong — usually a page break or a wrapped description.

Three caveats, or the check fails on good data:

• Exclude the opening-balance row from the sums. It is usually a row inside the table, not a header, and counting it as both the opening balance and a movement guarantees a false failure.
• Normalise signs first. Separate debit/credit columns, parentheses and trailing minus all mean negative, and text that merely looks negative contributes 0 to SUM.
• Fix misalignment, never plug it. A hardcoded adjustment buries what the check exists to find.

Zoho Books puts the same standard at the other end: "To reconcile your accounts, you need to make sure that the Closing Balance and Cleared Amount are the same and the difference is zero."

The five ways a PDF import lies to you

The two checks tell you that something is wrong and roughly where. These are the five things it usually is. Each one has a shape on the sheet, a check that catches it and a fix — and not one of them announces itself.

1. A brought-forward balance counted as a movement

What it looks like. A row at the top of each page reading Balance brought forward or Balance b/f with one figure on it. The columns shift on that row, because there is no description and no amount, so the balance drops into the money column instead of the balance column and becomes a transaction.

Which check catches it. The whole-statement one, and it hands you the diagnosis: the variance equals a balance printed somewhere on the statement rather than any transaction on it. Look for the figure the previous page closed with.

The fix. Filter the row out of the table entirely, and keep exactly one opening-balance row — the real one, at the top — outside the sums. If a brought-forward line is the only thing carrying a page's starting balance, keep it in a column of its own rather than in the amount column.

2. A repeated page header imported as a transaction

What it looks like. Rows that repeat every forty or fifty lines carrying the bank's name, the account number, or the words Date, Description, Money out, Money in, Balance sitting in the data as though they were a payment.

Which check catches it. Row by row. The balance cell on a header row is text or empty, so the arithmetic against the row above it fails and you get a CHECK exactly where the page turned. The count of extracted rows will also exceed the transaction count the statement prints.

The fix. Cleaning step 1 below: filter arrow on the date column, untick everything that is not a date. One step clears every page at once.

Why it is dangerous. This is the failure most likely to cancel another one. A header row that drags a number into the money column, against a brought-forward line doing the same thing in the other direction, can leave the totals tying perfectly on a file with two errors in it.

3. A wrapped description with its amount on a continuation row

What it looks like. One transaction spread over two or three rows: the description continues underneath, and the date, amount and balance sit only on the first. Sometimes worse — the reference number and the amount land on different rows from each other.

Which check catches it. Both. The row-by-row check fails at the transaction, and the whole-statement total is out by exactly that transaction's amount if the figure landed somewhere the SUM cannot see it.

The fix. Cleaning step 5 below, Fill Down then Group By. Do not delete the blank-amount rows before you have read them: a reference number stranded on a continuation line is often the only thing that will ever tie that payment to an invoice.

4. Negatives sitting in the cell as text

What it looks like. Money left-aligned instead of right-aligned — (1,240.50) or 1,240.50- — which is Excel telling you it is holding text, not a number.

Which check catches it. The whole-statement one, and the size of the gap says which version you have. Text contributes 0 to SUM, so an untouched text negative leaves you out by exactly that amount. A repair that strips the brackets and leaves the number positive is worse: the movement is now counted the wrong way round, and the check is out by twice it. A variance that is exactly double a transaction on the statement is a sign problem, not a missing row.

The fix. Home > Replace Values then Fixed decimal number in the query, or the repair formula in cleaning step 4 below, which handles all three sign conventions and flags what it cannot parse instead of guessing.

5. A day-first statement read as month-first

What it looks like. Nothing at all. Every value in the column is a valid date, formatted like a date, in a column Excel is happy with.

Which check catches it. Neither of them, and this is the one to be frightened of. Both proofs work down the rows in the order they were extracted; the arithmetic between a row and the one above it is unaffected by how the date was parsed. A statement can pass both checks with a third of its dates silently moved to a different month.

How to catch it instead. Sort a copy by the date column: on a statement the dates run in order, so an out-of-sequence block is the locale error showing itself. Then take the earliest and latest dates and compare them with the statement period printed on page 1. Dates outside the period are proof; dates inside it are not proof of anything, which is why the sort matters.

The fix. Cleaning step 3 below, Change Type > Using Locale, and never automatic detection. The cost of missing it is a payment sitting in the wrong month, which moves a cut-off, a VAT quarter and an accrual all at once.

When the totals tie and the file is still wrong

A clean tie is evidence, not proof. Two opposite errors cancel, and the pair does not have to be exotic: the page header and the brought-forward line from failure modes 1 and 2 are a matched set, and a row dropped against a row doubled will do the same thing. The arithmetic only proves that what is in the sheet is internally consistent with the balance column that came out of the same PDF.

So add three counts that a cancelling pair cannot survive, because they test the file against the statement rather than against itself.

Count the rows. Most statements print the number of transactions, or at least a page count you can multiply out. Compare it with what you extracted:

=COUNT($B$3:$B$501)

Split the movements. Debits and credits netting to the right figure is a much weaker claim than each side being right on its own. Where the statement prints a summary block of total money in and total money out, reproduce both:

=SUMIF($E$3:$E$501,"<0")
=SUMIF($E$3:$E$501,">0")

Bracket the period. A whole page that never made it into the extraction usually takes a contiguous block of dates with it, and the range gives that away at a glance:

=TEXT(MIN($B$3:$B$501),"dd/mm/yyyy")&" to "&TEXT(MAX($B$3:$B$501),"dd/mm/yyyy")

Three figures, thirty seconds, and between them they catch the errors the balance column cannot. If the statement prints no summary block of its own, the counts are still worth having month to month: a file with 18 per cent fewer rows than every previous month is telling you something before anybody reads a single line of it.

Then keep them. The proof cells belong in the finished workbook next to the data, not in a scratch calculation you delete once it reads zero — and the source PDF belongs filed beside the workbook. The check is the workpaper. Six months later, when someone asks how you knew the extraction was complete, the answer needs to be a cell they can look at rather than a recollection that you did check at the time.

Why a statement PDF does this to you

A PDF describes where marks go on a page, not what they mean. There is no row, column or cell, so a table has to be inferred from coordinates — and two things break the inference.

A PDF is either text or a picture of text. A bank-generated statement carries a text layer: real characters at real positions. A printed-and-scanned statement is pixels only, and every tool that reads a text layer gets nothing. To tell which you have, drag-select across a transaction line. Characters highlight: there is a text layer. A blue rectangle drags with nothing highlighting: it is an image, so skip to scanned statements.

Columns are not really columns. Your eye reads Date, Description, Money out, Money in, Balance because the numbers line up, but the file often has no vertical rules and no table metadata — the alignment is whitespace. Extractors guess the boundaries from gaps, and guess wrong in two places:

• Multi-line rows. "CARD PAYMENT TO WWW.SUPPLIER-PORTAL / REF 88214" wraps onto three lines, but the date, amount and balance sit only on the first.
• Page breaks. Every page repeats a header block, and often a "Balance brought forward" line, at the same coordinates as your data.

If you still have to do the extraction: Excel's own PDF connector

Excel for Windows includes a PDF connector in Power Query: no extra cost, and nothing leaves your machine.

1. Select Data > Get Data > From File > From PDF — the Get & Transform group on the Data tab.
2. Select your PDF file, and then click Open.
3. The Navigator dialog lists each table the connector detected and each page it found; click through the previews to find your transactions.
4. Choose Transform Data, not Load — a statement always needs cleaning, and the editor makes it repeatable next month.

Two constraints. Microsoft's Excel documentation states that "the PDF connector requires .NET Framework 4.5 or higher" — already present on any current Windows machine. And it is not available in Excel for Mac, whose source list is only Excel Workbook, Text/CSV, XML, JSON, SQL Server Database, SharePoint Online List, OData, Blank Table and Blank Query. If From PDF is missing from your Windows Excel, that build does not include it.

What it does well, and where it fails

Microsoft documents the problem directly: "In cases where multi-line rows aren't properly identified, you might be able to clean up the data using UI operations or custom M code. For example, you could copy misaligned data to adjacent rows using Table.FillDown, or group and combine adjacent rows using Table.Group."

Slow extractions are tuned in the underlying Pdf.Tables step, in the formula bar or Advanced Editor. For a heavy file, Microsoft's advice is to "try selecting pages one at a time or one small range at a time using the StartPage or EndPage options."

Cleaning the extracted table

The statement-specific subset of cleaning messy data in Excel; if the editor is new, start with Power Query for beginners.

1. Promote headers, delete the page furniture

Use Home > Use First Row as Headers if the real titles came through as row 1. Then click the filter arrow on the date column and uncheck everything that is not a date — Date, Page, Balance brought forward. That one step clears every page. Keep the opening-balance line, but note which row it is: it must be excluded from your totals.

This step is what deals with failure modes 1 and 2: the repeated page header leaves the table, the brought-forward lines go with it, and the one opening-balance row you keep is now identified so the sums can skip it.

2. Split a column holding several fields

Select the column — it must be a Text data type — then Home > Split Column > By Delimiter. Pick Space in the Select or enter a delimiter drop-down, and under Split at choose Left-most delimiter, Right-most delimiter or Each occurrence of the delimiter.

The statement trick is that the date is a fixed token at the front and the balance a fixed token at the back. Split by Space / Left-most delimiter to peel off the date, then split the remainder by Space / Right-most delimiter to peel off the balance; whatever survives is the description. New columns take the original name plus a period and a number — Description.1, Description.2. The reverse job, merging pieces back into one field, is combining two columns in Excel.

Split this badly and an amount ends up inside the description text, where no SUM will ever see it. The row-by-row check finds that on the first pass.

3. Repair the dates with a locale

Do not trust automatic detection. 03/04/2026 is 3 April in the UK and UAE and 4 March in a US-locale workbook, and both parse without error — silent and plausible, the worst kind of bug.

Right-click the column header, select Change Type > Using Locale, and in the Change Type with Locale dialog box pick Date plus the bank's locale. Microsoft is explicit that "this locale overrides the Power Query locale setting".

This is the only place failure mode 5 can be fixed, and the only one of the five that neither arithmetic check will find for you.

4. Type the money columns

Select the column, then Home > Data Type and pick Fixed decimal number for money — four digits to the right, 19 to the left — which avoids the floating-point dust that leaves a rec 0.0000001 short.

A value that refuses to convert is not a number yet. Parenthesised and trailing-minus negatives normally arrive as text; strip the characters with Home > Replace Values first. To repair them on the sheet instead, this handles all three sign conventions, ignores blanks, and flags what it cannot parse:

=IF(TRIM(A2)="","",IFERROR(IF(OR(LEFT(TRIM(A2),1)="(",LEFT(TRIM(A2),1)="-",RIGHT(TRIM(A2),1)="-"),-1,1)*VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(A2),"(",""),")",""),"-",""),",","")),"CHECK"))

That is failure mode 4. The CHECK is the point of the formula: a value it cannot parse is reported rather than quietly turned into a zero.

5. Collapse the multi-line rows

Where a description wrapped, the numeric columns are blank on the continuation rows. Select the date column and use Transform > Fill > Down so those rows inherit their parent's date, then Home > Group By on the date plus a stable key, with Sum on the amounts and All Rows as a second aggregation. Group By has no text-join operation, so rebuild the wrapped description in a custom column off that grouped table — if the All Rows column is Details, that is Text.Combine([Details][Description], " "). Do not delete blank-amount rows before reading them: a reference number on a continuation line is often the only thing that will match the row to an invoice.

Failure mode 3 is fixed here, and re-running the row-by-row check afterwards is what confirms it.

6. Two columns, or one signed amount

Most statements print Money out and Money in separately; reconciliation is easier against one signed amount. With Money out in C and Money in in D:

=IFERROR(VALUE(D2),0)-IFERROR(VALUE(C2),0)

Positive is money in, and blanks resolve to 0. Note the trade IFERROR makes: text becomes 0 instead of an error — which is exactly why the balance check below is not optional. Keep the original pair of columns for review.

Get the sign convention backwards here and the whole-statement check comes out at twice the transaction rather than at zero — which is the useful thing about a check that knows what the bank printed.

Scanned statements: what actually works

There is no OCR in Excel's PDF connector. Its only documented capability is Import, and Pdf.Tables "returns any tables found in pdf" — a scan has no text objects, so there is nothing to find and Navigator offers nothing usable. Microsoft's OCR ships elsewhere: Azure Document Intelligence, and SharePoint's OCR service, whose supported file types include ".pdf (scanned and hybrid)".

Excel's own image-to-table feature is Data > From Picture > Picture From File or Picture From Clipboard (Windows 10 version 1903 or later, Windows 11, macOS and Excel for the web; Windows needs the Edge WebView2 Runtime). Its documented inputs are an image file, a screen clipping or a camera scan — not a PDF. So for a scan, export each page as an image first, or use a real OCR tool — then run the balance validation, because a misread digit is still a plausible number.

Better still, check whether your online banking offers CSV or OFX for the same period. That skips extraction entirely.

Two things worth knowing before you trust an OCR'd statement in a client file. An OCR misread that lands the same wrong digit in an amount and in the balance beside it leaves the arithmetic between the rows perfectly consistent, so the row-by-row check passes on a figure that is simply not what the bank printed — on anything that came from a scan, re-key a sample of rows against the PDF by eye rather than relying on the check alone. And a scan of a statement is the weakest form of that evidence you could be holding: before spending an hour on it, ask the client for the bank-generated PDF, CSV or OFX, which usually still exists in their online banking and takes them two minutes to download.

Before you upload a client's statement to a free converter

A statement holds the account number or IBAN, the holder's name and address, and a record of who they pay and when. If you are an accountant that file is not yours, and uploading it puts client data on third-party infrastructure under retention terms nobody reads.

In a practice, three things follow from that. The confidentiality is owed by you: the client handed the file to you, not to a website you found while the kettle boiled. The retention terms on a free converter are rarely read and sometimes indefinite, and deleting the output from your browser does not delete the upload from their storage. And whatever those terms say, they are not in your engagement letter, which is the document your client actually agreed to — so if a tool has to see the file, it should be one the firm has assessed, with a written answer to where the data goes and how long it stays there. Otherwise keep extraction local: the Power Query route above runs on your own machine and sends nothing anywhere.

Doing it inside Excel with HISAB 360

Convert statements monthly for several clients and the Power Query route becomes a set of queries you maintain by hand. HISAB 360 is one option — a paid Excel add-in with an AI panel docked in the sheet. Its document extraction pulls tables out of PDFs, bank statements and invoices included, into a structured range; you can then ask in plain English for the merged column split, the dates re-typed for the bank's locale or the signed-amount column, and it writes and applies the Power Query or formulas to the live sheet.

It earns its place after extraction: the AI reconciliation matches statement lines to ledger rows on amount and date tolerance, handles one deposit covering several invoices, matches descriptions that do not agree word-for-word, flags duplicates and produces a report. Ledgers in QuickBooks Online, Xero, Zoho Books, Odoo or FreshBooks read into the sheet, and post back, over an OAuth connection you authorise.

Honest limits: Windows desktop Excel only, no Mac and no Excel for the web. It is paid after a 15-day free trial (every feature plus 50 HISAB AI credits, no credit card, once per machine); there is no permanently free tier, and pricing starts at $7/month for bring-your-own-AI-key. Every suggested match, like every ledger write, should be reviewed before you approve it.

And the proof at the top of this page applies to whatever produced the file — a converter, a connector, this add-in included. Anything that infers a table from a page can infer it wrong, and none of them can tell you they did. The arithmetic can.

Then categorise and reconcile

1. Categorise from the description with a keyword lookup table, not 400 typed labels. Where descriptions vary month to month, fuzzy matching in Excel groups "AMZN MKTPLACE" and "AMAZON.CO.UK". Keep the category names themselves consistent by picking them from a data-validation drop-down list rather than typing free text.
2. Reconcile in the free bank reconciliation template: a bank side, a book side, a DIFFERENCE row printing RECONCILED or OUT BY <amount>, and a Matched? flag on each transaction sheet built from COUNTIFS against the other side for 200 rows. Its stated limit: amount-only matching cannot handle one deposit covering several invoices. Cross the cleared lines off as you go with strikethrough in Excel and what remains unstruck is the worklist.
3. Slot it into the close — see building a month-end close checklist in Excel.

Never reconcile against an extraction you have not proved. Opening balance plus movements equals closing balance — and if it does not, the problem is your data, not your bank.

Frequently asked questions

Try HISAB 360 on your own workbook

HISAB 360 is an AI assistant inside Excel for accountants and finance teams — it writes macros, Power Query and formulas from plain English, and connects two-way to QuickBooks, Xero, Zoho Books, Odoo and FreshBooks. The 15-day trial is the full product, no card required.