Building Custom Reports with the Xero API

At some point, Xero's built-in reports stop cutting it. Maybe you need to consolidate across a few entities, break out profitability by tracking category, or build a P&L that actually matches how your business works. The Xero API lets you do all of that.

And here's the thing — this kind of project is way more accessible than it used to be. With AI coding tools like Claude Code, Cursor, and Copilot, you don't need to be a seasoned developer to get a working integration off the ground. The API boilerplate, authentication flows, data transformation scripts — these tools can scaffold most of it for you in an afternoon. You still need someone who understands the financial logic (what to map, what to eliminate, what to aggregate), but the engineering barrier is genuinely lower than it's ever been.

So whether you're a developer on a finance team or a technically curious accountant willing to get your hands dirty, this is a realistic project. Here's what's involved.

What You're Working With

Xero's API is REST-based with OAuth 2.0 auth, and it exposes most of the data sitting behind the platform. For reporting, the endpoints you'll care about most:

• Journals — every posted transaction as a set of debit/credit lines. This is the most powerful dataset for reporting, but more on access restrictions later.
• Accounts & Chart of Accounts — the structure you'll map everything against
• Invoices, Bills & Credit Notes — your AR and AP data
• Bank Transactions — reconciled transaction records
• Tracking Categories — how Xero tags things by department, location, cost centre, etc.
• Purchase Orders & Inventory Items — procurement and stock data
• Reports endpoint — pre-built Balance Sheet, P&L, and Trial Balance as structured JSON

The Reports endpoint is handy for grabbing standard statements quickly, but it's rigid. For anything custom, you'll want to pull raw data and build the logic yourself.

If this sounds too complex, you might want to check out how you can create custom reports through the non-programmatic approach. That does not require the use of Xero API and is generally a better approach if you're afraid to get technical.

How the Pieces Fit Together

A typical setup follows this flow:

Xero Org(s) → API Layer → Data Staging → Transformation → Output (Sheets/Dashboard/PDF)

Recommended Tech Stack

Tech stack: Python with the xero-python SDK is probably the most common path — pandas makes the data transformation side much easier. Node.js with xero-node works too. For output, most finance teams prefer Google Sheets since it's familiar and collaborative. Host the whole thing on a small cloud function or VPS, trigger it on a schedule, and you're set.

Step-by-Step Implementation

1. Authentication & Connection

Xero uses OAuth 2.0 with access tokens that expire every 30 minutes. The SDKs handle most of the heavy lifting, but you need a wrapper that stores tokens securely, refreshes them automatically, and supports multiple Xero orgs if needed (each org has its own tenant_id).

# Example: Token refresh pattern (Python) 
from xero_python.api_client import ApiClient 
from xero_python.api_client.oauth2 import OAuth2Token 
def get_fresh_token(stored_token): 
  if token_is_expiring_soon(stored_token): 
    new_token = client.refresh_oauth2_token( 
      client_id, client_secret, stored_token 
    ) 
    save_token(new_token) # persist to DB or secrets manager 
    return new_token 
  return stored_token 

Tip: Build token management early and make it solid. A failed silent refresh is the number one cause of broken overnight report jobs.

Scopes have changed too. Apps created since 2 March 2026 must request granular scopes (for example accounting.invoices.read, accounting.banktransactions.read and the per-report scopes) instead of the broad accounting.transactions. Older apps can keep their broad scopes until September 2027. Also, the user who authorises the connection needs reports permission: Standard users without it can no longer reach the Journals or ManualJournals endpoints.

2. Data Extraction

For most financial reports, the Journals endpoint is your primary data source. It returns every posted transaction as a set of journal lines with account codes, amounts, dates, and tracking categories.

# Pulling journals with pagination 
from xero_python.accounting import AccountingApi 
def fetch_all_journals(api_client, tenant_id, offset=0): 
  journals = [] 
  accounting_api = AccountingApi(api_client) 
  while True: 
    response = accounting_api.get_journals( 
      tenant_id, offset=offset
    ) 
    batch = response.journals 
    if not batch:
      break 
    journals.extend(batch)
    offset = batch[-1].journal_number 
  return journals

One thing to know: Journals are locked behind the Advanced tier (A$1,445/month — more on pricing below). It also needs Xero app certification, an initial and annual security assessment, and Xero's approval of your use case, and the accounting.journals.read scope is no longer granted by default. Without that tier, you can still pull data through individual endpoints (invoices, bills, bank transactions, and ManualJournals, which is open to every tier), but it takes more API calls and more code to stitch together.

Either way, cache locally after your first full sync, then use offsets or If-Modified-Since headers to grab only what's new. This keeps you well within rate limits and makes report generation fast.

3. Data Transformation

This is the core of the project and where your reports become genuinely yours. Raw journals are transactional — your job is to aggregate, filter, and reshape them into meaningful views.

Common transformations:

• Mapping to a reporting chart of accounts: Your internal reporting structure often differs from Xero's chart of accounts. Build a mapping table (a simple CSV or database table works) that groups Xero account codes into your reporting line items.
• Aggregation by period and dimension: Group journal lines by month, tracking category, or any combination that makes sense for your report.
• Multi-currency handling: If you operate across currencies, decide whether to convert at transaction date rates or period-end rates. Xero stores both the source currency amount and the base currency amount on each journal line — use whichever fits your reporting policy.
• Intercompany eliminations (for multi-entity): Tag intercompany accounts in your mapping table, then net them off during consolidation.

4. Output & Delivery

Google Sheets is a strong default output for finance teams — it's collaborative, familiar, and allows stakeholders to slice data further without needing developer involvement.

import gspread 
from google.oauth2.service_account import Credentials 
creds = Credentials.from_service_account_file('service_account.json') 
gc = gspread.authorize(creds) 
sheet = gc.open('Monthly P&L').sheet1 
# Write summary data 
sheet.update('A1', [df.columns.tolist()] + df.values.tolist()) 

For more polished dashboards, a Next.js app with a charting library like Recharts works well.

5. Testing Financial Logic

Financial reports need to tie out. Build checks into your pipeline:

• Trial balance check: Debits must equal credits for every period. If they don't, you've got a data issue.
• Reconciliation to Xero: Compare your report totals against Xero's built-in P&L or Balance Sheet (via the Reports endpoint) to catch mapping gaps.
• Period boundary checks: Verify that your date filtering handles Xero's UTC timestamps correctly relative to your organisation's timezone.

What to Budget

Xero API Access

Xero introduced usage-based developer pricing in March 2026. The tier you need depends on how many Xero organisations you're connecting and which endpoints you use.

Prices are in AUD and tax-exclusive. Egress is the data you download from Xero, and anything beyond your allowance costs A$2.40 per GB. Starter is capped at 5 connections; adding a sixth needs a payment card and moves you to Core. The paid tiers also involve Xero's app certification process, and there is an Enterprise tier with custom pricing.

The critical detail: the Journals endpoint is only available from the Advanced tier (A$1,445/month, roughly US$1,030 at about 0.71 in September 2026), plus the certification, annual security assessment and use-case approval mentioned earlier. Journals are the most efficient way to pull complete transaction data for reporting.

Without them, you can still build reports using individual endpoints (invoices, bills, bank transactions), but the integration is significantly more complex and requires more API calls to cover the same ground.

Two things can change this maths. Xero exempts bespoke integrations that accountants and bookkeepers build for their own practice or a single client from these tiers, and Custom Connections (single-organisation integrations) remain on the same commercial terms as before. But Custom Connections created since 29 April 2026 no longer include journals access, so check your case with Xero before budgeting for a tier.

For a single business with a few entities, the Starter tier (free) may be enough if you work around the Journals limitation and stay within 1,000 calls/day. For anything beyond that, factor in A$420 to A$17,340 a year (roughly US$300 to US$12,300 at about 0.71 in September 2026) in API access alone, depending on your tier.

Developer Cost

With AI coding tools (Cursor, Copilot, Claude Code), an experienced developer can build a single custom report integration in roughly 28–65 hours — about half what it would have taken a few years ago. The API boilerplate compresses dramatically with AI assistance.

What takes longer is getting the financial logic right. Mapping account codes, handling intercompany eliminations, and verifying reports tie out still requires someone who understands the accounting, not just the code.

At typical freelance rates ($100-$150/hr), a single-entity build comes to roughly $2,800 to $9,000. Multi-entity builds take longer and can reach about $18,000.

Hosting & Maintenance

Hosting is negligible — $5–$50/month, often free-tier eligible. Ongoing maintenance (Xero API updates, new edge cases, evolving report requirements) typically runs $1,000–$5,000/year.

Total Cost of Ownership (TCO)

The recurring column is per year: the annual API fee plus $1,000–$5,000 of maintenance, converted at about 0.71 USD per AUD (September 2026). The API fee is $0 on Starter, about $300 on Core, about $2,100 on Plus and about $12,300 on Advanced. So the Core/Plus row runs from Core plus the lowest maintenance ($1,300) to Plus plus the highest ($7,100). Hosting is left out (it adds $60–$600 a year), and so is the certification and annual security assessment work the Advanced tier needs, which this table doesn't price.

When Building Makes Sense

A custom build is the right move if:

• Your business logic is genuinely one-of-a-kind
• You have developers in-house with bandwidth and some Xero API familiarity
• The reporting system is part of your product (e.g., you're building a fintech tool)
• You need real-time data with sub-minute freshness

For most finance teams and accounting firms though, these don't all apply at once.

... or Just Skip the Build

If you need custom Xero reports but don't want to build and maintain the integration yourself, Cheetah is definitely worth a look. It delivers custom reports — built to your specific business logic, not templates — as Google Sheets with a one-click Excel download, and reports can run on a schedule. Implementation starts at $150 per report, then from $10/entity/month for accounting firms or $40/month flat for in-house finance teams.

Particularly solid for multi-entity setups and accounting firms that want the output of a custom build without owning the infrastructure.

That said, if you've got the skills and want full control, the Xero API is well-documented and the SDKs are solid. Hopefully this gives you a good starting point.

---

Updated 20 September 2026: we added what Xero now requires for the Journals endpoint (app certification, a security assessment and use-case approval), the new granular OAuth scopes and reports-permission rule, and the exemptions for bespoke integrations and Custom Connections. We also corrected the US dollar conversions and the cost table, which mixed currencies and labelled yearly figures as monthly, and updated Cheetah's pricing line.