Generating Automated Reports

What a report pipeline looks like

1. Collect data (CSV, API, DB)
2. Clean/aggregate
3. Render output (Excel/Docx/PDF)
4. Distribute (email/Slack)
5. Schedule (cron/schedule)

Mini example: CSV -> Excel

import csv
from openpyxl import Workbook
 
wb = Workbook()
ws = wb.active
 
with open("input.csv", newline="", encoding="utf-8") as f:
    reader = csv.reader(f)
    for row in reader:
        ws.append(row)
 
wb.save("output.xlsx")

Tips

• keep configuration separate (YAML/JSON)
• add logging + error handling
• store outputs with timestamps

Splitting the pipeline into functions

A reporting script is easier to fix and reuse when each stage is its own function. The stages from the list above become functions, and a small main connects them.

import csv
from collections import defaultdict
from datetime import date
from pathlib import Path
from openpyxl import Workbook
 
 
def load_rows(path: Path) -> list[dict]:
    with path.open(newline="", encoding="utf-8") as f:
        return list(csv.DictReader(f))
 
 
def summarise(rows: list[dict]) -> dict[str, float]:
    totals = defaultdict(float)
    for row in rows:
        totals[row["region"]] += float(row["amount"])
    return dict(totals)
 
 
def write_excel(totals: dict[str, float], out: Path) -> None:
    wb = Workbook()
    ws = wb.active
    ws.title = "Summary"
    ws.append(["Region", "Total"])
    for region, total in sorted(totals.items()):
        ws.append([region, round(total, 2)])
    wb.save(out)
 
 
def main() -> None:
    rows = load_rows(Path("sales.csv"))
    totals = summarise(rows)
    out = Path("reports") / f"sales_summary_{date.today():%Y-%m-%d}.xlsx"
    out.parent.mkdir(exist_ok=True)
    write_excel(totals, out)
    print("wrote", out)
 
 
if __name__ == "__main__":
    main()

Notice what each function does not do. summarise never touches a file, so you can test it with a small list of dictionaries. write_excel does not know where the data came from. If next month the input is an API instead of a CSV file, you replace load_rows and nothing else changes.

Distribute and schedule

The last two stages are what make it automation. After write_excel, send the file by email or post a link to Slack (both are covered in the communication phase). Then run the script on a schedule with cron, Task Scheduler, or the schedule library (Phase 6). A report that has to be started by hand is only a script.

Common mistakes

• No dated output name. Without a date, each run overwrites the last one and you cannot compare weeks.
• Silently skipping bad rows. If float(row["amount"]) fails, decide on purpose: log the row and continue, or stop. Do not swallow the error.
• Hardcoding file names and recipients. Move them into a config file or command-line arguments.
• Mixing the stages in one long loop. Then any change touches everything.
• Not checking the input. An empty CSV file produces an empty output that looks fine. Check the row count and fail loudly.

Adapting it

Add a chart with openpyxl, a Word summary with python-docx, or a second sheet with the raw rows. Keep the same structure: load, summarise, render, deliver.

🧪 Try It Yourself

Exercise 1 – List Files with os.listdir

Press Run to see what your code prints.

Exercise 2 – Join Paths with os.path.join

Press Run to see what your code prints.

Exercise 3 – Write and Read a File

Press Run to see what your code prints.