Automating Team Expense Reports with Python

In many small teams, each employee submits their monthly expenses as a separate Excel file. Someone — usually whoever handles approvals — then has to open every single file and manually copy the rows into one master sheet before anything can be reviewed.

It's slow, and it doesn't scale past a handful of people.

I built expense-tool, a small Python CLI that automates this: point it at a folder of expense files, and it produces one clean, aggregated summary.

Why this problem

The usual alternatives don't quite fit for a lot of teams:

• Excel macros work, but require someone comfortable writing VBA to set up and maintain.
• Power Query is powerful, but the setup isn't intuitive if you're not already familiar with Excel's data tools.
• No-code automation platforms (Zapier, Make) are great for connecting services, but are often overkill — and an extra subscription — for what is fundamentally a "read some files, sum some numbers" task.

Meanwhile, the actual manual process (open each file, copy rows, paste into a master sheet) is exactly the kind of repetitive task that's easy to automate with a short script, once the shape of the problem is well-defined.

What it does

expense-tool runs as a single CLI command and does three things in order:

1. Combine — reads every Excel file in a folder and merges them into one table.
2. Aggregate — sums expense amounts by employee and category.
3. Format — writes the result to a new Excel file with a bold header row, and highlights any row above a configurable amount threshold.

python expense_tool.py sample_data

Summary written to expense_summary.xlsx

Employee	Category	Amount
john	Meals	68.00
john	Office Supplies	15.30
john	Travel	42.50
sarah	Meals	145.00
sarah	Software	29.99
sarah	Travel	320.00

Rows above the threshold (sarah's Meals and Travel entries here) are highlighted in the output file, so whoever reviews the summary can spot larger expenses at a glance.

Design decisions

A few choices shaped how the tool is put together — and why.

One file, three functions

At this scale — three features — splitting the project across multiple files would have added structure without adding much clarity. Instead, expense_tool.py has one function per feature:

• load_expense_files — reads and merges the input files
• aggregate_expenses — groups and sums
• write_formatted_excel — writes the styled output

A single Typer command (run) calls the three in order: load → aggregate → write. If the tool grows — more input formats, more output options — splitting into modules becomes worth the added structure. For now, one file stays easy to read top to bottom.

Employee name comes from the filename, not a column

Since each file represents one employee's report, adding an "Employee" column inside every file would just be repeating the same value on every row. Instead, load_expense_files derives the employee name from each file's name (john.xlsx → "john") and adds it as a column only after merging:

for file_path in folder_path.glob("*.xlsx"):
    df = pd.read_excel(file_path)
    employee_name = file_path.stem
    df["Employee"] = employee_name
    dataframes.append(df)

This keeps each individual file simple — no extra column employees have to fill in correctly — while still giving the merged table everything it needs for aggregation.

pandas for data, openpyxl for style

write_formatted_excel uses pd.ExcelWriter with engine="openpyxl", so pandas handles writing the data and openpyxl handles the formatting on the same file afterward:

with pd.ExcelWriter(output_path, engine="openpyxl") as writer:
    df.to_excel(writer, index=False, sheet_name="Summary")
    worksheet = writer.sheets["Summary"]
    # openpyxl styling happens here, on the same worksheet

This keeps the data-processing logic (grouping, summing) written the simple pandas way, and treats formatting as a separate, later step — rather than mixing styling logic into the aggregation code.

Highlighting is a plain loop over rows

For highlighting rows above the threshold, there's no need for anything more elaborate than checking each row's Amount and applying a PatternFill when it exceeds the limit:

for row_index, amount in enumerate(df["Amount"], start=2):
    if amount > highlight_threshold:
        for col_index in range(1, len(df.columns) + 1):
            worksheet.cell(row=row_index, column=col_index).fill = highlight_fill

highlight_threshold is a parameter with a default value, so it's easy to adjust for a team with a different sense of what counts as a "large" expense.

Trying it yourself

git clone https://github.com/meiglyph/expense-tool.git
cd expense-tool
pip install pandas openpyxl typer
python expense_tool.py <input_folder>

Each input file just needs the columns: Date, Category, Description, Amount, Payee, Notes. Full details are in the README.

What's next

The current version assumes every input file shares the same columns — a reasonable assumption inside a single team with a shared template, but not something to take for granted more broadly. A natural next step would be adding an optional department-level breakdown, or making the highlight threshold configurable from the command line instead of the function signature.

For now, the core loop — combine, aggregate, format — does exactly what it needs to.