Financial Analysis Risk VS Return

I just recently took a course at the University of Nebraska Lincoln called Math in the City.  It is a problem based course where our group looked at stock analysis and Modern Portfolio Theory.  It started out slow, but at the end of the course everything fell together.  What I gleaned from the course that can very useful for high school students in the teaching of spread and central tendency was a fabulous, easily approachable way to intelligently compare stocks using Google docs spreadsheets.

There is a set of code in Google docs that can be used to retrieve daily data from the New York Stock Exchange.  Type this set of code into the first cell of your Google spreadsheet. =googlefinance(“Stock Symbol”, “type of Data”, “Beginning Date”, “Ending Date”) For example, in the photo below, I set up the formula to read Disney’s closing price from 5/10/2007 to 5/9/2012.  The spreadsheet then displays all closing bell prices for Disney between and including those dates.  With this data it is then possible to generate the daily rate of growth for that stock.  Using the same rate of change formula I teach to eighth graders which is Rate of growth =(new – original)/original.  In the photo above I used = if(b5<>””,(b5-b4)/b4,””) Thanks Jerel.  Then you find the mean of this daily return rate along with the variance (which represents the risk of the stock) and plot these two numbers on an x-y scatter where return rate is the y – axis and variance is the x-axis.   The graph with stock data is plotted below and right.  For example it would be easy to see in this graphic that IBM has a high positive return with low risk, while XIDE has a negative return with a high risk based on five years of data.  (This is for educational purposes only, and is not intended as investment advice.) Here is the actual spreadsheet Just change the symbols in each sheet and the dates.

In the course we went into more detail about the efficient frontier, and comparing stock portfolios where we calculated the rate of investment for each stock to maximize return and minimize risk, but this just seamed like an easily accessible application for almost every student I teach in 10th – 12th grade.  You can only look at the document right now.  As soon as I figure out how to protect all the math stuff I will allow anyone to enter the stocks they want to analyze.


Leave a Reply

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload the CAPTCHA.