XO.BALANCE
Get the balance for a single GL account. For P&L accounts, returns activity between dates. For Balance Sheet accounts, returns balance as of end date.
Syntax
Parameters
| Parameter | Required | Description |
|---|---|---|
| org_id | Yes | Organization ID from XO.ORG() |
| account | Yes | GL account code (e.g., "4000") |
| start_date | Conditional | Period start date. Must be a cell reference (e.g., C1) or an Excel serial date number. Typed text dates will cause an error. Required for P&L accounts; ignored for Balance Sheet accounts. |
| end_date | Yes | Period end date. Must be a cell reference (e.g., D1) or an Excel serial date number. Typed text dates will cause an error. |
| category1 | No | First tracking category name (e.g., "Region") |
| option1 | No | First tracking option (e.g., "North") |
| category2 | No | Second tracking category name |
| option2 | No | Second tracking option. Leave blank to include all transactions (no filter). |
P&L vs Balance Sheet Accounts
- P&L accounts (Revenue, Expense): Returns activity between start_date and end_date. Date range cannot exceed 365 days (Xero API limitation).
- Balance Sheet accounts (Asset, Liability, Equity): Returns balance AS OF end_date - the start_date is ignored. No date range limit.
Exact as-of-date balances
- Untracked Balance Sheet accounts (bank, accounts receivable, accounts payable, fixed assets, loans, equity) return the exact balance as of
end_dateon any date — including non-month-end period-ends such as 4-4-5, 52-week, or weekly close calendars. - A tracking filter or
[none]on a Balance Sheet account requires a month-endend_date. Xero only reports a tracked Balance Sheet at month-end, so on a non-month-end date the cell shows an explicit error instead of a wrong number. Remove the tracking filter to get the exact mid-month balance for the account total. - Current Year Earnings is not a chart-of-accounts account and cannot be queried with XO.BALANCE — use XO.PROFIT for net income over a period.
- P&L accounts(Revenue, Expense) are unaffected — they return exact activity for any date range up to Xero's 365-day limit, including with
[none].
Examples
Basic usage:
=XO.BALANCE(A2, "4000", C1, D1)Gets revenue for account 4000 for the date range in C1 to D1.
With tracking filter:
=XO.BALANCE($A$2, "6200", C1, D1, "Region", "North")Two tracking categories:
=XO.BALANCE(A2, "6200", C1, D1, "Region", "North", "Project", "Alpha")Sign Convention
XO.BALANCE automatically returns amounts with intuitive signs - you don't need to think about debits and credits:
| Account Type | Result |
|---|---|
| Revenue | Positive when you earn money |
| Expense | Positive when you spend money |
| Assets | Positive when normal debit balance |
| Liabilities | Positive when normal credit balance |
| Equity | Positive when normal credit balance |
Using [none] as a Tracking Option
You can pass [none] as a tracking option value to retrieve the balance for transactions that have no tracking category assigned:
=XO.BALANCE(A2, "4000", C1, D1, "Region", "[none]")Returns revenue for account 4000 where no Region tracking option is assigned.
- Useful for finding transactions not assigned to any tracking category
- Works with both category1/option1 and category2/option2 parameter pairs
- Note: There may be a performance impact when using
[none]as it requires additional API calls
Troubleshooting
If you see an error, check the troubleshooting guide. XO Report shows readable guidance messages for connection issues (not caught by IFERROR) and standard Excel errors (#VALUE!, #N/A) for data problems — hover over the cell to see the specific message.
For a complete overview of building Xero reports in Excel — including how this function fits into a full workflow — see our complete Xero to Excel guide.