Calculate the same formulas for many columns.

[sirius1170]

Hello everybody,
I have data like that and I would like to ask for help
My data is the price of many stocks. I would like to calculate the return of each stock by the formulas:
return = (Pt - Pt-1) / Pt-1
I try but can not find how to calculate all stock.
In addition, I would like to calculate the average return of each day.
.

df<-data.frame(
         AAA = c(NA, NA, NA, NA, NA, NA, NA, NA, NA, NA),
         AAM = c(NA, NA, NA, NA, NA, NA, NA, NA, NA, NA),
         ABT = c(14716L,15107L,15758L,15367L,16019L,
                 16800L,16930L,16930L,17321L,18102L),
         AGF = c(32466L,33941L,35417L,35417L,35712L,
                 37188L,37483L,37778L,37188L,38369L),
         ALT = c(14354L,15066L,15777L,15777L,15066L,
                 15421L,14710L,14116L,14235L,14472L),
         BBC = c(24101L,24219L,25395L,25865L,25395L,
                 25395L,25277L,25101L,24336L,25512L),
         BBS = c(4073L,4073L,4073L,4073L,4073L,4010L,
                 4073L,4386L,4825L,5295L),
        date = as.factor(c("2007-01-02",
                           "2007-01-03","2007-01-04","2007-01-05","2007-02-08",
                           "2007-02-09","2007-02-10","2008-01-11","2008-01-12",
                           "2008-01-15"))
)

My output is something like that

Thank you in advance.
Best regards,

[mattwarkentin]

Hi @sirius1170,

Are the numbers in the cells Pt? Where Pt-1 is the stock price from the previous time point? If so, here is one way to do it:

library(tidyverse)
df <- tibble(
  AAA = c(NA, NA, NA, NA, NA, NA, NA, NA, NA, NA),
  AAM = c(NA, NA, NA, NA, NA, NA, NA, NA, NA, NA),
  ABT = c(14716L,15107L,15758L,15367L,16019L,
          16800L,16930L,16930L,17321L,18102L),
  AGF = c(32466L,33941L,35417L,35417L,35712L,
          37188L,37483L,37778L,37188L,38369L),
  ALT = c(14354L,15066L,15777L,15777L,15066L,
          15421L,14710L,14116L,14235L,14472L),
  BBC = c(24101L,24219L,25395L,25865L,25395L,
          25395L,25277L,25101L,24336L,25512L),
  BBS = c(4073L,4073L,4073L,4073L,4073L,4010L,
          4073L,4386L,4825L,5295L),
  date = as.factor(c("2007-01-02",
                     "2007-01-03","2007-01-04","2007-01-05","2007-02-08",
                     "2007-02-09","2007-02-10","2008-01-11","2008-01-12",
                     "2008-01-15"))
)

df %>% 
  mutate_at(vars(AAA:BBS), as.integer) %>% 
  pivot_longer(AAA:BBS, names_to = 'stock', values_to = 'price') %>% 
  arrange(stock, date) %>% 
  group_by(stock) %>% 
  mutate(price_before = lag(price, 1),
         return = (price - price_before) / price_before) %>% 
  summarise(avg_return = mean(return, na.rm = TRUE))

[sirius1170]

@mattwarkentin

Thank you for your quick reply.
Yes, Pt is the price at the time t, Pt-1 is the price at the previous time.
But I would like to have the return of each stock, in order to transfer return from daily to monthly.
Then, I would like to calculate the average return by each day, not each stock.
You can see my desired output in the above table.

[mattwarkentin]

It shouldn't be too tricky to modify my above code to do what you want, I think this is what you're asking for...

stock_returns <- df %>% 
  mutate_at(vars(AAA:BBS), as.integer) %>% 
  pivot_longer(AAA:BBS, names_to = 'stock', values_to = 'price') %>% 
  arrange(stock, date) %>% 
  group_by(stock) %>% 
  mutate(price_before = lag(price, 1),
         return = (price - price_before) / price_before) %>% 
  ungroup()

stock_returns %>% 
  group_by(date) %>% 
  summarise(avg_daily_return = mean(return, na.rm = TRUE))

[sirius1170]

Thank you so much for your support.
Best regards,