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 supportsmultiple 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.

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). Without that tier, you can still pull data through individual endpoints (invoices, bills, bank transactions), 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.

The critical detail: the Journals endpoint is only available from the Advanced tier (A$1,445/month, roughly US$940). 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.

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 US$420 to US$11,340/year 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), we're looking at $2,800 to $18,000 build cost.

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)

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 — directly into Google Sheets, with implementation starting at $150 per report and $10/entity/month.

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.