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 | Yes | Period start date (cell reference) |
| end_date | Yes | Period end date (cell reference) |
| 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
- Balance Sheet accounts (Asset, Liability, Equity): Returns balance AS OF end_date - the start_date is ignored
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.