RSS feeds in Excel

The technique of Web lookups in Excel I described yesterday is very versatile. I will be running through some of the practical uses it can be put to over the next few days

TO generalise things beyond just getting the Amazon price, I created a user-defined function called XPATH. It takes two parameters:

URL of the XML feed to read
Search XPath list string (separated by spaces)

This function can be used to extract information out of any XML file on the Web and get it out as a table. For example, if you wanted to watch the Top 10 movies on the IMDb Top 250, and were looking for torrents, an RSS feed is available from mininova. The URL http://www.mininova.org/rss/movie_name/4 gives you an RSS file matching all movies with “movie_name”. From this, we need to extract the <item><title> and <item><link> elements. That’s represented by “//item title link” on my search string.

The formula becomes XPath2( "http://www.mininova.org/rss/"&A2&"/4", "//item title link"). The result is a 2-dimensional array returning individual items in rows, and the columns are title and link. Pulling it all together, you can get the sheet above.

All of this could be done using a command-line program. Excel has one huge advantage though. It’s one of the most powerful user-interfaces. Increasingly, I’m beginning to rely on just two user interfaces for almost any task. One is the browser, and the other is Excel. With Excel, I could have a sheet that has my movie wishlist (which changes often), and add check to see if the torrent exists. Every time I add a bunch of movies to the wishlist, it’s just a matter of copying the formula down. No need to visit a torrent search engine and typing each movie in, one by one.

Another example. Someone suggests 10 movies to watch. I’d rather watch the ones with a higher IMDb rating. Again, enter the Web lookup. Type in the movie names. Use a function like this to look up the rating on IMDb, and sort by the rating.

The possibilities are endless.