How to Use Claude to Categorise and Clean Up Expense Data

Claude can do in minutes what takes finance teams hours, turning messy, inconsistent expense exports into clean, categorised, analysis-ready data. This guide shows you exactly how to do it, with prompt templates you can use today.

Previously in this series

Previously in this series: How to Use Claude to Extract Line Items from Scanned Invoices.

Why expense data is a spreadsheet problem

Every finance team has the same spreadsheet problem. The expense export comes out of the ERP or expense management system looking like it was formatted by twelve different people using twelve different naming conventions, because it was. "Amazon Web Services" and "AWS" and "Amazon WS" are the same vendor. "Travel - Flights" and "Air Travel" and "Flights - Domestic" are the same category. "Mktg" and "Marketing Dept" and "MKT" are the same department. Before you can do anything useful with the data, analyse spend by category, compare month-on-month trends, identify anomalies, you have to clean it. And cleaning it manually, in Excel, takes hours. It requires judgment calls on hundreds of rows. And the next month, you do it all over again. Claude can handle most of this work. Not because it is magic, but because categorisation and normalisation are exactly the kind of pattern-matching, context-aware tasks that large language models do well. You describe what you need, paste in the data, and Claude applies consistent rules across every row. Here is exactly how to do it.

What Claude can do with expense data

Before the prompts, be clear about what Claude handles well and where it needs human oversight.

Handles well

• Standardising vendor names, collapsing variations into a single canonical form
• Categorising transactions by expense type based on vendor name, description, and amount
• Assigning GL codes or cost centre codes based on configured rules
• Flagging suspicious or anomalous transactions for review
• Identifying likely duplicates based on vendor, amount, and date proximity
• Reformatting inconsistent date, currency, and amount fields
• Generating a clean output CSV ready to import or analyse

Needs human oversight

• Ambiguous transactions where the category is not clear from the data alone
• New vendors with no prior transaction history to infer from
• Policy compliance decisions, Claude can flag, but a human should decide
• Any transaction above a defined materiality threshold

With that established, here are the prompts.

Each step in the workflow has a dedicated prompt designed to be copied, pasted, and adapted to your category structure.

Step 1: Prepare your data export

Before pasting anything into Claude, spend five minutes on your export. Claude works better with consistent input.

Fields to include

• Transaction date
• Vendor / merchant name
• Amount and currency
• Description or memo (if available)
• Submitter name or employee ID
• Department or cost centre (if already populated, even partially)
• Current category (if any, even if inconsistent)
• Expense report or receipt reference number

Format, size, and chunking

Format: CSV or a clean copy-paste from Excel. Claude reads both reliably. Size: Claude can handle several hundred rows in a single conversation. For very large datasets, thousands of rows, chunk the data by month or department and run separate sessions.

Step 2: The vendor normalisation prompt

This is the first step, standardising vendor names before you do anything else. Inconsistent vendor names are the root cause of most spend analysis problems.

I'm going to paste expense transaction data below. I need you to standardise the vendor names.

Rules:
- Identify all variations of the same vendor and consolidate them to a single canonical name
- Use the most formal/complete version of the vendor name as the canonical form
- Examples of what to consolidate:
  "AWS", "Amazon Web Services", "Amazon WS" → "Amazon Web Services"
  "AMZN", "Amazon.com", "Amazon" → "Amazon"
  "MSFT", "Microsoft", "Microsoft Corp" → "Microsoft"
  "Uber*trip", "Uber Technologies", "UBER" → "Uber"
- If you're uncertain about two vendors (might be the same, might be different), flag them with a note rather than merging them
- Return the data in the same format I gave you, but with a new column called "Vendor_Standardised" added
- After the data, add a summary table showing all the consolidations you made (original → canonical)

Here is the data:

[paste your data here]

Why this prompt works

Run this first. The clean vendor names become the foundation everything else is built on.

Step 3: The categorisation prompt

Once vendor names are standardised, categorise each transaction. The key to getting this right is giving Claude your specific category structure rather than asking it to invent one.

I'm going to paste expense transaction data with standardised vendor names. I need you to assign a category to each transaction.

Use ONLY the following expense categories:

SOFTWARE & SAAS: cloud software, SaaS subscriptions, app store purchases, developer tools
TRAVEL: flights, hotels, trains, car hire, Airbnb for business travel
MEALS & ENTERTAINMENT: restaurants, coffee, food delivery, team lunches, client dinners
TRANSPORT: Uber, Lyft, taxis, local transport (not long-distance travel)
MARKETING: advertising platforms, design tools, marketing software, sponsored content
OFFICE & SUPPLIES: stationery, equipment, office furniture, printing, shipping
PROFESSIONAL SERVICES: legal, accounting, consulting, freelancer, contractors
INFRASTRUCTURE & HOSTING: cloud computing, servers, data storage, domain names, CDN
TELECOMS: phone bills, internet, mobile plans, video conferencing subscriptions
TRAINING & DEVELOPMENT: online courses, conferences, books, certifications
UNKNOWN: use this when you cannot determine the category from the available information

For each transaction:
1. Assign the most appropriate category from the list above
2. Add a confidence score: HIGH, MEDIUM, or LOW
3. For LOW confidence, add a one-line note explaining why

Add columns: Category, Confidence, Notes

Return the full dataset with these columns added. After the data, list all LOW confidence transactions separately for my review.

Here is the data:

[paste your standardised data here]

Why confidence scoring matters

The confidence score is important, it tells you exactly which rows need human review rather than making you check everything.

Step 4: GL code assignment

If your categorisation maps to specific GL codes, add this step after categorisation. Give Claude the mapping and it applies it consistently.

I have categorised expense data. I need you to assign GL codes based on the following mapping:

SOFTWARE & SAAS → 6100
TRAVEL → 6200
MEALS & ENTERTAINMENT → 6300
TRANSPORT → 6310
MARKETING → 6400
OFFICE & SUPPLIES → 6500
PROFESSIONAL SERVICES → 6600
INFRASTRUCTURE & HOSTING → 6110
TELECOMS → 6700
TRAINING & DEVELOPMENT → 6800
UNKNOWN → 9999

Rules:
- Apply the GL code that matches the Category column
- If Category is UNKNOWN, use 9999 and flag the row
- If any transaction looks like it might span multiple GL codes (a single invoice covering both software and professional services, for example), flag it for manual review

Add a column: GL_Code

Return the full dataset with GL_Code added. List all 9999 rows and any flagged multi-code rows separately at the end.

Here is the data:

[paste your categorised data here]

Step 5: The anomaly detection prompt

This is the one most guides skip. After cleaning and categorising, ask Claude to look for things that seem off.

Review this categorised expense dataset and flag any transactions that warrant closer attention.

Look for:

1. POTENTIAL DUPLICATES
   Same vendor, same or very similar amount, within 14 days of each other

2. ROUND NUMBER ANOMALIES
   Transactions for exactly $500, $1,000, $2,500, $5,000 etc., round numbers in expense data can indicate estimates or fabricated entries

3. WEEKEND/HOLIDAY TRANSACTIONS
   Any transactions dated on a Saturday or Sunday that are not in a category where weekend spend is expected (travel, meals are fine, software subscriptions on a Sunday warrant a note)

4. UNUSUALLY HIGH AMOUNTS
   Any single transaction that is more than 3x the average transaction value for that category

5. CATEGORY MISMATCHES
   Any vendor that seems to be in an unexpected category based on its name (e.g. a restaurant categorised as Office Supplies)

For each flag:
- Transaction details (date, vendor, amount)
- Flag type (from the list above)
- One-line explanation of why it was flagged
- Recommended action: REVIEW / QUERY VENDOR / APPROVE AS-IS

Return the full anomaly list as a table.

Here is the data:

[paste your categorised data here]

Step 6: The clean output prompt

Once you have run the categorisation, GL assignment, and anomaly review, and handled any flagged items manually, ask Claude to produce the final clean output.

I have completed my review of the flagged transactions. Here are the resolutions:

[paste your manual decisions here, e.g. "Transaction 47: confirmed duplicate, remove. Transaction 82: reclassify from UNKNOWN to SOFTWARE & SAAS. Transaction 103: approved as legitimate, keep."]

Now produce the final clean version of the dataset with:

1. All confirmed duplicates removed
2. All reclassifications applied
3. All columns in this order:
   Date | Vendor_Standardised | Amount | Currency | Department | Category | GL_Code | Submitter | Reference | Notes
4. A summary table at the end showing:
   - Total transactions (before and after cleaning)
   - Total spend by category
   - Number of transactions removed (duplicates)
   - Number of transactions reclassified
   - Number of transactions flagged for further review (not yet resolved)

Format the main dataset as a clean CSV I can copy directly into Excel or import into my ERP.

Here is the data with my resolutions applied:

[paste final data here]

Making it repeatable: the monthly process

The real value is not running this once. It is building a consistent monthly process that takes 30 minutes instead of three hours.

The setup (do once)

Create a Claude project or a saved conversation template that contains:

• Your vendor normalisation rules, a growing list of "these are all the same vendor" pairs that you add to each month as new variations appear
• Your category structure, the exact list of categories you use, so you never have to re-explain them
• Your GL code mapping, once it is right, it is right
• Your anomaly detection thresholds, adjust as you learn what is normal for your operation

The monthly run

Export the month's expenses. Paste. Run the five prompts in sequence. Review the flagged items (typically 5 to 15% of transactions). Apply your decisions. Export the clean CSV. Once you have done it twice, the whole process, including manual review of flagged items, takes under 45 minutes for most mid-market AP teams.

Advanced: combining with spend analysis

Once the data is clean, the same conversation can shift directly into analysis. Add this prompt after your clean output is ready:

Now that the data is clean and categorised, give me a spend analysis covering:

1. Total spend by category this month, table, sorted by amount descending
2. Top 10 vendors by total spend
3. Month-over-month comparison (if I paste last month's clean data below, compare the two):
   - Categories that increased significantly
   - Categories that decreased significantly
   - New vendors this month that weren't present last month
   - Vendors that appeared last month but not this month
4. Any category where spend is tracking to exceed a monthly average (I'll define 'average' as the mean of the last 3 months of data if you have it)
5. A 150-word plain-English summary suitable for sharing with the CFO

[paste last month's clean data if available]

What Claude cannot do here

As with all the tools in this series, being clear about the limits matters. Claude works on data you give it in the conversation. It does not connect to your ERP, your expense management system, or any live data source. Every month is a fresh session unless you maintain a project with the context built in. Claude applies rules you define. It does not know your company's specific expense policy unless you tell it. If your policy says meals over $75 per person need CFO approval, Claude will not know that unless the prompt includes it. And Claude categorises based on pattern recognition. For genuinely ambiguous transactions, a vendor you have never seen before, an unusual amount with no description, it will flag LOW confidence correctly. Those need a human.

Where this stops and where Blackbee AI picks up

For live, continuous expense categorisation that applies your policies automatically at the point of submission, without a monthly manual export-and-paste process, that requires a system with native ERP integration and autonomous agent reasoning. That is what Blackbee AI's Invoice Processing Agent and Contract Intelligence Agent do within the full Intake-to-Pay cycle. But for today, the prompts above give any finance team a significantly cleaner expense dataset with about a third of the usual effort.