Welcome to the web's most helpful Online Free computer tips and tricks website. This site contains all Latest and Cool Computer tricks as well as some Internet Tricks, Registry tricks, Orkut tricks, Windows customize and Optimization tricks, Computer Tweaks. Solve your computer problems by browsing the website for related Computer solution. All tips work well in both Windows XP as well as windows vista or win 7 operating systems. Need some Computer Help? Then this website suites your must need. These windows and internet Hacks Tricks are safe and free from any Spy ware. Learn All Basic and Advanced computer tips and tricks and be a computer master. All the computer tips at Hack2011 are very interesting and are mainly meant for education purpose. Visit my new Computer Tips Blog and leave your comments and suggestions there. Enjoy your stay and read all the articles posted here.


How to Use Live External Web Data in Excel 2010

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
From Web Icon

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:
Yahoo Finance Page
…to get this page:
HP Stats 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
Arrow Boxes
To choose one of them, simply click on the arrow; since we want current stock price, we click on the one that holds that:
HP Data Selected
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:
Import Data Popup
Click on the Properties button to get this popup:
External Data Properties
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:
Imported Data in Sheet
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:
Formatted Data
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:
Refresh All
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