How to calculate monthly returns in R for every company in a dataset of 4000 companies?

Question

I want to calculate monthly returns for a time series of 4000 companies between 2014 and 2019.

This is how my dataset looks like

I'm using the following code to calculate the returns

nyseamex <- mutate(nyseamex, mon_return=adjprice/lag(adjprice)-1)

So far so good. However looking at the data R calculates for every adjusted price the monthly return. This is getting a problem as soon as the company name changes:

I tried to group the names by using the function group_by() however the I got an error message when I run my function, please see below:

Does anyone know how to calculate the correct return for every single company in the dataset like having NA in the return column for the first entry of the new company and than calculating the return up to the last date and do the same procedure for every new company in the series?

Thanks in advance.

Answer 1

I do not have a R/dplyr at my hand right now, but the following should work:

nyseamex %<>% group_by(name) %>% mutate(mon_return = adjprice/lag(adjprice)-1) %>% ungroup()

The first operator %<>%is the re-assignment operator, effectively x=f(x), and when using any of the pipe operators (%>% and %<>%), the first argument to the function can be dropped. Thus,

x=f(x,y)

will become

x %<>% f(y)

EDIT If you need to sort your data in the first place, I suggest

data %>% arrange(column)

from the dplyr universe... I would totally recommend using these things the dplyr way. The code is very clean, readable, and you can easily plug in different operations...

Answer 2

You have not said whether the prices are sorted by date (your function requires it), and you have not said what the desired final data structure should be. But here is one way to do it.

Start with your example dataset:

df <- data.frame(date = as.Date(c("2019-11-29", "2019-12-31",
                                  "2014-01-31", "2014-02-28")),
                 name = c("HANGER INC", "HANGER INC",
                          "ADAMS EXPRESS CO", "ADAMS EXPRESS CO"),
                 price = c(26.20, 27.61, 12.46, 12.92),
                 stringsAsFactors = FALSE)
df
##         date             name price
## 1 2019-11-29       HANGER INC 26.20
## 2 2019-12-31       HANGER INC 27.61
## 3 2014-01-31 ADAMS EXPRESS CO 12.46
## 4 2014-02-28 ADAMS EXPRESS CO 12.92

Now compute returns by splitting the data-frame by name.

library("PMwR")
library("zoo")
ans <- lapply(split(df, df$name),
              function(x) returns(zoo(x$price, x$date), pad = NA))
ans
## $`ADAMS EXPRESS CO`
## 2014-01-31 2014-02-28 
##         NA 0.03691814 
## 
## $`HANGER INC`
## 2019-11-29 2019-12-31 
##         NA 0.05381679 

The result is a list of return series. Using zoo has the advantage that it will make sure the prices are sorted in time.

If you prefer one large data-frame:

do.call(merge, ans)
##            ADAMS EXPRESS CO HANGER INC
## 2014-01-31               NA         NA
## 2014-02-28       0.03691814         NA
## 2019-11-29               NA         NA
## 2019-12-31               NA 0.05381679