🎮 The Next Input — Issue #051

The AI That Cleans Your Messy Bank Feed

Author

Aaron Bost
September 10, 2025

Workday

⚡ The Briefing — 60 sec

🛠️ The Playbook — AI Expense Categorizer

Mission Auto-clean and categorize messy bank feeds in real time, freeing your bookkeeper to focus only on edge cases.
Difficulty Medium | Build time 60–90 min
ROI Finance teams save ≈ 6–10 h/month in manual transaction coding.

Step

Action

1

Trigger – Import CSV or sync bank feed into Google Sheets/Airtable.

2

Normalize – Regex clean transaction strings (strip IDs, unify case).

3

Vendor Map – Lookup aliases (AMZ MKTPLACE → Amazon, etc.).

4

LLM Fallback – If no match, call Claude/GPT with Rulebook prompt.

5

Confidence Routing – ≥0.85 auto-post; 0.6–0.85 Needs Review; <0.6 Unclassified.

6

Review Queue – Bookkeeper approves/edits, which updates Vendor Map.

7

Push – Approved rows sync back to Xero/QuickBooks nightly.

Pro tip: Use MCC codes + recurring vendor detection to boost confidence before hitting the LLM.

🗺️ The Side Quest

Each week, we answer a question from a reader. This week, we’re fixing the biggest bottleneck in bookkeeping.

This Week’s Side Quest Question

“My company’s bank feed is a chaotic mess (AMZ MKTPLACE, STRIPE-PAYMENT-1AB23C, etc.). I waste hours categorizing for my bookkeeper. Can AI auto-clean and categorize in real time?”

1) The Core Goal — Augment, don’t replace

We’re not firing your bookkeeper—we’re feeding them clean, pre-coded data with a confidence score and reason. The AI normalizes payees, assigns categories/tax codes, and learns vendor rules over time; humans only review edge cases.

2) The Rulebook — Your Chart of Accounts (CoA) in machine-readable form

Create a tiny “policy DB” the AI can reference:

  • Chart of Accounts: Software, Advertising & Marketing, Travel, Meals, Office, Contractors, Bank Fees, etc.

  • Vendor Map (aliases → canonical):

    • AMZ MKTPLACE|AMZN DIGITAL|Amazon AU → Amazon → Office (default)

    • GOOGLE*SVCS|Google Cloud|GCP → Google → Software

    • UBER TRIP|UBER EATS → Uber → Travel / Uber Eats → Meals

  • Heuristics: MCC hints, currency/region, memo keywords, recurring list.

  • Tax rules: Map vendors to tax codes, let HITL confirm.

  • Edge policies: E.g., hardware > $1,000 → CapEx.

Store in Airtable/Sheets with columns: alias, vendor, default_category, default_tax_code, last_confirmed.

3) The Tech Stack — Lean → Standard

Lean (today): Bank CSV → Google Sheet → Make/Zapier → LLM categorize → write back → push to Xero/QuickBooks.

Standard (real-time): Bank aggregator webhook → Cloud function → Regex clean + vendor lookup → LLM if unresolved → Accounting API + Slack “Needs Review” queue.

Flow: Trigger → Normalize → Rule match → LLM → Write → Review → Learn.

4) The “Categorizer” Prompt — Drop-in template

(See full JSON schema + example in the detailed guide above.)

System role: Accounting assistant. Categorize bank transactions with provided CoA and Vendor Map. Return strict JSON: {vendor_normalized, category, tax_code, confidence, requires_review, reason}.

5) Human Checkpoint — Training the system

Thresholds: auto if ≥0.85, Needs Review if 0.6–0.85, Unclassified if <0.6. Approvals update Vendor Map automatically. Weekly 10-min audit keeps rules fresh.

6) The Simplest V1 — Afternoon build

  • Sheets tabs: Transactions, VendorMap, Results.

  • Scenario: Clean description → VendorMap lookup → LLM fallback → Write to Results.

  • Push nightly to accounting software.

Day-2 niceties: Refund detection, transfer detection, recurring vendor boost.

Pro Tips & Gotchas

  • Keep math deterministic (no model-calculated taxes).

  • Normalize first (regex out IDs).

  • MCC > model when present.

  • Guardrails: never auto-change Income/Transfers.

  • Privacy: mask PAN/IBAN in logs.

TL;DR

Build a Rulebook (CoA + Vendor Map). Flow: Trigger → Normalize → Rule match → LLM fallback → Review → Learn.
Ship a Sheets + Make/Zapier V1 today; upgrade to bank webhooks + accounting API later.

Result: Hours saved monthly, cleaner books, happier bookkeeper—and no more “AMZ MKTPLACE” chaos.

🎯 The Arsenal — Tools & Prompts

Asset

What it does

Link

Plaid / TrueLayer

Bank aggregator APIs.

https://plaid.com

Airtable

Vendor map + review queue.

https://airtable.com

Xero / QuickBooks APIs

Write back categorized results.

https://developer.xero.com

Prompt · Categorizer JSON

Transaction → structured output. 

Categorize this transaction using Rulebook. Return JSON: 
{vendor_normalized, category, tax_code, confidence, requires_review, reason}

💡 Free Office Hours

Need an AI categorizer for bank feeds?
Book a free 15-minute Office Hours slot—no sales pitch, just workflows solved.

🕹️ Game Over

Automate one bank-feed cleanup tonight—tomorrow your bookkeeper will thank you.
Share your win; you could headline Issue #052.

— Aaron
Automating the boring. Amplifying the brilliant.

Forwarded this? Subscribe here