Suppose I have a data.table like this:
print(priceDT)
Date Return Stock
1: 2011-01-03 0.0800 AFR
2: 2011-01-04 0.0300 AFR
3: 2011-01-05 -0.0125 AFR
4: 2011-01-06 0.0100 AFR
5: 2011-01-07 0.0020 AFR
6: 2011-01-10 0.0073 BMF
7: 2011-01-11 -0.0150 BMF
8: 2011-01-12 0.0130 BMF
9: 2011-01-13 -0.0080 BMF
10: 2011-01-14 0.0410 BMF
11: 2011-01-17 -0.0065 BMF
12: 2011-01-18 0.0180 BMF
13: 2011-01-19 0.0017 BMF
14: 2011-01-20 0.0262 BMF
15: 2011-01-21 -0.0190 BMF
I want to subtract a vector of market returns from the returns column in the priceDT data.table. The market returns is in another data.table and contains dates from the first 3 trading weeks in 2011. However, the two stocks in the priceDT do not have data for all of the trading weeks. The first stock, AFR, only has one week of data. The second stock has two weeks of data. The market returns have all three weeks of data.
The market returns looks like this:
print(market)
market
Date Return
1: 2011-01-03 0.01112
2: 2011-01-04 0.02800
3: 2011-01-05 0.00580
4: 2011-01-06 0.00400
5: 2011-01-07 0.00910
6: 2011-01-10 0.01300
7: 2011-01-11 -0.00800
8: 2011-01-12 -0.01000
9: 2011-01-13 0.02400
10: 2011-01-14 0.01390
11: 2011-01-17 0.01040
12: 2011-01-18 0.00718
13: 2011-01-19 0.01220
14: 2011-01-20 -0.00620
15: 2011-01-21 0.00944
How do I subtract the market returns of the first week of January from the returns of the first stock, AFR and substract the market returns of the next two weeks of January from the returns of the second stock, BMF. Is there a way to match by dates in both data before subtracting?
The result should be:
print(result)
Date Return Adjusted Return Stock
1: 2011-01-03 0.0800 0.06888 AFR
2: 2011-01-04 0.0300 0.00200 AFR
3: 2011-01-05 -0.0125 -0.01830 AFR
4: 2011-01-06 0.0100 0.00600 AFR
5: 2011-01-07 0.0020 -0.00710 AFR
6: 2011-01-10 0.0073 -0.00570 BMF
7: 2011-01-11 -0.0150 -0.00700 BMF
8: 2011-01-12 0.0130 0.02300 BMF
9: 2011-01-13 -0.0080 -0.03200 BMF
10: 2011-01-14 0.0410 0.02710 BMF
11: 2011-01-17 -0.0065 -0.01690 BMF
12: 2011-01-18 0.0180 0.01082 BMF
13: 2011-01-19 0.0017 -0.01050 BMF
14: 2011-01-20 0.0262 0.03240 BMF
15: 2011-01-21 -0.0190 -0.02844 BMF
Data:
priceDT <- fread(text = "Date,Return,Stock
2011-01-03,0.08,AFR
2011-01-04,0.03,AFR
2011-01-05,-0.0125,AFR
2011-01-06,0.01,AFR
2011-01-07,0.002,AFR
2011-01-10,0.0073,BMF
2011-01-11,-0.015,BMF
2011-01-12,0.013,BMF
2011-01-13,-0.008,BMF
2011-01-14,0.041,BMF
2011-01-17,-0.0065,BMF
2011-01-18,0.018,BMF
2011-01-19,0.0017,BMF
2011-01-20,0.0262,BMF
2011-01-21,-0.019,BMF
")
market <- fread(text = "Date,Return
2011-01-03,0.01112
2011-01-04,0.028
2011-01-05,0.0058
2011-01-06,0.004
2011-01-07,0.0091
2011-01-10,0.013
2011-01-11,-0.008
2011-01-12,-0.01
2011-01-13,0.024
2011-01-14,0.0139
2011-01-17,0.0104
2011-01-18,0.00718
2011-01-19,0.0122
2011-01-20,-0.0062
2011-01-21,0.00944
")
result <- fread(text = "Date,Return,Adjusted Return,Stock
2011-01-03,0.08,0.06888,AFR
2011-01-04,0.03,0.00200,AFR
2011-01-05,-0.0125,-0.01830,AFR
2011-01-06,0.01,0.00600,AFR
2011-01-07,0.002,-0.00710,AFR
2011-01-10,0.0073,-0.0057,BMF
2011-01-11,-0.015,-0.0070,BMF
2011-01-12,0.013,0.0230,BMF
2011-01-13,-0.008,-0.0320,BMF
2011-01-14,0.041,0.0271,BMF
2011-01-17,-0.0065,-0.0169,BMF
2011-01-18,0.018,0.01082,BMF
2011-01-19,0.0017,-0.0105,BMF
2011-01-20,0.0262,0.03240,BMF
2011-01-21,-0.019,-0.02844,BMF
")
I might have misunderstood your requirements but this matches the expected output
priceDT[, adj_return := market[.SD, on = "Date", i.Return - x.Return]]