Build a Personal Finance Categorizer from Bank CSVs Using a Free Local LLM

What We’re Building

We’re building a Python script that takes your raw bank CSV exports, feeds each transaction description to a free local LLM running through Ollama, and outputs a categorized report. No cloud API keys. No data leaving your machine. No monthly bills.

Feature list:

• Ingests any bank CSV with a Date, Description, and Amount column.
• Uses Mistral or Llama 3 via Ollama to assign categories like Groceries, Rent, Transport, Entertainment.
• Handles merchant name normalization (e.g., AMZN MKTP → Shopping).
• Outputs a clean CSV or interactive Streamlit dashboard.
• Runs entirely offline on a laptop with 8GB+ RAM.

If you’ve ever stared at a year’s worth of transactions dreading tax season, this is your weekend project. The core loop is dead simple: parse → prompt → parse again. Let’s wire it up.

Architecture Overview

Here’s how the pieces fit together. The flow is linear, but the LLM is the brain in the middle.

The script reads the CSV into a DataFrame, chunks transactions into small batches (to respect context windows), sends each batch to Ollama with a strict categorization prompt, parses the JSON response, and stitches everything back together. Optionally, Streamlit gives you a filterable UI.

Prerequisites and Free Tools

Everything here is free, open-source, or has a generous free tier. No credit card required.

Hardware note: Mistral 7B runs comfortably on 8GB RAM. Llama 3 8B needs a bit more headroom. If you’re on a 16GB machine, pull Llama 3 for better accuracy. On a Raspberry Pi? Stick to TinyLlama or Phi-3-mini.

Step 1: Set Up Your Python Environment

Create a project folder and a virtual environment. You know the drill.

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

Create an empty main.py and a prompts.py. We’ll fill them in as we go.

Step 2: Install and Run Ollama with a Local Model

Download Ollama from ollama.com/download and install it. Once it’s running (look for the tray icon or run ollama serve in a terminal), pull a model:

ollama pull mistral

Verify it works with a quick test:

curl http://localhost:11434/api/generate -d '{
  "model": "mistral",
  "prompt": "Categorize this transaction: UBER TRIP SAN FRANCISCO",
  "stream": false
}'

You should see a JSON response with a category like Transport or Ride Share. If you get a connection error, make sure ollama serve is running in the background.

Step 3: Parse Bank CSVs with Pandas

Bank CSVs are messy. Column names vary (Transaction Date vs Date, Debit vs Amount). We’ll write a flexible parser that normalizes common schemas.

# parser.py
import pandas as pd
from pathlib import Path

def load_transactions(csv_path: str) -> pd.DataFrame:
    df = pd.read_csv(csv_path)
    
    # Normalize column names: lowercase, strip whitespace
    df.columns = [col.strip().lower() for col in df.columns]
    
    # Map common column names to our standard schema
    column_map = {
        'date': 'date',
        'transaction date': 'date',
        'posting date': 'date',
        'description': 'description',
        'memo': 'description',
        'narration': 'description',
        'amount': 'amount',
        'debit': 'amount',
        'value': 'amount',
    }
    
    df = df.rename(columns={k: v for k, v in column_map.items() if k in df.columns})
    
    # Ensure required columns exist
    required = ['date', 'description', 'amount']
    missing = [col for col in required if col not in df.columns]
    if missing:
        raise ValueError(f"Missing required columns: {missing}")
    
    # Clean amount: handle parentheses for negatives, remove currency symbols
    df['amount'] = df['amount'].astype(str).str.replace(r'[$,]', '', regex=True)
    df['amount'] = df['amount'].str.replace(r'\(([^)]+)\)', r'-\1', regex=True)
    df['amount'] = pd.to_numeric(df['amount'], errors='coerce')
    
    # Drop rows with invalid amounts or descriptions
    df = df.dropna(subset=['amount', 'description'])
    df['description'] = df['description'].astype(str).str.strip()
    
    return df[['date', 'description', 'amount']]

Test it with your own bank export. Most banks let you download a CSV from the transactions page. If yours has a weird format, extend the column_map dictionary.

Step 4: Build the LLM Categorization Engine

This is the core. We send batches of transactions to Ollama with a structured prompt and parse the JSON response. The prompt is everything—be explicit about the output format.

# categorizer.py
import json
import requests
from typing import List, Dict

OLLAMA_URL = "http://localhost:11434/api/generate"
MODEL = "mistral"  # or "llama3:8b"

CATEGORIES = [
    "Housing", "Transport", "Groceries", "Dining Out", "Utilities",
    "Entertainment", "Shopping", "Healthcare", "Education", "Income",
    "Transfer", "Subscription", "Travel", "Other"
]

SYSTEM_PROMPT = f"""You are a personal finance categorizer. Given a list of bank transactions, assign each one to exactly one category from this list: {', '.join(CATEGORIES)}.

Rules:
- Use 'Dining Out' for restaurants, cafes, bars, food delivery.
- Use 'Groceries' for supermarkets, farmers markets.
- Use 'Shopping' for retail, Amazon, clothing.
- Use 'Subscription' for recurring services like Netflix, Spotify, gym memberships.
- Use 'Transfer' for bank transfers, Venmo, PayPal person-to-person.
- If unsure, use 'Other'.

Return ONLY a JSON array of objects with keys 'index' (the transaction's position in the input list) and 'category'. No other text."""

def categorize_batch(transactions: List[Dict]) -> List[Dict]:
    """Send a batch of transactions to Ollama and return categorized results."""
    
    # Build the user prompt with indexed transactions
    user_prompt = "Categorize these transactions:\n"
    for i, txn in enumerate(transactions):
        user_prompt += f"{i}: {txn['description']} (${txn['amount']:.2f})\n"
    
    payload = {
        "model": MODEL,
        "prompt": f"{SYSTEM_PROMPT}\n\n{user_prompt}",
        "stream": False,
        "format": "json",  # Ollama 0.1.8+ supports native JSON mode
        "options": {
            "temperature": 0.1,  # Low temp for consistent categorization
            "num_predict": 500
        }
    }
    
    try:
        response = requests.post(OLLAMA_URL, json=payload, timeout=60)
        response.raise_for_status()
        result = response.json()
        
        # Parse the response - Ollama returns the generated text in 'response'
        raw_output = result.get('response', '').strip()
        
        # Handle cases where the model wraps JSON in markdown fences
        if raw_output.startswith('```'):
            raw_output = raw_output.split('\n', 1)[1].rsplit('\n', 1)[0]
        
        categories = json.loads(raw_output)
        return categories
        
    except (json.JSONDecodeError, KeyError) as e:
        print(f"Failed to parse LLM response: {e}")
        print(f"Raw output: {raw_output}")
        # Fallback: mark all as 'Other'
        return [{"index": i, "category": "Other"} for i in range(len(transactions))]

Why batch? Sending one transaction per API call is slow. Batching 10-20 transactions per request hits a sweet spot between speed and accuracy. Mistral’s context window handles this easily.

Why temperature 0.1? We want deterministic categorization, not creative writing. Low temperature keeps the model from getting fancy.

Step 5: Assemble the Main Script and Run It

Now we wire everything together. The main script loads the CSV, chunks transactions, calls the categorizer, and writes the output.

# main.py
import pandas as pd
from parser import load_transactions
from categorizer import categorize_batch, CATEGORIES

BATCH_SIZE = 15  # Transactions per LLM call

def run(csv_path: str, output_path: str = "categorized_transactions.csv"):
    print(f"Loading transactions from {csv_path}...")
    df = load_transactions(csv_path)
    print(f"Loaded {len(df)} transactions.")
    
    # Prepare transactions as list of dicts
    transactions = df.to_dict(orient='records')
    
    # Process in batches
    all_categories = []
    for i in range(0, len(transactions), BATCH_SIZE):
        batch = transactions[i:i + BATCH_SIZE]
        print(f"Categorizing batch {i//BATCH_SIZE + 1} ({len(batch)} transactions)...")
        
        results = categorize_batch(batch)
        all_categories.extend(results)
    
    # Map categories back to the DataFrame
    category_map = {item['index']: item['category'] for item in all_categories}
    df['category'] = df.index.map(category_map)
    
    # Fill any gaps
    df['category'] = df['category'].fillna('Other')
    
    # Validate categories
    df['category'] = df['category'].apply(
        lambda x: x if x in CATEGORIES else 'Other'
    )
    
    # Save
    df.to_csv(output_path, index=False)
    print(f"Saved categorized transactions to {output_path}")
    
    # Print summary
    print("\nCategory breakdown:")
    print(df['category'].value_counts().to_string())
    
    return df

if __name__ == "__main__":
    import sys
    if len(sys.argv) < 2:
        print("Usage: python main.py path/to/transactions.csv")
        sys.exit(1)
    run(sys.argv[1])

Run it:

python main.py ~/Downloads/checking_account.csv

You’ll see progress logs for each batch, then a summary. The output CSV has your original columns plus a category column.

Step 6: Add a Streamlit UI (Optional)

For a more interactive experience, wrap the logic in a Streamlit app. Drop this in app.py:

# app.py
import streamlit as st
import pandas as pd
from main import run
import tempfile
import os

st.set_page_config(page_title="Finance Categorizer", layout="wide")
st.title("Personal Finance Categorizer")
st.markdown("Upload a bank CSV and categorize transactions with a local LLM.")

uploaded_file = st.file_uploader("Choose a CSV file", type="csv")

if uploaded_file:
    # Save to temp file
    with tempfile.NamedTemporaryFile(delete=False, suffix='.csv') as tmp:
        tmp.write(uploaded_file.getvalue())
        tmp_path = tmp.name
    
    with st.spinner("Categorizing transactions..."):
        df = run(tmp_path)
    
    os.unlink(tmp_path)
    
    st.success(f"Categorized {len(df)} transactions!")
    
    # Filters
    categories = df['category'].unique()
    selected_cats = st.multiselect("Filter by category", categories, default=categories)
    
    filtered_df = df[df['category'].isin(selected_cats)]
    
    # Summary metrics
    col1, col2, col3 = st.columns(3)
    with col1:
        st.metric("Total Inflow", f"${filtered_df[filtered_df['amount'] > 0]['amount'].sum():,.2f}")
    with col2:
        st.metric("Total Outflow", f"${abs(filtered_df[filtered_df['amount'] < 0]['amount'].sum()):,.2f}")
    with col3:
        st.metric("Net", f"${filtered_df['amount'].sum():,.2f}")
    
    # Data table
    st.dataframe(filtered_df, use_container_width=True)
    
    # Download button
    csv = filtered_df.to_csv(index=False).encode('utf-8')
    st.download_button("Download Categorized CSV", csv, "categorized.csv", "text/csv")

Launch it:

streamlit run app.py

Extensions to Make It Production-Grade

Once the basic pipeline works, here’s where you can take it:

1. Multi-account support: Add a --account flag and track which CSV came from which account. Merge them into a unified view.
2. Historical learning: Store past categorizations in SQLite. If the same merchant appears again, skip the LLM call and reuse the cached category.
3. Spending alerts: Add a rules engine that flags transactions over a threshold or unusual categories. Send a desktop notification via plyer.
4. Export to budgeting tools: Output a format compatible with YNAB, Monarch, or Google Sheets.
5. Custom category hierarchies: Let users define subcategories (e.g., Food > Groceries vs Food > Dining Out) and adjust the prompt accordingly.

If you enjoy wiring up local AI agents like this, you’ll probably get a lot out of our guide on Build a YouTube-to-Blog Repurposing Agent Using Whisper and Gemini Free Tier, which chains multiple free models together for content workflows.

Common Pitfalls and How to Avoid Them

Ollama returns garbled JSON. Some models (especially smaller quants) struggle with strict JSON output. Mitigation: use "format": "json" in the Ollama payload (requires Ollama 0.1.8+). Add a fallback that retries once with a stronger prompt like “You MUST return valid JSON only.” If it still fails, mark the batch as Other and move on—don’t let one bad batch kill the whole run.

CSV column mismatches. Every bank formats exports differently. Chase uses “Posting Date,” Wells Fargo uses “Date,” some European banks use semicolons instead of commas. Solution: print the detected columns before parsing and let the user map them interactively. Our column_map in Step 3 covers 80% of cases.

Running out of RAM. Mistral 7B uses ~4GB. If you’re on an 8GB machine and also running a browser, you might hit swap. Close Chrome, or use a smaller model like phi3:mini.

Slow categorization on large CSVs. 500 transactions at 15/batch = 34 API calls. Each call takes 2-5 seconds on CPU. That’s 2-3 minutes total. Acceptable for monthly runs. If you have 5,000 transactions, add the SQLite cache mentioned in Extensions.

FAQ

Q: Does this work on Apple Silicon? Yes. Ollama has native Metal support. Pull mistral or llama3:8b and you’ll get GPU acceleration out of the box.

Q: Can I use this for business expense tracking? Absolutely. Add categories like Client Entertainment, Office Supplies, and Travel - Client. Adjust the CATEGORIES list and the system prompt accordingly.

Q: What if my bank CSV has non-English descriptions? Mistral and Llama 3 are multilingual. Add a line to the system prompt: “Descriptions may be in Spanish, French, or German. Categorize based on meaning, not language.”

Q: How do I fix “Connection refused” errors? Make sure Ollama is running: ollama serve in a terminal. On Linux, it might be a systemd service: systemctl start ollama.

Q: Is my financial data safe? Yes. Everything runs locally. The CSV never leaves your machine. Ollama does not phone home. This is one of the strongest arguments for local LLMs over cloud APIs.

If you’re thinking about building more local AI tools, our breakdown of The Highest-Leverage Skills for an FDE in the AI Era: Beyond Prompt Engineering covers the engineering mindset that turns weekend prototypes into career accelerators.