Problems with rollapply in regression

General
,
1

Hi all,

I am working with daily stock data and I am trying to compute the monthly betas in month t from daily stock data on t-11 month time window (e.g. the beta in Dec comprises daily stock data from Jan up to and including Dec). Additionally, I want to include a minimum of 150 observations in the regression equation.

I want to calculate the beta by computing the coefficients of a regression of excess stock returns on the excess market return over the past 12 months. My sample data lists the excess returns of each stock (the stock is classified by a number) and the market return mktrf in the last column.

03
03982×300 38.6 KB

I came up with the following code, but unfortunately I cannot find the error: I used width=252 for the past 12 months in days, but have not included prerequisite of a minimum of 150 observations in the code yet. I also have troubles with the NAs when the stock got delisted.

rollingbeta <- rollapply(joined_data,
                         width=252,
                         FUN = function(x) {
                           t = lm(formula=paste0(" ` ", x , " ` ~ mktrf"), data = joined_data, na.rm=T);
                           return(t$coef) },
                         by.column=TRUE, 
                          align="right")

print(coef(rollingbeta))

Ideally, I wish to seek the output in the same data format as my input table.

Any ideas on this? Would appreciate any help!

Here is my sample data created with dput 
structure(list(date = structure(c(16804, 16805, 16806, 16807, 
16808, 16811, 16812, 16813, 16814, 16815, 16819, 16820, 16821, 
16822, 16825, 16826, 16827, 16828, 16829, 16832, 16833, 16834, 
16835, 16836, 16839, 16840, 16841, 16842, 16843, 16847, 16848, 
16849, 16850, 16853, 16854, 16855, 16856, 16857, 16860, 16861, 
16862, 16863, 16864, 16867, 16868, 16869, 16870, 16871, 16874, 
16875, 16876, 16877, 16878, 16881, 16882, 16883, 16884, 16888, 
16889, 16890, 16891, 16892, 16895, 16896, 16897, 16898, 16899, 
16902, 16903, 16904, 16905, 16906, 16909, 16910, 16911, 16912, 
16913, 16916, 16917, 16918, 16919, 16920, 16923, 16924, 16925, 
16926, 16927, 16930, 16931, 16932, 16933, 16934, 16937, 16938, 
16939, 16940, 16941, 16944, 16945, 16946, 16947, 16948, 16952, 
16953, 16954, 16955, 16958, 16959, 16960, 16961, 16962, 16965, 
16966, 16967, 16968, 16969, 16972, 16973, 16974, 16975, 16976, 
16979, 16980, 16981, 16982, 16983, 16987, 16988, 16989, 16990, 
16993, 16994, 16995, 16996, 16997, 17000, 17001, 17002, 17003, 
17004, 17007, 17008, 17009, 17010, 17011, 17014, 17015, 17016, 
17017, 17018, 17021, 17022, 17023, 17024, 17025, 17028, 17029, 
17030, 17031, 17032, 17035, 17036, 17037, 17038, 17039, 17042, 
17043, 17044, 17045, 17046, 17050, 17051, 17052, 17053, 17056, 
17057, 17058, 17059, 17060, 17063, 17064, 17065, 17066, 17067, 
17070, 17071, 17072, 17073, 17074, 17077, 17078, 17079, 17080, 
17081, 17084, 17085, 17086, 17087, 17088, 17091, 17092, 17093, 
17094, 17095, 17098, 17099, 17100, 17101, 17102, 17105, 17106, 
17107, 17108, 17109, 17112, 17113, 17114, 17115, 17116, 17119, 
17120, 17121, 17122, 17123, 17126, 17127, 17128, 17130, 17133, 
17134, 17135, 17136, 17137, 17140, 17141, 17142, 17143, 17144, 
17147, 17148, 17149, 17150, 17151, 17154, 17155, 17156, 17157, 
17158, 17162, 17163, 17164, 17165, 17169, 17170, 17171, 17172, 
17175, 17176, 17177, 17178, 17179, 17183, 17184, 17185, 17186, 
17189, 17190, 17191, 17192, 17193, 17196, 17197, 17198, 17199, 
17200, 17203, 17204, 17205, 17206, 17207, 17210, 17211, 17212, 
17213, 17214, 17218, 17219, 17220, 17221, 17224, 17225, 17226, 
17227, 17228, 17231, 17232, 17233, 17234, 17235, 17238, 17239, 
17240, 17241, 17242, 17245, 17246, 17247, 17248, 17249, 17252, 
17253, 17254, 17255, 17256, 17259, 17260, 17261, 17262, 17263, 
17266, 17267, 17268, 17269, 17273, 17274, 17275, 17276, 17277, 
17280, 17281, 17282, 17283, 17284, 17287, 17288, 17289, 17290, 
17291, 17294, 17295, 17296, 17297, 17298, 17301, 17302, 17303, 
17304, 17305, 17308, 17309, 17310, 17311, 17312, 17316, 17317, 
17318, 17319, 17322, 17323, 17324, 17325, 17326, 17329, 17330, 
17331, 17332, 17333, 17336, 17337, 17338, 17339, 17340, 17343, 
17344, 17345, 17346, 17347, 17350, 17352, 17353, 17354, 17357, 
17358, 17359, 17360, 17361, 17364, 17365, 17366, 17367, 17368, 
17371, 17372, 17373, 17374, 17375, 17378, 17379, 17380, 17381
), class = "Date"), `10001` = c(NA, -0.0132978723404255, 0.0148247978436657, 
0.0146082337317397, 0.0196335078534031, 0.0346598202824133, 0.0235732009925558, 
0, -0.0145454545454544, -0.0172201722017221, 0.016270337922403, 
-0.00492610837438412, 0.00247524752475248, 0.0148148148148148, 
-0.02676399026764, 0.0125, 0.0123456790123457, 0, 0.0146341463414634, 
0.0120092307692306, 0.0082916627078385, -0.0270892353266823, 
-0.0314869975786925, 0.04249, 0.0347621822541966, -0.048677439165701, 
0.0109522411693058, -0.0301304819277109, 0.031045900621118, -0.00603409638554224, 
-0.0060706060606061, -0.0122051219512195, -0.0247013580246912, 
-0.00507329113924053, 0.0101681170483461, -0.00756667506297231, 
0.00760421319796951, -0.0126044584382872, 0.00254102040816335, 
0.0559696437659033, -0.0168774698795181, 0.00489196078431371, 
-1e-05, -0.00122951219512195, -0.0134410134310134, -0.0061981188118813, 
0.0111979701120797, -0.0147883251231526, -0.00250999999999995, 
-0.0100350626566416, 0.021508987341772, 0.00618578686493161, 
0.00984221674876846, -1e-05, -1e-05, 0.00486804878048781, -0.0194274757281553, 
-0.0222872277227723, -0.00127582278481024, -1e-05, -0.0101494169835235, 
-0.00128040973111387, -0.00384615384615383, -0.0694980694980694, 
-0.020746887966805, 0.0409604519774012, 0.0284938941655359, 0.025065963060686, 
0.012870012870013, -0.00889453621346892, -0.0256410256410257, 
-0.0197368421052631, -0.0308724832214766, 0.0124653739612188, 
-0.0177838577291382, -0.0250696378830083, 0.0257142857142856, 
0.00278551532033422, 0.0208333333333333, -0.00952380952380949, 
0.00686813186813184, -0.00409276944065484, -0.0534346575342465, 
0.0188033140376265, -1e-05, -0.00356113636363646, -0.0192544761225944, 
0.00144348837209291, 0.011601030478955, 0.0114677618364419, 0.00708219858156018, 
0.00703225352112675, -0.00420580419580423, 0.00279898876404478, 
-0.011214481792717, -0.0226728895184134, -0.00290855072463774, 
0.00289697674418605, -1e-05, 0.00723637681159424, 0.00142884892086321, 
0.014357816091954, 0.0113214447592068, -0.00421168067226878, 
-0.0140746976090015, 0.00712266761768905, 0.00282286118980186, 
-0.0198122598870057, 0.00866460157353239, -1e-05, 0.00141857142857133, 
-0.00571613409415122, -0.00718360114777621, 0.0158859537572254, 
0.00994732574679931, -0.0155029577464787, -0.0014406151645207, 
0.00428799426934086, 0.00284306704707566, -0.00427742532005697, 
-0.0128671428571428, -0.0029043560057888, 0.0145037880986939, 
-0.00430184549356232, 0.0043003448275863, 0.0128655364806867, 
0.0141142937853108, 0.00322119777158776, 0.000934024877832073, 
-0.00555785020804438, 0.00138470013947007, -0.00418827298050133, 
-0.0056044055944056, 0.00420940928270037, 0.00559224089635852, 
0.00416827298050133, 0.00692481276005548, -1e-05, -0.00965187327823685, 
-1e-05, -0.00974574408901257, 0.0126304494382022, 0.0013769625520111, 
-0.00693520775623262, -0.00977290097629018, 0.00703225352112675, 
0.00530468531468526, -0.00112296605453537, 0.00138275766016733, 
0.00346705146036148, 0.0145430145530146, -0.00171765027322406, 
0.00170057133082453, 0.0109189617486339, -0.00406405405405412, 
0.00677426051560388, -0.00405312668463613, 0.00540271989174552, 
0.00941126514131902, 0.00532333333333341, 0.00396877984084882, 
-0.00661501981505956, -0.0119780851063829, 0.00402768506056523, 
-1e-05, -1e-05, -0.00671241286863271, -0.00675763832658565, 0.0108595652173914, 
0.00267817204301075, -0.00135048257372654, -0.00672140939597314, 
0.0121521621621621, 0.0133411348464619, -0.00132752305665345, 
0.00526704485488134, 0.0104886876640421, -1e-05, 0.0285614285714285, 
-0.00884838383838387, 0.00381165605095541, 0.00125903553299496, 
-1e-05, -1e-05, -0.00381228136882121, -0.0254552926208651, -0.00131548302872058, 
0.00391156862745097, -0.00131208333333326, -0.0169591525423728, 
-0.00531503978779839, 0.0266566666666666, -0.0155944155844156, 
0.013182612137203, 0.647125416666667, -0.00554359683794463, -0.00159982511923689, 
-0.00319471337579629, 0.00158744408945683, -0.0151615151515151, 
0.00403858299595157, 0.00402225806451601, -0.00804212851405621, 
-1e-05, -1e-05, 0.0121357489878543, -0.00801000000000001, -1e-05, 
-0.00605838709677424, 0.00201839756592299, -1e-05, -0.00405858299595134, 
0.0162501626016259, -0.000810000000000023, -0.00721576461168931, 
-1e-05, 0.00402225806451601, 0.00802212851405633, -1e-05, 0.00795812749003983, 
-0.0118677075098815, 0.00799000000000001, -0.00397825396825384, 
0.00397406374501992, -1e-05, 0.00395825396825395, -0.00396256916996056, 
NA, -1e-05, -0.00794650793650791, -1e-05, -0.00401, 0.00400606425702816, 
-0.00401, 0.0120381927710843, 0.00395825396825395, -0.00396256916996056, 
0.00395825396825395, -1e-05, -1e-05, -0.0079151383399209, -0.00399406374502003, 
-0.00801000000000001, 0.00805451612903225, 0.00799000000000001, 
-0.00397825396825384, -1e-05, -1e-05, 0.00397406374501992, -0.00397825396825384, 
-1e-05, -1e-05, 0.00396406374501992, -0.00398825396825384, 0.0119321912350598, 
-2e-05, -0.00789401574803148, 0.0118847619047619, -0.00394156862745108, 
-0.00395700787401563, -2e-05, -0.00397256916996056, 0.00394031746031755, 
-1.20947991682851e-05, -2e-05, 0.00393256916996045, -0.00395700787401563, 
0.00393256916996045, -2e-05, -2e-05, -0.00395700787401563, -2e-05, 
-2e-05, 0.00393256916996045, -0.00395700787401563, -2e-05, -2e-05, 
-0.00397256916996056, -2e-05, 0.00394825396825395, -0.0079251383399209, 
0.00396406374501992, 0.00394825396825395, -0.00397256916996056, 
0.00394825396825395, -0.00397256916996056, 0.00791650793650791, 
-0.00395700787401563, -2e-05, 0.00393256916996045, -0.00395700787401563, 
-1e-05, -1e-05, -1e-05, 0.0078951383399209, -0.00393156862745108, 
-1e-05, -1e-05, -0.00394700787401563, -0.0079151383399209, 0.00397406374501992, 
-1e-05, -1e-05, 0.00792650793650791, -0.00394700787401563, 0.00394256916996045, 
-0.00394700787401563, 0.00394256916996045, -0.00394700787401563, 
-1e-05, -0.00317205533596847, 0.00117953211736716, -0.00199019801980198, 
0.00792650793650791, -0.00396700787401563, -0.00398256916996056, 
0.00393825396825395, -0.00319205533596847, -0.00082302141157811, 
-0.000823650793650791, -0.00320712470214448, 0.00395406374501992, 
-3e-05, -3e-05, -0.00320460317460305, 0.00315471337579607, -3e-05, 
-0.00399825396825384, -3e-05, -3e-05, -3e-05, -0.00401406374502003, 
-3e-05, -3e-05, 0.00597000000000001, -0.00201807157057646, 0.00395406374501992, 
0.00195412698412698, 0.00195019801980198, -3e-05, -0.00200628458498023, 
0.00195019801980198, -3e-05, 0.00234154150197627, -0.00239593059936907, 
-3e-05, 0.00392256916996045, -3e-05, -0.00396700787401563, -3e-05, 
0.00392256916996045, -0.00396700787401563, 0.00392256916996045, 
NA, -3e-05, -0.00396700787401563, -3e-05, -3e-05, 0.00392256916996045, 
-3e-05, -3e-05, -3e-05, -3e-05, 0.00391488188976383, -3.78430757405323e-05, 
-0.00395156862745108, -3e-05, 0.0078440157480317, 0.00387625, 
-3e-05, 0.00386105058365757, -0.00390596899224807, 0.00386105058365757, 
-3e-05, -3e-05, 0.00384596899224807, -0.00196050193050182, -0.00197423597678921, 
-4e-05, -4e-05, -4e-05, 0.00189798449612403, -0.00197423597678921, 
0.00771193798449614, -4e-05, -0.00580923076923075, 0.0018942359767891, 
-0.00390100386100374, -0.00197798449612403, -0.00198174757281551, 
0.0155242023346305, -4e-05, -0.0115342528735632, 0.00771193798449614, 
-4e-05, -4e-05, -0.00388615384615394, 0.00768200772200793, 0.00379141762452088, 
-4e-05), `10025` = c(NA, 0.0165323131575352, 0.0415322580645159, 
-0.0385856239514776, 0.00429530201342265, -0.0253942796043838, 
0.0388096544157981, 0.0665346534653466, -0.0403515286545365, 
0.012769250612666, -0.0468670402445236, 0.0756280064136825, -0.0214906832298137, 
0.0662688840929289, -0.0770329801166806, 0.0816563467492259, 
-0.0197972570065593, -0.022022143813116, 0.0529982582732023, 
-0.0170232325141776, -0.00710134615384621, -0.0113887677036679, 
-0.0204581449736745, -0.06151, -0.00214106020245069, 0.0628570581954083, 
-0.0365540160743438, -0.039373920750782, 0.0439520081411125, 
-0.0130071406290617, -0.0166020463523833, 0.00507837707552212, 
0.0254363096189714, -0.00429738469533585, -0.0193210647181629, 
0.0508149068653539, -0.00912623195745759, 0.00829564784053145, 
-0.00254453301229256, -0.00267802185236936, -0.019245668789809, 
0.0164856487855568, 0.0296347738308203, -0.0247059543310995, 
0.00749731645247482, 0.00807284920434432, -0.0791881508393885, 
-0.0138875510204081, 0.0182019205298013, -0.0586820867208672, 
-0.0418985850007197, 0.0632412019230769, -0.0797047859262399, 
0.0299301197604792, -0.0146194215861658, -0.0127180181543114, 
-0.0113492583512106, -0.00899946063236206, 0.0422170878949016, 
0.0032913205282113, -0.0128726981752916, -0.00939393939393951, 
-0.004435607219333, -0.0408664925487787, -0.0131347108761813, 
-0.0157441973705567, 0.0126978891820579, 0.0203549910438039, 
0.00175550590488349, 0.00573522383304126, -0.000158403294788667, 
0.00998098859315588, 0.0349803921568628, -0.0031827826614127, 
0.0165729055800516, -0.0355967693688303, 0.00589330024813894, 
-0.0325316065371569, 0.0419123505976096, -0.00428265524625271, 
-0.0319508448540705, -0.0220564900031736, 0.0413659532695117, 
-0.0166818603926458, -0.0164891633655522, 0.00578990333494451, 
-0.0171491958994074, -0.00343242503259449, 0.0183056173344237, 
-0.0298799213104224, -0.00530713623572248, 0.00864368613746043, 
-0.0306980052796568, -0.0178823404255319, 0.00674909878682846, 
0.0101466534687554, 0.0327098364008181, 0.0181418151815181, -0.000172074554295066, 
-0.0246493256605608, -0.0187901229848762, 0.00422441734417351, 
0.0121337004553888, 0.00315613897683737, 0.0170996345514949, 
-0.0199348734280582, 0.0453157790368271, -0.0049518141240235, 
0.0128064049983979, 0.0740172065801964, 0.104113711340206, -0.00227757369614516, 
0.0521290374331552, 0.0133318043202034, 0.00325018808777429, 
-0.013633297087864, 0.0553625291434364, -0.00109056189218397, 
0.00936499999999991, 0.014279116456299, -0.0167979783986852, 
-0.0219801492537314, -0.00831179465266745, 0.00589914686692101, 
-0.0153080051401297, 0.0886054610986827, -0.0469331647448338, 
-0.00060894585529463, 0.0252806628311158, 0.0155382815057284, 
0.00919916311730169, -0.00742416676172013, 0.057907720064353, 
-0.024993706278514, 0.0103509625668448, -0.0145650777373469, 
-0.0292144310171198, 0.00863453665283538, -0.0102945389098389, 
0.0119978512873802, -0.0212307644038791, 0.013744516843455, -0.0149576831091181, 
-0.0234721220964165, -0.0382600597657184, -0.0184042331593338, 
0.0101191466193973, -0.0353572048132365, 0.0141532016632016, 
0.0103679628443306, 0.00620354298757293, -0.0137466099558914, 
0.000117779197546474, -1e-05, -0.0182800907116391, 0.0318744351900052, 
-0.0379719119687225, 0.00208753539590984, 0.0177817320774463, 
-0.00926449871465289, -0.0141511520498184, 0.0085437570732992, 
0.00429584551148217, 0.442889831103027, -0.00658302359085188, 
0.00270911538112923, 0.00143626231582756, -0.00172495622348594, 
0.00179831826401451, 0.00531490974729249, -0.0021645919741448, 
0.00421852001799368, -0.0139859899659559, -0.0169098727966564, 
0.00775340110905727, 0.00099880410858406, -0.00605672469079259, 
0.0111431016683566, -0.00265357338195087, 0.00465136550589535, 
0.00235535662299864, 0.0133318043202034, -0.00323436184505148, 
0.00124797466079614, -0.002881758054384, 0.004310043200432, -0.00296725423425042, 
-0.013851452453712, -0.0031999380240612, -0.00695888909207281, 
-0.00599471595617356, 0.00730752500926275, 0.00228885057471257, 
-0.00514761467889906, 0.00607631501291024, -0.00367636113657193, 
0.010385584176633, -0.00246834471455886, 0.00774830595107707, 
0.00171085861787874, 0.00677119349005435, 0.00537841490794795, 
-0.000456627958910151, -1e-05, 0.00624558534405712, -0.0106671936056837, 
-0.0197586535008977, -0.000467875457875456, -0.000468085203848047, 
0.00411465627864345, -0.000466412596987699, -0.00594607305936084, 
0.00320543408360125, -0.0032151282051283, 0.0100956499770327, 
0.0131778126421101, 0.0152503231597845, -0.0238826790450927, 
0.0117653623188406, 0.00222813786929283, 0.00668941938365337, 
0.0026519343389529, 0.000432477876106073, -0.00177912870411328, 
0.00663599025254753, 0.000430140845070491, -0.00308963044434678, 
NA, -0.00441140845070425, 0.041988231653404, -1e-05, -0.0144351166737377, 
0.0034338226431337, -0.00215500214500213, 0.00901837489251929, 
-1e-05, 0.0102158201959948, 0.00125528890763389, 0.00715090985678175, 
-0.00837470096194064, 0.00125528890763389, 0.00209614995787695, 
0.00755620428751575, -0.00251312891113891, -0.00544705562526131, 
-0.00211260723296885, -0.00464548251158885, 0.0025302201524134, 
-0.0033883783783784, -0.00594220338983058, -0.00214128729752776, 
-0.00812618966253735, 0.00687061154177439, 0.00896203592814362, 
-0.00595471810089019, 0.00637658848614078, -2e-05, 0.00294610169491518, 
0.00589465990705542, -0.00631987400251997, 0.00674246830092975, 
-0.000859630562552429, 0.00670268907563023, -0.0818230050083473, 
-0.000474545454545374, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA), `93436` = c(NA, 
8.95215075422673e-05, -0.0196482119679542, -0.0154766252739225, 
-0.0215627173661025, -0.0149289099526067, 0.0101996632186674, 
-0.0460065723674811, 0.0293045779042485, -0.00577165583470751, 
-0.00131713742133766, -0.0294060179757718, 0.00639154504277806, 
0.0129019352902937, -0.0304616144161937, -0.0143599144515735, 
-0.0283632982021079, 0.0086669856968149, 0.00790722192936211, 
0.030010920502092, -0.0719100710876408, -0.0508908403545247, 
0.0106540534931981, -0.0726159430787659, -0.0898623985239852, 
0.00174687546455827, -0.0309037605396291, 0.0473206883831002, 
0.003778130524357, 0.02733375, 0.0870557988013148, -0.0113332155560825, 
-0.00114929363794452, 0.0669848373154039, -0.00299188365027574, 
0.0100910101010102, 0.0470849720670391, 0.0155157962972843, 0.00834347273300413, 
-0.0290830995675508, 0.0106688301583043, 0.0392806445789529, 
0.0270667344436496, 0.0211300716275367, -0.0131134146816699, 
0.0301973050345508, -0.0169705212725181, 0.011297144945901, 0.0368574698795182, 
0.0148168649779224, 0.0164322460382889, 0.020041367548326, 0.028084354624967, 
0.0239652513534415, -0.0171298388721047, -0.0497880054644809, 
0.023217603558271, 0.0110108562019758, -0.000574579171371427, 
-0.0140889988267501, 0.0126833756445855, 0.0340340340340339, 
0.0395639547118987, 0.0343333738208025, 0.0389478216620347, -0.0309697837389799, 
-0.0277216174183514, -0.000599832047026849, -0.00840268886043527, 
0.0270761036235978, -0.0104899226024436, 0.0105217184149924, 
-0.00247534478016576, -0.0256420356073735, 0.01051057120912, 
-0.00672080649677964, 0.0219904144347336, -0.00760591133004929, 
0.00762449368596618, -0.0089461653661228, -0.0149520817592556, 
-0.0280570021395988, 0.00430965442764581, -0.0392159553349877, 
-0.0420210192837465, -0.04956966930266, 0.0160633702075355, -0.0279725924719678, 
-0.00111089986597739, 0.00128378504001165, -0.00804981623277183, 
0.00158204940177537, 0.00326537209190298, -0.0174376249459887, 
0.0317988537085899, 0.0191215054221719, 0.0235483848334184, -0.0184410877065553, 
0.00780611321801878, 0.00765371437749536, 0.0252199845158939, 
-0.00924951670220335, 0.000841865136298313, -0.0164504425928414, 
-0.00274273820368003, 0.000127011326269754, 0.00770724736289325, 
0.0528266866050389, 0.0136768382542825, -0.0261648913043478, 
-0.0460947575863272, -0.00421494538141591, -0.0133665887914812, 
0.0127365574990695, 0.00104649977032618, -0.011298028265957, 
0.019621503225507, -0.00041964952207545, -0.104513437912663, 
-0.0013320787145326, -0.0165578615071283, 0.0279475459487444, 
0.01630830773105, 0.041617434461569, 0.00993338455682953, 0.019869404560015, 
-0.0116497228637414, 0.00213973361996451, 0.00698496362618923, 
0.00387996943595443, 0.0368937734108313, -0.000588343268974102, 
-0.00944690184731805, -0.00450377612007373, -0.00511088927007624, 
0.0265326497277677, -0.00438569060773479, 0.0137518751664743, 
-0.034429337887546, 0.00801721088435376, 0.0348125131596707, 
-0.00218381852962917, -0.0044542507951723, 0.00926830539629748, 
0.0181158401630457, -0.020368618339793, -0.0122268601365158, 
-0.00621598591549293, 0.0213372695867842, -0.00252506873075757, 
-0.0168338925357562, 0.0129012133003184, -0.0149829352191375, 
-0.00328941502326612, 0.00310235605353266, -9.86485528124314e-05, 
-0.00878698479542532, -0.00166466660703906, 0.00119946067013072, 
0.00665636839515022, -0.00920999999999999, 0.0085577118377967, 
-0.00988368795587973, -0.00746665259186057, -0.00439993482983341, 
-0.0217837169871358, -0.0179468029739776, 0.00316024699536295, 
-0.0530263671524927, -0.0149026632465011, 0.0255234209727982, 
-0.00553186560173551, -0.0215756140002974, -0.0146532914471019, 
0.0196845544299894, -0.0113564447806354, 0.00182626625860733, 
0.0204064757395244, 0.0248378195788844, 0.00456643622200576, 
-0.00824882911699147, 0.00282424550430023, 0.00588611149010816, 
0.00493114227583187, 0.00741347553627387, -0.0152260390449304, 
0.00222507118215838, -0.027013442090464, 0.0165819282511211, 
0.0473849909327058, -0.0107259569489939, -0.0139639283856014, 
-0.0357962419648853, -0.0218507960199004, 0.0220641569604801, 
-0.00423990793729783, 0.00703647676161918, -0.00631241675351095, 
-0.0186376468238115, -0.0129864388580733, 0.0264903093421323, 
0.0223908036162733, -0.0219200019650227, 0.00496237569060771, 
0.013333995202159, -0.00208141448017356, -0.000504217653454587, 
0.00874197784810111, -0.0198129508357433, -0.0112116802520378, 
-0.0351083664593132, -0.0145285806383982, -0.00320114987767266, 
0.0167438149610502, 0.0138963811922755, 0.00894398788882562, 
-0.0250433435929003, -0.024791647900663, 0.0173085864580523, 
-0.0377168307170132, 0.0127758914301462, 0.000860653534309262, 
0.0257063051160767, -0.0193039679847344, -0.00271241055021081, 
0.036029453717754, 0.010294964168018, NA, -0.00270514365624202, 
-0.0334079196410361, -0.000906766365986124, -0.0397143294614573, 
-0.0022642335605894, 0.0293612459359674, -0.00509565310492515, 
0.0392689884315308, -0.00446249805850378, -0.000582052628841746, 
0.00129086377354566, 0.0297150948396819, 0.00271520817562438, 
-0.00559659217877089, 0.0248406933900192, 0.00117524371573892, 
0.0298819745474275, -0.00523055654006415, 0.00360097737120852, 
0.0234488630366994, 0.0290047182900535, 0.00094658907666385, 
-0.0230372139801584, -0.00462151481274458, 0.0154229313491506, 
0.0460650730448408, -0.00107731530023356, 0.00994692392502752, 
0.00989223090694726, -0.00611650639916981, -0.000629039892113028, 
-0.000629411047751696, 0.0355216176662746, -0.00914723449001052, 
0.0117806621954325, 0.0226348078536666, 0.0039593239251722, 0.0171009087565889, 
0.0228387497991324, -0.000569860571069538, -0.00772228317679889, 
0.00172250524731701, -0.00919177307768335, 0.00516692893907358, 
-0.0106975691660381, 0.00924817525276835, -0.000894577618763639, 
0.0256036820116977, -0.00114503394498953, 0.0178454652788566, 
0.0271472771672773, 9.14413075780085e-05, 0.0422115492329979, 
0.00133424091233061, -0.00436194604598204, -0.0386602630826422, 
0.0121755753857594, 0.0189345604819452, -0.0140075265871155, 
-0.0640761588241746, 0.00392546661978978, -0.0419266147859922, 
0.0152502757584373, 0.00011000480019192, 0.0018298528117749, 
0.00434164484190365, -0.00144101323687234, -0.0104395211177899, 
-0.00692902329136329, -0.00798990845384211, -0.00495079216006538, 
0.0101668640485862, 0.0480462213104765, -0.00880844961240317, 
0.0247035650881791, -0.00210883609998096, 0.00159611854684523, 
-0.0429238668295663, 0.0172630173926918, -0.000911925414689586, 
0.0328811217521, 0.026817785377717, 0.0267459766116496, -0.00026229771129931, 
0.0019367878001298, 0.00135729994242946, 0.0726254078332733, 
0.0173222712046095, -0.0286766908133025, 0.0125123728813558, 
0.0128257080682961, 0.0325276783235273, -0.0118101466116073, 
-0.0384803255482492, 0.0240907384449536, -0.008451052631579, 
-0.00397771762208068, 0.0175220399666944, -0.00988205551191404, 
0.0101845383623682, 0.00792157068062813, 0.0186694773236375, 
-0.0115663778323082, -0.00499501918302872, 0.0175962838998154, 
0.0278618712388958, -0.0122345658705821, -0.0247093565179215, 
-0.0500589370458491, 0.0435968868882421, -0.0037919588130372, 
0.0457722722093818, 0.0122964645458507, -0.00654866428878911, 
0.00526247910863506, -0.0275229959052985, 0.00354730783841959, 
-0.0344137733825431, 0.0226742566397699, -0.00715323516258873, 
-0.00157425248528123, -0.0209418736909941, 0.0209006917659449, 
0.0212774592224871, 0.0261985768393145, NA, 0.0176065264100267, 
-0.00190677780710238, -0.00155774921409049, 0.0219502854200382, 
0.0158919163883451, 0.0192416451750035, 0.0287479785902962, -0.0343002702702703, 
0.00469965409157067, 0.0471553151722794, 0.0124982617369331, 
-0.0140057263699891, -0.0105271492513455, -0.00433802369413019, 
0.00656816116819911, 0.0111455856436706, 0.016468405951116, 0.00216544706097596, 
-0.0155730955796062, -0.0400840411666535, 0.0244477437425836, 
-0.0282866533778688, 0.00235392238392238, -0.0249010381350074, 
-0.0724408848051728, -0.0558656137454523, 0.0141749402583946, 
0.00899518293850958, 0.0353025090966619, 0.00698891021331209, 
-0.0185821218742412, 0.0134722599795923, -0.0250872878149978, 
0.0270902062146008, -0.00911872288569345, 0.0142869999385107, 
-0.00464717749757533, 0.0429563459196103, -0.00856504963213818, 
0.0124747232037692, -0.027348419368911, 0.00178383543622566, 
-0.0346596317187453, -0.01209675951402, 0.0197365747723504, 0.0650126251189052
), mktrf = c(-0.0159, 0.0012, -0.0135, -0.0244, -0.0111, -6e-04, 
0.0071, -0.0267, 0.0165, -0.0214, -0.002, -0.0094, 0.0045, 0.0208, 
-0.0171, 0.0152, -0.0111, 0.0049, 0.0257, -4e-04, -0.02, 0.0046, 
0.0027, -0.0206, -0.0151, -0.001, 1e-04, -0.0117, 0.0198, 0.0178, 
0.0175, -0.0051, 6e-04, 0.0143, -0.0122, 0.0053, 0.011, -1e-04, 
-0.0069, 0.0234, 0.0054, 0.0051, 0.0037, 0.0021, -0.0127, 0.005, 
-0.0011, 0.0169, -0.0012, -0.0036, 0.0063, 0.0072, 0.0054, 0.001, 
-5e-04, -0.0086, 0, 4e-04, 0.0107, 0.0041, -0.0011, 0.0064, -0.0041, 
-0.0094, 0.0114, -0.0123, 0.0029, -0.0028, 0.0099, 0.0123, 3e-04, 
-4e-04, 0.0065, 0.0029, 0.0015, -0.0047, 0.0012, -0.0024, 0.0029, 
0.0021, -0.0096, -0.005, 0.0078, -0.0105, -0.0066, -8e-04, 0.0038, 
5e-04, 0.0126, -0.0089, -0.0011, -0.0082, 0.0099, -0.0095, 0.001, 
-0.0033, 0.0075, -0.0018, 0.0143, 0.0072, -4e-04, 0.0049, -1e-04, 
0.002, 0.0035, -0.0038, 0.006, 0.0012, 0.0037, -0.0027, -0.0105, 
-0.0084, -0.0019, -0.0011, 0.0024, -0.003, 0.0071, 0.0021, -0.002, 
0.0145, -0.037, -0.0207, 0.0176, 0.018, 0.0142, 0.0024, -0.0085, 
0.0062, 2e-04, 0.016, 0.0043, 0.0075, -5e-04, 0.0051, -5e-04, 
0.0025, -0.0022, 0.0048, -0.0038, 0.0048, -0.0027, 0.0013, -0.0014, 
0.0018, 0.0017, -0.0016, -0.007, 0.0045, 5e-04, 0.0093, -6e-04, 
5e-04, -0.0029, 0.0054, -6e-04, 0.0039, -0.0057, 0.0012, 0.0032, 
-0.0011, -2e-04, 0.0027, -0.0056, -8e-04, -0.0015, 0.0053, -0.0014, 
-0.0024, 3e-04, 0.0053, 0.0026, 9e-04, -0.0018, -0.0247, 0.0144, 
-0.0148, -8e-04, 0.0107, -0.0036, 5e-04, -2e-04, 0.0112, 0.007, 
-0.006, -0.0088, 0.0064, 0.0056, -0.0098, 0.0088, -0.0026, -0.0046, 
0.0058, -6e-04, -0.0038, 0.0052, -0.013, 6e-04, -0.0042, 1e-04, 
-0.0029, 0.006, 0.0025, -0.0016, 2e-04, 0.0054, -0.0046, -0.0023, 
-0.0033, -0.0029, 2e-04, -0.0068, -0.0073, -0.004, -0.0012, 0.0223, 
0.004, 0.0146, 0.0032, 0.0018, 0.0021, 0.008, -0.0012, 0.0056, 
-0.0016, 0.0077, 0.0031, 0.0016, 0.004, -0.0064, 0.0011, -0.0025, 
-0.0036, 0, 0.0075, 0.0048, 0.0126, 0.0036, 0.0046, -0.003, 0.006, 
-0.0082, 0.0046, -0.0022, 0.0022, 0.0044, -0.0024, -0.003, 0.0019, 
0.0027, -0.0087, -4e-04, -0.0052, 0.0083, 0.0079, -0.0021, 0.0029, 
-0.0037, 0.0016, 0.0031, -0.003, 0.0029, -0.0049, 0.0024, -0.0038, 
0.0033, -0.0029, 0.0083, 0.0084, -0.001, -0.0012, -0.0068, 0, 
4e-04, -2e-04, 0.0082, -0.0027, -1e-04, 6e-04, 0.0072, 0.0038, 
0.0049, 0.0045, 0.0054, -0.0016, 0.002, 0.006, -0.0015, -0.0011, 
0.0015, 0.0022, -0.0042, 0.0147, -0.007, 9e-04, -0.0038, -0.0036, 
-0.0019, 4e-04, 0.0034, 0.0013, -0.0036, 0.0086, -6e-04, -8e-04, 
-0.0025, -0.0148, 0.0016, -3e-04, -5e-04, -5e-04, 0.0072, 0.0018, 
0.0036, -0.0016, -0.0028, 5e-04, -0.0044, 0.0032, -8e-04, 8e-04, 
-5e-04, -0.0049, -0.0075, 0.0089, -0.0025, -8e-04, 0.0083, -0.0028, 
0.0118, 0.0066, 4e-04, 5e-04, -0.003, 0.0021, 3e-04, -0.0019, 
2e-04, 0.0046, -4e-04, -5e-04, 0.0021, -0.0026, -0.0018, 0.0053, 
-3e-04, -0.0197, 0.004, 0.007, 0.0056, 0.0018, 0.0024, 0.0042, 
6e-04, -0.0019, -2e-04, 0.0095, 0.0035, -0.0017, -0.0028, 0.0015, 
0.0018, -0.0012, -0.0011, 0.0055, -0.0017, -0.003, -3e-04, 0.0086, 
-0.0073, -7e-04, 2e-04, 0.0024, 4e-04, -0.0085, 0.0102, -0.0083, 
0.0014, 0.0024, 0.0012, -0.0095, 0.007, 5e-04, 0, 0.0072, 0.0017, 
0.0042, 0, 4e-04, 0.0059, -1e-04, -8e-04, -3e-04, 0.0038, -6e-04, 
-0.0014, -0.0017, -0.0011, 0.0024, -8e-04, -0.0021)), row.names = c(NA, 
-400L), class = c("data.table", "data.frame"))
2

Hi @Pia. I am not familiar with the rollapply, so I suggest an alternative. If I don't get wrong, you want to have the coefficient of regression about each stock to mktrf with a window of 252, right? If yes, try the following code. Because of character limit, just show 200 lines result.

library(tidyverse)

joined_data %>%
  bind_cols(
    map(1:nrow(joined_data), ~{
      dat <- joined_data[.x : min(.x + 251, nrow(joined_data)),]
      sapply(select(dat, -date, -mktrf), function(x, mktrf){
        tryCatch({
          lm(x ~ mktrf, na.action = na.omit)$coefficients[2]
        },
        error = function(e){
          NA_integer_
        })
      }, mktrf = dat$mktrf)
    }) %>%
      do.call(rbind, .) %>%
      data.frame()
  )

#>           date         10001         10025         93436   mktrf
#> 1   2016-01-04            NA            NA            NA -0.0159
#> 2   2016-01-05 -1.329787e-02  0.0165323132  8.952151e-05  0.0012
#> 3   2016-01-06  1.482480e-02  0.0415322581 -1.964821e-02 -0.0135
#> 4   2016-01-07  1.460823e-02 -0.0385856240 -1.547663e-02 -0.0244
#> 5   2016-01-08  1.963351e-02  0.0042953020 -2.156272e-02 -0.0111
#> 6   2016-01-11  3.465982e-02 -0.0253942796 -1.492891e-02 -0.0006
#> 7   2016-01-12  2.357320e-02  0.0388096544  1.019966e-02  0.0071
#> 8   2016-01-13  0.000000e+00  0.0665346535 -4.600657e-02 -0.0267
#> 9   2016-01-14 -1.454545e-02 -0.0403515287  2.930458e-02  0.0165
#> 10  2016-01-15 -1.722017e-02  0.0127692506 -5.771656e-03 -0.0214
#> 11  2016-01-19  1.627034e-02 -0.0468670402 -1.317137e-03 -0.0020
#> 12  2016-01-20 -4.926108e-03  0.0756280064 -2.940602e-02 -0.0094
#> 13  2016-01-21  2.475248e-03 -0.0214906832  6.391545e-03  0.0045
#> 14  2016-01-22  1.481481e-02  0.0662688841  1.290194e-02  0.0208
#> 15  2016-01-25 -2.676399e-02 -0.0770329801 -3.046161e-02 -0.0171
#> 16  2016-01-26  1.250000e-02  0.0816563467 -1.435991e-02  0.0152
#> 17  2016-01-27  1.234568e-02 -0.0197972570 -2.836330e-02 -0.0111
#> 18  2016-01-28  0.000000e+00 -0.0220221438  8.666986e-03  0.0049
#> 19  2016-01-29  1.463415e-02  0.0529982583  7.907222e-03  0.0257
#> 20  2016-02-01  1.200923e-02 -0.0170232325  3.001092e-02 -0.0004
#> 21  2016-02-02  8.291663e-03 -0.0071013462 -7.191007e-02 -0.0200
#> 22  2016-02-03 -2.708924e-02 -0.0113887677 -5.089084e-02  0.0046
#> 23  2016-02-04 -3.148700e-02 -0.0204581450  1.065405e-02  0.0027
#> 24  2016-02-05  4.249000e-02 -0.0615100000 -7.261594e-02 -0.0206
#> 25  2016-02-08  3.476218e-02 -0.0021410602 -8.986240e-02 -0.0151
#> 26  2016-02-09 -4.867744e-02  0.0628570582  1.746875e-03 -0.0010
#> 27  2016-02-10  1.095224e-02 -0.0365540161 -3.090376e-02  0.0001
#> 28  2016-02-11 -3.013048e-02 -0.0393739208  4.732069e-02 -0.0117
#> 29  2016-02-12  3.104590e-02  0.0439520081  3.778131e-03  0.0198
#> 30  2016-02-16 -6.034096e-03 -0.0130071406  2.733375e-02  0.0178
#> 31  2016-02-17 -6.070606e-03 -0.0166020464  8.705580e-02  0.0175
#> 32  2016-02-18 -1.220512e-02  0.0050783771 -1.133322e-02 -0.0051
#> 33  2016-02-19 -2.470136e-02  0.0254363096 -1.149294e-03  0.0006
#> 34  2016-02-22 -5.073291e-03 -0.0042973847  6.698484e-02  0.0143
#> 35  2016-02-23  1.016812e-02 -0.0193210647 -2.991884e-03 -0.0122
#> 36  2016-02-24 -7.566675e-03  0.0508149069  1.009101e-02  0.0053
#> 37  2016-02-25  7.604213e-03 -0.0091262320  4.708497e-02  0.0110
#> 38  2016-02-26 -1.260446e-02  0.0082956478  1.551580e-02 -0.0001
#> 39  2016-02-29  2.541020e-03 -0.0025445330  8.343473e-03 -0.0069
#> 40  2016-03-01  5.596964e-02 -0.0026780219 -2.908310e-02  0.0234
#> 41  2016-03-02 -1.687747e-02 -0.0192456688  1.066883e-02  0.0054
#> 42  2016-03-03  4.891961e-03  0.0164856488  3.928064e-02  0.0051
#> 43  2016-03-04 -1.000000e-05  0.0296347738  2.706673e-02  0.0037
#> 44  2016-03-07 -1.229512e-03 -0.0247059543  2.113007e-02  0.0021
#> 45  2016-03-08 -1.344101e-02  0.0074973165 -1.311341e-02 -0.0127
#> 46  2016-03-09 -6.198119e-03  0.0080728492  3.019731e-02  0.0050
#> 47  2016-03-10  1.119797e-02 -0.0791881508 -1.697052e-02 -0.0011
#> 48  2016-03-11 -1.478833e-02 -0.0138875510  1.129714e-02  0.0169
#> 49  2016-03-14 -2.510000e-03  0.0182019205  3.685747e-02 -0.0012
#> 50  2016-03-15 -1.003506e-02 -0.0586820867  1.481686e-02 -0.0036
#> 51  2016-03-16  2.150899e-02 -0.0418985850  1.643225e-02  0.0063
#> 52  2016-03-17  6.185787e-03  0.0632412019  2.004137e-02  0.0072
#> 53  2016-03-18  9.842217e-03 -0.0797047859  2.808435e-02  0.0054
#> 54  2016-03-21 -1.000000e-05  0.0299301198  2.396525e-02  0.0010
#> 55  2016-03-22 -1.000000e-05 -0.0146194216 -1.712984e-02 -0.0005
#> 56  2016-03-23  4.868049e-03 -0.0127180182 -4.978801e-02 -0.0086
#> 57  2016-03-24 -1.942748e-02 -0.0113492584  2.321760e-02  0.0000
#> 58  2016-03-28 -2.228723e-02 -0.0089994606  1.101086e-02  0.0004
#> 59  2016-03-29 -1.275823e-03  0.0422170879 -5.745792e-04  0.0107
#> 60  2016-03-30 -1.000000e-05  0.0032913205 -1.408900e-02  0.0041
#> 61  2016-03-31 -1.014942e-02 -0.0128726982  1.268338e-02 -0.0011
#> 62  2016-04-01 -1.280410e-03 -0.0093939394  3.403403e-02  0.0064
#> 63  2016-04-04 -3.846154e-03 -0.0044356072  3.956395e-02 -0.0041
#> 64  2016-04-05 -6.949807e-02 -0.0408664925  3.433337e-02 -0.0094
#> 65  2016-04-06 -2.074689e-02 -0.0131347109  3.894782e-02  0.0114
#> 66  2016-04-07  4.096045e-02 -0.0157441974 -3.096978e-02 -0.0123
#> 67  2016-04-08  2.849389e-02  0.0126978892 -2.772162e-02  0.0029
#> 68  2016-04-11  2.506596e-02  0.0203549910 -5.998320e-04 -0.0028
#> 69  2016-04-12  1.287001e-02  0.0017555059 -8.402689e-03  0.0099
#> 70  2016-04-13 -8.894536e-03  0.0057352238  2.707610e-02  0.0123
#> 71  2016-04-14 -2.564103e-02 -0.0001584033 -1.048992e-02  0.0003
#> 72  2016-04-15 -1.973684e-02  0.0099809886  1.052172e-02 -0.0004
#> 73  2016-04-18 -3.087248e-02  0.0349803922 -2.475345e-03  0.0065
#> 74  2016-04-19  1.246537e-02 -0.0031827827 -2.564204e-02  0.0029
#> 75  2016-04-20 -1.778386e-02  0.0165729056  1.051057e-02  0.0015
#> 76  2016-04-21 -2.506964e-02 -0.0355967694 -6.720806e-03 -0.0047
#> 77  2016-04-22  2.571429e-02  0.0058933002  2.199041e-02  0.0012
#> 78  2016-04-25  2.785515e-03 -0.0325316065 -7.605911e-03 -0.0024
#> 79  2016-04-26  2.083333e-02  0.0419123506  7.624494e-03  0.0029
#> 80  2016-04-27 -9.523810e-03 -0.0042826552 -8.946165e-03  0.0021
#> 81  2016-04-28  6.868132e-03 -0.0319508449 -1.495208e-02 -0.0096
#> 82  2016-04-29 -4.092769e-03 -0.0220564900 -2.805700e-02 -0.0050
#> 83  2016-05-02 -5.343466e-02  0.0413659533  4.309654e-03  0.0078
#> 84  2016-05-03  1.880331e-02 -0.0166818604 -3.921596e-02 -0.0105
#> 85  2016-05-04 -1.000000e-05 -0.0164891634 -4.202102e-02 -0.0066
#> 86  2016-05-05 -3.561136e-03  0.0057899033 -4.956967e-02 -0.0008
#> 87  2016-05-06 -1.925448e-02 -0.0171491959  1.606337e-02  0.0038
#> 88  2016-05-09  1.443488e-03 -0.0034324250 -2.797259e-02  0.0005
#> 89  2016-05-10  1.160103e-02  0.0183056173 -1.110900e-03  0.0126
#> 90  2016-05-11  1.146776e-02 -0.0298799213  1.283785e-03 -0.0089
#> 91  2016-05-12  7.082199e-03 -0.0053071362 -8.049816e-03 -0.0011
#> 92  2016-05-13  7.032254e-03  0.0086436861  1.582049e-03 -0.0082
#> 93  2016-05-16 -4.205804e-03 -0.0306980053  3.265372e-03  0.0099
#> 94  2016-05-17  2.798989e-03 -0.0178823404 -1.743762e-02 -0.0095
#> 95  2016-05-18 -1.121448e-02  0.0067490988  3.179885e-02  0.0010
#> 96  2016-05-19 -2.267289e-02  0.0101466535  1.912151e-02 -0.0033
#> 97  2016-05-20 -2.908551e-03  0.0327098364  2.354838e-02  0.0075
#> 98  2016-05-23  2.896977e-03  0.0181418152 -1.844109e-02 -0.0018
#> 99  2016-05-24 -1.000000e-05 -0.0001720746  7.806113e-03  0.0143
#> 100 2016-05-25  7.236377e-03 -0.0246493257  7.653714e-03  0.0072
#> 101 2016-05-26  1.428849e-03 -0.0187901230  2.521998e-02 -0.0004
#> 102 2016-05-27  1.435782e-02  0.0042244173 -9.249517e-03  0.0049
#> 103 2016-05-31  1.132144e-02  0.0121337005  8.418651e-04 -0.0001
#> 104 2016-06-01 -4.211681e-03  0.0031561390 -1.645044e-02  0.0020
#> 105 2016-06-02 -1.407470e-02  0.0170996346 -2.742738e-03  0.0035
#> 106 2016-06-03  7.122668e-03 -0.0199348734  1.270113e-04 -0.0038
#> 107 2016-06-06  2.822861e-03  0.0453157790  7.707247e-03  0.0060
#> 108 2016-06-07 -1.981226e-02 -0.0049518141  5.282669e-02  0.0012
#> 109 2016-06-08  8.664602e-03  0.0128064050  1.367684e-02  0.0037
#> 110 2016-06-09 -1.000000e-05  0.0740172066 -2.616489e-02 -0.0027
#> 111 2016-06-10  1.418571e-03  0.1041137113 -4.609476e-02 -0.0105
#> 112 2016-06-13 -5.716134e-03 -0.0022775737 -4.214945e-03 -0.0084
#> 113 2016-06-14 -7.183601e-03  0.0521290374 -1.336659e-02 -0.0019
#> 114 2016-06-15  1.588595e-02  0.0133318043  1.273656e-02 -0.0011
#> 115 2016-06-16  9.947326e-03  0.0032501881  1.046500e-03  0.0024
#> 116 2016-06-17 -1.550296e-02 -0.0136332971 -1.129803e-02 -0.0030
#> 117 2016-06-20 -1.440615e-03  0.0553625291  1.962150e-02  0.0071
#> 118 2016-06-21  4.287994e-03 -0.0010905619 -4.196495e-04  0.0021
#> 119 2016-06-22  2.843067e-03  0.0093650000 -1.045134e-01 -0.0020
#> 120 2016-06-23 -4.277425e-03  0.0142791165 -1.332079e-03  0.0145
#> 121 2016-06-24 -1.286714e-02 -0.0167979784 -1.655786e-02 -0.0370
#> 122 2016-06-27 -2.904356e-03 -0.0219801493  2.794755e-02 -0.0207
#> 123 2016-06-28  1.450379e-02 -0.0083117947  1.630831e-02  0.0176
#> 124 2016-06-29 -4.301845e-03  0.0058991469  4.161743e-02  0.0180
#> 125 2016-06-30  4.300345e-03 -0.0153080051  9.933385e-03  0.0142
#> 126 2016-07-01  1.286554e-02  0.0886054611  1.986940e-02  0.0024
#> 127 2016-07-05  1.411429e-02 -0.0469331647 -1.164972e-02 -0.0085
#> 128 2016-07-06  3.221198e-03 -0.0006089459  2.139734e-03  0.0062
#> 129 2016-07-07  9.340249e-04  0.0252806628  6.984964e-03  0.0002
#> 130 2016-07-08 -5.557850e-03  0.0155382815  3.879969e-03  0.0160
#> 131 2016-07-11  1.384700e-03  0.0091991631  3.689377e-02  0.0043
#> 132 2016-07-12 -4.188273e-03 -0.0074241668 -5.883433e-04  0.0075
#> 133 2016-07-13 -5.604406e-03  0.0579077201 -9.446902e-03 -0.0005
#> 134 2016-07-14  4.209409e-03 -0.0249937063 -4.503776e-03  0.0051
#> 135 2016-07-15  5.592241e-03  0.0103509626 -5.110889e-03 -0.0005
#> 136 2016-07-18  4.168273e-03 -0.0145650777  2.653265e-02  0.0025
#> 137 2016-07-19  6.924813e-03 -0.0292144310 -4.385691e-03 -0.0022
#> 138 2016-07-20 -1.000000e-05  0.0086345367  1.375188e-02  0.0048
#> 139 2016-07-21 -9.651873e-03 -0.0102945389 -3.442934e-02 -0.0038
#> 140 2016-07-22 -1.000000e-05  0.0119978513  8.017211e-03  0.0048
#> 141 2016-07-25 -9.745744e-03 -0.0212307644  3.481251e-02 -0.0027
#> 142 2016-07-26  1.263045e-02  0.0137445168 -2.183819e-03  0.0013
#> 143 2016-07-27  1.376963e-03 -0.0149576831 -4.454251e-03 -0.0014
#> 144 2016-07-28 -6.935208e-03 -0.0234721221  9.268305e-03  0.0018
#> 145 2016-07-29 -9.772901e-03 -0.0382600598  1.811584e-02  0.0017
#> 146 2016-08-01  7.032254e-03 -0.0184042332 -2.036862e-02 -0.0016
#> 147 2016-08-02  5.304685e-03  0.0101191466 -1.222686e-02 -0.0070
#> 148 2016-08-03 -1.122966e-03 -0.0353572048 -6.215986e-03  0.0045
#> 149 2016-08-04  1.382758e-03  0.0141532017  2.133727e-02  0.0005
#> 150 2016-08-05  3.467051e-03  0.0103679628 -2.525069e-03  0.0093
#> 151 2016-08-08  1.454301e-02  0.0062035430 -1.683389e-02 -0.0006
#> 152 2016-08-09 -1.717650e-03 -0.0137466100  1.290121e-02  0.0005
#> 153 2016-08-10  1.700571e-03  0.0001177792 -1.498294e-02 -0.0029
#> 154 2016-08-11  1.091896e-02 -0.0000100000 -3.289415e-03  0.0054
#> 155 2016-08-12 -4.064054e-03 -0.0182800907  3.102356e-03 -0.0006
#> 156 2016-08-15  6.774261e-03  0.0318744352 -9.864855e-05  0.0039
#> 157 2016-08-16 -4.053127e-03 -0.0379719120 -8.786985e-03 -0.0057
#> 158 2016-08-17  5.402720e-03  0.0020875354 -1.664667e-03  0.0012
#> 159 2016-08-18  9.411265e-03  0.0177817321  1.199461e-03  0.0032
#> 160 2016-08-19  5.323333e-03 -0.0092644987  6.656368e-03 -0.0011
#> 161 2016-08-22  3.968780e-03 -0.0141511520 -9.210000e-03 -0.0002
#> 162 2016-08-23 -6.615020e-03  0.0085437571  8.557712e-03  0.0027
#> 163 2016-08-24 -1.197809e-02  0.0042958455 -9.883688e-03 -0.0056
#> 164 2016-08-25  4.027685e-03  0.4428898311 -7.466653e-03 -0.0008
#> 165 2016-08-26 -1.000000e-05 -0.0065830236 -4.399935e-03 -0.0015
#> 166 2016-08-29 -1.000000e-05  0.0027091154 -2.178372e-02  0.0053
#> 167 2016-08-30 -6.712413e-03  0.0014362623 -1.794680e-02 -0.0014
#> 168 2016-08-31 -6.757638e-03 -0.0017249562  3.160247e-03 -0.0024
#> 169 2016-09-01  1.085957e-02  0.0017983183 -5.302637e-02  0.0003
#> 170 2016-09-02  2.678172e-03  0.0053149097 -1.490266e-02  0.0053
#> 171 2016-09-06 -1.350483e-03 -0.0021645920  2.552342e-02  0.0026
#> 172 2016-09-07 -6.721409e-03  0.0042185200 -5.531866e-03  0.0009
#> 173 2016-09-08  1.215216e-02 -0.0139859900 -2.157561e-02 -0.0018
#> 174 2016-09-09  1.334113e-02 -0.0169098728 -1.465329e-02 -0.0247
#> 175 2016-09-12 -1.327523e-03  0.0077534011  1.968455e-02  0.0144
#> 176 2016-09-13  5.267045e-03  0.0009988041 -1.135644e-02 -0.0148
#> 177 2016-09-14  1.048869e-02 -0.0060567247  1.826266e-03 -0.0008
#> 178 2016-09-15 -1.000000e-05  0.0111431017  2.040648e-02  0.0107
#> 179 2016-09-16  2.856143e-02 -0.0026535734  2.483782e-02 -0.0036
#> 180 2016-09-19 -8.848384e-03  0.0046513655  4.566436e-03  0.0005
#> 181 2016-09-20  3.811656e-03  0.0023553566 -8.248829e-03 -0.0002
#> 182 2016-09-21  1.259036e-03  0.0133318043  2.824246e-03  0.0112
#> 183 2016-09-22 -1.000000e-05 -0.0032343618  5.886111e-03  0.0070
#> 184 2016-09-23 -1.000000e-05  0.0012479747  4.931142e-03 -0.0060
#> 185 2016-09-26 -3.812281e-03 -0.0028817581  7.413476e-03 -0.0088
#> 186 2016-09-27 -2.545529e-02  0.0043100432 -1.522604e-02  0.0064
#> 187 2016-09-28 -1.315483e-03 -0.0029672542  2.225071e-03  0.0056
#> 188 2016-09-29  3.911569e-03 -0.0138514525 -2.701344e-02 -0.0098
#> 189 2016-09-30 -1.312083e-03 -0.0031999380  1.658193e-02  0.0088
#> 190 2016-10-03 -1.695915e-02 -0.0069588891  4.738499e-02 -0.0026
#> 191 2016-10-04 -5.315040e-03 -0.0059947160 -1.072596e-02 -0.0046
#> 192 2016-10-05  2.665667e-02  0.0073075250 -1.396393e-02  0.0058
#> 193 2016-10-06 -1.559442e-02  0.0022888506 -3.579624e-02 -0.0006
#> 194 2016-10-07  1.318261e-02 -0.0051476147 -2.185080e-02 -0.0038
#> 195 2016-10-10  6.471254e-01  0.0060763150  2.206416e-02  0.0052
#> 196 2016-10-11 -5.543597e-03 -0.0036763611 -4.239908e-03 -0.0130
#> 197 2016-10-12 -1.599825e-03  0.0103855842  7.036477e-03  0.0006
#> 198 2016-10-13 -3.194713e-03 -0.0024683447 -6.312417e-03 -0.0042
#> 199 2016-10-14  1.587444e-03  0.0077483060 -1.863765e-02  0.0001
#> 200 2016-10-17 -1.516152e-02  0.0017108586 -1.298644e-02 -0.0029
#>      X10001.mktrf X10025.mktrf X93436.mktrf
#> 1    0.1989125245    0.5925272    1.1584519
#> 2    0.1987861983    0.5924616    1.1611492
#> 3    0.1960580953    0.5929003    1.1759468
#> 4    0.2061070343    0.6308567    1.1730960
#> 5    0.2300636761    0.5954000    1.1933859
#> 6    0.2459438907    0.6014402    1.1842255
#> 7    0.2489972841    0.5992368    1.1827594
#> 8    0.2410550519    0.5877880    1.1814635
#> 9    0.2493317111    0.7220120    1.1574426
#> 10   0.2687401840    0.7734328    1.1514900
#> 11   0.2514381041    0.8115759    1.1792972
#> 12   0.2542035615    0.8032887    1.1803950
#> 13   0.2514925882    0.8804119    1.1604635
#> 14   0.2512353140    0.8856573    1.1602151
#> 15   0.2411627141    0.8255696    1.1695285
#> 16   0.2078343481    0.7496978    1.1634214
#> 17   0.2011294494    0.6838051    1.1886177
#> 18   0.2113338227    0.6730598    1.1771171
#> 19   0.2124732755    0.6801454    1.1759942
#> 20   0.2029430160    0.6200563    1.2157005
#> 21   0.2039118290    0.6185099    1.2179899
#> 22   0.2193338516    0.6247127    1.1469455
#> 23   0.2272113981    0.6288627    1.1612913
#> 24   0.2274318684    0.6319372    1.1546539
#> 25   0.2993794449    0.5510538    1.0683373
#> 26   0.3447860794    0.5567255    0.9778153
#> 27   0.3372657302    0.5664891    0.9774932
#> 28   0.3358684686    0.5636920    0.9837214
#> 29   0.3085679186    0.5295660    1.0384380
#> 30   0.2718166435    0.4818510    1.0754853
#> 31   0.2892137995    0.5137277    1.0651347
#> 32   0.3070182597    0.5515564    0.9710833
#> 33   0.3018851362    0.5546731    0.9753974
#> 34   0.3011183375    0.5553016    0.9760548
#> 35   0.3105533526    0.5711310    0.9239537
#> 36   0.3222094775    0.5552658    0.9348210
#> 37   0.3273665318    0.5376744    0.9444303
#> 38   0.3257185888    0.5523575    0.9139128
#> 39   0.3245170067    0.5529248    0.9105567
#> 40   0.3283143447    0.5527309    0.9118864
#> 41   0.2298290668    0.5884189    0.9947539
#> 42   0.2381037161    0.5986629    0.9870989
#> 43   0.2375439723    0.5939570    0.9746606
#> 44   0.2356407355    0.5869069    0.9681128
#> 45   0.2380971842    0.5903760    0.9687396
#> 46   0.2234992852    0.6072789    0.9688917
#> 47   0.2270986595    0.6059089    0.9599513
#> 48   0.2271022612    0.5916987    0.9553870
#> 49   0.2568561944    0.6320846    0.9628462
#> 50   0.2550174154    0.6352178    0.9493403
#> 51   0.2473956522    0.6107238    0.9446193
#> 52   0.2389450791    0.6367461    0.9362565
#> 53   0.2368123932    0.6010642    0.9287757
#> 54   0.2357849186    0.6404841    0.9179251
#> 55   0.2286964134    0.6395932    0.9572494
#> 56   0.2279443916    0.6376872    0.9568440
#> 57   0.2316087958    0.6287134    0.9225857
#> 58   0.2308879601    0.6277352    0.9204610
#> 59   0.2304420391    0.6273674    0.9178239
#> 60   0.2317145330    0.5946577    0.9388748
#> 61   0.2325664816    0.5952573    0.9442084
#> 62   0.2292484534    0.5926642    0.9459234
#> 63   0.2306774908    0.6018181    0.9311312
#> 64   0.2301875021    0.5995711    0.9262333
#> 65   0.1663776112    0.5607168    0.9651105
#> 66   0.1903692660    0.5855120    0.9491592
#> 67   0.2377019073    0.5697374    0.9281478
#> 68   0.2330114332    0.5679313    0.9324814
#> 69   0.2405682431    0.5748460    0.9333058
#> 70   0.2341611588    0.5816070    0.9501270
#> 71   0.2482882884    0.5871912    0.9513229
#> 72   0.2479777832    0.5870766    0.9274949
#> 73   0.2422335865    0.5878530    0.9152956
#> 74   0.2631155428    0.5700361    0.9206625
#> 75   0.2610502381    0.5719688    0.9246275
#> 76   0.2593472608    0.5706982    0.9109649
#> 77   0.2481330464    0.5502723    0.9056664
#> 78   0.2410518056    0.5501333    0.9013259
#> 79   0.2389015063    0.5390008    0.9064398
#> 80   0.2353763837    0.5298094    0.9057951
#> 81   0.2371453135    0.5311868    0.9073623
#> 82   0.2440388860    0.4976848    0.8942489
#> 83   0.2407465121    0.4834658    0.8847604
#> 84   0.2791322929    0.4565526    0.8874040
#> 85   0.3002626432    0.4376018    0.8608924
#> 86   0.2996993604    0.4236129    0.8377021
#> 87   0.2980333188    0.4240751    0.8445691
#> 88   0.3047958632    0.4317338    0.8416171
#> 89   0.3050771066    0.4315427    0.8355886
#> 90   0.2986668198    0.4189377    0.8515667
#> 91   0.3092880978    0.3859141    0.8604400
#> 92   0.3106556300    0.3840823    0.8576012
#> 93   0.3158251186    0.3924307    0.8492907
#> 94   0.3253045853    0.4338241    0.8546848
#> 95   0.3212027109    0.4120809    0.8809559
#> 96   0.3215900096    0.4120247    0.8857796
#> 97   0.3092621150    0.4156785    0.8852783
#> 98   0.3104964381    0.3950310    0.8715457
#> 99   0.3103215816    0.3996898    0.8649044
#> 100  0.3199085103    0.4158596    0.8749675
#> 101  0.3157983097    0.4412611    0.8803754
#> 102  0.3156591592    0.4382543    0.8827657
#> 103  0.3115371840    0.4393489    0.8887437
#> 104  0.3124290211    0.4400967    0.8872920
#> 105  0.3051875298    0.4404058    0.8799734
#> 106  0.3089704595    0.4363358    0.8802926
#> 107  0.3119314178    0.4239654    0.8760779
#> 108  0.3121286739    0.3980950    0.8696592
#> 109  0.3130103944    0.3987783    0.8684665
#> 110  0.3112631061    0.3959539    0.8685667
#> 111  0.3110699977    0.4254667    0.8666473
#> 112  0.3136270526    0.5748536    0.8237060
#> 113  0.3080743315    0.5746494    0.8441365
#> 114  0.3061501186    0.5919726    0.8375661
#> 115  0.3108110631    0.5947806    0.8446900
#> 116  0.3100423610    0.5948803    0.8463393
#> 117  0.3054843052    0.5879578    0.8324401
#> 118  0.3068201550    0.5478584    0.8160329
#> 119  0.3070791432    0.5486210    0.8150762
#> 120  0.3072042569    0.5516066    0.7860816
#> 121  0.3218483754    0.5438647    0.8061993
#> 122  0.3052002368    0.5518218    0.8564672
#> 123  0.3070387602    0.5030414    1.0116239
#> 124  0.2869380588    0.5631993    1.0326086
#> 125  0.3100813256    0.5852248    1.0097737
#> 126  0.3148279880    0.6566820    1.0199922
#> 127  0.3115384247    0.6277986    1.0100679
#> 128  0.3285566799    0.5433959    1.0003393
#> 129  0.3291244683    0.5504602    1.0674296
#> 130  0.3248527906    0.5532022    1.0727503
#> 131  0.3527419095    0.5375467    1.1016391
#> 132  0.3545602106    0.5336060    1.0831179
#> 133  0.3656196372    0.5544646    1.0905284
#> 134  0.3638646231    0.5680925    1.0859764
#> 135  0.3592413583    0.5983886    1.0964414
#> 136  0.3597854365    0.6004778    1.0978636
#> 137  0.3598625032    0.6083689    1.0912225
#> 138  0.3570045775    0.5897540    1.0785578
#> 139  0.3599296199    0.5862553    1.0727768
#> 140  0.3506937474    0.5760849    1.0548102
#> 141  0.3535518732    0.5692684    1.0482278
#> 142  0.3415872126    0.5529224    1.0591796
#> 143  0.3398747119    0.5510947    1.0574680
#> 144  0.3402718965    0.5436534    1.0638854
#> 145  0.3425838264    0.5513287    1.0619800
#> 146  0.3459636550    0.5624105    1.0678221
#> 147  0.3484074318    0.5522018    1.0582784
#> 148  0.3533786210    0.5713982    1.0484996
#> 149  0.3570448887    0.6088231    1.0296075
#> 150  0.3570132130    0.6090607    1.0300083
#> 151  0.3601766135    0.6061713    1.0457963
#> 152  0.3622011474    0.6070483    1.0428820
#> 153  0.3621251560    0.6069194    1.0430670
#> 154  0.3621259559    0.6056312    1.0366723
#> 155  0.3579773659    0.6135225    1.0433533
#> 156  0.3568601187    0.6077161    1.0437901
#> 157  0.3556499898    0.5865711    1.0462374
#> 158  0.3514080213    0.5286111    1.0426886
#> 159  0.3512301465    0.5289932    1.0430180
#> 160  0.3492219052    0.5210405    1.0442312
#> 161  0.3499430927    0.5160575    1.0458300
#> 162  0.3500965019    0.5127907    1.0446428
#> 163  0.3531065083    0.5111546    1.0433229
#> 164  0.3418259033    0.5158958    1.0386780
#> 165  0.3421304646    0.6674725    1.0370591
#> 166  0.3414229928    0.6647973    1.0357983
#> 167  0.3444973066    0.6652632    1.0547108
#> 168  0.3418523369    0.6666400    1.0495000
#> 169  0.3380048062    0.6668792    1.0513304
#> 170  0.3384120803    0.6670463    1.0484814
#> 171  0.3397450587    0.6643909    1.0632195
#> 172  0.3411851695    0.6661321    1.0573348
#> 173  0.3415619692    0.6657782    1.0576449
#> 174  0.3449919510    0.6584429    1.0501542
#> 175  0.4210004417    0.6562461    1.0880497
#> 176  0.4436143220    0.6620729    1.0830489
#> 177  0.4673648299    0.7220965    1.0907053
#> 178  0.4695256670    0.7191935    1.0909597
#> 179  0.4827957116    0.7054052    1.0789695
#> 180  0.5034770339    0.7060158    1.0992302
#> 181  0.5029719383    0.7066096    1.0993356
#> 182  0.5031806578    0.7077686    1.0977551
#> 183  0.5161265218    0.6843380    1.1182040
#> 184  0.5231641492    0.7020379    1.1222232
#> 185  0.5236933903    0.7176924    1.1342005
#> 186  0.5204202398    0.7334825    1.1615654
#> 187  0.5530182203    0.7327038    1.1865947
#> 188  0.5595104670    0.7453140    1.1920052
#> 189  0.5724327115    0.7184610    1.1585073
#> 190  0.5864589276    0.7501576    1.1513891
#> 191  0.5749037368    0.7433222    1.1821500
#> 192  0.5691688124    0.7379761    1.1752464
#> 193  0.5493569190    0.7303217    1.1969041
#> 194  0.5446958561    0.7323459    1.1874868
#> 195  0.5554776479    0.7282267    1.1706913
#> 196  0.0023175193    0.7227722    1.1585395
#> 197 -0.0134036930    0.7666768    1.1834621
#> 198 -0.0134785024    0.7690316    1.1836526
#> 199 -0.0169922633    0.7731577    1.1805244
#> 200 -0.0167902209    0.7776635    1.1774756

Created on 2019-09-28 by the reprex package (v0.3.0)

2 Likes
3

Hi @raytong! Thanks for your answer! Yes, that's correct. I will try to get an understanding of your code to be able to replicate it with other variables as well. I am more familiar with rollapply since it's often used in finance :slight_smile:

4

When calculating the coefficients in Excel I don't get your results for some reason. Any ideas?

5

@Pia. I guess if you reverse the x and y values or problem from missing values because missing value handling in Excel are a little bit tricky.

6

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.