Build a cost-per-mile report for a truck from bank and card CSVs

Cost per mile is total operating cost for a period divided by the miles driven in that period. To get it from bank data you need four things: every transaction from the business bank account, card and fuel card for the month, a rule that puts each one in a cost bucket, the interest part of the truck payment (not the whole payment), and the month's miles from the ELD. Divide each bucket by the miles, add them up, and you have the number that tells a driver which loads lose money.

Here's a working version with pandas, and the mistakes that make most homemade versions wrong.

The inputs

Three CSVs for October 2026 for a fictional one-truck owner-operator, Pinecrest Hauling, plus one number from the ELD:

import pandas as pd from decimal import Decimal MILES = 10_400 # ELD total for October 2026  frames = [] for path, source in [("checking.csv", "bank"), ("card.csv", "card"), ("fuelcard.csv", "fuel_card")]: df = pd.read_csv(path, dtype=str) # keep amounts as text until we convert them  df["source"] = source frames.append(df) tx = pd.concat(frames, ignore_index=True) tx["amount"] = tx["amount"].map(lambda s: Decimal(s.replace(",", ""))) 

Read amounts as strings and convert to Decimal. Pandas will happily turn 1,412.66 into NaN or a float, and either one ruins a report you're going to make decisions from.

Rule-based buckets

Merchant text on truck statements is repetitive, which makes plain rules work well. Order matters: the first match wins.

RULES = [ (r"PILOT|FLYING J|LOVE'?S|TA |PETRO|SHELL|CHEVRON", "Fuel"), (r"PROGRESSIVE|GREAT WEST|OOIDA INS|NATIONAL INTERSTATE", "Insurance"), (r"TIRE|SPEEDCO|TRUCK SERVICE|FREIGHTLINER|PETERBILT|KENWORTH", "Maintenance and tires"), (r"E-ZPASS|PIKEPASS|TOLL|BESTPASS", "Tolls"), (r"IRS 2290|IFTA|IRP|DMV|PERMIT", "Permits, plates and 2290"), (r"VERIZON|T-MOBILE|MOTIVE|SAMSARA|KEEPTRUCKIN", "ELD and phone"), (r"TRUCK LOAN|COMMERCIAL CREDIT|FINANCIAL SVCS", "Truck payment"), (r"TRANSFER TO .*SAVINGS|OWNER DRAW", "Owner pay"), ] import re COMPILED = [(re.compile(p, re.IGNORECASE), name) for p, name in RULES] def bucket(desc: str) -> str: for pattern, name in COMPILED: if pattern.search(desc or ""): return name return "Uncategorized" tx["bucket"] = tx["description"].map(bucket) 

Print the Uncategorized rows every month and add a rule for each new merchant. After two or three months the list is almost empty. Don't skip this: an unbucketed $900 repair makes the month look $0.09 a mile cheaper than it was.

Four mistakes that break cost per mile

1. Counting the whole truck payment. A $2,150 payment is mostly principal, which pays down the loan. Only the interest is a cost. If you have the lender's statement, use its interest figure. If you don't, estimate it from the balance:

loan_balance = Decimal("92000.00") annual_rate = Decimal("0.095") interest = (loan_balance * annual_rate / 12).quantize(Decimal("0.01")) # 728.33 

Then replace the "Truck payment" bucket with a "Truck loan interest" line of 728.33. The other 1,421.67 is principal, not a cost.

2. Counting owner pay. Transfers to the driver's personal account are not truck costs. They come out of what's left after costs. The Owner pay rule exists only so those rows can be dropped.

3. Counting fuel twice. If the carrier gave a fuel advance on the settlement and the same fill also shows on the fuel card statement, it is one cost, not two. Pick one source of truth for fuel per month (usually the fuel card) and drop the other. This is also where your IFTA fuel-by-state numbers come from, so getting it right matters twice.

4. Counting escrow as a cost. If you work from settlements rather than the bank, the weekly escrow deduction looks like an expense. It isn't. It's the driver's money held by the carrier and comes back when the lease ends. Leave it out of cost per mile.

The report

costs = tx[~tx["bucket"].isin(["Owner pay", "Truck payment", "Uncategorized"])] by_bucket = costs.groupby("bucket")["amount"].apply(lambda s: -sum(s)) # money out is negative by_bucket["Truck loan interest"] = interest report = pd.DataFrame({"October": by_bucket}) report["Per mile"] = report["October"].map(lambda v: (v / MILES).quantize(Decimal("0.001"))) report.loc["Total cost"] = [sum(by_bucket), (sum(by_bucket) / MILES).quantize(Decimal("0.001"))] print(report.sort_values("October", ascending=False)) 

For Pinecrest's October:

Settlements paid $24,440 gross for the month, which is $2.35 a mile. So Pinecrest kept about $1.27 a mile, or $13,198.92, before depreciation, owner pay and income tax. Any load offered below $1.08 a mile all-in loses money no matter how many miles it adds.

Make the inputs trustworthy first

The report is only as good as the CSVs. Before running it, check that each account's transactions add up from its opening to its closing balance on the statement. A missing page of the fuel card statement looks exactly like a cheap month.

That's the order a monthly close should run in anyway. The ZeraBooks trucking bookkeeping checklist puts cost per mile as the last step for that reason: settlements booked gross, accounts reconciled, loans split, then the review.

Disclosure: I work on ZeraBooks. If you are the driver and would rather not write Python, the owner-operator bookkeeping guide walks through the same numbers as a 20-minute weekly routine, and ZeraBooks can keep the books for you.