Build an Intercompany Reconciliation

When companies in the same group trade with each other, each records its side: the UK company books a receivable from its German sister, and the German company books a payable to the UK. On consolidation the two must cancel exactly — the group cannot owe money to itself — and at every month end they rarely do. One side booked an invoice the other has not received yet; the German side recorded a recharge in euros at a different rate; someone posted a payment to the wrong intercompany account; one entry has no counterpart at all. The intercompany reconciliation finds each difference and sorts it into a category the two finance teams can act on. This page builds it with pandas from the two companies' intercompany ledgers: matching entries by reference, converting to the group currency, separating timing, exchange and genuine differences, checking that the balances eliminate, and writing a shared workbook. It belongs to Building Finance and Operations Reports.

Prerequisites

pip install pandas openpyxl

The workbook holds the September 2026 intercompany ledger lines from both sides: the UK company's account due from Germany (receivable, positive amounts increase it) and the German company's account due to UK (payable, negative amounts increase it), each in the currency of the transaction. A rates file gives the group's month-end rate for euros.

Step 1 — Put both sides on the same basis

import pandas as pd

uk = pd.read_excel("intercompany.xlsx", sheet_name="UK - due from DE").assign(Side="UK")
de = pd.read_excel("intercompany.xlsx", sheet_name="DE - due to UK").assign(Side="DE")
rates = pd.read_excel("rates.xlsx").set_index("Currency")["Rate to GBP"]

def to_group(df, flip):
    out = df.copy()
    out["GBP"] = (out["Amount"] * out["Currency"].map(rates) * (-1 if flip else 1)).round(2)
    return out

uk_g, de_g = to_group(uk, flip=False), to_group(de, flip=True)
print(f"UK balance due from DE: {uk_g['GBP'].sum():,.2f} GBP | DE balance due to UK: {de_g['GBP'].sum():,.2f} GBP")

Two adjustments make the sides comparable. The German ledger shows its payable as negative numbers, so its signs are flipped: after flipping, both sides show "amount owed by Germany to the UK" as positive. And every amount is converted to sterling at the group's month-end rate, so euro transactions on either side can be compared with sterling ones. On this basis the UK says Germany owes it 60,606.40; Germany says it owes 51,942.26. The 8,664.14 gap is what the reconciliation has to explain.

Step 2 — Match entries by reference

m = uk_g.merge(de_g, on="Reference", how="outer", suffixes=("_UK", "_DE"), indicator=True)
m[["GBP_UK", "GBP_DE"]] = m[["GBP_UK", "GBP_DE"]].fillna(0)
m["Difference"] = (m["GBP_UK"] - m["GBP_DE"]).round(2)

def classify(r):
    if r["_merge"] == "left_only":
        return "Only in UK (in transit or missing in DE)"
    if r["_merge"] == "right_only":
        return "Only in DE (not booked in UK)"
    if abs(r["Difference"]) < 0.01:
        return "Matched"
    if r["Currency_UK"] != r["Currency_DE"]:
        return "Exchange difference"
    return "Amount differs"

m["Category"] = m.apply(classify, axis=1)
print(m[["Reference", "Description_UK", "GBP_UK", "GBP_DE", "Difference", "Category"]].to_string(index=False))

An outer merge on the transaction reference lines up each entry with its counterpart and keeps entries that exist on only one side, which indicator=True labels. The classification then follows the questions a group accountant asks. An entry only in the UK books is usually in transit — invoiced on the 29th and not yet received in Germany — or was never recorded there. An entry only in the German books is something the UK has not booked, such as a recharge raised by Germany. Where both sides have the entry but the sterling amounts differ, the cause is usually exchange: one side booked the invoice in sterling, the other converted it to euros at the rate of the day, and the month-end rates no longer agree. A difference between entries in the same currency is a genuine amount error.

Step 3 — Summarise and check the elimination

summary = m.groupby("Category")["Difference"].agg(["count", "sum"]).rename(columns={"count": "Lines", "sum": "GBP"}).round(2)
print(summary.to_string())
gap = round(uk_g["GBP"].sum() - de_g["GBP"].sum(), 2)
explained = round(summary["GBP"].sum(), 2)
print(f"gap {gap:,.2f} | explained by categories {explained:,.2f} | unexplained {gap - explained:,.2f}")
after = round(gap - summary.loc[summary.index.str.startswith(("Only in UK", "Exchange")), "GBP"].sum(), 2)
print(f"difference left after in-transit and FX items: {after:,.2f}")

The categories add up exactly to the gap between the two balances, which is the check that the reconciliation is complete. Then comes the question that matters for consolidation: once items in transit are booked by the receiving company and exchange differences are handled under the group's policy, what genuine difference remains? The 8,664.14 gap breaks down into 9,600.00 of goods shipped on 29 September and not yet received in Germany, 64.14 of exchange differences — 64.11 of it on the royalty, which Germany converted at a different rate from the group's, the rest pennies of rounding — and −1,000.00 for a freight recharge Germany booked that the UK never raised. After the in-transit and exchange items, that −1,000.00 is the genuine disagreement one of the two finance teams has to resolve before the balances will eliminate. Comparing two sets of records line by line is the same technique as in Build a Bank Reconciliation Report.

Step 4 — A workbook both teams can use

from openpyxl import load_workbook
from openpyxl.styles import Font

path = "ic_recon_UK_DE_2026-09.xlsx"
cols = ["Reference", "Date_UK", "Date_DE", "Description_UK", "Description_DE", "Currency_UK", "Amount_UK",
        "Currency_DE", "Amount_DE", "GBP_UK", "GBP_DE", "Difference", "Category"]
with pd.ExcelWriter(path, engine="openpyxl") as xw:
    m.sort_values(["Category", "Reference"])[cols].to_excel(xw, sheet_name="Line by line", index=False)
    summary.to_excel(xw, sheet_name="Summary", startrow=4)
wb = load_workbook(path)
ws = wb["Summary"]
ws["A1"], ws["A1"].font = "Intercompany reconciliation UK ↔ DE, 30 September 2026 (GBP)", Font(bold=True, size=13)
ws["A2"], ws["B2"] = "UK: due from DE", round(uk_g["GBP"].sum(), 2)
ws["A3"], ws["B3"] = "DE: due to UK", round(de_g["GBP"].sum(), 2)
ws["A4"], ws["B4"] = "Gap", "=B2-B3"
for c in ("B2", "B3", "B4"):
    ws[c].number_format = "#,##0.00"
wb.save(path)
print(wb.sheetnames)

The Summary sheet states both balances, the gap and its explanation by category; the Line by line sheet shows each reference with both sides' dates, descriptions, original currencies and amounts and the sterling comparison, sorted by category so each team can work through its own list — the German team confirms receipt of the in-transit goods, the UK team raises or disputes the freight recharge. With more than two companies, run the same comparison for every pair, and add a matrix of net balances between all entities; the group as a whole eliminates only when every pair does.

Making the matching reliable

The whole reconciliation depends on both companies recording the same reference for the same transaction. Where that discipline exists, the merge on reference does nearly all the work. Where it does not, references must be agreed first: the selling company's invoice number is the natural key, and the buying company should record it in its own entry. Without a shared reference, matching falls back on amount and date within a window — workable, but ambiguous when several transactions have similar amounts. It also helps to agree a cut-off: if both companies stop posting intercompany entries a day before month end, in-transit items mostly disappear. And book intercompany invoices in one agreed currency, usually the seller's, so the buying company converts once and both sides use the same amount, which removes most exchange differences before they arise.

Common pitfalls

• Comparing without flipping signs. A receivable is positive, the matching payable negative.
• Mixing currencies. Convert both sides to the group currency at one rate.
• An inner merge. Use an outer merge so one-sided entries appear.
• Explaining the total, not the lines. Classify every line and show they sum to the gap.
• Treating FX as an error. Exchange differences follow group policy, not corrections.

Conclusion

Reconcile intercompany balances by flipping the payable side's signs, converting both sides to the group currency, and outer-merging the entries on a shared reference. Classify each line as matched, exchange difference, amount error, or one-sided — in transit or not booked — check that the categories add up to the gap between balances, and isolate what remains after timing and exchange. Write a summary and a line-by-line sheet so both finance teams can clear their items before consolidation.

Frequently asked questions

How do I reconcile intercompany balances in Python? Load both companies' intercompany ledger lines, flip the signs on the payable side, convert to one currency, outer-merge on the transaction reference and classify each line by how the two sides differ.

What are typical causes of intercompany differences? Items in transit at month end, exchange differences when the two sides record different currencies, entries booked by only one side, and amount or account errors.

How do I check that intercompany balances eliminate? After converting to the group currency, the receivable on one side and the payable on the other must be equal; explain any gap line by line.

What if the two companies use different references? Agree a shared key, usually the seller's invoice number, or fall back to matching by amount and date within a window, which is less reliable.

Related

• Up one level: Building Finance and Operations Reports — finance reports from Python.
• Convert Multi-Currency Transactions with an FX Rate Table — the currency step.
• Build a Foreign Exchange Revaluation Report — exchange differences at month end.
• Find Rows in One Excel File Missing from Another — one-sided matching.