📎 This article includes 3 downloadable practice files ↓

📘 Python Beginner Course · Lesson 12 of 12

Here’s the job that makes Python pay for itself: every month, files land in a folder; someone copies them into one sheet, builds the same pivots and emails a report. This project automates all of it.

What the script does

1. Finds every sales_*.csv in an incoming folder.
2. Combines them, noting which file each row came from.
3. Calculates KPIs and a month × region table.
4. Writes one Excel report with three sheets, named with the date.

Combine a folder of files

import pandas as pd, pathlib
src = pathlib.Path("incoming")
files = sorted(src.glob("sales_*.csv"))
df = pd.concat([pd.read_csv(f, parse_dates=["date"]).assign(source=f.name) for f in files],
               ignore_index=True)
df["amount"] = df["qty"] * df["rate"]

(The practice script first splits sales.csv into monthly files to simulate them arriving.)

KPIs and summary

kpis = pd.DataFrame({"metric": ["Files", "Orders", "Total sales", "Average order"],
                     "value": [len(files), len(df), df["amount"].sum(), round(df["amount"].mean())]})
by_month = df.pivot_table(values="amount", index=df["date"].dt.strftime("%Y-%m"),
                          columns="region", aggfunc="sum", fill_value=0)

Write the report

import datetime as dt
out = f"sales_report_{dt.date.today():%Y%m%d}.xlsx"
with pd.ExcelWriter(out) as xw:
    kpis.to_excel(xw, sheet_name="KPIs", index=False)
    by_month.to_excel(xw, sheet_name="By month")
    df.to_excel(xw, sheet_name="All orders", index=False)

Add the openpyxl formatting from Lesson 11 to make it presentable.

Run it automatically

1. Open Task Scheduler › Create Basic Task.
2. Trigger: Monthly, day 2, 9:00 am.
3. Action: Start a program › Program: python (or the full path to python.exe) › Arguments: lesson-12.py › Start in: the script’s folder.

Ideas to extend it

• Email the report with Outlook (win32com) or smtplib; see sending email from Excel for the Outlook side.
• Move processed files to an archive folder so they aren’t counted twice.
• Read from a database with SQL (the SQL course) instead of CSVs: pd.read_sql(query, connection).

You’ve finished the course

You can install Python, write variables, decisions, loops and functions, handle files, clean and summarise data with pandas, produce formatted Excel files and automate a recurring report. Next: more Python automation, and Power Query for the no-code route.

Practice

Download the script (and data file, if listed) below into one folder. Open a terminal in that folder and run python lesson-NN.py. Compare with the expected output, then change something and run it again: that’s how Python is learned.