Bonus Material: FREE Xero KPIs Cheat Sheet (along with the SQL code you need) PDF
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 time, we need a lot of manual work to get it or gets a complicated and sometimes 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 efficiently 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 excellent platform.
- Excel will get your data from Xero. It is easily updated and refreshed. You will not need to add plugins to make it work.
Click here to get our FREE 10+ page PDF Xero KPIs Cheat Sheet!
I am not an IT person; I can not do it.
No worries, we got your back!
There are only three main steps, plus a little 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:
- Get (export) your data from Xero in minutes.
- Load it to an SQL database
- 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?
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: For anyone to access its data from Xero, it will require to create an account in Xero Developer Centre.
 |
 |
Sync 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.
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.
3> Open a new tab in your browser. Log in with your account at Xero Developer Centre.
4> Click Add Application
5> Select Private.
6> Provide an Application Name and select your Organization (that will be the Demo Company) from the drop-down menu.
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.
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.
12> Go back to the Blendo tab and paste the key into the Consumer Keybox.
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 set up an SQL database in Azure.
1. Create an account and go to your Blendo Dashboard.
2. If it is the first time you log in to Blendo, you will see the screen below. 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 case, 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 at specified intervals and send it to your SQL database.
If you also need a full guide, take a look at our MS SQL Server documentation:
- Step 1 (Azure): Connect Windows Azure Microsoft SQL Server
- Step 2 (Azure): Setup Windows Azure MS SQL Server
- Step 3 (Azure): PermissioningBlendo to Windows Azure MS SQL Server
- 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 more straightforward than it sounds. In this “how-to,” we use Microsoft Excel 2013. 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”.
3> Click on Firewall on the window on your right side.
4> Click Add Client IP. Then a rule will be created for our IP address.
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.
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.
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.
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.
13> In the Import Data dialog, press OK.
14> You may need to add your Password again. If asked, type it and click OK.
15> Magic!
Each time you need to get your new and updated data from Xero, you will only need to click on Refresh.
No need for manual updates. You can now focus on your reporting and consolidations.
Click here to get our FREE 10+ page PDF Xero KPIs Cheat Sheet!
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 manual work is something that can be done. Blendo will give you all the automation you need so you will not need to lose 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
