User story: new-hire onboarding tracker

Per-hire onboarding workbook

from openpyxl import load_workbook
from datetime import date, timedelta
import pandas as pd

template = "onboarding_template.xlsx"
hires = pd.read_excel("new_hires_this_week.xlsx")

for _, h in hires.iterrows():
    wb = load_workbook(template)
    ws = wb["Checklist"]
    ws["B2"] = h["name"]
    ws["B3"] = h["start_date"]
    ws["B4"] = h["manager"]
    ws["B5"] = h["department"]

    # Auto-fill due dates
    items = pd.read_excel(template, sheet_name="ItemDefs")
    for i, item in items.iterrows():
        row = 8 + i
        ws.cell(row=row, column=1, value=item["item"])
        ws.cell(row=row, column=2, value=pd.Timestamp(h["start_date"]) + pd.Timedelta(days=item["due_offset"]))

    wb.save(f"onboarding/{h['name'].replace(' ', '_')}.xlsx")

Master tracker — read every per-hire workbook, summarise

from pathlib import Path
rows = []
for p in Path("onboarding").glob("*.xlsx"):
    wb = load_workbook(p, data_only=True)
    ws = wb["Checklist"]
    name = ws["B2"].value
    for r in ws.iter_rows(min_row=8, values_only=True):
        item, due, done, owner = r[:4]
        if item:
            rows.append({"hire": name, "item": item, "due": due, "done": done, "owner": owner})

tracker = pd.DataFrame(rows)
tracker["overdue"] = (~tracker["done"].fillna(False)) & (tracker["due"] < pd.Timestamp.now())
print(f"Overdue items: {tracker['overdue'].sum()}")
tracker.to_excel("onboarding_master.xlsx", index=False)