2
$\begingroup$

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

This is how my dataset looks like enter image description here

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:

enter image description here

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:

enter image description here

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.

$\endgroup$
4
  • $\begingroup$ Forget my now deleted comment, what is nyseamex? Your column, your table or both? $\endgroup$
    – Bob Jansen
    Oct 8, 2020 at 14:06
  • $\begingroup$ 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()". $\endgroup$ Oct 8, 2020 at 14:09
  • $\begingroup$ 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. $\endgroup$
    – Max
    Oct 8, 2020 at 21:56
  • $\begingroup$ This really isn't specific to quant finance and is better-suited to stackoverflow.com/questions/tagged/r $\endgroup$
    – Chris
    Oct 9, 2020 at 6:45

2 Answers 2

1
$\begingroup$

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

$\endgroup$
2
  • $\begingroup$ 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. $\endgroup$
    – Max
    Oct 8, 2020 at 22:01
  • $\begingroup$ Hi Kermittfrog, that was very helpful. Now the code is doing what it is supposed to do. THANK YOU! $\endgroup$
    – Max
    Oct 9, 2020 at 9:45
1
$\begingroup$

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
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.