Calculating monthly returns based on Panel Data R

Question

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

How are you calculating the monthly returns? A sample of the desired output will also be helpful — SmitM, Sep 05 '18 at 16:30

G. Grothendieck · Accepted Answer · 2018-09-05T17:25:07.987

2

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

Note

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)

edited Sep 05 '18 at 17:25

answered Sep 05 '18 at 16:48

G. Grothendieck

254,981
17
203
341

How can I add the column to my data frame afterwards? – user9259005 Sep 05 '18 at 16:57
2

Assign the result of the `transform` statement to `DF` to overwrite it or to `DF2`, say, to create a new version with the extra column. – G. Grothendieck Sep 05 '18 at 17:02
Unbelievable!! Thank you so much. Any reccomendation where I could learn more about creating my own functions? – user9259005 Sep 05 '18 at 17:05
See the *Introduction to R* manual and the *R Language Definition* manual that come with R. https://cran.r-project.org/manuals.html Also there is some contributed document here: https://cran.r-project.org/other-docs.html – G. Grothendieck Sep 05 '18 at 17:24
Could maybe also explain me what "diff" and "head" mean in the function? – user9259005 Sep 06 '18 at 15:41
See `?diff` and `?head` and try out the examples there. – G. Grothendieck Sep 06 '18 at 15:51

score 0 · Answer 2 · answered Jun 29 '19 at 17:25

The R package TTR is fantastic for financial calculations: https://cran.r-project.org/web/packages/TTR/TTR.pdf

Here's the code from the ROC (rate of change) command: Description Calculate the (rate of) change of a series over n periods. Usage ROC(x, n = 1, type = c("continuous", "discrete"), na.pad = TRUE)

momentum(x, n = 1, na.pad = TRUE)

In your case, simply modify n for your chosen time period.

Calculating monthly returns based on Panel Data R

2 Answers2

Note

Linked