Arron Bennett, Founder of Bennett Financials

If you run a small business, or write code for one, you've probably seen this: a year of card and bank statements, exported as CSVs, with descriptions like FACEBK ADS 4R2X and GOOGLE *GSUITE. Somebody needs to know where the money went.

Accounting software does this well once it's set up. But a quick script is useful when you're cleaning up old data, checking how the books were categorised, or just want an answer before a meeting. Here's a small pandas script that merges card and bank exports, categorises spending with simple rules, flags what it can't place, and shows each group as a share of money coming in.

Key takeaways

  • A short list of regex rules can categorise most small-business spending, because the same vendors repeat every month.
  • Group categories into three P&L buckets: cost of revenue, sales and marketing, and overhead.
  • Always print what didn't match. The leftovers tell you which rules to add.
  • The output is a quick check, not a set of books. Payroll and other items often live outside card and bank exports.

Why categorise spending yourself?

Categorising spending yourself is useful when you inherit messy data, want to check that existing books are categorised sensibly, or need a fast answer about where money is going. A script is repeatable, so you can rerun it every month. It also forces you to decide which costs belong to delivery, to winning customers and to running the business.

The data

The example uses a hypothetical company: a 10-person e-learning business selling continuing education courses for nurses, with about $1.3 million in revenue. It has 14 months of exports from one card account and one bank account. The data is a synthetic sample made for this post.

The two files have different column names, which is normal:

card_2025_2026.csv: Transaction Date, Description, Amount bank_2025_2026.csv: Date, Details, Debit, Credit

The script maps both to one shape: date, description, amount, with spending positive and money in negative.

The code 

import re from pathlib import Path import pandas as pd # pattern -> (category, P&L bucket). First match wins, so put specific rules first. RULES = [ (r"GOOGLE ADS|FACEBK ADS", "Advertising", "Sales & marketing"), (r"CANVA|ADOBE", "Design tools", "Sales & marketing"), (r"AWS|KAJABI", "Course hosting", "Cost of revenue"), (r"NCSBN|CE APPROVAL", "Accreditation fees", "Cost of revenue"), (r"ZOOM|GSUITE|GOOGLE \*", "Software", "Overhead"), (r"GUSTO", "Payroll fees", "Overhead"), (r"RENT|WEWORK", "Rent", "Overhead"), (r"INSURANCE", "Insurance", "Overhead"), (r"STAPLES", "Office supplies", "Overhead"), (r"UBER|DELTA|AIR ", "Travel", "Overhead"), ] def load_card(path): df = pd.read_csv(path) return pd.DataFrame({"date": df["Transaction Date"], "description": df["Description"], "amount": df["Amount"]}) def load_bank(path): df = pd.read_csv(path) out = pd.DataFrame({"date": df["Date"], "description": df["Details"], "amount": df["Debit"].fillna(0) - df["Credit"].fillna(0)}) return out # spending positive, money in negative def categorize(desc): for pattern, category, bucket in RULES: if re.search(pattern, desc, re.IGNORECASE): return category, bucket return "UNCATEGORIZED", "Review" def main(folder="statements"): frames = [] for path in Path(folder).glob("*.csv"): loader = load_card if path.name.startswith("card") else load_bank frames.append(loader(path)) tx = pd.concat(frames, ignore_index=True) tx["date"] = pd.to_datetime(tx["date"]) tx = tx[tx["date"] >= tx["date"].max() - pd.DateOffset(months=12)] # last 12 months revenue = -tx.loc[tx["amount"] < 0, "amount"].sum() spend = tx[tx["amount"] > 0].copy() spend[["category", "bucket"]] = spend["description"].apply(lambda d: pd.Series(categorize(d))) todo = spend[spend["bucket"] == "Review"] print(f"{len(spend)} spending lines, {len(todo)} need a rule:") print(todo["description"].value_counts().head(5).to_string(), "\n") by_cat = spend.groupby(["bucket", "category"])["amount"].sum().sort_values(ascending=False) print(by_cat.round(0).to_string(), "\n") print(f"Money in (12 months): ${revenue:,.0f}") for bucket, total in spend.groupby("bucket")["amount"].sum().items(): print(f"{bucket:>18}: ${total:>9,.0f} {total / revenue:5.1%} of money in") if __name__ == "__main__": main()

How do the rules work?

Each rule is a regular expression, a category and a bucket. The script checks rules in order and uses the first one that matches, so put specific patterns before general ones. GOOGLE ADS has to come before a general GOOGLE pattern, or ad spend ends up under software. Anything that matches nothing goes to a Review bucket.

For most small businesses, 20 to 40 rules cover nearly everything, because the same vendors appear every month.

How do you choose the buckets?

Choose buckets by asking what each cost is for. Cost of revenue is what it takes to deliver what customers paid for. Sales and marketing is what it takes to win new customers. Overhead is what it takes to run the business regardless of sales. Every category should land in exactly one bucket, and the same vendor should always land in the same place.

For the e-learning company, that meant:

  • Cost of revenue: the course platform and video hosting, plus the fees paid to have courses approved for nursing continuing education credit. Without these, there's nothing to sell.
  • Sales and marketing: search and social ads, and the design tools used to make ads and landing pages.
  • Overhead: rent and coworking, insurance, office software, payroll provider fees, supplies and travel.

Some calls are judgment. Design tools could sit in overhead if they're mostly used for internal documents. Pick a rule, write it down in a comment next to the pattern, and apply it consistently. Consistency matters more than the perfect answer, because the trend over time is what you'll actually use.

The output 

267 spending lines, 36 need a rule: description JOE'S DELI 36 bucket category Sales & marketing Advertising 115998.0 Overhead Rent 52800.0 Cost of revenue Course hosting 18333.0 Overhead Travel 5845.0 Insurance 3720.0 Software 3240.0 Office supplies 3114.0 Payroll fees 2478.0 Review UNCATEGORIZED 1783.0 Sales & marketing Design tools 1260.0 Cost of revenue Accreditation fees 900.0 Money in (12 months): $1,246,544 Cost of revenue: $ 19,233 1.5% of money in Overhead: $ 71,197 5.7% of money in Review: $ 1,783 0.1% of money in Sales & marketing: $ 117,258 9.4% of money in

Thirty-six lines need a rule, all from one deli. That's a decision for a person: client meals, team lunches or personal spending that shouldn't be on the company card. The script's job is to surface it, not to guess.

What does this tell you about the business?

The numbers by bucket give a rough first view of where money goes, compared with money coming in. Sales and marketing, mostly ads, is about 9.4% of money in. Overhead in these exports is about 5.7%, and cost of revenue about 1.5%.

Those last two look low because payroll isn't in these files. It's run through a payroll provider, and only the fees show up here. Add salaries and the picture changes a lot. That's the most important caveat with any quick script: know what's not in the data.

In this short clip I make a related point about cutting expenses. A business can cut overhead hard and still be broken if the real problem is gross margin or sales and marketing. Knowing which bucket each cost belongs in is the first step to knowing which problem you actually have.

How do you extend the script?

Extend it by adding rules as the Review list shows new vendors, adding more loaders for other banks, and writing the categorised table to a CSV that a bookkeeper can review. You can also group by month to see trends, such as ad spend creeping up while money in stays flat.

A few useful additions:

  • Monthly view: spend.groupby([spend["date"].dt.to_period("M"), "bucket"])["amount"].sum().unstack()
  • Export for review: spend.to_csv("categorised.csv", index=False)
  • Transfers: add a rule that excludes transfers between your own accounts, so they don't count as spending or income.

What mistakes should you watch for?

The common mistakes are double counting card payments made from the bank account, treating transfers between your own accounts as income or spending, and mixing personal and business spending. Also watch for refunds, which appear as negative amounts and can be mistaken for revenue.

FAQ

Can pandas replace accounting software?

No. It's useful for checks and clean-ups. Proper books need accounting software and someone who maintains them.

How many rules do I need?

Often 20 to 40 for a small business. Add them as the Review list shows new vendors.

Should I use machine learning to categorise transactions?

For a small business, simple rules are usually enough and easier to check.

For more on organising categories so they're useful for decisions and taxes, see Bennett Financials' chart of accounts makeover guide and our overhead research.