Web lookup using Excel

Take a look at the Excel screenshot below.

Amazon Web Prices

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…

Flickr has a camera finder that shows the most popular cameras in the Flickr community.

Flickr Camera Finder

I love comparing gadgets, I’d been doing some research around these cameras, and the Fuji series (because I own a Fuji Finepix S5600). I’d normally make a spreadsheet that compares these cameras on various parameters, including price.

Since I believe in never typing in data, wondered if there was a way to get the prices in automatically…

Two things made this possible.

  1. Amazon offers web services which let you get price (and almost any other) data for their products
  2. Visual Basic lets you use pretty much any ActiveX object as a control. Microsoft offers MSXML which you can use to load (or download) any XML file, and parse it.

The first task is to get the XML feed for a product you want. Amazon lets you do that through by typing in a URL. The best way to construct the URL is through AWSZone. I picked the US ItemSearch method, which searches for a title or keyword within a category, and returns the matches. The feed for the Canon EOS Digital Rebel XT, based on this, would be at:


http://ecs.amazonaws.com/onca/xml?

    Service=AWSECommerceService&
    Version=2005-03-23&
    Operation=ItemSearch&
    SubscriptionId=0525E2PQ81DD7ZTWTK82&
    SearchIndex=Electronics&
    Keywords=Canon EOS Digital Rebel XT&
    Sort=salesrank&
    ResponseGroup=Offers,Small

(You really need to replace the Subscription ID with your own.)

If you retrieved this URL, you have an XML file containing the details of all Canon EOS Digital Rebel XTs, sorted by sales rank.

To load this in Excel, you need to create a UDF in Visual Basic. First, go to Tools – References and enable Microsoft XML, v3.0 or v4.0. Now, to load an XML document, do this:

Dim xDoc As MSXML2.DOMDocument30
Set xDoc = New MSXML2.DOMDocument30
xDoc.async = False
xDoc.Load(url)

If the load succeeds, then you can extract the information fairly easily, using XPath.

xDoc.setProperty "SelectionLanguage", "XPath"
xDoc.setProperty "SelectionNamespaces", _
        "xmlns:a=""http://webservices.amazon.com/AWSECommerceService/2005-03-23"""

The first line says we’ll be searching using XPath. The second line is a workaround to support default namespaces. (Don’t worry about it. I don’t quite get it either.)

Finally, you get the price from the XML tree. In this case, it’s under ItemSearchResponse/Items/Item/OfferSummary/LowestNewPrice/Amount, and it’s in cents.

        AmazonPrice = xDoc.selectSingleNode("/a:ItemSearchResponse//a:Amount").Text

That’s it! Now that this function is defined, just pass it the category and keywords, and you have the price of the first matching product. You can retrieve any other information about products as well — like sales rank, weight, ratings, reviews, etc.


Here’s the spreadsheet for you to play around with.


Excel 2003 Power Programming with VBA (Excel Power Programming With Vba) Writing Excel Macros with VBA, 2nd Edition

  1. Saurabh says:

    There is a simpler way as well. Use Data->Import External Data->New Web Query. While this is not as powerful as the user defined function that you have created, but definitely a good starting point for lesser geeks :) I use this, for example, to have a dashboard kind of view for the status of the multiple projects that I handle at my work. We rely heavily on Microsoft Sharepoint at work, and using the Web Query option, I can easily get a snapshot of the status of various projects. Same can also fetch shareprices from various websites.

  2. S Anand says:

    Good point, Saurabh! Web Query does make getting stuff out of HTML very easy. My function operates in a different domain, though. It can access XML, which Web Query won’t, but can’t access HTML, which Web Query does. I’m planning a few more posts on how to extend this concept… that will explain what I mean.

  3. rick sherrin says:

    Hi, I work at a public library, in the acquisitions department. The majority of the work is manually keying in order information (that is available electronically elsewhere). I read your article about using Excel to retrieve amazon information. This is something that would be very useful to our library and would make my job easier. I have a little excel knowledge, but I have no experience with visual basic or xml, etc. So although it looks straightforward, it is still out of my reach. I would like to be able to enter a column of ISBN numbers in one column, and retrieve title, author, publisher, price, reviews, synopsis, cover image, in adjacent columns. I would appreciate your help if you would give me an example of how I could easily do this. Thanks Rick

  4. dat says:

    useful article!
    Btw, how do I retrieve other info from amazon (published date, sales rank, customer review, etc)???
    thanks

  5. S Anand says:

    @dat: Have a look at http://www.awszone.com/ for examples on other parameters.

  6. Lydia says:

    this worked until august last year when amazon changed the way the query signs into their system…it now requires a signature with a timestamp in addition to the subscription ID