January 14, 2021

How to Get Xero Data into Excel

Xero and Excel are vital tools for many businesses, but it's not obvious how to link them together.

By
Mack Grenfell

Many businesses today rely on both Excel and Xero. They use Excel for their planning, reporting, and measuring, and Xero to manage their company's finances.

Given how popular both of these two tools are, natural to want to integrate them, and to be able to import your Xero data into Excel.

Before we dive into the specifics of how to get your Xero data into Excel, let's briefly look at why Excel is so commonly used in tandem with platforms like Xero.

Using Excel for accounting

There are a number of reasons why Excel can be useful for manipulating financial data from an accounting platform like Xero:

The reasons above explain in large part why Excel is so commonly used alongside accounting services like Xero.

The downsides of Excel

While Excel is great for manipulating, reformatting, and visualising data, it's less great at getting that data in the first place.

Even most Excel power-users have limited experience of integrating Excel with live data sources.

Having access to live data is critical when it comes to accounting. Because accounting data can change so frequently, and unpredictably, it's vital that any tools you use have a real-time connection to your underlying Xero data.

How do I get Xero data into Excel?

If you're looking to get your Xero data into Excel, you have a couple of options to choose from.

Manual Xero exports

Getting a static report from Xero into Excel is fairly easy. All you need to do is:

  1. Log into Xero and in the Accounting menu, select Reports.
  2. Select the Custom, Drafts, Published or Archived tab, and choose the report you'd like to export.
  3. Click the three dots icon on the report that you'd like to export, and choose Export to Excel.
  4. Save the file wherever you like, and proceed to open it in Excel.

If all you need is a quick one-off export from Xero to Excel, then the above will work perfectly.

In many cases though, you wont want to have to go through this process of exporting the data and re-doing your work in Excel each time your Xero data updates. If this is something you want to avoid, then you'll have to use the next method.

Connecting Excel to Xero

If you need a live connection between Excel and Xero, then there are two options you can take.

ODBC

One way to get live Xero data into Excel is to use something called Open Database Connectivity (ODBC). ODBC is a piece of technology that allows apps like Excel to communicate with external databases, such as the one that holds all your Xero data.

To use ODBC to get your Xero data into Excel, you should:

  1. Purchase a driver that will allow you to request the data from Xero via ODBC. The most popular driver can be found here.
  2. Once you've purchased your driver, install it on your computer.
  3. Open Excel and, in the Data tab, click Get Data → From Other Sources → From ODBC and select your driver (CData Xero Source if you bought from CData).
  4. Choose one (or more) reports that you want to pull from Xero, and hit Load → Load To and then your preferred report type.

Once you've done all this, your data will start flowing into Excel.

3rd party apps

There are also a number of 3rd party apps that you can use to connect Xero to Excel. The most prominent of these is DataDear, which allows you to export manual or updating reports to Excel.

These 3rd party apps do of course come at a cost. DataDear, for instance, starts at $420 a year for their most basic package and their standard plan comes in at $1,200 a year.

There has to be an easier way

If the methods above look needlessly painful, I can sympathise.

Each method is other prohibitively expensive for what should be such an easy task, or requires an advanced level of Excel knowledge.

Fortunately though, there is an easier way to be able to model and manipulate live Xero data.

Using Causal instead of Excel

Causal is a browser-based financial modelling tool which lets you build interactive forecasts and dashboards in minutes.

It goes beyond the features of traditional spreadsheet-based tools like Excel, and lets you create fully customisable models that you can share easily with your team.

You can build models on top of your Xero data, so you have live financial models that update in real-time.

How does Causal work?

Whereas Excel is built around rows and columns, Causal models are built out of variables.

Instead of writing formulae full of arbitrary cell references, Causal lets you write natural language formulae that show how your variables are related.

Once you've constructed your variables, you can build charts and forecasts with the click of a button.

All of your financial model's inputs are made visible to viewers, meaning that they can tweak your assumptions to see how the model changes in real-time.

Integrating Causal with Xero

Causal comes with a range of accounting integrations, including one that allows you to import your Xero data in seconds.

To import your Xero data, all you have to do is:

  1. Connect to your Xero account.
  2. Choose the dataset that you'd like to import.

You'll then see a preview of all the data that's been imported, and be able to reference the data in your model's variables.

Try Causal for free

Want to test out our integration with Xero? Just click here to sign up for a free account, where you'll be able to quickly get started building models off of your Xero data.

While you're here...
If you enjoyed this post, then you might also like our product — Causal. It's a new way to build models, visualise data, and communicate with numbers. Click here to learn more.