Automate your weekly Excel report with Python and pandas

If you build the same spreadsheet report every week, Python can do it for you. In this walkthrough we'll read raw sales data, summarise it, and write a clean Excel report automatically.

What you need

pip install pandas openpyxl

Our example input is sales.xlsx with the columns Date, Region, Product, Units and Revenue.

Step 1: Read and clean the data

import pandas as pd

df = pd.read_excel("data/sales.xlsx")
df["Date"] = pd.to_datetime(df["Date"])
df = df.dropna(subset=["Revenue"])

Step 2: Summarise

by_region = (df.groupby("Region", as_index=False)
               .agg(Units=("Units", "sum"), Revenue=("Revenue", "sum"))
               .sort_values("Revenue", ascending=False))

top_products = (df.groupby("Product")["Revenue"].sum()
                  .nlargest(5).reset_index())

Step 3: Write a multi-sheet report

with pd.ExcelWriter("output/weekly_report.xlsx") as xl:
    by_region.to_excel(xl, sheet_name="By region", index=False)
    top_products.to_excel(xl, sheet_name="Top products", index=False)
    df.to_excel(xl, sheet_name="Raw data", index=False)

print("Report saved!")

Step 4: Run it automatically

• Windows: use Task Scheduler to run python report.py every Monday at 8am.
• Mac/Linux: add a cron job, e.g. 0 8 * * 1 python /path/report.py.

Where to go next

Add a chart with matplotlib, email the report automatically, or pull data straight from a database. Each is a small step once the basics work.