Suppose I have a data.table, priceDT, with daily observations of returns on multiple shares like so:
> priceDT
Date Return Share
1: 2011-01-03 0.04500000 GAI
2: 2011-01-03 -0.02100000 KDV
3: 2011-01-04 0.03300000 GAI
4: 2011-01-04 0.01770000 KDV
5: 2011-01-05 -0.01742000 GAI
6: 2011-01-05 0.07900000 KDV
7: 2011-02-06 0.02400000 GAI
8: 2011-02-06 -0.02110000 KDV
9: 2011-02-07 -0.04300000 AFT
10: 2011-02-07 0.01199700 AIP
11: 2011-02-07 0.00551810 ARH
12: 2011-02-07 0.07451101 BIK
13: 2011-02-07 -0.03495597 BLU
14: 2011-02-07 -0.06062462 CGR
15: 2011-02-07 -0.03660000 GAI
16: 2011-02-07 -0.01240000 KDV
I want to calculate the average monthly return of all shares in a given month. So in January of 2011, the average of the return of the two shares. We know it's only two shares because of the share column. The first step is to get the average return of each share in that month. Then get the average return of the portfolio of shares in that month. So in January, the average of GAI is 0.02019333 and the average of KDV is 0.02523333. The average for the month is therefore: 0.02019333
That is the logic of the portfolio return. I want to repeat in data.table for the rest of the months
For my sample data, I want a result like so:
portfolio
Date avg_return
1: 2011-01 0.02271333
2: 2011-02 -0.008700561
Data:
priceDT <- fread(text = "Date, Return, Share
2011-01-03,0.045,GAI
2011-01-03,-0.021,KDV
2011-01-04,0.033,GAI
2011-01-04,0.0177,KDV
2011-01-05,-0.01742,GAI
2011-01-05,0.079,KDV
2011-02-06,0.024,GAI
2011-02-06,-0.0211,KDV
2011-02-07,-0.043,AFT
2011-02-07,0.011997,AIP
2011-02-07,0.0055181,ARH
2011-02-07,0.074511006,BIK
2011-02-07,-0.034955973,BLU
2011-02-07,-0.060624622,CGR
2011-02-07,-0.0366,GAI
2011-02-07,-0.0124,KDV
")
portfolio <- fread(text = "Date, avg_return
2011-01,0.022713333
2011-02,-0.01194431
")
priceDT[, mean(Return), by = .(ym = format(Date, "%Y-%m"), Share)
][, mean(V1), by = ym]
# ym V1
# 1: 2011-01 0.022713333
# 2: 2011-02 -0.008700561