Skip to Main Content
Amazon Hacks
book

Amazon Hacks

by Paul Bausch
August 2003
Intermediate to advanced content levelIntermediate to advanced
304 pages
7h 33m
English
O'Reilly Media, Inc.
Content preview from 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 ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

From ChatGPT to HackGPT: Meeting the Cybersecurity Threat of Generative AI

From ChatGPT to HackGPT: Meeting the Cybersecurity Threat of Generative AI

Karen Renaud, Merrill Warkentin, George Westerman
Incident Metrics in SRE

Incident Metrics in SRE

Stepan Davidovic

Publisher Resources

ISBN: 0596005423Supplemental ContentCatalog PageErrata