Our Fuel Card Statement Went Through Excel and Three Drivers Were Accused of Buying Each Other's Diesel

We run about three hundred and forty fuel cards, one for each vehicle. Every month the fuel card company sends a statement as a CSV file, one line per transaction, and our transport office matches each transaction to a vehicle and its driver, so that fuel can be checked against mileage and anything odd can be followed up.

In the spring, transport flagged three drivers for buying far more diesel than their vans could have used. One was invited to a meeting about it. He pointed out, correctly, that on two of the dates listed he had been on holiday.

The cause was the spreadsheet. Our card numbers are nineteen digits long. When the CSV was opened by double clicking it, Excel decided the card number column held numbers, and Excel keeps only fifteen significant digits of any number. It changes every digit after the fifteenth to zero and displays the result in scientific notation. Cards whose numbers differed only in their last four digits became the same number, and the lookup that matched cards to vehicles gave every transaction for that number to the first vehicle it found.

Nobody had changed the process. The fuel card company had reissued our cards in January from a new number range, and in the old range our cards happened to differ earlier in the number. The defect had been waiting in the process for as long as we had run it.

We fixed the data rather than blaming the people. The statement is now loaded by a query that reads the card number as text and never passes through a double click, and the matching checks that every card on the statement maps to exactly one vehicle, stopping if it does not. Excel in Microsoft 365 now has settings to turn off automatic conversion of long numbers and leading zeros, and we have turned them off for everybody in finance and transport. We apologised to the driver in writing, and to the other two before anyone had spoken to them.

Most identifiers are written in digits, and very few of them are numbers. Nobody adds up a card number, a phone number or a barcode. If you would never do arithmetic with a value, store it as text, and watch every tool that might decide otherwise.

– Serguey Shinder