CODEWITHSUNDEEP
rfor-loopreturn
user2893255
user2893255

Reputation: 33

calculate cummulative return using loop

I am currently working on some hedge fund data with monthly returns but I hope to have cummulative return instead. I have tried Return.cummulative function but it doesn't give me my desired results due to NAs in my data set. I have written a loop to get the results but it takes ages to finish looping. I copy my code below and please help me to figure out how I can reduce the looping time. I am a starter in R so my code looks quite stupid. Please feel free to let me know about other alternatives without the loop. My real data set contains 10000+hedge funds and 200+ monthly returns. As you can expect, my code will take hours to finish the calculation. I do believe there are alternatives but at this moment, I have to follow the stupic code I've written. 

mock<-data.frame(matrix(NA,ncol=2,nrow=4))
rownames(mock)<-as.vector(c("day1","day2","day3","day4"))
colnames(mock)<-as.vector(c("fund1","fund2"))
mock[,1]<-c(NA,-0.08,0.1,0.23)
mock[,2]<-c(NA,-0.08,0.22,NA)
mock
results<-data.frame(matrix(NA,ncol=2,nrow=4))
prev <- 1 
for (j in 1:2){for(i in 1:4) { 
  if (is.na(mock[i,j])) { 
    results[i,j] <- NA 
  } else { 
    results[i,j]<-((1+mock[i,j])*prev) 
  } 
  if(is.na(results[i,j])){prev<-1}else{prev <- results[i,j]} 
} }
results

I will appreciate any comment from you. Thanks

Wei

Upvotes: 1

Views: 163

Answers (1)

TheComeOnMan
TheComeOnMan

Reputation: 12875

Are you looking for something like this over each column?

library(data.table)
mock <- data.table(mock)

# initiate mockreturns dataset
mockreturns <- copy(mock)
# overwrite NAs with 0s to allo cumprod to work
mockreturns[is.na(mockreturns)] <- 0
# names of return cols in new dataset
setnames(mockreturns,paste0(colnames(mock),"returns"))
#calculate cumulative interest
mockreturns <- mockreturns[,
      lapply(
        .SD, 
        function(x) 
          cumprod(
            x+1
            )
        )
    ]
#replace the NA returns with NAs
mockreturns[is.na(mock)] <- NA

Output -

> mockreturns
   fund1returns fund2returns
1:           NA           NA
2:      0.92000       0.9200
3:      1.01200       1.1224
4:      1.24476           NA

Thanks Mark, for pointing out that I msised the NA part

Upvotes: 1

Related Questions