Oftentimes, users of Excel find they’d like to be able to import data from an external source such as a web page, this is particularly true of web sites that list stock information. What many people don’t know is that Excel does have a facility for doing just this.
To see how it works, open Excel to a new blank document, then click somewhere in your sheet to make it your current document, then click on the Data tab on the main ribbon
Next, click the From Web icon on the top left of screen; your default browser should launch with your default web page. Use your browser tools to maneuver to the page you want to gather information from; in this example, we’re going to use the Yahoo finance page to collect stock prices for Hewlet-Packard. First we type in the stock symbol for Hewlet-Packard, which is HPQ:
…to get this page:
Now, notice that there are little yellow boxes with black arrows in them placed strategically around the page, these are put there by Excel and your browser to indicate data tables that are available for capture
To choose one of them, simply click on the arrow; since we want current stock price, we click on the one that holds that:
Note: You can click on more than one arrow to gather information from multiple tables if you like.
Then at the very bottom of the open window, there is a button that says Import, clicking on it brings up this this little popup:
Click on the Properties button to get this popup:
Click whatever options will best suit your application, most notably the one that says Refresh data when opening the file, then use the maneuvering tool to highlight where in your worksheet you want your data to reside, then click on OK button, and after a moment, you should have your data:
Here all of the data from the web page is displayed.
You can also put a title above it so you’ll know what it’s about, and format the data using your normal Excel formatting tools:
And finally, if you want the data to be current you can update it by clicking the Refresh All button on the same Data tab whenever you want your sheet to go get fresh data:
And just like that, your data will be as fresh as it is on the web page you’ve selected.
You can use this same technique to retrieve and use live data from any web page that has recognizable table data, for use in whatever Excel project you’re working on.
To see how it works, open Excel to a new blank document, then click somewhere in your sheet to make it your current document, then click on the Data tab on the main ribbon
Next, click the From Web icon on the top left of screen; your default browser should launch with your default web page. Use your browser tools to maneuver to the page you want to gather information from; in this example, we’re going to use the Yahoo finance page to collect stock prices for Hewlet-Packard. First we type in the stock symbol for Hewlet-Packard, which is HPQ:
…to get this page:
Now, notice that there are little yellow boxes with black arrows in them placed strategically around the page, these are put there by Excel and your browser to indicate data tables that are available for capture
To choose one of them, simply click on the arrow; since we want current stock price, we click on the one that holds that:
Note: You can click on more than one arrow to gather information from multiple tables if you like.
Then at the very bottom of the open window, there is a button that says Import, clicking on it brings up this this little popup:
Click on the Properties button to get this popup:
Click whatever options will best suit your application, most notably the one that says Refresh data when opening the file, then use the maneuvering tool to highlight where in your worksheet you want your data to reside, then click on OK button, and after a moment, you should have your data:
Here all of the data from the web page is displayed.
You can also put a title above it so you’ll know what it’s about, and format the data using your normal Excel formatting tools:
And finally, if you want the data to be current you can update it by clicking the Refresh All button on the same Data tab whenever you want your sheet to go get fresh data:
And just like that, your data will be as fresh as it is on the web page you’ve selected.
You can use this same technique to retrieve and use live data from any web page that has recognizable table data, for use in whatever Excel project you’re working on.
No comments:
Post a Comment