Hi all,
for empirical finance analyses I have to create measures from daily trading data and aggregate them on a monthly level for every company. My simplified data look like this:
I created RET_CO_id, RET_OC_id and RET_id as well as NR_Dummy and PR_Dummy from PRC and OPENPRC:
For the NR_Dummy and PR_Dummy I just need a sum per month, but for each of the three return measures I have to compute the compounded monthly return. Additionally, each monthly observation should state the number of trading days (daily observations) for further computations. I tried to use
but was not able to adapt the statement to give a compounded return figure instead of the mean.
As it is my first post, I hope I used all code indicators and dataex correctly
for empirical finance analyses I have to create measures from daily trading data and aggregate them on a monthly level for every company. My simplified data look like this:
Code:
* Example generated by -dataex-. For more info, type help dataex clear input long date str36 COMNAM double(PERMCO PRC OPENPRC) float(RET_id RET_OC_id RET_CO_id NR_Dummy PR_Dummy month_id) 11870 "GREAT FALLS GAS CO" 7953 11.75 11.75 . 0 . 0 0 390 11871 "GREAT FALLS GAS CO" 7953 -12.125 . .031914894 . . 0 0 390 11875 "GREAT FALLS GAS CO" 7953 12.5 12.5 .030927835 0 .030927835 0 0 390 11876 "GREAT FALLS GAS CO" 7953 -12.125 . -.03 . . 0 0 390 11877 "GREAT FALLS GAS CO" 7953 12.25 12.25 .010309278 0 .010309278 0 0 390 11878 "GREAT FALLS GAS CO" 7953 -12.125 . -.010204081 . . 0 0 390 11879 "GREAT FALLS GAS CO" 7953 12 11.75 -.010309278 .021276595 -.030927835 0 1 390 11882 "GREAT FALLS GAS CO" 7953 -12.125 . .010416667 . . 0 0 390 11883 "GREAT FALLS GAS CO" 7953 11.75 11.75 -.030927835 0 -.030927835 0 0 390 11884 "GREAT FALLS GAS CO" 7953 -12.125 . .031914894 . . 0 0 390 11885 "GREAT FALLS GAS CO" 7953 -12.125 . 0 . . 0 0 390 11886 "GREAT FALLS GAS CO" 7953 12.5 12.5 .030927835 0 .030927835 0 0 390 11889 "GREAT FALLS GAS CO" 7953 -12.125 . -.03 . . 0 0 390 11890 "GREAT FALLS GAS CO" 7953 -12.125 . 0 . . 0 0 390 11891 "GREAT FALLS GAS CO" 7953 -12.125 . 0 . . 0 0 390 11892 "GREAT FALLS GAS CO" 7953 12.5 12.5 .030927835 0 .030927835 0 0 390 11893 "GREAT FALLS GAS CO" 7953 12.5 12.5 0 0 0 0 0 390 11896 "GREAT FALLS GAS CO" 7953 12.5 12.5 0 0 0 0 0 390 11897 "GREAT FALLS GAS CO" 7953 12.5 12.5 0 0 0 0 0 390 11898 "GREAT FALLS GAS CO" 7953 12.5 12.5 0 0 0 0 0 390 11899 "GREAT FALLS GAS CO" 7953 12.5 12.5 0 0 0 0 0 390 11900 "GREAT FALLS GAS CO" 7953 12.5 12.5 0 0 0 0 0 390 11903 "GREAT FALLS GAS CO" 7953 11.75 12.5 -.06 -.06 0 0 0 391 11904 "GREAT FALLS GAS CO" 7953 12.5 12.5 .06382979 0 .06382979 0 0 391 11905 "GREAT FALLS GAS CO" 7953 12.5 12.5 0 0 0 0 0 391 11906 "GREAT FALLS GAS CO" 7953 -12.25 . -.02 . . 0 0 391 11907 "GREAT FALLS GAS CO" 7953 12.5 12.5 .020408163 0 .020408163 0 0 391 11910 "GREAT FALLS GAS CO" 7953 -12.25 . -.02 . . 0 0 391 11911 "GREAT FALLS GAS CO" 7953 -12.25 . 0 . . 0 0 391 11912 "GREAT FALLS GAS CO" 7953 12.5 12.5 .020408163 0 .020408163 0 0 391 11913 "GREAT FALLS GAS CO" 7953 -12.25 . -.02 . . 0 0 391 11914 "GREAT FALLS GAS CO" 7953 12.5 12.5 .020408163 0 .020408163 0 0 391 11917 "GREAT FALLS GAS CO" 7953 12.375 12.375 -.01 0 -.01 0 0 391 11918 "GREAT FALLS GAS CO" 7953 12 12 -.03030303 0 -.03030303 0 0 391 11919 "GREAT FALLS GAS CO" 7953 -12.25 . .020833334 . . 0 0 391 11920 "GREAT FALLS GAS CO" 7953 -12.25 . 0 . . 0 0 391 11921 "GREAT FALLS GAS CO" 7953 -12.25 . 0 . . 0 0 391 11924 "GREAT FALLS GAS CO" 7953 12.5 12.5 .020408163 0 .020408163 0 0 391 11925 "GREAT FALLS GAS CO" 7953 -12.25 . -.02 . . 0 0 391 11926 "GREAT FALLS GAS CO" 7953 12.5 12.5 .020408163 0 .020408163 0 0 391 11927 "GREAT FALLS GAS CO" 7953 12.5 12.5 0 0 0 0 0 391 11928 "GREAT FALLS GAS CO" 7953 13 13 .04 0 .04 0 0 391 11931 "GREAT FALLS GAS CO" 7953 13 13 0 0 0 0 0 391 11932 "GREAT FALLS GAS CO" 7953 13 12.75 0 .019607844 -.01923077 0 1 392 11933 "GREAT FALLS GAS CO" 7953 13.25 13 .01923077 .01923077 0 0 0 392 11934 "GREAT FALLS GAS CO" 7953 13.25 13.25 0 0 0 0 0 392 11935 "GREAT FALLS GAS CO" 7953 -13.125 . -.009433962 . . 0 0 392 11939 "GREAT FALLS GAS CO" 7953 13.25 12.75 .00952381 .03921569 -.02857143 0 1 392 11940 "GREAT FALLS GAS CO" 7953 -13.375 . .009433962 . . 0 0 392 11941 "GREAT FALLS GAS CO" 7953 14 13.25 .04672897 .05660377 -.009345795 0 1 392 11942 "GREAT FALLS GAS CO" 7953 14.5 13.5 .035714287 .074074075 -.035714284 0 1 392 11945 "GREAT FALLS GAS CO" 7953 14 14 -.03448276 0 -.03448276 0 0 392 11946 "GREAT FALLS GAS CO" 7953 14 14.5 0 -.03448276 .035714287 1 0 392 11947 "GREAT FALLS GAS CO" 7953 13.75 13.75 -.017857144 0 -.017857144 0 0 392 11948 "GREAT FALLS GAS CO" 7953 -14.125 . .02727273 . . 0 0 392 11949 "GREAT FALLS GAS CO" 7953 -14.125 . 0 . . 0 0 392 11952 "GREAT FALLS GAS CO" 7953 14 14.5 -.0088495575 -.03448276 .02654867 1 0 392 11953 "GREAT FALLS GAS CO" 7953 14.25 14.75 .017857144 -.033898305 .05357143 1 0 392 11954 "GREAT FALLS GAS CO" 7953 14.25 14.25 0 0 0 0 0 392 11955 "GREAT FALLS GAS CO" 7953 14.75 15.25 .03508772 -.032786883 .07017544 1 0 392 11956 "GREAT FALLS GAS CO" 7953 15 14.25 .016949153 .05263158 -.033898305 0 1 392 11959 "GREAT FALLS GAS CO" 7953 14.875 15 -.008333334 -.008333334 0 0 0 392 11960 "GREAT FALLS GAS CO" 7953 -14.625 . -.016806724 . . 0 0 392 11961 "GREAT FALLS GAS CO" 7953 15 15 .025641026 0 .025641026 0 0 392 11962 "GREAT FALLS GAS CO" 7953 14.25 15 -.05 -.05 0 0 0 393 11963 "GREAT FALLS GAS CO" 7953 14.5 14.25 .01754386 .01754386 0 0 0 393 11966 "GREAT FALLS GAS CO" 7953 15 14.25 .03448276 .05263158 -.01724138 0 1 393 11967 "GREAT FALLS GAS CO" 7953 15 15 0 0 0 0 0 393 11968 "GREAT FALLS GAS CO" 7953 15 15 0 0 0 0 0 393 11969 "GREAT FALLS GAS CO" 7953 -14.625 . -.025 . . 0 0 393 11970 "GREAT FALLS GAS CO" 7953 14.5 14.25 -.008547009 .01754386 -.025641026 0 1 393 11973 "GREAT FALLS GAS CO" 7953 -14.625 . .00862069 . . 0 0 393 11974 "GREAT FALLS GAS CO" 7953 -14.625 . 0 . . 0 0 393 11975 "GREAT FALLS GAS CO" 7953 14.25 14.25 -.025641026 0 -.025641026 0 0 393 11976 "GREAT FALLS GAS CO" 7953 -14.625 . .02631579 . . 0 0 393 11977 "GREAT FALLS GAS CO" 7953 -14.625 . 0 . . 0 0 393 11980 "GREAT FALLS GAS CO" 7953 14.75 14.25 .008547009 .03508772 -.025641026 0 1 393 11981 "GREAT FALLS GAS CO" 7953 15 14.25 .016949153 .05263158 -.033898305 0 1 393 11982 "GREAT FALLS GAS CO" 7953 14.25 14.25 -.05 0 -.05 0 0 393 11983 "GREAT FALLS GAS CO" 7953 14.5 15 .01754386 -.033333335 .05263158 1 0 393 11984 "GREAT FALLS GAS CO" 7953 14.5 15 0 -.033333335 .034482762 1 0 393 11987 "GREAT FALLS GAS CO" 7953 -14.625 . .00862069 . . 0 0 393 11988 "GREAT FALLS GAS CO" 7953 14.25 14.875 -.025641026 -.04201681 .017094018 1 0 393 11989 "GREAT FALLS GAS CO" 7953 15 14.25 .05263158 .05263158 0 0 0 393 11990 "GREAT FALLS GAS CO" 7953 15 15 0 0 0 0 0 393 11991 "GREAT FALLS GAS CO" 7953 -14.625 . -.025 . . 0 0 393 11994 "GREAT FALLS GAS CO" 7953 15 14.25 .025641026 .05263158 -.025641026 0 1 394 11995 "GREAT FALLS GAS CO" 7953 -14.625 . -.025 . . 0 0 394 11996 "GREAT FALLS GAS CO" 7953 -14.625 . 0 . . 0 0 394 11997 "GREAT FALLS GAS CO" 7953 -14.625 . 0 . . 0 0 394 11998 "GREAT FALLS GAS CO" 7953 14.25 14.25 -.025641026 0 -.025641026 0 0 394 12001 "GREAT FALLS GAS CO" 7953 15 15 .05263158 0 .05263158 0 0 394 12002 "GREAT FALLS GAS CO" 7953 -14.625 . -.025 . . 0 0 394 12003 "GREAT FALLS GAS CO" 7953 14.25 14.25 -.025641026 0 -.025641026 0 0 394 12004 "GREAT FALLS GAS CO" 7953 -14.625 . .02631579 . . 0 0 394 12005 "GREAT FALLS GAS CO" 7953 15 15 .025641026 0 .025641026 0 0 394 12008 "GREAT FALLS GAS CO" 7953 14.25 14.25 -.05 0 -.05 0 0 394 12009 "GREAT FALLS GAS CO" 7953 15 15 .05263158 0 .05263158 0 0 394 12010 "GREAT FALLS GAS CO" 7953 14.5 14.5 -.033333335 0 -.033333335 0 0 394 12011 "GREAT FALLS GAS CO" 7953 14.25 14.25 -.01724138 0 -.01724138 0 0 394 end format %td date format %tm month_id
Code:
generate RET_id=(abs(PRC)/abs(PRC[_n-1]))-1 generate RET_OC_id=(abs(PRC)/abs(OPENPRC))-1 generate RET_CO_id= ((1+RET_id)/(1+RET_OC))-1 generate NR_Dummy = 0 replace NR_Dummy = 1 if RET_CO_id > 0 & RET_OC_id < 0 generate PR_Dummy = 0 replace PR_Dummy = 1 if RET_CO_id < 0 & RET_OC_id > 0
Code:
collapse (mean) RET_id RET_CO_id RET_OC_id (sum) NR_Dummy PR_Dummy, by (PERMCO CUSIP month_id)
As it is my first post, I hope I used all code indicators and dataex correctly
Comment