WebQuery Makes for Easy Website Analysis

By David Barnes, Director, Business Intelligence

The simplest definition of Analytics, according to Wikipedia, is "the science of analysis.” A simple and practical definition, however, would be how an entity (i.e., business) arrives at an optimal or realistic decision based on existing data. Business managers may choose to make decisions based on past experiences or rules of thumb, or there might be other qualitative aspects to decision making; but unless there are data involved in the process, it would not be considered “analytics.”

What better way to analyze data than to do so with one of the most widely used data crunching machines known to the business world, Excel! Excel offers a plethora of tools that allow its users to become more efficient at slicing, dicing and otherwise analyzing data with relative ease. With analysis comes efficiency and optimization of one’s time and efforts; enter the Excel WebQuery!

Excel’s WebQuery is a built in external data importer allowing users to enter a simple URL to retrieve up to the minute data and/or web based information. Whether you’re using a proprietary reporting software solution that offers customizable user reports, like Resolution Media’s RM3 or checking stock quotes on Yahoo finance, WebQuery works like a charm. With WebQuery you can now enter a URL and have any available data or information from that URL passed into an Excel Worksheet, including ticker quotes and daily data checks. Now, you probably want to stick to data driven information that tends to come in a table style format as it looks nice and clean in Excel, but feel free to experiment.

With plenty of customizable options built in you can leave your Worksheet open and set it up to automatically update your data. The refresh property allows for minutely updates if you’re into “right now” data, but can be set at any minute increment up to 32,767 minute intervals. Users also have the ability to manually refresh data from a WebQuery at their leisure.

All in all, the WebQuery by Excel is an excellent resource for those who are in need of current information in a dynamic data driven environment. A great mini five step tutorial on how to set up a WebQuery is available below.

Here’s how it works:

  1. In Excel 2007, click on the “Data” tab and then “From Web”
    a. If you’re operating with Excel 2003, click HERE for an example.
  2. Enter your web URL into the Address bar and press Go to render your page.
  3. Any area that contains a yellow arrow is a selectable data table. You are able to select one to many areas which will show up as green checkbox(s) when selected.
  4. Click Import
  5. Click to view larger image.
    Excel WebQuery Process

  6. Select a cell where you’d like your data to be placed and press “OK”.
  7. Click to view larger image.
    WebQuery Data Import

That’s it, you’re done, here’s what it looks like in your Worksheet. Feel free to sift through the “Properties” to customize your WebQuery further.

Click to view larger image.
Excel WebQuery Results


dgould said...

Sweet - This is really cool. never would have figured this one out on my own.

David J Barnes said...

Yeah, it's not as intuitive as say changing the background color of a cell.

Copyright © 2008 Resolution Media, Inc. All rights reserved.