May 3, 2007

Take a look at the Excel screenshot below. Yes, that’s right. I have a user-defined function called AMAZONPRICE. And it returns these cameras’ prices directly from Amazon.com. (Given the category and some keywords, it returns the price of the bestselling item on Amazon.com.) Here’s the code behind the function. Function AmazonPrice(index As String, keywords As String) As String Dim xDoc As MSXML2.DOMDocument30 Set xDoc = New MSXML2.DOMDocument30 xDoc.async = False If xDoc.Load("http://ecs.amazonaws.com/onca/xml?Service=AWSECommerceService" _ "&Version=2005-03-23&Operation=ItemSearch&SubscriptionId=03SDGQDFEB455W53SB82" _ "&AssociateTag=sanand-20&MinimumPrice=10000&ResponseGroup=OfferSummary,Small" _ "&Sort=salesrank&SearchIndex=" & index & "&Keywords=" & keywords) Then xDoc.setProperty "SelectionLanguage", "XPath" xDoc.setProperty "SelectionNamespaces", _ "xmlns:a=""http://webservices.amazon.com/AWSECommerceService/2005-03-23""" AmazonPrice = xDoc.selectSingleNode("/a:ItemSearchResponse//a:Amount").Text End If End Function This is how it all started… ...

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 and <item><link> elements. That’s represented by “//item title link” on my search string. ...