It is often necessary to collect data from web-based sources due to updates and other modifications. An example of a web-based data list would be a list of currency conversion amounts. It is possible to go to the Web, find the currency that you need and to record the information manually; however, due to daily updates and multiple currency conversions, the process can be costly and time-consuming.
In this recipe, you will learn how to import data from the Web. While the steps used to perform this data import are native to Excel, this recipe has been included within the building on section due to the external use of data and the new functionality of this process within Excel 2007 and Excel 2010.
We will begin in the worksheet that you plan to use for the data import:
- Using the Excel toolbar go to Data | Get External Data | From Web:
The window that opens will be similar to the Internet Explorer internet browser.
- In the address bar, enter the web address of the location of the data that you wish to import. In this instance the address is http://moneycentral.msn.com/investor/market/exchangerates.aspx for the MSN currency chart; then click on Go...
- Scroll down within the browser window to the beginning of the currency table:
You will see a yellow arrow just above the data table to be imported.
- Click on the yellow arrow for the currency rates table, and click on Import:
- In the import data window that appears, ensure the data will begin in the cell you have designated, in this case A1, and then click on Properties.
- In the properties window, select the refresh checkbox and set the refresh to occur every 60 minutes.
This will allow our data sheet to remain current with any updates that may occur.
- Click on OK.
Excel has now added the data table of the currency rates to the Excel worksheet, where we can use formulas to update the other data. The data imported will also refresh every 60 minutes to ensure that our data is always accurate and reflects the latest changes.
Excel utilizes a web query to gather information from data sources over the Internet. When setting the query, we pointed the Excel browser to the Internet's location that contained the data we wished to import, and Excel recognized the HTML code denoting a table within the page.
Setting the refresh to 60 minutes allows continuous updates of the data and ensures accurate formulas. Excel then imports the HTML table into Excel, and strips away all of the HTML code that makes the information viewable over the Internet.