'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:
Hope you can help me, I want to learn and I wouldn't like to do it manually :)
Solution 1:[1]
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:
Solution 2:[2]
You could just add another column and use EOMONTH like this =A2=EOMONTH(A2,0) and then just filter by TRUE
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | Spectral Instance |
| Solution 2 |