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

Posted on July 30th, 2008 in ideas , Learn Excel - 32 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?

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

32 Responses to “Want to Feed RSS to your Excel Sheet? Read this!”

  1. pvav says:

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

  2. Jon Peltier says:

    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.

  3. Hypnos says:

    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”)

  4. Chandoo says:

    @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

  5. KD says:

    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?

  6. Chandoo says:

    @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 ?

  7. calceola says:

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

  8. chandoo says:

    @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

  9. calceola says:

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

  10. […] If you are looking for a way to access RSS feeds from your Excel sheet, read this. […]

  11. JP says:

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

  12. mts says:

    What about more than 20 items? Any solution?

  13. Chandoo says:

    @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…

  14. Jerry Whiting says:

    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.

  15. […] ImportXml() etc. using which you can build a stock portfolio tracker, Webpage monitors, RSS to Excel converter. But these functions don’t seem to work properly when you open the document in Excel using […]

  16. Joy Fulton says:

    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!

  17. Michael says:

    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?

  18. Luke says:

    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

  19. Amith says:

    Hi Chandoo,

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

    regards,

    Amith

  20. Erwin says:

    Hi there!, I have Excel 4 Mac latest version and I want to know how to use New Web Query on Excel 4 Mac ‘cuz there’s not any button like that.

    Thnx 4 your time.

  21. Sebastiano says:

    Hi, very useful information, however there are few differences now (maybe cause of some updates in google docs and excel 2007).
    1) the correct formula is: =ImportFeed(“http://rssfeeds.s3.amazonaws.com/goldbox”; “items title”;true;20)
    with ; instead of , as you mentioned
    2) when exporting to test, google docs generates a .tsv file (and not a .txt file) that is not opened in Excel 2007 if given as a source for a web data source (it prompts you to open the file with notepad)

    so actually this method is no longer working (unless i did something wrong)
    do you have any advice or update on how to use it with newer version of google docs and excel 2007

  22. Ace says:

    I skipped the Google Spreadsheet route because of the 20 record limitation, and I instead imported data directly into Excel, but I still can’t get more than 20 RSS records … I need about 60 to get accurate results for what I’m doing.

    Thanks

  23. FeedBert says:

    I am trying to embed =ImportFeed(“http://rssfeeds.s3.amazonaws.com/goldbox”, “items title”,true,20) as per the worked example above but it doesn’t seem to be working.

    It it working for anyone else?

  24. Eric R. W says:

    FeedBert, it worked for me.

    Chandoo- great site. May I ask though- what code do I need to import the “description” data too?

    -Eric

  25. seblog says:

    FeedBert, just read my comment (n.19) you will see that a little bit has changed and need to be fixed in your formula…

  26. Eric R. W. says:

    Ok so I figured out how to get the decriptions from the RSS feed by using “summary”.

    But so of the other amazon RSS feeds don’t grab much information from the page. Does anyone know if Amazon has a RSS feed that gathers more content from the page about each item?

    The only why I know is to use a content scraper. Any other ideas?

  27. Sean H. says:

    Does anyone know how to get the time stamp out? What the item name would be?
    Trying to determine when sites posts the types of articles they post…

    Thanks
    Sean

  28. ArthurF says:

    I’d like to have several RSS feeds sources in the same table. It can’t make it work. Does anybody know how to do that? Thanks

  29. BWB says:

    I tired to follow this a long. When I shared my spreadsheet, Id did not get the publish option so I could not publish it as text doc.
    how do I do that?

    Much regards and Thank you.

  30. Nishad says:

    Dear Chandoo and all:

    Please let me know how to proceed as i need to populate few fields from stock exchange sites like stock name, last prices (quote) , dividend into my excel sheet automatically .
    thanks in advance
    Nishad

Leave a Reply