How to automate a weekly Excel report with Python

By Shenzee Tech · Published 14 October 2026 · 2 min read

If someone in your team spends an hour every week copying data into the same spreadsheet, a short Python script can do it in seconds and make the same result every time.

When automation is worth it

• The report is built the same way every time, from the same kind of source file.
• The steps are copying, filtering, adding up and formatting, not judgement calls.
• Mistakes in the manual version cost you time or money.

What you need

• Python 3 installed on the computer that will run the report.
• The pandas and openpyxl packages: run pip install pandas openpyxl.
• A source file with the same columns every week, such as a CSV exported from your sales or accounts system.

A working example

This script reads a sales export, adds up sales by month and category, and writes an Excel file with a summary sheet and the raw data. It assumes your file has the columns date, category and amount.

import pandas as pd
from datetime import date

# 1. Read the weekly export
sales = pd.read_csv("sales_export.csv", parse_dates=["date"])

# 2. Check the columns we rely on are there
missing = {"date", "category", "amount"} - set(sales.columns)
if missing:
    raise SystemExit(f"The export is missing columns: {sorted(missing)}")

# 3. Build the summary
sales["month"] = sales["date"].dt.to_period("M").astype(str)
summary = (
    sales.groupby(["month", "category"], as_index=False)["amount"]
    .sum()
    .sort_values(["month", "category"])
)

# 4. Write the Excel report
filename = f"weekly_report_{date.today():%Y-%m-%d}.xlsx"
with pd.ExcelWriter(filename, engine="openpyxl") as writer:
    summary.to_excel(writer, sheet_name="Summary", index=False)
    sales.to_excel(writer, sheet_name="Data", index=False)

print("Saved", filename)

Run it automatically

On Windows, Task Scheduler can run the script every Monday morning. Create a basic task, choose a weekly trigger, and point the action at python with the full path to your script. On Mac and Linux, cron does the same job.

Habits that keep it reliable

• Check the input before using it, as the script above does, so a changed export fails loudly instead of producing a wrong report.
• Keep the script and a sample input file together so anyone can run it.
• Never hard-code passwords or keys in the script. Read them from environment variables.
• Compare the first few automated reports against the manual version before you rely on it.

Automation saves the most time on reports that are boring and identical. If the rules change often, a well-built spreadsheet or a small web app may suit you better.