Amazon Hacks

Import Data Directly into Excel

Manipulate data the old-fashioned way—with a spreadsheet.

Microsoft’s Excel spreadsheet program was created to analyze and manipulate data. Using Amazon’s Web Services as a source of data, you can easily integrate live information into Excel spreadsheets. This example imports sales rank data for particular books and calculates the average rank.

What You Need

This hack relies on Excel’s ability to perform web queries, so you’ll need Excel 97 or higher. Excel’s Web Queries tool transforms simple HTML tables into Excel spreadsheets.

The Code

This code uses several features of both Amazon Web Services and Excel. Once you see how it’s put together, building your own queries is a snap.

Getting the Data

This hack starts with a standard XML/HTTP query. We want to analyze sales ranks of O’Reilly’s Hacks series, so we build a standard query to retrieve those results.

http://xml.amazon.com/onca/xml3?t=insert associate tag 
                  [RETURN]
&dev-t=insert developer token&PowerSearch=publisher:O'Reilly [RETURN]
%20and%20keywords:Hack&type=heavy&mode=books&f=xml

This request uses a Power Search [Hack #9] to specify a publisher (O'Reilly) and a keyword (Hack).

Transforming the Data

The next task is to get the Amazon response data into a form that Excel can work with. Because Excel Web Queries rely on simple HTML, Amazon’s response must be transformed. As mentioned, XSL stylesheets [Hack #83] are a quick way to make that happen.

Put the following code into a file called excel_SalesRank.xsl ...