Import Real-Time Data from the Web with Excel 2007

Excel 2007's ability to import, automatically update, and refresh (live) any data from the Web is one of its coolest features. This is made possible by Web Queries. Web Queries are links that auto-update as the data they grab from the web changes. Excel 2007 takes advantage of this but this works only if the data is in a table on the web. If the data is not in a table it will not work. Here is how to do the trick :

Create a worksheet and select the cell on which you would like the data to appear. Click the data tab and from the External Data group click "From web"(See the image below) to make the Web Query dialog box appear.


Copy the URL of the page that contains the data you would like to import into your worksheet, paste it on the Address box and click "Go". The page will be loaded into the dialog box and every table on the page will have a small yellow arrow next to it. Hold your arrow over any table, and the arrow turns green, and you’ll see all the data that you can import if you select that table. Click the arrows of the table or tables whose data you want to import. The arrows will turn green.You may choose to retain the formatting of the data from the web page by clicking options button and selecting Rich Text Formatting Only. If you select Full HTML Formatting option, all the table properties will be preserved.Do not click the Options button if you do not want any formatting to happen.Click the Import button. You may select a new location in your worksheet to place the data from the Import Data dialog box that appears, or you may keep the currently selected location. Next click the OK button. After a few seconds the data will be imported into your worksheet.To have Excel automatically update the data, put your cursor inside the table you want to update, select Properties from the Connections group on the Data tab, and from the form that appears check the box next to “Refresh every…” Then select how often you want the data updated and click OK. The data will now be updated on the schedule you set.

If you import multiple tables into a single worksheet, there’s a simple way to set how often you want to update each table without having to click each one to customize it. On the Data tab, click Connections in the Connections group. The Workbook Connections dialog box appears, and lists every imported table on the page. Highlight any, click Properties, and you’ll come to the dialog box pictured in Figure 9-13. From here, you can customize how frequently the table should be updated.

Related Posts :