Please see attached spreadsheet, I need to set formula to the green cells (B5-9), correct results are shown in blue cells (D5-8).
The problem is that the data series is a dynamic one, as I download the data from Bloomberg, so as I open the file everyday, it will automatically insert today's price to row 11. Therefore, formulars in cells D5-8 won't work as the day is moving.
I have set the formula in cell B5 to calculate the current MTD (i.e. from 1 July until today), I can use the same method for June, May and April returns, but the problem is that for example, beginning of June doesn't start with 1 June, it starts with 2 June and also ending of May is not 31 May but 30 May. Formula I set at cells A6-8 using EOMONTH. So I can't the same method, how shall I set the formula then?
thanks
Bookmarks