Automating Excel reports with Python: a practical example

In many companies the same scene repeats every Monday: someone downloads exports from the accounting software, web shop or till system, opens five Excel files, copies columns, fixes dates, builds a pivot table and emails the report to the boss. Two hours of work — every week, with a real chance of mistakes.

That job is a perfect candidate for automation. Below is a concrete example of a Python script that does it.

What we will automate

Example: a company has several branches and receives sales exports from its software in CSV and Excel format. The goal is one Excel file with three sheets:

• By branch — total sales per branch, sorted from highest;
• By week — sales by week and branch;
• All data — the cleaned, merged data for further checks.

What you need

• Python (free, from python.org),
• the pandas (table processing) and openpyxl (writing .xlsx files) libraries, installed with one command: pip install pandas openpyxl,
• a folder with the exports, e.g. exports/.

The complete code example

from pathlib import Path
from datetime import date
import pandas as pd

INPUT = Path("exports")     # folder with exports from your software
OUTPUT = Path("reports")    # the finished report is created here
OUTPUT.mkdir(exist_ok=True)

# 1) Load all exports (CSV and Excel) into one table
parts = []
for file in sorted(INPUT.glob("*")):
    if file.suffix == ".csv":
        df = pd.read_csv(file, sep=";", decimal=",", encoding="utf-8")
    elif file.suffix in (".xlsx", ".xls"):
        df = pd.read_excel(file)
    else:
        continue
    df["Source"] = file.name
    parts.append(df)
sales = pd.concat(parts, ignore_index=True)

# 2) Clean the data
sales["Date"] = pd.to_datetime(sales["Date"], dayfirst=True)
sales["Branch"] = sales["Branch"].str.strip().str.title()
sales = sales.dropna(subset=["Amount"]).drop_duplicates()

# 3) Summaries: by branch and by week
by_branch = (sales.groupby("Branch", as_index=False)["Amount"]
             .sum().sort_values("Amount", ascending=False))
by_week = sales.pivot_table(index=sales["Date"].dt.isocalendar().week,
                            columns="Branch", values="Amount",
                            aggfunc="sum", fill_value=0)

# 4) Write the finished report — each table on its own sheet
file = OUTPUT / f"sales_{date.today():%Y-%m-%d}.xlsx"
with pd.ExcelWriter(file, engine="openpyxl") as xls:
    by_branch.to_excel(xls, sheet_name="By branch", index=False)
    by_week.to_excel(xls, sheet_name="By week")
    sales.to_excel(xls, sheet_name="All data", index=False)

print("Report ready:", file)

What the script does, step by step

1. Merging exports

The script goes through every file in the folder, reads CSV and Excel files and merges them into one table. The Source column remembers which file each row came from — useful when tracking down an error. The sep=";" and decimal="," parameters are there because exports from European software often use semicolons and decimal commas.

2. Cleaning the data

Dates in day.month.year format become real dates, branch names are normalised (spaces, upper and lower case), and empty and duplicate rows are removed. This is exactly where manual work most often goes wrong.

3. Summaries

groupby totals sales per branch and pivot_table builds a table by week — the same as a pivot table in Excel, just without the clicking.

4. A finished Excel file

The result is an ordinary .xlsx file with the date in its name, ready to open, send or edit further. With openpyxl you can also add formatting, column widths, charts or automatic emailing.

Running it automatically

A script is most valuable when it runs by itself:

• Windows: in Task Scheduler create a task that runs python report.py every Monday at 7:00.
• Linux server: one line in cron, e.g. 0 7 * * 1 python3 /path/report.py.

When the employee arrives at work, the report is already waiting in the folder or inbox.

When automation pays off — and when it doesn’t

It pays off when the same work repeats (weekly or daily), involves several data sources and errors are costly. The maths is simple: two hours a week is about 100 hours a year.

It doesn’t pay off for a report produced once a year or whose format keeps changing. And for simple cases inside Excel itself, take a look at Power Query — a built-in tool that can merge and clean tables without coding.

What next

This example is deliberately simple. In real projects the script often fetches data directly from the software through an API or database, emails the report, flags unusual values or feeds a dashboard viewed in the browser. See what the web apps and automation service includes, and for manufacturers, examples of software for production.

Engimotive · Published October 27, 2026