Killing the Hand-Built Spreadsheet
Every IT team has one. It lives in a shared folder, and its filename ends in the month. On the first working day of each month someone opens last month's copy, saves it under a new name, and begins. Export a table from the monitoring tool. Paste it into the raw data tab. Find the paste is one column off because the export grew a field. Find the formula someone overwrote with a typed-in number in a hurry. Refresh the chart. Screenshot it because the chart never survives being emailed. Paste the screenshot into an email and send. The workbook has a tab called "old" and a tab called "old2".
Nobody designed this. It grew, one reasonable shortcut at a time, from a spreadsheet somebody built once to answer a question. It persists because it works, roughly, if the right person is not on holiday. It also persists because replacing it looks like a project while enduring it is only a morning. The mornings add up, the errors are invisible until they are embarrassing, and the whole thing evaporates the day its owner leaves.
This guide is the replacement project, in five steps small enough to do alongside your real work. By the end you will know how to find where every number in the spreadsheet really comes from. You will know how to automate the pulling and the arithmetic, and prove the new version matches the old. You will also know how to retire the old one without anyone losing a number they relied on. There is a script outline to adapt, a migration checklist, and an honest note about what should stay manual.
In this guide
- Why the spreadsheet is the problem, not the person
- Step one: find where every number really comes from
- Step two: automate the pull
- Step three: automate the math
- Step four: validate for two cycles
- Step five: retire the manual version
- An illustrative script outline
- The migration checklist
- What stays manual, honestly
- Where to go next
Why the spreadsheet is the problem, not the person
The problem is not the person doing the ritual, and it is not even the spreadsheet application. A spreadsheet is a superb tool for exploring numbers. It is a poor tool for producing the same numbers every month, for three reasons. It mixes data, calculation, and presentation in one file, so a change to any of them can silently break the others. It invites editing: anyone with the file open can type over a formula, and the file will not object. And it has no memory of how it was made; the sequence of exports, pastes, and fixes lives in the builder's head.
The migration separates those three things. Data gets pulled by a script into plain files. Calculation happens in code that can be read and version-controlled. Presentation is generated last, from the results. The spreadsheet does not have to disappear entirely. It can stay on as a viewer that people open to look at numbers. But it stops being the workbench where numbers are made.
Step one: find where every number really comes from
Go through the spreadsheet cell by cell, or at least column by column, asking one question of each number: where does this actually originate? Not "the raw data tab" — that is where it was pasted. The origin is the system that first knew the value. You will find a few categories:
- The monitoring tool. Availability, downtime minutes, interface utilization, disk usage, alert counts. These come out through the tool's export function (a CSV download from a report or a table). Or, if the tool offers one, they come out through its API. Which one you use, and how to call it, is the ground covered by pulling data out. For this guide, the point is simply to identify that the monitoring tool is the source and note which report or endpoint holds the number.
- A log. Backup completion, job run times, error counts. The number was pasted from a log viewer or a text file, and the origin is the log itself.
- A ticket export. Incident counts, time to resolve, tickets by category. The origin is the ticketing system's export or query.
- Another spreadsheet. The dreaded case: a number copied from a different hand-built file, which has its own origins. Follow the chain until you reach a system.
- A person. Some cells are typed in from memory or judgment: a note about why a number moved, an assessment of risk, an estimated cost. Mark these clearly; they are the subject of the honest note at the end.
Write the result down as a source map, with one row per column of the spreadsheet. Each row gives the origin system and the exact export or query that produces the number. It also gives the filter applied (production only? a particular site?) and the unit. The source map is the design document for everything that follows. It usually reveals a surprise. That might be two columns that claim to be the same thing from different exports. Or it might be a number whose origin nobody can identify at all. (It is usually the one on the slide.)
Tip: while you are building the source map, time the manual ritual once, honestly, including the fixing and the chasing. You will want that figure later, both to justify the migration and to enjoy the moment when the script finishes in under a minute.
Step two: automate the pull
With the source map in hand, write a scheduled script with just one job. It should fetch each source and save it, unchanged, as one CSV per source. Do not compute anything yet. Do not tidy anything yet. The raw files are your evidence that the pull worked. Keeping them separate from the calculation makes every later problem easier to diagnose. If a number is wrong, the raw CSV tells you at once whether the pull or the math is at fault.
Each pull follows one of a few patterns. The monitoring tool's export becomes a request to its API or a scripted download of the report it already produces. The log becomes a filtered read of a file or a query to whatever collects the logs. The ticket export becomes a query or a scheduled export the ticketing system can produce itself. Name the outputs by source and period — raw-availability-YYYY-MM.csv, raw-tickets-YYYY-MM.csv — and save them to the same archive folder the finished report will use.
Schedule the pull to run after the period's data is complete. A monthly report pulled at one minute past midnight on the first may run before the monitoring tool has finished its own end-of-period bookkeeping. A few hours later is safer. I learned this from a report that was always missing its last day. Log each run, including how many rows each source returned. That matters because a source that suddenly returns zero rows is the failure you most need to notice.
Step three: automate the math
Open the formula tabs and translate them. Every formula becomes a few lines of code that read the raw CSVs and produce the result. This is the step people fear. And it is usually the easiest, because spreadsheet formulas are already precise. A cell that says "downtime minutes divided by minutes in the month, subtracted from one" translates directly. What takes time is discovering the exceptions. Those include the cell that was hard-coded and the row that was manually excluded because "that device does not count." They also include the rounding that was applied in one column but not another. Each exception is a decision that was made once and forgotten; write it down and either encode it deliberately or drop it deliberately.
The output of this step is a second CSV: the computed summary, one row per service or device, with the figures the report needs. That file is what the spreadsheet used to be. From here the spreadsheet's role changes. If people like numbers in a grid, give them a template that opens the summary CSV and charts it. The template should have no formulas that matter and no raw data tab to break. It is a viewer. If someone types over a cell, they have changed their own view and nothing else. Next month's file replaces it anyway.
Two design rules for the math. First, compute in the script and only in the script. Never let a "quick fix" formula creep back into the viewer, because that is how the original spreadsheet was born. Second, keep the calculation readable by someone who is not you. Name the variables after the spreadsheet columns they replace, and comment the exceptions with the reason. That way, the next owner can follow the logic without your memory.
Step four: validate for two cycles
Do not switch over yet. For two full reporting cycles, run both: the old ritual as before and the new script alongside it. Then compare every number. This is not a formality; it is where most of the migration's value is found, because differences are findings. They fall into three kinds:
- The script is wrong. A filter was missed, a unit was confused, a period boundary was off by a day. Fix the script; that is what the validation cycle is for.
- The spreadsheet was wrong. A broken formula, a stale hard-coded number, a row that was accidentally excluded months ago. This is common, and it is worth handling carefully. The old figures were reported to people, and someone may need to know that the availability figure they were given was optimistic. Decide with your manager how to communicate corrections; do not just quietly change the number.
- Both are right, differently. The spreadsheet excluded scheduled maintenance from downtime; the script did not, or the reverse. Neither is wrong, but the report must say which method it uses. Pick one, document it, and apply it consistently from now on.
Record every difference and its resolution in a short log. By the end of the second cycle the two versions should agree to the last decimal. Or they should disagree only in ways you have chosen and written down. That log is also the answer to the question you will be asked at switchover: "how do we know the new one is right?"
At Ashgrove Housing the monthly service spreadsheet took its owner four hours. During the two validation cycles the script and the spreadsheet disagreed on the tenant portal's availability by just under one per cent. The spreadsheet's formula had been overwritten with a typed-in number during some earlier emergency. And that number had gone to the housing director ever since. The correction was an awkward week. The report now takes twenty minutes, all of it reading.
Step five: retire the manual version
Retirement is a small ceremony with three parts, and skipping any of them lets the spreadsheet come back.
Announce. Tell everyone who received the old report that from the next period it will be produced automatically and arrive on a fixed date. Tell them it will look like the sample you attach. Say what has changed in the numbers, if anything, and why. Name the owner of the new job and where the archive lives.
Archive. Move every historical copy of the old spreadsheet into a read-only archive folder, named by period. They are the record of what was reported, and they must survive, but they must also stop being the thing people open.
Delete the shared file that invites edits. This is the step people flinch at. The working copy, the one in the shared folder that everyone knows the path to, goes away. If it stays, someone will open it "just to check something," and update a cell. Within a few months you will then have two sources of truth again. Delete it, and leave a short text file in its place saying where the automated report now lives. Remove the recurring calendar reminder that used to summon the ritual.
An illustrative script outline
Here is the whole thing in outline, in one file. It is not production code. The fetch functions are stubs you replace with your tool's export or API call. The addresses are placeholders. And there is no error handling beyond the one check that matters. It is in Python; the same structure translates directly to PowerShell if that is what your team reads.
# monthly_report.py -- illustrative outline: pull, compute, write, email
import csv, smtplib, datetime
from email.message import EmailMessage
PERIOD = datetime.date.today().strftime("%Y-%m")
ARCHIVE = "/srv/reports/archive"
def save_csv(path, rows):
with open(path, "w", newline="") as f:
writer = csv.DictWriter(f, fieldnames=rows[0].keys())
writer.writeheader()
writer.writerows(rows)
# ---- step two: pull each source, save it raw, touch nothing ----
def pull_availability():
rows = fetch_from_monitoring_tool("availability", period=PERIOD) # your export or API call
save_csv(f"{ARCHIVE}/raw-availability-{PERIOD}.csv", rows)
return rows
def pull_tickets():
rows = fetch_from_ticket_system(period=PERIOD) # your ticket export
save_csv(f"{ARCHIVE}/raw-tickets-{PERIOD}.csv", rows)
return rows
# ---- step three: the math the spreadsheet used to do, written once ----
def compute(availability, tickets):
summary = []
for svc in availability:
monitored = float(svc["minutes_monitored"])
lost = float(svc["minutes_down"]) # method: maintenance NOT excluded
pct = 100.0 * (monitored - lost) / monitored
summary.append({
"service": svc["name"],
"availability_pct": round(pct, 3),
"hours_lost": round(lost / 60.0, 1),
"incidents": sum(1 for t in tickets if t["service"] == svc["name"]),
})
return summary
def render_html(summary):
parts = ["<h2>Monthly service summary " + PERIOD + "</h2>", "<table>",
"<tr><th>Service</th><th>Availability</th><th>Hours lost</th><th>Incidents</th></tr>"]
for r in summary:
parts.append("<tr><td>" + r["service"] + "</td><td>" + str(r["availability_pct"]) + "%</td>"
"<td>" + str(r["hours_lost"]) + "</td><td>" + str(r["incidents"]) + "</td></tr>")
parts.append("</table>")
return "\n".join(parts)
def send(html, csv_path):
msg = EmailMessage()
msg["Subject"] = "Monthly service summary " + PERIOD
msg["From"] = "reports@example.internal"
msg["To"] = "it-management@example.internal"
msg.set_content("This report is best viewed in an HTML-capable mail client.")
msg.add_alternative(html, subtype="html")
with open(csv_path, "rb") as f:
msg.add_attachment(f.read(), maintype="text", subtype="csv",
filename="service-summary-" + PERIOD + ".csv")
with smtplib.SMTP("mail.example.internal") as smtp:
smtp.send_message(msg)
if __name__ == "__main__":
availability = pull_availability()
tickets = pull_tickets()
summary = compute(availability, tickets)
if len(summary) == 0:
raise SystemExit("No rows computed; refusing to send an empty report")
out = f"{ARCHIVE}/service-summary-{PERIOD}.csv"
save_csv(out, summary)
send(render_html(summary), out)
Three things to notice. The raw files are saved before any calculation, so a bad month can be re-run from evidence. The availability method is stated in a comment right where it is applied, which is where the next person will look. And the script refuses to send an empty report, because a confident-looking blank is worse than no report at all. Retries, logging, and a PDF rendering for the archive can be added once the basic version has matched the spreadsheet for two cycles.
The migration checklist
The five steps as a checklist. Work down it in order; each row's "done when" is the test for moving on.
| Step | Task | Done when |
|---|---|---|
| 1 | Source map written | Every column in the spreadsheet has an origin system, an exact export or query, a filter, and a unit — or is marked "judgment" |
| 1 | Manual ritual timed | An honest hours-per-cycle figure exists, including fixing and chasing |
| 2 | Pull script written | One raw CSV per source appears in the archive after each scheduled run |
| 2 | Pull scheduled and logged | Runs after the period's data is complete; row counts per source are logged |
| 3 | Formulas translated | Every spreadsheet formula has a counterpart in code; every exception is documented or dropped deliberately |
| 3 | Summary CSV produced | One computed file per period with the figures the report needs |
| 3 | Viewer template built (optional) | A spreadsheet that opens the summary CSV and charts it, with no formulas that matter |
| 4 | Two cycles run in parallel | Old and new produced side by side for two full periods |
| 4 | Differences logged and resolved | Every discrepancy classified (script wrong, spreadsheet wrong, method choice) and closed |
| 4 | Corrections communicated | Anyone who received a wrong historical figure has been told, if the difference matters |
| 5 | Switchover announced | Recipients know the date, the format, the owner, and the archive location |
| 5 | Old copies archived read-only | Historical spreadsheets preserved by period, uneditable |
| 5 | Working copy deleted | The shared editable file is gone; a pointer note is in its place; the calendar reminder is removed |
| 5 | Report job monitored | Something alerts if the report does not arrive, and the alert has been tested |
The last row is covered in scheduled delivery. It is here because a migration that ends with an unmonitored job has traded a visible ritual for an invisible one.
What stays manual, honestly
Some of the cells in the old spreadsheet were never numbers. They were judgments. They included the comment explaining why the file server's availability dipped, and the sentence assessing whether the bandwidth trend is a problem. They also included the recommendation to buy more disk. No script can produce those, and a report that pretends to, by generating boilerplate sentences from thresholds, reads as generated. Keep those fields manual. Have the script leave a clearly marked space for commentary, and have a person add two or three sentences before it goes out. The numbers arrive automatically at six in the morning; the human sentences are added by nine; the report is sent at ten. That is still a ninety-minute task turned into a ten-minute one.
Be equally honest about the sources that resist automation. If a number can only come from a person — a count of something no system records, an estimate — it is a manual input. In that case, the script should read it from a small, clearly labeled input file rather than pretend it came from anywhere else. You may later find a system that records it; until then, one honest manual field beats an automated guess.
The migration is not always worth it. A spreadsheet rebuilt twice a year in an hour is not a problem; it is an hour. This guide is for the monthly ritual that eats a morning and the weekly one that eats an hour. It is also for the one whose owner is about to leave.
Where to go next
This guide is the deepest single migration in the pillar, and the others surround it. When it is done, the workbook with the "old2" tab sits in a read-only folder, and the morning is yours. Which reports deserve automation is where you decide that this particular spreadsheet is worth the effort, using an inventory and some arithmetic. Report templates per audience shapes what the script renders for each reader. The guide to reports people actually read makes sure the result is opened. Together with scheduled delivery, they make up Automating Routine Reports.
Put this into practice with SysPeep Monitor
Step one of this migration is much shorter when the monitoring tool already holds the numbers in one place and can report on them. SysPeep Monitor keeps availability, performance, and alert history behind a single web console, produces reports from it, and can email them. Check the product documentation for the report types and scheduling options. The free trial installs on any Windows machine in minutes.
Download Free TrialLearn About SysPeep