I seem to have problems with calculating mean returns (ret_sup in my data). My dataset has weekly obeservations for returns(customer en supplier). The lagged customer returns are split into quintiles of 5 with xtile. Based on the rank of the lagged customer returns, I want to calculate the weekly return of the associated supplier returns per quintile.
I used the following codes, but when I manually calculate the returns with excel for double checking, it doesnt give me the same results:
Code:
by stic ctic: gen L_ret= ret_cus[_n-1] egen retQ = xtile(L_ret), by( week_id ) nq(5) by week_id : egen retQ1=mean(ret_cus) if retQ ==1 by week_id : egen retQ2=mean(ret_cus) if retQ ==2 by week_id : egen retQ3=mean(ret_cus) if retQ ==3 by week_id : egen retQ4=mean(ret_cus) if retQ ==4 by week_id : egen retQ5=mean(ret_cus) if retQ ==5
Code:
* Example generated by -dataex-. To install: ssc install dataex clear input str8(stic ctic) float(week_id ret_sup ret_cus L_ret retQ retQ1 retQ2 retQ3 retQ4 retQ5) "PCZ" "CWG" 1926 .023622047 .069767445 .01775148 3 . . .0150178 . . "CPCI" "SNE" 1926 .067307696 .02152642 .02152642 3 . . .0150178 . . "CSDS" "GND" 1926 -.11594203 -.06666667 -.0625 1 .05393586 . . . . "PGEX" "FTR" 1926 .05633803 .03 .0033444816 2 . .03446858 . . . "HMTT" "IOM" 1926 .04918033 .1119403 -.02189781 1 .05393586 . . . . "SRI" "GM" 1926 . .016393442 .029535865 3 . . .0150178 . . "VICL" "RPR" 1926 -.007352941 .011666667 0 2 . .03446858 . . . "KOPN" "ROK" 1926 -.04807692 .030674847 -.012121212 1 .05393586 . . . . "MXC" "AQP" 1926 -.06451613 .008130081 -.008064516 2 . .03446858 . . . "TSGI" "NYN" 1926 -.06666667 .012562814 .015306123 3 . . .0150178 . . "AKS" "GM" 1926 .04153354 .016393442 .029535865 3 . . .0150178 . . "LEA" "GM" 1926 -.006493506 .016393442 .029535865 3 . . .0150178 . . "JCI" "GM" 1926 -.0056899 .016393442 .029535865 3 . . .0150178 . . "VCOM" "DSCP" 1926 -.014084507 -.010526316 .11111111 5 . . . . .0038411536 "SHLO" "GM" 1926 .028169014 .016393442 .029535865 3 . . .0150178 . . "IVAC" "HMTT" 1926 .04635762 .04918033 .18217054 5 . . . . .0038411536 "CSPLF" "CWG" 1926 -.01724138 .069767445 .01775148 3 . . .0150178 . . "BJICA" "DRYR" 1926 -.0825688 -.016666668 -.024390243 1 .05393586 . . . . "MNR" "AJL" 1926 -.02777778 .032786883 -.07575758 1 .05393586 . . . . "ITT" "GM" 1926 .005780347 .016393442 .029535865 3 . . .0150178 . . "ALET" "ALET" 1926 .04166667 .04166667 .04166667 5 . . . . .0038411536 "MCCL" "WMX" 1926 -.08571429 -.011235955 .011363637 3 . . .0150178 . . "SPTR" "OCC" 1926 .02352941 .06498195 -.017730497 1 .05393586 . . . . "LRCI" "BKCO" 1926 .0925926 .03448276 .035714287 5 . . . . .0038411536 "JPMX" "DBD" 1926 0 -.07212476 . . . . . . . "DSPT" "GM" 1926 0 .016393442 .029535865 3 . . .0150178 . . "PRCP" "GM" 1926 -.028368793 .016393442 .029535865 3 . . .0150178 . . "CPCI" "SNE" 1926 .067307696 .02152642 -.04307116 1 .05393586 . . . . "CVTX" "BGEN" 1926 .0877193 .15337424 -.006097561 2 . .03446858 . . . "PCZ" "TRP" 1926 .023622047 .02142857 .007194245 2 . .03446858 . . . "CEPH" "CHIR" 1926 .11229946 -.040625 .012658228 3 . . .0150178 . . "CFCI" "REY" 1926 .02479339 .03809524 .024390243 3 . . .0150178 . . "ROG" "AFLX" 1926 -.009090909 -.10784314 .05154639 5 . . . . .0038411536 "AFLX" "SFLX" 1926 -.10784314 -.015151516 .023255814 3 . . .0150178 . . "KLOC" "CBS" 1926 -.06666667 0 .018072288 3 . . .0150178 . . "HIW" "HIW" 1926 -.003610108 -.003610108 .029739777 5 . . . . .0038411536 "DEFI" "GM" 1926 -.01923077 .016393442 .029535865 3 . . .0150178 . . "RGLD" "PDG" 1926 -.1 .03030303 .012269938 3 . . .0150178 . . "SIND" "SHX" 1926 .11764706 .07692308 -.02803738 1 .05393586 . . . . "STCL" "RCII" 1926 .1935484 .02255639 .11764706 5 . . . . .0038411536 "STRB" "CBS" 1926 0 0 .018072288 3 . . .0150178 . . "WN" "GM" 1926 -.3415638 .016393442 .029535865 3 . . .0150178 . . "MCCL" "MCCL" 1926 -.08571429 -.08571429 .14130434 5 . . . . .0038411536 "DECO" "RBD" 1926 0 .03910615 .017045455 3 . . .0150178 . . "CSYI" "SGMA" 1926 -.01369863 .05755396 .007246377 3 . . .0150178 . . "SMPS" "GM" 1926 -.011494253 .016393442 .029535865 3 . . .0150178 . . "BGG" "ELUXY" 1926 .013812155 -.02484472 .07572383 5 . . . . .0038411536 "HILI" "GM" 1926 .15789473 .016393442 .029535865 3 . . .0150178 . . "ALET" "ALET" 1926 .04166667 .04166667 0 2 . .03446858 . . . "NFG" "UGI" 1926 -.02285714 .005586592 0 2 . .03446858 . . . "ZRAN" "IOM" 1926 .22826087 .1119403 -.02189781 1 .05393586 . . . . "SUP" "GM" 1926 -.010869565 .016393442 .029535865 3 . . .0150178 . . "GLFD" "RPR" 1926 -.02 .011666667 0 2 . .03446858 . . . "TIII" "NYN" 1926 .1122449 .012562814 .015306123 3 . . .0150178 . . "CHK" "AQP" 1926 -.1440678 .008130081 -.008064516 2 . .03446858 . . . "PTSI" "GM" 1926 -.020833334 .016393442 .029535865 3 . . .0150178 . . "NAII" "AJL" 1926 -.01369863 .032786883 -.07575758 1 .05393586 . . . . "SPZE" "EIRE" 1926 .1875 .13414635 .031446543 5 . . . . .0038411536 "HICKA" "GM" 1926 -.0125 .016393442 .029535865 3 . . .0150178 . . "ORCI" "GM" 1926 -.03448276 .016393442 .029535865 3 . . .0150178 . . "AMSC" "CWP" 1926 -.032967035 .037037037 -.010471204 2 . .03446858 . . . "HAVA" "GM" 1926 .35 .016393442 .029535865 3 . . .0150178 . . "GNTX" "GM" 1926 -.07317073 .016393442 .029535865 3 . . .0150178 . . "ARRS" "TCOMA" 1926 .07476635 .04424779 -.02586207 1 .05393586 . . . . "GGNS" "IIII" 1926 -.11764706 0 -.006993007 2 . .03446858 . . . "NSCC" "OHM" 1926 .04347826 -.014492754 0 2 . .03446858 . . . "RGR" "ELY" 1926 .006535948 .12820514 -.012658228 1 .05393586 . . . . "IMPX" "ROK" 1926 .071428575 .030674847 -.012121212 1 .05393586 . . . . "AWRE" "USRX" 1926 .25714287 .008561644 .06181818 5 . . . . .0038411536 "STRT" "GM" 1926 0 .016393442 .029535865 3 . . .0150178 . . "IMPX" "IOM" 1926 .071428575 .1119403 -.02189781 1 .05393586 . . . . "MAYS" "FULT" 1926 0 .10843374 0 2 . .03446858 . . . "SWTX" "SNE" 1926 -.05357143 .02152642 -.04307116 1 .05393586 . . . . "AOS" "YRK" 1926 -.06072874 .009433962 -.023041476 1 .05393586 . . . . "LCBM" "CHIR" 1926 -.006849315 -.040625 .012658228 3 . . .0150178 . . "FSCR" "GM" 1926 .022900764 .016393442 .029535865 3 . . .0150178 . . "INVI" "GM" 1926 0 .016393442 .029535865 3 . . .0150178 . . "ORBT" "GM" 1926 .0882353 .016393442 .029535865 3 . . .0150178 . . "SPA" "WAT" 1926 .008064516 .06521739 -.033613447 1 .05393586 . . . . "NAII" "NUS" 1926 -.01369863 -.041841 .012711864 3 . . .0150178 . . "NFG" "NUI" 1926 -.02285714 0 0 2 . .03446858 . . . "CTS" "GM" 1926 .028744327 .016393442 .029535865 3 . . .0150178 . . "KVHI" "GM" 1926 0 .016393442 .029535865 3 . . .0150178 . . "WALB" "GM" 1926 0 .016393442 .029535865 3 . . .0150178 . . "SEC" "MCD" 1926 .037037037 .04519774 0 2 . .03446858 . . . "DASTY" "DASTY" 1926 -.011961723 -.011961723 .14520548 5 . . . . .0038411536 "DRTE" "RPR" 1926 -.0625 .011666667 0 2 . .03446858 . . . "PMD" "GM" 1926 .02654867 .016393442 .029535865 3 . . .0150178 . . "KRT" "HCDV" 1926 .014492754 0 -.032967035 1 .05393586 . . . . "ASI" "ASI" 1926 .29801324 .29801324 -.01948052 1 .05393586 . . . . "PAR" "MCD" 1926 .14049587 .04519774 0 2 . .03446858 . . . "PDKL" "COGE" 1926 -.023809524 .09574468 -.005291005 2 . .03446858 . . . "PSDS" "DRI" 1926 .10204082 -.03076923 -.029850746 1 .05393586 . . . . "WCSTF" "GM" 1926 -.01599016 .016393442 .029535865 3 . . .0150178 . . "BWA" "GM" 1926 .04140127 .016393442 .029535865 3 . . .0150178 . . end format %tw week_id
0 Response to Generating mean returns, based on xtile returns
Post a Comment