Variance too small

[Bukco13]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

I'm calculating the Variance for monthly return on a stock. So I have downloaded monthly stock info from Yahoo and then made a column that reads like this: (E2-E3)/E3 for the monthly return. When I try to find the Variance of this I get an answer that seems to be 1/10,000th too small. For example, instead of a variance of 122, I get .00122. Any ideas.

Also, is there anyway so that after I have saved and exited a spreadsheet, it will save my functions so if I wanted to play with it when I came back to a saved spreadsheet, I don't have to re-enter the function, which is what I have to do now.

 

[Bob Greenblatt]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

I'm calculating the Variance for monthly return on a stock. So I have
downloaded monthly stock info from Yahoo and then made a column that reads
like this: (E2-E3)/E3 for the monthly return. When I try to find the Variance
of this I get an answer that seems to be 1/10,000th too small. For example,
instead of a variance of 122, I get .00122. Any ideas.

Also, is there anyway so that after I have saved and exited a spreadsheet, it
will save my functions so if I wanted to play with it when I came back to a
saved spreadsheet, I don't have to re-enter the function, which is what I have
to do now.

What do you mean by variance? Are you using an Excel formula? Which one?

Of course, Excel remembers your functions. Are you saving the file as an
Excel workbook? You probably downloaded it as a text file, entered your
formulas and saved it as a text file? Don't do this, save it as an Excel
workbook.

 

[Bukco13]

Ah ha! they were saved as csv, comma separated values, since I had downloaded the spreadsheet from Yahoo and was just working off of those. That fixed the remembering functions issue.

Unfortunately the variance (VAR function) of percentages still isn't coming out right. I worked around it by just multiplying the results by 100 and formatting the cells as Number with 2 decimal places rather than percentage. The variance comes out correctly that way. Still kind of a nuisance though.

 

[Mike Middleton]

Bukco13 -

Some ideas about the variance:

The variance is measured in squared units of the original measure. So, if
you have five returns, e.g., 0.03, 0.04, 0.05, 0.06, 0.07, the sample
variance (VAR) is 0.00025. If the measurment units of the returns are
percents, then the variance is measured in percents squared. If the original
measurements were dollars, the variance is dollars squared.

So, if you need a measure of variation, I suggest using the standard
deviation (the square root of the variance), because it's expressed in the
same units as the original measure. The sample standard deviation (STDEV) of
0.03, 0.04, 0.05, 0.06, 0.07 is 0.0158. If the original values are 3%, 4%,
5%, 6%, 7%, the sample standard deviation is 1.58%.

- Mike Middleton
http://www.DecisionToolworks.com
Decision Analysis Add-ins for Excel