O'Reilly logo

Excel Hacks by Raina Hawley, David Hawley

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Hack #98. Import Data Directly into Excel

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.

Tip

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.

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 an Amazon Power Search to specify a publisher (O'Reilly) and a keyword (Hack).

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required