Skip to content

Fuel Card Reconciliation

Example prompt: "Every Monday at 9am, read last week's Allstar fuel card CSV from our shared Drive folder, match each transaction to driver, vehicle, and run, and draft a query email to the manager for anything that looks off — off-route fills, after-hours transactions, or litres above the vehicle's tank capacity."

The Problem

The Allstar or Shell CSV arrives every Monday morning, finance pastes it into a sheet, and tries to match each transaction against the rota and the vehicle register by VRN. The match is usually fine on the obvious cases — a driver filling up at the depot's contracted site on a Tuesday afternoon. The cases that need attention are the 95-litre fill into a 75-litre Transit, the after-hours transaction on a vehicle that was off-shift, the second card-use at a site 40 miles off-route, and the MPG slowly drifting down on a particular van. Finding them by eye in 200 transactions takes a morning, and most weeks finance has stopped looking by Wednesday.

How GloriaMundo Solves It

We build a workflow that runs every Monday at 9am. An integration step reads last week's fuel-card CSV from the shared Drive folder and the Driver Register, Vehicle Register, and Run Log tabs of the operations sheet. A code step joins each transaction to a (driver, vehicle, run) match by VRN, transaction timestamp, and the rota. A conditional step routes each transaction by anomaly class — litres-over-tank-capacity, off-route fill, after-hours transaction, MPG drift over 15% from the eight-week baseline, or product mismatch on a diesel VRN. An LLM step composes the manager's query email for each flagged transaction, and an integration step saves the queries as Gmail drafts grouped by driver. Glass Box preview shows the matched table and the driver queries before any email reaches anyone.

Example Workflow Steps

  1. Trigger (scheduled): Every Monday at 9am.
  2. Step 1 (integration): Read last week's fuel-card CSV from the configured Drive folder; read the Driver Register, Vehicle Register, and Run Log tabs of the operations Google Sheet.
  3. Step 2 (code): For each CSV row, parse TransactionDate, Time, SiteName, VRN, Litres, ProductCode, GrossAmount, and the driver-keyed Odometer. Join to the rota on (VRN, TransactionDate) to find the assigned driver and run. Tag a confidence — hard (VRN matched + rota matched), soft (VRN matched, no rota row for that day), exception (no VRN match in the Vehicle Register).
  4. Step 3 (code): For each matched row, compute MPG against the previous fill on the same VRN and compare against the eight-week rolling baseline for that make/model. Flag drift over 15%.
  5. Step 4 (code, gate): Dedupe on (csv_filename, transaction_authcode) against the 'Fuel Card Reconciliation Log' tab. Final state ('Reviewed') halts; provisional state ('Allocating') resumes; no row writes a provisional row before flagging.
  6. Step 5 (conditional): Route each transaction by anomaly class — over-tank, off-route, after-hours, MPG drift, product mismatch, or clean.
  7. Step 6 (llm + integration): For each non-clean transaction, compose a one-paragraph query naming the driver, the vehicle, the transaction time and site, and the specific anomaly. Group queries by driver and save as a single Gmail draft per driver for the manager to review.
  8. Step 7 (integration): Write the matched, flagged, and clean transactions to the Fuel Card Reconciliation Log, with anomaly_class, confidence, and gmail_draft_link populated.
  9. Step 8 (integration): Post a one-line summary in #fleet on Slack — last week's total spend, total litres, count of flagged transactions, and the number of driver drafts waiting for manager review.

Integrations Used

  • Google Drive — the inbox folder where the fuel-card CSV lands each Monday
  • Google Sheets — the Driver Register, Vehicle Register, Run Log, and the Fuel Card Reconciliation Log
  • Gmail — the per-driver query drafts for the manager
  • Slack — the Monday morning summary in #fleet

Who This Is For

Finance and operations leads at small couriers and haulage firms running 10 to 80 vehicles on Allstar, Shell, or BP fuel cards, where the weekly CSV is too large to eyeball but small enough that no-one has paid for the fuel-card provider's bolt-on analytics tier.

Time & Cost Saved

Reconciling a 200-transaction week by hand against the rota and the vehicle register typically takes finance two to three hours, and the soft anomalies (off-route, after-hours, MPG drift) usually do not get caught until the quarterly review. This workflow turns the weekly reconciliation into a 30-minute review of the queue of driver query drafts; the anomaly classification catches the cases finance was missing; the manager has them in front of the driver at the same week's stand-up rather than three months later.