How to Use Web Queries with Excel - Part 1

You can keep the data in your spreadsheet up to date by getting it automatically from the Net - Ian Waugh uses Excel to show you how...

Excel has dozens of uses - everything from doing accounts, creating business plans, forms and invoices, to solving complex What If equations. Don't worry - we won't be doing any hard stuff here!

One of Excel's most popular uses is for finances, and with an increasing number of people dabbling in stocks, it's a very useful way to keep track of a portfolio. Particularly if you can get up-to-date prices directly from the Internet rather than looking it up yourself and entering it into the spreadsheet manually.

That's what we'll be doing in this mini series, although you can use the same principles to fetch and incorporate any type of data from the web such as statistics, timetables, weather reports, even lists of personnel - if it's up there you can usually get it!

First of all, if you're relatively new to Excel, check out our introductory series.

Find your Web page

The first step is to find the web page whose data you want. Excel can grab an entire web page but you will usually only want a small part of it. There are two ways to do this, and the best method will depend on the web page and how easy the data in it is to access.

The first method is to load the specified tables that you want. Most web pages which display finance data, for example, put it in table format which Excel can read. The second method is to access the data directly - we'll look at this in Part 2.

On the table

From Excel's Data menu select Get External Data>New Web Query.

If you know the page you want, you can type it in here but an easier way is to click the Browse Web button. This opens your browser and lets you surf to the required page. The New Web Query window automatically copies the URL from the page. Clever, eh!

AOL has a quotes section so surf over to:

http://pfweb.aol.com

In the Enter Symbol box enter BT, select London from the Exchange menu and click the Get Quote button. Our US and Canadian cousins can find quotes from their exchanges. If you're not sure of a company's symbol, click the Lookup Symbol link.

The result for BT produces a few options. We want BT-A so click on that and you'll see a display of the company name, the last price, the previous close, the change and so on. We're primarily interested in the last price but you can use however much data you want.

Okay. Go back to the New Web Query window and you'll see that the URL has been inserted.

Now we only want the data from this one table so how you proceed now depends on the web page. As it happens, this page helpfully only has one table so click the "Only the tables" radio button and then on OK.

This produces the "Returning External Data to Microsoft Excel" window. Never use one word when six will do!

If the page has lots of table, you must check the "One or more specific tables on the page" button and enter the tables that you want. How? Well, some web pages may name them but for most pages you will have to enter the numbers of the tables as they appear in the page. Yes, this will likely require a little trial and error but it's not too arduous.

Desirable properties

At this point it's worth clicking the Properties button so you can determine how the data is imported. This window may vary from one version of Excel to another but the main points are:

Refresh. Excel can refresh the data when the file is opened. If you have a broadband connection, for example, you may want to check this box otherwise leave it unchecked so you can refresh the data manually when you are connected to the Net.

You can set the program to refresh the data automatically every so-many minutes. If you like to watch your share prices ducking and diving throughout the day, this is for you. Otherwise if you just want to check prices at the end of the day, leave it unchecked. You can always refresh the data manually.

Data formatting. The two main settings here are "Preserve cell formatting" and "Adjust column width". As we're not going to be too bothered about the layout - we'll see why in the Part 3 - you can leave these checked.

Click on the OK button to go back to the previous window then click that OK button and - voila! - the data will appear in your spreadsheet.

In the next part we see how to pick and choose the data we want to retrieve from the web.

 

Ian Waugh
Read More of Ian's music reviews and tips at www.making-music.com