Xero trial balance CSV export

Pull a trial balance straight from the Xero API into a tidy CSV that Power BI (or pandas, or Excel) loads without cleanup. The 4 Python files (auth.py, xero_client.py, export_tb.py, token_store.py) handle consent, token refresh, the API request and CSV output.

The maintained source is under packages/xero-trial-balance-export in the Accounting Review Pipeline. The xero-trial-balance-export distribution, export-tb command, xero-tb-auth command and xero-trial-balance-export-token-cache cache format remain compatibility identifiers.

For citation metadata, see CITATION.cff and the exact xero-trial-balance-export/v0.1.11 release. The API path is read-only and cannot write to the ledger; see Scope and disclaimer and the fabricated samples/sample-output.csv output.

Quick proof

The card is built from the fabricated sample. Recalculate both balance checks and confirm that the committed proof is current:

python tools/render_quick_proof.py --check

Why

The exporter writes a fixed CSV schema, so downstream queries can use the same column names every run:

ReportDate, Tenant, Section, AccountID, AccountName, AccountCode, Debit, Credit, YTDDebit, YTDCredit

Column semantics, straight from Xero's report: Debit/Credit are the current month's movement up to the report date; YTDDebit/YTDCredit are the cumulative as-at balances, the pair an accountant means by 'the trial balance'. Slice year-end numbers on the YTD pair. AccountID is the account's stable GUID, the join key that survives code and name changes.

See samples/sample-output.csv for the exact output shape (fabricated entity).

Reproduce the offline movement and YTD gate with the fabricated fixtures in the Xero trial balance integrity evaluation.

Setup (once, ~5 minutes)

1. Create an app at developer.xero.com → New app → Web app. Redirect URI: http://localhost:8400/callback. This script intentionally accepts localhost only: it runs a local plain-HTTP callback and does not expose an OAuth listener to your LAN.
2. python -m pip install --require-hashes -r requirements.lock (Python 3.10 or newer)
3. Copy .env.example to .env, fill in the app's client ID and secret
4. python auth.py: browser opens, consent, done. On Windows, token.json is protected immediately with current-user DPAPI. Works with Xero's free Demo Company; no paid subscription needed.

Use

python export_tb.py --date 2026-06-30

Options: --tenant "name-or-id" (name substring, or an exact tenantId when display names collide), --out relative/path.csv, --payments-only (cash basis), --token-file path/to/token.json (where the token cache lives; the flag beats the XERO_TOKEN_FILE environment variable, and the default is the per-user state directory: ~/.local/state/xero-trial-balance-export/token.json on Unix, %LOCALAPPDATA%\xero-trial-balance-export\token.json on Windows. An existing token.json beside xero_client.py is still used so older clones keep working). --out must be a .csv path beneath the process working directory; absolute paths outside the working directory, .. traversal and paths through an existing symlink that escapes that directory are rejected. A missing parent directory under --out is created rather than refused (--out exports/tb.csv makes exports/ if it is not there), so a fetched report is never thrown away for want of a folder. --quiet drops the Tenant: line, the balance-check totals line and every written filename, in failure messages as well as successful ones, for a run whose log is not access-controlled; the default filename embeds the organisation name, so the output directory is printed instead of the path. The account count, the fact that a manifest was written and every warning still print. Default filename: {tenant}-{tenantid8}-tb-{date}-{accrual|cash}.csv, so the 2 bases never overwrite each other. The {tenant} segment is sanitised for filesystem safety; see the Filename reference appendix for the exact rules and their edge cases.

--date is an as-at date, not a range. Xero's Reports/TrialBalance endpoint takes only date and paymentsOnly, so this tool reproduces the Trial Balance report and cannot reproduce Trial Balance by Date Range. That second report exists in the Xero UI only; export it by hand if you need it. The Debit/Credit pair already gives you the movement for the month ending on --date, which covers the common reason people reach for a range. (Endpoint parameters checked against Xero's published OpenAPI specification on 22 August 2026.)

Every export validates the response before replacing an existing CSV or manifest. It requires exactly one report identified as TrialBalance, the expected columns, and unique, non-empty account IDs. Repeated column titles are rejected. Its single As at D Month YYYY title must match the requested date, using a full English month name. Xero's separate ReportDate is not used as the reporting period; the documented example gives those dates different values.

Both pairs must balance (movement and YTD). Where Xero supplies section or grand totals, they must equal the amounts recomputed from the account rows. A balanced omission that contradicts a supplied total is refused. These checks cannot prove completeness if rows and their control totals are both absent or consistently altered. The balance warning names the pair that failed, the account count and the difference. A refusal that cannot resolve one organisation names the connected tenant IDs and their count.

Nothing is written inside a version-control checkout unless git already ignores every path the run can leave there. That is more than the CSV and its manifest: the payload is staged as tmp*.csv.tmp beside the destination and kept when the final replace fails, the manifest is staged as tmp*.manifest.json.tmp, and a manifest from an earlier run is parked as <out>.manifest.json.previous. Ignoring *.csv and *.manifest.json ignores none of those, so the simplest arrangement is to ignore the output directory itself. The CSV holds a client's balances and the manifest names the organisation, so a destination among tracked files is one git add -A from a history every clone copies. Without --out the filename is not known until the organisation is, so the working directory is checked against a representative default-shaped name before any credential is read, and the real filename is checked again once it exists. A directory the guard cannot read, or a git it cannot run, is refused rather than written to.

The CSV is written as UTF-8 with a BOM (utf-8-sig): Excel's double-click open needs the BOM to decode non-ASCII account names correctly, and Power BI and pandas strip it automatically.

A manifest is written beside the CSV as <out>.manifest.json once the CSV is on disk. It records what the file is, which the 10 columns do not: the tenant id and name, the as-at date, the basis (accrual or cash), the CSV filename and the SHA-256 of the bytes as written, plus a UTC generated_at. Two exports that differ only in basis stay distinguishable after a rename, and a downstream review that binds to the export's digest can read it rather than type it. The manifest states only what the exporter knows, so it carries no currency. --no-manifest suppresses it. The ledger-review boundary in this repository still accepts only its synthetic-mode sample manifests; this live manifest is lineage for the operator, not yet an input to that gateway.

Power BI

1. Get Data → Text/CSV → point at the export. Columns arrive typed and tidy; Section and AccountCode are ready for slicers and drill-downs.
2. For a zero-click refresh, set the scheduled task's working directory (Windows Start in, or cron's cd) to the fixed Power BI data directory, then schedule export_tb.py with an explicit --tenant and relative --out, for example, from C:\data: python C:\path\to\export_tb.py --tenant "Org Name" --out tb-latest.csv.
3. Run the Windows task in the same Windows user profile that ran auth.py: current-user DPAPI is deliberately not a portable cache format, and a non-Windows process cannot decrypt it.
4. Pin the output name with --out, as above. The default filename embeds the report date, so a bare scheduled run writes a new file every day while Power BI keeps refreshing the stale one from setup day.

A ready-made query is committed at samples/power-bi-query.pq. Paste it into Power BI Desktop (Home → Transform data → New Source → Blank Query → Advanced Editor), set the path at the top, and it loads the 10 exported columns with explicit types, refusing any file whose header is not the exporter's. Point it at the fabricated samples/sample-output.csv to exercise the whole load path with no Xero connection. It types AccountCode as text on purpose: codes like 090 lose their leading zero as a number, which breaks slicers and joins back to the ledger.

When a run hits a locked destination, a concurrent export, or a disk that refuses the final flush, see the 'Power BI failure modes' appendix below.

Two Xero platform limits worth knowing, checked against Xero's OAuth 2.0 API limits on 13 September 2026: a new app starts in the Starter tier with 5 connections and Core raises that to 50, while a listing on the Xero App Store needs the Plus tier or above; and each organisation or practice can connect at most 2 uncertified apps, with no limit on certified ones. Read the current tiers from that page rather than from this line.

Scheduled runs

Point the job at a stable token cache first. export_tb.py resolves it in the order --token-file, then XERO_TOKEN_FILE, then an existing module-adjacent token.json, then the per-user state directory. Pin the path in a scheduled job so a checkout move cannot orphan the cache and an operator's flag always wins. The lock file (<cache>.lock) always sits beside whichever cache path wins. Run the job as the same user that ran auth.py (on Windows this is mandatory: the DPAPI cache only decrypts under that user's profile).

Authorise into that same path. auth.py has no --token-file flag: it takes the cache path only from XERO_TOKEN_FILE, read from the environment or from .env. Set it before running auth.py, or the pinned path in the recipes below names a cache auth.py never wrote and the first scheduled run fails with no token.json. Both scripts refuse a cache outside the allowed roots (your home or profile directory, the working directory, the system temp directory, or the install directory), so pin a path under one of those. Put XERO_TOKEN_FILE=~/.xero/token.json in .env (see .env.example) so both commands agree (.env gets no shell expansion, so use ~, which the path check expands, rather than $HOME), or set it for the one authorisation run:

XERO_TOKEN_FILE=$HOME/.xero/token.json python auth.py    # Linux or macOS

$env:XERO_TOKEN_FILE = "$env:USERPROFILE\xero\token.json"; python auth.py    # Windows PowerShell

Exit codes: 0 means the export succeeded and the CSV is in place. 1 means the run failed and printed a one-line reason (most failures report on stderr; the balance-check warnings print on stdout, so capture both streams). 2 means a command-line error (a malformed --date, an --out outside the working directory, or an output inside a version-control checkout that git does not ignore). Any non-zero exit writes no CSV to the destination, though a locked destination leaves the finished export beside it as a named *.csv.tmp.

cron (Linux or macOS), daily at 06:30, with both streams appended to a log:

30 6 * * * cd /srv/powerbi-data && XERO_TOKEN_FILE=$HOME/.xero/token.json /usr/bin/python3 /opt/xero-trial-balance-export/export_tb.py --tenant "Org Name" --out tb-latest.csv >> /var/log/xero-export.log 2>&1

Windows Task Scheduler: create a task that runs as the Windows user who ran auth.py, with 'Start in' set to the Power BI data directory. Action program: cmd.exe. Arguments:

/c ""C:\Python313\python.exe" "C:\tools\xero-trial-balance-export\export_tb.py" --tenant "Org Name" --out tb-latest.csv --token-file "%USERPROFILE%\xero\token.json" >> "C:\logs\xero-export.log" 2>&1"

Task Scheduler records the exit code as the task's 'Last Run Result', so a 1 or 2 there means read the log. The >> redirection is what captures the one-line error messages; without it a failed scheduled run leaves nothing to read.

The refresh-token gotcha

Xero refresh tokens rotate on use: every refresh returns a replacement refresh token. If the refresh response does not arrive, Xero permits retrying the previous token for up to a 30-minute grace period; outside that window, the user must re-authorise. The Xero OAuth FAQ was checked on 20 August 2026 (2026-08-20); recheck it for apps created or used after that date.

xero_client.py uses one TokenSession for the selected cache path. A cross-process lock covers cache reads, migration, refresh and writes. The session persists the new token pair before using the access token, writing through a temporary file and os.replace so a crash cannot leave a partial token.json. A refresh callback keeps Xero's endpoint and HTTP handling outside the cache boundary.

On Windows, the cache and any fully written recovery temporary file are DPAPI-protected before bytes reach disk. The first read of a valid plaintext cache migrates it atomically under the same lock, preserving its original obtained_at, before any Xero request.

A corrupt cache or unknown envelope version stops the run without a network call or rewrite. If a token is unusable, for example after restoring an old token.json from backup, the error directs the operator back to auth.py.

Files

Scope and disclaimer

Read-only (accounting.reports.trialbalance.read); this tool cannot write to any ledger. Web and PKCE apps created on or after 2 March 2026 use granular scopes, while existing apps using the broad accounting.reports.read scope must migrate by 13 September 2027. Xero's OAuth scope list, Granular Scopes FAQ and developer changelog were checked on 20 August 2026 (2026-08-20); recheck them for apps created or used after that date. token.json and .env are gitignored. They are credentials, so treat them like passwords.

On Windows, token.json uses the operating system's current-user Data Protection API (CryptProtectData/CryptUnprotectData) with UI forbidden. It is encrypted at rest and bound to that user's DPAPI security context; there is no repository key and no custom cryptography. Treat the cache as non-portable and re-authorise instead of trying to move it between unrelated accounts or machines. DPAPI does not protect tokens from code already running as that user, an administrator controlling the machine, or a compromised user session. .env remains plaintext because it must supply the OAuth client credentials, so keep the clone inside your own user profile. On a shared machine, restrict the clone directory before scheduling anything, for example with icacls <clone-dir> /inheritance:r /grant:r <your-username>:(OI)(CI)F.

Python's standard library has no equivalent portable secret store. On non-Windows systems the project therefore retains its existing plaintext JSON cache as an explicit compatibility fallback and forces its mode to owner-read/write only (0600) on every save and load. Use a private account and directory, and do not copy a Windows DPAPI envelope to Linux or macOS: it cannot be decrypted there. token.json.lock contains only zero-valued lock bytes and no credentials. MIT-licensed utility code, no warranty; outputs feed professional review like any other workpaper input. Not affiliated with or endorsed by Xero. Full boundary statement: DISCLAIMER.md.

Tests

With the dependencies installed, run the offline regression suite from this component directory (packages/xero-trial-balance-export):

python -B -m unittest discover -s tests -v

Power BI failure modes

Concurrent exports using the same checkout serialise their token-cache read, migration, refresh and write through token.json.lock; a waiter re-reads the rotated cache instead of spending the same refresh token. The lock coordinates processes using that local cache, not copies of token.json on other machines.

If the destination CSV is locked when the export finishes (Excel or Power BI Desktop holding it open), the run retries briefly, then exits non-zero and leaves the finished export beside it as a *.csv.tmp, naming that file in the error. Rename it into place rather than re-running, because the report has already been fetched and a re-run spends another refresh token.

A disk that refuses the final flush is handled the same way: once the rows are written the *.csv.tmp is complete and balance-checked, so it is kept and named in the error instead of being deleted. Nothing deletes those files, so a scheduled job against a destination that stays locked leaves one per run.

Filename reference

The default filename is {tenant}-{tenantid8}-tb-{date}-{basis}.csv, for example demo-company-au-{tenantid8}-tb-2026-06-30-accrual.csv. This naming rule changed every default filename. If a refresh still reads an old path, pin the destination with --out so it reads the file the exporter writes.

The exporter composes the organisation name to NFC. For the {tenant} segment, it collapses each run of characters outside ASCII letters, digits, ., _ and - to a single -, trims leading and trailing -, then lowercases the result. Accents, macrons, Cyrillic, Chinese and emoji are treated like punctuation. NFC normalisation gives composed and decomposed spellings the same filename.

Different names can therefore collapse to the same segment:

• 'Acme (Holdings) Pty Ltd' and 'Acme Holdings Pty Ltd' both become acme-holdings-pty-ltd.
• 'ACME Pty Ltd' and 'Acme Pty Ltd' both become acme-pty-ltd.
• Names differing only in Chinese characters can both become pty-ltd.

The exporter also collapses unsafe character runs in the tenant ID to -, takes the first 8 characters and trims leading and trailing - from that suffix. It appends the suffix to every default name to distinguish organisations whose names collide, keeping the filename to one path segment. If the organisation name leaves no usable characters, the suffix forms the whole tenant segment.

Related

accounting-excel-toolkit: Power Query parsers for the manual-export path, when API access isn't on the table.

Author

Ryan Duguid, accountant in Newcastle NSW, provisional member of Chartered Accountants ANZ.