Biff and Joe,

Thanks for the help. I've got a question for Biff. I copy the exact formula
to cell D2 and E2 (the other setting is just as you assumed). I got different
answer as Jan 1999 = 0.013515868. But i know that 0.011753089 is the right
answer.

Any idea?

Frank


"Biff" wrote:

=TEXT(DATE(1999,0+ROWS($1:1),1),"mmm yyyy")

Don't need the 0+. Don't know why I put that in there!

=TEXT(DATE(1999,ROWS($1:1),1),"mmm yyyy")

Biff

"Biff" wrote in message
...
Hi!

Try this:

Dates in column A, Return in column B.

Say, in D2, enter this formula: (I'm assuming your data starts in January
1999)

=TEXT(DATE(1999,0+ROWS($1:1),1),"mmm yyyy")

In E2 enter this formula as an array using the key combo of
CTRL,SHIFT,ENTER:

=STDEV(IF(TEXT(A2:A500,"mmm yyyy")=D2,B2:B500))

Select both D2 and E2 and copy down as needed.

The results will look like this:

Jan 1999..........0.0117530886768349
Feb 1999.........0.0036587412554555
Mar 1999........0.0154788521445444
Apr 1999.........0.0054782145874155
etc
etc

Adjust the range in the Stdev function to suit.

Biff

"frank" wrote in message
...
I have a time series data contains two columns. (example as below) It
records
stocks' daily return for 6 year period. What I am interested is to find
out
the monthly return volatility presented by standard deviation. Therefore,
my
first monthly return volatility will be =stdev(B2:b21). I can do this
manually each month for 6 year (6*12=72 times) and repeat for another
stock.
Can anyone help me to simplify the process?

Date Return
1/4/1999 0.010539721
1/5/1999 0.016203103
1/6/1999 0.021746115
1/7/1999 -0.000462872
1/8/1999 0.009811546
1/11/1999 -0.003875422
1/12/1999 -0.020677227
1/13/1999 -0.0102644
1/14/1999 -0.005702611
1/15/1999 0.025050688
1/18/1999 0.009379503
1/19/1999 -0.00936856
1/20/1999 -0.006888695
1/21/1999 -0.011408838
1/22/1999 -0.006306738
1/25/1999 0.003726166
1/26/1999 0.006794611
1/27/1999 -0.00176185
1/28/1999 0.002516646
1/29/1999 0.009164049