Want to Feed RSS to your Excel Sheet? Read this!

Posted on July 30th, 2008 in Learn Excel , ideas - 19 comments

rss-feeds-to-excel-spreadsheet-howtoRSS feeds are everywhere. So much that you cannot avoid them in your data processing, analytics or day to day spreadsheet needs.

If you can get the RSS feeds to excel sheet you can do pretty interesting things with it, like:

  • Prepare a sheet to watch deals on Amazon, Craigslist, Deals2buy and other favorite sites
  • Watch news, alerts on your company / brand / blog using technorati, google news etc.
  • So much more, just use your imagination

Of course, Excel 2003 (and above) has XML import option using which you can get any XML files (and thus RSS files too) and show them in spreadsheet. But the problem is, the layout is messy, and too confusing. So here is a simpler way to read RSS/ATOM/XML feeds from excel sheets.

For this example, let us build an excel sheet that will fetch Amazon Gold box Today’s deals RSS feed show all deal titles and product URLs.

  1. First Create a new Google Docs – Spreadsheet

    We will use a round about way to get RSS feed to our excel sheet. Create a new google docs – spreadsheet. We will now use google’s importfeed() external data function. This function fetches external feed data and loads it in to the spreadsheet. We will write this function in 2 columns, one for the feed title telling us what the goldbox deal is about and another with the product’s URL.

    The syntax will look like: =ImportFeed("http://rssfeeds.s3.amazonaws.com/goldbox", "items title",true,20) for deal title and =ImportFeed("http://rssfeeds.s3.amazonaws.com/goldbox", "items url",true,20) for the URL.

    When done, the sheet should display gold box deals like below:

    amazon-gold-box-feed-google-docs

  2. Now share the google doc as text file to access from excel sheet

    Once the feed is fetched to the google doc, we will share this sheet as a txt file so that we can read the gold box deals from excel.

    You can share the google doc by clicking “Publish” option available to the right.

    We will select the “more publishing options” to share this sheet as a txt file. Click here to see a sample shared txt file containing latest 20 gold box deals.

    Make sure you have clicked on the “Automatically republish this document when changes are made” option.

  3. Finally read the text file from excel using web queries

    In the excel sheet we will create a new web query to connect to our shared text file using menu > data > import external data > new web query as shown below:

    import-web-pages-to-excel-sheet

    We will mention the URL of the shared google doc in the web query dialog and select everything. See below:

    web-query-excel-howto

    That is all, we have now fed our excel sheet with tasty gold box deals. When you need new deals just “refresh data” :)

    download the excel sheet with rss feeds to spreadsheet example and play around

Do you like this? Tell me what would you do if you get RSS feeds to excel sheets?

More...
Subscribe to Chandoo.org Email updates and get a free excel e-book with 95 tips & tricks

Comments
pvav July 30, 2008

Can by Excel sheet used like RSS source too (new line=new feed)?

Jon Peltier July 30, 2008

This is very interesting. I’m starting to think of it as a way to get product pricing information from different sources, for a client who compiles this information for some electronics markets.

Hypnos July 31, 2008

Dude… do you dream in Excel?

Do you talk to people in Excelese? (You know, “Meet me at J59 and we’ll go to A22 for lunch”)

Chandoo July 31, 2008

@Prav … I didnot understood what you meant… Can you elaborate?

@Jon: that is very neat, I use this approach often to find nice deals on books, electronics across sites…

@Hypnos.. that is very funny.. lets catch up at J59 :D haha
yeah, I have shifted the focus of blog to mainly excel and technology :D

KD August 1, 2008

Hey do you use the same technique for Craigslist? I tried to do it and the formatting really sucks… amazon, google news and other stuff works fine. Craigslist formatting on excel is not pretty. Did u encounter this?

Chandoo August 2, 2008

@KD.. I have used similar technique for craigslist when looking for a car. My criteria was “between $4000 and 6000, made by toyota, honda, volkswagen, nissan, from years 1998-2002, in my city”. I created yahoo pipe to filter out car posts made on craigslist and read them through my google reader. most of the time the craigslist results shown in reader had to be clicked through to view properly though..

did you try yahoopipes or one of the feed filtering services ?

calceola August 4, 2008

what’s about excel to rss, I have a daily updated excelsheet which I like to feed to some people – any hint?

chandoo August 4, 2008

@calceola: the best way for you could be using google spreadsheet as each change you made would be released in to spreadsheet feed. But if you want to stick with ms-excel, you can probably,

1. publish your sheet as a webpage (save as, file type as web page or txt file or csv file)
2. upload it to a place (overwrite next day)
3. pass this url through any service that would generate rss feeds from html pages that do not have feeds
4. give that feed url to people who wants to track your sheet through rss.

repeat step 1-2 every day. Very tedious, I know, but I am not aware any option in excel 2003 that can get automatic rss feeds for you. If you come across one, let us know and we will buy you a donut :D

calceola August 6, 2008

@chandoo: sometimes a solution ist so easy, thanks.

JP February 5, 2009

How about a UI version of this? Like a newsreader, but in Excel :)

mts March 24, 2009

What about more than 20 items? Any solution?

Chandoo March 25, 2009

@mts .. good question and welcome to PHD blog.

I think this is more of a google docs (or the way importfeed) limitation. If you need more than 20 items to be read from excel, you may want to write your own code to read RSS from web instead of going through this route. It is very simple and you can use all the built in stuff like DOM parser etc….

Let me know if you need more ideas to get this going…

Jerry Whiting April 26, 2009

I stumbled across this looking for something similar. I wanna read RSS feeds in Excel but find the Web Query “obtuse” at best.

I’d like to be able to read the item titles and item descriptions for a given feed within Excel. I’d be happy if it updated every time I opened the spreadsheet. Nothing fancy, right? http://www.AzaleaRSS.com/FAQ/feed.xml

The above technique works but I’m confused about having to involve Google Docs Spreadsheet.

Joy Fulton July 29, 2009

This does work great – thanks so much for this tip! Awesome!

I am interested in your comment above (March 25, 2009). I do need more than 20 items to be read from excel. Can you help with examples of code to read RSS from the web? With up to 100 items or more?

You said, “It is very simple and you can use all the built in stuff like DOM parser etc….” I did find code on another blog, and tried it, but it did not work.

Thanks in advance!

Michael August 26, 2009

Hi Chandoo…great advice here. I run into a hiccup though and that is when doing the data import in Excel (step 3 in your tutorial), Excel gets hung up doing the refresh. It displays the “refreshing” message but doesn’t update. Am I missing something?

Luke October 22, 2009

Chandoo – fantastic post, very useful info. I too could use any wisdom you have on collecting more than 20 posts, could you point us in the right direction? Many thanks,

Luke

Amith August 26, 2010

Hi Chandoo,

can you teach me how to get more than 20 items from google reader into excel.

regards,

Amith

RSS feed for comments on this post. TrackBack URI

Leave a comment

   Name (required)

   E-mail (required, never displayed)

   URL