WindowsDevCenter.com
oreilly.comSafari Books Online.Conferences.

advertisement


AddThis Social Bookmark Button

Pulling Stock Quotes into Microsoft Excel

by Steven Roman
12/10/2001

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.

Related Reading

VB .NET Language in a NutshellVB .NET Language in a Nutshell
By Steven Roman, Ron Petrusha & Paul Lomax
Table of Contents
Index
Full Description
Read Online -- Safari

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
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

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 utStock:

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.

DOW
9545.17
+82.27
NAS
1768.96
-6.51
0.85%|
212,835,200
-0.37%
9423(174)9598 1763(29)1792
BRCM
37.05
-1.95
COMS
4.40
0.00
-5.56%|
20,944,000
0.%|
1,458,300
36(4)40 4(0)4.5

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

Next Pagearrow