I am trying to reproduce an equally weighted price index. I have the prices and a matrix which tells when a stock is in and out of the index. Here is a small part of the data.
table="date, A, B,C,D,E,F
1,31/01/1998,1,1,1,1,1,0
2,28/02/1998,1,1,1,1,1,0
3,31/03/1998,1,1,1,1,1,0
4,30/04/1998,1,1,1,1,1,0
5,31/05/1998,1,1,1,1,1,0"
matrix=read.csv(text=table)
table2="date,A,B,C,D,E,F
1,05/01/98,20.56,97.40,279.70,72.85,20.33,298.00
2,06/01/98,20.56,96.50,276.20,72.90,20.22,299.90
3,14/02/98,20.84,98.45,282.50,73.75,20.70,302.80
4,15/02/98,20.90,98.50,280.70,73.65,20.71,306.50
5,09/03/98,20.58,97.00,276.20,72.95,20.25,304.00"
price=read.csv(text=table2)
The order of the stock is the same in the price and matrix data. Since I would like to multiply the matrix with the price I turned both into matrix.
as.matrix(price)
as.matrix(matrix)
as.Date[price[,1], format="%d/%m/%y"] #Error: object of type 'closure' is not subsettable
as.Date[matrix[,1], format="%d/%m/%Y"]
(1)However here I got my first problem. The dates are not recognized whether in the matrix nor if I do it before using as.Matrix(). I also tried methods proposed here (Extract month and year from a zoo::yearmon object). I need the dates for the following reason. I would like to make a loop which 1. Takes the month and year from the matrix and searches for the same months and years in the price data. 2. If same month and years are found it should multiply the row from matrix with the rows from prices. This is due to the fact that the matrix is on monthly basis and the prices are daily. And this would be my Idea of the loop:
for (matrix(%m/%Y) in price$date){
if (matrix(%m/%Y)== price(%m/%y)
c<- matrix[position of matrix(%m/%Y),] %*% price[position of price(%m/%y),]
}
(2)However I never worked with loops before and the second question is if the for loop is suitable for my problem? The desired output of the loop would be the following:
table3="date,A,B,C,D,E,F
1,05/01/98,20.56,97.40,279.70,72.85,20.33,0
2,06/01/98,20.56,96.50,276.20,72.90,20.22,0
3,14/02/98,20.84,98.45,282.50,73.75,20.70,0
4,15/02/98,20.90,98.50,280.70,73.65,20.71,0
5,09/03/98,20.58,97.00,276.20,72.95,20.25,0"
desired_c=read.csv(text=table3)
At the end however, I would like to have an equally weighted price index like this:
table4="date, price
1,05/01/98,98.168
2,06/01/98,97.276
3,14/02/98,99.248
4,15/02/98,98.892
5,09/03/98,97.396"
desired_index=read.csv(text=table4)
if I could put that in my loop that would be great. Please note that the matrix and the prices are consisting of many observations. Therefore only deleting last column is not an option.
