Xero.com data and reporting

Once upon a time, I was using Xero to record and manage a company’s finances and wanted to use Zebra Bi for Power BI to create interactive income statement actual vs plan vs budget reporting.

Xero Features

Zebra BI offers very compelling visualization tools to create financial reporting in Power BI and Excel specifically for comparing actuals vs plan and budget. Zebra BI’s product is a Power BI and Excel plugin. Using it in Power BI is as simple as choosing Zebra BI as a visualization in same way as any other Power BI chart object.

Zebra BI Example Visualizations

Xero API

The primary challenge to using Power BI with Xero is deciding the best way to get data out of Xero so that it can be used by Power BI.

Xero has an API that provides access to almost all of the Xero data. The API can be used directly or indirectly via many third parties. However, the API features and limitations require careful  consideration to determine if they meet your use case.

The Xero API has some good documentation as well as an interactive API explorer that can be used in a no-code way to learn about the API endpoint features and data returned.

Two methods of using the Xero API:

    • Directly from Xero – The API can be used directly by registering for API access and developing your own code and process.
    • Indirectly via third parties – There are also a variety of third parties include a range of SaaS platforms and tools that connect to the API and retrieve Xero data on your behalf. These provide a wide range of methods and Xero data “modelling” and transformation to enable it to be readily used for financial reporting.

I ended up conducting a selection process to identify the best solution to get Xero data which considered a variety of Xero API and third party limitations.

Requirements Summary

    • Multi-year Xero data retrieval and analysis eg 2019, 2020, 2021.
    • Use Power BI to enable Zebra BI reporting.
    • Data must include actuals, budget and multiple forecasts, and ability to create previous year metrics.
    • Method to allow ad-hoc “account mapping” eg ability to categorize accounts other than Xero tracking categories eg group all travel as “Travel”.
    • Entirely automated to minimize manual effort required. This includes retrieval and transformation of Xero data and Power BI report refresh.

Xero API Limitations

    • API 11 month per call limit – need workarounds to get more.
    • Scheduling – need method to schedule data refresh.
    • Update existing data – either delete all or update existing data.
    • Data modelling – Xero Report API’s provide Xero modelling however additional modelling required to get desired datasets.
    • Budget data – need a method to incorporate Xero budget data. Xero is only recently providing budget data via API.
    • Forecast data – need a method to incorporate forecast data. Xero does not have forecast data functionality. However, a workaround is to create a budget that has forecast data.
    • Account grouping – how to categorize accounts other than Xero tracking categories eg group all travel as “Travel”.
    • Implementation & support complexity – code, technical mix, processes, components, environments.
    • All methods require extraction of Xero data into some intermediary storage location eg a datawarehouse or datamart.


The graphic below summarizes the selection investigation results and comparision and highlights my top two choices that best satisfied my requirements.

Note this comparison doesn’t include costs. However, generally speaking, these solutions have more or less similar costs.

My requirements were best met using Acterys which provides Xero API data ETL,  an advanced datawarehouse, and scheduling and user administration capabilities.  Of the remaining solutions FreshBI  was ranked second.

Note that there are a lot of other potential solutions including the many Xero application partners  or the many cloud data integration tools but these were the ones that I found to be most useful.

Solution Details

FreshBI Power BI Custom Connector for Xero Accounting was initially selected primarily because it was quick and easy way to get Xero API data.

    • The FreshBI team has developed their own custom Power BI Xero connector that they sell for a fixed price. Once you have the connector you are free to modify it as required which is what I did.
    • The FreshBI connector connects to API and returns data to Power BI as Power BI queries. The connector can be accessed and the query code modified as desired to get data into desired shape or to modify it to retrieve multiple years of data.
    • However, code required non-trivial modification to get desired data. Original connector retrieves only 1 year of pivoted format data. I modified the query code to unpivot the data months from columns to rows. Stopped before making further modifications to retrieve > 1 year data. (Interesting note: based on my discussions with FreshBI they said they may modify connector to provide this capability which they will make available as update to purchased connector).
    • Connector cannot refresh from Xero from PBI Online. Only Microsoft ‘certified’ connectors have this capability and FreshBI said Microsoft will certify their connector only if it has many more users. Hence, a Power BI pbix file published to PBI Online can only refresh data by using the PBI Gateway which is free app that runs connector on laptop or on server and syncs Xero data and PBI Online dataset.
    • Support complexity deemed high due to modifications, multiple components and environments.

Acterys ultimately selected because it provides best supported, most robust Xero data solution.

    • Acterys has developed a sophisticated modern BI data modelling and process which meets all data requirements eg actuals, budget, forecast, relational star schema, process to import budget and forecast, modify existing datasets.
    • Includes Microsoft Azure SQL Server database for Xero data storage as reporting data source.
    • Xero data refresh process includes scheduling, status email notifications, and previously retrieved update process. Changes made to data within past 7 days updated in Acterys database.
    • Support complexity deemed medium, though Acterys solution is quite complex, it built and supported by Acterys, for all of their customers, which reduces end user support complexity.

ODataLink and CData were not selected as they are essentially alternate methods of FreshBI connector API data acquisition, and do not offer additional data modelling other than Xero Report API. Interesting note: As a result of my discussions with them, OdataLink added new functionality to retrieve > 1 year data.

Synchub not selected because while it provides excellent data syncing capabilities it does not have any data modelling simply providing raw Xero database table data.

Xero PBI App is a Power BI application that is available in the Microsoft Power BI app store. It is considered only as a Xero “showcase”. While the reports can be modified, or new reports created using the dataset, the data has only rolling 1 year data, and it is not possible to have access to the report pbix file to inspect or modify the dataset, hence it is limited to the purpose of the “showcase” reporting.

Hopefully this write-up was helpful when deciding how to get data out of Xero for analytical and reporting purposes. It seemed to me while researching this topic that there is still a lot of opportunity to provide Xero or other ERP / financial application data integration and reporting capability.

Canadian Government First Nations long term water advisory data

The Government of Canada is working with Canadian First Nations communities to end long-term drinking water advisories which have been in effect for more than 12 months since November 2015. This includes projects to build or renew drinking water infrastructure in these communities.

The First Nation drinking water advisories were a big part of the 2021 Canadian federal election campaigns. The current government had made big promises to fix all of the advisories in 2015. The opposition criticized them for not having achieved that goal to-date. However there was poor communication about the actual number and status of the advisory projects. So I wanted to find some data to learn the actual status for myself.

TLDR the results can be seen on a Github.io hosted page.  A screenshot is provided below. The code to retrieve and transform the data to create that web page are in my Github https://github.com/sitrucp/first_nations_water repository.

Finding the data I needed only took a  few minutes of Google search from an Indigenous Services Canada  website that mapped the water advisories by counts of advisories by FN community, project status, advisory dates, and project type.

While the web page also included a link to download the map data, after a bit of web page scraping and inspection, I learned that the downloadable map data was created by web page Javascript from another larger data source referenced in the map page code https://www.sac-isc.gc.ca//DAM/DAM-ISC-SAC/DAM-WTR/STAGING/texte-text/lTDWA_map_data_1572010201618_eng.txt.

This larger data source is a text file containing JSON format records of all Canadian First Nations communities and is many thousands of records. As a side note this larger file appears to be an official Canadian government dataset used in other government websites. The map data is limited to only about 160 First Nations communities with drinking water issues.

So rather than download the 160 record map page data, I retrieved the larger JSON format text file and used similar logic as that for the map web page code to get the 160 records. This was done in two steps.

Step 1: retrieve JSON format text file using a Python script water_map_data.py to retrieve and save the data file locally. I may yet automate this using a scheduled task so the map gets regularly updated data as advisory status changes over time.

Step 2: process the saved data file and present it in an HTML web page as Plotly.js charts and an HTML tabular format using Javascript in this file  first_nations_water.js

Finally, I also separately created a Excel file with Pivot Table and Chart that you can download and use to do your own analysis. Download this Excel file from the Github repository. The file contains an Excel Power Query link to the larger text JSON file described above. You can simply refresh the query to get the latest data from the Indigenous Services Canada  website.

Business Intelligence – IT Black Box Challenge

The process of engaging IT teams can be difficult to understand for many non-technical people. IT is perceived as a ‘black box’ because its inner workings are often not-transparent.


BI teams may not realize that their business side coworkers do not know how to engage them.


When IT workers reach out to engage business users they may often confuse their potential customer by using too much jargon.


Technical teams often also make too many assumptions of non-technical technical understanding about IT terms and descriptions.


Therefore, it is important for any IT worker to start any engagement with non-technical co-workers at the most basic level of communication.

Do not assume anything. Do not use jargon. Explain your technical job title and role. Describe the process of engagement, what you will do, and what will be expected of the non-technical co-worker. Shine some light on the inside of that IT black box and make it transparent!