Microsoft Excel — RSS News Feed Into Excel Using PowerQuery?

This was the Challenge Question I Got Today

Don Tomoff

Follow

Published in

Let’s Excel

4 min readJun 17, 2017

--

The Challenge?

This question came up with a client CFO today because we were looking for an easy way to share industry information and news with the team.

They have Excel open everyday and we wanted to provide a resource that would require little (OR NO) maintenance. So we settled on identifying a few relevant RSS Feeds.

WHAT ARE FEEDS? WHAT IS RSS?
A feed delivers regularly updated summaries of web content, including headlines that link to full versions of that content. When you subscribe to a feed using a feed reader, you’ll be able to quickly see summaries of new information in one place. RSS (“Really Simple Syndication”) is a widely-supported format for feeds. — http://www.reuters.com/tools/rss

PowerQuery is a great way for importing content into Excel, but could we use it for an RSS feed? As it turns out, this is not an easy thing to do (at least my Google search made it appear not easy…).

But there is a workaround that we came up with (and it IS easy)…

The Goal

Here is my outline of expected ongoing effort on my part:

The workaround is to use Google Sheets, which provides for the ability to import a feed (“IMPORTFEED” function), and then use PowerQuery to import the Google Sheet in Microsoft Excel.

So, once the process is established, my required effort is:

Open Microsoft Excel,
Refresh query
Use it!

The Process

Here is the workflow:

Identify the RSS feed(s)

I selected a Reuters Business News feed from their web page of RSS feeds:

Reuters RSS Feed list — http://www.reuters.com/tools/rss

Setup Google Sheet with IMPORTFEED set up,

Use IMPORTFEED function in Google Sheets

Here is a description of how IMPORTFEED works and the function syntax

Set up the formula — here is the formula to import the “Reuters Business News” feed =IMPORTFEED(“http://feeds.reuters.com/reuters/businessNews",,TRUE,20)

Google Sheet IMPORTFEED formula — imports RSS feed into spreadsheet

Publish Google Sheet to the Web,

Select Sheet to Publish — Copy Link to use in Excel PowerQuery

Query from the Web into Excel

Access PowerQuery Add-in within Excel (In Excel 2016, this is now called “Get and Transform”)

PowerQuery Add-in in Excel — Query “From Web” is my choice

Paste the copied link into the “From Web” dialog box that appears.

Preview Query and select Edit (to modify before loading into Excel)

Edit query to import as you want it, then click “Load To”

Edit Query to “Transform” it to your specifications, then select “Load To”

It’s now in Excel!

Review and modify table format to your preference. Here is the final basic table:

Modify formatting in Excel, add Hyperlink (URL Column hidden once done) to allow Quick Access

This example is for one RSS feed, but hopefully you see how this can be quickly accommodated for multiple feeds, making it a very valuable resource for any organization.

Good luck!