Search code examples
rdplyrapplyfinanceperformanceanalytics

Create an Index of Cumulative Returns from Monthly Stock Returns

I'm still quite the rookie data wrangle-R and am struggling to find a solution to this.

Here's what my data frame with monthly percentage changes looks like:

Month    Security1 Security2 ... SecurityN
1970-01   -2.30%     1.02%         0.64%
1970-02    1.87%    -0.01%         9.50%
1970-03    3.38%     2.33%         5.56%

I am trying to get a data frame outputted with each security's monthly return (>1500 of them) indexed to show cumulative return, like so: 

Month    Security1 Security2 ... SecurityN
1970-01     100       100           100
1970-02    101.87    99.99         109.50
1970-03    105.32    102.32        115.59

I have tried using:

cum.ret <- apply(dataframe, 2, function(x) Return.cumulative(x, geometric = TRUE))

But this only returns how much each stock has made since inception: e.g. Security1 returned 121%, Security2 returned 233%, etc.

Asides from loading the data, I have no other code in the notebook.

Any help would be greatly appreciated!

Solution

  • A possible solution:

    mydf[-1] <- lapply(mydf[-1], function(x) as.numeric(sub('%','',x)))
mydf[1,-1] <- 100
mydf[-1] <- lapply(mydf[-1], cumsum)

    which gives:

    > mydf
    Month Security1 Security2 SecurityN
1 1970-01    100.00    100.00    100.00
2 1970-02    101.87     99.99    109.50
3 1970-03    105.25    102.32    115.06

    Used data:

    mydf <- read.table(text="Month    Security1 Security2  SecurityN
1970-01   -2.30%     1.02%         0.64%
1970-02    1.87%    -0.01%         9.50%
1970-03    3.38%     2.33%         5.56%", header=TRUE, stringsAsFactors=FALSE)