Unfortunately, I have a large data set which VBA has quite the problems with to process. Therefore, I was hoping that R could help me.
I have the following data:
ID month Price
1 1 0,1
1 2 0,2
1 3 0,3
2 1 0,1
2 2 0,2
2 3 0,2
I would like to add a fourth column labeled "returns" in which the monthly returns are displayed. I tried it with a loop but it didn't yield the result I wanted unfortunately.
Desired result and calculation of returns: returnt = Pt / Pt-1 -1
ID month Price return
1 1 0,1
1 2 0,2 1
1 3 0,3 0,5
2 1 0,1
2 2 0,2 1
2 3 0,2 0,5
In VBA my code looks like this:
Dim i as integer
dim j as integer
for i= 1 to 10
j= i + 1
If cells(i,1) = cells (j,1) then
cells(j, 4) = cells(j,3) / cells(i,3) - 1
Else cells(j, 4) = 0
End if
next i
Using the data in the Note at the end create a return function, returnfun, and then apply it separately to the Price vector of each ID using ave. No packages are used.
returnfun <- function(x) c(NA, diff(x) / head(x, -1))
transform(DF, Returns = ave(Price, ID, FUN = returnfun))
giving:
ID month Price Returns
1 1 1 0.1 NA
2 1 2 0.2 1.0
3 1 3 0.3 0.5
4 2 1 0.1 NA
5 2 2 0.2 1.0
6 2 3 0.2 0.0
Alternately define returnfun using zoo like this:
library(zoo)
returnfun <- function(x) diff(zoo(x), arithmetic = FALSE, na.pad = TRUE) - 1
Lines <- "
ID month Price
1 1 0.1
1 2 0.2
1 3 0.3
2 1 0.1
2 2 0.2
2 3 0.2"
DF <- read.table(text = Lines, header = TRUE)