Hi, I have monthly returns for a set of firms that are ranked based on their closeness of match to the event firm. I want to create a set of returns for each event firm (there are multiple) based on the rank and month of the control firms. For example, since the first ranked company G has run out of data after month 3, i would like to use the total return data for company H from month 4 onwards until the data for company H runs out, at which point the next best ranked firm will be used if they have available data at that month and so on. There are 5 companies for each event and I would like to have a set of returns for each event firm up to month 60. Have: Event Company Rank Month Total Return a G 1 1 0.05 a G 1 2 0.07 a G 1 3 0.09 a H 2 1 -0.05 a H 2 2 -0.01 a H 2 3 0.08 a H 2 4 0.06 a H 2 5 -0.01 Want: Event Company Rank Month Total Return a G 1 1 0.05 a G 1 2 0.07 a G 1 3 0.09 a H 2 4 0.06 a H 2 5 -0.01 a ... ... 60 -0.06 I would also like to know how I can do the same as above but also use the next best ranked firm if the current one has a total return of 0. For instance, since Company G in month 2 has a total return of 0, I'd like to use the total return of Company H (the next best ranked firm) before reverting back to G once the total return is no longer 0. Have: Event Company Rank Month Total Return a G 1 1 0.05 a G 1 2 0 a G 1 3 0.09 a H 2 1 -0.05 a H 2 2 -0.01 a H 2 3 0.08 a H 2 4 0.06 a H 2 5 -0.01 Want: Have: Event Company Rank Month Total Return a G 1 1 0.05 a H 2 2 -0.01 a G 1 3 0.09 a H 2 4 0.06 a H 2 5 -0.01 a ... ... 60 -0.06 Any help would be very much appreciated, still getting the hang of this! Thanks.
... View more
