For years, Monday mornings on our team started the same way. One of our analysts would open a handful of spreadsheets, put on headphones, and start copying. Around lunchtime, the weekly reconciliation report landed in everyone’s inbox.
Nobody thought twice about it. The report was important, it was always right (or so we assumed), and three hours was just what it cost.
Then the analyst took a week off. The report went out on Wednesday instead of Monday, a few people grumbled, and somebody finally asked what should have been asked years earlier: why on earth does this take three hours?
It doesn’t anymore. It takes about three minutes. Here’s how we got there, including the parts that were messier than the headline suggests.
First, we just watched
Our first instinct was to open an editor and start coding. We’re glad we didn’t. One of our developers pulled up a chair next to the analyst on a Monday and took notes while the report got built. Here’s roughly what those three hours looked like:
- Download export files from three internal systems, plus the latest county data (about 20 minutes)
- Clean them up: fix the date formats, remove duplicates, get the account numbers to look the same everywhere (45 minutes)
- Match records across the files with spreadsheet lookups (an hour, on a good day)
- Go through the mismatches and work out which ones are real problems (40 minutes)
- Put together the summary and email it out (15 minutes)
Look at that list again and only step 4 needs a human being. That’s the part where experience counts, where someone has to know that one mismatch shows up every month and means nothing, while another means picking up the phone.
The rest, well over two hours of it, was the same clicking and pasting every week.
The code was the easy part
We started small. Just the cleanup step, in a short Python script, because its rules looked like the simplest ones to pin down.
They weren’t. Most of the rules, it turned out, lived in the analyst’s head. Account numbers from one system sometimes show up with leading zeros. One county sends dates in a different format from everyone else. One type of record always gets left out, and to this day nobody can tell us who decided that or why. None of it was written down anywhere. We spent more time asking “wait, why do you do that?” than we spent writing Python.
We also didn’t trust the script at first, and we’d recommend that attitude. For three weeks it ran alongside the manual process, and we compared the two results line by line. They disagreed twice. Both times we assumed the script had a bug. Both times it was the manual report, with a copy-and-paste slip nobody had noticed. That was a slightly uncomfortable discovery, because it made us wonder about all the Mondays before.
After that we added the matching, then the summary, then the email. Each one ran next to the old way for a while before it took over.
So, three minutes?
Yes, with an asterisk. The script does the downloading, cleaning, matching and summarizing in under three minutes. The analyst still goes through the flagged exceptions, and we have no plans to change that. So the honest version is that about 140 minutes of busywork disappeared each week, which adds up to roughly 120 hours a year.
What we didn’t see coming were the side effects. The report no longer depends on one person being at their desk on Monday morning, so holidays stopped being a problem. The rules are finally written down, even if “written down” means Python.
And because the report is now cheap to run, we run it every morning instead of once a week. A problem that used to sit unnoticed until the following Monday now gets caught the next day. If we had to pick, that has turned out to matter more than the hours.
Is your task a good candidate?
We’ve since gone looking for other tasks like this one, and we’ve talked ourselves out of automating a few. The ones worth doing tend to look alike. They come around on a schedule, weekly or monthly. You could explain every step to a new hire without saying “it depends” too often. The files arrive in the same shape each time. And the work is tedious enough that mistakes slip in without anyone noticing.
If a task only comes up twice a year, or the input changes format every time, a script will probably cost you more than it saves.
One more thing we’d pass on: don’t automate a process you don’t understand yet. If we had skipped the sitting-and-watching part, we would have built a very fast way of producing a slightly wrong report.
Where to start
If you want to find your own version of our Monday report, try asking your team what they do every week that they could do half asleep. You’ll get a list quickly, and probably a few groans.
Pick the smallest thing on it. Sit with whoever does it and write down what they really do, which is rarely what the procedure document says. Automate one step. Then check it against the manual result for a few weeks before you trust it.
Ours took about two months from that first Monday to the day the old spreadsheet was retired. The analyst, for the record, doesn’t miss it.
