How to feed live data from a web page into Excel

There are two ways you can feed live data from a web page into Excel. The easiest just entails
making menu and dialogue box selections in Excel. However, you can perform more
sophisticated operations using a web query file.
You can capture data which is held in a table or as preformatted text (inside the HTML <table>
or <pre> tags).
Quick Importing of Live Data
You can easily import a table of data from a web page into Excel, and regularly update the table
with live data.
1. Open a worksheet in Excel.
2. From the Data menu select either Import External Data or Get External Data.
3. Select New Web Query.
4. In Excel XP:
○ Enter the URL of the web page from which you want to import the data and click
Go.
○ Choose the table of data you wish to import, then click the Import button.
5. In Excel 2000:
○ Fill in this dialogue with the URL of the web page, the table of data you want and
the formatting you want to keep.
 6. Choose how often you want to refresh the data. From the Data menu select Import
External Data again, then choose Data Range Properties.
This method is also useful for capturing static data from a web site. The data will be reliably
imported into correct columns and rows in Excel. If you merely copy and paste data from a web
page to Excel, you sometimes find the entire row of data is in one cell. (Using the Paste Special
command will often also achieve this, but not always.)

Creating a Web Query File

For more flexibility in importing data use a web query file. This is a text file with the extension
.iqy. Its contents looks like this:
http://weather.msn.com/local.aspx?wealocations=wc:UKXX0092

Selection=2
Formatting=None
• The first line is the URL of the page holding the data. In our example current weather
conditions in Manchester, from the msn.com site.
• The second line is blank.
• The third line sets which table of data to import, in this case the second table on the page.
It takes the form selection=. See below for more details.
• The fourth line specifies that any formatting on the web page should be ignored.
• Right-click here to download an example .iqy file
To use this text file,
1. In Excel 97 and XP, from the Data menu select Import External Data.
○ Select Import Data and choose your .iqy file.
○ Choose where to put the data and click OK. The live data appears in your
spreadsheet.
2. In Excel 2000, from the Data menu select Get External Data
○ Click Run Saved Query
○ Select the .iqy file you have created and choose where to put the data.
Other selection options are available: Selection=AllTables, Selection=EntirePage and
Selection="name of table". To find the name of a table you will have to look at the source code
of the page. For more than one table, separate the table names or numbers with commas.
Other formatting options are available: Formatting=None returns plain text without any
formatting. You could also use formatting=All, to copy all the fonts and hyperlinks in the web
page to the Excel sheet. Finally you could use Formatting=RTF, to keep most of the formatting
put not the hyperlinks.
Using a query file gives you great flexibility. For instance, you can code the file so Excel will
ask you which data items you want.

Requesting Different Items of Data

Excel calls this using dynamic parameters. As an example I'll use a Google search for "Windmill
Software".
If you searched on Google for "Windmill Software", the URL of the search results might look
like this
http://www.google.co.uk/search?q=windmill+software
If you used this as your Web Query URL, the search results for "Windmill Software" would be
placed in your spreadsheet.
However, if you use a line like this as your Web Query
http://www.google.com/search?q=["q","Enter the Search Term:"]
you will be asked for the search term Google is to use.
Of course, you won't often need to display search results in your spreadsheet, but it illustrates the
method. You can do this for any web site handling data in this way. That is, any website
returning data with a ? in the URL followed by a name=value pair.
In Excel XP, you can also nominate a cell in the spreadsheet to hold the query term.
Whenever the contents of this cell change, the data will be updated in Excel.
To do this:
1. In Excel, from the Data menu select Import External Data.
2. Choose an .iqy file then click the Parameters button.
3. Choose to "Get the value from the following cell" and select the cell.
Using a query file in this way provides an easy and quick way to get live data from the web into
your spreadsheets. You can choose how often to refresh the data, or simply to keep it as it is.
Updating Excel From the Web Rating: 3.9 out of 5
By Bill Jeffries Rate this article

 print this article

 email this article to a colleague

 suggest an article
Introduction
<script language="JavaScript" type="text/javascript"> document.write('<a
href="http://mjxads.internet.com/RealMedia/ads/click_lx.ads/intm/webdev/www.15seconds.com
/focus/dataaccess/L46/554645040/flex/WMBrands/MSFT_BIEB_MCS_GEMS_1h/msftbiebmcs
gemsbob.html/796857793830766f6257494141653974?
http://clk.atdmt.com/MRT/go/201770456/direct;wi.336;hi.280/01/" target="_blank"><img
src="http://view.atdmt.com/MRT/view/201770456/direct;wi.336;hi.280/01/554645040"/></a>');
</script><noscript><a
href="http://mjxads.internet.com/RealMedia/ads/click_lx.ads/intm/webdev/www.15seconds.com
/focus/dataaccess/L46/554645040/flex/WMBrands/MSFT_BIEB_MCS_GEMS_1h/msftbiebmcs
gemsbob.html/796857793830766f6257494141653974?
http://clk.atdmt.com/MRT/go/201770456/direct;wi.336;hi.280/01/" target="_blank"><img
border="0"
src="http://view.atdmt.com/MRT/view/201770456/direct;wi.336;hi.280/01/554645040"
/></a></noscript>

Microsoft Simplifies Client Virtualization

Microsoft has approached virtualization at several
different levels. In Windows Server 2008, Hyper-V is a
64-bit hypervisor-based virtualization technology that
offers reliable and scalable platform capabilities. >>

An Introduction to Hyper-V Virtualization

Microsoft has approached virtualization at several
different levels. In Windows Server 2008, Hyper-V is a
64-bit hypervisor-based virtualization technology that
offers reliable and scalable platform capabilities. >>

Discover the Advantages of Desktop

Virtualization
With offerings to virtualize the application, data, and
Windows operating system layers, companies can
choose the Microsoft technologies that allow them to
best optimize and manage their desktop infrastructure.
>>
Learn more. Test Drive Windows 7.
Evaluate Windows Server 2008 R2

MARKETPLACE
Autodesk - Civil 3D - Transportation
Accelerate Projects with Integrated Data Sources & Automatic
Documentation.
www.AutoDesk.com/AutoCAD-Civil-3D
Microsoft SQL Server® 2008 - Free Trial
Download the Free 180-day Trial of SQL Server® 2008 Enterprise
Edition!
Microsoft.com/EverybodysBusiness
Autodesk Maya
Use Autodesk Maya & Create Integrated 3d Models. Download Your
Free Trial Today.
www.Autodesk.com/Maya
Increase online sales and signups
Get feedback from your website visitors and increase sales and
signups.
www.starmypage.com
Web based bug tracking - AdminiTrack.com
AdminiTrack offers an effective web-based bug tracking system
designed for professional software development teams.
www.adminitrack.com
To the normal every-day Excel user, a web query is nothing more than a cool trick. But to
creative-minded Excel users, with some HTML knowledge behind them, web queries can lead
to a drastic increase in productivity.
Getting Started with Sample Excel Web Queries
To get started, take a jog over to Microsoft's page that explains the basics of web queries:
"Getting Data From the Web In Excel".
The example web queries that come with Excel are usually for importing stock quote data into
Excel. For rapidly changing data like stock quotes, the web queries can be set to automatically
refresh every 5,7,21,40,... minutes.
For more information about using web queries to download Stock Quotes into Excel, take a look
at the Excel Stock Quotes Template.
Although importing stock quotes into Excel happened to be the reason I came across Excel web
queries, I have since found many other ways to use them.
It is not necessarily the web query itself that is THE big secret that this article is about. Instead,
it's the ability to make a DYNAMIC Web Query!
By dynamic, I'm not talking about using "refresh" to update the data. What I mean is the ability
to change the parameters of the web query, such as typing in a new stock symbol into a cell
and having the table update automatically.
If you find yourself using the internet to gather data by filling out forms and copying and pasting
data over and over, an excel web query might be the solution to your monotonous woes. It's
not always going to be better, and it's not always going to work, but it's worth a try, especially
since a simple web query could drastically boost your productivity!
Excel Web Query ".iqy" Files
The real key to creating a dynamic excel web query is to work directly with a ".iqy" file. In it's
basic form, the ".iqy" file is simply a TEXT file with three main lines:
WEB
1
http://www.thedomain.com/script.pl?paramname=value&param2=value2
You can create the file using a simple text editor! You don't even need to know what the first
two lines are for. I knew once, but I've forgotten. I just keep putting them there out of habit.
The second line is the important one! It's simply the URL. Notice that is contains two
parameters. If you don't know what parameters are for, just browse the web for a while and pay
attention to what shows up in the address bar of your browser. Go to Google.com for instance,
and look up "excel web query".
Top of Form

Search Google
excel w eb query

Bottom of Form

Parameter name / value pairs are listed after the "?" in the URL and are separated by an "&".
Make the Web Query Dynamic
To make the query dynamic, simply replace the value of each parameter in the web query file
(queryname.iqy) with:
["paramname","Enter the value for paramname:"]
Want to see how this would apply to a Google search? The form that I used above consists of
HTML code that looks like this:
<form action="http://www.google.com/search">
<input type="text" name="q" value="excel web query">
<input type="submit" value="Search Google">
</form>
Notice that "q" is the name of the parameter, and the action tells you what the URL should be.
The dynamic web query file for a simple google search would look like this:
WEB
1
http://www.google.com/search?q=["keyword","Enter the Search Term:"]

Let's Create a Web Query

1. Open up a text editor and copy the Google example. Save the file as GoogleSearch.iqy
2. Open up Excel and enter a search term in Cell B3
3. Open the web query file you just saved (GoogleSearch.iqy)
Excel 2000: Go to Data --> Get External Data --> Run Saved Query...
Excel XP: Go to Data --> Import External Data --> Import Data ...
4. When prompted for the search term, enter: =B3
If that doesn't work right off, you can edit your web query later, using the External Data
Toolbar (Tools --> Customize ...)

Play around with the web query options and properties (using the External Data Toolbar).
You will find that you need to select a cell inside of the web query before you can click on the
toolbar buttons.
For the Google Search example, try selecting just the portion of the page that tells how many
sites matched your search. If you need a little help, or are just lazy, click here.
When you have the query looking just the way you want it, save the Excel web query (an icon
or button for saving the web query is in the "Edit Query" window. When you save the new
query, your ".iqy" file will include the options you have selected.
Another Example:
Want to try another interesting web query? How about a link to Overture's keyword suggestion
tool? It tells you the popularity of various keywords (in terms of searches per month). Click Here
for the IQY File.
After a little formatting, you can do some fun stuff with the query. The following link is an
example Excel spreadsheet (.xls file) containing the dynamic web query:
Overture Keyword Suggestion Tool Example
Become a Web Query Wiz
Granted, web queries are obviously not the solution to every problem, but when they DO work, it
sure is great fun!
Take a few minutes to think about what mundane or repetitive tasks YOU do on the internet,
particularly cases where you go to the same sites over and over and fill out the same on-line form
again and again. If you find yourself copying and pasting or re-typing information into Excel
so that you can perform calculations with the data, you may have found an ideal use for an
excel web query.
There are two main things that you need in order to become a Web Query Wiz.
1. A working knowledge of Excel formulas. By this, I mean the ability to use text-
manipulation and other formulas. A GREAT book for both learning and reference
(and the one that I use all the time) is John Walkenbach's "Excel Formulas". Go get it
now! If you are an avid Excel user, it will probably be the best investment you will make
for a long time! The only problem I have with the book is that I need TWO copies - one
for work and one for home.
2. A working knowledge of HTML. This is the hard part unless you are a web designer or
programmer. The articles listed in the side bar will help to some extent, but ultimately
this issue was the reason why I didn't write a more detailed tutorial. Something that will
be a great help is to learn about how <form> tags work. Here is my favorite reference.