Building a Simple Xero Query Tool: Because Sometimes You Just Need the Data

Screenshot of the xero query tool

The Problem: Excel Geeks Meet API Complexity

If you’ve ever worked with Xero’s accounting data, you know the platform is incredibly powerful. But here’s the frustrating part: sometimes you just need to answer a simple question.

“How many unpaid invoices do we have?”
“Which bank accounts have unreconciled transactions?”
“Show me all payments from the last 30 days.”

These should be quick, 30-second queries. Instead, you find yourself:

  • Clicking through multiple Xero screens
  • Exporting reports that almost (but not quite) show what you need
  • Manually filtering data in Excel
  • Or worse, asking someone to write a custom integration

For those of us who love Excel and think in terms of rows and columns, there had to be a better way.

The Solution: A Direct Line to Your Data

I built the Xero Query Tool as a simple, no-nonsense interface between you and the Xero API. Think of it as “SQL for accountants” — you select what you want to see, hit execute, and get clean tabular data ready for export.

What It Does

  1. Pre-configured Scenarios: Common queries like “Unpaid Invoices” or “Recent Payments” are ready to go — just one click.
  2. Direct API Access: Queries hit the Xero API directly with minimal overhead
  3. CSV Export: Every result can be exported to CSV for further analysis in Excel
  4. Aggregated Views: Special queries like “Unreconciled Bank Transactions” automatically group and sum data by account
  5. Session-Based Security: Your credentials never leave the session — no database storage, no permanence

How It Actually Works

The Architecture

The tool is built as a Next.js application with a clean separation of concerns:

┌─────────────┐
│   Browser   │  User enters Xero Custom Connection credentials
│   (React)   │  Selects scenario, sets parameters, executes query
└──────┬──────┘
       │
       ▼
┌─────────────┐
│  API Route  │  Validates credentials, manages authentication
│ (/api/xero) │  Acts as secure proxy to Xero API
└──────┬──────┘
       │
       ▼
┌─────────────┐
│  Xero Auth  │  Handles OAuth2 client_credentials flow
│   Module    │  Caches tokens (5min buffer), discovers tenants
└──────┬──────┘
       │
       ▼
┌─────────────┐
│  Xero API   │  Returns raw data (invoices, transactions, etc.)
│             │
└──────┬──────┘
       │
       ▼
┌─────────────┐
│  Transform  │  Aggregates if needed (e.g., by bank account)
│   & Format  │  Formats currency with locale awareness
└──────┬──────┘
       │
       ▼
┌─────────────┐
│   Display   │  Sortable table with CSV export
│   Results   │  Regional formatting (£1,234.56 vs 1.234,56 £)
└─────────────┘

Key Technical Decisions

1. Session-Based Credentials
No database, no permanent storage. You enter your Xero Custom Connection credentials, they live in memory for the session, then they’re gone. This eliminates a huge security surface area.

2. Configuration as JSON
All query scenarios live in public/scenarios.json. Want to add a new query? Just edit the JSON file — no code changes needed:

{
  "id": "unpaid-invoices",
  "name": "Unpaid Invoices",
  "endpoint": "https://api.xero.com/api.xro/2.0/Invoices",
  "parameters": [{
    "name": "where",
    "type": "hidden",
    "value": "Status==\"AUTHORISED\" AND AmountDue>0"
  }]
}

3. Smart Aggregation
Some queries benefit from aggregation. For example, “Unreconciled Bank Transactions” needs to:

  • Fetch all bank accounts (even those with no transactions)
  • Group unreconciled transactions by account
  • Show count and total balance per account

The tool handles this automatically when aggregateByAccount: true is set in the scenario.

4. Locale-Aware Formatting
Using Intl.NumberFormat with the browser’s locale settings means:

  • UK users see £1,234.56
  • Australian users see £1,234.56 (same number format)
  • European users might see 1.234,56 £ (regional grouping)

The currency stays GBP, but the formatting adapts to user preferences.

5. Hydration Safety
Next.js Server-Side Rendering can cause “hydration errors” when server and client render differently. We avoid this by:

  • Using deterministic ISO date formatting (YYYY-MM-DD)
  • Formatting numbers consistently on both sides
  • Doing locale-specific formatting only AFTER data loads

Why I Built This

The Real Story

I needed to match 200 transactions from a CSV against Xero’s bank transactions. Specifically:

  • Match by exact amount
  • Date within ±14 days
  • Exclude any PayPal accounts
  • Extract bank account name and status

The Xero UI wasn’t designed for this kind of bulk matching. The API was perfect — but writing a one-off script felt wasteful. What if next week I need to query something else?

That’s when it clicked: What if I could configure queries without writing code each time?

The first version was a Python script. Then it grew into a web app. Now it’s a tool I use weekly for ad-hoc Xero queries.

Who This Is For

You might love this tool if you:

  • ✅ Work with Xero data regularly
  • ✅ Think in spreadsheets and CSV files
  • ✅ Need quick answers that Xero’s UI makes complicated
  • ✅ Want direct API access without writing code every time
  • ✅ Value simple, focused tools over enterprise platforms

This probably isn’t for you if:

  • ❌ You need real-time dashboards (this is for ad-hoc queries)
  • ❌ You want to write data back to Xero (read-only by design)
  • ❌ You need automated scheduled reports (manual execution only)

The Excel Geek Philosophy

This tool embodies a specific philosophy:

  1. Data should be accessible — If it’s in the system, you should be able to query it
  2. CSV is underrated — Sometimes you just need the raw data in a spreadsheet
  3. Configuration > Code — Add new queries by editing JSON, not writing JavaScript
  4. Session-based security — No permanent credential storage means fewer attack vectors
  5. Do one thing well — Query and export. That’s it.

Technical Highlights

Multi-Endpoint Queries

Some scenarios need data from multiple endpoints. The “Unreconciled Bank Transactions” query:

  1. Calls /Accounts?where=Type=="BANK" to get all bank accounts
  2. Calls /BankTransactions?where=IsReconciled==false for unreconciled items
  3. Merges the data client-side
  4. Shows ALL accounts (even those with zero unreconciled)

This happens automatically when you set:

{
  "fetchAllAccounts": true,
  "accountsEndpoint": "https://api.xero.com/api.xro/2.0/Accounts"
}

Integer vs Decimal Formatting

A subtle but important detail: when displaying aggregated counts vs. monetary amounts:

if (Number.isInteger(value)) {
  return value.toString(); // Show "12" not "12.00"
}
if (Math.abs(value) >= 0.01) {
  return value.toFixed(2); // Show "1234.56"
}

Counts display as integers (12 transactions), balances as currency (£1,234.56).

Custom Connection vs OAuth

Xero offers multiple authentication methods. I chose Custom Connection (client_credentials grant) because:

  • ✅ No user redirect flow needed
  • ✅ Simpler implementation for internal tools
  • ✅ Long-lived credentials (user manages rotation)
  • ✅ Perfect for server-to-server API access

The tradeoff? Users need to create their own Xero app. But the instructions make this straightforward.

Lessons Learned

1. Start Simple, Stay Simple

I was tempted to add features:

  • User accounts and saved credentials
  • Scheduled queries
  • Dashboard visualizations
  • Multi-org support

But every feature adds complexity. The tool’s power is its simplicity.

2. Configuration Files Are Powerful

By putting scenarios in JSON, I’ve made the tool extensible without code changes. Non-developers can add queries.

3. Regional Formatting Matters

Early versions hardcoded UK formatting. Then I tested with users in other regions — their browser settings expected different number formats. Intl.NumberFormat solved this elegantly.

4. Hydration Errors Are Sneaky

Next.js SSR means the server renders HTML, then React “hydrates” it on the client. Any difference causes errors. Locale-specific formatting (toLocaleDateString()) was the culprit — server and client produced different dates. Solution: deterministic ISO dates for SSR, locale formatting only after hydration.

5. Documentation Is Part of the Product

Including QUERY_CONFIGURATION.md with examples means users can extend the tool themselves. The best products teach users to be self-sufficient.

Try It Yourself

The tool is straightforward to set up:

  1. Get Xero credentials:

    • Go to developer.xero.com
    • Create a “Custom Connection” app
    • Copy your Client ID and Secret

  2. Run the tool:

    • Enter your credentials (session-only, not stored)
    • Select a scenario from the dropdown
    • Hit “Execute Query”
    • Export results to CSV

  3. Customize scenarios:

    • Edit nextjs_space/public/scenarios.json
    • Add your own queries
    • Refresh browser — changes take effect immediately

You can self host this, clone the repo at https://github.com/cclambie/xero-query-tool# or I will put this somewhere publicly accessible too.

What’s Next?

I’m keeping this tool minimal by design, but a few enhancements make sense:

  • Date range presets for more scenarios (YTD, Last Quarter, etc.)
  • Column filtering in the results table
  • Dark mode (because why not?)
  • Query templates for common patterns

But the core will stay the same: query Xero data, export to CSV, keep it simple.

Final Thoughts

This tool saved me hours of clicking through Xero’s UI or writing one-off scripts. If you’re an Excel geek who works with Xero, I hope it saves you time too.

The beauty of simple tools is they solve real problems without creating new ones. No user accounts, no database, no subscription fees — just you, your data, and a direct line to the Xero API.

Sometimes the best software is the kind that gets out of your way.

Built with: Next.js 14, TypeScript, Xero API v2.0
License: MIT
Author: An Excel geek who got tired of clicking
Status: In active use, battle-tested on real accounting data

Have questions or built something similar? I’d love to hear about it. The challenge of making APIs accessible to non-developers is endlessly fascinating.

The following two tabs change content below.
My Twitter profileMy Facebook profileMy LinkedIn profileMy Instagram profileMy YouTube channel

Craig Lambie

Over the last 6 years I have managed to find the love of my life and create a family, create a successful business and know when to quit (maybe a bit late) and moved into a lifestyle of investing. Currently working on a passive income to replace my previous income, we are about 50% of the way towards it) I am passionate about helping guys become more fullfilled in their life by addressing micro problems one at a time in the sphere of Health, Wealth and Wisdom.

Leave your reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.