Trial balance in Python — from journals to a P&L with pandas
To build a trial balance in Python, load the journal lines into pandas, check that debits equal credits, add up debit minus credit for each account, and map every account to its reporting line to get the P&L. The 51 lines of pandas below do this on a DATA DARE general ledger practice set — 33 months of journals from a 12-store grocery retailer — and the results agree with the answers in the set's Question Book.
The sets live now
Mizan Retail: Sales Up, Profit Down — Journals, Trial Balance and Budget
Build the monthly trial balance and P&L of a 12-store grocery retailer from 33 months of ERP journal lines, then explain the results against last year and budge...
Get the data
Three Companies, Four Dates, One Close — GL, Trial Balance, Bank and Budget
Reconcile three companies' ledgers to the trial balances their ERPs exported and to the bank, build calendar and fiscal-year reporting without a date table, and...
Get the dataThe steps
- Load the journal lines, the chart of accounts and the budget — three sheets of one Excel workbook.
- Check that the ledger balances: total debits equal total credits, and every journal balances on its own.
- Add up debit minus credit for each account and year. That is the trial balance; the chart of accounts maps each account to a P&L or balance-sheet line.
- Turn the P&L lines round so that income is positive, work out gross profit and margin, and compare them with the budget.
- Split the change in gross profit into a volume effect and a margin effect, and check that every month-end accrual was reversed.
The code
Download the L1 general ledger set (DD-GLT-001) and run this in the folder that holds its Excel workbook. It needs pandas and openpyxl (pip install pandas openpyxl).
import pandas as pd
pd.options.display.float_format = "{:,.2f}".format
pd.options.display.width = 120
workbook = "DD-GLT-001_GL_TrialBalance.xlsx"
lines = pd.read_excel(workbook, sheet_name="FactJournalLines")
accounts = pd.read_excel(workbook, sheet_name="DimAccount")
budget = pd.read_excel(workbook, sheet_name="FactBudget")
# 1. The ledger must balance: total debits = total credits, and so must every journal.
print(f"Debits {lines['Debit'].sum():,.2f} credits {lines['Credit'].sum():,.2f}")
by_journal = lines.groupby("JournalID")[["Debit", "Credit"]].sum().round(2)
print(f"Journals that do not balance: {(by_journal['Debit'] != by_journal['Credit']).sum()}")
# 2. The trial balance: debit minus credit for each account and year, mapped to its line.
lines["Year"] = lines["PeriodKey"] // 100
lines["Net"] = lines["Debit"] - lines["Credit"]
tb = lines.groupby(["AccountCode", "Year"])["Net"].sum().unstack(fill_value=0)
tb = accounts[["AccountCode", "Statement", "LineSortOrder", "ReportingLineName"]].merge(
tb, left_on="AccountCode", right_index=True)
# 3. The P&L by reporting line. Income is a credit, so flip the sign: income +, costs -.
keys = ["LineSortOrder", "ReportingLineName"]
pl = -tb[tb["Statement"] == "P&L"].groupby(keys)[[2024, 2025]].sum()
print("\n" + pl.droplevel(0).to_string(), "\n")
rev, cos = pl.xs("Revenue", level=1).iloc[0], pl.xs("Cost of sales", level=1).iloc[0]
gp = rev + cos
margin = gp / rev
print(f"Gross profit 2025: {gp[2025]:,.2f}")
print(f"Gross margin 2024: {margin[2024]:.2%} 2025: {margin[2025]:.2%}")
# 4. Against the 2025 budget (budget amounts are positive for income and costs alike).
b = budget[budget["PeriodKey"] // 100 == 2025].merge(accounts[["AccountCode", "ReportingLineCode"]])
b_rev = b.loc[b["ReportingLineCode"] == "PL-REV", "BudgetAmount"].sum()
b_cos = b.loc[b["ReportingLineCode"] == "PL-COS", "BudgetAmount"].sum()
print(f"Revenue vs budget: {rev[2025] - b_rev:,.2f}")
print(f"Gross profit vs budget: {gp[2025] - (b_rev - b_cos):,.2f}")
# 5. Why gross profit grew less than sales: a volume effect and a margin effect.
volume = (rev[2025] - rev[2024]) * margin[2024]
margin_effect = (margin[2025] - margin[2024]) * rev[2025]
print(f"Gross profit change {gp[2025] - gp[2024]:,.2f}"
f" = volume {volume:,.2f} + margin {margin_effect:,.2f}")
# 6. Control: every month-end electricity accrual (AJ) must be reversed by an RV journal.
accruals = lines[(lines["SourceCode"] == "AJ") & (lines["AccountCode"] == 62000)]
reversed_ids = lines.loc[lines["SourceCode"] == "RV", "ReversesJournalID"].unique()
missing = accruals[~accruals["JournalID"].isin(reversed_ids) & (accruals["PeriodKey"] < 202609)]
print("\nAccruals never reversed (one journal, spread over the stores):")
print(missing.groupby(["JournalID", "PeriodKey"])["Debit"].agg(["count", "sum"]).to_string())
What it prints
2024 and 2025, exactly as the script prints it:
Debits 846,249,564.48 credits 846,249,564.48
Journals that do not balance: 0
2024 2025
ReportingLineName
Revenue 67,129,959.71 74,734,827.36
Cost of sales -46,423,631.79 -53,033,597.11
Selling and distribution expenses -11,172,545.88 -13,251,174.50
Administrative expenses -3,746,365.37 -3,889,593.75
Other operating income 292,854.99 313,435.57
Depreciation and amortisation -2,588,809.39 -2,913,403.04
Finance income 44,876.15 42,341.66
Finance costs -882,481.34 -951,732.64
Income tax and zakat -238,847.14 -94,599.32
Gross profit 2025: 21,701,230.25
Gross margin 2024: 30.85% 2025: 29.04%
Revenue vs budget: -1,428,812.64
Gross profit vs budget: -1,741,139.75
Gross profit change 994,902.33 = volume 2,345,731.83 + margin -1,350,829.50
Accruals never reversed (one journal, spread over the stores):
count sum
JournalID PeriodKey
RET-0015400 202507 12 120,387.35
How to read the result
- The ledger balances: 846,249,564.48 of debits and the same of credits, and no journal is out of balance.
- Revenue grew from 67,129,959.71 to 74,734,827.36, but the gross margin fell from 30.85% to 29.04%, so gross profit rose by only 994,902.33.
- The bridge explains it: more sales added 2,345,731.83 of gross profit, and the lower margin took 1,350,829.50 back.
- Against the 2025 budget, revenue was 1,428,812.64 short and gross profit 1,741,139.75 short.
- One electricity accrual, journal RET-0015400 for July 2025, was never reversed: 120,387.35 across 12 cost centres. When the real bill is booked, that electricity is counted twice.
- The ledger totals, revenue, cost of sales, gross profit and margins, the budget variances, the margin effect and the missing reversal are all questions in the set, so each figure can be checked in its Question Book.
Mistakes this data will catch
- Adding debits and credits as if they had the same sign. Work with debit minus credit, then flip the sign of income for the P&L.
- Building the balance sheet from one year of lines. P&L accounts are added up for the year; balance-sheet accounts from the very first line, including the opening balances.
- Forgetting the budget sign. The budget holds income and costs both positive; the ledger does not.
- Treating a reversal as a new cost. An RV journal undoes the accrual it points to; check that each accrual has one.
The next levels
The L3 set (DD-GLT-003) comes as Parquet, JSON, CSV and Excel files for a group: several entities, journal sources and users, period status, the bank statement and a ready-made trial balance to reconcile your own to. The same steps carry over, entity by entity.
Python or Power BI
Python suits the close: a script that checks the ledger, builds the trial balance and lists the exceptions every month. Power BI suits the management pack, where the reporting lines, months and stores are slicers.
The course
- Basic Python Course Free recording Basic Python takes you from never having written a line of code to scripts that do real work on your own files. You will cover the language itself, then move quickly to the part th...
- Python for Finance — Automation & Controls Coming soon Python for Finance — Automation & Controls is about the monthly tasks that eat a week and shouldn't. You will automate reconciliations, build controls that check data instead of tr...
- Python for Finance — Advanced Analytics & Reporting Coming soon Python for Finance — Advanced Analytics & Reporting is where the scripts stop being utilities and start producing analysis. You will work with datasets larger than Excel will hold,...
- Python + AI for Finance Workflows Coming soon Python + AI for Finance Workflows is about putting a language model inside a process, not chatting with one. You will build workflows that read documents, extract what matters, che...
- Data Transformation in Python — At Scale Free recording One export of 6.18 million dirty rows, cleaned in Python and reconciled to a total you can defend. For Power Query users who have never written Python. Amounts stored as text, thre...