Turn one repeated spreadsheet task into a small Python script: a virtual environment, one package, readable functions, a debugger session and a clear failure.
5 min readBranislav Mateas
Copy post as Markdown
On this page
Many analytics tasks start as a weekly copy and paste job. A file arrives, someone computes a few rates, and the result goes into a report. A small Python script can do the same job the same way every week. That only works if the script runs in a known environment and stops loudly when the input is wrong.
This article walks through one hypothetical task from start to finish. The code was written for this article and was not run, so test it on your own files before you rely on it.
The task and the expected result
A marketing team sends a weekly Excel file, channels.xlsx. The first sheet has four columns: channel, sessions, purchases and revenue. You need a CSV with conversion rate and revenue per session for each channel, plus a total row.
Write the expected result down before you write code. This is the hypothetical input after one fix, described below:
Digital analyst and developer helping teams turn unreliable measurement into evidence they can use.
48
3,600
2.00%
1.50
paid_search
1,500
45
3,150
3.00%
2.10
email
600
30
2,400
5.00%
4.00
referral
300
12
900
4.00%
3.00
Total
4,800
135
10,050
2.81%
2.09
The total row comes from summed counts: 135 / 4,800 = 2.8125%, and 10,050 / 4,800 = 2.09375. The average of the four channel rates is (2 + 3 + 5 + 4) / 4 = 3.5%, which is wrong, because it gives email's 600 sessions the same weight as organic's 2,400. A hand-checked table like this becomes your first test.
A virtual environment and one package
The standard library cannot read .xlsx files, so the task needs one package: openpyxl. Install it into a virtual environment, not into the system Python. The venv documentation describes an environment as disposable and not movable. It is easy to delete and recreate, you keep no project code in it, and it should not go into Git.
On Windows, the venv page lists .venv\Scripts\activate.bat for cmd.exe and .venv\Scripts\Activate.ps1 for PowerShell. Activation is optional. You can also call the environment's interpreter by its full path, which is useful in a scheduled job.
The pip user guide describes requirements files as a way to get repeatable installs. pip freeze writes the exact versions of everything in the environment. A colleague can rebuild the same setup with python -m pip install -r requirements.txt.
One readable script
The script has four small functions. Each one does a single job and has a name that says what it does.
"""Weekly channel summary. Usage: python channel_summary.py IN.xlsx OUT.csv"""
import csv
import sys
from openpyxl import load_workbook
HEADER = ("channel", "sessions", "purchases", "revenue")
def is_number(value):
return isinstance(value, (int, float)) and not isinstance(value, bool)
def read_rows(path):
workbook = load_workbook(path, read_only=True, data_only=True)
rows = [row[:4] for row in workbook.active.iter_rows(values_only=True)]
workbook.close()
return [row for row in rows if any(value is not None for value in row)]
def check_rows(rows):
if not rows or tuple(rows[0]) != HEADER:
return [f"Expected header {HEADER}"]
if len(rows) < 2:
return ["No data rows"]
problems = []
for number, row in enumerate(rows[1:], start=2):
if len(row) < 4:
problems.append(f"Row {number}: fewer than 4 columns")
continue
for name, value in zip(HEADER[1:], row[1:]):
if not is_number(value):
problems.append(f"Row {number} ({row[0]}): {name} is {value!r}")
if is_number(row[1]) and row[1] <= 0:
problems.append(f"Row {number} ({row[0]}): sessions must be above 0")
return problems
def summarise(data):
totals = ("Total", sum(r[1] for r in data), sum(r[2] for r in data), sum(r[3] for r in data))
return [
{
"channel": channel,
"sessions": sessions,
"purchases": purchases,
"conversion_rate_pct": round(purchases / sessions * 100, 2),
"revenue_per_session": round(revenue / sessions, 2),
}
for channel, sessions, purchases, revenue in data + [totals]
]
def main():
if len(sys.argv) != 3:
sys.exit("Usage: python channel_summary.py IN.xlsx OUT.csv")
rows = read_rows(sys.argv[1])
problems = check_rows(rows)
if problems:
sys.exit("Summary not written:\n" + "\n".join(problems))
summary = summarise(rows[1:])
with open(sys.argv[2], "w", newline="", encoding="utf-8") as file:
writer = csv.DictWriter(file, fieldnames=list(summary[0]))
writer.writeheader()
writer.writerows(summary)
print(f"Wrote {len(summary)} rows to {sys.argv[2]}")
if __name__ == "__main__":
main()
A few choices follow the documentation. The openpyxl read-only mode page says a read-only workbook must be closed explicitly. With data_only=True, a formula cell returns the value stored the last time Excel read the sheet, not the formula text. The csv documentation says to open the output file with newline="". List comprehensions appear only where they replace a short loop. The script needs no classes.
Debugging the first failure
Suppose an earlier version had no check_rows, and the week's file had an empty sessions cell for referral. The run would stop with a traceback. Read it from the bottom up. The last line, TypeError: unsupported operand type(s) for +: 'int' and 'NoneType', names the problem, and the line above it points to the session total in summarise.
The pdb documentation shows how to run a script under the debugger: python -m pdb channel_summary.py channels.xlsx out.csv. When the program fails, pdb enters post-mortem debugging. There, p data[-1] would print ('referral', None, 12, 900). You can also put breakpoint() on a line to stop there. The built-in functions page says it calls pdb.set_trace() by default. Remove it before the script goes into a schedule.
The data fix belongs in the export: someone corrects referral to 300 sessions. The code fix is the check, so the next bad file fails with a message instead of a traceback.
Failing clearly instead of writing a bad file
With check_rows in place, the same bad file produces this message and no CSV:
Summary not written:
Row 5 (referral): sessions is None
Row 5 is the referral row, counting the header as row 1. Blank rows are skipped, so the count can drift from the sheet if blank rows sit in the middle. The sys documentation says sys.exit() with a string prints it to stderr and exits with status 1, which a scheduler can treat as a failed run. A script that skips a bad row and still writes a file produces a total that looks plausible and is wrong.
The checks also catch quieter cases. A number exported as text, such as "1,500", fails is_number. An inference from the openpyxl note on data_only: a formula in a file never saved by a spreadsheet application may have no stored value and come back as None. The same check reports it.
Run the script on the corrected file and compare the CSV with the hand-checked table. The CSV writes plain numbers, such as 1.5 instead of 1.50, but the values should match. Workflow tools such as Make or n8n can move the file and start the run, but the checks in the script still decide whether the result is safe to report. For data pulled from an API instead of a spreadsheet, the same pattern appears in this GA4 Data API reporting script.
