How to connect Microsoft Excel to Xero through AccountingBi

March 15, 2024

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

There are three methods available: Excel Add-in, Excel Power Query, and a limited option for specific reports (Journals) through contacting AccountingBi support.

Before you begin:

Great! Now that you've downloaded your reports, let's move on to viewing them in Excel. The following steps will guide you through the process to ensure everything is accurate and displayed correctly.

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.

  1. To verify that you've retrieved the accurate data reports, navigate to different endpoints: Balance Sheet and Profit and Loss reports
  2. 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.
  3. 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:

   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 Power Query M Code:

  • Click on Data Tab in Excel, go to Get Data and Launch Power Query Editor.

  • Click on New Source from New Query Section.

  • From New Source select Other sources and click on Blank Query.

  • Then Click on Advanced Editor present in Query Section.

  • Paste the below m code:
    let

    Token = "Bearer (Paste the Bearer Token here)",

    Url = "(Paste the URL Here)",

    GetAPIData = (token as text) as record =>

    Let
    Source = Json.Document(Web.Contents(Url, [Headers=[Authorization=token]])),
    AnotherAPICall = if Record.HasFields(Source, {"url"}) then
    Json.Document(Web.Contents(Source[url]))
    else
    Json.Document(Source[data]),
    Result = AnotherAPICall
    in
    Result,
    FinalResult = GetAPIData(Token)
    in
    FinalResult

  • Then click on done and in edit credentials select Public in all then

  • Click on into table.

  • Then click on Double arrow and click on Expand to new Rows

  • Again click on Double arrow and click ok

  • Your Data will be displayed accordingly.

b. Using Templates:

Excel templates with pre-built web connections compatible with Power Query are available at this URL: accountingbi template dashboards

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:
  1. Go to “Data” Tab in Excel.
  2. 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.

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.
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 6
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