Formula accomodating for the different number of days in a month

[Coyote_e]

Hey all,

I have the problem of not having enough brain to work out how to turn these daily returns into monthly returns in an easy to perform fashion. The attached example is a segment of a pattern that goes on the same for a total of 294 publicly listed companies; in other words until row 764438.

The formula here would be closing price at the end of the month take away the closing price of the start of the month, divided by the closing price at the start of each month. I am avoiding the use of opening price so as to make this all a bit easier to accomplish at the expense of a little accuracy.

The biggest problem apparent to me making a repeatable formula here is that there are a different number of days between months.

As such does anyone think it is at all possible to use the use the Close price column as the inputs for each monthly return, in a way that will be able to be copied down. To me, this sounds impossible, but then again people have shocked and humbled me here in the past. I am not expecting miracles don't worry :p this seems very improbable.

Thank you and all the best,

[Bob Phillips]

Assuming the month name is in K2, the year in L2, you can use

Please Login or Register  to view this content.

[CARACALLA]

N3=IF($K$1<>"",SUMPRODUCT(($A$3:$A$7000=$K$1)*((MONTH($B$3:$B$7000)=MONTH(N$2))*(YEAR($B$3:$B$7000)=$M3)*($B$3:$B$7000<>"")*($I$3:$I$7000))),SUMPRODUCT((MONTH($B$3:$B$7000)=MONTH(N$2))*(YEAR($B$3:$B$7000)=$M3)*($B$3:$B$7000<>"")*($I$3:$I$7000)))

copy down

Select Symbol with yellow drop down month and year return for symbol


with blank drop down month and year return only

[Coyote_e]

Caracalla, your method is pretty ingenius and is very pretty on the eye to have that level of functionality and sorting. However, it makes my Excel unbearably slow when I implement it for over 294 stocks so unfortunately I cannot use it. I'm very thankful for your efforts though.

Bob --- I tried your method and could not get it to work. I have attached my updated example worksheet. What's going wrong do you think?

[Bob Phillips]

If you format M2 as percentage to 2 dec pl you will see it does work. BTW, no need to array-enter, it is not an array formula.