Published 14 September 2026

Getting Historical Monthly Trial Balance Snapshots from Xero

Run the trial balance in Xero as at 31 March, and you get the balances as the ledger stands at the moment you press the button. Run it again in September and the figures may well have moved. Nothing has gone wrong. A journal was posted with a March date in May, someone recoded a purchase invoice, an accrual was reversed and re-raised. The report is telling the truth about the ledger; it just isn't telling you what you reported in April.

That gap matters as soon as you need more than one month at a time. Comparatives, twelve-month trends, opening balance checks and year-end lead schedules all assume you have a series of month-end positions that stay put. Xero gives you one report, run one date at a time, and leaves the archiving to you. This guide covers how to build that archive in Excel, where the obvious approach quietly drifts, and how to make restatements visible instead of silent (the approach Flow takes is to re-verify historic months rather than write them once).

Two different things get called history

Before building anything, decide which of these you want, because they are not the same table.

As-reported is the figure you circulated at the time: March as you saw it on 12 April. It is what a director remembers, what last month's pack shows, and what an auditor will hold you to.

As-restated is March as the ledger stands today, including everything posted since. It is the correct figure for a trend analysis, for year-end, and for anything where you want the current best view of a past period.

Most practices need both and archive only one, which is where the arguments start. The fix is not to choose. It is to store a capture date alongside every row so both views come out of the same table. That one extra column is the difference between an archive and a pile of exports.

The manual route, and what it costs

The baseline approach is one export per month end. In Xero, go to Accounting → Reports → Trial Balance, set the date to the month end you want, run it, then Export → Excel. Repeat for every month you need.

Two properties of that workflow deserve stating plainly. First, the cost scales with months multiplied by organisations, not with reports. Two years of history across six clients is 144 exports before you have opened Power Query. Second, it is a one-way capture: once the file is on disk it never learns about the journal posted next week.

Neither is fatal for a small portfolio. Both are worth being honest about before committing to it as a monthly process. If you are going down this route, impose a filename convention on day one and never break it, because the convention is what turns a folder into a dataset:

TB-Trading-2026-03.xlsx
TB-Trading-2026-04.xlsx
TB-Property-2026-03.xlsx

Load the folder, not the files

With that convention in place, Power Query can read the whole archive as one table. Clean a single trial balance export first, using the steps in Xero trial balance in Excel with Power Query, then right-click that query and choose Create Function so the same cleaning applies to every file. Call it CleanTB.

let
    Source    = Folder.Files("C:\Reports\Xero\TB"),
    Workbooks = Table.SelectRows(Source, each Text.EndsWith([Name], ".xlsx")
                                          and not Text.StartsWith([Name], "~$")),
    Parts     = Table.AddColumn(Workbooks, "Parts",
                    each Text.Split(Text.BeforeDelimiter([Name], "."), "-"), type list),
    Org       = Table.AddColumn(Parts, "Organisation", each [Parts]{1}, type text),
    Period    = Table.AddColumn(Org, "Period",
                    each Date.EndOfMonth(#date(Number.FromText([Parts]{2}),
                                               Number.FromText([Parts]{3}), 1)), type date),
    Captured  = Table.AddColumn(Period, "CapturedOn",
                    each Date.From([#"Date created"]), type date),
    Loaded    = Table.AddColumn(Captured, "Data", each CleanTB([Content]), type table),
    Expanded  = Table.ExpandTableColumn(Loaded, "Data", {"AccountCode", "AccountName", "Net"}),
    Output    = Table.SelectColumns(Expanded,
                    {"Organisation", "Period", "CapturedOn", "AccountCode", "AccountName", "Net"})
in
    Output

The ~$ filter excludes the lock files Excel leaves behind when a workbook in that folder is open, which is the most common reason a folder query fails on a Monday morning and works on a Tuesday.

CapturedOn comes from the file's created date here, which is approximate and breaks if the folder is ever copied or restored. If you are re-exporting periods deliberately, put the capture date in the filename instead (TB-Trading-2026-03-captured-2026-09-12.xlsx) and parse it the same way as the period. Ugly, and correct.

Why the naive archive drifts

Three failure modes account for nearly all of it.

The backfill assumption. You export twenty-four months once, get a clean history, and never revisit it. Every back-dated journal posted after that date is now missing from your archive and present in Xero, so your trend and your source disagree, permanently and silently.

Overwriting in place. The opposite habit: re-export March, save over the old file, keep the archive current. Now the as-reported view is gone. Nobody can explain why the March figure in the pack differs from the March figure in the ledger, because the evidence was deleted.

Period locking as a substitute for archiving. Setting a lock date under Accounting → Advanced → Financial settings stops most users posting into closed periods, which genuinely reduces drift. It does not eliminate it. Advisers can usually post beyond a lock date, and locks get lifted for a correction and not always reinstated. A lock is a control, not a snapshot.

The pattern that survives all three is to append rather than overwrite, keying every row on organisation, period and capture date, and to re-capture recent months on a schedule rather than once. Prior periods stop moving eventually. They rarely stop moving on the date you closed them.

Make restatements visible

Once capture dates are in the table, the restatement report is a single formula. Put a month end in $B$2:

=LET(
    period,  $B$2,
    vint,    FILTER(tb[CapturedOn], tb[Period] = period),
    first,   MIN(vint),
    latest,  MAX(vint),
    codes,   SORT(UNIQUE(FILTER(tb[AccountCode], tb[Period] = period))),
    asFiled, SUMIFS(tb[Net], tb[Period], period, tb[CapturedOn], first,  tb[AccountCode], codes),
    asNow,   SUMIFS(tb[Net], tb[Period], period, tb[CapturedOn], latest, tb[AccountCode], codes),
    moved,   asNow - asFiled,
    FILTER(HSTACK(codes, asFiled, asNow, moved), ROUND(moved, 2) <> 0)
)

SUMIFS takes the spilled codes array as a criterion and returns one row per account, and the outer FILTER drops everything that hasn't moved. On a quiet month the block returns a single empty row. On a month where something landed late, it lists exactly which accounts changed and by how much, which is a far better answer to "why has March changed?" than re-running two reports and eyeballing them.

Two more checks belong on the same sheet and cost nothing:

• Each period foots. Sum Net by organisation and period. Every one should be nil. A non-nil period usually means a partial export rather than a real imbalance.
• Closing agrees to opening. For balance sheet accounts, this month's closing balance should equal next month's opening. Where it doesn't, you are missing a capture or holding two vintages you have not reconciled.

Where several entities share the archive, the organisation column and mapping patterns in consolidating multiple Xero organisations in one Excel workbook apply unchanged. History and consolidation are the same long table with a different grouping.

Pitfalls worth designing around

• The current month is not a snapshot. It changes every day. Capture it if you like, but label it provisional and never let it into a comparative column.
• Sign conventions. Fix them once in CleanTB, debits positive and credits negative. An archive with one month's convention flipped still foots and is still wrong.
• Account codes change. Map by code rather than name, and keep a mapping table, because an archive spanning two years will span at least one chart of accounts tidy-up.
• Year-end journals arrive months late. Period 12 of a closed year is the single most likely month to restate. Re-capture it after the accounts are signed, not before.
• Two years of monthly detail is not large. Twelve months of trial balance for a mid-sized client is a few thousand rows. Storage is not the constraint here; the discipline of capturing consistently is.

Where Flow fits

Flow syncs your connected Xero organisations into an Azure SQL database, holding monthly trial balance snapshots alongside invoices, credit notes and the chart of accounts. Historic months are re-verified on an ongoing basis rather than written once, so a journal back-dated into March reaches your March figures without anyone re-exporting anything, and the period is a column you filter rather than a file you have to find.

Plans start at £20/month for up to 5 organisations with 2 years of trial balance history. Plus (£40/month) covers 30 organisations with configurable 1–5 year history and real-time refresh, and Premium (£60/month) adds tracking category breakdowns across up to 60 organisations. There is a 14-day free trial, no credit card required.