Metadata-Version: 2.4
Name: xero-trial-balance-export
Version: 0.1.8
Summary: Export a Xero trial balance to a tidy CSV for Power BI, pandas or Excel.
License-Expression: MIT
Project-URL: Homepage, https://github.com/ryanduguid/accounting-review-pipeline/tree/main/packages/xero-trial-balance-export
Project-URL: Issues, https://github.com/ryanduguid/accounting-review-pipeline/issues
Classifier: Programming Language :: Python :: 3
Classifier: Programming Language :: Python :: 3.10
Classifier: Programming Language :: Python :: 3.11
Classifier: Programming Language :: Python :: 3.12
Classifier: Programming Language :: Python :: 3.13
Classifier: Operating System :: OS Independent
Classifier: Topic :: Office/Business :: Financial :: Accounting
Requires-Python: >=3.10
Description-Content-Type: text/markdown
License-File: LICENSE
Requires-Dist: requests>=2.34.2
Provides-Extra: dev
Requires-Dist: ruff==0.16.6; extra == "dev"
Requires-Dist: mypy==2.3.1; extra == "dev"
Requires-Dist: pytest==9.1.1; extra == "dev"
Requires-Dist: pytest-cov==7.1.0; extra == "dev"
Requires-Dist: coverage==7.16.0; extra == "dev"
Requires-Dist: build>=1.2; extra == "dev"
Dynamic: license-file

# Xero trial balance CSV export

![xero-trial-balance-export](assets/banner.svg)

[![Verify](https://github.com/ryanduguid/accounting-review-pipeline/actions/workflows/ci.yml/badge.svg)](https://github.com/ryanduguid/accounting-review-pipeline/actions/workflows/ci.yml) [![PyPI](https://img.shields.io/pypi/v/xero-trial-balance-export.svg?color=5C2D91&labelColor=04001F)](https://pypi.org/project/xero-trial-balance-export/) [![License: MIT](https://img.shields.io/badge/License-MIT-4F485E.svg?labelColor=04001F)](LICENSE) [![Python 3.10+](https://img.shields.io/badge/python-3.10%2B-5C2D91.svg?logo=python&logoColor=white&labelColor=04001F)](https://www.python.org/downloads/)

Pull a trial balance straight from the Xero API into a tidy CSV that Power BI (or pandas, or Excel) loads without cleanup. The four 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`](CITATION.cff) and the exact
[`xero-trial-balance-export/v0.1.7` release](https://github.com/ryanduguid/accounting-review-pipeline/releases/tag/xero-trial-balance-export%2Fv0.1.7).
The API path is read-only and cannot write to the ledger; see
[Scope and disclaimer](#scope-and-disclaimer) and the fabricated
[`samples/sample-output.csv`](samples/sample-output.csv) output.

## Quick proof

[![Validated fabricated trial balance](assets/quick-proof.svg)](assets/quick-proof.md)

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

```bash
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`](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](evaluation/xero_tb_integrity/README.md).

## Setup (once, ~5 minutes)

1. Create an app at [developer.xero.com](https://developer.xero.com/app/manage) → 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

```bash
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. Default filename: `{tenant}-{tenantid8}-tb-{date}-{accrual|cash}.csv`, so the two bases never overwrite each other. The `{tenant}` segment is sanitised for filesystem safety; see the [Filename reference](#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 runs a balance check before anything touches disk. Both pairs must balance (movement **and** YTD), and the expected report columns must all be present; otherwise no file is written and the script exits non-zero, so a truncated or reshaped report can never slip into a refresh pipeline.

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.

## 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`, e.g. 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`](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 ten exported columns with explicit types, refusing any file whose header is not the exporter's. Point it at the fabricated [`samples/sample-output.csv`](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: uncertified apps connect to at most 25 organisations (the Demo Company doesn't count), and going past that requires App Partner certification.

## 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:

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

```powershell
$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). 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:

```cron
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](https://developer.xero.com/faq/oauth2) was checked on 20 August 2026 (`2026-08-20`); recheck it for apps created or used after that date.

[`xero_client.py`](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

| File | Purpose |
|---|---|
| [`auth.py`](auth.py) | One-time browser consent → `token.json` |
| [`xero_client.py`](xero_client.py) | Token cache, rotation-safe refresh, authed GET with 429 and 401 retries |
| [`export_tb.py`](export_tb.py) | Fetch report → flatten nested rows → CSV + balance check |
| [`token_store.py`](token_store.py) | Resolves the cache path (flag, env, legacy module-adjacent file, then per-user state) |

## 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](https://developer.xero.com/documentation/guides/oauth2/scopes/), [Granular Scopes FAQ](https://developer.xero.com/faq/granular-scopes) and [developer changelog](https://developer.xero.com/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](https://learn.microsoft.com/en-us/windows/win32/api/dpapi/nf-dpapi-cryptprotectdata) (`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](DISCLAIMER.md).

## Tests

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

```bash
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 eight 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`](https://github.com/ryanduguid/accounting-review-pipeline/tree/main/adapters/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.
