Reputation: 37
We have daily returns from well over 100 mutual funds that we wish to convert into monthly returns. The monthly return should not be the average from each month, but the funds' return at the end of each month. The funds start and end at different points in time, and they need to remain by themself (not adding all mutual fund returns into 1 each month).
As of the picture one may see the dataframe (df) and parts of its content. The dates are in the first column and it needs to be sorted. enter image description here We would love some help to solve this problem.
I believe that it contains sufficient information be able to write the code.
DATA,dput(df5[1:50,])
- "Date" "Name" "Nav"
- 2012-01-02 Aktiva 10 121.738
- 2012-01-03 Aktiva 10 121.87
- 2012-01-04 Aktiva 10 121.906
- 2012-01-05 Aktiva 10 121.89
- 2012-01-06 Aktiva 10 121.949
- 2012-01-09 Aktiva 10 122.024
- 2012-01-10 Aktiva 10 122.205
- 2012-01-11 Aktiva 10 122.219
- 2012-01-12 Aktiva 10 122.324
- 2012-01-13 Aktiva 10 122.309
- 2012-01-16 Aktiva 10 122.45
- 2012-01-17 Aktiva 10 122.433
- 2012-01-18 Aktiva 10 122.483
- 2012-01-19 Aktiva 10 122.596
- 2012-01-20 Aktiva 10 122.548
- 2012-01-23 Aktiva 10 122.653
- 2012-01-24 Aktiva 10 122.507
- 2012-01-25 Aktiva 10 122.582
- 2012-01-26 Aktiva 10 122.783
- 2012-01-27 Aktiva 10 122.804
- 2012-01-30 Aktiva 10 122.749
- 2012-01-31 Aktiva 10 122.865
- 2012-02-01 Aktiva 10 123.044
- 2012-02-02 Aktiva 10 123.184
- 2012-02-03 Aktiva 10 123.32
- 2012-02-06 Aktiva 10 123.402
- 2012-02-07 Aktiva 10 123.322
- 2012-02-08 Aktiva 10 123.342
- 2012-02-09 Aktiva 10 123.421
- 2012-02-10 Aktiva 10 123.368
- 2012-02-13 Aktiva 10 123.418
- 2012-02-14 Aktiva 10 123.389
- 2012-02-15 Aktiva 10 123.558
- 2012-02-16 Aktiva 10 123.735
- 2012-02-17 Aktiva 10 123.636
- 2012-02-20 Aktiva 10 123.68
- 2012-02-21 Aktiva 10 123.701
- 2012-02-22 Aktiva 10 123.705
- 2012-02-23 Aktiva 10 123.663
- 2012-02-24 Aktiva 10 123.723
- 2012-02-27 Aktiva 10 123.77
- 2012-02-28 Aktiva 10 123.9
- 2012-02-29 Aktiva 10 123.91
- 2012-03-01 Aktiva 10 123.95
- 2012-03-02 Aktiva 10 124.02
- 2012-03-05 Aktiva 10 123.98
- 2012-03-06 Aktiva 10 123.74
- 2012-03-07 Aktiva 10 123.79
- 2012-03-08 Aktiva 10 123.92
- 2012-03-09 Aktiva 10 124.05
dput(df[1:50,]) data looks like this:
structure(list(Date = structure(c(954720000, 954806400, 954892800,
954979200, 955065600, 955324800, 955411200, 955497600, 955584000,
955670400, 955929600, 956016000, 956102400, 956620800, 956707200,
956793600, 956880000, 957225600, 957312000, 957398400, 957484800,
957744000, 957830400, 957916800, 958003200, 958089600, 958348800,
958435200, 958608000, 958694400, 958953600, 959040000, 959126400,
959212800, 959299200, 959558400, 959644800, 959731200, 959904000,
960163200, 960249600, 960336000, 960422400, 960508800, 960854400,
960940800, 961027200, 961113600, 961372800, 961459200), class = c("POSIXct",
"POSIXt"), tzone = "UTC"), SecurityId = c(45731, 45731, 45731,
45731, 45731, 45731, 45731, 45731, 45731, 45731, 45731, 45731,
45731, 45731, 45731, 45731, 45731, 45731, 45731, 45731, 45731,
45731, 45731, 45731, 45731, 45731, 45731, 45731, 45731, 45731,
45731, 45731, 45731, 45731, 45731, 45731, 45731, 45731, 45731,
45731, 45731, 45731, 45731, 45731, 45731, 45731, 45731, 45731,
45731, 45731), FundId = c(109, 109, 109, 109, 109, 109, 109,
109, 109, 109, 109, 109, 109, 109, 109, 109, 109, 109, 109, 109,
109, 109, 109, 109, 109, 109, 109, 109, 109, 109, 109, 109, 109,
109, 109, 109, 109, 109, 109, 109, 109, 109, 109, 109, 109, 109,
109, 109, 109, 109), Symbol = c("AI-NORS2", "AI-NORS2", "AI-NORS2",
"AI-NORS2", "AI-NORS2", "AI-NORS2", "AI-NORS2", "AI-NORS2", "AI-NORS2",
"AI-NORS2", "AI-NORS2", "AI-NORS2", "AI-NORS2", "AI-NORS2", "AI-NORS2",
"AI-NORS2", "AI-NORS2", "AI-NORS2", "AI-NORS2", "AI-NORS2", "AI-NORS2",
"AI-NORS2", "AI-NORS2", "AI-NORS2", "AI-NORS2", "AI-NORS2", "AI-NORS2",
"AI-NORS2", "AI-NORS2", "AI-NORS2", "AI-NORS2", "AI-NORS2", "AI-NORS2",
"AI-NORS2", "AI-NORS2", "AI-NORS2", "AI-NORS2", "AI-NORS2", "AI-NORS2",
"AI-NORS2", "AI-NORS2", "AI-NORS2", "AI-NORS2", "AI-NORS2", "AI-NORS2",
"AI-NORS2", "AI-NORS2", "AI-NORS2", "AI-NORS2", "AI-NORS2"),
ISIN = c("NO0010089576", "NO0010089576", "NO0010089576",
"NO0010089576", "NO0010089576", "NO0010089576", "NO0010089576",
"NO0010089576", "NO0010089576", "NO0010089576", "NO0010089576",
"NO0010089576", "NO0010089576", "NO0010089576", "NO0010089576",
"NO0010089576", "NO0010089576", "NO0010089576", "NO0010089576",
"NO0010089576", "NO0010089576", "NO0010089576", "NO0010089576",
"NO0010089576", "NO0010089576", "NO0010089576", "NO0010089576",
"NO0010089576", "NO0010089576", "NO0010089576", "NO0010089576",
"NO0010089576", "NO0010089576", "NO0010089576", "NO0010089576",
"NO0010089576", "NO0010089576", "NO0010089576", "NO0010089576",
"NO0010089576", "NO0010089576", "NO0010089576", "NO0010089576",
"NO0010089576", "NO0010089576", "NO0010089576", "NO0010089576",
"NO0010089576", "NO0010089576", "NO0010089576"), Name = c("ABIF Norge ++",
"ABIF Norge ++", "ABIF Norge ++", "ABIF Norge ++", "ABIF Norge ++",
"ABIF Norge ++", "ABIF Norge ++", "ABIF Norge ++", "ABIF Norge ++",
"ABIF Norge ++", "ABIF Norge ++", "ABIF Norge ++", "ABIF Norge ++",
"ABIF Norge ++", "ABIF Norge ++", "ABIF Norge ++", "ABIF Norge ++",
"ABIF Norge ++", "ABIF Norge ++", "ABIF Norge ++", "ABIF Norge ++",
"ABIF Norge ++", "ABIF Norge ++", "ABIF Norge ++", "ABIF Norge ++",
"ABIF Norge ++", "ABIF Norge ++", "ABIF Norge ++", "ABIF Norge ++",
"ABIF Norge ++", "ABIF Norge ++", "ABIF Norge ++", "ABIF Norge ++",
"ABIF Norge ++", "ABIF Norge ++", "ABIF Norge ++", "ABIF Norge ++",
"ABIF Norge ++", "ABIF Norge ++", "ABIF Norge ++", "ABIF Norge ++",
"ABIF Norge ++", "ABIF Norge ++", "ABIF Norge ++", "ABIF Norge ++",
"ABIF Norge ++", "ABIF Norge ++", "ABIF Norge ++", "ABIF Norge ++",
"ABIF Norge ++"), NAV = c(100, 99.4291, 93.5061, 96.9827,
98.3752, 98.9016, 96.932, 96.6528, 95.5597, 92.9564, 90.4477,
91.9742, 94.0813, 94.6611, 95.7128, 94.3541, 96.9792, 98.5621,
99.8926, 100.9441, 100.8709, 101.4863, 101.9841, 100.9585,
101.6321, 103.3527, 103.3474, 104.5718, 106.1602, 104.2994,
103.8382, 103.5229, 101.8999, 103.1289, 101.968, 101.0299,
102.4697, 102.1109, 104.3061, 103.2584, 102.2464, 101.8824,
102.7758, 103.4881, 103.2249, 103.9476, 103.5058, 104.2614,
103.3023, 103.4716), NAVAdj = c(98.149473714552, 97.5891383691156,
91.7757450410027, 95.1880096441628, 96.554741065638, 97.0713998952714,
95.1382478609895, 94.8642145303785, 93.7913426332048, 91.2362173839938,
88.7739415369168, 90.2721932531695, 92.3403008138088, 92.9093714624058,
93.9416094774617, 92.6080525781021, 95.1845744125828, 96.7381824320104,
98.0440611797826, 99.0761028958911, 99.004257481132, 99.6082693423714,
100.096857422522, 99.090236420106, 99.7513712750472, 101.44013111978,
101.434929197673, 102.636671353834, 104.195677594316, 102.369312187435,
101.916646814664, 101.607181524042, 100.014215565655, 101.220472597607,
100.081055357254, 99.1603151443382, 100.57347126688, 100.221310955192,
102.375888202174, 101.347576166067, 100.354303492076, 99.9970394077547,
100.873906805921, 101.573025507189, 101.314696092373, 102.024022338908,
101.590397964037, 102.332015387424, 101.390663785028, 101.556830844026
), Dividends = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), CorpAdj = c(1,
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1), DividendAdj = c(0.98149473714552,
0.98149473714552, 0.98149473714552, 0.98149473714552, 0.98149473714552,
0.98149473714552, 0.98149473714552, 0.98149473714552, 0.98149473714552,
0.98149473714552, 0.98149473714552, 0.98149473714552, 0.98149473714552,
0.98149473714552, 0.98149473714552, 0.98149473714552, 0.98149473714552,
0.98149473714552, 0.98149473714552, 0.98149473714552, 0.98149473714552,
0.98149473714552, 0.98149473714552, 0.98149473714552, 0.98149473714552,
0.98149473714552, 0.98149473714552, 0.98149473714552, 0.98149473714552,
0.98149473714552, 0.98149473714552, 0.98149473714552, 0.98149473714552,
0.98149473714552, 0.98149473714552, 0.98149473714552, 0.98149473714552,
0.98149473714552, 0.98149473714552, 0.98149473714552, 0.98149473714552,
0.98149473714552, 0.98149473714552, 0.98149473714552, 0.98149473714552,
0.98149473714552, 0.98149473714552, 0.98149473714552, 0.98149473714552,
0.98149473714552), lnDeltaNAV = c(0, -0.00572535863116119,
-0.0614181525486561, 0.0365059372732999, 0.0142561276907758,
0.00533667668298765, -0.0201157147168702, -0.00288452599160749,
-0.011373992683045, -0.027620617044005, -0.0273587829153019,
0.016736319832737, 0.0226511991448142, 0.00614384286518277,
0.0110488958739232, -0.0142973147308307, 0.0274417964293168,
0.0161902838567531, 0.0134088024604093, 0.0104712894394297,
-0.000725416873969209, 0.0060823326001147, 0.00489310477802452,
-0.0101073774671372, 0.00664988881292139, 0.0167879804789104,
-5.12820264839675e-05, 0.0117777882336343, 0.0150753571586755,
-0.0176836646173788, -0.00443169090857154, -0.00304107417442001,
-0.0158018853966349, 0.0119887032476456, -0.0113206235496355,
-0.00924252593779595, 0.0141506324870466, -0.00350766756849463,
0.021270367882865, -0.0100952609714282, -0.00984899798499139,
-0.00356637951931749, 0.00873070978798651, 0.00690671350197469,
-0.00254652723566728, 0.00697682300028468, -0.00425927623174083,
0.0072735574356857, -0.00924156609376592, 0.0016375377920701
), lnDeltaOSEBX = c(0, -0.00940531538140732, -0.0515228085845063,
0.0330841053596203, 0.0149328822530661, 0.00140488772073599,
-0.0184601740944386, 0.00262641959218701, -0.012317562508172,
-0.0263240283504187, -0.0138437179343693, 0.0132849051679038,
0.0210929614810249, 0.0016248841643085, 0.00827607079901416,
-0.0154081787503344, 0.0261622502400947, 0.0179854981160581,
0.013271853834639, 0.0118876641774017, -0.000609937259577364,
0.00136092770291629, 0.00375227676786949, -0.00991727309818735,
0.00470404341043462, 0.0178788765076252, 0.000550091704436539,
0.015506961679753, 0.0137508866550915, -0.0167880781194372,
-0.00746925582682945, -0.00163624416898944, -0.0165229230024071,
0.0105430179398134, -0.0133781957394445, -0.00546854351296844,
0.00984014548362389, -0.00321159549991012, 0.018852089647897,
-0.00986344346201573, -0.0101602069993261, 0.000661533679649828,
0.0073117003894172, 0.00344172289439459, 0.00378742002574128,
0.0007234081265306, -0.00095362267457233, 0.00833764093971467,
-0.000905517713564841, -0.00730795598720579), lnDeltaOBX = c(0,
-0.00675924299385855, -0.0446627432412416, 0.0271511087647243,
0.0104352836056094, 0.00252750079747255, -0.0139940420412588,
0.000443962766651929, -0.0134847680574186, -0.0212769716066035,
-0.0129102509389352, 0.0167416215616996, 0.0179482930426307,
0.00615727334394656, 0.0110326474282347, -0.0133618870045504,
0.0222017092722471, 0.0151286494232705, 0.00551851236591716,
0.00800823957119601, -0.00142848216759983, 0.00617540558679863,
0.00572868073875021, -0.00951190548323133, 0.00500378510823829,
0.0164558409589644, -2.4488196690875e-05, 0.0111775180525235,
0.0216079600277741, -0.0195879405922108, -0.00801940591750494,
0.00259119504119276, -0.0135284308990071, 0.00875445456788881,
-0.0118997124506839, -0.0047675452047562, 0.0104833246950742,
-0.0035685318306351, 0.0184672492972027, -0.0092276363398982,
-0.0119552952128883, 0.00394764949324067, 0.00666321451700913,
0.00669200495886102, 0.00710600824465679, -0.00211320833357309,
0.00160644545155453, 0.0127356648288117, -0.00153836093566273,
-0.00467729378767778), lnDeltaOSEFX = c(0, -0.0132566852489493,
-0.054436017358416, 0.036447582787936, 0.0143346129652224,
0.00322243291943547, -0.0202744508590715, 0.00351120943021765,
-0.0139984771357593, -0.0264788767234734, -0.0224291179937897,
0.0147570608902363, 0.0256818445585978, 0.00382403430885514,
0.00892967356250907, -0.0176447368553463, 0.0257512997592757,
0.0177749823933526, 0.0117569174284275, 0.0136460053312675,
-0.00124573887812041, 0.00116160809454247, 0.00402634234849675,
-0.0105042345492476, 0.00589741004413469, 0.0189357154629182,
0.000452100549805046, 0.0149796051273769, 0.0122373634579906,
-0.0179535473500074, -0.00708298187974066, -0.000441760549906078,
-0.0169947891004751, 0.0138275915131079, -0.0149290919606084,
-0.00617441051640188, 0.0111232403312256, -0.00372148073099599,
0.0226673312134444, -0.00968981475443709, -0.00997237606061496,
-0.00060224703743561, 0.0086779720852439, 0.00319008738691462,
0.00133857674206705, 0.00058350744660185, -0.00325430504398128,
0.00767990023126242, -0.000607277053698674, -0.00564152856083044
), SMB = c(0.0123311034288627, 0.00915156609628357, -0.00158716760294442,
-0.0297416735452946, 0.0014191924545611, -0.0150755252076274,
0.0115701574233579, -0.00111340347515908, -0.00819114157880345,
-0.024104652357138, 0.0402172827616449, 0.00534146788916141,
-0.0154634888942257, 0.00354035521403617, 0.00206630945718951,
0.00110824503543285, 0.00577986123704642, -0.0091511299479988,
-0.00628038625739494, 0.00641233515248658, -0.00348433542025252,
0.00571653658752371, -0.00108622140744236, -0.00467826333193927,
0.0015293960849232, 0.0150302224117192, 0.0128782375422192,
0.00182479014151233, 0.0118018051325084, -0.00122505374600254,
0.0067581316644394, 0.00289618880797662, -0.00922587897314921,
-0.0112790492150875, -0.0131539668848055, -0.00483914410215184,
0.00392018158948877, -0.00654011695984795, -0.00245063806434721,
0.00390394664204772, -0.0119417973629864, 0.00391466483105893,
-0.00907824016648776, 0.00281885664081369, 0.0137050768450805,
0.0151027414136372, -0.00635772453077626, 0.0141303322841679,
-0.000411054109417454, -0.00821935988613381), HML = c(0.0811601046922382,
0.0263351665848042, 0.106475381968986, -0.0717486018918445,
-0.013401199905366, -0.0262830431094566, 0.0377662920323366,
-0.00983345919969417, 0.0384794311058455, 0.101128024713768,
0.0389117895270927, -0.0243149370435034, -0.0579205369141316,
-0.0174301787358929, 0.00261970745261295, 0.0318994811019162,
-0.0400042937351153, -0.00153694356567032, 0.0233985279797224,
-0.0401746710352116, 0.0190299884544687, 0.039648558170685,
0.0244504738247621, 0.0198792894699696, -0.00870600396129643,
-0.102281854622862, -0.00487726994595788, -0.0062245997679463,
0.0103673220988611, 0.0642078211928357, 0.000607234654230467,
-0.0371130284191367, 0.0309287156839412, -0.0234612031829678,
0.0733878599469245, 0.0385598691553834, -0.0278492201926123,
-0.0066708890999078, -0.0448929604242489, -0.0143415360857507,
-0.0238935048340895, 0.0108320873597463, -0.00762360971561658,
-0.00828199565147488, 0.0305644139491684, -0.0254099474698697,
0.0250996171797242, 0.00538982163947071, -0.00210515259916917,
-0.0195066162810241), LIQ = c(0.0157034253750779, 0.0227002836975072,
0.065738432925917, -0.0552351227361365, -0.020988058634736,
-0.0119287058062276, 0.0413731668748464, 0.012718095507565,
0.00369068026836507, 0.0276632935575419, 0.0370998398194863,
0.000314306564174449, -0.0340165785757102, 0.00816108559328334,
0.0196198820076372, 0.0206822207525292, -0.022369198083528,
-0.00547122285430531, -0.00349413197881119, 0.0154782104314266,
0.000385689216523621, 0.00781858322435598, -0.00871198187764726,
0.00431754203466546, -0.00946171019470503, 0.0107870226457883,
0.0151864839115862, -0.0116275474872604, -0.00141908832914339,
-0.000513165399929121, -0.00989121260626544, 0.00203118199429453,
-0.00133920286743205, 0.000513811721746565, -0.00890441247364418,
-0.000194479560384646, 0.00132777366732754, 0.0109845718520624,
-0.0131579761871903, 0.00169925198332302, -0.0175742369163852,
0.000469051062196477, 0.00340342712080001, -0.0105237634528422,
-0.000376810706883849, 0.0043542754059329, -0.00861982360414743,
0.0192809486833832, -0.0043442441716499, -0.0221072737122197
), MOM = c(0.0287454429557529, 0.0134782075274417, 0.00806633703967767,
-0.0294639612554887, 0.00283144507570127, -0.0117475999780675,
0.0271459089999048, -0.00805275416260212, -0.00491915532340628,
0.039267068265006, 0.00133730551324356, -0.00723365456343063,
-0.0226409142038954, 0.0145194649631461, 0.00724883603814357,
-0.00115205579341722, 0.016500139365733, -0.000185216749684504,
-0.00934779411541315, -0.0259092770501304, -0.00880067920625633,
0.00598712321696791, -0.000870616235833688, 0.00586781179120599,
0.00753842573531934, -0.00391557978990263, 0.0059364572892294,
-0.0135728247033562, 0.0132819781153358, 0.00189481246194319,
0.0103643573349146, 0.00298287034990829, 0.00989923925491796,
-0.0166922330388068, 0.0137537988848887, 0.0201338553207169,
-0.00545353385720432, -0.00659043125965345, -0.0233699553489148,
-0.0168952251152045, 0.00801157245015491, 0.00310250561711743,
-0.00582512011171042, 0.00948774604918234, 0.0156395952714775,
0.00142863409832036, 0.0112959563212738, -0.00791703211090294,
-0.0068101823373943, -0.000386985037747023)), row.names = c(NA,
-50L), class = c("tbl_df", "tbl", "data.frame"))
Upvotes: 2
Views: 941
Reputation: 30474
With tidyverse
and lubridate
you can do the following.
You can group_by
month and then filter
to show only the last row of data for each month. arrange
is used to sort by Date
just in case.
Edit: Also group_by
year(Date)
in this example. Results updated with new data provided.
library(tidyverse)
library(lubridate)
df %>%
group_by(Name, Year = year(Date), Month = month(Date)) %>%
arrange(Date) %>%
filter(NAV == last(NAV))
Output
# A tibble: 3 x 21
# Groups: Name, Year, Month [3]
Date SecurityId FundId Symbol ISIN Name NAV NAVAdj Dividends CorpAdj DividendAdj lnDeltaNAV lnDeltaOSEBX lnDeltaOBX lnDeltaOSEFX
<dttm> <dbl> <dbl> <chr> <chr> <chr> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
1 2000-04-28 00:00:00 45731 109 AI-NO… NO00… ABIF… 96.9792 95.18457 0 1 0.9814947 0.02744180 0.02616225 0.02220171 0.02575130
2 2000-05-31 00:00:00 45731 109 AI-NO… NO00… ABIF… 102.1109 100.2213 0 1 0.9814947 -0.003507668 -0.003211595 -0.003568532 -0.003721481
3 2000-06-20 00:00:00 45731 109 AI-NO… NO00… ABIF… 103.4716 101.5568 0 1 0.9814947 0.001637538 -0.007307956 -0.004677294 -0.005641529
# … with 6 more variables: SMB <dbl>, HML <dbl>, LIQ <dbl>, MOM <dbl>, Year <dbl>, Month <dbl>
Upvotes: 2