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.
- Open a worksheet in Excel.
- From the Data menu depending on version of Excel select Get & Transform Data > From Web (eg in Excel 2016) or Get External Data (eg in 2000) or Import External Data (eg in XP).
- In Excel 2016:
- Fill in the URL of the web page with your desired data table. Excel will connect.
- Choose a table and select Load.
Strain gauge
- In Excel 2000:
- Select New Web Query.
- Fill in this dialogue with the URL of the web page, the table of data you want and the formatting you want to keep.
- In Excel XP:
- Select New Web Query.
- 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.
- Choose how often you want to refresh the data. In Excel 2016, From the Data menu select Queries & Connections. Right-click your table and select Properties then choose how often to refresh. In Excel 2000, 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,
- 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.
- 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:
- In Excel, from the Data menu select Import External Data.
- Choose an .iqy file then click the Parameters button.
- 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 and Tricks
- Our free Monitor newsletter (ISSN 1472-0221) features a series of Excel Corners, giving hints and tips on using Excel. To subscribe to Monitor fill in your e-mail below.
- For information on charting with Excel, see our Excel Charting Tips
- For more tips on using Excel, see our Excel Tips and Tricks page.
