Hi!
I have a dataframe with companies, year, month, MV, price and RI.
Illustrated:
data_frame<-cbind(company=c("comp1","comp1","comp2","comp2","comp2","comp2","comp3",
"comp3", "comp3", "comp3", "comp3", "comp3"),
year=c(2011,2012,2014,2015,2016,2017, 1980,1981,1982,1983,1984,1985),
month=c(12,12,12,12,12,12,12,12,12,12,12,12),
MV=c(123,345,543,544,345,654,765,123,324,543,544,666),
price=c(92.8,132.5,90.4,143.74,150.1,38.06,33.1,33.8,34.2, 54.4, 56, 56.8),
RI=c(34,54,76,87,87,67,34,65,76,89,12,34))
data_frame<-as.data.frame(data_frame)
print(data_frame)
company year month MV price RI
1 comp1 2011 12 123 92.8 34
2 comp1 2012 12 345 132.5 54
3 comp2 2014 12 543 90.4 76
4 comp2 2015 12 544 143.74 87
5 comp2 2016 12 345 150.1 87
6 comp2 2017 12 654 38.06 67
7 comp3 1980 12 765 33.1 34
8 comp3 1981 12 123 33.8 65
9 comp3 1982 12 324 34.2 76
10 comp3 1983 12 543 54.4 89
11 comp3 1984 12 544 56 12
12 comp3 1985 12 666 56.8 34
My real data frame have 4691 observations of annual stock price data (December (12) close price for different years. The observations start and ends at different time intervals, some companies have annual data from 1980-2020, and some might have data for 2010-2015 etc.
My problem is: I need to calculate the returns for each company from price-column. -> create a new column of returns calculated from prices (price(t)/price(t-1))-1
I will then need the return column to be shifted, so the returns are represented as return(t+1) compared to the other data, meaning for the year 2012 I will have the 2013 return in the row.
Any tips?
Thanks!