Bank Transactions Table
BetaEvery bank movement from Xero in Excel, already reconciled to your Bank Summary. One table shows all the money in and out of each bank account, pulled straight from Xero.
What it brings together
Xero keeps bank activity in separate places. This table puts it all in one list, per bank account:
- Spend money and receive money
- Customer and supplier payments, and refunds
- Prepayments and overpayments
- Transfers between your own bank accounts
Reconciled and unreconciled transactions are both included. The IsReconciled column shows which is which.
Checked against your Bank Summary
After every insert and refresh, XO Report compares each bank account's money in and money out with Xero's own Bank Summary report for the same period, to check that the totals agree.
If something does not add up, the table says so in the Flag column with a short message. It never hides a difference or guesses a figure. A difference of one cent (or the smallest unit of the currency) is treated as rounding.
Foreign-currency bank accounts
Each account is shown in its own currency, like the bank statement. It is still checked against Bank Summary, which Xero shows in your base currency. When a movement cannot be converted exactly, for example a transfer between two different foreign currencies, the account is flagged instead of guessed.
Parameters
Organizations
Pick one or several Xero organizations. They are combined into one table, with OrgID and OrgName columns to tell them apart.
Date Range (Required, up to 12 months)
Use Smart presets, Cell references, or Fixed dates. The 12-month limit also applies when you refresh.
Rows: Summary or Detail
Summary gives one line per transaction. Detail gives one line per line item, with account codes. The Detail lines always add up exactly to the Summary.
Output Columns
| Column | Description |
|---|---|
| Date | Transaction date |
| BankAccount | Bank account name |
| BankAccountCode | Bank account code |
| Type | Xero type code, for example RECEIVE (money in), SPEND (money out), ACCRECPAYMENT (invoice paid), ACCPAYPAYMENT (bill paid), RECEIVE-TRANSFER or SPEND-TRANSFER, or CONTROL on a check line |
| Source | Where in Xero the row came from: BankTransaction, Payment or BankTransfer, or BankSummary on a check line |
| Contact | Contact name (blank for transfers) |
| Description | Line description, related document number, or the other account of a transfer |
| Reference | Reference |
| Received | Money in, in the bank account’s own currency |
| Spent | Money out, in the bank account’s own currency |
| CurrencyCode | Currency of the amount shown |
| CurrencyRate | Exchange rate recorded on the transaction (blank when Xero gives none) |
| IsReconciled | Whether the transaction is reconciled in Xero |
| Status | AUTHORISED, or CONTROL on a check line |
| Flag | Blank when everything adds up. Otherwise a short message saying what does not |
| SourceID | Xero identifier of the underlying record |
| XeroLink | Link that opens the transaction in Xero (blank on check lines and on some transfer legs) |
Plus OrgID, OrgName, and Timestamp columns.
Keep the Flag column ticked: it is how the table tells you a total is incomplete. When Bank Summary shows money that Xero did not return as a transaction, the table adds a marked control line for it rather than leaving the gap unexplained.
Reconciling one account at a time
Filter the BankAccount column to one account and compare its totals with Bank Summary for the same period. Keep the Flag column visible so any gap shows in the data.
Limits to know about
- It is in Beta
- The table is new. If a total does not agree with Xero, the Flag column tells you, and we want to hear about it.
- Classic expense-claim payments are not included
- XO Report’s connection to Xero cannot read them.
- Some foreign-currency transfers are checked as a group
- When two or more accounts in the same foreign currency move money between themselves, they are checked together on their combined total rather than one by one.
- Up to 12 months per table
- Pick any period up to 12 months. For a longer span, insert one table per year.
- A short check report while in Beta
- When a table ends up with flags, the add-in sends XO Report a short report so we can find the cause. It includes the organization ID, account codes, currencies, the size of any gap, and transaction types and dates. It never includes account names, contacts, descriptions or references.
Deleted and voided transactions are never included. Bank feed lines you have not yet turned into transactions in Xero are not in this table, and not in Bank Summary either.