How to Automate Excel Reports With Python, Schedule Them and Email Them

To automate Excel reports with Python, write one script that pulls the data from its source, cleans and summarises it with pandas, writes a formatted workbook with openpyxl or XlsxWriter, and emails or uploads the file. Then schedule that script with cron or Task Scheduler so the report arrives on its own every day or week. Add logging and a failure alert, and the report keeps running without anyone touching it.

This guide walks through each step with short, working code, then covers the optional AI summary, the mistakes that break report automations and when it makes sense to hire a developer.

What a report automation actually does

Almost every recurring report follows the same four steps, whether it is weekly sales, stock levels or marketing spend:

1. 1Collect the data from exports, databases, APIs or shared spreadsheets.
2. 2Process it: clean, filter, join and summarise.
3. 3Build a workbook people want to open, with the right sheets, totals and formatting.
4. 4Deliver it by email, shared drive or chat, on a fixed schedule.

If someone on your team does these four steps by hand every week, the whole thing can usually be replaced by one script.

The tools you need

• Python 3 on the machine or server that will run the report.
• pandas for reading, cleaning and summarising data.
• openpyxl for writing and formatting .xlsx files, or editing an existing template.
• XlsxWriter (optional) when you need native Excel charts and conditional formatting on new files.
• A scheduler: cron, Windows Task Scheduler or a scheduled job on a server.

Install them with pip install pandas openpyxl. Excel itself is not needed: the script writes the file directly.

Step 1: Pull the data from where it lives

Start from the source, not from a spreadsheet someone exported by hand. Typical sources are:

• CSV or Excel exports dropped into a shared folder by another system.
• A database such as PostgreSQL or MySQL, read with pandas.read_sql.
• An API from your CRM, shop or ad platform, read with requests and turned into a DataFrame.
• Google Sheets, read through the Sheets API.

The closer the script reads to the original source, the fewer manual steps remain to break.

Step 2: Clean and summarise with pandas

Here is a weekly sales summary built from an exported orders file:

import pandas as pd

orders = pd.read_csv("exports/orders.csv", parse_dates=["order_date"])
week_start = pd.Timestamp.today().normalize() - pd.Timedelta(days=7)
last_week = orders[orders["order_date"] >= week_start]

summary = (
    last_week.groupby("region", as_index=False)
    .agg(orders=("order_id", "count"), revenue=("amount", "sum"))
    .sort_values("revenue", ascending=False)
)

Do the checks here too: drop duplicate orders, make sure required columns exist and stop with a clear error if the file is empty. A report that fails loudly is far better than one that quietly shows wrong numbers.

Step 3: Build a workbook people want to open

A raw data dump is not a report. Give each audience its own sheet, freeze the header row and size the columns:

from datetime import date

path = f"reports/weekly-sales-{date.today():%Y-%m-%d}.xlsx"
with pd.ExcelWriter(path, engine="openpyxl") as writer:
    summary.to_excel(writer, sheet_name="Summary", index=False)
    last_week.to_excel(writer, sheet_name="Orders", index=False)

    sheet = writer.sheets["Summary"]
    sheet.freeze_panes = "A2"
    for column in sheet.columns:
        width = max(len(str(cell.value or "")) for cell in column) + 2
        sheet.column_dimensions[column[0].column_letter].width = width

If your team already has a branded layout, keep it: open the template with openpyxl, write the numbers into its cells and save a dated copy. People keep the report they know, and you remove the manual work behind it.

Step 4: Run it on a schedule

Once the script works, it should run on its own. On Linux or macOS, one cron line runs it every Monday at 7:00:

0 7 * * 1 /usr/bin/python3 /opt/reports/weekly_sales.py >> /var/log/weekly_sales.log 2>&1

If cron syntax is new to you, the free cron expression generator builds and explains the schedule in plain English. On Windows, Task Scheduler does the same job.

Whatever runs it, log every run and send an alert when it fails. A scheduled report nobody watches can be broken for weeks before anyone notices.

Step 5: Deliver it automatically

Email is still the most common delivery. Python's standard library is enough:

import os
import smtplib
from email.message import EmailMessage

msg = EmailMessage()
msg["Subject"] = f"Weekly sales report, {date.today():%d %b %Y}"
msg["From"] = "reports@yourcompany.com"
msg["To"] = "team@yourcompany.com"
msg.set_content("The weekly sales report is attached.")
with open(path, "rb") as f:
    msg.add_attachment(f.read(), maintype="application", subtype="octet-stream", filename=os.path.basename(path))

with smtplib.SMTP_SSL("smtp.yourcompany.com", 465) as smtp:
    smtp.login("reports@yourcompany.com", os.environ["SMTP_PASSWORD"])
    smtp.send_message(msg)

Keep the password in an environment variable, never in the script. The same file can also go to a shared drive, a Google Sheet or a Slack channel instead.

Optional: add an AI-written summary

Most people read the email, not the attachment. An LLM such as Claude or GPT can turn the summary table into three short sentences: what went up, what went down and what needs attention. Two rules keep this reliable:

• Python calculates every number; the AI only describes them. Pass it the finished summary table, never the raw data.
• Check the output in code before sending, for example that every figure it mentions appears in the table.

This is the kind of narrow, well-defined AI step that works well in production. My AI workflow automation page explains how I build these safely.

Mistakes that break report automations

• Reading from a file someone edits by hand. Read from the source system, or lock the input format.
• No validation. Check row counts, required columns and totals before the report goes out.
• Hard-coded dates and paths. Calculate the reporting period in code so the script never needs editing.
• Silent failures. Log every run and alert someone when it fails.
• Passwords in the code. Use environment variables or a secrets manager.

Do it yourself or hire a developer?

A single report from one clean export is a good weekend project with the code above. It is worth bringing in a developer when the report pulls from several systems or APIs, needs to run on a server reliably, feeds other tools, or replaces hours of work every week where errors are costly.

To see whether it pays off, compare the build cost with the time the report takes today. The free automation ROI calculator does the maths, and the guide on automating repetitive business tasks helps you spot what else is worth automating.

Next steps

If your team spends hours every week building the same Excel reports, describe the report and where the data comes from, and I will tell you how I would automate it, how long it would take and what it would cost. You can see the full scope of this work on my business process automation page.