An automation recipe book

1 — Add today's date to every filename in a folder

from pathlib import Path
from datetime import date
stamp = date.today().isoformat()
for p in Path("inbox").glob("*.xlsx"):
    p.rename(p.with_name(f"{stamp}_{p.name}"))

2 — Find every sheet across a folder of workbooks containing a keyword

from openpyxl import load_workbook
matches = []
for p in Path("archive").rglob("*.xlsx"):
    wb = load_workbook(p, read_only=True, data_only=True)
    for ws in wb.worksheets:
        for row in ws.iter_rows(values_only=True):
            if any(c == "URGENT" for c in row if isinstance(c, str)):
                matches.append((p.name, ws.title))
                break
print(matches)

3 — Roll a year of weekly reports into one DataFrame

import pandas as pd
frames = [pd.read_excel(p) for p in Path("weekly_2026").glob("week_*.xlsx")]
pd.concat(frames, ignore_index=True).to_excel("weekly_2026_combined.xlsx", index=False)

4 — Find rows that exist this month but didn't last month (new customers)

this_m = set(pd.read_excel("apr.xlsx")["email"])
last_m = set(pd.read_excel("mar.xlsx")["email"])
new = this_m - last_m
print(f"{len(new)} new customers")

5 — Convert all CSVs in a folder to Excel

for p in Path(".").glob("*.csv"):
    pd.read_csv(p).to_excel(p.with_suffix(".xlsx"), index=False)

6 — Rename a sheet across every workbook in a folder

for p in Path("reports").glob("*.xlsx"):
    wb = load_workbook(p)
    if "Sheet1" in wb.sheetnames:
        wb["Sheet1"].title = "Data"
        wb.save(p)

7 — Email the boss a CSV every Monday morning

# See Lesson 5 — combine with Lesson 6 scheduling.

8 — Build a daily PDF dashboard

import matplotlib.pyplot as plt
fig, ax = plt.subplots(figsize=(8, 5))
df.groupby("region")["amount"].sum().plot(kind="bar", ax=ax)
ax.set_title(f"Revenue {date.today():%Y-%m-%d}")
fig.savefig(f"dashboards/{date.today():%Y-%m-%d}.pdf")