Extracting Mileage and Per Diem Entries From an Expense Report

Almost every line on an expense report is backed by a receipt that states an amount. Mileage and per diem are not. They are computed from a claimed quantity and a published rate, which means the document contains its own derivation and you can check it.

Two lines with no receipt behind them

The distinction matters because it changes what an error looks like. On a receipted line, an extraction error is a misread number and the truth is on the attached image. On a computed line, the number can be extracted perfectly and still be wrong, because the claimant did the arithmetic by hand in a spreadsheet at the end of a trip. These are the lines where a validator earns its place: not detecting bad optical character recognition, but detecting a claim that does not follow from its own stated inputs.

Both line types carry a quantity, a rate and an amount, and both need the same three-field structure preserved through extraction. Collapsing a mileage line to its reimbursement amount throws away the ability to check it ever again, and a per diem line reduced to a total is indistinguishable from a meal receipt in the general ledger.

Miles times rate, and which rate

The identity is trivial and the interesting part is entirely in the second term.

reimbursement = miles * rate_per_mile

Worked with a rate assumed for the example at $0.70 per mile, and a claimed 142 miles: 142 × 0.70 = $99.40. If the line claims $99.40 the extraction and the claimant agree. If it claims $198.80, the claimant doubled a one-way distance for a round trip after already entering the round-trip mileage, which is the single most common arithmetic error on these lines and shows up as a ratio of exactly two.

The rate is the part that goes stale. In the United States the Internal Revenue Service publishes an optional standard mileage rate annually, in a notice, with separate rates for business, medical or moving, and charitable use; the current and prior rates are listed at irs.gov. Three consequences for extraction:

• The applicable rate is fixed by the travel date, not the submission date. A report submitted in January covering travel in late December legitimately contains two different rates on adjacent lines. A validator that applies one rate to the whole report will flag every December line as wrong. This is the trap worth building the validator around.
• The business rate is a ceiling, not a requirement. An employer may reimburse below it, and many do at a flat internal rate. So the check is against the rate printed on the line or configured for the policy in force on the travel date, and only secondarily against the published figure.
• The rate on the line may not be printed at all. Many expense tools print only miles and amount. Then the implied rate is amount divided by miles, and an implied rate that is not a value the policy ever used is the finding.

Per diem is not a daily constant

A per diem claim looks like a number of days times a daily rate, and it almost never is. In the United States the General Services Administration publishes per diem rates for the continental states at gsa.gov, with lodging and meals-and-incidental-expenses as separate figures, set by locality — defined by county rather than by city name, which is itself a source of mismatches — and varying by month in seasonal destinations. Rates outside the continental states come from other authorities entirely.

The structural feature that breaks naive checking is the proration of travel days. The meals-and-incidentals allowance for the first and last day of travel is claimed at three quarters of the daily figure, on the reasoning that a traveller does not eat all their meals away on a departure day. So for a four-day trip with an assumed meals allowance of $79 per day:

day 1 (travel)  0.75 * 79.00 = 59.25
day 2 (full)           79.00 = 79.00
day 3 (full)           79.00 = 79.00
day 4 (travel)  0.75 * 79.00 = 59.25
                              -------
claimed M&IE                   276.50

A validator that computes 4 × 79.00 = 316.00 and flags the claim as understated has misunderstood the rule and will generate a correction that costs the organisation money. The general formula, for a trip of n days with n at least two, is the daily figure times n minus one half — the two travel days contribute 1.5 days between them. A single-day trip is a different case again and is often not claimable as per diem at all.

Two further deductions commonly appear as their own lines. Meals provided by a conference or included in a hotel rate are deducted using the published breakdown of the daily allowance into its breakfast, lunch and dinner components, so a claim reduced by an odd-looking amount is usually a provided-meal deduction rather than an error. Lodging is capped at the locality figure and reimbursed at actual cost against a receipt, which makes it a receipted line living in the middle of a computed block.

The double-claim cross-check

The check that finds real money is not within these lines but across them. A per diem claim asserts that meals on a given day were paid for out of the allowance. A receipted meal expense on the same day asserts that a meal was reimbursed directly. Both on one report for one day is a double claim, and it is usually an honest mistake by someone who switched methods mid-trip.

Detecting it requires the categorised lines from the rest of the report, which is why the two extractions belong to one pipeline: expense category extraction produces the meal lines this check consumes. The same joins catch a mileage claim on a day with a claimed taxi fare between the same two places, and a mileage claim on a day the traveller also claimed a rental car with fuel.

Emit these as findings on the report rather than as errors on a line, and prioritise them by the money at stake rather than by model uncertainty — review queue financial impact priority is the pattern. Nothing about a double claim identifies which of the two lines is the wrong one, and a pipeline that silently picks the cheaper is making a decision that belongs to a reviewer.

Reading the log the numbers came from

Mileage frequently arrives as a photographed log rather than as typed rows, and the log has its own reading problems, all of which are about columns and inheritance rather than about characters.

• Odometer columns give a third check. Where start and end odometer readings are recorded, the end minus the start must equal the claimed miles — another cross-field validation rule — and consecutive rows should chain: one row’s end reading is normally the next row’s start. A break in the chain is either personal driving between trips, which is fine, or a transposed digit, which is not.
• Dates inherit from a header. A log with a month printed at the top and only day numbers in the rows will produce twelve wrong dates if the header is not read first. The same pattern appears in the year, and a date field validation rule that bounds each date to the claimed travel period catches it.
• Round trip is a checkbox, not a number. A row marked round trip with a one-way distance in the miles column is complete and correct; the doubling happens downstream. Extract the marker.
• Purpose is required and is free text. It is the field a reviewer reads and the field an extractor is most tempted to summarise. Keep it verbatim; a paraphrased business purpose is useless as evidence.
• Totals rows. A log usually foots itself. Extract the printed total and compare it to the sum of the rows, which catches a row lost to a page break far more reliably than per-field confidence scoring does.