With a WebQuery you can update your data frequently. It comes in handy that the always present HTML tables work pretty good together with MSXL. What we want to do in this post is localize the data to import and then set up our MSXL sheet including the dynamic actualization. Now let's start.
- Head over to Yahoo Finance. We want to import the Dow Jones Industrial Average table.
- In MSXL go to Data > Import External Data > New WebQuery. This will open a new window, similar to an internal browser. Head over to your favorite browser, by the way you IE users, you will get an extra bonus at the end of this post.
- Copy the webaddress of the Yahoo Finance website, which you can also find here.
- In the Excel-internal browser simply paste webaddress into the address bar and click the Go button. You will notice that the browser gets the requested site and marks all tables with little yellow arrows. If you hover over these little arrows, the to be selected table will highlight in a blue frame. By the way, if the browser window is to small, click and drag the lower right corner of it to enlarge.
- Click the little yellow arrow shown in the picture below which makes it then turn into a green check mark.
- If you want to keep the formatting of the Yahoo Finance webpage, please click the Options button in the top right. This opens a context menu, where you can select the kind of formatting you want import. For this table, we will check Full HTML Formatting. Click OK in this little menu.
- Back in the MSXL browser click Import in the bottom right. You'll get a dialog box called Import Data. Here you can select if you want to import the data into the existing spreadsheet or if you want a new one. In this example we will import into the existing one as it is "clean and fresh".
- Click Properties Here we can define - you guessed it - several (external data range) properties of our dynamic import. We are interested in the refresh rate. We want our table to refresh every 10 minutes, so check the box for Refresh every and then enter "10" in the minutes box.
- Back in the Import Data Dialog box click OK to start the import.
There we have it, our data from Yahoo Finance and it will be updated every 10 minutes. So, IE users, tired of all this people yelling at you that you still use Microsoft's browser? Finally, I have some small advantage for you 
If you are using MS Internet Explorer (newer than version 4) there is a nice workaround in importing tabular data from webpages into MSXL.
- Look for the table to import, but make sure not to select anything. In this case it is the headings row starting with the word "Symbol".
- Right-Click on your the word you have identified in the previous step and from the upcoming context menu select Export to Microsoft Excel.
Boom, there you have it ;-) The above mentioned process for non-IE users has to be reversed here a little bit, as we must change the formatting and the refresh-rate AFTER we have imported the data. But that is no problem.
- After you have imported the data from Yahoo, you will notice that there is a little floating window with seven icons in your interface, labeled External Data. Click the second icon (Data Range Properties) and enter the refresh rate as mentioned in step 8 above.
- To achieve the same formatting as on Yahoo (see step 6 above) you have to click the first icon called Edit Query. The internal browser opens and you can follow step 6 above. When you're finished, click import and the data is formatted the "Yahoo way". Please note, that importing this way will always result in a new file!
Although this is a very cool way to import dynamic data to your table for e. g. drawing a stock chart or adding additional data to your existing spreadsheets, please be warned: This technique does not work on every webpage. I have experienced, that although it seems to be a regular HTML table on several websites it is not possible to import the data. You get an import error message which states that there is no data to import. Searching the MS knowledgebase for an answer, Ionly found some password related issues, which may not always be helpful. Maybe you have a solution on that problem? Leave a comment and tell it to the community. 
Do you like this technique? What kind of data will you import? What will you use the WebQuery function for? Join by commenting and share you experiences and examples.
Discuss this blog post in the forums (click the blue button)
Comments
thanks for your comment. As the table from Yahoo! Finance has some strange formatting (i. e. it shows the value per share and the last trade date) it’s not possible to make a chart of it. One solution could be to separate the last tarde date from the value with the following approach (assuming that the value + trade date are in column C):
1. Insert a new column directly right beside column C
2. Insert =(LEFT(C2,6))*1 – This returns this first 6 characters (be careful, we have a value of more than 100 in the table, therefore 6 characters) and – because MSXL saves the original content as text – multiplies it by 1 to make it a number which you can use then in your chart.
3. Apply any chart type you like, i.e. a bar chart with the names of the companies to compare the value per share for each company.
Hope that helps.
I will update the post in the next days as mentioned here in the comment.
Recently,I found an excellent online store, the XX are completely various, good quality and cheap price,it’s worth buying! www.alliask.net/
RSS feed for comments to this post.