How to automate your Xero Reporting with Excel

Giorgos Psistakis

How can I get my data from Xero? How can I use Excel with Xero? How can I have my data consistent and fresh or is It too techie for me?

You are not the only one with these questions. I hear them every time I talk with people that work with Xero. And there are many reasons why you would like to pull your data from Xero, for some of these reasons you can read How to Achieve Awesomeness with Xero Data & Excel.

Accounting is all about data

Technology today offers access to more data than ever but with a cost. There are data in many data points, but most of the times we need a lot of manual work to get it, or gets a difficult and sometimes a messy process. Other times it may require advanced technical expertise.
For example, accountants that use (and love) Xero want to go a step further. They are looking to create advanced reporting or reconciliations that cannot be achieved from Xero directly. People said to me “There’s no magic formula for what we do. We end up to multiple Excel downloads and manual capturing of data“.

There’s no magic formula for what we do. We end up to multiple Excel downloads and manual capturing of data.

How can I build a flexible custom solution for Financial reporting?

If you want to get a general overview of the ways you can do it, you can read How to Achieve Awesomeness with Xero Data & Excel. This “how to” post is about how to augment your accounting work with a little bit of IT power.

The idea is to easily work with the Xero data from Excel to build a custom solution for your financial reporting.

  • Xero will always contain your original and raw data. Always backed up and available from their great platform.
  • Excel will get your data from Xero. Easily updated and refreshed. You will not need to add plugins to make it work.

I am not an IT person, I can not do it.

No worries, we got your back!

There are only 3 main steps, plus a little of magic sauce. You may need some initial time to set this up but that will be necessary only for the first time. Then everything will be available to your Excel sheet. We will:

  1. Get (export) your data from Xero in minutes.
  2. Load it to an SQL database
  3. Connect Excel.

Bam! Your Xero data in one place, always available, up to date, automatically, accessible with Excel.

The magic sauce is Blendo. The missing piece that can connect all these parts together. You get the IT powers with minimal IT knowledge. Sounds like cheating right?

Building Financial Analytics Dashboards- an eBook for accountants and CFOs

Let’s start!

How to export my data from Xero?

There are three ways to do it. We will use a tool like Blendo that will help you get the data from Xero (or any other source) in no time and easily send it to the destination you like. You can read a more high-level description in our latest post How to Achieve Awesomeness with Xero Data & Excel. Blendo will help you step up the game and have easy access to all the Xero data and always up to date.

Let’s get the data from Xero then. For this “how to” I will use the Demo Company (Global) that is found inside Xero. Note: In order for anyone to access is data from Xero, it will require to create an account in Xero Developer Centre.

Import your Xero data into your data warehouse - Blendo.coSync your accounting data from Xero to any data warehouse. Analytics-ready data do consolidate your accounting with no hassle.Integrate Now

Considering that you already have created your account in Xero Developer Centre, here are the steps to follow on:

1> Open a new Tab in your browser. Go to Blendo Home page. Click Add a Source.

Xero Integration

2> By pressing Add, you will be prompted with a new window containing your X509 Certificate and Consumer Key box. Both are needed to connect to Xero. You will need to copy the Certificate and use it in a later step.

Xero Integration

3> Open a new tab in your browser. Log in with your account at Xero Developer Centre.

4> Click Add Application

Xero Developer Centre

5> Select Private.

6> Provide an Application Name and select your Organization (that will be the Demo Company) from the drop-down menu.

Xero Developer Centre - Add Application

7> Go to your browser tab with Blendo. You will need to copy the Certificate from Blendo. Select the whole text and copy it.

8> Go back to the browser tab with Xero and paste it in the X509 Public Key Certificate box of Xero Application.

Xero Integration setup

9> Read the Terms and Conditions and if you agree, check it.

10> Click Save.

11> In the new window you will see your OAuth Credentials. We will need the Consumer Key. Click Show and copy the value.Xero Integration setup

12> Go back to Blendo tab and paste the key into the Consumer Keybox.

Xero Integration setup

13> Click Connect.

14> Click Import. Your data will start flowing to Blendo.

15> After some seconds the import should have finished.

How to Load Xero data to Azure MS SQL Database

In this example, we consider you already have an SQL database ready and configured. If not, here is a post to help you setup an SQL database in Azure.

1. Create an account and go to your Blendo Dashboard.

2. If it is the first time you login to Blendo you will see the screen bellow. Click on the box with SQL Server.

On the next screen, click on the box with SQL Server.

3. Add your credentials to connect to Microsoft SQL Server.

  • SQL Server Host: For our example, this will be the gandalfxero.database.windows.net
  • SQL Server Port: Leave the default
  • SQL Server Database: For our example, this will be Myxerodb
  • SSL: In our example, we do not use SSL, so leave it blank. NOTE: It is advisable to have SSL!
  • Username: For our example it is testusr
  • Password: the password
  • *Export table prefix: It is not mandatory, I used myxero_ for me to know which tables will be created inside the SQL database.

If You have successfully connected all your data will automatically go from Any source to Azure MS SQL and that will happen for every new update that happens to that source. Blendo will get your data in certain intervals and send it to your SQL database.

If you also need a full guide take a look at our MS SQL Server documentation:

  1. Step 1 (Azure): Connect Windows Azure Microsoft SQL Server
  2. Step 2 (Azure): Setup Windows Azure MS SQL Server
  3. Step 3 (Azure): PermissioningBlendo to Windows Azure MS SQL Server
  4. Step 4 (Azure): How to connect Windows Azure MS SQL Database as Data Warehouse Integration

How to connect Excel to Azure MS SQL database?

It is simpler than it sounds. In this “how to” we use Microsoft Excel 2013. In order to connect to Azure MS SQL Server we will need to add a new firewall rule for our PC to connect. So:

1> Go back to your browser tab with the Azure Portal.

2> Click on All Resources on the left sidebar. In the new window start typing the name of the server we created before ie “gandalfxero”. From the filtered results click the server named “gandalfxero”.

Azure Portal

3> Click on Firewall on the window on your right side.

Azure SQL Server Firewall Rule

4> Click Add Client IP. Then a rule will be created for our IP address.

Azure SQL Server Firewall Rule

5> Click Save to create the rule. NOTE: Your Client IP address may change from time to time, and you may not be able to access your server until you create a new firewall rule.

6> Open Excel and then create a new workbook.

7> In the menu bar at the top of the page click Data, click From Other Sources, and then click From SQL Server.Get Xero data to Excel

8> In the new window:

  • type the SQL Database Server name you want to connect to (this will be the gandalfxero.database.windows.net)
  • Click Use the following User Name and Password.
  • Username will be testusr
  • Password the one you used.

Click Next.Get Xero data to Excel

9> In the next window, click on the drop down and select the Myxerodb database. Wait a few seconds and you will see a list of tables or views bellow. There you will find all our Xero tables with the myxero_ prefix.

10> Select (for example) the myxero_Accounts. Click Next.

Get Xero data to Excel

11> In the next popup you can leave the defaults. You may always add a description to remember what you’re connecting to or find the connection.

12 Click Always attempt to use this file to refresh data if you want connection information stored in the .odc file so it can update when you connect to it. Click Finish.Get Xero data to Excel

13> In the Import Data dialog just press OK.

Get Xero data to Excel14> You may need to add your Password again. If asked, type it and click OK.

15> Magic!Get Xero data to Excel

 

Each time you need to get your new and updated data from Xero you will only need to click on Refresh.
Get Xero data to Excel

No need for manual updates. You can now focus on your reporting and consolidations.

Conclusion

Creating a custom Accounting Reporting for Xero doesn’t sound so difficult now I hope. Getting a great application like Xero and combining it with powerful Excel without having to deal with the manual work is something that can be done. Blendo will give you all the automation you need so you will not need to loose any more time in “multiple Excel downloads and manual capturing of data”. We got you covered!

For any help of setting it up or any other inquiry contact us at hello@blendo.co

Help your accounting and executive team take ownership of the data that live inside Xero to better consolidate your accounting data.

Blendo is the easiest way to automate powerful data integrations.

Try Blendo free for 14 days. No credit card required.