Technical Notes: Web to Excel


Transferring live data from a Web Site to an Excel Spreadsheet

There are two ways you can feed live data from a web page into Excel. You can make menu and dialogue box selections in Excel or 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.
      Importing live web data into Excel
  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

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 "Microlink Measurement".

If you searched on Google for "Microlink Measurement", the URL of the search results might look like this
http://www.google.co.uk/search?q=microlink+measurement
If you used this as your Web Query URL, the search results for "Microlink Measurement" 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.

More Excel Tips are on the Windmill Software site.


Microlink Measurement and Control Systems HomeHome
Data acquisition and control IndexIndex
Technical Notes

Measurement News Feed (RSS)
Copyright Biodata Ltd
10 Stocks Street, Manchester, M8 8QG, UK
Tel: +44 161-834 6688
Fax: +44 161-833 2190
Email: [email protected]