.png)
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:
- Flexibility
Once you have all of your Xero data in Excel, you can do whatever you like with it. You can create whatever reports, models, or analyses you need, and aren't constrained by what features are on the Xero platform. - Familiarity
If you're reading this, then chances are that you're familiar with Excel, and won't have any trouble using it to manipulate your accounting data. - Support
Given the popularity of Excel worldwide, it's typically easy for find answers to common issues in Excel. - Cost
With many businesses already having access to Excel licenses, using it for your accounting shouldn't come at any additional cost to you.
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:
- Log into Xero and in the Accounting menu, select Reports.
- Select the Custom, Drafts, Published or Archived tab, and choose the report you'd like to export.
- Click the three dots icon on the report that you'd like to export, and choose Export to Excel.
- 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:
- Purchase a driver that will allow you to request the data from Xero via ODBC. The most popular driver can be found here.
- Once you've purchased your driver, install it on your computer.
- 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).
- 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:
- Connect to your Xero account.
- 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.
How does Causal get data from Xero?
To pull in your Xero data, Causal uses a service called Railz.
Railz lets you access real-time accounting data from all the most popular accounting platforms, including Xero. Its API-as-a-service model enables on-demand access to financial transactions, analytics, insights and reports from your customers.
If you're managing Xero data from multiple accounts or customers, using Railz could significantly reduce your development time and costs.
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.
.svg)
.svg)