Stories Earlier work, 2017
Current affairs
Eight meters logged every ten seconds, and a weekly report that gives the final consumption per client without the Excel magic
A facility billed its users for the electricity they used during their stay. I replaced an afternoon of file handling and Excel transformations with a report that arrived by email every week, with one number per client. The numbers matched what they had computed by hand, so they trusted it quickly, and nobody has opened one of those files since.
- The data
- eight meters, a reading every ten seconds, one rotating log file per PC
- Before
- files pulled by hand into Excel, slow and risky
- After
- one number per client in a weekly email
Before
Eight electricity meters at eight sites, each wired to its own PC over a serial cable. Every ten seconds, each PC appended the current reading to its local, daily rotating log file. The facility's clients used the premises for a period of time and paid for the electricity they used during it, so somebody had to turn those files into a number per client.
They pulled the files from the PCs remotely, imported them into Excel, found the readings at the start and the end of the client's stay, and subtracted. The files were enormous, Excel crawled, and the import had to be repeated for every client and every period. When something went wrong, a client paid too much or too little. Both are bad, and both mean a phone call.
What was built
Nothing about the meters changed. The system collected the files on a schedule, removed the duplicate readings the logging produced, threw away the lines that were broken, and loaded the rest into a database. The raw files stayed on disk, untouched, in case anyone ever needed them.
The system already knew the clients and the periods they had booked, so the report could do the subtraction itself. Every week an email went out with the consumption per client for the period they had used the facility. One number per client, ready for the invoice.
After
The first reports were checked against the numbers they had computed by hand. They matched, which is why trust came fast. After that, the people took the number from the email and put it on the invoice. Nobody opened one of those files again, and nobody waited for Excel.
I never measured the hours saved, but a multi-step process a person had to run became a weekly email nobody has to think about.
The lesson
Handling large amounts of data is something computers are excellent at and people aren't. When we know up front what has to come out of the data, automating it pays off fast. The first version should produce the same numbers the people already trust. Match first, then improve.
More stories
More for your business
Case study 2020
When paychecks depend on Excel formulas
A regulated payment's path from a spreadsheet to a dedicated system, with an audit trail and five times the throughput
An HR department calculated a regulated payment in one spreadsheet. What replaced it, what it changed, and what I'd do differently today.
ReadEarlier work 2017
Check, please
Thousands of payment notices with pre-filled checks, so the back office stopped fixing payments
Thousands of payment notices went out with a blank check attached, and some payers filled it in wrong. What changed, what didn't, and the lesson.
ReadEarlier work 2015
Lost in translation
A project management system built in full and approved at every demo, when a to-do list was the need
Built in full, approved at every demo, and wrong at the end. What the client asked for, what they needed, why nobody noticed, and what I do differently.
ReadWhich report takes an afternoon?
Bring the files and the steps you take to get the number. You leave with a straight answer on whether it's worth fixing, which may be no.