Pulling data into Excel from web queries

Excel 2007 is an amazing tool. I normally find Microsoft’s software unintuitive, annoying, and slow. But for quick numeric analysis tasks or advanced reporting, Excel can do amazing things. Before utilizing all this reporting and analytical firepower, you have to get some data into Excel. One method that allows for a very flexible setup is pulling data in from a web query. You can direct Excel to pull out one or more data tables from any URL-accessible resource. Here’s how:

In a new or existing workbook, go to Data in the Ribbon, then select “From Web”:
Then in the address field, enter the URL of the table you want to pull in. But first, watch and laugh as the MSN homepage loads by default and throws runtime errors in Microsoft’s own software. Always a good time.
Once you enter the URL and press Go, the tables available will load in the window below. Click the yellow arrow to the left of the table you want to import. It will turn green.
WARNING: If the report contains HTML you want to render, like links, you need to perform an extra step. Click Options in the top right of the query window. Change formatting to “Full HTML formatting”:
Then click OK
Once you have your table selected, click Import at the bottom, and the data should be imported.

Now a new sort of infrastructure becomes feasible. Web resources serving up raw or slightly processed data in the form of HTML tables connect to backend data sources. Spreadsheets pull down the latest data, which then powers advanced reporting and analysis functionality built into the spreadsheet. This allows a central source of data to feed a variety of individual approaches to crunching that data, without a steep learning curve or expensive reporting system.

In all fairness, OpenOffice’s Calc does offer the ability to import data from a web query. However, I continuously had problems with it not working with certain URLs. I’m not sure if the intended use case was different, and OO Calc only supports importing certain types of documents. But any arbitrary page with a table on it that one could view in a web browser would work fine with Excel every time. And this makes things a lot easier.

Be Sociable, Share!