Free reporting: automate your weekly Excel report with Python

First, put a number on the manual work

Before automating, find out what the manual reports actually cost. Multiply the hours per report by the people involved and by how often it repeats, then by a fully loaded hourly cost. The Manual Reporting Cost tool does this in your browser in two minutes.

What you need (all free)

After installing Python, run pip install pandas openpyxl once in a terminal.

The 25-line report

The example uses a weekly sales export with Date, Region, Product, Units and Revenue columns. The goal is a workbook with monthly revenue by region and the top 10 products. Rename the columns to match your own export.

import pandas as pd

# 1) Read
df = pd.read_excel("sales.xlsx")
# CSV instead: df = pd.read_csv("sales.csv")

# 2) Clean
df["Date"] = pd.to_datetime(df["Date"])
df = df.dropna(subset=["Revenue"]).drop_duplicates()

# 3) Summarize
df["Month"] = df["Date"].dt.to_period("M").astype(str)
by_region = df.pivot_table(index="Region", columns="Month", values="Revenue", aggfunc="sum", fill_value=0)
top10 = (
    df.groupby("Product", as_index=False)["Revenue"].sum()
    .sort_values("Revenue", ascending=False)
    .head(10)
)

# 4) Write
with pd.ExcelWriter("weekly_report.xlsx") as writer:
    by_region.to_excel(writer, sheet_name="Region x Month")
    top10.to_excel(writer, sheet_name="Top 10 products", index=False)

Read

read_excel loads the workbook into a table (a DataFrame) ^[2]; read_csv does the same for CSV files and lets you set the separator, decimal mark and encoding when an older system exports something unusual ^[3].

Clean

Parse dates, drop rows without revenue and remove exact duplicates. Real data needs more: the same customer spelled three ways, mixed units, gaps in the history. Run your file through the free Data Quality Check to see what's there; it runs entirely in your browser.

Summarize

pivot_table is the pandas version of an Excel PivotTable: regions in rows, months in columns, summed revenue in the cells ^[1]. groupby totals revenue per product, which is then sorted and cut to the top 10.

Write

ExcelWriter puts both tables on separate sheets of one workbook ^[4]. The report now comes out identical, and without copy-paste errors, every time it runs.

Run it every week automatically

• Windows: create a task in Task Scheduler that runs python report.py every Monday at 08:00 ^[5].
• Linux and macOS: add 0 8 * * 1 python3 /path/report.py to your crontab; it runs the script every Monday at 08:00 ^[6].

When a script stops being enough

Once reports multiply and pull from several systems, the problem moves from code to data infrastructure: every report should read from one single source of truth with one definition per metric. That is the core of my reporting automation work, and the natural next step is a custom dashboard on top of it.