How-to

Automating Reporting WorkflowsDeep dive

Build a Profit and Loss Statement from a General Ledger

Turn a general ledger export into a monthly profit and loss statement in Excel: check the ledger balances, map account numbers to P&L lines with a chart of accounts, flip signs so revenue and costs read naturally, add gross, operating and net profit with margins, and write a workbook with collapsible account detail and live subtotal formulas.

Report layouts & templates Pivots & summaries

Every accounting system can print a profit and loss statement, but the one management wants rarely matches it: different line groupings, months side by side, margins, a layout that fits the board pack. So someone exports the general ledger to Excel each month and rebuilds the statement with SUMIFS. That rebuild is exactly the kind of work Python does well: read the journal lines, check they balance, map every account to a line, and lay out the statement the same way every month. This page is part of Building Finance and Operations Reports.

Prerequisites

Bash

pip install pandas openpyxl xlsxwriter

gl_q3.xlsx holds the export for July to September 2026: a GL sheet with one row per journal line (journal number, date, account, debit, credit) and an Accounts sheet with the chart of accounts. Accounts follow the common numbering: 1000–3999 are balance sheet accounts, 4000s revenue, 5000s cost of sales, 6000s operating expenses, 7000s other income and expense, 8000s tax.

Step 1 — Check the ledger balances

A statement built on an unbalanced or partial export is wrong before it starts. Check that debits equal credits overall and for every journal, and that every account in the ledger exists in the chart of accounts:

Python

import pandas as pd

gl = pd.read_excel("gl_q3.xlsx", sheet_name="GL", parse_dates=["Date"])
coa = pd.read_excel("gl_q3.xlsx", sheet_name="Accounts")

total = (gl["Debit"] - gl["Credit"]).sum().round(2)
by_journal = gl.groupby("Journal")[["Debit", "Credit"]].sum().round(2)
unbalanced = by_journal[by_journal["Debit"] != by_journal["Credit"]]
unknown = set(gl["Account"]) - set(coa["Account"])
print(f"{len(gl)} lines, {gl['Journal'].nunique()} journals; debits - credits = {total}")
print(f"unbalanced journals: {len(unbalanced)}; accounts not in the chart: {sorted(unknown) or 'none'}")

Stop the script if either check fails. An unknown account usually means someone opened a new account; it needs a line in the mapping before the numbers can be trusted, not a silent exclusion.

Step 2 — Map accounts to statement lines

The mapping is the heart of the report and should be data, not code: a table of account ranges and the line each belongs to. pd.cut assigns every account to its range in one call:

Python

bounds = [4000, 4100, 4200, 5000, 6000, 6100, 6300, 7000, 7100, 7200, 8000, 9000]
lines = ["Revenue", "Discounts", None, "Cost of sales", "People", "Premises and software",
         "Marketing", "Other income", "Other expense", None, "Tax"]
line_map = pd.DataFrame({"From": bounds[:-1], "To": bounds[1:], "Line": lines}).dropna()
print(line_map.to_string(index=False))

pl = gl[gl["Account"].between(4000, 8999)].merge(coa, on="Account", how="left")
pl["Line"] = pd.cut(pl["Account"], bins=bounds, right=False, labels=False).map(dict(enumerate(lines)))
print("unmapped P&L accounts:", sorted(pl.loc[pl["Line"].isna(), "Account"].unique()) or "none")

Keep the range table in its own sheet or file, owned by finance, so a new account is a one-row change. When groupings do not follow number ranges, map account by account with a merge instead; The VLOOKUP Equivalent in pandas for Excel Files shows that pattern.

Step 3 — Sign conventions

In a ledger, revenue is a credit and costs are debits. A statement reads better with both positive, then revenue minus costs. Compute each line's natural amount: credit minus debit for revenue and other income, debit minus credit for everything else. Discounts are a contra-revenue account — a debit that reduces revenue — so they stay on the revenue side with a negative value:

Python

credit_lines = {"Revenue", "Discounts", "Other income"}
natural = pl["Credit"] - pl["Debit"]
pl["Amount"] = natural.where(pl["Line"].isin(credit_lines), -natural)
pl["Month"] = pl["Date"].dt.to_period("M").astype(str)

detail = pl.pivot_table(index=["Line", "Account", "Name"], columns="Month",
                        values="Amount", aggfunc="sum", fill_value=0).round(2)
print(detail.head(6).to_string())

Step 4 — Subtotals and margins

A P&L is a sequence of subtracted lines with subtotals: revenue less discounts is net revenue, less cost of sales is gross profit, less operating expenses is operating profit, then other items and tax to net profit. Compute them from the line totals and add margins as a share of net revenue:

Python

by_line = detail.groupby(level="Line").sum()
L = lambda name: by_line.loc[name] if name in by_line.index else 0

statement = pd.DataFrame({
    "Revenue": L("Revenue"),
    "Discounts": L("Discounts"),
    "Net revenue": L("Revenue") + L("Discounts"),
    "Cost of sales": L("Cost of sales"),
})
statement["Gross profit"] = statement["Net revenue"] - statement["Cost of sales"]
for line in ["People", "Premises and software", "Marketing"]:
    statement[line] = L(line)
statement["Operating profit"] = statement["Gross profit"] - statement[["People", "Premises and software", "Marketing"]].sum(axis=1)
statement["Other income"] = L("Other income")
statement["Other expense"] = L("Other expense")
statement["Profit before tax"] = statement["Operating profit"] + statement["Other income"] - statement["Other expense"]
statement["Tax"] = L("Tax")
statement["Net profit"] = statement["Profit before tax"] - statement["Tax"]
statement = statement.T
statement["Q3"] = statement.sum(axis=1)

margins = (statement.loc[["Gross profit", "Operating profit", "Net profit"]] / statement.loc["Net revenue"] * 100).round(1)
print(statement.round(0).astype(int).to_string())
print(margins.to_string())

Always reconcile the bottom line independently: net profit must equal the sum of every P&L account's credit minus debit, whatever the grouping. If a mapping change drops an account, this check catches it. It cannot catch an account filed under the wrong line, such as software landing in marketing because a range boundary is off by one; only the account detail under each line shows that, which is why the report keeps it:

Python

independent = (pl["Credit"] - pl["Debit"]).sum().round(2)
print(f"net profit from the statement {statement.loc['Net profit', 'Q3']:.2f}; "
      f"from the ledger {independent:.2f}")

Step 5 — Write the statement with account detail

The reader wants the summary lines, with the option to open a line and see the accounts behind it. Excel's outline grouping does that: account rows sit at level 1 under each line and collapse with the minus buttons in the margin. Subtotals are SUM formulas over the line rows, so the workbook stays consistent if someone adjusts a figure:

Python

import xlsxwriter

months = list(statement.columns)
wb = xlsxwriter.Workbook("pl_q3_2026.xlsx")
ws = wb.add_worksheet("P&L")
num = wb.add_format({"num_format": "#,##0;(#,##0)"})
bold = wb.add_format({"num_format": "#,##0;(#,##0)", "bold": True})
sub = wb.add_format({"num_format": "#,##0;(#,##0)", "bold": True, "top": 1})
pct = wb.add_format({"num_format": "0.0%", "italic": True, "font_color": "#555555"})
head = wb.add_format({"bold": True, "bottom": 1, "align": "right"})
acct_fmt = wb.add_format({"num_format": "#,##0;(#,##0)", "font_color": "#555555"})

ws.write(0, 0, "Profit and loss, Q3 2026", wb.add_format({"bold": True, "font_size": 13}))
ws.write_row(2, 1, [pd.Period(m).strftime("%b %Y") if m != "Q3" else "Q3 total" for m in months], head)
row = 3
cell = {}          # statement line -> worksheet row, for formulas

def col(c):
    return xlsxwriter.utility.xl_col_to_name(c)

def write_line(name, fmt=bold):
    global row
    ws.write(row, 0, name, fmt)
    for c, m in enumerate(months, start=1):
        ws.write_number(row, c, float(statement.loc[name, m]), fmt)
    cell[name] = row
    row += 1
    accounts = detail.loc[name] if name in detail.index.get_level_values(0) else None
    if accounts is not None and len(accounts) > 1:
        for (acct, acct_name), values in accounts.iterrows():
            ws.write(row, 0, f"   {acct} {acct_name}")
            for c, m in enumerate(months[:-1], start=1):
                ws.write_number(row, c, float(values[m]), acct_fmt)
            ws.write_formula(row, len(months), f"=SUM(B{row + 1}:{col(len(months) - 1)}{row + 1})", acct_fmt)
            ws.set_row(row, None, None, {"level": 1, "hidden": True})
            row += 1
        ws.set_row(cell[name], None, None, {"collapsed": True})

def write_total(name, terms):
    """terms: (sign, line) pairs, e.g. [("+", "Revenue"), ("+", "Discounts")]."""
    global row
    ws.write(row, 0, name, sub)
    for c in range(1, len(months) + 1):
        formula = "".join(f"{sign}{col(c)}{cell[line] + 1}" for sign, line in terms)
        ws.write_formula(row, c, "=" + formula.lstrip("+"), sub)
    cell[name] = row
    row += 2

write_line("Revenue"); write_line("Discounts")
write_total("Net revenue", [("+", "Revenue"), ("+", "Discounts")])
write_line("Cost of sales")
write_total("Gross profit", [("+", "Net revenue"), ("-", "Cost of sales")])
opex = ["People", "Premises and software", "Marketing"]
for line in opex:
    write_line(line)
write_total("Operating profit", [("+", "Gross profit")] + [("-", line) for line in opex])
write_line("Other income"); write_line("Other expense")
write_total("Profit before tax", [("+", "Operating profit"), ("+", "Other income"), ("-", "Other expense")])
write_line("Tax")
write_total("Net profit", [("+", "Profit before tax"), ("-", "Tax")])

for name in ["Gross profit", "Operating profit", "Net profit"]:
    ws.write(row, 0, f"{name} margin", pct)
    for c in range(1, len(months) + 1):
        ws.write_formula(row, c, f"={col(c)}{cell[name] + 1}/{col(c)}{cell['Net revenue'] + 1}", pct)
    row += 1

ws.set_column(0, 0, 30)
ws.set_column(1, len(months), 13)
ws.outline_settings(True, False, True, False)   # summary rows above the detail
ws.freeze_panes(3, 1)
wb.close()
print(f"wrote {row} rows; net profit on row {cell['Net profit'] + 1}")

The account rows are written hidden at outline level 1 and the line above them is marked collapsed, so the statement opens showing only its summary lines; the plus buttons in the left margin open each line. outline_settings puts the buttons on the line above the detail, which is where a reader of a P&L expects them. Group and Outline Rows in Excel with openpyxl covers outlines in more depth.

Step 6 — Recalculate and compare

The subtotal formulas must agree with the pandas statement. Drop xlsxwriter's cached zeros with an openpyxl save, recalculate with LibreOffice, and compare the net profit row:

Python

from openpyxl import load_workbook
load_workbook("pl_q3_2026.xlsx").save("pl_nocache.xlsx")

Bash

soffice -env:UserInstallation=file://$PWD/lo_profile --headless --convert-to xlsx --outdir recalculated pl_nocache.xlsx > /dev/null 2>&1
ls recalculated

Python

ws = load_workbook("recalculated/pl_nocache.xlsx", data_only=True)["P&L"]
r = cell["Net profit"] + 1
sheet_values = [ws.cell(row=r, column=c).value for c in range(2, len(months) + 2)]
print([round(v, 2) for v in sheet_values])
print("matches pandas:", all(abs(a - b) < 0.005 for a, b in zip(sheet_values, statement.loc["Net profit"])))

Common pitfalls

Skipping the balance check. An export cut off mid-journal still produces a plausible-looking P&L.
Mixing signs. Decide the convention once — revenue and costs both positive — and apply it by line, not by account.
Contra accounts. Discounts, returns and allowances reduce revenue; keep them on the revenue side with a negative sign rather than as a cost.
Mapping in code. Account groupings change; keep them in a table finance can edit, and fail on unmapped accounts.
Months with no activity. Pivot with fill_value=0 so a quiet month shows 0 rather than disappearing.
Rounding. Round only for display; subtotals of rounded lines drift from the ledger total.

Conclusion

Check the ledger balances and every account is known, map account ranges to statement lines with a table and pd.cut, sign amounts so revenue and costs are both positive, and compute net revenue, gross, operating and net profit with margins. Reconcile net profit to the raw ledger independently. Write summary lines with collapsible account detail and subtotal formulas, then recalculate once to confirm the workbook and pandas agree.

Frequently asked questions

How do I turn debits and credits into P&L amounts? For revenue and other income use credit minus debit; for costs use debit minus credit. Both then read as positive numbers, and profit is revenue minus costs.

Where should the account-to-line mapping live? In a table — a sheet in the workbook or a small CSV — with account ranges or individual accounts and the line they belong to. The script reads it and fails if any P&L account is not mapped.

How do I show account detail under each line in Excel? Write the account rows beneath each line with an outline level of 1 and hidden set, and put summary rows above the detail. Readers expand a line with the plus button in the margin.

Why does my P&L not match the accounting system? Check the export's date range and whether it includes closing or adjusting journals, look for unmapped accounts, and reconcile net profit to the sum of credit minus debit across all P&L accounts.

Up one level: Building Finance and Operations Reports — the rest of the finance reports.
Build a Budget vs Actual Variance Report — comparing the statement with the plan.
Allocate Shared Costs Across Departments — P&Ls by department.
Build a Bank Reconciliation Report — another ledger check.
Group and Outline Rows in Excel with openpyxl — outlines and collapsible detail.

More on report layouts & templates