I am trying to extract "close1" for the last reported value of "day_of_week" with respect to the "week_num". In other words, I am trying to extract the stocks' last close price of the week - ideally friday - but since some of these stocks are old, discontinued, and illiquid they are not reported for all days of the week. Therefore, I would like to extract the last close price of the week in order to calculate weekly returns.
Any tips? (PS: Large dataset with 1,4 million observations)
Below I have an example of the dataset.
Any tips? (PS: Large dataset with 1,4 million observations)
Below I have an example of the dataset.
date1 | isin1 | sector1 | name1 | close1 | ret | day_of_week | friday_close | increase | weekly_return | week_num |
08.03.1990 | NO0005394908 | Frysja Elektro | 14 | -.7700607 | 4 | 09.03.1990 | .2299393 | 0 | 10 | |
11.04.1990 | NO0005394908 | Frysja Elektro | 12 | -.2772484 | 3 | 13.04.1990 | .7227516 | 0 | 15 | |
09.05.1990 | NO0005394908 | Frysja Elektro | 12 | -.7994583 | 3 | 11.05.1990 | .2005417 | -.7189392 | 19 | |
11.05.1990 | NO0005394908 | Frysja Elektro | 14 | -.7189392 | 5 | 11.05.1990 | .2810608 | -.7189392 | 19 | |
18.05.1990 | NO0005394908 | Frysja Elektro | 11 | -.796392 | 5 | 18.05.1990 | .203608 | 0 | 20 | |
22.06.1990 | NO0005394908 | Frysja Elektro | 10 | -.8528534 | 5 | 22.06.1990 | .1471466 | 0 | 25 | |
13.04.1983 | NO0005396200 | Industrials | Simrad Optronics | 200 | -.2939858 | 3 | 15.04.1983 | .7060143 | .6817558 | 15 |
14.04.1983 | NO0005396200 | Industrials | Simrad Optronics | 230 | -.2950562 | 4 | 15.04.1983 | .7049438 | .6817558 | 15 |
15.04.1983 | NO0005396200 | Industrials | Simrad Optronics | 230 | 1.385659 | 5 | 15.04.1983 | 2.385659 | .6817558 | 15 |
18.04.1983 | NO0005396200 | Industrials | Simrad Optronics | 210 | -.5354477 | 1 | 22.04.1983 | .4645523 | -.7946736 | 16 |
19.04.1983 | NO0005396200 | Industrials | Simrad Optronics | 215 | -.4592903 | 2 | 22.04.1983 | .5407097 | -.7946736 | 16 |
20.04.1983 | NO0005396200 | Industrials | Simrad Optronics | 205 | -.5373737 | 3 | 22.04.1983 | .4626263 | -.7946736 | 16 |
21.04.1983 | NO0005396200 | Industrials | Simrad Optronics | 195 | 1.108895 | 4 | 22.04.1983 | 2.108895 | -.7946736 | 16 |
22.04.1983 | NO0005396200 | Industrials | Simrad Optronics | 200 | -.6107797 | 5 | 22.04.1983 | .3892203 | -.7946736 | 16 |
25.04.1983 | NO0005396200 | Industrials | Simrad Optronics | 210 | -.6133224 | 1 | 29.04.1983 | .3866776 | -.8287998 | 17 |
26.04.1983 | NO0005396200 | Industrials | Simrad Optronics | 235 | -.5259737 | 2 | 29.04.1983 | .4740263 | -.8287998 | 17 |
27.04.1983 | NO0005396200 | Industrials | Simrad Optronics | 240 | -.4991617 | 3 | 29.04.1983 | .5008383 | -.8287998 | 17 |
28.04.1983 | NO0005396200 | Industrials | Simrad Optronics | 225 | -.5126684 | 4 | 29.04.1983 | .4873316 | -.8287998 | 17 |
29.04.1983 | NO0005396200 | Industrials | Simrad Optronics | 222,5 | .4797208 | 5 | 29.04.1983 | 1.479721 | -.8287998 | 17 |
Comment