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

Share

Facebook
Twitter
LinkedIn

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?

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Overall I learned a lot and I thought you did a great job of explaining how to do things. This will definitely elevate my reporting in the future.
Rebekah S
Reporting Analyst
Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

One Response to “How to compare two Excel sheets using VLOOKUP? [FREE Template]”

  1. Danny says:

    Maybe I missed it, but this method doesn't include data from James that isn't contained in Sara's data.

    I added a new sheet, and named the ranges for Sara and James.

    Maybe something like:
    B2: =SORT(UNIQUE(VSTACK(SaraCust, JamesCust)))
    C2: =XLOOKUP(B2#,SaraCust,SaraPaid,"Missing")
    D2: =XLOOKUP(B2#,JamesCust, JamesPaid,"Missing")
    E2: =IF(ISERROR(C2#+D2#),"Missing",IF(C2#=D2#,"Yes","No"))

    Then we can still do similar conditional formatting. But this will pull in data missing from Sara's sheet as well.

Leave a Reply