Build a Fleet Mileage and Fuel Report

Every vehicle in a company fleet buys fuel on a fuel card, and every transaction comes back in a monthly export: date, card, vehicle registration, litres, cost and — because drivers are asked to key it in at the pump — the odometer reading. That export is the raw material for the fleet report: how far each vehicle went, how much fuel it used, what it cost per kilometre, and whether anything looks wrong. Wrong matters as much as the averages: fuel cards are a well-known route for misuse and plain mistakes, from a mistyped odometer to fuel bought for a private car on a company card. This page builds the report with pandas: distance and fuel economy per vehicle from consecutive fill-ups, cost per kilometre, a set of checks that flag suspicious transactions, a comparison with each vehicle's usual economy, and a monthly workbook. It belongs to Building Finance and Operations Reports.

Prerequisites

pip install pandas openpyxl

Two inputs: the fleet list with each vehicle's type, fuel, tank size and expected fuel economy, and the fuel card export for September 2026 with date, vehicle, fuel type, litres, cost and the odometer reading keyed at the pump. The export contains a few problems on purpose.

Step 1 — Distance and fuel between consecutive fill-ups

import pandas as pd

fleet = pd.read_excel("fleet.xlsx").set_index("Vehicle")
tx = pd.read_excel("fuel_card_export.xlsx", parse_dates=["Date"]).sort_values(["Vehicle", "Date"])
tx["Prev odometer"] = tx.groupby("Vehicle")["Odometer km"].shift()
tx["km"] = tx["Odometer km"] - tx["Prev odometer"]
tx["L/100km"] = (tx["Litres"] / tx["km"] * 100).where(tx["km"] > 0)
print(tx.groupby("Vehicle")[["km", "Litres"]].sum().round(0).to_string())

The odometer reading at each fill-up, minus the reading at the previous one for the same vehicle, is the distance driven between them. Assuming drivers fill the tank each time — the full-to-full method most fleets ask for — the litres bought at a fill-up are the fuel used over that distance, so litres divided by kilometres gives the fuel economy for the interval, conventionally expressed in litres per 100 km. The first fill-up of the month has no previous reading in the export and contributes fuel but no distance; a production report carries the last reading over from the previous month's file. Negative or zero distances are left out of the economy calculation, because they are data errors rather than driving, and the next step deals with them.

Step 2 — Flag the transactions that need a question

tx = tx.join(fleet[["Fuel", "Tank litres", "Expected L/100km"]], on="Vehicle")
checks = {
    "odometer went backwards": tx["km"] < 0,
    "no odometer entered": tx["Odometer km"].isna(),
    "more fuel than the tank holds": tx["Litres"] > tx["Tank litres"] * 1.05,
    "wrong fuel type": tx["Fuel type"] != tx["Fuel"],
    "economy far from expected": (tx["L/100km"] > tx["Expected L/100km"] * 1.5) | (tx["L/100km"] < tx["Expected L/100km"] * 0.5),
}
tx["Flags"] = [", ".join(name for name, hit in checks.items() if hit.loc[i]) for i in tx.index]
flagged = tx[tx["Flags"] != ""]
print(flagged[["Date", "Vehicle", "Fuel type", "Litres", "Odometer km", "km", "Flags"]].to_string(index=False))

Each check is a single condition on the joined table, kept in a dictionary so the list of rules is easy to read and extend. An odometer lower than the previous reading is almost always a typing error at the pump, and it also corrupts the next interval's distance, which is why the following fill-up for the same car shows an implausible economy too. A missing odometer means the transaction cannot be checked at all. Litres above the tank's capacity, with a small allowance for pump tolerance and a tank run nearly dry, cannot have gone into that vehicle. Fuel of the wrong type is the classic sign of a card used for a different car — here 61 litres of diesel on a petrol car's card, with no odometer reading, which is also more than its 50-litre tank. Economy far from the vehicle's expected figure catches the rest. None of these flags proves misuse; each is a question for the driver, and most turn out to have an innocent explanation that is quick to record.

Step 3 — Economy and cost per vehicle, and changes over the month

valid = tx[(tx["Flags"] == "") & tx["km"].notna()]          # clean fills with a measured distance
per_vehicle = valid.groupby("Vehicle").agg(km=("km", "sum"), litres=("Litres", "sum"), cost=("Cost", "sum"))
per_vehicle["L/100km"] = (per_vehicle["litres"] / per_vehicle["km"] * 100).round(1)
per_vehicle["mpg (UK)"] = (282.481 / per_vehicle["L/100km"]).round(1)
per_vehicle["£ per km"] = (per_vehicle["cost"] / per_vehicle["km"]).round(3)
per_vehicle = per_vehicle.join(fleet["Expected L/100km"])
per_vehicle["vs expected"] = (per_vehicle["L/100km"] / per_vehicle["Expected L/100km"] - 1).map("{:+.0%}".format)

trend = valid.assign(Half=valid.groupby("Vehicle").cumcount() < 3).groupby(["Vehicle", "Half"])["L/100km"].mean().unstack()
trend.columns = ["Later fills", "Early fills"]
trend["Change"] = (trend["Later fills"] / trend["Early fills"] - 1).map("{:+.0%}".format)
print(per_vehicle.round(1).to_string())
print(trend.round(1).to_string())

Using only the transactions that passed every check and have a measured distance — the first fill of the month for each vehicle is left out, because its litres were used over kilometres driven last month — each vehicle's economy for the month is total litres over total distance — more reliable than averaging the per-fill figures, which over-weights short intervals. It is shown both in litres per 100 km and in UK miles per gallon (282.481 divided by litres per 100 km), because drivers and finance teams in the UK often think in different units. Cost per kilometre is the figure for budgets and for recharging vehicle costs to departments. Comparing each vehicle with its expected economy, and its later fill-ups with its earlier ones, shows a pattern that the individual checks miss: one van's economy has got steadily worse through the month, using about a third more fuel per kilometre in its later fills. A gradual change like that usually means a mechanical problem — tyre pressures, a dragging brake, an injector — and is worth a workshop visit before it becomes a breakdown.

In September four of the five vehicles run within a few per cent of their expected economy: the two cars at 6.3 and 6.7 litres per 100 km, the first van at 9.2 and the truck at 28.6. The second van, VN22 DEF, averages 11.6 against an expected 9.5 — 22% worse — and its later fills are a third worse than its earlier ones, which is the pattern of a fault developing rather than of a heavier workload. At its September mileage, the extra fuel costs roughly £100 a month, before counting the risk of the vehicle being off the road.

Step 4 — The monthly workbook

with pd.ExcelWriter("fleet_report_2026-09.xlsx") as xw:
    per_vehicle.reset_index().to_excel(xw, sheet_name="By vehicle", index=False)
    trend.reset_index().to_excel(xw, sheet_name="Economy trend", index=False)
    flagged.drop(columns=["Prev odometer"]).to_excel(xw, sheet_name="Questions for drivers", index=False)
    tx.drop(columns=["Prev odometer"]).to_excel(xw, sheet_name="All transactions", index=False)
print("written; total cost £", round(tx["Cost"].sum(), 2), "| flagged", len(flagged))

The report has four sheets: the summary per vehicle — distance, fuel, cost, economy in both units, cost per kilometre and the comparison with expected; the economy trend; the questions for drivers, one row per flagged transaction with every reason; and all transactions for reference. The flagged list is the one that changes behaviour: when drivers know odometer readings are checked every month, the readings get more accurate and misuse becomes rare. Odometer readings work like the meter readings in Build an Energy Consumption Report from Meter Readings, and the same day-by-day apportioning applies if distance has to be split across months.

Common pitfalls

• Averaging per-fill economy. Divide total litres by total distance instead.
• Keeping bad rows in the averages. Exclude flagged transactions from economy and cost per km.
• No tank-size check. Fills larger than the tank are the clearest sign of a card used elsewhere.
• Ignoring the next fill after a bad odometer. One mistyped reading distorts two intervals.
• Only monthly totals. Look at the trend within the month to spot mechanical problems.

Conclusion

A fleet report from fuel card data uses consecutive odometer readings per vehicle to get the distance between fill-ups, and with full-to-full fills, litres per 100 km and cost per kilometre. Checks for odometers going backwards or missing, fills larger than the tank, the wrong fuel type and economy far from expected turn the export into a list of questions for drivers, and comparing each vehicle's later fills with its earlier ones reveals vehicles that need a workshop visit.

Frequently asked questions

How do I calculate fuel economy from fuel card data in pandas? Sort by vehicle and date, subtract the previous odometer reading to get distance, and divide litres by distance times 100 for litres per 100 km.

How do I convert litres per 100 km to miles per gallon? UK miles per gallon is 282.481 divided by litres per 100 km; US mpg is 235.215 divided by it.

What checks catch fuel card misuse? Fills larger than the tank, the wrong fuel type, missing or backwards odometer readings, and economy far from the vehicle's normal figure.

Why exclude flagged transactions from the averages? Errors such as a mistyped odometer produce impossible distances that would distort the vehicle's economy.

Related

• Up one level: Building Finance and Operations Reports — finance and operations reports.
• Consolidate Employee Expense Claims into a Report — other employee spending.
• Detect Outliers in Excel Data with pandas — outlier methods.
• Build an Energy Consumption Report from Meter Readings — cumulative readings again.