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))