How to query your Xero data from Microsoft Excel

Xero is a sophisticated and comprehensive cloud-based accounting platform. For many, the built-in reports and analysis that Xero offers is enough for our day-to-day needs. However, if you need something a little more customized, or you need to aggregate your data over multiple Xero accounts, or you need to cross-reference your Xero data with other cloud services, then you’re going to have to build it yourself. And what better way than using the tool you’re already familiar with - Microsoft Excel.

In this article, I’ll take you through the steps to connect Excel to your Xero data, and get started customizing the reports you need.

First - grab your SyncHub Credentials

If you don’t already have a SyncHub account, you can grab a free trial here - go on, I’ll wait - it only takes a minute and you can cancel as soon as you’ve finished this tutorial if you like.

Ready? Now, a quick reminder - SyncHub works by staging your Xero data in a relational database. This gives us a huge advantage over other connectors which query the Xero API directly, but I won’t go in to them here (check this blog post if you need convincing). The point is, once you have connected your Xero account, querying from Excel is trivial as you are just using it’s native Database Connector.

So, once you’re connected, go to your SyncHub Dashboard and grab your new database credentials:

As you can see, there are four main parts to your connection - the server and database, and the username/password. You’ll need them all for the next parts.

Reading your Xero data from Microsoft Excel

We now just need to tell Excel where to find your data. Select a new SQL Server connection from the Data ribbon:

When prompted, enter the Server and Database that SyncHub provided in the first step above:

Next - and this is the part that trips most people up - switch to the Database credentials tab (highlighted by the arrow below), then enter the username and password provided to you by SyncHub in the step above:

Don’t worry about which level to apply the settings to - database or server - it does not matter to SyncHub.

Troubleshooting. This is the point where you are most likely to run into issues - your login won’t be accepted. After triple-checking that you have used the correct server/database/username/password, the most common resolutions we see are:

1. try opening Excel in Administrator mode

2. if you are in an office, perhaps you have some firewall restrictions?

All going well, you’ll now be presented with the tables from Xero:

Simply select the data that you’d like to report on. Excel keeps things really simple by adding each “table” from SyncHub as a new tab. Here, we’ve selected the Payments table:

Bonus: It isn’t usually efficient to download entire tables to Excel, when it is likely that you don’t need all the data. For example, you may wish to exclude deleted records from your result set, or merge data from multiple tables. To facilitate this, we recommend our Insights module.

Creating Xero reports in Excel

Let’s proceed for a few more minutes and I’ll show you how easy it is to actually build customized reports from your Xero data. Here, we use Excel’s fantastic Quick Analysis feature to select a range of data then graphically represent our payments over the last few months:

As you can see, Excel does all the heavy lifting for you - all you need is the right data in the first place. With SyncHub, you can pull down information from different tables and join according to our data model. For example, you might update the report above to show Payments by Customer, just by using lookups between the Payments table and the Invoice table, and then to the Customer table. With the raw data provided by SyncHub, your options are almost unlimited.

Keeping your data up-to-date

SyncHub updates it’s staged data from Xero in near-realtime, but unfortunately Excel does not have a similar feature. But not to worry - the solution isn’t too onerous. Every time you want to refresh your data, simply click the (appropriately named) Refresh button in your Ribbon:

Beyond Xero

Xero-specific reports are essential, but the true power of SyncHub comes when you augment your Xero data with additional information:

• SyncHub allows you to pull in data from multiple Xero accounts, and compare/report/aggregate from within the same Excel report

• most businesses use multiple cloud platforms. SyncHub provides connectors to a wide range of popular cloud platforms - Deputy, Tanda, Unleashed, Pipedrive, Teamwork, simPRO, Wrike….and many many more. Imagine the insights you could gather by consolidating this information into a single dashboard (or see this case study for real-world examples).

So what are you waiting for? Grab a free trial of SyncHub here and see what you can do. In ten minutes from now, you could be reporting against your Xero data and taking your first steps towards a data-driven business.