The manual process we replace
Every Monday, a sales assistant opens one Excel file per region, copies the rows into a master workbook, builds a pivot table and adjusts the formatting. It takes about an hour and errors creep in when a file has an extra column. The script below does the same thing in seconds and always produces the same structure.
Step 1: combine all regional files
glob finds every file in the folder; we tag each row with its source file to keep traceability.
from pathlib import Path
import pandas as pd
frames = []
for f in Path("regions").glob("*.xlsx"):
d = pd.read_excel(f)
d["source"] = f.stem
frames.append(d)
data = pd.concat(frames, ignore_index=True)Step 2: build the summary
pivot_table reproduces the Excel pivot table, including grand totals with margins=True.
summary = data.pivot_table(
index="salesperson", columns="source", values="amount",
aggfunc="sum", fill_value=0, margins=True, margins_name="Total",
)Step 3: write and format with openpyxl
Write both sheets, then reopen the workbook with openpyxl to bold the header, apply a currency format and freeze the first row so the report is readable without manual work.
from openpyxl import load_workbook
from openpyxl.styles import Font
out = "weekly_report.xlsx"
with pd.ExcelWriter(out) as w:
summary.to_excel(w, sheet_name="Summary")
data.to_excel(w, sheet_name="Detail", index=False)
wb = load_workbook(out)
ws = wb["Summary"]
for cell in ws[1]:
cell.font = Font(bold=True)
for row in ws.iter_rows(min_row=2, min_col=2):
for cell in row:
cell.number_format = '#,##0.00 €'
ws.freeze_panes = "B2"
wb.save(out)Step 4: schedule it
On Windows, create a Task Scheduler entry that runs python report.py every Monday at 8:00; on macOS or Linux, use a cron line such as 0 8 * * 1. Add a try/except that writes errors to a log file so a missing file does not fail silently.
Going further
Validate column names before concatenating (raise an error if a regional file is missing "amount"), and send the report automatically by email with smtplib. If your files have a different layout, describe it to FormulAI and ask for the adapted script.