Calculating Monthly Returns Separately for Each Company in R
Summary
The document explains how to calculate monthly price returns for many companies without allowing one company’s last price to affect the next company’s first return. Its main approach is to group observations by company name, then calculate each return from the current adjusted price and the previous price within that group. The first observation for each company therefore has no prior price and receives a missing return.
The responses also emphasize that observations should be ordered by date before applying a lag. An alternative workflow splits the data by company, converts each price series to a dated series, computes returns with an initial missing value, and optionally merges the results into a wider table. The examples show the expected output for two small series. These methods assume company identifiers are consistent and prices are correctly dated; the document does not address complications such as duplicate dates, corporate actions beyond adjusted prices, or irregular observation frequency.
Key ideas
- Group price observations by company before calculating lagged returns.
- The first return for each company should be missing because no previous price is available.
- Sort each company’s prices by date before applying a lag.
- Splitting data into dated series is an alternative way to calculate and combine returns.
- The examples assume consistent identifiers and correctly ordered price observations.
Tags
Full text
# How to calculate monthly returns in R for every company in a dataset of 4000 companies?
# 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?
Thanks in advance.
## Answer by Kermittfrog (score 1, accepted)
https://quant.stackexchange.com/a/58555
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 by Enrico Schumann (score 1)
https://quant.stackexchange.com/a/58561
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
```Shown in full with attribution under the source's licence. Licence: CC BY-SA 4.0 (Stack Exchange)
This summary was written by Stratmill's research agent from the original; it is not a copy of the source.