Manual Journals Table
Pull manual journals from Xero: accruals, prepayment releases, depreciation, reclassifications and year-end adjustments. One row per journal line, with the journal's date, narration and status repeated on each of its lines.
Pick a date range and the statuses you want once, and refresh the table from XO Report instead of re-exporting a CSV. Available in Excel and Google Sheets, with several organisations in one table.
Parameters
| Parameter | Required | Description |
|---|---|---|
| Date Range | Yes | Filter by journal date. The From date must be on or before the To date |
| Status | Yes | Draft, Posted, Voided, Deleted, Archived. Draft and Posted are ticked by default. At least one must stay ticked |
| Amount Column | No | Optional signed line amount (debit positive, credit negative) in the Columns step (unticked by default) |
Output Columns
| Column | Description |
|---|---|
| Date | Journal date |
| Narration | The journal's narration |
| Status | DRAFT, POSTED, VOIDED, DELETED or ARCHIVED |
| AccountCode | The line's account code |
| AccountName | The account's current name in the chart of accounts |
| Description | The line's description |
| Debit | The line amount when it is a debit (blank otherwise) |
| Credit | The line amount when it is a credit, as a positive number (blank otherwise) |
| TaxType | Tax rate on the line |
| TaxAmount | Tax on the line |
| Tracking1Category | First tracking category on the line |
| Tracking1Option | Its option |
| Tracking2Category | Second tracking category on the line |
| Tracking2Option | Its option |
| LineAmountTypes | NoTax, Exclusive or Inclusive |
| ShowOnCashBasisReports | Whether the journal shows on cash-basis reports |
| UpdatedDateUTC | When the journal was last changed in Xero (date and time, UTC) |
| ManualJournalID | Xero unique identifier |
| XeroLink | Click-through link that opens the journal directly in Xero |
All the columns above are ticked by default. The table also adds OrgID and OrgName (prefix) and Timestamp (suffix). The optional Amount column (unticked by default) gives the signed line amount, debit positive and credit negative, handy for summing the net movement per account in one column.
The XeroLink column is ticked on by default. Every line of a journal links to that journal, and a draft journal opens in Xero's edit view. See Open in Xero for how the links work.
Status Filters
| Status | Description |
|---|---|
| DRAFT | Not yet posted (default) |
| POSTED | Posted to the ledger (default) |
| VOIDED | Voided in Xero (optional, unticked by default) |
| DELETED | Deleted in Xero (optional, unticked by default) |
| ARCHIVED | Archived in Xero (optional, unticked by default) |
At least one status must be ticked. A table with no status is refused with "Select at least one status." on insert, in Manage and on refresh.
Debit, Credit and tax
- Debit and Credit are the line amounts as Xero stores them, each in its own column as a positive number. For a tax-exclusive journal they are before tax, and the tax is in TaxAmount (Xero posts it to the tax account).
- For a No Tax journal, a posted journal's Debit and Credit totals are equal.
- AccountName is the account's current name: if an account was renamed after the journal was posted, the new name shows.
Not included
- A one-row-per-journal summary
- Attachments
- Xero's general-ledger Journals (every transaction's postings)
- Manual Journals formulas (there are none)
Troubleshooting
| Issue | Solution |
|---|---|
| One organisation fails while others load | Xero users with the Standard role need reporting permissions to read manual journals. Ask the organisation’s admin to give your Xero user those permissions, or use an Adviser account. |
| "Select at least one status." | Tick at least one status (Draft and Posted are the defaults). |
| "The From date must be a real date on or before the To date." | Check the date range (or the date cells it reads). |
| A journal is missing | Check the Status filter (Voided, Deleted and Archived are unticked by default) and that the journal date falls inside the range. |
Tips
- Journals have no number in Xero's API, so use ManualJournalID (or the date and narration) to tell them apart
- A journal with many lines makes many rows. A very long date range on a busy organisation can exceed the sheet: the table is then refused with a message asking you to narrow the date range or the statuses, and nothing is written
- Refresh it like any other table, in Excel or Google Sheets: see Table Refresh