Calculating monthly returns from panel data in R

Question

This may sound like an easy question, but I am unable to find a way to calculate monthly returns from daily price data in R. I have data for 500 companies for over 10 years, therefore I have a panel dataset. My data is presented in the following way:

Date	Ticker	Closing Price
2018-09-07	CBRE	45.80
2018-09-10	CBRE	46.35
2018-09-11	CBRE	46.65
....
2018-09-07	CE	20.80
2018-09-10	CE	21.35
2018-09-11	CE	21.65
...	..	...

Thank you very much for your help. I would really appreciate some guideline on how to calculate this.

score 0 · Answer 1 · answered Mar 15 '22 at 12:01

First I created a reproducible dataframe:

data <- data.frame(Date = c("2018-09-07", "2018-09-07", "2018-09-07", "2018-10-07", "2018-10-07", "2018-10-07", "2018-09-07", "2018-09-07", "2018-09-07", "2018-10-07", "2018-10-07", "2018-10-07"),
                   Ticker = c("CBRE", "CBRE", "CBRE", "CE", "CE", "CE", "CE", "CE", "CE", "CBRE", "CBRE", "CBRE"),
                   Closing_price = runif(12, 20, 50))

Sample data:

         Date Ticker Closing_price
1  2018-09-07   CBRE      27.96526
2  2018-09-07   CBRE      31.16372
3  2018-09-07   CBRE      37.18560
4  2018-10-07     CE      47.24623
5  2018-10-07     CE      26.05046
6  2018-10-07     CE      46.95169
7  2018-09-07     CE      48.34026
8  2018-09-07     CE      39.82393
9  2018-09-07     CE      38.87342
10 2018-10-07   CBRE      21.85359
11 2018-10-07   CBRE      26.17924
12 2018-10-07   CBRE      25.29670

You can use this code to calculate the total closing price per month:

data %>%
  mutate(Month=month(Date,label = T),Year=year(Date)) %>%
  group_by(Year,Month) %>% 
  summarise(Total_Closing_price=sum(Closing_price,na.rm=T))

Output:

# A tibble: 2 × 3
# Groups:   Year [1]
   Year Month Total_Closing_price
  <dbl> <fct>               <dbl>
1  2018 sep                  223.
2  2018 okt                  194.

Calculating monthly returns from panel data in R

1 Answers1