Bank reconciliation with Claude: prompt + controls

When to use it

Use it for any cash account where your ERP or reconciliation tool's auto-match leaves a long tail of unmatched items: lockbox deposits batched differently from the GL, card settlements net of fees, payroll funding, or accounts where nobody ever built matching rules.

It is not a replacement for a reconciliation tool that already auto-matches nearly all of your volume. It's for the leftovers and for teams that still reconcile in Excel.

Input data

• Bank statement or bank activity export for the period (CSV or XLSX), including the bank's ending balance.
• GL detail for the cash account for the same period (date, JE number, source, description, amount), plus the GL ending balance.
• Prior month's reconciliation, showing open reconciling items carried forward (outstanding checks, deposits in transit).
• Optional: your matching tolerance (e.g. date ±3 business days) and known recurring patterns ("merchant settlements post net of fees").

The prompt

Replace the [bracketed] items. Paste it with your files attached. It works in any capable assistant; tool-specific notes are in the steps.

You are helping me prepare a bank reconciliation for [account name, last 4 digits only] for the period ending [MM/DD/YYYY].

Files attached:
1. Bank activity: [file name]. Bank ending balance per statement: [amount].
2. GL cash detail: [file name]. GL ending balance: [amount].
3. Prior-month reconciliation with open items: [file name].

Do the following, and show your work in tables I can paste into Excel:

A. Profile both files first: row counts, date range, sum of debits and credits, and any duplicates (same date, amount and reference). Confirm the GL detail rolls from beginning to ending balance. If anything doesn't tie, stop and tell me before matching.

B. Match bank lines to GL lines in this order:
   1) exact amount + same reference/check number;
   2) exact amount within ±[3] business days;
   3) one-to-many or many-to-one where a group of lines sums exactly to one line (e.g. a batched deposit), and list every component;
   Never match on amount alone when there are multiple candidates with the same amount; list them as "ambiguous" instead.

C. Clear prior-month open items that now appear in the bank, and list the ones still open with their age in days.

D. Put every unmatched item in exactly one bucket:
   - Outstanding check / payment (in GL, not yet in bank)
   - Deposit in transit (in GL, not yet in bank)
   - Bank activity not yet recorded in GL (fees, interest, returns, unknown debits)
   - Possible error (amount or date mismatch, duplicate, transposition)
   For each, give the evidence and your confidence (high/medium/low).

E. Produce the reconciliation: bank ending balance, plus/minus each reconciling category, adjusted bank balance; GL ending balance, plus/minus unrecorded items, adjusted GL balance; and the unreconciled difference. Do not force it to zero. If there is a difference, say so and list the most likely causes.

F. For bank items not in the GL, suggest the account and a draft entry description. Label them "DRAFT – for review". Do not assume an entry is needed for anything you rated low confidence.

Rules: use only the data in the files. Do not invent transactions, balances or explanations. If a column is ambiguous, ask me.

Steps

1. Export the bank activity and the GL cash detail for exactly the same cut-off. Mismatched cut-offs are the #1 cause of noise.
2. Mask the account numbers down to the last four digits before uploading.
3. Run the prompt. Read section A first: if the files don't profile or roll forward, fix the exports rather than letting the model carry on.
4. Spot-check at least five matches from each matching tier against the source documents, and look at every one-to-many match.
5. Work the exception list: investigate the "possible error" and "unrecorded" buckets in the source systems. The AI's explanation is a lead, not evidence.
6. Prepare any adjusting entries in your ERP through the normal JE workflow, with support attached.
7. Complete the reconciliation in your system of record (reconciliation tool or the standard template). The preparer signs off and the reviewer reviews as usual.

Control map

What the AI may do, step by step. How ratings work.

Audit trail

• Save the exact files you uploaded, the prompt and the AI's output with the reconciliation workpaper (a PDF or text export of the chat is fine).
• Record who ran it, the date, the tool and the model/version if it's shown.
• Evidence the reviewer's spot-checks: tick-mark the matches you re-performed.
• The reconciliation itself must still tie to the bank statement and the GL trial balance from your systems, not to numbers the AI typed.

Failure modes

• Same-amount collisions. Recurring payments with identical amounts get matched to the wrong date. The prompt forces these into "ambiguous", so check that it actually did.
• Plausible but invented explanations for unmatched items ("likely a bank fee"). Treat these as hypotheses.
• Silent truncation. Very large files can be partially read. The row counts in step A are your check, so compare them to the source.
• Sign conventions. Bank exports show debits as negatives, while GL exports may not. Confirm the model's sign assumption in the profile.
• Forced balancing. Without the "do not force to zero" instruction, models tend to make a reconciliation look balanced.

Data handling

• Use a company-approved AI workspace whose terms say your inputs are not used for model training. Check your plan; don't assume it.
• Strip or mask what the task doesn't need: employee names, bank account numbers, customer PII, SSNs or TINs.
• If you are a public company, treat pre-release results as material non-public information and keep them inside approved systems.