How to connect Microsoft excel to Xero through AccountingBi

April 20, 2022

This guide explains how to connect Microsoft Excel to your Xero accounting software using AccountingBi.

You can also watch the video (to be uploaded) - it gives a deeper insight into the process.

Before you begin:

Connect your Xero account to AccountingBi. You can find instructions for this process here.
Query Balance Sheet and Profit and Loss reports from Xero via AccountingBi.

Connecting Excel to AccountingBi: Three Methods Explained

There are three methods available:

Excel Add-in, Excel Power Query, and a limited option for specific reports (Journals) through contacting AccountingBi support.

Method 1: Using the AccountingBi Excel Add-in

This method offers two options: working with a blank Excel file or using a pre-built template.

a. Blank File

For a Single User to get an Add-in:

Installing the Add-in: Individual users can install the AccountingBi add-in directly into your Excel desktop and Excel Web. Here’s how to:

Access the "Add-ins" section under the "Home" tab, select "+More Add-ins," search for "Accounting.bi," and install the add-in into your system.

For Organisational Users:

If you're part of an organization, your Microsoft Admin will need to install the Accounting.bi Add-In and grant you access.

The process involves your administrator adding the add-in through the admin center and then individual users accessing it within their Excel application.

Active Subscription:

If you possess an active subscription to Accounting.BI then follow this third step. If not, then first set up your account in AccountingBi via this URL and take the Subscription with the given plans.

Let's now move on to viewing the downloaded reports in Excel and gain a complete understanding of the process.

To verify that you've retrieved the accurate data reports, navigate to different endpoints: Balance Sheet and Profit and Loss reports
Proceed to Excel Desktop or the web version. In the bottom right corner of the Home tab, you will see your installed add-in for AccountingBi reports.
Select the installed add-in and go to the " Select report" section. Choose the specific report corresponding to the data fetched from https://app.accounting.bi/.

4.When you see the "Click here for bearer token popup", paste the bearer token, and save the changes. Then click on "View Reports" to display the report in Excel.

5.Viewing your Reports in Excel

Please see samples of the Balance Sheet/ Profit & Loss Reports shown below:

For further assistance, please contact our support team at: support@accounting.bi.

b. Templates

1.Finding Templates:

Access the templates associated with the Excel add-in through the following URL: https://accounting.bi/template-dashboards/

2.Downloading the Template:

Choose the template that best suits your needs & requirements then download it.

3.Getting the AccountingBi Add-in for Excel:

There are two ways to get the Accounting.bi add-in, depending on whether you're a single user or part of an organization:

For an Individual User:

To integrate the Accounting.bi add-in into your Excel desktop and web versions, follow these steps:

Go to the "Home" tab in Excel, click on the "Add-ins", select "+More Add-ins," search for "Accounting.bi,". Locate the add-in and install it 0n your system.

For Organisational Users:

If you belong to an organisation, the administrator for the Microsoft Admin Centre might need to install the Accounting.bi add-in and grant you access. Contact your IT department for assistance.

4.Subscription and Account Setup:

If you already have an active subscription accessible at https://app.accounting.bi/, proceed to step 5 (data verification).

If not then first set up your account in AccountingBi by given URL: https://accounting.bi/tips/how-to-link-your-account-and-setup-acountingbi and choose a subscription plan that meets your needs.

5.Viewing your Reports in Excel

Now having downloaded your reports, installed the add-in, and set up your account, (if necessary), navigate to both the different endpoints: the Balance Sheet Reports and Profit and Loss reports sections to verify the accuracy of the retrieved data.

Following the acquisition of accurate data, go to Excel Desktop or web navigate to the bottom right corner of the Home tab where your installed add-in will be visible.
Select the installed add-in and proceed to the " Select report" section. Choose the specific report corresponding to the data fetched from AccountingBi.
When you see the "Click here for bearer token popup", paste the bearer token, and save the changes. Then click on "View Reports" to display the report in Excel.
Now your Data will be visible in that Table.

6.Refreshing PivotTables

If your Excel sheet contains pivot tables using the downloaded data, navigate to the "Data" tab.
Click on "Refresh All" to update the pivot tables with the latest data.

For further assistance, please contact our support team at: support@accounting.bi.

Method 2: Leveraging Excel Power Query

This method utilizes Excel's built-in Power Query tool to import Xero data from AccountingBi. There are two approaches:

a. Using a Blank File (for Profit and Loss & Balance Sheet):

1)Connecting Data through Get Data from Web

Accessing Power Query:

In Excel, go to the "Data" tab and under "From Other Sources", select "From Web" under the "Get Data" Section.

Click "Advanced" and paste the URL for the desired report endpoint you retrieved from AccountingBi (Balance Sheet or Profit and Loss).

Setting Up the Connection:

In the "HTTP request header parameters" section, in the Dialog box where it is written Enter or select a value type “Authorization” (must be the US spelling) and in the other dialog box type "Bearer", followed by a space and paste the Bearer Token (copied earlier).

(Note: To find URL Endpoints and Bearer Tokens, access the Balance Sheet and Profit and Loss reports within Accounting.bi.)

Then click "OK" to allow Microsoft Excel to start communicating with the Accounting Bi API to start loading the data.
Data Preview:

The next screen will show you the reports that you had already called from Xero through Accounting Bi.

Then data will be shown in Power Query click on "Into Table" tab to convert your data into a table.

Click the "Into Table" tab to convert your data into a table.

2.Expand the record by clicking on the two arrows pointing in opposite directions,

3.A table with your report data should now appear in Power Query.

Once you're satisfied with the data preview, click "Close & Apply" in the Power Query Editor. This will import the data into your Excel file as a table.

b. Using Templates:
Excel templates with pre-built web connections compatible with Power Query are available at this URL: https://www.smartsheet.com/top-excel-accounting-templates

Like the add-in method, there are Excel templates with pre-built web connections compatible with Power Query.

Download the template that best suits your needs from there and open it in Excel.
If you want to connect your own Organization data:

Go to “Data” Tab in Excel.
From the Dropdown Click on Launch Power Query Editor.

Locate the Bearer Token parameter on the left side of the Power Query Editor window.
Change the Bearer Token and your data will change accordingly in Excel Template.

To refresh pivot tables, go to the "Data" tab and click "Refresh All."
Please make sure to regularly refresh your reports, so that you can easily switch between the different types of reports, such as balance sheets or profit and loss statements.

(Note: To find URL Endpoints and Bearer Tokens, access the Balance Sheet and Profit and Loss reports within Accounting.bi.)
For further assistance, please contact our support team at: support@accounting.bi.

Method 3: Excel Power Query (Limited Options):

Excel Power Query functionality is limited for Journals reports.

We recommend contacting us at support@accounting.bi for assistance connecting this report to your Excel.

By following these methods, you can leverage the power of AccountingBi to seamlessly connect your Xero accounting data with Microsoft Excel, enabling you to create insightful reports and streamline your financial analysis.

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.

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.