Guide

Automating Reporting WorkflowsDeep dive

Building Finance and Operations Reports in Excel with Python

The month-end pack, the aging report, the commission statement and the stock reorder list share one shape: exports in, reference data joined, a period chosen, numbers calculated, totals tied back to the source and exceptions surfaced first. This guide builds that shape once — a budget-versus-actual report from a ledger export — and shows how each finance and operations report reuses it.

Report layouts & templates Pivots & summaries pandas

Finance and operations teams run on a handful of recurring reports: the month-end budget-versus-actual pack, the receivables aging, the commission statement, the timesheet roll-up, the stock reorder list and the bank reconciliation. On the surface they look nothing alike. Underneath, they are built the same way — one or more exports from a system of record, a few reference tables maintained by hand, a reporting period, some arithmetic, a check that the numbers tie back to the source, and a workbook that puts the exceptions in front of the reader. This guide, part of Automating Reporting Workflows, builds that shape once, end to end, as a budget-versus-actual report from a general ledger export, then shows how each of the other reports reuses it.

The code runs top to bottom as one script. Each section adds one stage and explains the decisions that matter in finance work specifically: signs, periods, mappings and controls.

Install the dependencies

Bash

pip install pandas openpyxl xlsxwriter

The example uses two input files. gl_export.xlsx is a ledger export: one row per posting with a date, account, cost centre, amount and description, the way most accounting systems export a transaction listing. It covers September 2026, plus a stray posting from August and one posting to a suspense account that is not in the mapping. reference.xlsx holds the hand-maintained tables: the September budget by account and cost centre, and the account mapping that turns account numbers into report lines.

Choose the period once

Every date in a monthly report derives from one value: the period being reported. Make it a parameter, default it to the most recent month end, and compute everything else from it. That way the scheduled run needs no argument and a rerun for March needs exactly one:

Python

from datetime import date, timedelta
import pandas as pd

def previous_month_end(today):
    return today.replace(day=1) - timedelta(days=1)

def period_bounds(period_end):
    start = period_end.replace(day=1)
    return pd.Timestamp(start), pd.Timestamp(period_end)

period_end = previous_month_end(date(2026, 10, 2))
start, end = period_bounds(period_end)
label = f"{period_end:%Y-%m}"
print(label, start.date(), end.date())

In the real script, date(2026, 10, 2) is date.today() and an optional --period 2026-03 argument overrides the default. Deriving the start from the end means a period can never be accidentally 31 days in February. Run a Report on the Last Business Day of the Month covers triggering the run at the right moment, and Backfill Excel Reports for Past Dates covers rerunning it for a range of past periods.

Load exports against a column contract

System exports change without warning: a column is renamed after an upgrade, a new column appears in the middle, a date arrives as text. Declare the columns the report depends on and check them the moment the file is read, so a changed export stops the run with a clear message instead of producing a plausible but wrong report:

Python

REQUIRED = ["Posting Date", "Account", "Cost Centre", "Amount"]

def load_export(path, required):
    df = pd.read_excel(path)
    missing = set(required) - set(df.columns)
    if missing:
        raise SystemExit(f"{path}: missing columns {sorted(missing)}; found {list(df.columns)}")
    df["Posting Date"] = pd.to_datetime(df["Posting Date"])
    df["Account"] = df["Account"].astype("int64")
    df["Amount"] = pd.to_numeric(df["Amount"], errors="raise")
    return df

gl_all = load_export("gl_export.xlsx", REQUIRED)
gl = gl_all[gl_all["Posting Date"].between(start, end)].copy()
print(len(gl_all), "rows in export,", len(gl), "in period", label)

Converting with errors="raise" is deliberate: an amount that is not a number is a problem with the export, not something to blank out. The period filter drops the August posting; the count before and after is worth keeping, because it goes on the About sheet later. Validate Excel Columns Before Import with pandas goes further with type and value checks.

Fix the sign convention immediately

Ledgers store credits as negative numbers, so revenue arrives negative and costs positive. Reports read the other way. Flip revenue once, right after loading, using the account mapping — never scattered through the later calculations:

Python

accounts = pd.read_excel("reference.xlsx", sheet_name="Accounts")
gl = gl.merge(accounts, on="Account", how="left", validate="many_to_one")

unmapped = gl[gl["Line"].isna()]
print("unmapped:", unmapped[["Account", "Amount", "Description"]].to_dict("records"))

gl["Value"] = gl["Amount"].where(gl["Kind"] != "revenue", -gl["Amount"])
print(gl.groupby("Kind")["Value"].sum().to_dict())

validate="many_to_one" makes the merge fail if an account appears twice in the mapping — the classic way a mapping table doubles a line of the report. The left merge keeps unmapped postings instead of silently dropping them: account 6999, a suspense posting of 250, has no report line. In a real month that is an exception for the accountant, not something the script should guess at, so it is kept and reported. Standardise Category Values with a Mapping Table covers maintaining mapping tables like this one.

Summarise and calculate variance

Group the mapped postings to the report's grain — line and cost centre — and join the budget at the same grain. Define variance so that positive always means favourable: actual minus budget for revenue, budget minus actual for costs. Readers should never have to remember which lines are "the other way round":

Python

budget = pd.read_excel("reference.xlsx", sheet_name="Budget")
budget = budget[budget["Month"] == label].merge(accounts, on="Account", validate="many_to_one")

keys = ["Kind", "Line", "Cost Centre"]
actual = gl.dropna(subset=["Line"]).groupby(keys, as_index=False)["Value"].sum().rename(columns={"Value": "Actual"})
report = budget.groupby(keys, as_index=False)["Budget"].sum().merge(actual, on=keys, how="outer").fillna({"Budget": 0, "Actual": 0})

favourable = report["Actual"] - report["Budget"]
report["Variance"] = favourable.where(report["Kind"] == "revenue", -favourable)
report["Variance %"] = (report["Variance"] / report["Budget"]).where(report["Budget"] != 0)
report = report.sort_values(["Kind", "Line", "Cost Centre"], ascending=[False, True, True], ignore_index=True)
print(report.round(3).to_string(index=False))

The outer merge matters: a line with budget but no actuals, or actuals but no budget, still appears, with the missing side as zero. An inner merge would drop exactly the lines a reviewer most needs to see. Variance % is left empty where the budget is zero rather than dividing by zero; Calculate Percent of Total per Group in pandas covers percentage calculations in more depth.

Tie the totals back to the source

Before anything is written, prove that the report accounts for every posting in the period. The sum of the report's actuals plus anything deliberately excluded must equal the sum of the export for the period — computed independently, from the raw file:

Python

raw = pd.read_excel("gl_export.xlsx")
raw_period = raw[pd.to_datetime(raw["Posting Date"]).between(start, end)]
raw_total = raw_period["Amount"].sum()

report_total = gl.dropna(subset=["Line"])["Amount"].sum()
excluded_total = unmapped["Amount"].sum()
difference = round(raw_total - (report_total + excluded_total), 2)
print(f"export {raw_total:,.2f} = report {report_total:,.2f} + unmapped {excluded_total:,.2f}; difference {difference}")
assert difference == 0, "report does not tie to the export"
assert len(raw_period) == len(gl), "posting count changed during processing"

The totals are compared in the ledger's own sign convention, before the flip, so the check does not depend on the mapping being right. If a merge duplicated rows or a filter lost some, the difference is not zero and the run stops before anyone sees a wrong number. Reconcile Excel Totals with Control Totals covers control totals more broadly, including totals supplied by the source system itself.

Put the exceptions first

A reviewer's first question is "what do I need to look at?" Answer it on the first sheet. Define materiality explicitly — here, a variance over 10% and over 1,000 in absolute terms, so a small line swinging wildly and a large line moving slightly are both ignored — and list the lines that break it, together with anything that could not be mapped:

Python

material = report[(report["Variance %"].abs() > 0.10) & (report["Variance"].abs() > 1000)]
exceptions = pd.concat([
    material.assign(Issue=lambda d: d["Variance"].map(lambda v: "favourable" if v > 0 else "unfavourable") + " variance")[
        ["Line", "Cost Centre", "Budget", "Actual", "Variance", "Issue"]],
    unmapped.assign(Line="(account " + unmapped["Account"].astype(str) + ")", Budget=0.0, Actual=unmapped["Amount"],
                    Variance=float("nan"), Issue="account not in mapping")[["Line", "Cost Centre", "Budget", "Actual", "Variance", "Issue"]],
], ignore_index=True)
print(exceptions.to_string(index=False))

Three lines are material — Product revenue ahead of budget, Cost of sales over it, Travel far over it — plus the unmapped suspense posting. Services revenue missed budget by 3,800, which is 9.5%: just inside the threshold, so it appears in the summary but not among the exceptions. The thresholds are a business decision; put them in configuration with the period, not in the code.

Lay out the workbook for the reader

A finance workbook has a conventional structure, and following it makes the report easy to review: a summary with the exceptions on top, the detail that supports it, and a sheet recording how it was produced. Use accounting number formats — thousands separators, negatives in parentheses — and freeze the header so long sheets stay readable:

Python

from datetime import datetime

run_at = datetime(2026, 10, 2, 7, 0)
out = f"budget_vs_actual_{label}.xlsx"
with pd.ExcelWriter(out, engine="xlsxwriter") as writer:
    wb = writer.book
    money = wb.add_format({"num_format": "#,##0;(#,##0);-"})
    pct = wb.add_format({"num_format": "0.0%;(0.0%);-"})
    title = wb.add_format({"bold": True, "font_size": 14})

    exceptions.to_excel(writer, sheet_name="Summary", index=False, startrow=2)
    start_row = len(exceptions) + 5
    report.drop(columns="Kind").to_excel(writer, sheet_name="Summary", index=False, startrow=start_row)
    ws = writer.sheets["Summary"]
    ws.write("A1", f"Budget vs actual — {label}", title)
    ws.write(start_row - 1, 0, "All lines", wb.add_format({"bold": True}))
    ws.set_column("A:A", 24)
    ws.set_column("B:B", 13)
    ws.set_column("C:E", 12, money)
    ws.set_column("F:F", 22, pct)
    ws.freeze_panes(3, 1)

    gl.drop(columns=["Kind"]).to_excel(writer, sheet_name="Detail", index=False)
    writer.sheets["Detail"].set_column("A:A", 12, wb.add_format({"num_format": "yyyy-mm-dd"}))
    writer.sheets["Detail"].set_column("D:D", 12, money)
    writer.sheets["Detail"].set_column("E:G", 20)

    about = pd.DataFrame({"Item": ["Period", "Ledger export", "Rows in export", "Rows in period", "Export total (ledger signs)",
                                    "Unmapped postings", "Tie-out difference", "Run at"],
                          "Value": [label, "gl_export.xlsx", len(gl_all), len(gl), round(raw_total, 2),
                                    len(unmapped), difference, f"{run_at:%Y-%m-%d %H:%M}"]})
    about.to_excel(writer, sheet_name="About", index=False)
    writer.sheets["About"].set_column("A:B", 28)

print(pd.ExcelFile(out).sheet_names)
print(pd.read_excel(out, sheet_name="About").to_string(index=False))

The number format #,##0;(#,##0);- has three sections — positive, negative and zero — so negatives show in parentheses and zeros as a dash, the way accountants expect. The detail sheet is what a reviewer filters when a variance needs explaining; keeping every period posting there, mapped or not, means the answer is always one filter away. Format Numbers in Thousands and Millions in Excel covers scaled formats for larger figures.

Keep the reference data under control

The mapping and budget tables are the part of the pipeline most likely to be wrong, because people edit them by hand. Treat them as inputs with their own checks. Every account that received postings in the period should have a mapping row — the unmapped list above is that check — and every mapping row should point at a report line that exists in the layout. Keys should be unique, which the validate argument enforces. Record who owns each table and where it lives, and keep dated copies: when the June report is rerun in December, it should use the mapping that was in force in June, not the one after a reorganisation moved Travel into a new cost centre. A simple rule is to save the reference tables next to the report on every run, so each report carries the exact mapping that produced it. Check References Between Excel Sheets with pandas covers the key checks in detail.

Cut-offs, late postings and reruns

Ledgers stay open after the month ends. Accruals, corrections and late invoices are posted days later, so a report run on the second working day may differ from one run on the fifth. Decide which run is the reported one and make the difference visible rather than surprising. Two practices help. First, record the export's own extraction time on the About sheet next to the run time, so a reader knows how current the figures are. Second, when a closed month is rerun, compare the new totals with the version that was sent and list the postings that changed — often a handful of late journals — instead of silently replacing the file. Filtering on the posting date, as this example does, reports activity in the period; an as-at report such as receivables aging instead filters on what was open at the period end, a distinction the aging recipe handles explicitly.

Review and sign-off

A finance report is usually reviewed before it is distributed. Build that step into the process rather than around it: write the workbook to a draft location with the period and a version number in the name, send it to the reviewer, and only copy it to the distribution folder or mailing list once it is approved. Never overwrite a file that has already been sent; write a new version and say what changed. The About sheet makes review quicker, because the reviewer can check the tie-out and row counts before reading a single variance, and the exceptions block tells them where to spend their time.

Values or formulas

A report written as values is a snapshot: it shows the same numbers wherever it is opened, which is what an audit trail needs. Reviewers, though, like to click a variance and see how it was calculated. A practical compromise is values for everything that came from a source system — actuals, budget — and formulas for the arithmetic on top, such as =D4-C4 for a revenue variance and =SUM() totals. Write Formula to Excel Cell with openpyxl shows how; remember that a workbook written by Python has no cached formula results until Excel recalculates it, so anything that reads the file programmatically sees empty cells for those formulas.

Make the output reproducible

The same inputs should produce the same report. Avoid anything that varies between runs apart from the recorded run time: sort every table explicitly, round only at presentation, and record the inputs on the About sheet. Then a rerun for a closed month either matches what was sent or shows exactly what changed. Make Excel Output Reproducible covers the details, including fixed workbook metadata so identical content gives an identical file.

The same shape, six reports

Each of the recipes in this section is this pipeline with a different calculation step:

Build a Budget vs Actual Variance Report — this guide's example, extended with year-to-date columns and a formatted variance layout.
Build an Accounts Receivable Aging Report — buckets open invoices by days overdue as at the period end.
Build a Sales Commission Report — tiered rates, clawbacks and one statement per rep.
Consolidate Timesheets from Many Workbooks — one file per person rolled into hours by project.
Build an Inventory Reorder Report — stock, usage and lead time turned into a purchase list.
Build a Bank Reconciliation Report — match statement lines to ledger entries and list what is left.

Common mistakes in finance reports

Inner joins on reference data. Lines without a budget or a mapping disappear; use left or outer joins and report the gaps.
Signs flipped in several places. Convert once after loading and define variance so positive is favourable.
Dates computed separately. Derive every date from one period parameter.
No tie-out. A report that does not reconcile to its source must not be sent.
Rounding too early. Round in the number format, not in the data, or totals stop adding up.
Thresholds in code. Materiality levels change; keep them in configuration next to the period.
No record of inputs. Without an About sheet, nobody can say later where a number came from.

Key takeaways

Finance and operations reports share one pipeline: period, load, map, calculate, tie out, write.
A column contract on each export turns a changed file into a clear error rather than a wrong report.
Merge reference data with validate="many_to_one" and keep unmapped rows as exceptions.
Positive variance should always mean favourable, whatever the line.
Tie the report's totals back to the raw source before writing, and stop if they differ.
Lead with exceptions, follow with the detail, and record the inputs on an About sheet.

Frequently asked questions

Should a finance report contain formulas or values? Values for the figures that come from source systems, so the report does not change when opened elsewhere; formulas for the arithmetic a reviewer wants to trace, such as variance and totals. Many teams write values and add a few formulas where the reader expects to click a cell and see how it was calculated.

How do I handle the sign convention in ledger exports? Most ledgers export revenue as negative credits and costs as positive debits. Flip the sign once, right after loading, so revenue is positive in every later step, and define variance so that a positive number always means favourable — above budget for revenue, below budget for costs.

What is a control total and why does it matter? A control total is a figure you can compute from the source independently — the total of the export, the number of transactions — and compare with the report. If the report's total does not tie back, rows were lost in a filter or a failed join, and the report must not go out.

How do I make a monthly report run for any month? Pass the period as a parameter — a command-line argument or configuration value — and derive every date from it. Defaulting to the previous month end lets the scheduled run need no argument while a rerun for an earlier month needs only one.

Why put an About sheet in the report? It records the period, source files, row counts, control totals and run time. When someone asks a month later where a figure came from, the answer is in the file they already have.

Up one level: Automating Reporting Workflows — the full reporting pipeline from data to delivery.
Building Multi-Sheet Excel Dashboards — summary and detail sheets with KPIs and charts.
Error Handling and Logging in Excel Automation — what a failed tie-out should do in an unattended run.
Creating Pivot Tables from Excel Data — the grouping behind every summary.
Scheduling Python Excel Scripts with Cron — running the pack at month end.

More on report layouts & templates