Build a Personal Finance Categorizer from Bank CSVs Using OpenRouter Free Models

What You’re Shipping

A single Python script that drags a messy bank CSV through a free LLM, slaps a category on every transaction, and renders a clean Streamlit dashboard. No credit card, no GPU, no cloud bill. You’ll own the pipeline end-to-end.

Feature list:

• Accepts generic bank CSV exports (Date, Description, Amount).
• Normalizes column names and formats automatically.
• Batches transactions to OpenRouter’s free models (e.g., Mistral 7B, Gemma 2) for zero-cost categorization.
• Caches LLM responses so re-runs don’t burn your rate limit.
• Displays a Streamlit dashboard with monthly spend breakdowns, category pie charts, and a searchable transaction table.
• Keeps your financial data on your machine—no third-party aggregators.

If you’ve already built agents that triage email or tailor resumes, this is the same muscle: unstructured text in, structured decisions out. For a deeper dive on practical agent patterns, see how we combined Gemini and Groq free tiers to auto-draft replies in the Gmail AI Triage Agent guide.

Architecture: The Data Flow

Key design decisions:

• SQLite cache keyed on transaction description + amount means you never re-categorize the same transaction twice. This is critical when you’re iterating on the dashboard and re-running the script.
• Batch size of 10 keeps you under OpenRouter’s free-tier rate limits while giving the model enough context to spot patterns (e.g., “UBER” and “UBER TRIP” should both map to “Transport”).
• Streamlit’s @st.cache_data decorator loads the processed DataFrame once, so filtering is instant.

Prerequisites & Free-Tier Setup

OpenRouter setup (2 minutes):

1. Create an account at openrouter.ai.
2. Go to API Keys and generate a key. The free tier gives you access to models like mistralai/mistral-7b-instruct:free and google/gemma-2-9b-it:free.
3. Export it: export OPENROUTER_API_KEY=sk-or-v1-...

No credit card, no trial expiration. Rate limits are ~20 requests/minute on free models, which is why we batch and cache.

Step 1: Scaffold the Project & Dependencies

mkdir finance-categorizer && cd finance-categorizer
python -m venv venv && source venv/bin/activate  # Windows: venv\Scripts\activate
pip install pandas streamlit requests openai

Create the project structure:

finance-categorizer/
├── main.py           # Streamlit dashboard
├── categorizer.py    # LLM categorization logic
├── cache.db          # SQLite cache (auto-created)
├── transactions.csv  # Your bank export (you provide this)
└── requirements.txt

requirements.txt:

pandas>=2.0.0
streamlit>=1.28.0
requests>=2.31.0
openai>=1.0.0

Step 2: Ingest and Normalize Bank CSVs

Bank CSVs are chaos. Column names vary (Transaction Date vs Date vs Posting Date), amounts might be signed or have a separate Debit/Credit column. We normalize aggressively.

Create categorizer.py:

import pandas as pd
import hashlib
import json
import sqlite3
from datetime import datetime

EXPECTED_COLUMNS = {
    'date': ['date', 'transaction date', 'posting date', 'value date'],
    'description': ['description', 'memo', 'narrative', 'payee', 'name'],
    'amount': ['amount', 'value', 'sum', 'transaction amount'],
    'debit': ['debit', 'withdrawal', 'money out'],
    'credit': ['credit', 'deposit', 'money in'],
}

def normalize_csv(filepath: str) -> pd.DataFrame:
    df = pd.read_csv(filepath)
    df.columns = [c.strip().lower() for c in df.columns]

    # Map columns
    col_map = {}
    for target, aliases in EXPECTED_COLUMNS.items():
        for alias in aliases:
            if alias in df.columns:
                col_map[alias] = target
                break

    df = df.rename(columns=col_map)

    # Handle debit/credit split format
    if 'amount' not in df.columns and 'debit' in df.columns and 'credit' in df.columns:
        df['debit'] = df['debit'].fillna(0).astype(float)
        df['credit'] = df['credit'].fillna(0).astype(float)
        df['amount'] = df['credit'] - df['debit']
        df = df.drop(columns=['debit', 'credit'])

    # Parse dates
    df['date'] = pd.to_datetime(df['date'], dayfirst=False, errors='coerce')
    df = df.dropna(subset=['date', 'description', 'amount'])

    # Normalize amount to float
    df['amount'] = df['amount'].astype(str).str.replace('[£$€,]', '', regex=True).astype(float)

    return df[['date', 'description', 'amount']]

This handles the three most common CSV formats: single amount column, separate debit/credit columns, and European-style currency symbols. If your bank does something exotic, you add one alias to EXPECTED_COLUMNS and move on.

Step 3: Call OpenRouter Free Models for Categorization

We use the OpenAI-compatible endpoint so we can swap models with one line change. The prompt is engineered for consistency: we ask for a single-word category from a fixed taxonomy.

Add to categorizer.py:

from openai import OpenAI

CATEGORIES = [
    "Housing", "Transport", "Food & Dining", "Utilities",
    "Entertainment", "Shopping", "Healthcare", "Income",
    "Transfer", "Subscription", "Other"
]

client = OpenAI(
    base_url="https://openrouter.ai/api/v1",
    api_key="your-key-here",  # Use os.environ.get("OPENROUTER_API_KEY")
)

def get_cache_db():
    conn = sqlite3.connect("cache.db")
    conn.execute("CREATE TABLE IF NOT EXISTS cache (key TEXT PRIMARY KEY, category TEXT)")
    return conn

def cache_key(description: str, amount: float) -> str:
    raw = f"{description.lower().strip()}|{amount:.2f}"
    return hashlib.sha256(raw.encode()).hexdigest()

def categorize_batch(transactions: list[dict]) -> list[str]:
    """Send a batch to OpenRouter, return categories in order."""
    items = "\n".join(
        f"{i+1}. {t['description']} (${t['amount']:.2f})"
        for i, t in enumerate(transactions)
    )

    prompt = f"""Categorize each transaction into exactly one of: {', '.join(CATEGORIES)}.
Respond ONLY with a JSON array of strings in the same order, no explanation.

Transactions:
{items}

Output:"""

    response = client.chat.completions.create(
        model="google/gemma-2-9b-it:free",  # Swap to mistralai/mistral-7b-instruct:free if rate-limited
        messages=[{"role": "user", "content": prompt}],
        temperature=0.0,
        max_tokens=200,
    )

    raw = response.choices[0].message.content.strip()
    # Clean markdown fences if present
    if raw.startswith("```"):
        raw = raw.split("\n", 1)[1].rsplit("\n", 1)[0]
    return json.loads(raw)

def categorize_transactions(df: pd.DataFrame, batch_size: int = 10) -> pd.DataFrame:
    conn = get_cache_db()
    df = df.copy()
    df['category'] = None

    for i in range(0, len(df), batch_size):
        batch = df.iloc[i:i+batch_size]
        uncached = []
        indices = []

        for idx, row in batch.iterrows():
            key = cache_key(row['description'], row['amount'])
            cached = conn.execute("SELECT category FROM cache WHERE key=?", (key,)).fetchone()
            if cached:
                df.at[idx, 'category'] = cached[0]
            else:
                uncached.append({'description': row['description'], 'amount': row['amount']})
                indices.append(idx)

        if uncached:
            try:
                categories = categorize_batch(uncached)
                for idx, cat in zip(indices, categories):
                    df.at[idx, 'category'] = cat
                    key = cache_key(df.at[idx, 'description'], df.at[idx, 'amount'])
                    conn.execute("INSERT OR REPLACE INTO cache VALUES (?, ?)", (key, cat))
            except Exception as e:
                print(f"Batch failed: {e}. Falling back to 'Other'.")
                for idx in indices:
                    df.at[idx, 'category'] = "Other"

        conn.commit()

    conn.close()
    return df

Why this works: The cache is the secret sauce. On first run, a 500-transaction CSV takes ~50 API calls. On second run, it takes zero. The temperature=0.0 makes categorization deterministic, so cache hits are consistent.

Step 4: Build the Spending Dashboard with Streamlit

Create main.py:

import streamlit as st
import pandas as pd
import plotly.express as px
from categorizer import normalize_csv, categorize_transactions
import os

st.set_page_config(page_title="Finance Categorizer", layout="wide")
st.title("💸 Personal Finance Categorizer")
st.caption("Powered by free LLMs via OpenRouter — your data never leaves this machine.")

uploaded_file = st.file_uploader("Upload bank CSV", type=["csv"])

if uploaded_file:
    with st.spinner("Normalizing CSV..."):
        df = normalize_csv(uploaded_file)

    if st.button("Categorize Transactions"):
        with st.spinner("Categorizing with LLM (first run may take a minute)..."):
            df = categorize_transactions(df)

        st.session_state['df'] = df
        st.success(f"Categorized {len(df)} transactions.")

    if 'df' in st.session_state:
        df = st.session_state['df']

        # KPI row
        col1, col2, col3 = st.columns(3)
        total_spent = df[df['amount'] < 0]['amount'].sum()
        total_income = df[df['amount'] > 0]['amount'].sum()
        col1.metric("Total Spent", f"${abs(total_spent):,.2f}")
        col2.metric("Total Income", f"${total_income:,.2f}")
        col3.metric("Net", f"${total_income + total_spent:,.2f}")

        # Monthly breakdown
        df['month'] = df['date'].dt.to_period('M').astype(str)
        monthly = df.groupby(['month', 'category'])['amount'].sum().reset_index()

        st.subheader("Monthly Spend by Category")
        fig = px.bar(
            monthly[monthly['amount'] < 0],
            x='month', y='amount', color='category',
            title="Monthly Spending", barmode='group'
        )
        fig.update_layout(yaxis_tickprefix='$')
        st.plotly_chart(fig, use_container_width=True)

        # Category pie
        col_pie, col_table = st.columns([1, 2])
        with col_pie:
            st.subheader("Category Breakdown")
            cat_totals = df.groupby('category')['amount'].sum()
            fig2 = px.pie(values=cat_totals.abs(), names=cat_totals.index, hole=0.4)
            st.plotly_chart(fig2, use_container_width=True)

        with col_table:
            st.subheader("All Transactions")
            search = st.text_input("Search descriptions")
            filtered = df[df['description'].str.contains(search, case=False)] if search else df
            st.dataframe(
                filtered[['date', 'description', 'amount', 'category']].sort_values('date', ascending=False),
                use_container_width=True,
                hide_index=True
            )

This dashboard follows the same pattern as our Resume Tailoring Agent—upload unstructured data, process with an LLM, render actionable output. The @st.cache_data decorator isn’t needed here because we’re using st.session_state, but for larger datasets you’d add it to normalize_csv.

Step 5: Run It Locally

export OPENROUTER_API_KEY=sk-or-v1-your-key-here
streamlit run main.py

Open http://localhost:8501, upload a CSV, click Categorize Transactions, and watch the dashboard populate.

Testing without a real bank CSV: Create a test.csv:

Date,Description,Amount
2024-01-05,AMAZON.COM, -29.99
2024-01-06,UBER TRIP, -14.50
2024-01-07,SALARY DEPOSIT, 3200.00
2024-01-08,NETFLIX SUBSCRIPTION, -15.99
2024-01-09,TRADER JOE'S, -67.32

First run categorizes 5 transactions in one batch. Second run hits cache instantly.

Sensible Extensions

1. Multi-account support: Add a --account flag that tags each CSV with an account name, then filter by account in the dashboard.
2. Custom category taxonomies: Let users define their own categories in a config.yaml. The prompt dynamically injects them.
3. Export to Google Sheets: Use gspread to push the categorized DataFrame to a sheet. Combine with the Gmail AI Triage Agent pattern to auto-email weekly summaries.
4. Local model fallback: If OpenRouter is down, fall back to Ollama running llama3.2:1b locally. Same OpenAI-compatible endpoint, just change base_url.
5. Anomaly detection: Flag transactions >2 standard deviations from the category mean and highlight them in red on the dashboard.

Common Pitfalls

• CSV encoding: Some banks export UTF-16 or ISO-8859-1. If pd.read_csv throws, add encoding='utf-16' or encoding='latin1'.
• OpenRouter rate limits: Free models throttle at ~20 RPM. If you hit 429, the script will fail that batch. Solution: reduce batch_size to 5 and add a time.sleep(3) between batches.
• JSON parsing failures: Occasionally the free model returns malformed JSON (trailing commas, markdown fences). The json.loads will fail. The current code catches this and falls back to "Other" for that batch. For production, add a retry with response_format={"type": "json_object"} if the model supports it.
• Negative amounts: Some banks represent debits as positive numbers with a "Debit" column. The normalizer handles this, but if your bank uses yet another convention, inspect the raw CSV and extend EXPECTED_COLUMNS.
• Memory with large CSVs: A 10,000-row CSV is fine. At 100k+ rows, switch from loading the full DataFrame in Streamlit to paginated queries against a DuckDB file.

FAQ

Q: Is my financial data sent to OpenRouter? Yes—the transaction descriptions and amounts are sent as prompts. OpenRouter’s privacy policy states they don’t store prompts for free models, but if you’re uncomfortable, swap the model for a local Ollama instance. The code structure doesn’t change.

Q: How accurate is the categorization? With gemma-2-9b-it, expect ~90% accuracy on common merchants. Edge cases like "SQ* COFFEE SHOP" might get "Other". You can improve this by adding few-shot examples to the prompt or fine-tuning (which breaks the “free” constraint).

Q: Can I use this with multiple currencies? The normalizer strips $, £, €. The dashboard displays $ but you can change the prefix. Multi-currency accounts need a currency column and conversion logic—left as an exercise.

Q: What if I want to deploy this for my family? Streamlit Cloud has a free tier. Push the repo, add your OPENROUTER_API_KEY as a secret, and deploy in 2 clicks. Each family member uploads their own CSV; the cache is per-deployment, so you’d want to switch to a serverless SQLite (like Turso’s free tier) for shared caching.

Q: How does this compare to Mint or YNAB? Those are polished products with bank syncing. This is a privacy-first, zero-cost tool you own. If you’re an engineer who wants control over categorization logic and data residency, this is your path. For a deeper look at building practical AI tools that ship value fast, check out A Week in the Life of a Forward Deployed Engineer.