Send Out Budget Templates and Consolidate the Returns

Run the annual budget round from Python: generate one pre-filled budget workbook per cost centre with locked formulas, validated input cells and a hidden identity sheet, then read the returned files back, check that each is the right template for the right cost centre with sensible numbers, consolidate them into one table and keep a tracker of who has returned what.

Report layouts & templates Validation & data quality

The budget round is a classic Excel workflow: finance sends a workbook to every budget holder, each fills in next year's figures, and finance stitches the returns together. Most of the pain is in the stitching. Files come back renamed, with rows inserted, formulas overwritten, the wrong cost centre's template filled in, or "TBC" typed into a number cell — and a spreadsheet of who has replied is kept by hand alongside. Python can run both halves: generate a template for each cost centre that is hard to break and easy to identify, then read every return, check it, consolidate the figures and report who is still missing. This page does that with openpyxl and pandas, using a small set of cost centres and simulated returns that include the usual mistakes. It belongs to Building Finance and Operations Reports.

Prerequisites

Bash

pip install openpyxl pandas

The inputs are a list of cost centres with their budget holders, and last year's actuals per cost centre and account, which are pre-filled in each template as a reference. The budget is for 2027, collected in October 2026.

Step 1 — Generate one template per cost centre

Each workbook carries the cost centre's own accounts and last year's actuals, input cells for the 2027 budget, a formula for the change, and a hidden sheet that records which cost centre and template version it is:

Python

import hashlib
from pathlib import Path
import pandas as pd
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Protection
from openpyxl.worksheet.datavalidation import DataValidation

VERSION = "B2027-v1"
centres = pd.DataFrame({"cc": ["CC100", "CC200", "CC300", "CC400"],
                        "name": ["Finance", "Operations", "Sales", "IT"],
                        "holder": ["amira@example.com", "dev@example.com", "farid@example.com", "lena@example.com"]})
accounts = ["6100 Salaries", "6200 Travel", "6300 Training", "6400 Software", "6500 Consumables"]
actuals = pd.DataFrame([(cc, acc, round(20000 + 9000 * i + 4000 * j, -2)) for i, cc in enumerate(centres["cc"])
                        for j, acc in enumerate(accounts)], columns=["cc", "account", "actual_2026"])
OUT = Path("outbox"); OUT.mkdir(exist_ok=True)
INPUT = PatternFill("solid", fgColor="FFF4CE")

def make_template(cc, name):
    wb = Workbook()
    ws = wb.active
    ws.title = "Budget"
    ws["A1"], ws["A1"].font = f"Budget 2027 — {cc} {name}", Font(size=14, bold=True)
    ws.append([])
    ws.append(["Account", "Actual 2026", "Budget 2027", "Change", "Comment"])
    rows = actuals[actuals["cc"] == cc]
    for r, (acc, act) in enumerate(zip(rows["account"], rows["actual_2026"]), start=4):
        ws.append([acc, act, None, f'=IF(C{r}="","",C{r}-B{r})', None])
        for col in "CE":
            ws[f"{col}{r}"].protection = Protection(locked=False)
            ws[f"{col}{r}"].fill = INPUT
    last = 3 + len(rows)
    ws.append(["Total", f"=SUM(B4:B{last})", f"=SUM(C4:C{last})", f"=C{last + 1}-B{last + 1}"])
    dv = DataValidation(type="decimal", operator="greaterThanOrEqual", formula1="0", allow_blank=True,
                        showErrorMessage=True, errorTitle="Budget", error="Enter an amount of 0 or more.")
    ws.add_data_validation(dv)
    dv.add(f"C4:C{last}")
    ws.protection.sheet = True
    ws.protection.password = "budget"
    meta = wb.create_sheet("_id")
    meta.append(["cc", "version", "rows", "fingerprint"])
    meta.append([cc, VERSION, len(rows), hashlib.sha256("|".join(rows["account"]).encode()).hexdigest()[:16]])
    meta.sheet_state = "veryHidden"
    path = OUT / f"Budget_2027_{cc}.xlsx"
    wb.save(path)
    return path

sent = [make_template(cc, name) for cc, name in zip(centres["cc"], centres["name"])]
print([p.name for p in sent])

Only the budget and comment cells are unlocked and shaded; the sheet is protected, so the account list, the actuals and the formulas cannot be changed by accident — the password stops accidents, not determined people, which is all it is for. Data validation refuses negative or non-numeric budget entries as they are typed. The hidden _id sheet — set to very hidden, so it does not appear in Excel's Unhide list — records the cost centre, template version, number of rows and a fingerprint of the account list, which is how the return is identified later however the file has been renamed. Sending each file to its holder is covered in Send Each Recipient Their Own Excel Report.

Step 2 — The returns come back

Simulate four budget holders: one fills in everything correctly, one renames the file and leaves a cell blank, one returns another cost centre's template, and one has not replied yet:

Python

from openpyxl import load_workbook
import shutil

INBOX = Path("returns"); INBOX.mkdir(exist_ok=True)

def fill(src, dest, values, comment=None):
    wb = load_workbook(src)
    ws = wb["Budget"]
    for r, v in enumerate(values, start=4):
        ws[f"C{r}"] = v
    if comment:
        ws["E5"] = comment
    wb.save(dest)

fill(OUT / "Budget_2027_CC100.xlsx", INBOX / "Budget_2027_CC100.xlsx", [21000, 5800, 9000, 12500, 4100])
fill(OUT / "Budget_2027_CC200.xlsx", INBOX / "ops budget FINAL v3.xlsx", [30500, 33000, None, 47000, 52000], "travel up: new site")
fill(OUT / "Budget_2027_CC100.xlsx", INBOX / "Budget_2027_CC300.xlsx", [1, 2, 3, 4, 5])
print(sorted(p.name for p in INBOX.iterdir()))

The Operations holder saved the file as "ops budget FINAL v3.xlsx" — normal behaviour that would break any process relying on file names. The Sales holder filled in a copy of Finance's template and named it after their own cost centre, which is surprisingly common when people forward templates to each other.

Step 3 — Identify, validate and consolidate

Read every file in the inbox, identify it from its hidden sheet rather than its name, and check it before taking any numbers:

Python

def read_return(path):
    wb = load_workbook(path, data_only=False)
    if "_id" not in wb.sheetnames:
        return None, ["not a budget template"]
    cc, version, nrows, fingerprint = [c.value for c in wb["_id"][2]]
    problems = []
    if version != VERSION:
        problems.append(f"template version {version}, expected {VERSION}")
    ws = wb["Budget"]
    rows = [[c.value for c in row] for row in ws.iter_rows(min_row=4, max_row=3 + nrows, max_col=5)]
    accounts_now = [r[0] for r in rows]
    if hashlib.sha256("|".join(map(str, accounts_now)).encode()).hexdigest()[:16] != fingerprint:
        problems.append("account rows changed")
    blanks = [r[0] for r in rows if r[2] is None]
    if blanks:
        problems.append(f"blank budget for {', '.join(blanks)}")
    bad = [r[0] for r in rows if r[2] is not None and not isinstance(r[2], (int, float))]
    if bad:
        problems.append(f"non-numeric budget for {', '.join(bad)}")
    data = pd.DataFrame(rows, columns=["account", "actual_2026", "budget_2027", "change", "comment"]).assign(cc=cc, file=path.name)
    return data, problems

tracker, frames = [], []
for path in sorted(INBOX.glob("*.xlsx")):
    data, problems = read_return(path)
    cc = data["cc"].iloc[0] if data is not None else None
    expected = path.stem.split("_")[-1]
    if cc and expected.startswith("CC") and expected != cc:
        problems.append(f"file named {expected} but contains {cc}'s template")
    tracker.append({"file": path.name, "cc": cc, "for": expected if expected.startswith("CC") else cc,
                    "status": "valid" if not problems else "invalid", "problems": "; ".join(problems)})
    if data is not None and not problems:
        frames.append(data)

tracker = pd.DataFrame(tracker)
returned = set(tracker.loc[tracker["status"] == "valid", "cc"])
status = centres.assign(status=centres["cc"].map(lambda c: "valid" if c in returned else
                                                  ("invalid" if c in set(tracker["for"]) else "missing")))
print(tracker.to_string(index=False))
print(status[["cc", "name", "holder", "status"]].to_string(index=False))

The hidden sheet identifies the renamed Operations file correctly as CC200, and its blank Training budget is reported by account name rather than as a cell address. The file named after Sales turns out to contain Finance's template, and — since Finance's own return already arrived — it is rejected rather than counted twice. The tracker records which cost centre each file was meant for — from a file name that follows the pattern, otherwise from the hidden sheet — so Sales shows as invalid rather than missing: they did reply, just with the wrong workbook. The status table combines the tracker with the list of cost centres, so the missing IT return is visible even though there is no file for it. In a real round, the invalid and missing lists become reminder emails; only valid returns are consolidated.

Step 4 — The consolidated budget and the tracker

Python

budget = pd.concat(frames, ignore_index=True) if frames else pd.DataFrame()
by_account = budget.pivot_table(index="account", columns="cc", values="budget_2027", aggfunc="sum", margins=True, margins_name="Total")
with pd.ExcelWriter("budget_2027_consolidated.xlsx", engine="openpyxl") as xw:
    status.to_excel(xw, sheet_name="Tracker", index=False)
    tracker.to_excel(xw, sheet_name="Files", index=False)
    budget.to_excel(xw, sheet_name="Lines", index=False)
    by_account.to_excel(xw, sheet_name="By account")
print(by_account.to_string())

The consolidated workbook has the tracker first — who is valid, invalid and missing — then the file-level problems, every budget line with the file it came from, and a pivot by account and cost centre. Rerunning the script after more returns arrive rebuilds everything from the inbox, so there is no hand-maintained list to drift out of date. Comparing the consolidated budget against actuals as the year goes on is covered in Build a Budget vs Actual Variance Report.

Common pitfalls

Identifying returns by file name. People rename files; use a hidden identity sheet.
Unprotected formulas. Lock everything but the input cells and protect the sheet.
Trusting typed values. Validate as entered and again on return.
Counting a forwarded template twice. Check the cost centre inside the file.
A tracker kept by hand. Rebuild it from the files on every run.

Conclusion

Generate each budget holder's workbook from Python with their accounts and prior-year actuals, unlocked and validated input cells, protected formulas and a very hidden sheet recording cost centre, version and a fingerprint of the rows. When the returns arrive, identify each from that sheet, check version, rows and values, consolidate only valid returns, and rebuild a tracker of valid, invalid and missing cost centres every time the script runs.

Frequently asked questions

How do I send a different Excel template to each budget holder? Loop over the cost centres and build each workbook with openpyxl from the same function, pre-filled with that cost centre's accounts and figures, then email each file to its holder.

How can I tell which cost centre a returned file belongs to? Store the cost centre and template version on a very hidden sheet when generating the template and read it back, rather than relying on the file name.

How do I stop people breaking formulas in a budget template? Unlock only the input cells, protect the sheet, and add data validation to the input cells so only valid numbers can be entered.

How do I consolidate many returned budget workbooks with pandas? Read each file, validate it, turn its rows into a DataFrame tagged with the cost centre, concatenate the valid ones and pivot by account and cost centre.

Up one level: Building Finance and Operations Reports — finance reports from Python.
Consolidate Timesheets from Many Workbooks — consolidating other returns.
Lock Cells and Protect a Sheet with openpyxl — the protection used here.
Quarantine Bad Excel Files in a Batch Job — handling files that fail checks.

More on report layouts & templates