HOW TO DYNAMICALLY HANDLE XERO TRACKING CATEGORIES AND TRACKING OPTIONS FROM MICROSOFT EXCEL

February 2, 2023

Basic overview of Xero Tracking Codes

Xero Accounting Software allows users to allocate any number of Tracking Codes to transactions within your accounting records. This allows for additional reporting capabilities without the need to endlessly expand the number of account codes within your Chart of Accounts. This could be used for reporting on different business units, regions, customer demographics and more.

The concept works where a user can create a Tracking Category (such as Region), and within this category they will then create Tracking Options (such as North, South, East, West).

After setting it up the bookkeeper can assign these Tracking Options to transactions as they post them to Xero within their usual bookkeeping cycle.

How these Tracking Options feed through to Excel

Whilst the user interface of Xero will allow accountants to create an unlimited amount of Tracking Categories and Tracking Options, the Xero API (the interface that Accounting.BI uses to transfer the data to Excel) only allows the filtering of 2 Tracking Categories.

This means after you have established your connection to Xero via Accounting.BI the data will sync with the Tracking Options for the first and second Tracking Categories you select within the user interface of Accounting.BI. These will be displayed in columns I & J.

It important when building your reports that you understand the structure of the data that is loaded to excel with your Tracking Options. The amount assigned to the 'Total' value is the sum of all the transactions for the given account code shown in Column G, this should directly reconcile back to the amount shown in the user interface of Xero when you run a Profit & Loss report for the relevant period without applying a filter for any tracking codes. The other values in Column I & J represent the value of transactions with the specific tracking options applied.

Note - the example below show a dual filter, where for example the 5636.17 represent the sum of the transactions for the period where both Test3 & Overseas Tracking Options have been applied. It is important to note that if you do not use this dual filter and only want to report a single list of options for a given Tracking Category, you may have a different value.

To connect your Xero reports to excel sign up (https://app.accounting.bi/sign-up) to Accounting.BI

For further enquiries, reach out to us.

learn more

Why is PowerBI important for Accountants?

As an accountant, you may find yourself referring to multiple sources to track the performance of your client’s cash flow, profitability, business trends and even bookkeeping cycle. What if you could access those results and comparisons automatically refreshed, on-demand, from your tablet, smartphone, PC, or even wearable device? Business intelligence and data visualisation systems can provide accountants with – the critical data and organisation summary in a custom format in one (or more) visuals or a dashboard. One of these powerful business intelligence tools is PowerBI.
READ MORE

Power BI: What is DAX?

DAX (Data Analysis Expressions) is a formula expression language developed by Microsoft to interact with data in different analysis services and visualisation tools platforms like Power BI, PowerPivot and SSAS tabular models. DAX can seem familiar to competent excel users as the language uses similar formulas and expressions.
READ MORE

How to connect your Xero account(s) to AccountingBi

This guide will walk you through two methods for connecting your Xero accounting software to AccountingBi
READ MORE

How to call your profit and loss account reports from Xero to AccountingBi

We have outlined a step-by-step process of calling your profit and loss report data from your Xero account to AccountingBi - article and video.
READ MORE

How to call the balance sheet reports from Xero to AccountingBi

We have outlined the steps of calling balance sheet reports from Xero to AccountingBi in a video (watch below) and an article.
READ MORE

How to connect Microsoft excel to Xero through AccountingBi

This video and article provide steps for connecting Xero to PowerBi through AccountingBi. It assumes that you've already pulled the Xero API from accounting BI for your various reports.
READ MORE
1 2 3 5
READ MORE
Accounting.bi - Business Intelligence Software
Copyright © 2023 Accounting.bi is a brand owned and operated by Accounting SQL Limited, incorporated in the United Kingdom. ​
Terms & Conditions - GDPR