Excel Transactions Reports for Xero

Home / Report Templates / Excel Transactions Reports for Xero

The Excel Transactions Reports for Xero is a simple report that allows you to review and analyse your General Ledger (GL) data posted to Xero. For a Power BI version of this report, see the Transaction Reports for Xero in Power BI.

This report can also consolidate data from multiple Xero organisations (data files) when using the Consolidation OData Feed.

It uses the JournalsAccrualTable endpoint to download all debit/credit amounts posted to your GL It also includes additional fields from the transactions and contacts endpoints.

  • Excel Transaction Reports for Xero
    Excel Transaction Reports for Xero

While this is a fully working and standalone dashboard, it can also be used as a starting point that can be further customised or modified as required to fit whatever business rules you need. It is not locked in any way. Because of this, it can also be used to learn how to create power queries, how to create DAX formulas, etc.

Reports Included

Transactions Report

This report mimics the Account Transactions Report or General Ledger Detail Report within Xero and list all transaction amounts posted to your General Ledger (GL).

It is provided as a pivot table allowing you to easily drag, drop and/or remove columns as needed.

Requirements

To use the report, you will need to:

  1. Have an OdataLink Subscription or Trial Account.
  2. Connect OdataLink to your MYOB company file.
  3. Configure a Model that includes the following endpoints:

Download and Setup

We provide 2 versions of this dashboard. Once downloaded, you can connect it to your own data using this article.

Transactions Report for Xero

This version of the report uses and includes all journals posted to Xero to create the data set.

Download the Transactions Report for Xero

Transactions Report for Xero (Filtered)

This version of the report filters journals posted to Xero and keeps only the final journal for a transaction. Journals are an audit trail that includes multiple records each time a transaction is edited or deleted. You can learn more about the algorithm we devised to filter out these records.

Download the Transactions Report for Xero (Filtered)

How to refresh the reports

To refresh the reports, follow these steps.

  1. Go to the Inputs sheet to enter the Parameters driving this report.
  2. Enter the Date Start and Date End for the months you wish to report on.
    This can be any date range but the larger it is, the longer the refresh will take to run.
  3. (optional) If using tracking categories, enter the names of the tracking categories list to use for Tracking Category 1 and Tracking Category 2.
    This is important as it allows you to control which appears first and which appears second.
  4. Go to the Data tab in the ribbon, click the Refresh All button and wait for the data to download.
  5. Select a Worksheet and use the Filters to display the data you want.

Understanding the Power Queries used to produce the numbers

The Transactions Report for Xero (Filtered) uses the JournalsAccrualTable endpoint to download all debit/credit amounts posted to your GL. It then performs complex manipulations to this table to exclude edited transactions and reversal entries.

The reason for these complex manipulations stems from the way the journals endpoint operate. Xero designed the journals endpoint to provide an audit trail of all changes posted to your GL. When a transaction is edited, Xero post reversal entries of the original transaction and re-entries of the current state of the transaction. Similar concepts applied to deleted or voided transactions.

While this mechanism is useful for auditing purposes, it does create confusion when looking at it from the transactions point of view.

The Power Queries in this version of the report performs complex operation to remove these duplicates. This ensures that a single set of numbers is displayed for a given transaction as opposed to a complete audit trail of all debits/credits ever made.

Find an issue with the template?

Let us know of any issues you find with the template. We built it to use the least amount of data possible but this might not suit everyone.

Change Logs

2025-05-05:

  • Enhanced to include CurrencyCode and CurrencyRate in the Transactions table data set which requires the Organisations as an additional endpoint.

2025-04-24:

  • Resolved issue with credit notes in foreign currencies where the algorithm would fail to identify the final journal correctly.
  • Enhanced Power Queries to make more use of Power Pivot.

2024-10-10:

  • Relationships were broken on the template.
  • There was a performance issue where the buffering was done too early and causing all queries to run.
  • Credit notes were being unfiltered correctly due to rounding and very small number which should have rounded to 0. Applied rounding to resolve this issue.