Automated Bank Reconciliation in Excel Using Claude AI
Progress1 of 4
1
The finished reconciliation
2
Prepare your data
3
Prompt, FAQ & related
4
Quick quiz
Section 01
What the finished bank reconciliation looks like
Automated Bank Reconciliation in Excel Using Claude AI
This course gives you a ready-to-use Claude prompt that turns two raw data exports — your bank statement and your general ledger — into a completed reconciliation in Excel. You'll get matched transactions, flagged discrepancies, and a clear summary of the outstanding balance. The whole process takes under five minutes once your data is prepared.
What Your Finished Bank Reconciliation Looks Like
Below is the output Claude produces. Each row from the bank statement is matched against the ledger by date, amount, and reference. Unmatched and partially matched items are flagged automatically — no items are forced into a match.
A
B
C
D
E
F
1
Date
Bank Ref
Bank Amt
Ledger Amt
Difference
Status
2
01/06/2025
TXN-4401
-12,500.00
-12,500.00
0.00
Matched
3
03/06/2025
TXN-4402
-3,200.00
-3,200.00
0.00
Matched
4
05/06/2025
TXN-4410
8,750.00
8,700.00
50.00
Partial
5
07/06/2025
TXN-4415
-1,980.00
—
-1,980.00
Unmatched
6
10/06/2025
TXN-4418
25,000.00
25,000.00
0.00
Matched
Claude flags partial matches (amount differs) and unmatched items (no ledger entry found) so you can investigate each one individually.
Matches bank transactions to ledger entries by date, amount, and reference number
Flags partial matches where amounts differ — no silent rounding or forcing
Lists all unmatched items on both sides (bank-only and ledger-only)
Produces a reconciliation summary with opening balance, adjustments, and closing balance
Where this fits: Bank reconciliation is one of the core tasks covered in our AI for Excel course hub. If your reconciliation feeds into a broader month-end process, see the month-end close checklist course next.
Prepare Your Bank and Ledger Data for Reconciliation
Claude needs two clearly separated datasets. Export your bank statement and general ledger cash account into Excel or CSV, then format each one with consistent column headers. Here is the minimum required layout:
Bank Statement Layout
A
B
C
D
1
Date
Reference
Description
Amount
2
01/06/2025
TXN-4401
Acme Ltd – inv 1022
-12,500.00
3
03/06/2025
TXN-4402
Globex Corp – Q2
-3,200.00
4
05/06/2025
TXN-4410
Client deposit – Initech
8,750.00
General Ledger (Cash Account) Layout
A
B
C
D
1
Date
GL Ref
Description
Amount
2
01/06/2025
JE-0871
Acme Ltd payment
-12,500.00
3
03/06/2025
JE-0872
Globex Corp payment
-3,200.00
4
05/06/2025
JE-0875
Initech deposit
8,700.00
01
Use consistent date formats
Both datasets must use the same date format — DD/MM/YYYY or MM/DD/YYYY. Mixing formats causes false mismatches. Specify your format in the prompt.
02
Keep amounts as plain numbers
Remove currency symbols and thousands separators if they're inconsistent between bank and ledger. Negative for outflows, positive for inflows — same sign convention on both sides.
03
Include a reference column on each side
Bank reference (e.g. TXN-4401) and GL reference (e.g. JE-0871) don't need to match each other. Claude uses date + amount as the primary matching key and references for confirmation.
Tip: If your bank export includes running balances or summary rows, delete those before pasting into the prompt. Claude should only see individual transaction rows.
The Exact Claude Prompt for Bank Reconciliation
Copy this prompt into Claude. Paste your bank statement data and ledger data where indicated. The prompt includes anti-hallucination guardrails — Claude will flag unmatched items rather than inventing matches.
Prompt — paste into Claude
I need to reconcile a bank statement against a general ledger cash account in Excel. Below are both datasets.
BANK STATEMENT (columns: Date, Reference, Description, Amount):
[Paste your bank statement rows here]
GENERAL LEDGER — CASH ACCOUNT (columns: Date, GL Ref, Description, Amount):
[Paste your ledger rows here]
Rules:
- Match transactions by date and amount first. Use reference and description as secondary confirmation only.
- Dates are in DD/MM/YYYY format. Amounts use negative for outflows, positive for inflows.
- If a bank item matches a ledger item exactly (same date and amount), mark it "Matched".
- If a bank item matches a ledger item on date but the amounts differ, mark it "Partial" and show the difference.
- If a bank item has no corresponding ledger entry at all, mark it "Unmatched (Bank only)".
- If a ledger item has no corresponding bank entry, mark it "Unmatched (Ledger only)".
- Do NOT invent or force matches. Do NOT fabricate amounts. If something doesn't match, flag it.
- Do NOT assume timing differences resolve themselves — list every discrepancy.
Output:
1. A reconciliation table with columns: Date | Bank Ref | Bank Amt | Ledger Amt | Difference | Status
2. A separate list of all unmatched items (bank-only and ledger-only)
3. A summary showing: Bank statement total, Ledger total, Total matched, Total unmatched, Net difference
Format the output as an Excel-ready table I can paste directly into a spreadsheet.
Why Bank Reconciliation Still Needs Human Oversight
Bank reconciliation is a controls process, not just a matching exercise. The point isn't to get every row to say "Matched" — it's to find the items that don't match and understand why. Common causes of discrepancies include timing differences (a cheque issued but not yet cleared), bank fees not yet recorded in the ledger, and data entry errors in either system.
Claude handles the repetitive matching work. Your job is to investigate every Partial and Unmatched item, determine the root cause, and post the appropriate adjusting entries. If you're tracking those adjustments as part of a broader close process, the cash flow dashboard course shows how to feed reconciliation outputs into ongoing cash monitoring.
Common Reconciliation Errors
A
B
C
1
Error
Likely Cause
Fix
2
All items show "Unmatched"
Date formats differ between datasets
Specify date format in prompt; reformat in Excel first
3
Small differences on many rows
Sign convention mismatch (debits vs credits)
Ensure both sides use negative=outflow, positive=inflow
4
Duplicate matches
Multiple transactions with same date & amount
Add reference as a secondary key; tell Claude to use it
Frequently Asked Questions
Can Claude read my Excel bank statement directly?
Claude cannot open .xlsx files natively in the free chat. You can paste your data as text, or upload the file in Claude Pro which supports file attachments. For best results, copy the relevant columns from Excel and paste them into the prompt.
How does Claude match bank transactions to ledger entries?
Claude compares rows across your two datasets using the matching criteria you specify — typically date, amount, and reference. It flags exact matches, partial matches (same amount but different date, or vice versa), and completely unmatched items on both sides.
Will Claude invent matches that don't exist?
Not if your prompt includes the guardrails from this course. The prompt explicitly instructs Claude to flag unmatched items rather than forcing a match. Always verify the output against your source data.
Can I use this for multi-currency bank reconciliation?
Yes. Add a Currency column to both datasets and include the exchange rate in your prompt. Ask Claude to convert all amounts to your reporting currency before matching.
How often should I run this reconciliation?
Most finance teams reconcile monthly at minimum. Weekly reconciliation catches errors faster and makes month-end close significantly smoother. The template Claude builds works for any frequency.