Check expense claims against travel policy caps with pandas

Turn the policy document into a table first

Travel policies are written as paragraphs: "Grade G1 employees travelling to metro cities may claim hotel stay up to…". A script cannot read that, and neither can a reviewer checking fifty claims on a Friday evening. Before writing any code, rewrite the numeric parts of the policy as a table with one row per combination that the policy distinguishes, and get the finance or HR owner to confirm it. Everything in the table below is a made-up example, not a recommendation.

Which cities count as "metro" is itself a policy decision and varies between companies; keep that list next to the caps table so it is reviewed together. Add the non-cap rules as named constants: the submission window (30 days here) and the amount above which a bill is required (₹500 here).

The screen

Eight synthetic claim lines from four employees are checked below.

import pandas as pd

# A made-up policy for illustration. Replace every number with your own approved policy.
CAPS = pd.DataFrame({            # daily caps in rupees by grade and city class
    "grade": ["G1", "G1", "G2", "G2"],
    "city_class": ["metro", "other", "metro", "other"],
    "hotel_cap": [4500, 3000, 7000, 5000],
    "meal_cap": [900, 700, 1400, 1000],
})
METRO = {"Mumbai", "Delhi", "Bengaluru", "Chennai", "Kolkata", "Hyderabad"}   # per this policy
SUBMIT_WITHIN_DAYS = 30
BILL_REQUIRED_ABOVE = 500

# Synthetic claim lines.
claims = pd.DataFrame({
    "claim_id": ["C1", "C1", "C1", "C2", "C2", "C3", "C3", "C4"],
    "emp": ["E11", "E11", "E11", "E12", "E12", "E13", "E13", "E14"],
    "grade": ["G1", "G1", "G1", "G2", "G2", "G1", "G1", "G2"],
    "city": ["Mumbai", "Mumbai", "Mumbai", "Lucknow", "Lucknow", "Jaipur", "Jaipur", "Delhi"],
    "category": ["hotel", "meal", "meal", "hotel", "meal", "meal", "meal", "taxi"],
    "expense_date": ["2026-08-03", "2026-08-03", "2026-08-03", "2026-08-20", "2026-08-20",
                     "2026-07-28", "2026-07-28", "2026-09-05"],
    "submitted": ["2026-08-10"] * 3 + ["2026-08-25"] * 2 + ["2026-09-15"] * 2 + ["2026-09-08"],
    "amount": [5200, 640, 380, 4800, 1250, 450, 450, 780],
    "bill_no": ["HTL-8812", "R-221", None, "HTL-4410", "R-9011", "R-310", "R-310", None],
})
claims["expense_date"] = pd.to_datetime(claims["expense_date"])
claims["submitted"] = pd.to_datetime(claims["submitted"])
claims["city_class"] = claims["city"].map(lambda c: "metro" if c in METRO else "other")
c = claims.merge(CAPS, on=["grade", "city_class"], how="left", validate="many_to_one")

# Find repeated bills first, so a duplicate cannot also inflate the day's meal total.
dup = c.duplicated(["emp", "bill_no", "amount", "expense_date"], keep="first") & c["bill_no"].notna()

flags = []
# Meal cap is per day, so compare the day's total (excluding duplicates), not each line.
meal_day = (c[(c["category"] == "meal") & ~dup].groupby(["emp", "expense_date"])["amount"]
            .transform("sum"))
c["meal_day_total"] = meal_day
for r in c.itertuples():
    f = []
    if r.category == "hotel" and r.amount > r.hotel_cap:
        f.append(f"hotel {r.amount} over cap {r.hotel_cap} by {r.amount - r.hotel_cap}")
    if r.category == "meal" and r.meal_day_total > r.meal_cap:  # NaN for duplicates: skipped
        f.append(f"meals for the day {int(r.meal_day_total)} over cap {r.meal_cap}")
    if (r.submitted - r.expense_date).days > SUBMIT_WITHIN_DAYS:
        f.append(f"submitted {(r.submitted - r.expense_date).days} days after expense")
    if r.amount > BILL_REQUIRED_ABOVE and pd.isna(r.bill_no):
        f.append("no bill number above threshold")
    flags.append("; ".join(f))
c["flag"] = flags

c.loc[dup, "flag"] = c.loc[dup, "flag"].where(c.loc[dup, "flag"] == "", c.loc[dup, "flag"] + "; ") \
                     + "same bill claimed twice"

print(c.loc[c["flag"] != "", ["claim_id", "category", "amount", "flag"]].to_string(index=False))

# Summary for the approver: what is clean, what is excess, what needs a decision.
excess = (c.loc[c["category"] == "hotel", "amount"] - c.loc[c["category"] == "hotel", "hotel_cap"]).clip(lower=0).sum()
print("\nlines:", len(c), "| flagged:", (c["flag"] != "").sum(), "| hotel excess over cap:", excess)
assert (c["flag"] != "").sum() == 7 and excess == 700

Seven of eight lines are flagged, and the reasons are specific enough to act on without opening the claim:

• C1: a Mumbai hotel ₹700 over the G1 metro cap, and two meal bills that are each under ₹900 but together come to ₹1,020 for the day.
• C2: meals of ₹1,250 against a ₹1,000 cap in a non-metro city. The Lucknow hotel at ₹4,800 is within the G2 cap and passes.
• C3: submitted 49 days after the expense, and the same bill R-310 appears twice.
• C4: a ₹780 taxi with no bill number.

The ₹380 meal line in C1 has no bill either, but it is below the threshold and correctly passes that rule.

Choices that change the answers

Meals are capped per day, not per bill. Checking each line against a daily cap lets two ₹640 and ₹380 bills through when their ₹1,020 total is over. The screen totals meal lines per employee per date first, then compares. If your policy caps per meal instead, change the grouping, not the rule text.

Duplicates are found before daily totals. In the first version of this script, C3's repeated bill was also counted twice in the meal total and pushed a ₹450 day over its ₹700 cap. The duplicate was one problem reported as two. Identifying duplicates first and leaving them out of the daily total keeps each finding attributed to its real cause.

The cap table join is validated. validate="many_to_one" makes pandas stop if the caps table ever has two rows for the same grade and city class, for instance after a policy revision was pasted below the old one. Without it, every matching claim line would be duplicated and every total inflated.

Excess is reported as an amount. "Over cap" alone does not help an approver decide. "₹700 over" does, and summing the excess across a month shows finance what the policy is costing in exceptions.

Doing the same screen in Excel

With the caps in a table named Caps and a helper column Key holding grade and city class joined (for example =[@Grade]&"|"&[@CityClass]), three columns reproduce the main rules:

Hotel cap  =XLOOKUP([@Key], Caps[Key], Caps[HotelCap], "NO CAP")
Meal day   =SUMIFS([Amount], [Emp], [@Emp], [ExpenseDate], [@ExpenseDate], [Category], "meal")
Late days  =[@Submitted] - [@ExpenseDate]

Then a flag column compares each against its limit. Excel handles this well up to a few thousand lines a month; beyond that, or when the duplicate check has to span a full year, the script is easier to keep correct.

Reporting back

Send approvers one sheet per claim with its flagged lines and reasons, and send finance a monthly summary: number of claims, share flagged, total excess over caps by rule. If one rule flags most claims, the policy may be out of date rather than the employees careless, which is worth raising with its owner. Date arithmetic across weekends and holidays, such as a submission window counted in working days, is covered in counting working days with NETWORKDAYS.INTL for second and fourth Saturdays.

Turning a written policy into a table of testable rules, and presenting exceptions so a manager can act on them, is a practical skill we work on with synthetic finance and HR data in the Data Analyst programme. To see which of these skills you already have, try the skill check.