RSS 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.
-
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:
-
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.
-
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:
We will mention the URL of the shared google doc in the web query dialog and select everything. See below:
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
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. 
Do you like this? Tell me what would you do if you get RSS feeds to excel sheets?
33 Responses to “Want to Feed RSS to your Excel Sheet? Read this!”
Can by Excel sheet used like RSS source too (new line=new feed)?
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.
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")
@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 😀 haha
yeah, I have shifted the focus of blog to mainly excel and technology 😀
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?
@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 ?
Dear Chandoo, would be great if you'd be kind to please share the spreadsheet you mention above....with thanks
what's about excel to rss, I have a daily updated excelsheet which I like to feed to some people - any hint?
@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 😀
@chandoo: sometimes a solution ist so easy, thanks.
[...] If you are looking for a way to access RSS feeds from your Excel sheet, read this. [...]
How about a UI version of this? Like a newsreader, but in Excel 🙂
What about more than 20 items? Any solution?
@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...
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.
[...] 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 [...]
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!
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?
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
Hi Chandoo,
can you teach me how to get more than 20 items from google reader into excel.
regards,
Amith
[...] Make your own Amazon Deal Tracker in Excel [...]
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.
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
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
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?
FeedBert, it worked for me.
Chandoo- great site. May I ask though- what code do I need to import the "description" data too?
-Eric
FeedBert, just read my comment (n.19) you will see that a little bit has changed and need to be fixed in your formula...
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?
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
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
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.
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
Hi I need to pull data from RSS feeds but with the title and url i also require description provided. URGENT