The AI For Series

Month-End Bank Reconciliation with AI: A Step-by-Step Method with Tie-Outs

By Chandan Gupta, CA, CIA · 2026-11-14

Every month someone matches the bank statement against the cash account in the GL. The lookups were never the hard part. The exceptions are: a check the bank paid at a different amount than you booked, a customer payment entered twice, a wire out that was posted as a receipt, a deposit the bank split into two lines.

AI can do the mechanical matching. Two questions come first: what leaves my laptop, and how do I know the result is right? This guide answers both.

The rule: let the AI write the script, not read the data

If you paste a bank statement into a chat window, every line goes to the provider's servers.

There is a better pattern. Claude Code, Anthropic's AI tool that works on files on your own computer (it needs a paid Claude plan), can write a short Python script, run it on your machine, and show you only the result. The AI sees the headers, a few sample rows and the totals the script prints. The full statement and GL detail are read by the script, locally.

This reduces what is sent; it does not make it zero. Sample rows are real data, an exception list names payees, and if Claude Code opens a file to debug something, that content goes too. So work in a clean job folder that holds only the files this reconciliation needs, and read each permission prompt before you approve it.

What you need

A job folder with three files (the bank's activity export, the cash account's GL detail from your ERP, and last month's outstanding items), Claude Code with Python, pandas and openpyxl, and Excel to check the output. The file names below are examples; name yours in the prompt.

Step 1: Look at the structure, not the data

Start with the shape of the files and nothing else:

This folder has three files for the June bank reconciliation of our operating account: bank_statement_jun.csv (the bank's activity export), GL_1010_detail_jun.xlsx (GL detail for cash account 1010 from our ERP) and Outstanding_Items_May.xlsx (last month's outstanding checks and deposit in transit). Do not read the files in full. Write and run a short Python script that prints, for each file: the sheet names, the number of rows, any title or preamble lines, the header row and the first 3 data rows. Then tell me in a few lines how the files line up, and stop. Do not start the reconciliation.

This step catches what breaks a rec before matching starts: preamble lines above the header, amounts stored as text with a trailing minus, a GL export with opening-balance and total rows that are not transactions.

Then check one more thing: does the bank's opening balance, adjusted for last month's outstanding items, equal the GL opening balance? If not, stop and find out why. A reconciliation built on a broken opening position never ties for the right reasons.

Step 2: Write the rules down, as you would for a staff accountant

The quality of the result depends on the rules you give. These are the ones that close the common gaps:

Now write bank_rec.py, a Python script that runs on this computer, and run it. Rules: 1. Bank file: skip the preamble lines. Make deposits positive and withdrawals negative. Prove that the beginning balance plus all activity equals the ending balance in the preamble. 2. GL file: skip the title rows, the Beginning Balance row and the Total row. Amount = Debit minus Credit. Prove that the beginning balance plus activity equals the GL ending balance. 3. Clear last month's outstanding items first: checks on check number and amount, the deposit on amount within 7 days. List every May item as cleared or still outstanding. 4. Then match June in rounds and record the round in a column called Match basis: (a) checks on check number (digits only, ignore prefixes and leading zeros) and amount; (b) other items on the same signed amount with dates within 5 days, closest date first; (c) one GL deposit to two or three bank deposits on the same or next day that add up to it exactly. Each line can be used once. 5. Do not force a match. Same check number with a different amount, or the same amount with the opposite sign within 5 days, goes to Exceptions with the reason. A GL line with the same name and amount as an already matched GL line within 7 days, where the bank shows only one, goes to Exceptions as a possible duplicate. 6. Write bank_rec_jun.xlsx with sheets: Summary, Matched, Bank Only, Book Only, Exceptions, Prior Month. The Summary is a standard two-sided reconciliation: balance per bank, plus deposits in transit, less outstanding checks, adjusted bank balance; balance per books, plus or minus bank items not yet booked, plus or minus the corrections from Exceptions, adjusted book balance; difference. Every line except the two starting balances must be a formula that points at the detail sheets. 7. Do not book anything and do not decide the accounting. In Bank Only, add a column with a suggested treatment for me to review. Then show me only the Summary figures and the row count of each sheet.

Rule 3 stops last month's checks showing up as bank-only items. Rule 4(c) handles a deposit the bank posted as two lines. Rule 5 matters most. A matcher that ignores the sign will pair a $7,500 wire out with a $7,500 "receipt" in the books and call it matched, hiding a $15,000 error. Rule 6 makes the Summary traceable, and rule 7 keeps the accounting with you.

Step 3: Pull out the items you will act on

From bank_rec_jun.xlsx, list every line in Bank Only, Book Only and Exceptions, and the May items still outstanding: date, description or payee, amount, and the reason. For the items that need a journal entry, draft the entry (accounts and amounts) as a suggestion for me to review. Use a script; do not change the workbook.

A useful test: if you posted all the draft entries, would the GL land exactly on the adjusted book balance? Ask it to show that. Then read each as a draft; a returned customer check may be better handled in your ERP by reversing the payment. Those are your calls.

How to check the result

A balanced Summary is a claim, not proof. Open the workbook in Excel and check these yourself:

A script-written workbook has no calculated values until Excel opens it, so read the Summary in Excel.

What goes in the rec support

The input files as downloaded, bank_rec.py beside the output workbook, a short note on the rules, and each exception with its resolution, approver and date. Next month the same script runs the same rules.

Limits

The script matches and flags; it does not decide. Following up a stale check, correcting a misposted wire and booking an NSF check are judgments for you and your reviewer. A clean run last month says nothing about this month if the bank changes its export layout, so run the structure check on every new file. Results vary between runs and versions, so the checks matter more than any single good run.

Not affiliated with or endorsed by Anthropic or Microsoft.

From the book

This guide is a short version of one method in Excel + AI for Finance Teams. Chapter 15 of Excel + AI for Finance Teams walks through it end to end on practice files with an answer key.

More free guides