Excel web query tutorial

Pulling data from the web into your spreadsheets

Do you find yourself going back to the same pages on the same websites every so often, trying to keep your spreadsheets up to date with the latest figures?

Good news... you can tell Microsoft Excel 2003 to look the numbers up, and drop them into the right place in your spreadsheet, automatically, every time you open the file. It's called an Excel web query, and it's no more difficult to set up than surfing the web.

Let's imagine you're a small business dealing in several foreign countries. You need to convert from various currencies into pounds sterling, and you're a frequent visitor to websites like MSN Money for the latest exchange rates.

What we're going to do is effectively lift the table of numbers from the web page, and into Excel. You could just 'copy and paste' the data into a worksheet, and Excel will format the data neatly into columns. But wouldn't it be better if you could just tell Excel to do that 'copy and paste' task for you, every time?

Here's how to do it. In Excel 2003, click on Data > Import External Data > New Web Query. A little window will pop up, with an Address box and a 'Go' button. If it looks to you a bit like a web browser, that's because it is. Type the address of the web page into the 'Address' box, and the page will load up beneath.

Excel adds lots of little arrowheads to the page, each time it recognises a grid or box on the web page. Web designers use grids and boxes for all sorts of layout tricks, so you'll probably see quite a few - even if there's only one true 'table' on the page.

Scroll down to the table of numbers, and look for the arrowhead beside it. As you hover over the arrowhead, it will change colour (yellow to green), and the relevant area will be outlined. If, as in the example below, there are two arrowheads in close proximity, choosing the right one for you may just be a case of trial and error.

Click on the arrowhead, and it will change to a tick, with the relevant area becoming highlighted. Then, just press 'Import' at the bottom of the window. This will take you back into familiar Excel territory.

Excel will then ask you where to put the data. In this example, we'll keep it simple, and drop it into the existing worksheet at cell A1.

After a brief delay, to let Excel retrieve the data you selected, you'll see the information inside your spreadsheet, at the location you specified. It can be copied, referenced and manipulated like anything else. Excel will also have done some usual things to the data, such as rounding off to two decimal places, or whatever you have set as your preferred standard.

That's pretty clever... but it gets better. Try right-clicking on one of the cells in your table. You should see an option to 'Refresh Data'. If you choose this, you'll probably see a security warning, followed by a brief flash as Excel goes to the original website, and brings in the latest data. If the website's version has updated, so will yours.

Now right-click somewhere in the table, and choose Data Range Properties. Under the heading 'Refresh control', you'll see various options.

You can tell Excel to refresh the data automatically, as frequently as once per minute if you want. But to meet our objective, we're going to tick the next box down - 'Refresh data on file open'.

Save the file to your hard disk, and close it. Now open it up again. Excel will recognise there's a web query in the spreadsheet and will ask you if you want to update the data.

If you say yes, you do want to enable automatic refresh, Excel will go to the web page we stored earlier, will find the table we highlighted earlier, and will paste the data into the location we indicated earlier. And all without you having to lift a finger.

This method works best on pages which are rigidly defined in grids. On the web or on an intranet, it shouldn't matter. A word of caution, though: if you're lifting data from someone else's website, be sure to check the terms and conditions. There may well be restrictions on what you are allowed to do, for reasons of copyright.

What next?