Bank Statement PDF to Excel With AI: Check Every Line Before Import

Convert the PDF to rows, then prove the result before you import it: opening balance plus total credits minus total debits must equal the closing balance printed on the statement, the row count must match the statement, and every sign must agree. If any check fails, do not import. Find the row, fix it against the PDF, and rerun the checks.

Converter pages promise "99% accuracy", which sounds fine until you remember a bank statement has two hundred lines and one wrong digit moves the books. Catch-up bookkeeping, where you inherit twelve months of PDFs and no feed, is exactly where this bites. And a closing balance that agrees is not proof on its own: two errors can cancel each other out, which is why the row count and a row-by-row balance test sit alongside it.

Follow me on Instagram@sagnikteaches

Decide the tool first, because the tool decides the risk

A bank statement is personal and financial data. Where the file goes matters more than how pretty the output is. Options, in order of how much data leaves your control:

Connect on LinkedInSagnik Bhattacharya

1. Excel's own PDF import, locally. Excel's Power Query has a PDF connector; Microsoft's support page gives the steps for Excel on Windows and notes that the connector needs .NET Framework 4.5 or later installed. Microsoft's documentation describes the function behind it, Pdf.Tables, which returns any tables found in a PDF, with options for start page, end page, combining similar tables across consecutive pages and enforcing border lines as cell boundaries. The file stays on your machine.
2. A business-grade AI tool with terms you have read. Upload only what you are allowed to, to a plan whose terms say your content is not used for training, and with the client's agreement.
3. A free online converter or consumer chatbot. Avoid for client statements. The terms are often unclear, the retention unknown and the audit trail nil.

Whichever you pick, write the choice into your process notes and your engagement letter. The tutorial on whether it is safe for an accountant to use ChatGPT with client data covers the reasoning in more depth, and connecting AI tools to a business bank account deals with the live-feed side.

Subscribe on YouTube@sagnikteaches

Step one: record the statement's own numbers before converting

Before you convert anything, read these from the PDF's first and last pages and type them into a small check block on a separate Checks sheet in the workbook, so it never mixes with the transaction rows. They are your yardstick.

Many statements print a summary box with totals; where yours does not, you will derive totals from the converted rows and rely on the balance equation instead. Count transactions by page if there is no stated total: page 1 has 38, page 2 has 40, page 3 has 34. That gives 112, and writing it down before you convert prevents you matching the count to whatever the tool happened to produce.

Step two: convert with a method you can repeat

Using Excel's connector, go to Data, Get Data, From File, From PDF (the path on Microsoft's support page), choose the file and pick the tables in the Navigator. Load the right ones into Power Query for cleaning. Microsoft's documentation notes a few behaviours worth knowing: multi-page tables are combined by default; rows that wrap over several lines may not be identified properly and can need clean-up; and large files can be handled by selecting a page range at a time. Where a description wraps, you can often repair it by filling down or grouping adjacent rows, using steps in the Power Query editor.

If you use an AI tool instead, give it the file and a strict instruction set, and ask for output in a table you can paste:

Convert the attached bank statement PDF into a table with columns:
Date (DD/MM/YYYY), Description, Debit, Credit, Balance.
Rules: one row per transaction; keep descriptions exactly as printed;
put money out in Debit and money in in Credit, always as positive numbers;
do not merge, skip or reorder rows; do not calculate or correct anything.
If a value is unreadable, write UNREADABLE in that cell.
At the end state how many rows you produced.

The "do not correct anything" line matters. Models are tuned to be helpful and will quietly repair what looks like an error, which in a bookkeeping setting is the last thing you want. The instruction to report the row count gives you a first test: if the model says 110 and your page count says 112, you already know.

Step three: the three checks that prove nothing was missed

Check one: the balance equation

In the check block, calculate:

=Opening + SUM(Credit) - SUM(Debit)
=Result - Closing

The second cell must be exactly zero. In the illustrative statement: 12,480.55 plus 31,906.20 minus 29,330.75 is 15,056.00, which equals the closing balance. If the converter had dropped a 450.00 payment, the result would be 15,506.00 and the difference would be 450.00, a number you can search for in the PDF.

Check two: the row count

=COUNTA(A:A)-1

Compare to the count you recorded from the PDF. A count that is too low means missing lines; too high means duplicates or a header row repeated on each page. Remember to exclude repeated header rows that were converted as data.

Check three: the running balance

If the statement has a balance column, test every row: the previous balance, minus debit, plus credit, should equal this row's balance. With the sheet layout shown further down (debit in D, credit in E, printed balance in F), put this in G3 and fill down:

=ROUND(F2-D3+E3-F3,2)

For the first transaction row, use the opening balance from the check block in place of F2. Filter the result column for anything not zero. This is the most powerful check because it points at the exact row where things went wrong, not just the total. A single misread digit produces one non-zero row, and the balances below it still carry on from the printed figure, so the error stays isolated.

What the failures look like, and where they come from

Each failure has a signature. Learn them and you can read a check result like a diagnosis.

The "off by double" signature is worth memorising. If the difference between your calculated closing balance and the printed one is 780.00 and no single transaction is 780.00, but one is 390.00, a 390.00 debit has been read as a credit (or vice versa): the swing is twice the amount.

A worked example: three pages, one dropped line and one sign error

Say a bookkeeper takes on a catch-up job for a small craft brewery supplier and has twelve monthly statements. March is the first. The recorded numbers are in the check block above: opening 12,480.55, credits 31,906.20, debits 29,330.75, closing 15,056.00, 112 transactions.

The converter returns 111 rows. The balance equation on the converted rows gives zero: opening 12,480.55 plus converted credits 31,786.20 minus converted debits 29,210.75 is exactly 15,056.00. A bookkeeper who stops at the balance check would import it. The row count says one row is missing, and the totals do not match the statement's own summary box: credits are 31,786.20 against 31,906.20 and debits are 29,210.75 against 29,330.75, each 120.00 short.

Two offsetting errors explain it. A 240.00 credit at the foot of page 2 was dropped at the page break, and a 120.00 supplier payment on page 3 was read into the Credit column instead of Debit. The first lowers credits by 240.00. The second raises credits by 120.00 and lowers debits by 120.00, which together add 240.00 to the closing calculation. They cancel exactly, so the closing balance looks right.

The running-balance check finds both. Row 77 fails by 240.00 because the previous row's balance does not lead to it, and the row 98 supplier payment fails by 240.00 in the other direction (twice 120.00). Inserting the missing credit and moving the 120.00 into Debit brings the count to 112, the totals to the summary box, and every row to zero.

The lesson is the reason to keep all three checks, plus the totals from the statement summary. Two offsetting errors can make one total look right. Only the row-by-row test, or a comparison with the printed summary, shows that every line agrees.

Dates, descriptions and the quiet errors the checks do not catch

The balance tests prove amounts and completeness. They do not prove dates and descriptions, and those drive categorisation and tax treatment later.

• Dates. Pick the statement's format and convert with an explicit pattern. Then confirm every date falls inside the statement period with a simple filter. A date outside it is a misread.
• Descriptions. Spot-check ten rows against the PDF, including any with long references. Check that continuation lines were joined to the right transaction.
• Duplicates. Genuine duplicates exist (two identical payments on the same day), so do not delete them automatically. Compare against the PDF.
• Currency symbols and thousands separators. A figure such as 1.250,00 in some formats reads as 1,250.00 in others. Check that no amounts became text.

To spot text-formatted numbers quickly, add a column with =ISNUMBER(F2) and filter for FALSE. For more on tidying data that arrives messy, cleaning messy data goes through the common repairs.

Using AI for the part it is good at: investigating failed rows

When a check fails and you cannot see why, give the model the failing row, the rows around it and the relevant page text, and ask it to compare, not to repair.

Here are rows 41 to 46 from my converted statement and the text of page 2 from the PDF.
Row 44 fails the running balance check by 390.00.
Compare the rows with the PDF text. Tell me whether row 44 appears in the PDF text and with what debit or credit.
Do not change any numbers. Quote the PDF line you rely on.

Illustrative output: "In the PDF text, the line '14 Mar SUPPLIER PAYMENT 390.00' appears under the Paid out column. In your row 44 it is under Credit." You then fix the cell yourself. The value of AI here is quick comparison of two blocks of text, which is tedious by eye; the decision and the correction stay with you.

Scanned statements and photographed pages

Everything above assumes the PDF contains real text. Some clients send a scan or a phone photo, which is an image, so a table connector has nothing to read and a converter must first recognise the characters. Recognition errors are more frequent and more random than layout errors: 8 for 3, 1 for 7, a decimal point lost. Two adjustments make the checks more important rather than less.

• Treat every amount as unproven until the running-balance column passes. If the scan has no balance column, ask the client for a proper PDF download from their bank, which usually takes them two minutes.
• Do the digit spot-check on a larger sample: twenty rows, not ten, with extra attention to amounts that end in 0, 3, 5, 6 or 8.

Say a nursery's administrator photographs paper statements at an angle. Three amounts come back with a misplaced decimal, one of them 1,250.00 read as 125.00. The balance equation is out by 1,125.00, a figure that matches no transaction, which is the signature of an amount misread rather than a row lost. The fix is to search the PDF or photo for amounts near that size. Where a statement is this poor, asking for a fresh copy beats any amount of cleaning.

A sheet layout to copy

Keep the same layout for every statement so your formulas never change. One workable layout, with illustrative first rows:

The source page column is the unglamorous hero. When a row fails, you open the PDF at that page rather than scrolling through forty. It also lets a reviewer, or you six months later, trace any figure back to the document. Keep columns D and E as numbers or blanks only; mixing a minus sign and a letter marker in one column is how sign errors enter.

Cleaning descriptions without losing the reference

Bank descriptions are untidy: card numbers, long references, truncated names. Do not shorten them in the evidence sheet; the raw text is your audit trail. Add a separate "clean name" column for categorisation. AI is useful for that column, since it only needs the description text and no amounts or account numbers.

Below are bank statement descriptions, one per line.
For each, give a clean payee name (max 4 words) and nothing else.
If the payee is unclear, write UNCLEAR. Do not invent names. Keep the same order and number of lines.

Illustrative output for "CARD PAYMENT TO ACME TRADE SUPPLIES 0314" is "Acme Trade Supplies", while "TRANSFER 14/03 EXAMPLE PACKAGING LTD 4471" returns "Example Packaging Ltd", and a string like "DD 889221 REF A7" returns UNCLEAR. Count the lines in and out: if the numbers differ, rerun, because a missing line shifts every payee after it. Strip account numbers and personal names from the descriptions you send if they identify private individuals.

What to do when a statement is missing a page or a month

A balance chain break between months usually means a page or a whole statement is missing. Do not bridge it by inventing a line. Ask the client for the missing statement, or use the bank's transaction export for that period if one is available. Where the bank cannot supply it, record the gap, the amount of the break, and the date you asked, and tell the accountant before the accounts are prepared. A documented gap is a normal part of catch-up work. A hidden plug is a problem for everyone.

Keeping the evidence so the work can be reviewed

Save, for each statement: the original PDF, the converted sheet with its check block, and a short note of any fixes made (row, what was wrong, how it was resolved). Name files consistently, such as client code, account, and period. If a reviewer or the client asks how you know March is complete, the answer is one sheet: the recorded printed numbers, the three checks at zero, and the list of fixes. Keep these working papers for as long as your retention policy requires, and delete the files from any temporary location, including downloads folders and AI tool uploads, once the job is done.

Handling a catch-up batch of twelve statements

For a year of statements, structure the work so errors cannot travel between months.

1. Process one statement per sheet, each with its check block.
2. Confirm that each month's opening balance equals the previous month's closing balance. A break between months usually means a missing statement or a page.
3. Combine only after every month shows zero on all three checks.
4. Keep the PDFs and the check blocks as the working-paper evidence.

Run the chain test as a formula: =Closing_Feb - Opening_Mar should be zero for every pair. If you have a kitchen fitter who changed account mid-year, the chain breaks legitimately; note it and move on, with the account change documented.

Time and cost

Rough planning figures, not measurements: about ten minutes per clean statement once the template exists (convert, three checks, spot checks), thirty to forty-five minutes for a statement with failures. A year of twelve statements is typically two to four hours, against a day or more for retyping. The checks cost nothing; the main cost is discipline.

Importing into the accounting software only after the checks pass

Most accounting systems accept a CSV or similar file for bank transactions, with specific column layouts. Check your software's current import guide for required columns and date formats rather than assuming. Save the cleaned sheet as a new file, not over the original, and import one month first as a test. After import, compare the software's bank balance for that date to the statement's closing balance. This is the final tie: the checks proved the sheet, the import check proves the software. Then categorise using AI receipt capture and bank categorisation, and move on to matching with AI bank reconciliation.

If clients can connect a live feed, that replaces most of this work; the open banking consent and feed checklist covers when that makes sense. For anything where you are unsure how the statements, tools and client agreements fit together, my AI implementation consultation can help map it, and the AI guide lists related tutorials.

Questions about converting statements with AI

Is it safe to upload client bank statements to an online converter?

Treat it as a data-protection decision, not a convenience. A free consumer converter may keep or reuse files. Use a tool whose terms you have read, ideally a business plan or software on your own machine, and get the client's agreement. Where unsure, use Excel's built-in PDF import locally.

What does the closing balance check prove?

That opening balance plus all credits minus all debits equals the closing balance printed on the statement. If it holds, no line is missing and no amount was misread by an amount that does not cancel out. It does not prove descriptions or dates are right, so check those separately.

Why do converted statements get signs wrong?

Statements show debits and credits in separate columns, or in one column with minus signs, brackets or letters like DR and CR. A converter may read the wrong column or drop the marker. A sign check compares total debits and credits with the statement summary.

Can AI fix the rows that fail the checks?

It can suggest where a row went wrong if you show it the failing row and the page text, but you should confirm the fix against the PDF. Never let AI change a figure to make the totals agree. A plug that forces a tie hides the exact error you were trying to find.

Further reads

• AI Readiness Checklist for Accountants, Solicitors, Consultants — Check the wider practice is ready for more AI work.
• How Accountants Use AI to Spot Errors in Client Books — Spot errors in the books after the catch-up is done.
• Can AI Do My Bookkeeping? What Still Needs an Accountant — What AI can and cannot do in bookkeeping.
• 7 AI Mistakes That Put an Accounting Firm's Client Trust at Risk — How data-handling slips damage client trust.
• Catch-Up Bookkeeping With AI: Rescue Months of Untouched Books — Plan the whole catch-up job, not just the statements.
• Dext vs AutoEntry for a Small Practice: Costs, Accuracy Claims and How to Check Them — Run a 30-document accuracy test on Dext and AutoEntry, then compare cost per client with cost per credit at your own volumes.
• AI Bookkeeping in Canada: GST/HST Coding and Remittance Checks — A bookkeeper's review routine for GST/HST coding and payroll remittances: formulas for the maths, AI for the judgement calls, CRA pages for the dates.
• AI Tools for Landlords: Messages, Lease Checks and Application Fraud — A small landlord's guide to AI for tenant replies, lease checks and fake application documents, with the legal hard stops and a human decision log.
• AI for Australian Bookkeepers: BAS Prep, GST Checks and Xero — A quarter-end BAS review that pairs Xero's automation with invoice checks, reconciliations and a documented sign-off.
• Best AI for Family Law UK: Form E Prep and Client Updates — A safe route to Form E bank statement summaries and calmer client updates: what to redact, which tools to use, what to check and what the SRA has said.
• Claude for Excel for Accountants: Reconciliations a Manager Can Sign Off — A reviewable Excel reconciliation workflow: copies, source totals, exception notes, formula tests and a manager approval gate.
• Can AI Draft Journal Entries? Accruals and Prepayments, Then Review — Trace an invoice to two adjusting entries, review the schedule and keep posting authority with the accountant.
• Can Claude Read Payslips and Bank Statements for a Mortgage Case? — A broker's document-extraction workflow with source pages, salary-credit tie-outs and exception review.
• AI Tools and AI Development: The Complete 2026 Guide — the AI hub, including every tutorial in the AI-for-business series.

Sources: Microsoft Learn documentation for the Power Query PDF connector (Pdf.Tables options, multi-line rows, large files) and Microsoft Support's Power Query import page (From PDF menu path, .NET requirement), both read 4 November 2026; the bookkeeping method is the author's own.