Hi all,
I have a problem, and I was hoping that some of you could help me.
I am working with a dataset, that contains around 200 stocks. All these stocks have 20 years of monthly shareprices.
Some of these stocks are delisted (for example goes bankrupt) during the time series. The problem is, that the delisted stocks keep having the same shareprice (the last price quoted before delisted) through the whole sample period.
for example (look at stock 4):
<tbody>
</tbody>
So stock 4 is delisted in month 3, but the same price stay in all the months after.
I want to delete the prices (leave cells blank) if the same shareprice appears 3 time in a row for a stock.
So for stock 4 i want to keep the price 88 in month 3, month 4 and month 5, but then I want to delete the price in month 6 and all months after.
Can you help me to solve this, so I dont need to do this manually?
I am able to do a little bit of VBA programming, but it is difficult for me to write a code from scratch.
Maybe it can be done without VBA?
Can any of you guide me?? Any ideas is much appreciated!
Thank you so much!
I have a problem, and I was hoping that some of you could help me.
I am working with a dataset, that contains around 200 stocks. All these stocks have 20 years of monthly shareprices.
Some of these stocks are delisted (for example goes bankrupt) during the time series. The problem is, that the delisted stocks keep having the same shareprice (the last price quoted before delisted) through the whole sample period.
for example (look at stock 4):
Date | Stock1 | Stock2 | Stock3 | Stock4 (delisted) |
month1 | price | price | price | 87 |
month2 | price | price | price | 91 |
month3 | price | price | price | 88 (delisted) |
month4 | price | price | price | 88 |
month5 | price | price | price | 88 |
month6 | ... | ... | ... | 88 |
<tbody>
</tbody>
So stock 4 is delisted in month 3, but the same price stay in all the months after.
I want to delete the prices (leave cells blank) if the same shareprice appears 3 time in a row for a stock.
So for stock 4 i want to keep the price 88 in month 3, month 4 and month 5, but then I want to delete the price in month 6 and all months after.
Can you help me to solve this, so I dont need to do this manually?
I am able to do a little bit of VBA programming, but it is difficult for me to write a code from scratch.
Maybe it can be done without VBA?
Can any of you guide me?? Any ideas is much appreciated!
Thank you so much!