Real World via Excel
So, how many websites do you visit at least daily just to get the latest set of numbers? These sites can contain important data like the stats from last night’s game or the latest sales numbers. Once you get these numbers, you then have to manually enter them into a spreadsheet to produce your reports.
Well, Excel has a little known feature that will automatically retrieve data from any web page you can access. This will allow you to refresh the current spreadsheet and have Excel connect to the web page and get the data for you.
Let’s put together a sample spreadsheet to see how this works. Go to the site website http://www.nasdaq.com/markets/ . This is the official NASDAQ website where we can see how all the Markets are doing. Now that we know where the data is located, let’s start Excel.
NOTE: Although all the references are for Excel 2010, the process is valid in 2007 and 2003.
Under the “Data” tab you will find a “From Web” icon
Clicking on “From Web” will open a new window (your default web browser on your home page).
Enter the address to the website (see above) and click “Go”. Sometimes you will get a scripting error, just click yes. Scroll down until you see the data you want to import. There should be a yellow arrow next to the data.
If you are not getting a yellow next to the data you want you will have to import the entire page by clicking on the yellow arrow at the top of the page. When you click on the arrow, it puts a box around the data it will import and a green check box next it. Now, press “Import” and place the data in your spreadsheet.
Now that you have the data you want, just press “Refresh All” when you want to import the latest data.
In later blogs we will talk about what to do with your data.