Using Amazon's Web Services as a source of data, you can easily integrate live information about books into Excel spreadsheets. This example imports sales rank data for particular books and calculates the average rank.
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.
To make this work, you'll need to sign up with Amazon and get an associate tag. To do this, visit http://www.amazon.com/associates/. Then you'll need to go to http://amazon.com/webservices/ and click on "Apply for a free developer's token". Both of these pieces are required to use Amazon's web services.
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.
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 an Amazon Power Search to specify a publisher
O'Reilly) and a keyword
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. ...