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.
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.
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
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 ...