How do I find annualized standard deviation on 60 years of S&P data?

Thanks for the replies.

The column of cells I am looking at is the result of the stategy at the close of each day for about 60 years. It starts at 10,000 and has a different result each day depending on the accumulated gain of the strategy and ends with the final number after about 60 years. Question:

1. I noticed in your example it had open, low, high, and close....as I just have the closing result of the portfolio value for each day would I just need to the e2-h2 formulas you listed above. Also, since it is daily not yearly, do I just copy this down for the approximate 13000 rows of data?

(I believe Yahoo has from 1950-present for S&P 500 data...that is what I used)

My overall purpose for this question is to determine the risk of my system vs. the risk of the S&P 500. I believe the annual standard deviation (is this how you would say that?) for the S&P 500 is around 14%, I want to find out the SD of my system so I can compare the two. Just in case it was unclear, I wanted to share that.

Thanks,

Stephen

PS....

StephenBB wrote:

I could just simply do the standard deviation of all 60 years of the portfolio, divide by the initial investment - 10,000

So your data is probably "total return" for some frequency (daily, weekly, monthly, etc), not the open, high, low and close of the S&P 500 index.

That should not substantially affect your understanding of the formulas that I suggested.  It just simplifies some things.  But to be clear, the formulas are:

B2, open:      =INDEX(val,MATCH(MIN(IF(YEAR(tdate)=A2,tdate)),tdate,0),1)

C2, low:        =MIN(IF(YEAR(tdate)=A2,val))

D2, high:       =MAX(IF(YEAR(tdate)=A2,val))

E2, close:      =INDEX(val,MATCH(MAX(IF(YEAR(tdate)=A2,tdate)),tdate,0),1)

F2, mean:      =AVERAGE(IF(YEAR(tdate)=A2,val))

G2, stdev:     =STDEVP(IF(YEAR(tdate)=A2,val))

H2, %stdev:  =G2/F2

Where "val" is the name of the column of data corresponding to the dates in the column named "tdate".

See my previous posting about entering the array formulas in B2:G2.

StephenBB wrote:

I have a workbook that has about 60 years of data on the S&P 500.

[....] I am interested in finding the standard deviation of each calender year (ie Jan 1, 1960-January 1, 1961), then dividing it by the price on the first cell of the data (ie January 1, 1960). This will give me the annual percent standard deviation...then I want to average all 60 years of this answer and have it inputed in one cell.

First, what you describe here is an __annual__ standard deviation as you wrote, not an "annualized" standard deviation as you wrote in the subject line.  I will assume that your description here is what you truly want.  It is a more reliable statistic.

Second, I quibble with the way in which you compute the (annual) "percent standard deviation".  That is not how "relative standard deviation" (aka "percent relative standard deviation") is normally computed.  It is unclear whether you truly want to compute a different statistic (of dubious value), or you misunderstand the statistic.

To answer your question....

Suppose the dates corresponding to the S&P 500 values are in a column named "tdate".  For completeness, I will assume you have columns of S&P 500 values which you can name "open", "high", "low" and "close".  Finally, suppose A2:A61 contains the year numbers (not dates) 1950 through 2009.

(By the way, where did you get so much data?  Finance.yahoo.com provides only 48 years of SP500 data, 1962 through present day.)

Some of the following formulas might be useful to you, either directly or as paradigms for whatever you need.  Put the following formulas into the indicated cells and copy down through row 61.

B2, open:      =INDEX(open,MATCH(MIN(IF(YEAR(tdate)=A2,tdate)),tdate,0),1)

C2, low:        =MIN(IF(YEAR(tdate)=A2,low))

D2, high:       =MAX(IF(YEAR(tdate)=A2,high))

E2, close:      =INDEX(close,MATCH(MAX(IF(YEAR(tdate)=A2,tdate)),tdate,0),1)

F2, mean:     =AVERAGE(IF(YEAR(tdate)=A2,(high+low)/2))

G2, stdev:     =STDEVP(IF(YEAR(tdate)=A2,(high+low)/2))

H2, %stdev:  =G2/F2

Format H2 as Percentage.

All formulas except H2 are array formulas.  Enter an array formula by pressing ctrl+shift+Enter instead of simply Enter.  Excel will display curly braces around the entire formula in the Formula Bar ({=formula}); but you should not type the curly braces yourself.  If you make a mistake, select the cell, press F2, edit as needed, then press ctrl+shift+Enter.

Finally, the average %stdev is =AVERAGE(H2:H61), a non-array formula formatted as Percentage.

Notes:

1. The mean in F2 is the average "daily" average (i.e. the average high and low for whatever the frequency of your data is).  If you prefer the average close, for example, simply use AVERAGE(IF(YEAR(tdate)=A2,close).  Similarly for the standard deviation in G2.
2. The standard deviation in G2 uses STDEVP instead of STDEV.  This is because the data in the matching range represents the entire data ("population"), not a sampling of data for that period.

Note:  I think Excel 2010 uses different function names for STDEVP and STDEV.  I use Excel 2003.

3. The %stdev in H2 is the statistic known as "relative standard deviation", to wit:  the ratio of the standard deviation to the average.  If you truly want the ratio of the standard deviation to the year's open value, simply use G2/B2.

----- original message -----

Hi. I have a workbook that has about 60 years of data on the S&P 500. I designed a trading system and have the compounded results of the portfolio (day to day) from the beginning of the test to current. I am interested in finding the standard deviation of each calender year (ie Jan 1, 1960-January 1, 1961), then dividing it by the price on the first cell of the data (ie January 1, 1960). This will give me the annual percent standard deviation...then I want to average all 60 years of this answer and have it inputed in one cell.

Is there a formula I could use that would find the January 1sts and would do this calculation between each one then give me the average % standard deviation of all of them? I could just simply do the standard deviation of all 60 years of the portfolio, divide by the initial investment - 10,000, then divide it by 60 (years), but I would guess it would return a slightly different answer.

Thanks,

Stephen

 

Stephen,

You could try this array formula

=STDEV(IF(YEAR($A$2:$A$20)=2009,$B$2:$B$20))/MIN(IF(YEAR ($A$2:$A$20=2009),$B$2:$B$20))

This is an array formula.

This means that you use Ctrl-Shift-Enter to commit the formula, not just Enter (array Enter it). Excel will put curly brackets around the formula in the formula bar, you don't do this.

If you need to change the formula at any time, you must array Enter it again.

--

HTH

Bob

<StephenBB> wrote in message news:*** Email address is removed for privacy *** .com...

Hi. I have a workbook that has about 60 years of data on the S&P 500. I designed a trading system and have the compounded results of the portfolio (day to day) from the beginning of the test to current. I am interested in finding the standard deviation of each calender year (ie Jan 1, 1960-January 1, 1961), then dividing it by the price on the first cell of the data (ie January 1, 1960). This will give me the annual percent standard deviation...then I want to average all 60 years of this answer and have it inputed in one cell.

Is there a formula I could use that would find the January 1sts and would do this calculation between each one then give me the average % standard deviation of all of them? I could just simply do the standard deviation of all 60 years of the portfolio, divide by the initial investment - 10,000, then divide it by 60 (years), but I would guess it would return a slightly different answer.

Thanks,

Stephen