Build a Month-End Close Checklist Tracker

A month-end close is a list of thirty or more tasks — post the payroll journal, reconcile the bank accounts, run depreciation, accrue expenses, review the trial balance, publish the management pack — each with an owner and a deadline expressed relative to the end of the month: working day 2, working day −1. Most teams keep the list in a spreadsheet that someone updates by hand each morning, recalculating dates every month and colouring cells as tasks slip. Most of that is mechanical. This page builds a tracker that does it: deadlines stored as working-day offsets and converted to real dates for each month with a holiday calendar, status read from the sheet the team updates, overdue and blocked tasks flagged — a task is blocked when something it depends on is not done — progress compared with plan, and a colour-coded workbook written each morning. It belongs to Building Finance and Operations Reports.

Prerequisites

pip install pandas openpyxl holidays

Two inputs. The checklist is permanent: task, owner, deadline as a working-day offset from month end, and the tasks each one depends on. The status sheet is what the team updates during the close: status and completion date. The close being tracked is September 2026, and the tracker is run on the morning of Tuesday 6 October.

Step 1 — Turn working-day offsets into dates

import holidays
import numpy as np
import pandas as pd

tasks = pd.read_excel("close_checklist.xlsx", keep_default_na=False)
period_end = pd.Timestamp("2026-09-30")
uk = [np.datetime64(d) for d in holidays.country_holidays("GB", subdiv="ENG", years=[2026])]

def working_day(offset):
    """WD+1 is the first working day after month end; WD-1 is the last working day of the month."""
    end = np.datetime64(period_end.date())
    if offset > 0:
        return pd.Timestamp(np.busday_offset(end, offset, roll="forward", holidays=uk))
    last_wd = np.busday_offset(end, 0, roll="backward", holidays=uk)
    return pd.Timestamp(np.busday_offset(last_wd, offset + 1, roll="backward", holidays=uk))

tasks["Due"] = tasks["Working day"].map(working_day)
print(tasks[["Task", "Working day", "Due"]].assign(Due=tasks["Due"].dt.strftime("%a %d %b")).to_string(index=False))

Close checklists measure time in working days relative to month end because calendar dates move every month while the rhythm of the close does not. WD+1 is the first working day after the month ends, WD−1 the last working day of the month itself. NumPy's busday_offset does the counting, skipping weekends and the public holidays passed to it, so the same checklist gives the right dates in a month with a bank holiday. September 2026 ends on a Wednesday, so WD−1 is Wednesday 30 September, WD+1 Thursday 1 October, and WD+5 — the management pack — Wednesday 7 October, after the weekend. Storing the offset rather than the date is the key design decision: the checklist never needs editing month to month.

Step 2 — Status, overdue and blocked tasks

today = pd.Timestamp("2026-10-06")
status = pd.read_excel("close_status.xlsx", parse_dates=["Completed"])
tracker = tasks.merge(status, on="Task", how="left")
tracker["Status"] = tracker["Status"].fillna("Not started")

done = set(tracker.loc[tracker["Status"] == "Done", "Task"])
deps = tracker["Depends on"].str.split(",").map(lambda xs: [x.strip() for x in xs if x.strip()])
tracker["Waiting for"] = deps.map(lambda xs: ", ".join(x for x in xs if x not in done))

def flag(row):
    if row["Status"] == "Done":
        return "Done late" if row["Completed"] > row["Due"] else "Done"
    if row["Waiting for"]:
        return "Blocked"
    return "Overdue" if row["Due"] < today else ("Due today" if row["Due"] == today else "On track")

tracker["Flag"] = tracker.apply(flag, axis=1)
print(tracker[["Task", "Owner", "Due", "Status", "Waiting for", "Flag"]].assign(Due=tracker["Due"].dt.strftime("%d %b")).to_string(index=False))

Joining the status sheet to the checklist gives each task its current state; tasks nobody has touched yet count as not started. A task is blocked when any of the tasks it depends on is not done, and the tracker lists exactly which ones it is waiting for. That one column changes the morning conversation: the intercompany reconciliation is late, but its owner cannot finish it until treasury completes the bank reconciliations, and the trial balance review, due today, is waiting for three other tasks. Tasks that are not blocked and are past their due date are overdue. Completed tasks are checked too — the revenue review was done on WD+3 as planned, but if it had been completed after its due date it would show as done late, which is useful history when the close timetable is reviewed.

The distinction matters for what happens next. An overdue task needs its owner to finish it; a blocked task needs someone else to finish theirs first, and chasing its owner only adds noise. On this Tuesday morning two tasks are overdue — the bank reconciliations and the stock valuation, both in progress — and three are blocked behind them.

Step 3 — Progress against plan, and the critical chain

by_day = tracker.groupby("Due").size().cumsum().rename("Planned")
actual = tracker.dropna(subset=["Completed"]).groupby("Completed").size().cumsum().rename("Done")
progress = pd.concat([by_day, actual], axis=1, sort=True).ffill().fillna(0).astype(int)
progress = progress[progress.index <= today]
print(progress.assign(Gap=progress["Done"] - progress["Planned"]).rename(index=lambda d: d.strftime("%a %d %b")).to_string())

pack = tracker.set_index("Task")
def chain(task):
    waiting = [t for t in pack.loc[task, "Waiting for"].split(", ") if t]
    return [task] + (chain(waiting[0]) if waiting else [])
print("what holds up the pack:", " ← ".join(chain("T10")))

A cumulative count of tasks planned by each date, next to the count actually completed, shows whether the close is keeping pace: by the end of Monday eight of the ten tasks should have been finished, but only five are, and with the trial balance review due today the gap widens to four. The gap tells management how far behind the close is; the dependency chain tells them why. Following the waiting for links back from the management pack shows the path that is holding everything up — the pack waits for the trial balance review, which waits first of all for the bank reconciliations — and that is where help should go. The bank reconciliations also block the intercompany reconciliation, so finishing them unblocks two tasks at once, while the stock valuation is the other open dependency of the review.

Step 4 — The tracker workbook

from openpyxl import load_workbook
from openpyxl.styles import PatternFill

out = tracker[["Task", "Description", "Owner", "Working day", "Due", "Status", "Completed", "Waiting for", "Flag"]]
out = out.assign(Due=out["Due"].dt.date, Completed=out["Completed"].dt.date)
with pd.ExcelWriter("close_tracker_2026-09.xlsx") as xw:
    out.to_excel(xw, sheet_name="Tracker", index=False)
    progress.rename(index=lambda d: d.date()).to_excel(xw, sheet_name="Progress")
wb = load_workbook("close_tracker_2026-09.xlsx")
ws = wb["Tracker"]
colours = {"Done": "C6EFCE", "Done late": "FFEB9C", "Blocked": "FCE4D6", "Overdue": "FFC7CE", "Due today": "FFEB9C", "On track": "FFFFFF"}
for row in ws.iter_rows(min_row=2):
    fill = PatternFill("solid", fgColor=colours[row[8].value])
    for cell in row:
        cell.fill = fill
ws.freeze_panes = "B2"
wb.save("close_tracker_2026-09.xlsx")
print(out["Flag"].value_counts().to_dict())

The workbook colours each row by its flag — green for done, red for overdue, orange for blocked, yellow for due today or done late — so the state of the close is visible at a glance, and a second sheet holds the progress table for a burn-up chart. Run each morning of the close from a scheduler and emailed to the team, it replaces the manual update entirely: owners only change their status, and everything else follows. Because the dates come from offsets, next month's tracker needs nothing more than a new period_end.

Keeping each month's final tracker also builds a useful record. Over a year, the done late flags and the gaps in the progress table show which tasks routinely slip and which dependencies cause the waits, which is exactly the evidence needed to shorten the close: moving a task earlier, splitting a reconciliation between two people, or removing a dependency that turns out not to be real.

Common pitfalls

• Storing dates instead of offsets. Keep WD offsets in the checklist and compute dates each month.
• Ignoring holidays. Pass the holiday calendar to busday_offset.
• Overdue without context. Separate blocked tasks from genuinely late ones.
• Unchecked dependencies. Verify every dependency names a real task before the close starts.
• Status edited in the tracker. Keep status in its own sheet so the tracker can be rebuilt.

Conclusion

A month-end close tracker stores deadlines as working-day offsets and turns them into dates each month with numpy.busday_offset and a holiday calendar. Joining the team's status updates gives each task a flag — done, done late, blocked by named dependencies, overdue, due today or on track — and a cumulative count of planned and completed tasks shows whether the close is on pace, while the dependency chain shows what is holding it up.

Frequently asked questions

How do I calculate working day 3 after month end in Python? Use numpy.busday_offset(month_end, 3, roll="forward", holidays=holiday_list).

How do I find tasks blocked by unfinished dependencies? Store each task's dependencies, collect the set of completed tasks, and list the dependencies of each task that are not in that set.

Why store close deadlines as working-day offsets? Because the close follows the same rhythm every month while calendar dates and holidays change; the offsets never need editing.

How do I show close progress against plan? Count tasks due by each date cumulatively and compare with the cumulative count of tasks completed by each date.

Related

• Up one level: Building Finance and Operations Reports — finance reports from Python.
• Run a Report on the Last Business Day of the Month — working-day scheduling.
• Build a Gantt Chart in Excel with Python — visualising the timetable.
• Build an Accruals and Prepayments Schedule — one of the close tasks.