StockFetcher Forums · General Discussion · Historic volatility<< >>Post Follow-up
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
293 posts
msg #54505
Ignore stocktrader
9/2/2007 6:22:48 PM

I'm certain this is "not" what you're looking for:

Fetcher[set{volref, Historical Volatility(10,1) / 100}
set{x1,count(open equals ind(syk, open),1) * count(volume equals ind(syk, volume),1)}

x1 equal 1

add column volref
]



stocktrader
293 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
293 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
293 posts
msg #54521
Ignore stocktrader
9/3/2007 12:10:10 PM

Thanks miko.

Using 250 gets the volatility to .17

Fetcher[set{volref, Historical Volatility(10,1,250) / 100}
set{x1,count(open equals ind(syk, open),1) * count(volume equals ind(syk, volume),1)}

x1 equal 1

add column volref
]



stocktrader
293 posts
msg #54522
Ignore stocktrader
9/3/2007 1:35:01 PM

Fetcher[Close above 1
volume above 1000000

set{HV10, Historical Volatility(10,1,250) / 100}

HV10 above 0.80

add column HV10

sort column 5 descending
]



StockFetcher Forums · General Discussion · Historic volatility<< >>Post Follow-up

*** Disclaimer *** StockFetcher.com does not endorse or suggest any of the securities which are returned in any of the searches or filters. They are provided purely for informational and research purposes. StockFetcher.com does not recommend particular securities. StockFetcher.com, Vestyl Software, L.L.C. and involved content providers shall not be liable for any errors or delays in the content, or for any actions taken based on the content.


Copyright 2016 - Vestyl Software L.L.C.Terms of Service | License | Questions or comments? Contact Us
EOD Data sources: DDFPlus & CSI Data Quotes delayed during active market hours. Delay times are at least 15 mins for NASDAQ, 20 mins for NYSE and Amex. Delayed intraday data provided by DDFPlus