Project: the monthly close pack

The story

Month-end. Priya wants one script to run that produces the whole close pack: a cover sheet, reconciliation, variance, AR aging, FX-translated P&L. One file. One double-click.

The skeleton

"""monthly_close.py — generate the month-end close pack."""
from datetime import date
from pathlib import Path
import pandas as pd

# 1) Recon
def make_recon():
    # ... reuse code from Lesson 1
    return matched, gl_only, bank_only

# 2) Variance
def make_variance():
    # ... reuse code from Lesson 2
    return variance_df

# 3) AR aging
def make_aging():
    # ... reuse code from Lesson 5
    return aging_pivot

# 4) FX P&L
def make_pnl():
    # ... reuse code from Lesson 6
    return pnl_df

def make_cover(matched, gl_only, bank_only, variance, aging, pnl):
    return pd.DataFrame({
        "Metric": [
            "Period",
            "GL transactions",
            "Bank transactions",
            "GL only", "Bank only",
            "Material variances",
            "AR over 60 days",
            "Total revenue (USD)",
        ],
        "Value": [
            f"{date.today():%B %Y}",
            len(matched) + len(gl_only),
            len(matched) + len(bank_only),
            len(gl_only), len(bank_only),
            (variance["flag"] == "MATERIAL").sum(),
            aging["61-90"].sum() + aging["90+"].sum(),
            pnl.loc["Total", "Total"],
        ],
    })

def main():
    matched, gl_only, bank_only = make_recon()
    variance = make_variance()
    aging    = make_aging()
    pnl      = make_pnl()
    cover    = make_cover(matched, gl_only, bank_only, variance, aging, pnl)

    out = f"close_pack_{date.today():%Y-%m}.xlsx"
    with pd.ExcelWriter(out) as w:
        cover.to_excel(w,    sheet_name="Cover",    index=False)
        matched.to_excel(w,  sheet_name="Recon-Matched",  index=False)
        gl_only.to_excel(w,  sheet_name="Recon-GL only",  index=False)
        bank_only.to_excel(w,sheet_name="Recon-Bank only",index=False)
        variance.to_excel(w, sheet_name="Variance", index=False)
        aging.to_excel(w,    sheet_name="AR Aging")
        pnl.to_excel(w,      sheet_name="USD P&L")
    print(f"✅ Wrote {out}")

if __name__ == "__main__":
    main()

Schedule it

Set this script to run automatically on the 1st of every month (Module 12, Lesson 6). Wake up to a finished close pack in your inbox.