How to connect Microsoft Excel to Xero through AccountingBi

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:

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

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:

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

• (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.