R Creating Portfolio Returns of Historic Trades-CodePudding

I created this thread because there are currently no threads for the portfolio calendar approach in an event study. Although this approach is used in finance, this question relates to the code used in the first step of using this approach; calculating the average return of all trades over the period.

I want to create portfolio returns (equally weighted) of a set of pre-specified trades (n =~100000), in a sample period (in this example: 01-01-2000 to 01-01-2010), while already having the actual return of these trades per day. Being that this is an event study, only the first x days are taken into account per trade (21 days in this example, with t=0 being the starting day of the period and t=20 being the final day of the trade).

The data is structured in such a way:

For every transaction, the ID is unique, and for every transaction the actual return per day following the event is known. For example, Ret.t0 is the return made on the day the event took place (which is found in the “Date” column, e.g. 2000-01-01), Ret.t1 is the return made on 1 day after the day the event took place (e.g., 2000-01-02).

Creating a sample, reproducible in r code:

size = 1e5

df <- data.frame(
  ID = seq.int(size),
  FirmID = sample(1:1000),
  Date = sample(seq(
    as.Date('2000/01/01'), as.Date('2010/01/01'), by = "day"
  ), size, replace = TRUE),
  Ret.t0 = sample(-2000:2000, size, replace = TRUE)/100000,
  Ret.t1 = sample(-2000:2000, size, replace = TRUE)/100000,
  Ret.t2 = sample(-2000:2000, size, replace = TRUE)/100000,
  Ret.t3 = sample(-2000:2000, size, replace = TRUE)/100000,
  Ret.t4 = sample(-2000:2000, size, replace = TRUE)/100000,
  Ret.t5 = sample(-2000:2000, size, replace = TRUE)/100000,
  Ret.t6 = sample(-2000:2000, size, replace = TRUE)/100000,
  Ret.t7 = sample(-2000:2000, size, replace = TRUE)/100000,
  Ret.t8 = sample(-2000:2000, size, replace = TRUE)/100000,
  Ret.t9 = sample(-2000:2000, size, replace = TRUE)/100000,
  Ret.t10 = sample(-2000:2000, size, replace = TRUE)/100000,
  Ret.t11 = sample(-2000:2000, size, replace = TRUE)/100000,
  Ret.t12 = sample(-2000:2000, size, replace = TRUE)/100000,
  Ret.t13 = sample(-2000:2000, size, replace = TRUE)/100000,
  Ret.t14 = sample(-2000:2000, size, replace = TRUE)/100000,
  Ret.t15 = sample(-2000:2000, size, replace = TRUE)/100000,
  Ret.t16 = sample(-2000:2000, size, replace = TRUE)/100000,
  Ret.t17 = sample(-2000:2000, size, replace = TRUE)/100000,
  Ret.t18 = sample(-2000:2000, size, replace = TRUE)/100000,
  Ret.t19 = sample(-2000:2000, size, replace = TRUE)/100000,
  Ret.t20 = sample(-2000:2000, size, replace = TRUE)/100000)

The main problem is that, for example, a trade made on 01-01-2000 has a return on day t=6 that needs to be matched with a trade made on 07-01-2000, which the return corresponds to day t=0. Every trade that has a return, needs to be taken into account in the average return, which is the portfolio return. However, the number of trades at any given time is also not fixed.

The portfolio return output needs to look like this:

Date       Return
2000-01-01 0.01205
2000-01-02 0.0089
2000-01-03 0.0012
….
2010-01-21 0.0302

CodePudding user response：

like this?

library(dplyr)
library(tidyr)

df %>%
    ## ID not needed:
    select(-ID) %>%
    ## stack return timepoints per FirmID and Date
    pivot_longer(cols = starts_with('Ret'),
                 names_to = 'return_code',
                 values_to = 'return') %>%
    arrange(FirmID, Date) %>%
    rename('start_date' = 'Date') %>%
    ## extract timepoint t = 0, 1, ... from return code Ret.t0 ...
    ## and cast it to integer to add it to start date:
    mutate(t = gsub('.*t', '', return_code) %>% as.integer,
           date = start_date   t) %>%
    ## group by actual date and summarise:
    group_by(date) %>%
    summarise(avg_return = mean(return, na.rm = TRUE))