Historical Stock Data by rkf23660


More Info
									Accessing Financial Data on the Web Using
      Excel Web Queries and VBA
          (Excel 2003 Version)

                        David S. Allen
                Associate Professor of Finance
             The W. A. Franke College of Business
                 Nort hern Arizona University

                          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

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 proc ess data that can be used
for class assignments and research projects.


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

bbfb5609-4eb8-4b41-9503-8bf4fdc0d861.doc                                                                 1
Web Browser Capture s

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.


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 capt ure 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.

bbfb5609-4eb8-4b41-9503-8bf4fdc0d861.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.

bbfb5609-4eb8-4b41-9503-8bf4fdc0d861.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
refres h the data with the original formatting as seen in Figure 5.

Figure 4.

bbfb5609-4eb8-4b41-9503-8bf4fdc0d861.doc                                                                 4
Figure 5.

bbfb5609-4eb8-4b41-9503-8bf4fdc0d861.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 con venient 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
automat e 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 toolb ox is made visible as shown in Figure 7.

Figure 7.

bbfb5609-4eb8-4b41-9503-8bf4fdc0d861.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 indicat ed 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.

bbfb5609-4eb8-4b41-9503-8bf4fdc0d861.doc                                                                   7
Double-click the “Get Quot es” button and type a subroutine to call module with the code to retrieve the

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.

bbfb5609-4eb8-4b41-9503-8bf4fdc0d861.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.

bbfb5609-4eb8-4b41-9503-8bf4fdc0d861.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 execut e.


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.

bbfb5609-4eb8-4b41-9503-8bf4fdc0d861.doc                                                             10
Appendix A

Public Sub fetchDat a()

   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
    ActiveSheet.Name = "temp"

     varconnection = "http://chart.yahoo.com/table.cs v?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 ActiveS heet.QueryTables.Add(Connection:= _
        "URL;" & varconnection, Destination:=Range( _
        .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

   Selection.Text ToColumns DataTy pe:=xlDelimited, ConsecutiveDelimiter:= True, Comma:= True

   Application.CutCopyMode = False
   Selection.Delete Shift:=xlToLeft

   If Cells(2, 1).Value = "" Then

bbfb5609-4eb8-4b41-9503-8bf4fdc0d861.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
   End If

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

  Range("B12").Value = symbol

  Application.DisplayAlerts = False
  ActiveWindow.SelectedS heets.Delete

  Cells(1, 1).Select
  Application.DisplayAlerts = True


Resume Next

End Sub

bbfb5609-4eb8-4b41-9503-8bf4fdc0d861.doc                                                             12

To top