Automate Excel Reports with Python

You can automate an Excel report with Python by writing one script that reads your raw exports, calculates the summary, writes a formatted .xlsx file and runs on a schedule. The pandas library handles the data, openpyxl handles the Excel formatting, and your operating system's scheduler runs it every week without you opening a single file.

Why automate Excel reports with Python?

Many weekly reports follow the same routine: download a few CSV files, paste them into a workbook, refresh a pivot table, fix the formatting and email the result. It is repetitive, and every manual copy-and-paste is a chance to make a mistake. A Python script does the same steps identically every time, keeps a written record of the logic, and finishes in seconds.

Python is not the only automation option, though. Before writing code, it helps to see where it fits:

If your report only needs cleaning and combining, start with Power Query in Excel. When you need the whole process to run unattended, Python is the natural next step.

Step 1: Set up the project and load your data

Create the project folder

1. Install Python 3 and run pip install pandas openpyxl in a terminal.
2. Create a folder such as weekly_report with two subfolders: data for raw exports and output for finished reports.
3. Save your exports with a consistent name pattern, for example sales_2026-10-19.csv. A predictable pattern is what lets the script find the files on its own.
4. Create an empty file called weekly_report.py. All the code below goes into that one file.

In this example, each CSV has the columns date, account, product, units and revenue. Adjust the names to match your own exports. If you prefer to test each step interactively first, build it in a notebook as shown in our Jupyter Notebooks guide and then copy the working code into the script.

Load and combine every export

This block finds every file that matches the pattern, stacks them into one table, removes exact duplicate rows (useful when two exports overlap) and adds a week column with the Monday that starts each week.

from pathlib import Path
import pandas as pd

DATA = Path("data")
OUT = Path("output")
OUT.mkdir(exist_ok=True)

files = sorted(DATA.glob("sales_*.csv"))
sales = pd.concat(
    (pd.read_csv(f, parse_dates=["date"]) for f in files),
    ignore_index=True,
).drop_duplicates()

sales["week"] = sales["date"].dt.to_period("W").dt.start_time

Step 2: Build the summary

Most sales reports answer a simple question: how did each account do this week compared to last week? The code below creates a pivot table with one row per account and one column per week, then calculates the percentage change.

this_week = sales["week"].max()
prev_week = this_week - pd.Timedelta(weeks=1)

summary = (
    sales[sales["week"].isin([prev_week, this_week])]
    .pivot_table(index="account", columns="week", values="revenue",
                 aggfunc="sum", fill_value=0)
    .reindex(columns=[prev_week, this_week], fill_value=0)
)
summary.columns = ["prev_week", "this_week"]
summary["change_pct"] = (
    summary["this_week"] / summary["prev_week"] - 1
).where(summary["prev_week"] > 0)
summary = summary.sort_values("this_week", ascending=False).reset_index()

Two details make this robust. The reindex line guarantees both week columns exist even if an account had no sales last week, and .where(...) leaves the change empty instead of dividing by zero. These small safeguards are what separate a script you trust from one you have to babysit. For more on reshaping data like this, see our pandas tutorial for Excel users.

Step 3: Write and format the Excel file

Next, write the summary and the detailed data to two sheets. The file name includes the week, so old reports are never overwritten.

report = OUT / f"weekly_sales_{this_week:%Y-%m-%d}.xlsx"

with pd.ExcelWriter(report, engine="openpyxl") as writer:
    summary.to_excel(writer, sheet_name="Summary", index=False)
    sales.to_excel(writer, sheet_name="Detail", index=False)

A raw export looks unfinished, so the last step applies formatting with openpyxl: a colored header, currency and percentage formats, frozen top row and a wider first column.

from openpyxl import load_workbook
from openpyxl.styles import Font, PatternFill

wb = load_workbook(report)
ws = wb["Summary"]

for cell in ws[1]:
    cell.font = Font(bold=True, color="FFFFFF")
    cell.fill = PatternFill("solid", fgColor="1F4E78")

for row in ws.iter_rows(min_row=2, min_col=2, max_col=3):
    for cell in row:
        cell.number_format = '"$"#,##0'

for cell in ws["D"][1:]:
    cell.number_format = "0.0%"

ws.freeze_panes = "A2"
ws.column_dimensions["A"].width = 28
wb.save(report)
print(f"Report saved: {report}")

Run the script with python weekly_report.py. Open the file in the output folder and compare a couple of account totals with your manual report. Checking against a number you already trust is the single most important test.

Step 4: Run it on a schedule

Windows: Task Scheduler

1. Open Task Scheduler and choose Create Basic Task.
2. Pick a trigger, for example weekly on Monday at 7:00 a.m.
3. For the action, choose Start a program. In Program/script, enter the full path to python.exe; in Add arguments, enter weekly_report.py; in Start in, enter your project folder.
4. Save it, then right-click the task and choose Run to test it immediately.

macOS and Linux: cron

Run crontab -e and add a line like this (adjust the paths):

# Every Monday at 7:00 a.m.
0 7 * * 1 cd /home/me/weekly_report && /usr/bin/python3 weekly_report.py

Whichever scheduler you use, the computer has to be on at that time. If that is a problem, the same script can run on a server or a cloud machine later.

Want the report emailed too? Python's built-in smtplib and email modules can attach the file and send it, and in Microsoft environments many teams let Power Automate pick up the new file from a shared folder instead. Follow your company's IT rules for sending email from scripts, and never store passwords in plain text inside the code.

Common problems and how to avoid them

• Column names change. If a system renames Revenue to Net Revenue, the script stops. Add a check at the top that lists the required columns and prints a clear message when one is missing.
• Files are open. On Windows, writing to a workbook that is open in Excel fails with a permission error. Since every report has a new dated name, this rarely happens, but close old files before testing.
• Wrong working folder. Schedulers often start in a different directory. Setting the Start in folder (or the cd in cron) fixes most "file not found" errors.
• Silent failures. Write a short log line with the date and row count each run, so you notice when a week was skipped.

Next steps

Pick the report you rebuild most often, list its manual steps on paper, and translate them into the four stages above: load, summarize, write and schedule. Start with only the summary sheet, confirm the numbers match your manual version for two or three weeks, then add formatting and scheduling. If you are still designing the report itself, our guide to building a weekly sales report in Excel covers which metrics and layout to include.

Watch: video lessons

▶

Frequently asked questions