Build a Trial Balance from a General Ledger

The trial balance is the first report of every month-end close: every account in the ledger with its balance, debits in one column and credits in the other, and — if the books are in order — the two columns adding up to the same number. Accountants use it to spot problems before anything else is built on top: an account with a balance on the wrong side, a suspense account that should be empty, a journal that was posted to only one side. The ledger system can print one, but the raw journal lines usually end up in Excel anyway, and building the trial balance in Python makes it repeatable and lets you add the checks the accountants actually perform. This page builds it from journal lines with pandas, adds those checks, compares with the prior month, and writes a formatted workbook. It belongs to Building Finance and Operations Reports.

Prerequisites

pip install pandas openpyxl

gl_lines.xlsx holds journal lines — date, journal reference, account number, debit, credit — from the opening balances at the end of July through September. chart_of_accounts.xlsx maps each account to a name and type (Asset, Liability, Equity, Revenue, Expense). The trial balance is for September 2026.

Step 1 — Load the ledger and check it balances

Before summarising anything, confirm the raw material is sound: every journal should balance on its own, and every account used should exist in the chart of accounts:

import pandas as pd

gl = pd.read_excel("gl_lines.xlsx")
coa = pd.read_excel("chart_of_accounts.xlsx")

by_journal = gl.groupby("Journal")[["Debit", "Credit"]].sum()
unbalanced = by_journal[(by_journal["Debit"] - by_journal["Credit"]).abs() > 0.005]
unknown = sorted(set(gl["Account"]) - set(coa["Account"]))
print("journals:", len(by_journal), "| unbalanced:", list(unbalanced.index), "| unknown accounts:", unknown)
print("ledger totals:", gl["Debit"].sum(), gl["Credit"].sum())

All journals balance and every account is known, so the ledger as a whole balances too. If a journal does not, the trial balance will not either, and it is far easier to find the problem here — by journal reference — than from a difference at the bottom of the report. Comparing with a tolerance of half a cent avoids false alarms from floating-point sums of amounts read from Excel.

Step 2 — Opening, movement and closing per account

A trial balance at the end of September needs each account's balance brought forward from before the month, the month's movements, and the closing balance:

start, end = pd.Timestamp("2026-09-01"), pd.Timestamp("2026-09-30")
gl["Net"] = gl["Debit"] - gl["Credit"]

opening = gl[gl["Date"] < start].groupby("Account")["Net"].sum()
moves = gl[(gl["Date"] >= start) & (gl["Date"] <= end)].groupby("Account")[["Debit", "Credit"]].sum()

tb = (coa.set_index("Account")
        .join(opening.rename("Opening"))
        .join(moves)
        .fillna({"Opening": 0.0, "Debit": 0.0, "Credit": 0.0}))
tb["Closing"] = tb["Opening"] + tb["Debit"] - tb["Credit"]
tb = tb[(tb[["Opening", "Debit", "Credit", "Closing"]] != 0).any(axis=1)]
print(tb[["Name", "Opening", "Debit", "Credit", "Closing"]].to_string())

Balances are kept as one signed number — debit positive, credit negative — which makes the arithmetic simple: closing equals opening plus debits minus credits. Starting from the chart of accounts and joining the movements on means accounts with an opening balance but no September activity, such as Equipment, still appear; accounts with nothing at all are dropped. Revenue and expense accounts carry balances from August here because this is a year-to-date ledger; at the year end they are closed into retained earnings.

Step 3 — Debit and credit columns

The familiar presentation puts each closing balance in a Debit or a Credit column depending on its sign, and both columns must total the same:

tb["Dr"] = tb["Closing"].clip(lower=0)
tb["Cr"] = (-tb["Closing"]).clip(lower=0)
print(tb[["Name", "Dr", "Cr"]].to_string())
print(f"totals: Dr {tb['Dr'].sum():,.2f}  Cr {tb['Cr'].sum():,.2f}  difference {tb['Dr'].sum() - tb['Cr'].sum():,.2f}")

clip(lower=0) keeps positive closing balances in the debit column and the negated negative ones in the credit column. The totals agree — a balanced trial balance. A difference here, after Step 1 passed, would mean the period filter or the opening balance calculation dropped lines, which is why the check is printed every run.

Step 4 — Flag what an accountant would question

A balanced trial balance can still be wrong. Three checks catch the most common problems: balances on the abnormal side for their account type, a balance in the suspense account, and large swings against last month:

normal_side = {"Asset": "Dr", "Expense": "Dr", "Liability": "Cr", "Equity": "Cr", "Revenue": "Cr"}

def flags(row):
    out = []
    side = "Dr" if row["Closing"] > 0 else "Cr" if row["Closing"] < 0 else None
    if side and side != normal_side[row["Type"]]:
        out.append(f"abnormal {side} balance")
    if row["Name"] == "Suspense" and row["Closing"] != 0:
        out.append("suspense not cleared")
    return "; ".join(out)

tb["Check"] = tb.apply(flags, axis=1)

prior_end = start - pd.Timedelta(days=1)
prior = gl[gl["Date"] <= prior_end].groupby("Account")["Net"].sum()
tb["Prior"] = prior.reindex(tb.index).fillna(0.0)
tb["Change"] = tb["Closing"] - tb["Prior"]
print(tb.loc[tb["Check"] != "", ["Name", "Closing", "Check"]].to_string())
print(tb[["Name", "Prior", "Closing", "Change"]].loc[[1000, 1100, 4000]].to_string())

One flag appears: the suspense account holds 350 from the ADJ-17 journal — someone parked a cost they could not classify, and it needs clearing before the books close. Every other balance sits on its normal side. The comparison with the prior month-end shows movements at a glance; Accounts receivable rose from 11,600 to 15,800 as September's invoice exceeded the cash collected. Asset and expense accounts normally carry debit balances and the others credit balances; an asset in credit — a bank account overdrawn, receivables with unapplied cash — is not necessarily wrong, but it is always worth a look.

Step 5 — Write the trial balance to Excel

The workbook has one sheet with the trial balance, totals as formulas so they stay right if someone edits a line, and a check cell that turns red if the columns differ:

from openpyxl import Workbook
from openpyxl.formatting.rule import FormulaRule
from openpyxl.styles import Font, PatternFill

wb = Workbook()
ws = wb.active
ws.title = "Trial balance"
ws["A1"] = "Trial balance at 30 September 2026"
ws["A1"].font = Font(bold=True, size=14)
headers = ["Account", "Name", "Type", "Prior", "Debit", "Credit", "Change", "Check"]
ws.append([])
ws.append(headers)
for c in ws[3]:
    c.font = Font(bold=True)
for acct, r in tb.iterrows():
    ws.append([acct, r["Name"], r["Type"], r["Prior"], r["Dr"] or None, r["Cr"] or None, r["Change"], r["Check"] or None])
last = ws.max_row
ws.append(["", "Total", "", "", f"=SUM(E4:E{last})", f"=SUM(F4:F{last})"])
ws.append(["", "Difference", "", "", f"=E{last + 1}-F{last + 1}"])
for row in ws.iter_rows(min_row=4, max_row=last + 2, min_col=4, max_col=7):
    for c in row:
        c.number_format = "#,##0.00;[Red]-#,##0.00"
for col, w in zip("ABCDEFGH", (9, 22, 10, 12, 12, 12, 12, 24)):
    ws.column_dimensions[col].width = w
for c in ws[last + 1]:
    c.font = Font(bold=True)
red = PatternFill("solid", fgColor="FFC7CE")
ws.conditional_formatting.add(f"E{last + 2}", FormulaRule(formula=[f"ABS(E{last + 2})>0.005"], fill=red))
ws.conditional_formatting.add(f"H4:H{last}", FormulaRule(formula=["LEN(H4)>0"], fill=PatternFill("solid", fgColor="FFEB9C")))
ws.freeze_panes = "A4"
wb.save("trial_balance_2026-09.xlsx")
print("rows:", last - 3, "| totals at row", last + 1)

Blank cells instead of zeros (r["Dr"] or None) keep the columns readable, as on a printed trial balance. The totals are SUM formulas and the difference cell has a conditional format that turns it red if it is ever non-zero; the Check column is shaded yellow wherever a flag appears, so the suspense line stands out. The ledger amounts themselves are values — the trial balance reports the ledger; it is not a place to edit it. The same workbook is the natural source for the statements built on top of it, such as Build a Profit and Loss Statement from a General Ledger.

Step 6 — Verify the written totals

Recalculate the workbook and confirm the totals Excel computes agree with pandas — a check that the layout code put every line in:

import subprocess
from openpyxl import load_workbook

subprocess.run(["soffice", "--headless", "--convert-to", "xlsx", "--outdir", "recalc", "trial_balance_2026-09.xlsx"],
               check=True, capture_output=True)
ws = load_workbook("recalc/trial_balance_2026-09.xlsx", data_only=True).active
total_row = next(r for r in range(1, ws.max_row + 1) if ws.cell(r, 2).value == "Total")
print("Excel totals:", ws.cell(total_row, 5).value, ws.cell(total_row, 6).value,
      "| difference:", ws.cell(total_row + 1, 5).value, "| pandas:", tb["Dr"].sum(), tb["Cr"].sum())

LibreOffice recalculates the formulas while converting, and the cached results show debit and credit totals of 94,250 each, matching pandas, with a zero difference. This kind of check is cheap and catches the layout bugs — an off-by-one range in the SUM, a row skipped — that look perfectly fine on screen.

Common pitfalls

• Starting from the ledger instead of the chart of accounts. Accounts with no movement drop out; join movements onto the account list.
• Exact float comparisons. Compare debits and credits with a small tolerance.
• Ignoring abnormal balances. A balanced trial balance can still contain a credit bank balance or a debit payable.
• Leaving suspense balances. They must be cleared before the close.
• Editing amounts in the output. Fix the ledger, then regenerate.

Conclusion

A trial balance from journal lines is a few pandas steps: check every journal balances and every account exists, compute opening balances, period debits and credits and closing balances per account starting from the chart of accounts, and split signed closing balances into debit and credit columns that must total the same. Flag balances on the wrong side and uncleared suspense, compare with the prior month, and write the result with formula totals and a red difference cell — then recalculate to prove the totals agree.

Frequently asked questions

How do I make a trial balance from a general ledger in Python? Load the journal lines, sum debits minus credits per account up to the period end, join onto the chart of accounts, and put positive closing balances in a debit column and negative ones in a credit column. Both columns must total the same.

Why doesn't my trial balance balance? Usually a journal was posted unevenly, or lines were lost by a date filter or an account missing from the chart of accounts. Check each journal's debits equal its credits and that every account in the ledger is known.

What is an abnormal balance? A balance on the opposite side to the account type's normal side, such as a credit balance on an asset or a debit balance on a liability. It is not always an error but should be reviewed.

Should trial balance totals be formulas in Excel? Yes. SUM formulas for the debit and credit totals, with a difference cell that is highlighted when non-zero, keep the check visible if anyone edits the sheet.

Related

• Up one level: Building Finance and Operations Reports — finance and operations reports in Excel.
• Build a Bank Reconciliation Report — proving the bank balance in the trial balance.
• Generate Customer Account Statements from a Ledger — the receivables detail behind account 1100.
• Build a Deferred Revenue Schedule — a schedule that feeds month-end journals.