Microsoft Office Tutorials and References
In Depth Information
Lesson 23: Managing External Data
Suppose you are interested in monitoring the stock prices of a half-dozen or so technology
companies. If you want to avoid the monotony of going to a financial website and entering the same stock
symbols every time, you can automate the process with a Web query, and refresh the data anytime
When you build a Web query, you need to tell Excel what website to extract the information from,
and the cell address on the destination sheet where you want the QueryTable to be located. Some
background information about URLs and their parameters might be helpful for you to understand
what is going on.
If you open your Web browser and enter the URL http://money.cnn.com/quote/quote
and Google. With this URL, you are essentially passing URL parameters that allow you to pass
information such as search criteria to a website. In this case, the URL parameters being used are
the symbols for Yahoo (YHOO) and Google (GOOG).
The following macro places the QueryTable on cell A1, and points to one of the bevy of websites out
there that provide current stock quotes. For demonstration purposes, I chose a few companies that
are all headquartered in Silicon Valley where I live. The stock symbols of those companies are the
criteria that will apply URL parameters through the code to gather the stock quote information that
will populate the QueryTable. Figure 23-1 shows what the result looked like when I ran this macro
in January 2011.
‘Declare variables for destination worksheet,
‘and two halves of the connection string:
‘one half for the URL, and the other half for
‘the quotes, to make it easier for you to edit.
Dim wsDestination As Worksheet
Dim strURL As String, strStocks As String
‘Set the destination worksheet; here it is Sheet2.
Set wsDestination = Worksheets(“Sheet2”)
‘Define the URL for getting your stock quotes.
‘There are many websites where you can do this.
strURL = “http://money.cnn.com/quote/quote.html?symb=”
‘Define your stocks of interest. I only selected these
‘as an example because they are silicon valley businesses
‘near where I live and run my Excel development company.
strStocks = “AAPL,CSCO,EBAY,GOOG,INTC,ORCL,YHOO”
‘My preference is to activate the destination worksheet
‘and select cell A1.
Application.Goto wsDestination.Range(“A1”), True
‘Clear the cells in the worksheet so you know the data
‘being imported will not be confused with other data
‘you may have imported previously and not yet deleted.