Excel Stock Sheets

Document Sample
Excel Stock Sheets Powered By Docstoc
					Accessing Financial Data on the Web Using
      Excel Web Queries and VBA
          (Excel 2003 Version)



                              By
                        David S. Allen
                Associate Professor of Finance
             The W. A. Franke College of Business
                 Northern Arizona University
                    david.allen@nau.edu

                         Presented at:
                Financial Education Association
                     September 28, 2007


              Data used in the paper is available at:
          http://www.cba.nau.edu/allen-d/FEA/FEA.htm
Abstract

The Excel spreadsheet can be used to access financial data from a variety of web sites. This paper
shows how to use two Excel features: web queries and VBA, to collect and process data that can be used
for class assignments and research projects.


Introduction

There are numerous web sites that allow users to view financial data on publicly owned firms. However,
saving this data for future use typically involves a cut-and-paste from the web browser to Excel




1caa5130-8134-4d28-abb4-4ad815c2ed3b.doc                                                                 1
Web Browser Captures

Many web sites use tables to format and present financial data such as historical stock prices and
financial statements. A table is similar to a spreadsheet in its use of rows, columns, and cells to hold the
data items. Microsoft’s Internet Explorer web browser has a built-in feature for capturing data from tables
on web pages and pasting it into an Excel spreadsheet. It actually uses the web query technique we will
discuss later. For now, it represents the simplest introduction to the concept and technique.

Example-1

The following link will present the income statement for Microsoft Corporation as reported by by
Zacks.com: http://www.zacks.com/research/report.php?type=ais&t=msft

The web browser will display the data as shown in Figure 1. To capture and paste it to Excel, simply
right-click with the mouse on the data table of interest and select “Export to Microsoft Excel” as indicated.

Figure 1.




The data will appear in Excel as shown in Figure 2.


1caa5130-8134-4d28-abb4-4ad815c2ed3b.doc                                                                    2
Figure 2.




In this case, Excel did not retain the formatting of the original table. We can fix this and update our table
by right-clicking on any cell in the spreadsheet containing data and selecting “Edit Query” as shown in
Figure 3.

Figure 3.




1caa5130-8134-4d28-abb4-4ad815c2ed3b.doc                                                                        3
A small web browser will open (see Figure 4). Click the options button in the upper right corner, and then
click the “Full HTML formatting” button and “OK.” Click the “Import” button and the spreadsheet will
refresh the data with the original formatting as seen in Figure 5.

Figure 4.




1caa5130-8134-4d28-abb4-4ad815c2ed3b.doc                                                                 4
Figure 5.




1caa5130-8134-4d28-abb4-4ad815c2ed3b.doc   5
You can also edit your query using the query toolbar. If it is not already visible, make it so by clicking on
“View” “Toolbars” “External Data” as seen in Figure 6.

Figure 6.




The toolbar can be dragged to any convenient location. The icons on the toolbar can be used to edit the
query as before, or to refresh the data in the table with the most recent data on the associated web site.


VBA for Automated Data Retrieval

VBA (Visual Basic for Applications) is a programming language built in to Excel that can be used to
automate the collection of financial (or other) data from the Internet. We will show by example how to
collect historical stock prices from the Yahoo’s quote server ( chart.yahoo.com )

We begin by creating a simple worksheet with input cells that will be used to specify the ticker symbol and
a start and end date for our quotes. We also use the “Control Toolbox” to place a button on the page to
click in order to retrieve the quotes. The control toolbox is made visible as shown in Figure 7.

Figure 7.




1caa5130-8134-4d28-abb4-4ad815c2ed3b.doc                                                                        6
Figure 8 shows the Control Toolbox and the button tool used to place the “Get Quotes” button on the
page. We must be in “Design Mode” in order to place the button on the page. Click the icon indicated to
enter design mode, then click-and-drag to place the button.

Figure 8.




The properties of the button can be changed by right-clicking it and entering the desired values in the
Properties box as seen in Figure 9.

Figure 9.




1caa5130-8134-4d28-abb4-4ad815c2ed3b.doc                                                                  7
Double-click the “Get Quotes” button and type a subroutine to call module with the code to retrieve the
quotes.

Figure 9.




Next, insert a module with the code.

Figure 10.




A portion of the code is shown below. The full code is available in Appendix A.




1caa5130-8134-4d28-abb4-4ad815c2ed3b.doc                                                                  8
Figure 11.




Save the file, return to the worksheet, and exit Design Mode as seen in Figure 12.

Figure 12.




Enter the desired ticker and dates, click the “Get Quotes” button and the VBA code will retrieve the data.
A portion of the retrieved data is shown in Figure 13.




1caa5130-8134-4d28-abb4-4ad815c2ed3b.doc                                                                     9
Figure 13.




The a file containing VBA is re-opened, Excel may provide the warning seen in Figure 14.

Figure 14.




You must click the Enable Macros button for the VBA code to execute.

Conclusion

Web queries and VBA greatly enhance the ability to retrieve data from various Internet sites. VBA can be
used to automate the process and make further calculations based on the data acquired.




1caa5130-8134-4d28-abb4-4ad815c2ed3b.doc                                                             10
Appendix A

Public Sub fetchData()

   On Error GoTo Errorhandler
   Dim symbol, bmonth, bday, byear, emonth, eday, eyear, counter, columncount As Integer
   Dim wst As Worksheet
   Dim sheetExists As Boolean

   symbol = Range("B3").Value
   bmonth = Month(Range("b5").Value)
   bday = Day(Range("b5").Value)
   byear = Year(Range("b5").Value)

   emonth = Month(Range("b7").Value)
   eday = Day(Range("b7").Value)
   eyear = Year(Range("b7").Value)

'Get the stock data
'-----------------------------------------------------------------------------------------------------
    Sheets.Add
    ActiveSheet.Name = "temp"
    Sheets("temp").Select

    varconnection = "http://chart.yahoo.com/table.csv?a=" & bmonth - 1 & "&b=" & bday & "&c=" & byear
& "&d=" & emonth - 1 & "&e=" & eday & "&f=" & eyear & "&g=d&q=q&s=" & symbol & "&y=0&x=.csv"

      With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;" & varconnection, Destination:=Range( _
        "A1"))
        .FieldNames = False
        .RefreshStyle = xlInsertDeleteCells
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .RefreshOnFileOpen = False
        .HasAutoFormat = True
        .BackgroundQuery = True
        .TablesOnlyFromHTML = True
        .Refresh BackgroundQuery:=False
        .SavePassword = False
        .SaveData = True
      End With

   Sheets("temp").Columns("A").Select
   Selection.TextToColumns DataType:=xlDelimited, ConsecutiveDelimiter:=True, Comma:=True


   Sheets("temp").Columns("B:F").Select
   Application.CutCopyMode = False
   Selection.Delete Shift:=xlToLeft
   Range("A1").Activate

   Sheets("temp").Select
   If Cells(2, 1).Value = "" Then
      Sheets("temp").Select
      ActiveWindow.SelectedSheets.Delete

1caa5130-8134-4d28-abb4-4ad815c2ed3b.doc                                                                 11
      Sheets(symbol & " historical stock quotes").Delete
      MsgBox (symbol & " appears to be an invalid ticker symbol. Please double check that you've got a
valid ticker symbol and try again.")
      GoTo Nevermind
  Else
  End If

  Worksheets("temp").Range("A1:B1500").Copy
  ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range("A12")

  Sheets("Sheet1").Select
  Range("B12").Value = symbol

  Application.DisplayAlerts = False
  Sheets("temp").Select
  ActiveWindow.SelectedSheets.Delete

  Sheets("Sheet1").Select
  Cells(1, 1).Select
  Application.DisplayAlerts = True


Nevermind:

Errorhandler:
Resume Next

End Sub




1caa5130-8134-4d28-abb4-4ad815c2ed3b.doc                                                             12

				
DOCUMENT INFO
Description: Excel Stock Sheets document sample