Microsoft Excel has tools to help you extract data from web sources without coding experience. In this article, we're going to demonstrate how it's possible to create a live feed into Excel from a Layer report via the public API.

Before we start, obtain a valid set of API tokens so we can authenticate our API calls. You can manage API application tokens in API Management within Company Settings.

We recommend you create a new application for auditing purposes.

Next up, look for the API link for the report you wish to bring into Excel.

In this case, we're going to look at "All Opportunities by Closed Date" for the past year.

After setting your parameters and running a sample report.

You'll see an API Link window appear in the top right corner of the Report Download screen. Copy this link to the clipboard and paste it into notepad.

It should look something like this:

Due to the way Excel parses this data, we will need to add an extra parameter onto the query string of this URL before it's ready to go;

So your final API URI should look something like this:

Now we have the URL fully formed, we're ready to go and pull this into Excel.

Firstly, select the position in your spreadsheet you wish to pull this data in to, and select "From Web" from the "Data" tab

Configure your web request as follows, in "Advanced" mode, where:

You'll then be presented with the Power Query editor, where we need to make a few alterations before pulling in the feed.

Without any formatting, the query pulls in as follows:

We need to hit the "To Table" button in the "Transform" tab in order to start formatting this data into the style in which we need it for our Excel table:

Once you've done this, hit this icon in order to prepare the table & select the columns you need:

This should give you a better view of your table

Format this as you need, and then hit "Close & Load" in the Home Ribbon to bring this data into Excel

You'll then see this as the table data loads for the first time.

And eventually, we'll see this:

TIP: There are plenty of properties in the "Query Properties" tab you can customise in order to make your data feed useful and keep it up to date.

Please refer to Microsoft Office documentation for further information on data feeds.