Spreadsheets and Other Tools

This page is intended to help students make use of Excel spreadsheets to help understand finance, work through problems and complete spreadsheet-related projects. An introduction to spreadsheet use in finance can be linked to below . Several spreadsheet files are provided here for use in Dr. Teall's courses. The data retreival and manipulation files are provided to simplify the process of downloading data from the web and then performing computations. Other files provided here may be useful for students for a variety of purposes. Finally, several links are provided below to other interesting spreadsheets, applications and other aids. Each of Dr. Teall's course web pages will link to spreadsheet files (typically through "Other" in the table of links on the home page for the course) that may be useful for problem solving for that specific course.

Return to B40.3331 Data Retreival and Manipulation Instructions and Assistance Using Spreadsheets
Teaching and Courses
Introduction to VBA
Introductory Spreadsheet Material
Teaching and Courses HOME
Data Retrieval and Manipulation
There are many sources for historical stock price data on the web. One of the most familiar to many students is provided Yahoo . As of October 2001 (this procedure may change), one can easily obtain historical stock price data from Yahoo in spreadsheet format by following steps:

   Link to Yahoo at www.yahoo.com
   Click the Finance link
   Obtain the ticker symbol for the stock. If you don't know it, click the symbol lookup link .
   Enter one or more ticker symbols separated by commas
   Press the Get button
   For the price history, click the "Chart" link
   For a table of prices, go to "Historical Prices"
   Select desired time period and intervals (daily, weekly or monthly)
   For spreadsheet format, click "Download Spreadsheet Format"

For students needing data for a market index, the S&P 500 index is available through Yahoo. The symbol is ^SPC. Price histories for the S&P 500 the same way as for a stock. Information on other indices is available as well through Yahoo. Be aware that the exact procedure for obtaining price histories from Yahoo may evolve over time and also that historical prices are available through many other sites. Also notice as you link through the Yahoo web site that there are numberous other data sets that you can retreive for various companies. For example, you can obtain research reports, accounting statements, SEC filings and news releases through the Yahoo site. There are also a variety of competing sites that accumulate investment information and financial data for large numbers of companies.

One of the more useful spreadsheets for my courses, YahooRetrieve2 , has been provided by Professor Michael Kishinevski, who teaches Portfolio Management courses at Pace. This file enables one to efficiently download stock price data from Yahoo.com in a spreadsheet format. Then this data can be used for spreadsheet computations. Your computer should have Microsoft Excel loaded onto it to use the file. Simply click this YahooRetrieve 2 link, wait for Excel to open YahooRetrieve2, and either save the spreadsheet file or use it by following its instructions. To use the most essential applications of this program, merely click on the HistData Worksheet and fill in stock symbols and dates for which you require prices. More applications are available for the program if you wish. More spreadsheet files are available on Professor Kishinevski's website . For an updated version of this file (or if the one on this page does not work; otherwise, you get can data directly from Yahoo following the instructions above), go to Professor Kishinevski's website .

Instructions and Assistance Using Spreadsheets
Instructions are available here for students who require a review of spreadsheet usage. The Elementary Math Review provides a brief introduction to the use of Excel spreadsheets in the Appendix to Chapter Two . Additional spreadsheet instruction, hints and applications to finance are provided in the appendices to Chapters Three and Four of the Elementary Mathematics Review. After completing these three appendices, additional assistance concerning return and risk can be found in the appendix to Chapter 5 . The appendices in these files, especially Chapter Two , should be studied by any finance student not familiar with the use of Excel. However, these and other spreadsheet instructional material have been packaged in the *.pdf file Using Spreads heets . Again, students who are not familiar with the use of spreadsheets are encouraged to download this file or the appendices referred to above and read them before beginning a spreadsheet-oriented technology project. Use of the files described above require Acrobat Reader .

Students requiring some review for the use of Excel macros are directed to the the Introduction to Visual Basic for Applications (VBA) page . VBA is most useful for performing more complex operations and for making files more user friendly. This page provides some introductory details and some examples. The spreadsheet file vba.xls is intended to provide examples of macro use and may also be downloaded. It is suggested that this file be used with the the Introduction to Visual Basic for Applications page .

There are a few special-purpose spreadsheet illustrations that can be linked to here. One such spreadsheet file, Offest Function , is intended to illustrate the use of the offset function for inverting matrices of varying sizes.  While this process can be easily be accomplished with the use of VBA, some students may find the Offset Function to be a little easier for this specific purpose.

Professor Simon Benninga, currently at the Wharton School, has written a very useful textbook and is preparing a draft for another that may be useful to students preparing finance-oriented spreadsheets. The textbook,  Financial Modeling published by the MIT Press in 1997 presents some applications-oriented finance material with a heavy emphasis on spreadsheet analysis. The book also includes an introduction to VBA. The draft of the manuscript for his second text is available from Professor Benninga's web site .  Users may obtain an ID and Password by contacting Professor Benninga directly. Craig Holden has also compiled a textbook series entitled  Spreadsheet Modeling in the Fundamentals of Investments which was published by Prentice Hall in 2002.

Downloading Adobe Acrobat
Most material on this page is provided in *.pdf format which can be downloaded by selecting the appropriate links. After downloading, you will need to access this material with Adobe Acrobat Reader . If you do not have this program, it can be downloaded without cost by linking to Adobe's site , selecting the "Get Acrobat Reader link" and following instructions. Be cautious about a few mathematics symbols and Greek letters that not convert properly from WP8.0 to *.pdf format. Feel free to contact John Teall to report any difficulties using this page or to obtain any additional assistance.

Teaching and Courses Top HOME
updated 06/02/2007