Where Does Your Money Go? Auto-Categorize Bank Statements with Python and Gemini

Most people can tell you their salary to the rupiah. Very few can tell you what they spent on food delivery last month, or how much the business paid in bank admin fees this year. The numbers are all there in the statement (mutasi rekening), but they look like this:

TRSF E-BANKING DB 0910/FTSCY/WS95031 ...
KARTU DEBIT INDOMARET T1234 ...
BIAYA ADM

(Illustrative. Every bank formats descriptions differently.)

Sorting 200–400 lines a month into categories by hand takes roughly 10 seconds a line once you include working out what “FTSCY” was, so 30 to 60 minutes. Most people do it for two months and give up.

In this guide you’ll build a small Python tool that:

• reads the CSV export from your bank, whatever its column names and number format
• cleans each description into a short merchant key (dates, reference numbers and account numbers removed)
• applies your own rules first, for free and with no AI involved
• sends only the unknown merchant keys to Gemini 3.5 Flash-Lite, and never sends amounts
• caches every confident answer, so next month most lines are categorized without an API call
• writes a categorized CSV, a spending summary and a short review list

AI cost: under $0.01 per month for a typical personal or small-business account. Build time: about 40 minutes. Monthly effort after the first month: about 5 minutes.

What you need

Pricing as of October 2026 (check ai.google.dev/pricing before you rely on it, because Google changes models and prices often):

• Gemini 3.5 Flash-Lite (gemini-3.5-flash-lite, paid tier): $0.30 per 1M input tokens, $2.50 per 1M output tokens. This is the default in the script.
• Free tier: rate-limited and fine for testing with fake data. On the free tier, Google may use your inputs to improve its products. Don’t put real bank data through it.

Getting the CSV. Most Indonesian banks let you download mutasi rekening from internet banking or the mobile app, usually as PDF and often also as CSV or Excel. The menu differs per bank and changes with app updates, so look for “Mutasi Rekening”, “Account Statement” or “e-Statement”. This guide doesn’t cover turning PDFs into tables. If the app only offers PDF, check whether the internet banking website has a CSV option, because it often does.

How the cost works out

The trick that keeps this cheap is that the AI never sees individual transactions, only unique merchant keys. A month with 300 transactions usually has around 80–150 distinct merchants and payees, because you pay the same shops, apps and people over and over.

For 150 unique keys in the first month:

• Prompt plus keys: about 4,000 input tokens × $0.30/1M = $0.0012
• JSON answers: about 3,000 output tokens × $2.50/1M = $0.0075
• Total: under $0.01

From the second month, the cache already knows most merchants, so you typically send 10–40 new keys and pay a fraction of a cent. The script prints real token counts after each run, so you don’t have to trust these estimates.

Gemini 3.x models “think” before answering and thinking tokens are billed as output. The script sets thinking_level to minimal, which is plenty for “is GOFOOD eating out?”.

Step 1: Set up the project

mkdir spending && cd spending
python3 -m venv .venv && source .venv/bin/activate   # Windows: .venv\Scripts\activate
pip install google-genai pydantic

export GEMINI_API_KEY="paste-your-key-here"           # Windows PowerShell: $env:GEMINI_API_KEY="..."

Open your CSV in a text editor (not Excel) and note three things:

1. How many junk lines come before the header row. Many banks put the account name, number and period at the top. You’ll pass this as --skip.
2. The column names for date, description and amount. Some banks use one signed amount column; others use separate debit and credit columns, or an amount plus a DB/CR marker, either in its own column (use --dbcr) or written inside the amount cell like 50,000.00 DB (plain --amount handles that).
3. The number format. 1.250.000,00 and 1,250,000.00 are both common. The script handles both.

Step 2: Write your rules and categories

Rules are the part you control. They run before the AI, cost nothing and are always right, because you wrote them. Create rules.csv, one keyword,category per line. A rule matches if the keyword appears anywhere in the cleaned merchant key (uppercase):

keyword,category
BIAYA ADM,Fees & admin
GOFOOD,Eating out
GRABFOOD,Eating out
SHOPEEFOOD,Eating out
INDOMARET,Groceries
ALFAMART,Groceries
PLN,Utilities & bills
TARIK TUNAI,Cash withdrawal
GAJI,Income

Rules are checked top to bottom, and the first match wins. Put specific keywords above general ones: GOFOOD before GOJEK, for example, if you want rides and food in different categories. Very short keywords like PLN can match inside longer words, so check the source column after your first run.

The default category list is in the script. To use your own, create categories.txt with one category per line. A small shop might use Stock purchases, Packaging, Shipping, Marketplace fees, Ads, Rent, Salaries, Owner drawings. Keep it under about 20 categories. More than that and you’ll spend your review time arguing with yourself about edge cases.

Step 3: The script

Save this as categorize.py:

import argparse, csv, json, os, re, sys, time
from collections import defaultdict
from pathlib import Path

from google import genai
from google.genai import errors, types
from pydantic import BaseModel

MODEL = os.environ.get("CAT_MODEL", "gemini-3.5-flash-lite")
PRICE_IN, PRICE_OUT = 0.30, 2.50   # USD per 1M tokens, Gemini 3.5 Flash-Lite paid tier
BATCH = 60                         # merchant keys per request
CACHE = Path("cache.json")
DEFAULT_CATEGORIES = [
    "Income", "Groceries", "Eating out", "Transport", "Fuel", "Utilities & bills",
    "Phone & internet", "Shopping", "Health", "Education", "Subscriptions",
    "Business expenses", "Fees & admin", "Transfer", "Cash withdrawal", "Other",
]

class Guess(BaseModel):
    id: int
    category: str
    confident: bool

def parse_amount(s):
    s = re.sub(r"(?i)rp|idr|\s", "", s or "")
    if not s:
        return 0.0
    neg = s.startswith("-") or s.startswith("(")
    if s[-2:].upper() in ("DB", "CR"):  # some banks write "50,000.00 DB" in the amount cell
        neg, s = s[-2:].upper() == "DB", s[:-2]
    s = s.strip("-()")
    if "," in s and "." in s:            # 1.250.000,00 or 1,250,000.00
        if s.rfind(",") > s.rfind("."):
            s = s.replace(".", "").replace(",", ".")
        else:
            s = s.replace(",", "")
    elif "," in s:                       # 50000,00 (decimal) or 1,250,000 (thousands)
        tail = s.split(",")[-1]
        s = s.replace(",", ".") if s.count(",") == 1 and len(tail) == 2 else s.replace(",", "")
    elif s.count(".") > 1 or (s.count(".") == 1 and len(s.split(".")[-1]) == 3):
        s = s.replace(".", "")           # 1.250.000 or 50.000
    v = float(s)
    return -v if neg else v

def merchant_key(desc):
    s = re.sub(r"\S*\d\S*", " ", desc.upper())   # drop tokens with digits: dates, refs, account no.
    s = re.sub(r"[^A-Z& ]", " ", s)
    return " ".join(s.split())[:60] or "UNKNOWN"

def load_rules(path):
    if not Path(path).exists():
        return []
    with open(path, encoding="utf-8-sig", newline="") as f:
        return [(r["keyword"].upper().strip(), r["category"].strip())
                for r in csv.DictReader(f) if r.get("keyword")]

def ask_gemini(client, items, categories):
    prompt = f"""You categorize bank transactions for a person or small business in Indonesia.
Each item is a cleaned transaction description and its direction ("in" = money received,
"out" = money spent). Pick exactly one category from this list, spelled exactly as written:
{json.dumps(categories)}

Rules:
- Return one result per id, with the same id.
- Use your knowledge of Indonesian merchants, apps and banking terms.
- A transfer to or from a person's name with no other clue: category "Transfer", confident false.
- If you are guessing, set confident to false. Do not invent a merchant you don't recognise.

Items:
{json.dumps(items, ensure_ascii=False)}"""
    ids = {i["id"] for i in items}
    for attempt in range(3):
        try:
            resp = client.models.generate_content(
                model=MODEL,
                contents=prompt,
                config=types.GenerateContentConfig(
                    response_mime_type="application/json",
                    response_schema=list[Guess],
                    thinking_config=types.ThinkingConfig(thinking_level="minimal"),
                ),
            )
        except errors.APIError as e:
            print(f"  API error {e.code} (attempt {attempt + 1}), retrying", file=sys.stderr)
            time.sleep(5 * (attempt + 1))
            continue
        got = {g.id: g for g in (resp.parsed or [])}
        if set(got) == ids:
            return got, resp.usage_metadata
        print(f"  ids mismatch (attempt {attempt + 1}), retrying", file=sys.stderr)
    sys.exit("A batch failed 3 times. Try a smaller BATCH.")

def main():
    ap = argparse.ArgumentParser()
    ap.add_argument("csv_file")
    ap.add_argument("--skip", type=int, default=0, help="junk lines before the header row")
    ap.add_argument("--date", required=True, help="date column name")
    ap.add_argument("--desc", required=True, help="description column name")
    ap.add_argument("--amount", help="signed amount column")
    ap.add_argument("--debit", help="money-out column (instead of --amount)")
    ap.add_argument("--credit", help="money-in column (instead of --amount)")
    ap.add_argument("--dbcr", help="column holding DB/CR, used with --amount")
    ap.add_argument("--rules", default="rules.csv")
    args = ap.parse_args()

    cats_file = Path("categories.txt")
    categories = ([c.strip() for c in cats_file.read_text(encoding="utf-8").splitlines() if c.strip()]
                  if cats_file.exists() else DEFAULT_CATEGORIES)
    rules = load_rules(args.rules)
    cache = json.loads(CACHE.read_text(encoding="utf-8")) if CACHE.exists() else {}

    with open(args.csv_file, encoding="utf-8-sig", newline="") as f:
        lines = f.read().splitlines()[args.skip:]
    dialect = csv.Sniffer().sniff(lines[0], delimiters=",;\t")
    rows = []
    for r in csv.DictReader(lines, dialect=dialect):
        r = {k.strip(): (v or "").strip() for k, v in r.items() if isinstance(k, str)}
        if not r.get(args.desc):
            continue                     # footer lines, totals, blank rows
        if args.amount:
            amt = parse_amount(r[args.amount])
            if args.dbcr and r.get(args.dbcr, "").upper().startswith("D"):
                amt = -abs(amt)
        else:
            amt = parse_amount(r.get(args.credit, "")) - parse_amount(r.get(args.debit, ""))
        if amt == 0:
            continue                     # balance lines like "Saldo Akhir" with no amount
        direction = "in" if amt > 0 else "out"
        key = merchant_key(r[args.desc])
        rows.append({"date": r.get(args.date, ""), "description": r[args.desc],
                     "amount": amt, "key": f"{direction}|{key}", "category": "", "source": ""})

    pending = []
    for row in rows:
        merchant = row["key"].split("|", 1)[1]
        rule = next((c for kw, c in rules if kw in merchant), None)
        if rule:
            row["category"], row["source"] = rule, "rule"
        elif row["key"] in cache:
            row["category"], row["source"] = cache[row["key"]], "cache"
        elif row["key"] not in pending:
            pending.append(row["key"])

    unsure, tok_in, tok_out = {}, 0, 0
    if pending:
        client = genai.Client()          # reads GEMINI_API_KEY
        for start in range(0, len(pending), BATCH):
            chunk = pending[start:start + BATCH]
            items = [{"id": i, "direction": k.split("|")[0], "text": k.split("|", 1)[1]}
                     for i, k in enumerate(chunk)]
            got, usage = ask_gemini(client, items, categories)
            tok_in += usage.prompt_token_count or 0
            tok_out += (usage.candidates_token_count or 0) + (usage.thoughts_token_count or 0)
            for i, k in enumerate(chunk):
                cat = got[i].category if got[i].category in categories else "Other"
                if got[i].confident and cat != "Other":
                    cache[k] = cat
                else:
                    unsure[k] = cat
        CACHE.write_text(json.dumps(cache, ensure_ascii=False, indent=1, sort_keys=True),
                         encoding="utf-8")
        for row in rows:
            if not row["source"]:
                k = row["key"]
                row["category"] = cache.get(k) or unsure[k]
                row["source"] = "ai" if k in cache else "ai-unsure"

    stem = Path(args.csv_file).stem
    with open(f"{stem}.categorized.csv", "w", encoding="utf-8", newline="") as f:
        w = csv.DictWriter(f, fieldnames=["date", "description", "amount", "category", "source"],
                           extrasaction="ignore")
        w.writeheader()
        w.writerows(rows)

    totals = defaultdict(float)
    for row in rows:
        totals[row["category"]] += row["amount"]
    print(f"\n{len(rows)} transactions, {len(pending)} new merchants sent to {MODEL}\n")
    for cat, total in sorted(totals.items(), key=lambda x: x[1]):
        print(f"  {cat:<22} {total:>16,.0f}")

    if unsure:
        with open(f"{stem}.review.csv", "w", encoding="utf-8", newline="") as f:
            w = csv.writer(f)
            w.writerow(["keyword", "category"])
            for k, cat in sorted(unsure.items()):
                w.writerow([k.split("|", 1)[1], cat])
        print(f"\n{len(unsure)} unsure merchants -> {stem}.review.csv "
              f"(fix the category, then paste the lines into {args.rules})")
    cost = tok_in / 1e6 * PRICE_IN + tok_out / 1e6 * PRICE_OUT
    print(f"Tokens: {tok_in} in / {tok_out} out, about ${cost:.4f}")

if __name__ == "__main__":
    main()

What it does, in order:

1. Reads the CSV after skipping the bank’s header junk, and works out whether it’s comma-, semicolon- or tab-separated. Rows without a description or without an amount (opening and closing balance lines, totals, blank footers) are skipped.
2. Parses amounts in either Indonesian or English number format, from a signed column, a debit/credit pair, or an amount with a DB/CR marker (in its own column, or stuck to the end of the amount like 50,000.00 DB). Money out ends up negative and money in positive.
3. Builds the merchant key. Any word containing a digit is dropped. That removes dates, terminal IDs, reference codes and account numbers in one go, so KARTU DEBIT INDOMARET T1234 0910 becomes KARTU DEBIT INDOMARET, and the same shop gets the same key every month.
4. Applies rules, then the cache, and collects whatever is left as unique keys. Direction is part of the key, so a transfer to “BUDI SANTOSO” and a transfer from him are treated separately.
5. Asks Gemini about the unknown keys only, with a JSON schema so it can’t merge or skip items, and a fixed category list. An answer that isn’t on the list becomes “Other”.
6. Caches only confident answers. Guesses go to a review file instead, so a wrong guess never gets locked in.

Step 4: Run it

Example for a CSV with a 4-line header, separate debit and credit columns, and Indonesian column names (use whatever your file actually says):

python categorize.py mutasi_sept.csv --skip 4 --date "Tanggal" --desc "Keterangan" \
  --debit "Debet" --credit "Kredit"

Or for a single signed amount column:

python categorize.py statement.csv --date "Date" --desc "Description" --amount "Amount"

Example output (your numbers will differ):

287 transactions, 112 new merchants sent to gemini-3.5-flash-lite

  Transfer                    -4,350,000
  Eating out                  -1,912,500
  Groceries                   -1,486,200
  Shopping                      -967,000
  Transport                     -412,000
  Subscriptions                 -186,000
  Fees & admin                   -47,500
  Income                      12,500,000

14 unsure merchants -> mutasi_sept.review.csv (fix the category, then paste the lines into rules.csv)
Tokens: 3870 in / 2954 out, about $0.0085

Step 5: The 5-minute monthly review

This step is what makes the numbers trustworthy.

1. Open review.csv. It’s usually transfers to people’s names and small merchants with cryptic names. Fix the category column, delete any line you don’t want as a permanent rule, and paste the rest into rules.csv. A transfer to your landlord becomes PAK HENDRA,Rent once and is right forever.
2. Sort categorized.csv by amount and check the 10 biggest lines. A miscategorized Rp 3.000.000 matters; a miscategorized Rp 8.000 parking fee doesn’t.
3. Fix wrong cache entries. If Gemini confidently put something in the wrong place, either add a rule (rules always beat the cache) or edit the line in cache.json.
4. Re-run the script. Unsure merchants are not cached, so they are sent to the AI again on every run until you add a rule for them. Once the review lines are in rules.csv, nothing new goes to the AI and the re-run is free.

Expect the first month to take 15–20 minutes, mostly writing rules. By month three most lines come from rule or cache and the review is down to a handful of new names.

To track month over month, paste each month’s summary into a sheet, or open the categorized CSVs in Google Sheets and build a pivot table (Insert → Pivot table, rows = category, values = SUM of amount).

Honest cost breakdown

For comparison, a full budgeting app like YNAB costs around $109 a year (check their site for the current price) and does a lot more: budgeting, goals, shared plans. If you want a full budgeting app and it supports your bank, pay for one. This tool is for people who just want to know where the money went, or who keep their books in a spreadsheet anyway.

Can you earn with it? Possibly, as part of a bookkeeping service. Small shops and freelancers often hand someone a statement each month and ask for categorized expenses for their own records or their accountant. Once a client’s rules are written, most of the sorting becomes checking, though how much time that saves depends on how messy their statements are. But you’re handling clients’ bank data, which comes with legal duties (see below), and the value you sell is accuracy and consistency, not “AI”. Don’t pitch it as a standalone product.

Reality check

Garbage descriptions stay garbage. Some lines just say TRSF E-BANKING DB with a name, or a QRIS payment shows only the merchant’s registered name, which may look nothing like the shop sign. No model can know that “CV MAJU JAYA” is your coffee supplier. That’s what rules are for.

Transfers are the hardest category. Money moved to your own savings account, to a family member, or paid to a person for goods all look the same. The script marks person-name transfers as unsure on purpose. Write rules for the regular ones.

The merchant key can be too aggressive. Dropping tokens with digits also drops names like 7-ELEVEN or K24. If a merchant keeps landing as UNKNOWN or with a too-short key, add a rule using a word that survives, or adjust merchant_key() for your bank’s format.

Number parsing can guess wrong. 1.250 is treated as one thousand two hundred fifty, which is right for rupiah but wrong for an account that uses three decimal places (rare). Check the totals against your statement’s opening and closing balance the first time. If they don’t add up, the amount columns or format are wrong.

Privacy. The script sends cleaned merchant keys and the in/out direction. It never sends amounts, dates or anything with a digit in it. But person names in transfer descriptions do get sent. If that bothers you, add rules for those names so they never reach the API. Use a paid-tier key: on the free tier, inputs may be used to improve Google’s products.

Doing this for clients means personal data law. Indonesia’s Personal Data Protection Law (UU No. 27/2022) applies to financial data you process for other people. Get written consent, keep files encrypted, delete them when you’re done, and don’t put client statements through free-tier AI.

This is not accounting. Categorized spending is useful for decisions (“we spend more on delivery than on rent”), but it isn’t a tax return or a profit and loss statement. For taxes, give your accountant the categorized file and let them do their job.

Model names change. Google retires and reroutes Gemini models every few months. If gemini-3.5-flash-lite stops working, set CAT_MODEL to the current Flash-Lite model and update PRICE_IN/PRICE_OUT so the cost line stays honest.

Final checklist

• CSV exported and opened in a text editor to check skip lines, columns and number format
• Paid-tier Gemini API key set
• rules.csv started with your 10 most obvious merchants
• First run done, totals checked against the statement
• review.csv fixed and pasted into rules.csv
• Biggest 10 transactions checked by hand
• Reminder set for the same 5 minutes next month

Run it on last month’s statement tonight. The first summary is usually the one that changes a habit.