Consolidation in Python — intercompany eliminations with pandas
To consolidate group accounts in Python, load every company's trial balance into one pandas table, map the accounts to reporting lines, match the intercompany revenue and cost by pair of companies and leave them out, then add up what remains. The non-controlling interest is its percentage of each subsidiary's own profit. The 45 lines of pandas below do this on a DATA DARE consolidation practice set — a parent, a wholly owned subsidiary and a 70%-owned subsidiary — and the results agree with the answers in the set's Question Book.
The sets live now
Group Consolidation: Subsidiaries, NCI & Intercompany
Mizan Holding Group at its simplest: Mizan Retail owns 100% of Mizan Manufacturing and 70% of Mizan Logistics. Each company sends a monthly trial balance export...
Get the data
Multi-Currency Consolidation: Translation, Loans & Unrealised Profit
Mizan Holding Group adds a Saudi wholesale company that reports in SAR. Mizan Retail (parent, USD) owns 100% of Mizan Manufacturing, 70% of Mizan Logistics and...
Get the data
Diversified Group Close: Acquisition, Goodwill & Multi-ERP Consolidation
Mizan Holding Group closes three years across seven companies, four currencies and three ERPs. Turn raw daily journal lines from SAP, Oracle and a legacy Pakist...
Get the dataThe steps
- Load the trial balance and the account tables, and keep one period. In this set debits are positive, credits are negative and P&L balances are year to date, so December holds the full year.
- Attach each account's reporting line (Revenue, Cost of sales and so on) and its intercompany flag.
- Match intercompany revenue against intercompany cost by pair of companies. What does not cancel is an intercompany difference to investigate.
- Add the companies together without the intercompany rows, and show the unmatched difference on its own line. That is the consolidated P&L.
- Take the non-controlling interest as its percentage of each subsidiary's own profit. The rest belongs to the owners of the parent.
- On the balance sheet, net intercompany receivables against intercompany payables. The remainder is a timing gap.
The code
Download the L2 consolidation set (DD-CON-002) and run this in the folder that holds its CSV files. It needs only pandas (pip install pandas).
from pathlib import Path
import pandas as pd
pd.options.display.float_format = "{:,.2f}".format
data = Path(".") # the folder that holds the DD-CON-002 CSV files
tb = pd.read_csv(data / "DD-CON-002_FactTrialBalance.csv")
account = pd.read_csv(data / "DD-CON-002_DimAccount.csv")
mapping = pd.read_csv(data / "DD-CON-002_MapAccountToLine.csv")
lines = pd.read_csv(data / "DD-CON-002_DimReportingLine.csv")
owners = pd.read_csv(data / "DD-CON-002_DimOwnership.csv")
# 1. December 2025, each balance with its reporting line.
# Debits are positive and credits negative. P&L balances are year to date,
# so the December rows hold the whole of 2025.
dec = tb[tb["PeriodKey"] == 202512].merge(mapping, on="AccountCode").merge(account, on="AccountCode")
pl = dec[dec["ReportingLineCode"].str.startswith("PL-")]
# 2. Match intercompany revenue and cost by pair of companies.
# LOG selling to RET and RET buying from LOG both get the pair name "LOG / RET".
ic = pl[pl["IsIntercompany"] == "Y"].copy()
ic["Pair"] = [" / ".join(sorted(p)) for p in zip(ic["EntityCode"], ic["ICPartnerCode"])]
match = ic.pivot_table(index="Pair", columns="AccountClass", values="ClosingBalance", aggfunc="sum")
match["Difference"] = match["Revenue"] + match["Expense"]
print(match, "\n")
# 3. Consolidated P&L: add the three companies, leave out the intercompany rows,
# and show whatever did not match on its own line.
group = pl[pl["IsIntercompany"] == "N"].groupby("ReportingLineCode")["ClosingBalance"].sum()
group["PL-ICD"] = match["Difference"].sum()
group = -group # income positive, cost negative
report = lines.set_index("ReportingLineCode").loc[group.index].assign(Amount=group).sort_values("SortOrder")
print(report[["ReportingLineName", "Amount"]].to_string(index=False), "\n")
print(f"Group profit for 2025: {group.sum():,.2f}")
# 4. Non-controlling interest: the outside shareholders' share of each company's own profit.
own_profit = -pl.groupby("EntityCode")["ClosingBalance"].sum()
nci = (own_profit * owners.set_index("EntityCode")["NCIPct"] / 100).sum()
print(f"Attributable to non-controlling interests: {nci:,.2f}")
print(f"Attributable to owners of the parent: {group.sum() - nci:,.2f}")
# 5. Balance sheet: intercompany receivables (11500) and payables (20500) cancel.
# What is left is a timing gap, shown as intercompany suspense.
suspense = dec.loc[dec["AccountCode"].isin([11500, 20500]), "ClosingBalance"].sum()
print(f"Intercompany suspense at 31 Dec 2025: {suspense:,.2f}")
What it prints
The output for 2025, exactly as the script prints it:
AccountClass Expense Revenue Difference
Pair
LOG / MFG 2,606,892.98 -2,597,566.85 9,326.13
LOG / RET 5,281,019.31 -5,272,419.27 8,600.04
ReportingLineName Amount
Revenue 456,625,585.48
Cost of sales -310,823,932.90
Selling and distribution expenses -77,254,174.10
Administrative expenses -24,102,940.35
Other operating income 1,239,211.92
Depreciation and amortisation -18,106,011.42
Finance income 595,041.51
Finance costs -7,366,591.00
Intercompany differences (consolidation only) -17,926.17
Income tax and zakat -1,870,943.67
Group profit for 2025: 18,917,319.30
Attributable to non-controlling interests: 1,217,834.71
Attributable to owners of the parent: 17,699,484.59
Intercompany suspense at 31 Dec 2025: 52,959.30
How to read the result
- The logistics company billed the group 7,869,986.12 and the two buyers recorded 7,887,912.29, so 17,926.17 did not match. It is shown on the "Intercompany differences" line instead of disappearing into revenue or cost.
- Consolidated revenue is 456,625,585.48: the three companies' revenue without the intercompany service revenue.
- Group profit is 18,917,319.30. The logistics company is 70% owned, so 30% of its own profit — 1,217,834.71 — belongs to the non-controlling interest.
- Intercompany receivables are 52,959.30 more than intercompany payables at the year end: an invoice one company has booked and the other has not.
- Revenue, selling expenses, group profit, the non-controlling interest, the intercompany difference and the suspense balance are all questions in the set, so each figure can be checked in its Question Book.
Mistakes this data will catch
- Adding the twelve months of a P&L account. The balances are year to date; one month is this month's closing balance less last month's.
- Removing only the seller's intercompany revenue. The buyers' cost has to go as well, or group cost is overstated.
- Calculating the non-controlling interest on group profit. It is a share of the subsidiary's own profit.
- Losing the sign. Revenue is negative in the trial balance, so flip the sign once, at the end.
The next levels
The higher levels of the set come as Parquet files (pd.read_parquet) and add a foreign-currency subsidiary with an FX rate table, intercompany loans and stock, and at L4 a mid-year acquisition with goodwill. The pattern stays the same: one table, a pair key, eliminate, then add up.
Python or Power BI
Python suits the close itself: a script that runs the same way every month and leaves a record of each elimination. Power BI suits the report people open afterwards, where the eliminations are measures and every question is a slicer.
The course
- Consolidation in Power BI — Foundations Free recording Consolidation in Power BI — Foundations builds the group reporting structure most finance teams still keep in spreadsheets. You will set up the entity structure, map charts of acco...
- Group Consolidation in Power BI — Advanced Coming soon Group Consolidation in Power BI — Advanced picks up where the foundations stop: the cases that break a simple roll-up. You will work through multi-currency translation, partial own...