How to categorize bank transactions with AI, then tie it out

Step 1: Prepare the export

• Download the export as a CSV if the bank offers one. Reading a PDF adds its own errors.
• Keep the original file untouched and work on a copy.
• Before you start, note the number of lines and the statement's opening and closing balances. These are your control totals.
• Take out account numbers, and anything that identifies a client or an individual, unless your firm has approved the tool for client data. The guide to putting client data into AI tools covers what can go in.

Step 2: Set the rules

"Categorize these transactions" gets you a confident answer on every line, including the ones nobody could code from the description. Four rules make the output something you can check:

1. A fixed category list. Paste the accounts it may use, with a line on what each one covers, and tell it not to invent new ones.
2. A rule for lines it can't match. Have it mark them "Needs review" and say what's missing, instead of picking the closest category. A guessed code looks exactly like a right one.
3. Every line, in the original order. No merging, and no removing what it thinks are duplicates. Two identical charges on the same day can both be real.
4. A fixed output format. Date, description, amount, category and a short reason, as a table you can paste into a spreadsheet.

Attached are a bank export (CSV) and our category list.

Code each line to one category from the list. Rules:
- Use only the categories on the list. Don't add new ones.
- If a line doesn't say enough to code it, put "Needs review" in the Category column and say what's missing in the Reason column. Don't guess.
- Keep every line of the export, in its original order. Don't merge or remove any lines, even if they look like duplicates.
- Keep each amount exactly as it appears, with money out as a negative number.

Return a table with these columns: Date, Description, Amount, Category, Reason.

Step 3: Tie it out

This is the step that turns an AI table into something you can post. Don't ask the AI whether its table is right. Check it yourself, in a spreadsheet, against figures that came from the bank. Paste the table in with the amounts in column C and the categories in column D.

1. The line count

The table should have as many lines as the export. COUNT counts the amounts:

2. The closing balance

The opening balance plus money in, less money out, should equal the closing balance on the statement. With the opening balance in cell F1:

3. The category subtotals

Total each category with SUMIF, then check that the subtotals add up to the same figure as =SUM(C:C). A line with a blank or misspelled category drops out of the subtotals, so this catches it.

4. Read it through

Sort by category and read down each one. A wrong code tends to stand out next to the right ones. Read the reasons on the largest amounts most closely.

If the balance doesn't tie, find the gap before you do anything else. The difference is often a single line, so search the export for that exact amount.

Step 4: Handle the exceptions

Lines marked "Needs review" go to someone who knows the answer: the client, the cardholder, or whoever approved the spend. Don't send them back to the AI for a better guess.

Some lines are easy to code wrongly, by a person or an AI. Look twice at these:

• Transfers between the business's own accounts look like income or spending, and they're neither.
• Owner draws and contributions are equity, not expenses or revenue.
• Refunds and reversals need the right sign, and usually belong in the same account as the original charge.
• Loan payments usually mix principal and interest, so one bank line becomes two entries.
• Payment processor deposits from services such as Stripe, Square or PayPal are often net of fees, so the sales figure is higher than the deposit.
• Credit card payments settle the card balance and aren't an expense in themselves; the card's own transactions are.

When the AI gets a line wrong, point it at the exact line and say what it is:

Line 14, "PAYPAL *4821", +$312.00, is a refund of the October 3 software charge, not a sale. Recode it to Software as money in, and check whether any other PayPal lines are refunds.

Step 5: Record the review

Before anything is posted, write down what you checked. A short note on the file is enough, and it's what a reviewer will ask for.

Can ChatGPT do a bank reconciliation?

Not on its own. Categorizing a bank export is coding. A bank reconciliation matches the bank to your ledger and explains every difference: outstanding checks, deposits in transit, bank fees not yet booked and errors. AI can help with the matching too, especially on a long list, but the same rule applies: it suggests the matches, and you prove the difference to the cent.

Matching two lists and finding the difference is lesson 5 in the track, which opens soon.