How to connect Microsoft Excel 2016 to Xero through AccountingBi 

October 18, 2022

Preparation 

Ensure you do the following before you start the process:  

  • Connect your Xero account to AccountingBi.   
  • Query Balance sheet Reports and Profit and Loss reports from Xero via AccountingBi.  

This process applies to Microsoft Excel 2016, if you use another version of Excel, click here instead. 

Steps to connecting Microsoft Excel 2016 to Xero 

Please follow these steps: 

  •  Start Microsoft Excel 2016 to generate a blank workbook. 

Connect Xero data to Microsoft excel

  • Click the Data tab. 

Connect Xero data to Microsoft Excel

  • Select New Query from the drop-down menu.  

Extract Xero data into Microsoft excel

  • Choose From Other Sources. 

Extract xero data into Microsoft excel

  • Select From Web in the succeeding drop-down menu. 

Extract Xero data into Microsoft Excel when using the AccountingBi

  • From the popup window that appears, check the Advanced button. When you select Advanced radio button, a dialog box will appear as below. 

Select the Advanced radio button

Microsoft power query radio button

 

AccountingBi My bearer token tab

  • Copy the relevant API URL depending on what you want - Profit and Loss and Balance Sheet or Chart Of Accounts API URLs. 

AccountingBi API URLs page

  • Go back to your Microsoft Excel worksheet and paste your URL into the URL parts field. 

Microsoft Excel/Query URL parts

  • Head back to your AccountingBi Bearer token page. Copy the Bearer token.

AccountingBi My Bearer Token 

  • Paste your token on the box on the right under the HTTP Request Header Parameters section in your Excel worksheet. 

Bearer Token URL

  • Navigate to the front part of the pasted URL and type the word Bearer (the letter B must be capitalised). You should then insert a space between the word and the token URL. 

Power Query - Bearer Token

  • Type the word Authorization (must be the US spelling) in the box on the left under the HTTP Request Header Parameters section. 

Authorization - Bearer Token - Power Query

  • Click Ok so that Microsoft Excel can request the AccountingBi API to start loading the data. 

Click OK

  • An Access Web Content dialogue will appear on this page. Proceed with the Anonymous tab because you may get stuck at this stage if you put in the wrong login details. Click Connect. 

"Access Web Content" dialog

  • You will see all the reports you have already called from Xero through AccountingBi on this screen. 

Call data from Xero to Excel

  • Select the Into Table tab to convert your data to a table. 

  • Click the double-headed arrow in the reports window as shown below. 

Xero data in excel

  • Choose Expand to new rows from the dropdown menu.

Connect Xero data to excel using accountingbi api 

  • Click the double-headed arrow (as shown below) to reveal your data columns. 

Connect Xero to Microsoft Excel

 

Query Xero data into Excel

  • Click Ok to load your data. 

Query Xero data into Microsoft Excel using AccountingBi

  • Click the Close & Load pull-down menu and choose Close & load. 

Excel data in Microsoft Excel

After the process, your data will be loaded into a new sheet in your Excel workbook. Whenever you have new data in Xero, you only need to refresh the reports tab on your workbook for the new information to reflect on your worksheet. 

You can refresh your data by using any of the following methods: 

  • Just click the Document icon on your report under Queries & Connections to refresh your data. 

Refreshb Xero data in Microsoft Excel

  • Right-click the report tab under Queries & Connections and select Refresh from the succeeding pull-down menu. 

Xero data in Microsoft Excel

If your excel version is later than 2016, click here instead.  If you have any questions or concerns, please contact us. 

learn more

Why is PowerBI important for Accountants?

As an accountant, you may find yourself referring to multiple sources to track the performance of your client’s cash flow, profitability, business trends and even bookkeeping cycle. What if you could access those results and comparisons automatically refreshed, on-demand, from your tablet, smartphone, PC, or even wearable device? Business intelligence and data visualisation systems can provide accountants with – the critical data and organisation summary in a custom format in one (or more) visuals or a dashboard. One of these powerful business intelligence tools is PowerBI.
READ MORE

Power BI: What is DAX?

DAX (Data Analysis Expressions) is a formula expression language developed by Microsoft to interact with data in different analysis services and visualisation tools platforms like Power BI, PowerPivot and SSAS tabular models. DAX can seem familiar to competent excel users as the language uses similar formulas and expressions.
READ MORE

How to connect your Xero account(s) to AccountingBi

This guide will walk you through two methods for connecting your Xero accounting software to AccountingBi
READ MORE

How to call your profit and loss account reports from Xero to AccountingBi

We have outlined a step-by-step process of calling your profit and loss report data from your Xero account to AccountingBi - article and video.
READ MORE

How to call the balance sheet reports from Xero to AccountingBi

We have outlined the steps of calling balance sheet reports from Xero to AccountingBi in a video (watch below) and an article.
READ MORE

How to connect Microsoft excel to Xero through AccountingBi

This video and article provide steps for connecting Xero to PowerBi through AccountingBi. It assumes that you've already pulled the Xero API from accounting BI for your various reports.
READ MORE
1 2 3 5
READ MORE
Accounting.bi - Business Intelligence Software
Copyright © 2023 Accounting.bi is a brand owned and operated by Accounting SQL Limited, incorporated in the United Kingdom. ​
Terms & Conditions - GDPR