Refresh Web Data into Excel Every Minute
October 10, 2005
To try this tip on your own computer, download and unzip CFH263.zip.
Do you use Finance.Yahoo.com to look up stock prices throughout the day? That web page even has a link that will allow you to download the data to a spreadsheet.
Or, you might just copy and paste the table to Excel. Either way, you will then have to add columns and formulas to total the value of your portfolio. There is a better way.
With a web query, you can set up a program that will automatically gather up-to-the second refreshed data from the web every time you open the spreadsheet.
To start, use Data - Import External Data - New Web Query.
A mini-browser opens inside of Excel. Initially, it will display your default Internet Explorer home page.
Use the mini-browser to navigate to the web page containing data that you would like to download. In my case, I still had Internet Explorer open, so I simply copied the URL from the address bar in I.E. and pasted into the address bar in the Excel browser. Click Go, the page loads. You will notice that there are many yellow arrows on the page. These arrows point to each table on the page. The Yahoo page has a lot of tables. Some web pages might just have one arrow, to grab the whole page. Click on a yellow arrow to select that table. Once you select the table(s), the yellow arrow(s) turn to green checkmarks.
If desired, press the options button in the Excel browser to change if formatting is imported.
When you click the Import button, the Import Data dialog is displayed. By default, the data will be imported at the current cell pointer location. You can change this. The Properties button contains many important settings. I will show you how to get back to the Properties button in a few steps.
Click the OK button. Intitially, you will see a strange code in cell A1.
After a few seconds, the data from the web page loads into the worksheet.
If you like, you can now add formulas to display your portfolio value.
Move the cell pointer so that it is selecting one of the cells inside the imported data range. This will cause all of the buttons on the External Data toolbar to become enabled. The second button is the Data Range Properties button. When you choose this button, you will see the default settings for the web query.
This is the same dialog that you could have accessed with the Properties button before importing the data. There are many good settings here. In this case, I would suggest:
- Refresh every 1 minute
- Refresh data on file open
- Insert entire rows for new data, clear unused cells
- Fill down Formulas in columns adjacent to Excel data
With these settings, the query will automatically update every 1 minute that the workbook is open.
