Mini project: monthly report generator script

Concept

1. The scenario

Every branch sends a CSV of its sales. At month end you get a folder like:

data/2026-09/
    north.csv
    south.csv
    west.csv

Each file has: Date, Salesperson, Region, Product, Amount. Dates are Indian format (15/09/2026). Some rows have problems: extra spaces, lowercase names, a bad date, "NA" in Amount, a duplicate entry.

2. What the script produces

Sales_Report_2026-09.xlsx with sheets:

Sheet	Contents
By Region	total per region, highest first
By Salesperson	orders and total per person
Region x Product	pivot table
Clean Data	all valid rows, with the source file name
Rejected Rows	rows with a bad date or amount, with the original text, for someone to fix

Every sheet gets a styled header, number and date formats, column widths and frozen header row.

3. Project structure

monthly-report/
    .venv/
    data/2026-09/*.csv
    monthly_report.py
    requirements.txt      ← pandas, openpyxl

4. The script

"""
monthly_report.py
Combine all CSV files in a folder, clean them, summarise, and write a formatted Excel report.

Usage:
    python monthly_report.py --input data/2026-09 --output Sales_Report_2026-09.xlsx
"""
import argparse
from pathlib import Path

import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import Font, PatternFill, Alignment
from openpyxl.utils import get_column_letter

TEXT_COLS = ["Salesperson", "Region", "Product"]


def load_data(folder: Path, files: list[Path] | None = None) -> pd.DataFrame:
    files = sorted(folder.glob("*.csv")) if files is None else files
    if not files:
        raise FileNotFoundError(f"No CSV files found in {folder}")
    frames = []
    required = {"Date", "Salesperson", "Region", "Product", "Amount"}
    for f in files:
        frame = pd.read_csv(f, keep_default_na=False, dtype=str)
        frame.columns = frame.columns.str.strip()
        missing = required - set(frame.columns)
        if missing:
            raise ValueError(f"{f.name}: missing columns: {', '.join(sorted(missing))}")
        frames.append(frame.assign(SourceFile=f.name))
    return pd.concat(frames, ignore_index=True)


def clean(df: pd.DataFrame) -> tuple[pd.DataFrame, pd.DataFrame]:
    df = df.copy()
    df.columns = df.columns.str.strip()
    dates = pd.to_datetime(df["Date"], format="%d/%m/%Y", errors="coerce")
    amounts = pd.to_numeric(df["Amount"], errors="coerce")

    bad = dates.isna() | amounts.isna() | amounts.isin([float("inf"), float("-inf")])
    rejected = df[bad].copy()                                   # keep the original text for review
    for col in TEXT_COLS:
        df[col] = df[col].astype(str).str.strip().str.title()
    good = (df[~bad]
            .assign(Date=dates[~bad], Amount=amounts[~bad])
            .drop_duplicates(subset=["Date", "Salesperson", "Region", "Product", "Amount"]))
    return good, rejected


def summarise(df: pd.DataFrame) -> dict[str, pd.DataFrame]:
    by_region = (df.groupby("Region", as_index=False)["Amount"].sum()
                   .sort_values("Amount", ascending=False))
    by_person = (df.groupby("Salesperson", as_index=False)
                   .agg(Orders=("Amount", "count"), Total=("Amount", "sum"))
                   .sort_values("Total", ascending=False))
    pivot = df.pivot_table(index="Region", columns="Product", values="Amount",
                           aggfunc="sum", fill_value=0)
    return {"By Region": by_region, "By Salesperson": by_person, "Region x Product": pivot}


def write_report(clean_df, rejected, summaries, out_path: Path) -> None:
    out_path.parent.mkdir(parents=True, exist_ok=True)
    with pd.ExcelWriter(out_path, engine="openpyxl") as writer:
        for name, frame in summaries.items():
            frame.to_excel(writer, sheet_name=name, index=(name == "Region x Product"))
        clean_df.to_excel(writer, sheet_name="Clean Data", index=False)
        rejected.to_excel(writer, sheet_name="Rejected Rows", index=False)
        # CSV text must remain text, even when it starts with an equals sign.
        for ws in writer.book.worksheets:
            for row in ws:
                for cell in row:
                    if isinstance(cell.value, str):
                        cell.data_type = "s"


def format_report(path: Path) -> None:
    wb = load_workbook(path)
    header_fill = PatternFill("solid", start_color="1F4E78")
    for ws in wb.worksheets:
        for cell in ws[1]:
            cell.font = Font(bold=True, color="FFFFFF")
            cell.fill = header_fill
            cell.alignment = Alignment(horizontal="center")
        for col_cells in ws.columns:
            length = max(len(str(c.value)) if c.value is not None else 0 for c in col_cells)
            ws.column_dimensions[get_column_letter(col_cells[0].column)].width = min(length + 3, 45)
        for row in ws.iter_rows(min_row=2):
            for c in row:
                if isinstance(c.value, (int, float)):
                    c.number_format = "#,##0"
                elif c.is_date:
                    c.number_format = "dd-mmm-yyyy"
        ws.freeze_panes = "A2"
    wb.save(path)


def main() -> None:
    parser = argparse.ArgumentParser(description="Build the monthly sales report.")
    parser.add_argument("--input", required=True, help="Folder containing CSV files")
    parser.add_argument("--output", required=True, help="Excel file to create")
    args = parser.parse_args()

    raw = load_data(Path(args.input))
    good, rejected = clean(raw)
    if good.empty:
        raise ValueError("No valid rows; input files retained for review")
    summaries = summarise(good)
    out = Path(args.output)
    write_report(good, rejected, summaries, out)
    format_report(out)
    print(f"Rows read: {len(raw)} | clean: {len(good)} | rejected: {len(rejected)}")
    print(f"Report saved: {out.resolve()}")


if __name__ == "__main__":
    main()

5. How it's organised

One function per job — load_data, clean, summarise, write_report, format_report. Easy to test and change one part.
clean never silently drops bad data. Rows that fail date or amount parsing go to Rejected Rows with their original text, so the problem is visible and fixable.
format="%d/%m/%Y" makes sure 05/09/2026 is 5 September, never 9 May.
Duplicates are removed on the business columns, not on SourceFile (the same sale sent in two files is still a duplicate).
argparse lets you run any month without editing code.

6. Run it

python monthly_report.py --input data/2026-09 --output Sales_Report_2026-09.xlsx

Output:

Rows read: 10 | clean: 7 | rejected: 2
Report saved: C:\...\monthly-report\Sales_Report_2026-09.xlsx

(With the sample data in this lesson: 10 rows read, one duplicate removed, one bad date and one "NA" amount rejected.)

7. Extend it (ideas)

Add a Summary sheet with a bar chart (Lesson 4).
Add a month-over-month column by loading last month's report.
Log each run to a text file with Python's logging module.
In the Scheduling module, we'll run this automatically and email the result.

Common mistakes

Running from the wrong folder (use full paths or cd into the project). Forgetting to activate the virtual environment. Column names in the CSV that differ slightly ("Amount " with a space — the script strips them, but "Amt" would break it; add a rename step if branches differ).

What you'll learn