What R function could I use to add up monthly returns in a dataframe?

Question

i would like to aggregate monthly returns in a dataframe to a yearly return. Unfortunately i don't understand how the aggregate function works based on the information provided by R; or if it's even the right function to begin with. The code itself is based on BatchGetSymbols, because i want to rank all Tickers from an Index (in this case the S&P 500) for Momentum/Contrarian Strategies.

I have already tried the aggregate function, which gives me these error messages:

aggregate(prelimranking, FUN = add_row(prelimranking$ret.adjusted.prices))

"Error: Can't subset with `[` using an object of class NULL.
Call `rlang::last_error()` to see a backtrace
In addition: Warning message:
`.data` must be a data frame in `add_row()` and `add_case()`."

This is the relevant code:

//

library(BatchGetSymbols)

library(tidyverse)

library(plyr)

library(dplyr)

first.date <- Sys.Date() - 365

last.date <- Sys.Date()

GSPCData <- GetSP500Stocks(do.cache = TRUE, cache.folder = "BGS_Cache")

tickers <- GSPCData$Tickers


l.out <- BatchGetSymbols(tickers = tickers,first.date = first.date,last.date = last.date, do.cache=FALSE, freq.data = "monthly")


prelimranking <- na.omit(l.out$df.tickers) 

//

So what i get as "prelimranking" is basically a table that has about 6000 rows, with every ticker having 12 rows for the return of every single month. What i would like to have is a table with the monthy returns summed up, so i get around 500 rows, one for each ticker; so that i can rank them based on their returns. Thank you very much for your time.

Possible duplicate of [Aggregate / summarize multiple variables per group (e.g. sum, mean)](https://stackoverflow.com/questions/9723208/aggregate-summarize-multiple-variables-per-group-e-g-sum-mean) — Wimpel, Sep 17 '19 at 14:20

score 0 · Answer 1 · answered Sep 17 '19 at 14:34

Since you are using the BatchGetSymbols package, which returns a list object that contains a tibble, you might also want to load tidyquant. This is a package that can deal with the tibble and use the quantmod functions for calculating period returns. Otherwise you would have to split all the data into a giant list and use lapply and xts transformations.

I created an example below with only 2 tickers as I'm not going to download the full data of the SP500 stocks.

library(BatchGetSymbols)
library(tidyquant)

tickers <- c('FB','MMM')

first.date <- Sys.Date() - 365
last.date <- Sys.Date()

l.out <- BatchGetSymbols(tickers = tickers,
                         first.date = first.date,
                         last.date = last.date, do.cache=FALSE,
                         freq.data = "monthly")



l.out$df.tickers %>%
  group_by(ticker) %>%
  tq_transmute(select     = "price.adjusted", 
               mutate_fun = periodReturn, 
               period     = "yearly", 
               type       = "arithmetic")

# A tibble: 4 x 3
# Groups:   ticker [2]
  ticker ref.date   yearly.returns
  <chr>  <date>              <dbl>
1 FB     2018-12-03       -0.121  
2 FB     2019-09-03        0.293  
3 MMM    2018-12-03        0.00313
4 MMM    2019-09-03       -0.221

score 0 · Accepted Answer · answered Sep 17 '19 at 14:41

library(PerformanceAnalytics)

aggmon <- function(x){
  vals <- rep(NA, 11)
  ann <- c(vals, Return.cumulative(x))
  return(ann)
    }

annual_rets <- transform(prelimranking, ret.monthly = ave(ret.adjusted.prices, ticker, FUN = aggmon))

The above code keeps the original structure of the data and places the annual return in the last row of the observations per ticker. The custom function aggmon calculates the annual return of the monthly returns. It uses the function from PerformanceAnalytics package to calculate cumulative returns. The function returns 11 NAs and the annual return. This is because there are exactly 12 observations for each ticker.

What R function could I use to add up monthly returns in a dataframe?

2 Answers2