Generate Customer Account Statements from a Ledger

At month end, accounts receivable sends each customer a statement: what they owed at the start of the month, every invoice, payment and credit note since, the running balance, what they owe now, and how much of it is overdue. The data is all in the sales ledger, usually exported to Excel, but turning it into dozens of individual statements by hand is slow and error-prone. With pandas, the calculations are a few group operations: the balance brought forward is a sum of everything before the period, the running balance is a cumulative sum, and the ageing comes from allocating payments to the oldest invoices first. XlsxWriter then lays out one statement workbook per customer. This page builds that job and finishes with checks that tie every statement back to the ledger. It belongs to Generating Excel Reports from Templates.

Prerequisites

pip install pandas openpyxl xlsxwriter

ledger.xlsx is a receivables ledger with one row per transaction: customer code and name, date, type (INV invoice, PAY payment, CRN credit note), reference and a signed amount — positive for what the customer owes, negative for what they paid or were credited. The statement period is September 2026, and invoices are due 30 days after their date.

Step 1 — Split the ledger at the period start

Everything before the period collapses into one balance brought forward line per customer; transactions inside the period are listed individually:

import pandas as pd

ledger = pd.read_excel("ledger.xlsx", sheet_name="Ledger").sort_values(["Customer", "Date", "Reference"])
start, end = pd.Timestamp("2026-09-01"), pd.Timestamp("2026-09-30")

before = ledger[ledger["Date"] < start]
period = ledger[(ledger["Date"] >= start) & (ledger["Date"] <= end)].copy()

opening = before.groupby("Customer")["Amount"].sum()
customers = ledger.drop_duplicates("Customer").set_index("Customer")["Name"]
opening = opening.reindex(customers.index, fill_value=0.0)
print(opening.round(2).to_dict())

Oakridge Farms brings forward 860.00 — the July invoice was paid in August, leaving the August invoice open. Café Señor brings forward 1,170.00 from two unpaid invoices. Zenith Logistics had no activity before September, and reindex(..., fill_value=0.0) gives it an explicit zero rather than leaving it out — every customer with activity gets a statement, even one that starts from nothing.

Step 2 — Running balance through the period

The running balance on each line is the opening balance plus the cumulative sum of the period's amounts:

period["Balance"] = period.groupby("Customer")["Amount"].cumsum() + period["Customer"].map(opening)
closing = opening.add(period.groupby("Customer")["Amount"].sum(), fill_value=0.0)

oak = period[period["Customer"] == "C001"][["Date", "Type", "Reference", "Amount", "Balance"]]
print(oak.assign(Date=oak["Date"].dt.strftime("%d %b")).to_string(index=False))
print("closing:", closing.round(2).to_dict())

groupby(...).cumsum() restarts for each customer, and mapping the opening balance onto every row shifts the whole series. The last line of each customer's running balance equals their closing balance — Oakridge owes 1,760.00, Café Señor 1,250.00, and Zenith Logistics has paid in full. Sorting by date and then reference keeps same-day transactions in a stable order, so a statement regenerated next week shows the lines in the same sequence. Grouped cumulative sums are covered further in Calculate Month-to-Date and Year-to-Date Totals with pandas.

Step 3 — Age the balance by allocating payments

The ageing summary says how much of the balance is current and how much is 1–30, 31–60 or more than 60 days overdue. That depends on which invoices are still open, and the usual convention — when payments are not matched to specific invoices — is first in, first out: payments and credits settle the oldest invoices first:

def open_invoices(txns):
    """Allocate payments and credits to the oldest invoices; return what remains open."""
    invoices = txns[txns["Amount"] > 0][["Date", "Reference", "Amount"]].to_dict("records")
    credit = -txns.loc[txns["Amount"] < 0, "Amount"].sum()
    for inv in invoices:                      # already sorted by date
        used = min(credit, inv["Amount"])
        inv["Open"] = round(inv["Amount"] - used, 2)
        credit -= used
    return [i for i in invoices if i["Open"] > 0], round(credit, 2)

def age(txns, as_of, terms=30):
    buckets = {"Current": 0.0, "1-30": 0.0, "31-60": 0.0, "Over 60": 0.0}
    still_open, unapplied = open_invoices(txns)
    for inv in still_open:
        overdue = (as_of - (inv["Date"] + pd.Timedelta(days=terms))).days
        key = "Current" if overdue <= 0 else "1-30" if overdue <= 30 else "31-60" if overdue <= 60 else "Over 60"
        buckets[key] += inv["Open"]
    buckets["Unapplied credit"] = -unapplied
    return buckets

upto_end = ledger[ledger["Date"] <= end]
ageing = pd.DataFrame({c: age(g, end) for c, g in upto_end.groupby("Customer")}).T.round(2)
print(ageing)

For Oakridge Farms, the July invoice was fully paid in August; September's payment and credit note (640.00 in total) then settle the August invoice down to 220.00, which is 10 days overdue on 30 September, and the September invoice of 1,540.00 is current. Café Señor's June invoice is reduced by the September card payment to 150.00 and is more than 60 days overdue — exactly the line a credit controller wants to see. A payment larger than all open invoices appears as unapplied credit, a negative amount, so the ageing buckets still add up to the balance. If your ledger records which invoice each payment settles, allocate by that link instead of FIFO.

Step 4 — Lay out one statement per customer

Each customer gets a workbook with a header, the statement lines and the ageing summary. XlsxWriter writes it with formats for money and dates:

import xlsxwriter
from pathlib import Path

Path("statements").mkdir(exist_ok=True)

def write_statement(code):
    name = customers[code]
    lines = period[period["Customer"] == code]
    book = xlsxwriter.Workbook(f"statements/{code}_2026-09.xlsx")
    ws = book.add_worksheet("Statement")
    title = book.add_format({"bold": True, "font_size": 16, "font_color": "#4338CA"})
    head = book.add_format({"bold": True, "bottom": 1, "bg_color": "#EEF2FF"})
    money = book.add_format({"num_format": "#,##0.00;[Red]-#,##0.00"})
    bold_money = book.add_format({"num_format": "#,##0.00", "bold": True, "top": 1})
    day = book.add_format({"num_format": "dd mmm yyyy", "align": "left"})
    ws.set_column("A:A", 14); ws.set_column("B:C", 16); ws.set_column("D:E", 14)

    ws.write("A1", "Statement of account", title)
    ws.write("A3", name); ws.write("A4", f"Account {code}")
    ws.write("D3", "Period"); ws.write("E3", f"{start:%d %b} – {end:%d %b %Y}")
    ws.write_row("A6", ["Date", "Type", "Reference", "Amount", "Balance"], head)
    ws.write_datetime("A7", start.to_pydatetime(), day)
    ws.write("C7", "Balance brought forward")
    ws.write_number("E7", float(opening[code]), money)
    r = 7
    for row in lines.itertuples():
        ws.write_datetime(r, 0, row.Date.to_pydatetime(), day)
        ws.write_row(r, 1, [row.Type, row.Reference])
        ws.write_number(r, 3, row.Amount, money)
        ws.write_number(r, 4, row.Balance, money)
        r += 1
    ws.write(r, 3, "Amount due", book.add_format({"bold": True, "top": 1}))
    ws.write_number(r, 4, float(closing[code]), bold_money)

    r += 2
    ws.write(r, 0, "Ageing", head)
    buckets = ageing.loc[code]
    ws.write_row(r + 1, 0, list(buckets.index), head)
    ws.write_row(r + 2, 0, [float(v) for v in buckets], money)
    ws.set_landscape(); ws.fit_to_pages(1, 0)
    book.close()
    return f"statements/{code}_2026-09.xlsx"

paths = [write_statement(c) for c in customers.index]
print(paths)

The money format shows negative amounts — payments and credits — in red. Balances are written as values rather than formulas so the statement shows the same figures in every viewer, including mail previews; the ledger remains the source of truth. Landscape orientation and fit-to-width make the statement print or convert to PDF on one page across. A statement sent as PDF rather than as a workbook is a natural next step: see Convert Excel to PDF on Linux with LibreOffice, and Password-Protect a PDF Exported from Excel for locking it.

Step 5 — Reconcile every statement with the ledger

Statements go to customers, so a mistake is public. Before sending, check that each one ties out — the closing balance equals the ledger total, the ageing adds up to the closing balance, and the last running balance matches:

from openpyxl import load_workbook

problems = []
for code in customers.index:
    ledger_total = round(upto_end.loc[upto_end["Customer"] == code, "Amount"].sum(), 2)
    if round(closing[code], 2) != ledger_total:
        problems.append(f"{code}: closing {closing[code]} != ledger {ledger_total}")
    if round(ageing.loc[code].sum(), 2) != ledger_total:
        problems.append(f"{code}: ageing adds to {ageing.loc[code].sum()}")
    ws = load_workbook(f"statements/{code}_2026-09.xlsx")["Statement"]
    due = next(ws.cell(r, 5).value for r in range(1, ws.max_row + 1) if ws.cell(r, 4).value == "Amount due")
    if round(due, 2) != ledger_total:
        problems.append(f"{code}: workbook shows {due}")
print("problems:", problems or "none", "| total due:", round(closing.sum(), 2))

The check recomputes each balance straight from the ledger rather than from the intermediate tables, reads the written workbook back, and confirms all three agree. The sum of all closing balances should also equal the receivables control account in the general ledger — a one-line comparison worth adding if you have that figure. Only when the problem list is empty do the statements move on to emailing; Send Each Recipient Their Own Excel Report covers that loop.

Common pitfalls

• Dropping customers with no prior balance. Reindex the opening balances over all customers.
• Unstable line order. Sort by date and reference so same-day lines keep their order.
• Ageing from invoice dates instead of due dates. Overdue days start at the due date.
• Ignoring unapplied credit. Show it so the ageing still adds up to the balance.
• Sending without reconciling. Tie each statement back to the ledger first.

Conclusion

Customer statements come straight out of a receivables ledger: sum everything before the period into a balance brought forward, list the period's transactions with a grouped cumulative sum for the running balance, and age the closing balance by allocating payments and credits to the oldest invoices first. Write one formatted workbook per customer with XlsxWriter, and reconcile closing balance, ageing and the written amount due against the ledger before anything is sent.

Frequently asked questions

How do I calculate a running balance per customer in pandas? Sort the transactions by customer and date, take groupby on the customer with cumsum of the amount, and add each customer's opening balance mapped onto the rows.

How do I work out the balance brought forward? Sum all transactions dated before the statement period for each customer, and reindex over the full customer list with a zero fill so customers without earlier activity start at zero.

How are statement ageing buckets calculated? Allocate payments and credits to the oldest invoices first, then place each invoice's remaining open amount in a bucket by the number of days past its due date at the statement date.

How do I check generated statements are correct? Compare each statement's closing balance and ageing total with the ledger total for that customer, and read the written workbook back to confirm the amount due it shows.

Related

• Up one level: Generating Excel Reports from Templates — documents built from spreadsheet data.
• Build an Accounts Receivable Aging Report — ageing across all customers.
• Build a Trial Balance from a General Ledger — the ledger-to-report pattern for accounts.
• Generate an HTML Report from Excel Data with Jinja2 — another per-audience output.