I am new to R and I have a problem with lagging an operation.
These are my data frames.
Stock_Returns
Date Stock_A Stock_B
01.01.1990 NA NA
01.02.1990 0.02 0.04
01.03.1990 0.03 0.05
01.04.1990 0.04 0.06
Market_Cap
Date Stock_A Stock_B Sum
01.01.1990 30 30 60
01.02.1990 20 35 55
01.03.1990 30 50 80
01.04.1990 40 60 100
I want to have the sumproduct of Stock_Returns and Market_Cap (with a lag of -one) and then divide it with the Market_Cap$Sum (with lag of -one).
This is my code with no lag! I need a lag of -1
Index <- rowSums(Returns[,2:ncol(Returns)]*(Market_Cap[,3:ncol(Market_Cap)-1]),na.rm=TRUE)/Market_Cap$Sum
My output should look like this:
Index_Return
Date Index
01.01.1990 NA
01.02.1990 0.03
01.03.1990 0.0427
01.04.1990 0.0525
The calculation for the 01.02.1990 would be (0.02*30+0.04*30)/60
Thank you in advance!
This is a way to achieve your desired output by use of row numbers. We select a subset of rows in the first table with indices n, and then in the second table a subset of rows with indices n-1.
Index <- rowSums(Returns[2:nrow(Returns), 2:ncol(Returns)] * Market_Cap[2:nrow(Returns)-1, 3:ncol(Market_Cap)-1], na.rm = T) / Market_Cap[2:nrow(Returns)-1, "Sum"]
Then we can append it to Returns:
Returns$Index[2:nrow(Returns)] <- Index