CODEWITHSUNDEEP
excelexcel-formula
queenbegop777
queenbegop777

Reputation: 83

Filtering prices of stocks by the last day of the month

I have this Excel sheet with daily prices of some stocks. I want to calculate monthly returns from December 2017 to December 2019 with the last days of the month that the market was open. So I need to "erase" all the prices that are not from the last day of each month I have tried using max function with filter function, but I'm a noobie on Excel so I don't really know how to use them. Here is how the excel sheet looks like:

enter image description here Hope you can help me, I want to learn and I wouldn't like to do it manually :)

Upvotes: 0

Views: 163

Answers (2)

Spectral Instance
Spectral Instance

Reputation: 2494

Use a helper column where you compare the result of the MONTH() function for the current row with the result of the MONTH() function for the following row: Screenshot illustrating use of proposed formula

Upvotes: 1

DKoontz
DKoontz

Reputation: 307

You could just add another column and use EOMONTH like this =A2=EOMONTH(A2,0) and then just filter by TRUE

Upvotes: 0

Related Questions