 |
|
Advertisement |
 |
PPC
>
Computing
Guides >
How Do I....?
Web Queries 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.
|
