Dividend Adjustment and Volatility Calculation using Fidelity/Yahoo Data against ETFReply Result
Author: crazyjava
Creation Date: 4/5/2014 6:39 AM
profile picture

crazyjava

#1
I am using WL to implement ETF rotation based on return and volatility as suggested by ETFReply. So I am bench marking my result with ETFReply site.
Return is using dividend adjusted data. Volatility is using pre-dividend-adjusted data.

First case below - Fidelity may miss 11/28 data.
Second case below - Yahoo data agrees more with EtfReply result than Fidelity data.

Please help me look into the code and fidelity data. In addition, I can make return very close to EtfReply result. But I could not make volatility exactly same as EtfReply's. Please provide suggestion on other possible parameters to calculate (raw) volatility.

Case 1
Period DFE-return SPY-return DFE-Volatility SPY-Volatility (last month)
11/26/2008-12/31/2008 DFE 9.78 % 2.26 % 45.8 % 43.6 % Fidelity (no 11/28 data?)
11/28/2008-12/31/2008 DFE 9.78 % 0.99 % 45.8 % 46.2 % Yahoo
Nov 28, 2008 Dec 31, 2008 DFE +8.53 % +0.98 % 50.5 % 46.0 % EtfReply

Case 2
5/29/2009-6/30/2009 DFE -2.36 % -0.63 % 37.7 % 20.7 % Fidelity
5/29/2009-6/30/2009 DFE -2.35 % -0.06 % 36.9 % 19.9 % Yahoo
May 29, 2009 Jun 30, 2009 DFE -2.43 % -0.07 % 32.3 % 19.9 % EtfReply


Full Yahoo Result:
12/29/2006-12/29/2006 DFE 0.00 % 0.00 % 0.0 % 0.0 % 44.16 121.64
12/29/2006-1/31/2007 DFE 2.57 % 1.50 % 8.1 % 8.0 % 45.30 123.47
1/31/2007-2/28/2007 DFE -0.52 % -1.96 % 26.7 % 16.1 % 45.06 121.05
2/28/2007-3/30/2007 DFE 5.72 % 1.16 % 26.0 % 14.0 % 47.64 122.45
3/30/2007-4/30/2007 DFE 4.79 % 4.43 % 9.1 % 7.7 % 49.92 127.88
4/30/2007-5/31/2007 DFE 2.59 % 3.39 % 10.6 % 8.0 % 51.21 132.21
5/31/2007-6/29/2007 DFE -3.91 % -1.46 % 18.2 % 14.5 % 49.21 130.28
6/29/2007-7/31/2007 DFE -1.24 % -3.13 % 19.2 % 17.2 % 48.60 126.20
7/31/2007-8/31/2007 DFE -2.46 % 1.28 % 29.9 % 22.5 % 47.40 127.82
8/31/2007-9/28/2007 DFE -1.03 % 3.87 % 17.4 % 14.8 % 46.92 132.77
9/28/2007-10/31/2007 DFE 5.99 % 1.36 % 15.8 % 13.9 % 49.72 134.57
10/31/2007-11/30/2007 DFE -9.09 % -3.87 % 29.7 % 26.6 % 45.20 129.36
11/30/2007-12/31/2007 DFE -4.55 % -1.13 % 41.2 % 17.5 % 43.15 127.90
12/31/2007-1/31/2008 DFE -6.14 % -6.05 % 25.4 % 22.5 % 40.50 120.16
1/31/2008-2/29/2008 DFE 2.67 % -2.58 % 22.2 % 18.1 % 41.58 117.06
2/29/2008-3/31/2008 DFE 2.25 % -0.90 % 23.9 % 27.4 % 42.51 116.00
3/31/2008-4/30/2008 DFE -0.72 % 4.77 % 13.5 % 17.7 % 42.21 121.53
4/30/2008-5/30/2008 DFE 0.02 % 1.51 % 14.2 % 15.0 % 42.21 123.37
5/30/2008-6/30/2008 DFE -12.08 % -8.35 % 19.2 % 19.3 % 37.11 113.07
6/30/2008-7/31/2008 DFE -4.76 % -0.90 % 26.5 % 22.0 % 35.35 112.06
7/31/2008-8/29/2008 DFE -1.14 % 1.55 % 18.4 % 19.6 % 34.95 113.79
8/29/2008-9/30/2008 DFE -15.80 % -9.43 % 54.2 % 47.3 % 29.42 103.05
9/30/2008-10/31/2008 DFE -27.85 % -16.52 % 63.0 % 89.8 % 21.23 86.03
10/31/2008-11/28/2008 DFE -11.51 % -6.96 % 62.0 % 70.9 % 18.79 80.04
11/28/2008-12/31/2008 DFE 9.78 % 0.99 % 45.8 % 46.2 % 20.63 80.83
12/31/2008-1/30/2009 DFE -9.58 % -8.21 % 52.4 % 38.7 % 18.65 74.20
1/30/2009-2/27/2009 DFE -5.39 % -10.74 % 45.4 % 34.4 % 17.64 66.22
2/27/2009-3/31/2009 DFE 4.53 % 8.35 % 42.5 % 48.3 % 18.44 71.76
3/31/2009-4/30/2009 DFE 20.38 % 9.93 % 35.7 % 29.4 % 22.20 78.88
4/30/2009-5/29/2009 DFE 12.33 % 5.85 % 32.2 % 27.6 % 24.94 83.50
5/29/2009-6/30/2009 DFE -2.35 % -0.06 % 36.9 % 19.9 % 24.35 83.44
6/30/2009-7/31/2009 DFE 7.75 % 7.46 % 25.3 % 19.8 % 26.24 89.67
7/31/2009-8/31/2009 DFE 12.68 % 3.69 % 24.3 % 16.4 % 29.57 92.98
8/31/2009-9/30/2009 DFE 6.08 % 3.55 % 28.3 % 15.3 % 31.37 96.28
9/30/2009-10/30/2009 DFE -3.85 % -1.92 % 30.7 % 21.2 % 30.16 94.43
10/30/2009-11/30/2009 DFE 4.46 % 6.16 % 25.8 % 15.2 % 31.50 100.25
11/30/2009-12/31/2009 DFE -0.91 % 1.91 % 16.0 % 10.3 % 31.22 102.16

Full Fidelity Result:
12/29/2006-12/29/2006 DFE 0.00 % 0.00 % 0.0 % 0.0 % 44.16 122.33
12/29/2006-1/31/2007 DFE 2.57 % 1.50 % 8.1 % 8.0 % 45.30 124.17
1/31/2007-2/28/2007 DFE -0.52 % -1.96 % 26.7 % 16.1 % 45.06 121.73
2/28/2007-3/30/2007 DFE 5.72 % 1.16 % 26.0 % 14.0 % 47.64 123.15
3/30/2007-4/30/2007 DFE 4.79 % 4.43 % 9.1 % 7.7 % 49.92 128.60
4/30/2007-5/31/2007 DFE 2.59 % 3.39 % 10.6 % 8.0 % 51.21 132.96
5/31/2007-6/29/2007 DFE -3.91 % -1.46 % 18.2 % 14.5 % 49.21 131.02
6/29/2007-7/31/2007 DFE -1.24 % -3.13 % 19.2 % 17.1 % 48.60 126.92
7/31/2007-8/31/2007 DFE -2.46 % 1.28 % 30.0 % 22.5 % 47.40 128.54
8/31/2007-9/28/2007 DFE -1.03 % 3.87 % 17.4 % 14.8 % 46.92 133.52
9/28/2007-10/31/2007 DFE 5.99 % 1.36 % 15.8 % 13.9 % 49.72 135.33
10/31/2007-11/30/2007 DFE -9.09 % -3.87 % 29.7 % 26.6 % 45.20 130.09
11/30/2007-12/31/2007 DFE -4.55 % -1.13 % 41.2 % 17.5 % 43.15 128.62
12/31/2007-1/31/2008 DFE -6.14 % -6.05 % 25.4 % 22.5 % 40.50 120.84
1/31/2008-2/29/2008 DFE 2.67 % -2.58 % 22.2 % 18.1 % 41.58 117.72
2/29/2008-3/31/2008 DFE 2.25 % -0.90 % 23.9 % 27.4 % 42.51 116.66
3/31/2008-4/30/2008 DFE -0.72 % 4.77 % 13.5 % 17.7 % 42.21 122.22
4/30/2008-5/30/2008 DFE 0.02 % 1.51 % 14.2 % 15.0 % 42.21 124.07
5/30/2008-6/30/2008 DFE -12.08 % -8.35 % 19.2 % 19.3 % 37.11 113.71
6/30/2008-7/31/2008 DFE -4.76 % -0.90 % 26.5 % 22.0 % 35.35 112.69
7/31/2008-8/29/2008 DFE -1.14 % 1.55 % 18.4 % 19.6 % 34.95 114.43
8/29/2008-9/30/2008 DFE -15.80 % -9.43 % 54.2 % 47.3 % 29.42 103.64
9/30/2008-10/31/2008 DFE -27.85 % -16.52 % 63.0 % 89.8 % 21.23 86.52
10/31/2008-11/26/2008 DFE -11.51 % -8.12 % 63.9 % 72.7 % 18.79 79.49
11/26/2008-12/31/2008 DFE 9.78 % 2.26 % 45.8 % 43.6 % 20.63 81.29
12/31/2008-1/30/2009 DFE -9.58 % -8.21 % 52.4 % 38.7 % 18.65 74.62
1/30/2009-2/27/2009 DFE -5.39 % -10.74 % 45.4 % 34.4 % 17.64 66.60
2/27/2009-3/31/2009 DFE 4.53 % 8.35 % 42.5 % 48.3 % 18.44 72.16
3/31/2009-4/30/2009 DFE 20.38 % 9.93 % 35.7 % 29.4 % 22.20 79.33
4/30/2009-5/29/2009 DFE 12.34 % 5.85 % 33.0 % 28.6 % 24.94 83.97
5/29/2009-6/30/2009 DFE -2.36 % -0.63 % 37.7 % 20.7 % 24.35 83.44
6/30/2009-7/31/2009 DFE 7.75 % 7.46 % 26.0 % 19.5 % 26.24 89.67
7/31/2009-8/31/2009 DFE 12.68 % 3.69 % 24.4 % 16.4 % 29.57 92.98
8/31/2009-9/30/2009 DFE 6.08 % 3.55 % 28.3 % 15.3 % 31.37 96.28
9/30/2009-10/30/2009 DFE -3.85 % -1.92 % 30.7 % 21.2 % 30.16 94.43
10/30/2009-11/30/2009 DFE 4.46 % 6.16 % 25.9 % 15.2 % 31.50 100.25
11/30/2009-12/31/2009 DFE -0.91 % 1.91 % 16.0 % 10.3 % 31.22 102.16

Result from ETFReply.com Advanced backtesting

Start End Portfolio Return SPY-Return Volatility SPY-Volatility
Dec 29, 2006 Jan 31, 2007 DFE +2.57 % +1.50 % 8.1 % 8.0 % 1
Jan 31, 2007 Feb 28, 2007 DFE -0.52 % -1.96 % 26.7 % 16.1 % 1
Feb 28, 2007 Mar 30, 2007 DFE +5.72 % +1.16 % 26.0 % 13.8 % 1
Mar 30, 2007 Apr 30, 2007 DFE +4.79 % +4.43 % 9.1 % 7.7 % 1
Apr 30, 2007 May 31, 2007 DFE +2.59 % +3.39 % 10.6 % 8.0 % 1
May 31, 2007 Jun 29, 2007 DFE -3.91 % -1.46 % 18.2 % 14.7 % 1
Jun 29, 2007 Jul 31, 2007 DFE -1.24 % -3.13 % 19.2 % 17.2 % 1
Jul 31, 2007 Aug 31, 2007 DFE -2.46 % +1.28 % 30.0 % 22.5 % 1
Aug 31, 2007 Sep 28, 2007 DFE -1.03 % +3.87 % 17.4 % 14.8 % 1
Sep 28, 2007 Oct 31, 2007 DFE +5.99 % +1.36 % 15.8 % 13.9 % 1
Oct 31, 2007 Nov 30, 2007 DFE -9.09 % -3.87 % 29.8 % 26.6 % 1
Nov 30, 2007 Dec 31, 2007 DFE -5.05 % -1.13 % 26.5 % 18.0 % 1
Dec 31, 2007 Jan 31, 2008 DFE -6.14 % -6.05 % 25.4 % 22.5 % 1
Jan 31, 2008 Feb 29, 2008 DFE +2.67 % -2.58 % 22.2 % 18.1 % 1
Feb 29, 2008 Mar 31, 2008 DFE +2.25 % -0.90 % 23.9 % 27.8 % 1
Mar 31, 2008 Apr 30, 2008 DFE -0.72 % +4.77 % 13.5 % 17.7 % 1
Apr 30, 2008 May 30, 2008 DFE +0.02 % +1.51 % 14.2 % 15.0 % 1
May 30, 2008 Jun 30, 2008 DFE -12.08 % -8.35 % 19.4 % 18.8 % 1
Jun 30, 2008 Jul 31, 2008 DFE -4.76 % -0.90 % 26.5 % 22.0 % 1
Jul 31, 2008 Aug 29, 2008 DFE -1.14 % +1.55 % 18.4 % 19.6 % 1
Aug 29, 2008 Sep 30, 2008 DFE -15.80 % -9.44 % 54.3 % 48.0 % 1
Sep 30, 2008 Oct 31, 2008 DFE -27.85 % -16.52 % 63.2 % 89.8 % 1
Oct 31, 2008 Nov 28, 2008 DFE -11.51 % -6.96 % 62.2 % 70.9 % 1
Nov 28, 2008 Dec 31, 2008 DFE +8.53 % +0.98 % 50.5 % 46.0 % 1
Dec 31, 2008 Jan 30, 2009 DFE -9.58 % -8.21 % 52.4 % 38.7 % 1
Jan 30, 2009 Feb 27, 2009 DFE -5.39 % -10.74 % 45.4 % 34.5 % 1
Feb 27, 2009 Mar 31, 2009 DFE +4.52 % +8.35 % 43.8 % 47.8 % 1
Mar 31, 2009 Apr 30, 2009 DFE +20.38 % +9.93 % 35.8 % 29.5 % 1
Apr 30, 2009 May 29, 2009 DFE +12.34 % +5.85 % 32.3 % 27.6 % 1
May 29, 2009 Jun 30, 2009 DFE -2.43 % -0.07 % 32.3 % 19.9 % 1
Jun 30, 2009 Jul 31, 2009 DFE +7.75 % +7.46 % 25.4 % 19.8 % 1
Jul 31, 2009 Aug 31, 2009 DFE +12.68 % +3.69 % 24.5 % 16.4 % 1
Aug 31, 2009 Sep 30, 2009 DFE +6.08 % +3.55 % 27.6 % 15.1 % 1
Sep 30, 2009 Oct 30, 2009 DFE -3.85 % -1.92 % 30.8 % 21.2 % 1
Oct 30, 2009 Nov 30, 2009 DFE +4.46 % +6.16 % 25.9 % 15.3 % 1
Nov 30, 2009 Dec 31, 2009 DFE -0.91 % +1.91 % 16.0 % 10.4 %


Complete Code for using Yahoo and Fidelity data:
CODE:
Please log in to see this code.