roheba
20 posts
msg #54504
- Ignore roheba |
9/2/2007 4:42:22 PM
I'm trying to determine a stock's 10-day historic volatility and use it in one of my filters. Sadly, I'm not getting the same results in SF as I am in Excel. I'd like some help figuring out why.
Volatility is one standard deviation of the natural logarithm of the price change annualized.
I'm assuming 250 trading days in a year, so the factor to annualize the standard deviation is sqrt(250), or 15.8114.
Here's the formula I'm using in SF to give me the 10-day volatility:
standard deviation(10) * 15.8114
For illustration, let's look at the stock SYK. SYK's last 10 day's Close are as follows:
Day Close
8/20/2007 66.86
8/21/2007 66.32
8/22/2007 67.29
8/23/2007 66.95
8/24/2007 67.19
8/27/2007 67.09
8/28/2007 65.75
8/29/2007 66.75
8/30/2007 66.63
8/31/2007 66.80
Using this data in Excel, I get a 10-day volatility of 17.34%. SF is telling me the 10-day volatility of SYK is 6.81%.
I think the problem is with the standard deviation. SF gets 0.43%, Excel gets 1.10%.
For what it's worth,here's the filter I'm playing with:
set{v10,standard deviation(10) * 15.81139}
add column v10
add column standard deviation(10)
sort by col 1
And here's the Excel calculation:
(A) (B) ( C ) (D)
1 Day Close Ln of Change Cell Formula
2 8/20/2007 66.86
3 8/21/2007 66.32 -0.81% <------ =LN(B3/B2)
4 8/22/2007 67.29 1.45%
5 8/23/2007 66.95 -0.51%
6 8/24/2007 67.19 0.36%
7 8/27/2007 67.09 -0.15%
8 8/28/2007 65.75 -2.02%
9 8/29/2007 66.75 1.51%
10 8/30/2007 66.63 -0.18%
11 8/31/2007 66.8 0.25%
12
13 Standard Deviation 1.10% <------ =STDEV(C3:C11)
14
15 Trading days in a year 250
16
17 Sq. rt.of trading days 15.81139<------ =SQRT(C15)
18
19 Annual Volatility 17.34% <------ =C13*C17
(Sorry about the alignment...I think you can get it.)
I'm stumped. Any insight would be appreciated.
|
stocktrader
294 posts
msg #54505
- Ignore stocktrader |
9/2/2007 6:22:48 PM
I'm certain this is "not" what you're looking for:
|
stocktrader
294 posts
msg #54506
- Ignore stocktrader |
9/2/2007 6:40:17 PM
I think you should double check your Excel math....
|
roheba
20 posts
msg #54509
- Ignore roheba |
9/2/2007 8:41:03 PM
Stocktrader,
Thanks for your response.
I'm not sure what your filter does, but it did point out the "Historical Volatility" measure.
I'm new to SF. I looked for and didn't find a "Volatility" measure, but I completely overlooked the "Historical Volatility" measure.
Back to the drawing board.
|
stocktrader
294 posts
msg #54516
- Ignore stocktrader |
9/2/2007 11:39:54 PM
Welcome to SF roheba.
Historical Volatility
|
miko
68 posts
msg #54520
- Ignore miko |
9/3/2007 10:51:07 AM
There seems to be an undocumented third parameter for the number of days in a year to use, with the default 365. If you want to use 250 days (more like the industry standard), you would use Historical Volatility(10,1,250).
|
stocktrader
294 posts
msg #54521
- Ignore stocktrader |
9/3/2007 12:10:10 PM
Thanks miko.
Using 250 gets the volatility to .17
|
stocktrader
294 posts
msg #54522
- Ignore stocktrader |
9/3/2007 1:35:01 PM
|