Consolidate Employee Expense Claims into a Report

Each month, every employee who travelled fills in the expense claim template — name, employee number and cost centre at the top, a table of receipts below — and emails it to finance. Someone then opens thirty workbooks, copies the lines into a master sheet, converts the euro and dollar receipts, checks each line against the expenses policy, and totals it all up for payroll. It is slow, it is where copy-paste errors creep in, and the policy checks are the first thing skipped when the deadline is close. A Python script can do all of it in seconds: read every form, combine the lines, convert currencies, apply the policy rules consistently, and produce a report with the totals to reimburse and a list of exceptions for the approver. It belongs to Building Finance and Operations Reports.

Prerequisites

pip install pandas openpyxl

The claims/ folder holds one form per employee in the company template: the claimant's name, employee ID and cost centre in cells B2–B4, and from row 7 a table with date, category, description, amount, currency and whether a receipt is attached (Y/N). The policy used below is an example; replace its numbers with your own.

Step 1 — Read every form: header cells and the claim table

The form mixes two shapes: single values in fixed cells at the top and a table underneath. Read the header with openpyxl and the table with pandas, then attach the header values to every line:

from pathlib import Path
import pandas as pd
from openpyxl import load_workbook

HEADER_CELLS = {"Employee": "B2", "Employee ID": "B3", "Cost centre": "B4"}

def read_claim(path):
    wb = load_workbook(path, read_only=True, data_only=True)
    ws = wb["Claim"]
    header = {k: ws[cell].value for k, cell in HEADER_CELLS.items()}
    wb.close()
    lines = pd.read_excel(path, sheet_name="Claim", header=6, dtype={"Receipt": str})
    lines = lines.dropna(how="all")
    return lines.assign(**header, Source=path.name)

claims = pd.concat([read_claim(p) for p in sorted(Path("claims").glob("*.xlsx"))], ignore_index=True)
claims["Date"] = pd.to_datetime(claims["Date"])
print(len(claims), "lines from", claims["Source"].nunique(), "forms")
print(claims[["Employee", "Date", "Category", "Amount", "Currency"]].head(3).to_string(index=False))

header=6 tells pandas the column headings are on row 7 (counted from zero), skipping the header block above. dropna(how="all") removes blank rows at the end of a form, which templates often have. Keeping the source file name on each line means every exception in the report can be traced back to the form it came from. If some forms might be on an older template, check the heading row matches the expected columns before reading — Find the Header Row in an Excel Sheet with a Shifting Layout covers locating headers dynamically.

Step 2 — Convert to the reporting currency

Claims in euros and dollars are reimbursed in pounds at a rate set by finance for the month. Keep the rates in a small table and convert with a merge, so a missing rate is caught rather than silently producing NaN:

rates = pd.DataFrame({"Currency": ["GBP", "EUR", "USD"], "Rate to GBP": [1.0, 0.86, 0.75]})
claims = claims.merge(rates, on="Currency", how="left", validate="many_to_one")
missing = claims.loc[claims["Rate to GBP"].isna(), "Currency"].unique()
if len(missing):
    raise ValueError(f"no rate for {list(missing)}")
claims["GBP"] = (claims["Amount"] * claims["Rate to GBP"]).round(2)
print(claims.groupby("Currency")["GBP"].sum().round(2).to_dict())

validate="many_to_one" makes pandas raise an error if the rate table accidentally lists a currency twice, which would otherwise duplicate claim lines. Rounding each converted line to pennies before totalling matches how the payment will be made. For daily or transaction-date rates rather than one rate per month, use an as-of merge as in Convert Multi-Currency Transactions with an FX Rate Table.

Step 3 — Apply the expenses policy

The policy rules become vectorised checks, each producing a readable reason. Lines can fail several rules, and every reason is kept:

POLICY = {
    "limits": {"Meals": 75.00, "Hotel": 180.00, "Taxi": 60.00},   # per line, GBP
    "receipt_over": 25.00,
    "allowed": {"Travel", "Meals", "Hotel", "Taxi"},
}

checks = {
    "category not allowed": ~claims["Category"].isin(POLICY["allowed"]),
    "over category limit": claims["GBP"] > claims["Category"].map(POLICY["limits"]).fillna(float("inf")),
    "receipt missing": (claims["Receipt"].str.upper() != "Y") & (claims["GBP"] > POLICY["receipt_over"]),
    "possible duplicate": claims.duplicated(["Employee ID", "Date", "Category", "Amount", "Currency"], keep=False),
    "weekend": claims["Date"].dt.dayofweek >= 5,
}
claims["Issues"] = [
    "; ".join(name for name, mask in checks.items() if mask.iloc[i]) for i in range(len(claims))
]
flagged = claims[claims["Issues"] != ""]
print(flagged[["Employee", "Date", "Category", "GBP", "Issues"]].to_string(index=False))

The rules are deliberately simple and transparent, and the run shows what each one catches: Ana's £142 client dinner is over the meal limit, receipts are missing for Ben's dinner and Chloé's mileage, Chloé's online course appears twice and is not an allowed category, and two meals fall on Saturdays. Ben's Lyon hotel is flagged too — at £266.60 it is over the £180 limit, but it covers two nights, so it is well within a nightly limit. That is a rule-design finding rather than a policy breach: the form should record the number of nights so the check can be per night. A flag is not a rejection — the weekend lunch may be perfectly legitimate — but it routes the line to a human instead of straight to payroll. Keeping the policy as data in one dictionary makes the annual policy update a one-line change, and the same table can be printed into the report so everyone sees which rules were applied.

Step 4 — Summarise for approval and payment

Approvers want a line per employee; finance wants the cost by cost centre and category. Lines with issues are held back from the amount to pay until someone approves them:

claims["Status"] = claims["Issues"].map(lambda s: "Review" if s else "OK")
by_employee = (claims.pivot_table(index=["Employee ID", "Employee", "Cost centre"], columns="Status",
                                  values="GBP", aggfunc="sum", fill_value=0)
                     .reindex(columns=["OK", "Review"], fill_value=0)
                     .rename(columns={"OK": "Payable now", "Review": "Held for review"})
                     .reset_index())
by_cc = claims.pivot_table(index="Cost centre", columns="Category", values="GBP", aggfunc="sum",
                           fill_value=0, margins=True, margins_name="Total")
print(by_employee.to_string(index=False))
print(by_cc.round(2).to_string())

Each employee's total is split between what can be paid now and what is held. The cost-centre table, with margins=True adding row and column totals, is what the budget owners see. Both are computed from the same converted lines, so they add up to the same grand total — a check worth asserting in the script.

Step 5 — Write the report

The workbook has the summary first, then the exceptions the approver must look at, then every line for reference:

from openpyxl.styles import Font, PatternFill

with pd.ExcelWriter("expense_report_2026-09.xlsx", engine="openpyxl") as xw:
    by_employee.to_excel(xw, sheet_name="By employee", index=False)
    flagged[["Employee", "Source", "Date", "Category", "Description", "Amount", "Currency", "GBP", "Issues"]] \
        .to_excel(xw, sheet_name="Exceptions", index=False)
    by_cc.round(2).to_excel(xw, sheet_name="By cost centre")
    claims.drop(columns=["Rate to GBP"]).to_excel(xw, sheet_name="All lines", index=False)
    pd.DataFrame([(k, str(v)) for k, v in POLICY.items()], columns=["Rule", "Setting"]) \
        .to_excel(xw, sheet_name="Policy", index=False)
    for ws in xw.book.worksheets:
        for cell in ws[1]:
            cell.font = Font(bold=True)
        ws.freeze_panes = "A2"
        for col in ws.columns:
            ws.column_dimensions[col[0].column_letter].width = max(12, min(40, max(len(str(c.value or "")) for c in col) + 2))
    for row in xw.book["Exceptions"].iter_rows(min_row=2):
        row[-1].fill = PatternFill("solid", fgColor="FFEB9C")

total = claims["GBP"].sum()
print(f"total claimed £{total:,.2f} = payable £{by_employee['Payable now'].sum():,.2f} + held £{by_employee['Held for review'].sum():,.2f}")

The Exceptions sheet lists each flagged line with the form it came from, the original amount and currency, and the reasons in a highlighted column — the approver's to-do list. The Policy sheet records the rules in force, so a report opened in six months still explains why a line was held. The final line proves the split adds up: what is payable now plus what is held equals everything claimed. Emailing the report to approvers is covered in Send Each Recipient Their Own Excel Report.

Common pitfalls

• Reading the table without skipping the header block. Set header= to the heading row.
• Silent NaN from a missing exchange rate. Merge with a rate table and fail on gaps.
• Rejecting instead of flagging. Policy checks route lines for review; people decide.
• Hard-coded limits scattered through the code. Keep the policy in one table and print it in the report.
• Totals that don't reconcile. Assert payable plus held equals the total claimed.

Conclusion

Consolidating expense claims is a read-combine-check-summarise job: read the header cells and claim table from every form, convert foreign-currency lines with a validated rate table, apply the expenses policy as vectorised checks that produce readable reasons, and split each employee's total into payable now and held for review. A report with a summary, an exceptions sheet, every line and the policy in force gives approvers what they need and finance an auditable record.

Frequently asked questions

How do I combine many Excel expense forms into one table? Loop over the files, read the fixed header cells with openpyxl and the claim table with pandas read_excel using the header row, add the header values and file name as columns, and concatenate the results.

How do I check expense claims against a policy in Python? Express each rule as a boolean mask on the DataFrame, such as amount above a category limit or no receipt above a threshold, and join the names of the failing rules into an issues column.

How do I find duplicate expense claims? Use duplicated on employee, date, category, amount and currency with keep=False, which marks every line in a group of identical claims.

How do I convert expense claims to one currency? Merge a rate table on the currency column with validate many_to_one, raise an error if any rate is missing, and multiply the amount by the rate, rounding each line.

Related

• Up one level: Building Finance and Operations Reports — finance and operations reports in Excel.
• Consolidate Timesheets from Many Workbooks — the same pattern for hours.
• Combine Multiple Excel Files into One — the basic folder merge.
• Build a Trial Balance from a General Ledger — where reimbursed expenses end up.