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

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?

• Forget my now deleted comment, what is nyseamex? Your column, your table or both? – Bob Jansen Oct 8 '20 at 14:06
• Hi, I think you must drop the nyseamex variable and also drop the tick marks from the mon_return formula. Also, afterwards you need to "ungroup()". – Kermittfrog Oct 8 '20 at 14:09
• Hi Bob, Thanks for your reply, nyseamex is my table (screenshot 1). It contains all stocks listed on NYSE and on AMEX during my time period 2014-2019. Screenshot 2 is the continuation of screenshot 1 with the additional column mon_return. Up to this point I only added column adjprice and mon_return, no sorting or grouping. Do you need more information? @Kermittfrog Thanks for your reply too! What do you mean with dropping my nyseamex variable? I think the mutate () formula needs to where to put the extra column mon_return. Could you be a little bit more precise on that? Thanks. – Max Oct 8 '20 at 21:56
• This really isn't specific to quant finance and is better-suited to stackoverflow.com/questions/tagged/r – Chris Oct 9 '20 at 6:45

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

• Hi Kermittfrog, You already were a little bit more precise on your comment above, my bad sorry. I will try that and will let you know if it works. – Max Oct 8 '20 at 22:01
• Hi Kermittfrog, that was very helpful. Now the code is doing what it is supposed to do. THANK YOU! – Max Oct 9 '20 at 9:45

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.

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",
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