Pulling Stock Quotes into Microsoft Excelby Steven Roman
Microsoft has spent a lot of time and effort trying to convince us to place Office documents on our Web sites -- witness the Microsoft Office 2000 Web Components, which are ActiveX controls designed to bring spreadsheet (Excel) and database (Access) functionality to a Web site. Whole books have been written about these Web components.
But Microsoft has spent much less effort pushing the tools they have created for going in the other direction -- placing the Web into our Office documents. This article shows one application for doing just that: importing stock share prices and indexes into an Excel spreadsheet. This is something I decided to do recently to keep track of my meager financial assets -- a few stocks, some bonds, and, of course, the usual savings/checking accounts. I have used MyYahoo to keep track of stocks, but it does not provide the range of formatting possibilities that Excel does, and I cannot do such things as compute my net worth (and I use the term a bit laughingly).
The problem, of course, is how to get updated stock data into an Excel worksheet automatically, either at regular intervals or with the click of a button. Not only do I want the current (read: delayed) price of my stocks (as well as other data such as the current change in price), but I also want historical stock prices so I can chart week-long, month-long or year-long performance for the stocks.
I devised a two-fold solution that uses Web queries and the WebBrowser control. It is a simple matter to set up a Web query that will retrieve stock information from Microsoft's MSN Web site. With a little more effort, we can use the WebBrowser control to get historical stock prices from, say, Yahoo. Of course, Excel excels at creating the desired performance charts.
Figure 1 shows the main part of my Excel application, simplified for this discussion (the stock list is fabricated and I actually have three charts -- one for 7-day performance, one for 30-day performance, and one for 180-day performance.
|Figure 1. As the active cell moves from row to row, the chart on the right changes to reflect the stock associated with that row.|
If you are interested in creating a similar application, read on.
The application consists of four worksheets.
- The Activity worksheet, shown above, holds the main stock data.
- The LookupInfo worksheet lists the stock symbols to look up. Here is a sample:
Symbol Name MSN Symbol CSCO Cisco Systems, Inc. DELL DELL Computer Corporation IBM International Business Machines Corporation INTC Intel Corporation MSFT Microsoft Corporation DOW Dow Jones Industrial Average Index $INDU NASDAQ Nasdaq Combined Composite Index $COMPX RUS2000 Russell 2000 Stock Index $IUX
We need this sheet for two reasons. First, we need to associate each stock symbol with the company name exactly as MSN knows it -- for example, "Cisco Systems, Inc." rather than just Cisco Systems. Although the Web query accepts stock symbols in its URL, it does not return those symbols. It returns only the full company name (along with the stock data, of course). So we need to have the exact name in order to find the information corresponding to a given stock symbol. (These names, by the way, can be found by just looking at the returned data in the Web query.)
In addition, MSN uses rather unintuitive symbols for stock indices, such as $IUX for the Russell 2000 index, whereas I would prefer something like RUS2000 in my main spreadsheet.
- The WebQuery worksheet contains the return data from the Web query. Once the data is returned from the query, we can use the Excel object model to get the data and place it into a global array for subsequent use, as described later.
- The Historical worksheet holds a WebBrowser control that browses Yahoo to get historical price information on stocks.
The general plan for the application is simple. Whenever the user clicks on a button to get stock info, we create a new Web query file and execute it, using the
Add method of the
QueryTables property of the WebQuery worksheet:
With Worksheets(conWebQuerySheet).QueryTables.Add(Connection:= _ "FINDER;" & sWebQueryFQFile, _ Destination:=Worksheets(conWebQuerySheet).Range("A1")) .Name = "Microsoft Investor Stock Quotes" End With
(There are some additional properties that you will find in the actual code.)
Once the Web query is complete, we can glean the required information from the WebQuery worksheet and fill a global array of type
Private Type utStocks Symbol As String MSNSymbol As String ' For indices, this differs from common symbol ' Data from table Name As String Price As String Change As String Volume As String High As String Low As String PrevClose As String YearHigh As String YearLow As String PERatio As String MarketCap As String EarningsPerShare As String SharesOut As String ' Computed ChangeSingle As Single PercentChanged As String ChangeIsNumeric As Boolean End Type
Once we have filled the array, we can do whatever we like with the data, including placing it on the Activity worksheet. You might also want to create a ticker-like display, as shown below, or do other things with the data.
As for historical data, this requires a bit more work. I am not aware of any Web queries that will retrieve such data, so we need to use a more brute-force approach. In particular, we use a WebBrowser control to browse to the appropriate URL at Yahoo, and then use a portion of the DHTML object model to pick apart the returned document and extract the historical prices.
Let's look at the details.
Pages: 1, 2