Calculate 100yrs monthly st. deviation with daily inputs

[amiko92]

Hi guys, this is my first thread here and I would be glad if you can solve my problem. I try to explain it:

I have got daily returns for an index, starting from 1926 ending today. My pourpose is to calculate monthly standard deviations but since months have different days (28-29-30-31) I'm not able to determine them simply by replicating the command. I have uploaded a spreadsheet where I have just calculated the first monthly data: i need to do it of course for the other months.
What do you suggest me to do? If i wasn't clear in the description of the issue, just tell me and i will try to explain it better.

thank you

Luca

[CAntosh]

Welcome to the forum!

Try the following formula in D2. It should be array-entered (confirmed with Ctrl + Shift + Enter instead of Enter):

=+STDEV.P(IF((YEAR($A$2:$A$23929)=YEAR($C2))*(MONTH($A$2:$A$23929)=MONTH($C2))=1,$B$2:$B$23929))

Fill it down for the rest of the months. Give it a look to see if it'll do:

[amiko92]

Thank You really CAntosh.. i will mention you in my thesis

Luca

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.