Beru Learning

Transaction exports from banks rarely display recognizable store names. Descriptions often include point-of-sale terminal IDs, processor codes, store numbers, and internal routing tags. Importing these strings into a spreadsheet creates duplicate merchant records and messy categories.

You can clean bank CSV transactions using a two-stage process: filter out repetitive artifacts with regular expressions first, then let an offline language model categorize the cleaned merchant names without sharing data externally.

The Two-Tier Architecture: Deterministic First, AI Second

Using a language model to process thousands of raw CSV lines is slow and prone to formatting inconsistencies. A combined workflow works better:

Tier 1: Regular Expressions (Regex): Quickly removes predictable noise, including merchant codes, phone numbers, and payment gateway prefixes (like "SQ ", "TST", or "PAYPAL *").
Tier 2: Lightweight Local LLM: Evaluates the cleaned business names and assigns them to standard budget buckets, such as groceries, utilities, or dining.

Tier 1: Stripping Merchant Noise with Regex

Before categorizing names with an AI tool, run a simple regex script in Python or your spreadsheet editor to strip point-of-sale artifacts:

import re  def clean_merchant_string(raw_text):     # Remove common processor prefixes     text = re.sub(r'^(SQ	en|TST	en|PAYPAL	en|	en)', '', raw_text)     # Remove store numbers and sequential digits     text = re.sub(r'#	en+|	en{3,}', '', text)     # Remove trailing city/state references (e.g., NY 10001)     text = re.sub(r'[A-Z]{2}	en+', '', text)     # Collapse multiple spaces     return text.strip()

This step reduces strings like SQ *HARBOR COFFEE ROAST #4928 NEW YORK NY down to HARBOR COFFEE ROAST.

Tier 2: The Categorization Prompt for Local Models

After cleaning the raw text, send the unique merchant names to a local model (such as Llama 3 8B or Mistral 7B) using this structured JSON prompt:

You are a financial classification assistant. Normalize each merchant and map it to a standard budget category. Output ONLY valid JSON array with no conversational commentary.  Allowed Categories: Dining, Groceries, Utilities, Transport, Subscriptions, Retail.  Input List: ["HARBOR COFFEE ROAST", "AMZN MKTPLACE", "SHELL OIL"]  Output Format: [   {"raw": "HARBOR COFFEE ROAST", "clean": "Harbor Coffee", "category": "Dining"} ]

Because the output is restricted to JSON, the resulting table can be imported straight into your budgeting spreadsheet without manual cleanup or cloud exposure.

FAQ

Can I run this workflow entirely inside Microsoft Excel or Google Sheets?

Yes. You can use spreadsheet regex functions like REGEXREPLACE to strip prefixes and store codes, then export the cleaned merchant list for classification.

Why not let the LLM do the regex stripping work as well?

Regex handles pattern matching in microseconds with complete predictability. Using an LLM for simple text replacement uses unnecessary compute and risks unexpected text changes.

Is my financial privacy protected with this workflow?

Yes. Running both the script and the language model on your own device ensures that account numbers, dates, and spending records remain offline.

Key Takeaways

Bank CSV files contain point-of-sale prefixes, store codes, and system tags.
A two-tier setup uses regex for text cleanup and an LLM for categorization.
Regex strips predictable processor codes quickly and reliably.
Limiting model outputs to JSON makes spreadsheet imports straightforward.
Local processing keeps your transaction history off third-party servers.

Cleaning Unstructured Bank CSV Exports with Local Regex and Lightweight LLMs