Setting up an Authenticated Excel Live Feed

This guide explains how to use an Excel Power Query to set up an authenticated Live Feed. Due to how live feeds in Microsoft Office 365 work, the standard two-step data retrieval process for Excel no longer supports basic authentication. Setting up a Power Query instead allows you to use authenticated feeds in Excel, ensuring your data is protected from unauthorised access.

Before you get started

Permissions

• You need the School: General Admin: Administer permission to access the Live Feeds page on School MIS
• If using MAT or LA MIS, you need the Group: Group Details: Administer permission to access the Live Feeds page.

If you don't have the relevant permission, ask your admin team to give you permission using these instructions.

Prerequisites

Before starting, ensure you have:

• Created a Live Feed in Arbor 
• Enabled the authorisation setting for the feed. 
• The specific Excel Power Query code generated by Arbor 

Setting up the feed

Follow the instructions below to configure your authenticated feed in Excel. The steps differ slightly depending on your operating system, so please ensure you follow the correct instructions for your device.

Mac

• Create a Live Feed or click on an existing feed on the Live Feeds page (School > Data > Export > Live Feeds)
• On the slide-over, tick Require authentication

• Scroll down until you see the Excel Power Query section (this only shows if you have ticked Require Authentication).

  

• Copy everything starting from 'let' down to 'FinalTable' (i.e. all of the 'code' under the Excel Power Query heading)
   
• Now open Excel
• In Excel, navigate to the Data tab, and click Get Data (Power Query)
• Click From Other Sources 
• Select Blank Query

• Remove all the default text/code and paste your copied code.
• Click Next (You may see a warning that says 'e could not evaluate this query due to invalid or missing credentials' at this point, you can ignore this)

• Click Close and load

• On the Connect to data source pop-up, ensure the Authentication kind is set to Anonymous.
• Click Connect to add your feed

Automating updates for Mac

Now that you have added your feed, there is an option to configure updates every time the file is opened. This can be enabled on the Data tab in Excel by clicking Properties and then ticking Refresh data when opening file.

Windows

• Create a Live Feed or click on an existing feed on the Live Feeds page (School > Data > Export > Live Feeds)
• On the slide-over, tick Require authentication

• Scroll down until you see the Excel Power Query section

  

   

• Copy everything, starting from 'let' down to 'FinalTable' (i.e. all of the 'code' under the Excel Power Query heading)
• Open Excel
• In Excel, navigate to the Data tab and click Get Data

• Select From Other Sources in the menu

• Then select Blank Query (near the end of the list).
• Right click Query 1 and select Advanced Editor

• Remove all the text/code and paste your copied code.

• Click Done to save the code
• Click Close & Load