Write and run SAS programs in your web browser

Geometric mean of monthly return values

Accepted Solution Solved
Reply
Highlighted
Regular Contributor
Posts: 184
Accepted Solution

Geometric mean of monthly return values

Hello,

 

@Astounding helped me use a monthly return value in my firm performance dataset to create a variable for annualized return. Here is the code he provided, which worked:

 

data paper.CSRP_Monthly_Stock;
	set paper.CSRP_Monthly_Stock;
	by ticker date;
	retain annual_return 1;
	annual_return = annual_return * (1 + RET);
	month = int( mod(date,10000) / 100);
	if month = 12 or last.ticker;
	annual_return = (annual_return - 1) * 100;
	output;
	annual_return = 1;
run;

 

How can I use this code to calculate the geometric mean of the monthly values instead? (which takes several values and multiplies them together and sets them to the 1/nth power). This is how my professor wishes to see the annualized return calculated.

 

Thanks in advance for the help!


Accepted Solutions
Solution
‎03-30-2018 07:27 PM
Trusted Advisor
Posts: 1,337

Re: Geometric mean of monthly return values

This nothing more than the usual compounding formulas.

 

Prior to the 

    annual_return = (annual_return - 1) * 100;

 

statement, put

   mnth_gmean= 100*(exp(log(annual_return)/12)-1);

View solution in original post


All Replies
SAS Super FREQ
Posts: 4,239

Re: Geometric mean of monthly return values

[ Edited ]

Won't there be months and firms for which the return is negative? Most people require that all arguments to the geometric mean be positive.

Trusted Advisor
Posts: 1,337

Re: Geometric mean of monthly return values

@Rick_SAS

 

I suspect what the user wants is 

   geometric mean of (1+return) - 1

in which case all the arguments will be positive.

 

Super User
Posts: 10,766

Re: Geometric mean of monthly return values

data class;
 set sashelp.class;
run;
proc sort data=class;by sex;run;

ods select none;
ods output GeometricMeans=want;
proc surveymeans data=class geomean;
by sex;
var weight;
run;
ods select all;

proc print;run;

x.png

Regular Contributor
Posts: 184

Re: Geometric mean of monthly return values

Cool--thanks! But how will this incorporate the varying amount of months reported in each year? Will this code give me an annual return for each year? Or will it just find the geometric mean of all the values in my dataset?

 

Thanks again!

Solution
‎03-30-2018 07:27 PM
Trusted Advisor
Posts: 1,337

Re: Geometric mean of monthly return values

This nothing more than the usual compounding formulas.

 

Prior to the 

    annual_return = (annual_return - 1) * 100;

 

statement, put

   mnth_gmean= 100*(exp(log(annual_return)/12)-1);

Regular Contributor
Posts: 184

Re: Geometric mean of monthly return values

Thanks for the suggestion!

 

Here is a sample of the output after implementing your code:

 

rowdate      tickercusip     ret         year  annual_return      mnth_gmean
220171229AA13872100.2977620171533755.5247123.26228808
320171229AABA2134610-0.0029972017270363.4636293.200857856
420141231AAC307100.07137920141341124.3459120.77935099
520151231AAC30710-0.2233092015-239255.8353.
620161230AAC30710-0.1370682016-470522.6346.
720171229AAC30710-0.0142392017226261.2329890.356117157
820101231AAME48209100.0684212010658075.50432108.06313737
920111230AAME4820910-0.010052011-30199.14677.
1020121231AAME48209100.0644072012674676.12071108.49547895
1120131231AAME48209100.0224742013336319.9660896.746408621
1220141231AAME48209100.0467532014-15071.60054.
1320151231AAME48209100.0439332015255878.3408992.31667199
1420161230AAME48209100.0252016-193604.8557.
1520171229AAME4820910-0.0555562017-158087.7759.
1620101231AAN2535300.0235942010488518.92321102.96174595
1720111230AAN2535300.0152212011310436.4771295.438168973
1820121231AAN253530-0.014634201250543.86717668.026290038
1920131231AAN2535300.026536201334175.87542962.648119455
2020141231AAN2535300.077167201438808.65330264.375536486
2120151231AAN253530-0.0774622015-235114.8392.
2220161230AAN2535300.0995022016514200.17148103.82997714
2320171229AAN2535300.0572642017268176.2271693.070171567
2420101231AAON360200.0998052010555974.3979105.16081156
2520111230AAON36020-0.064811201157587.13627569.859528806
2620121231AAON36020-0.008551201226075.44772259.034437296
2720131231AAON360200.03834920131402764.9079121.607602
2820141231AAON360200.080598201458960.56306770.192910485
2920151231AAON36020-0.060299201531807.29940661.680448027
3020161230AAON360200.0045592016428507.3052100.75743154
3120171229AAON360200.0068592017109790.7735779.231127104
3220101231AAPL37833100.036672010559830.31805105.27898829
3320111230AAPL37833100.0596552011276286.7710393.54996822
3420121231AAPL3783310-0.0907382012259682.533392.553239238
3520131231AAPL37833100.008902201371553.00945572.956227117
3620141231AAPL3783310-0.0718912014338907.4960196.872070344
3720151231AAPL3783310-0.1102282015-33065.91721.
3820161230AAPL37833100.0479552016125929.8138181.289551502
3920171229AAPL3783310-0.0152462017459754.50674101.93814875
4020111230AAT24013100.0038762011-8358.607451.
4120121231AAT24013100.0334192012424850.97291100.61415321
4220131231AAT24013100.0147482013150688.4442684.019567242
4320141231AAT24013100.0188932014300225.4073394.894390609
4420151231AAT2401310-0.0303942015-22794.53475.
4520161230AAT24013100.0818772016165216.9238685.435598202
4620171229AAT2401310-0.0215962017-94767.04474.
4720101231AAU20283300.06053820103931243.7099141.47847134
4820111230AAU2028330-0.0735292011-410410.8691.
4920121231AAU20283300.1485512012328675.9027196.369935846
5020131231AAU20283300.0086212013-652025.9662.
5120141231AAU2028330-0.1132082014-163560.1257.
5220151231AAU20283300.3038462015-486972.9477.
5320161230AAU2028330-0.1029632016336060.4918596.733758589
5420171229AAU20283300.157303201772934.98585273.231784479
5520101231AAWW49164200.0232772010511949.62871103.75549879
5620111230AAWW4916420-0.0904142011-267047.5305.
5720121231AAWW49164200.024032012150377.8605283.987951473
5820131231AAWW49164200.0716152013-92954.54066.
5920141231AAWW49164200.0799562014220093.0905789.918369866
6020151231AAWW49164200.0004842015-171720.9187.
6120161230AAWW49164200.0556682016280758.8045393.809028051
6220171229AAWW49164200.0155842017118298.2335780.348320345
6320101231AAXJ464288180.0563042010170325.8162585.906517378
6420111230AAXJ46428818-0.0415262011-195345.4494.
6520121231AAXJ464288180.0491592012248619.8025691.856211188
6620131231AAXJ46428818-0.0044220134897.620220638.536306307
6720131231AAXJ46428818-0.004422013-14436.81355.
6820141231AAXJ46428818-0.021851201417023.24731853.50841143
6920151231AAXJ46428818-0.0210942015-108511.0023.
7020161230AAXJ46428818-0.026487201634497.8297262.774887829
7120171229AAXJ464288180.0249082017427820.41603100.73060055
7220101231ABAX256710-0.005556201040271.21088164.881772674
7320111230ABAX2567100201120440.46351255.853844618
7420121231ABAX2567100.0095392012373972.6261898.493517253
7520131231ABAX2567100.112062201385847.8932575.598015301
7620141231ABAX256710-0.009932014410747.49059100.0506898
7720151231ABAX2567100.0492762015-23939.31161.
7820161230ABAX2567100.0228732016-54985.51838.
7920171229ABAX2567100.0160032017-62144.5521.
8020101231ABB375200.1596072010262051.5149692.698957139
8120111230ABB37520-0.007382011-148237.2988.
8220121231ABB375200.0705462012164741.8967185.391136548
8320131231ABB375200.039532013334546.098396.659748981
8420141231ABB37520-0.0566462014-167259.9219.
8520151231ABB37520-0.0579172015-126271.0164.
8620161230ABB375200.0313262016234601.9807990.930977606
8720171229ABB375200.0419582017328152.4994896.343865472
8820101231ABC3.07E130.1059972010385181.1890298.982469817
8920111230ABC3.07E130.001077201193041.02233476.778081639
9020121231ABC3.07E130.0227382012176883.3941686.49235934
9120131231ABC3.07E13-0.0031192013639304.68716107.56206589
9220141231ABC3.07E13-0.0097752014283120.6146693.944322993
9320151231ABC3.07E130.0513992015170520.3443185.92419131
9420161230ABC3.07E130.0025642016-244457.2604.
9520171229ABC3.07E130.0825282017216890.3883589.686624421
9620101231ABCW32839100.36363620101668667.2807124.83624042
9720111031ABCW32839100.0305052011-620932.9121.
9820101231ABG43436100.1740792010819065.45448111.89187423
9920111230ABG43436100.0933062011188186.274887.456953737
10020121231ABG43436100.0609472012535908.11737104.53342015

 

 

Should I be concerned about the missing values under the new variable? It seems to do with negative growth rates in the other variables. Here's the log:

 

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
70
71 data paper.CSRP_Monthly_Stock;
72 set paper.CSRP_Monthly_Stock;
73 by ticker date;
74 retain annual_return 1;
75 annual_return = annual_return * (1 + RET);
76 month = int( mod(date,10000) / 100);
77 if month = 12 or last.ticker;
78 mnth_gmean= 100*(exp(log(annual_return)/12)-1);
79 annual_return = (annual_return - 1) * 100;
80 output;
81 annual_return = 1;
82 run;
 
NOTE: Invalid argument to function LOG(-385.5522418) at line 78 column 26.
PERMNO=16347 date=20161230 NAMEENDT=. SHRCD=11 EXCHCD=1 SICCD=3334 NCUSIP=1387210 TICKER=AA COMNAM=ALCOA CORP SHRCLS= TSYMBOL=AA
NAICS=331313 PRIMEXCH=N TRDSTAT=A SECSTAT=R PERMCO=55718 ISSUNO=0 HEXCD=1 HSICCD=3334 CUSIP=1387210 DCLRDT=. DLAMT= DLPDT=
DLSTCD= NEXTDT= PAYDT=. RCRDDT=. SHRFLG=0 HSICMG= HSICIG= DISTCD=. DIVAMT=. FACPR=. FACSHR=. ACPERM= ACCOMP= SHRENDDT=20170130
NWPERM= DLRETX= DLPRC= DLRET= TRTSCD=. NMSIND=. MMCNT=. NSDINX=. BIDLO=28.08 ASKHI=32.05 PRC=28.08 VOL=802781 RET=-0.030721
BID=28.08 ASK=28.09 SHROUT=182869 CFACPR=1 CFACSHR=1 ALTPRC=28.08 SPREAD= ALTPRCDT=20161230 RETX=-0.030721 vwretd=0.018775
vwretx=0.016633 ewretd=0.015889 ewretx=0.01242 sprtrn=0.018201 year=2016 annual_return=1 month=12 mnth_gmean=. FIRST.TICKER=1
LAST.TICKER=0 FIRST.date=1 LAST.date=1 _ERROR_=1 _N_=1
NOTE: Invalid argument to function LOG(-2391.558353) at line 78 column 26.
PERMNO=14945 date=20151231 NAMEENDT=. SHRCD=11 EXCHCD=1 SICCD=6799 NCUSIP=30710 TICKER=AAC COMNAM=A A C HOLDINGS SHRCLS=
TSYMBOL=AAC NAICS=523910 PRIMEXCH=N TRDSTAT=A SECSTAT=R PERMCO=55038 ISSUNO=0 HEXCD=1 HSICCD=6799 CUSIP=30710 DCLRDT=. DLAMT=
DLPDT= DLSTCD= NEXTDT= PAYDT=. RCRDDT=. SHRFLG=0 HSICMG= HSICIG= DISTCD=. DIVAMT=. FACPR=. FACSHR=. ACPERM= ACCOMP=
SHRENDDT=20160128 NWPERM= DLRETX= DLPRC= DLRET= TRTSCD=. NMSIND=. MMCNT=. NSDINX=. BIDLO=18.55 ASKHI=24.72 PRC=19.06 VOL=60903
RET=-0.223309 BID=18.98 ASK=19.04 SHROUT=22407 CFACPR=1 CFACSHR=1 ALTPRC=19.06 SPREAD= ALTPRCDT=20151231 RETX=-0.223309
vwretd=-0.022257 vwretx=-0.024498 ewretd=-0.035887 ewretx=-0.039462 sprtrn=-0.01753 year=2015 annual_return=1 month=12 mnth_gmean=.
FIRST.TICKER=0 LAST.TICKER=0 FIRST.date=1 LAST.date=1 _ERROR_=1 _N_=5
NOTE: Invalid argument to function LOG(-4704.226346) at line 78 column 26.
PERMNO=14945 date=20161230 NAMEENDT=. SHRCD=11 EXCHCD=1 SICCD=6799 NCUSIP=30710 TICKER=AAC COMNAM=A A C HOLDINGS SHRCLS=
TSYMBOL=AAC NAICS=523910 PRIMEXCH=N TRDSTAT=A SECSTAT=R PERMCO=55038 ISSUNO=0 HEXCD=1 HSICCD=6799 CUSIP=30710 DCLRDT=. DLAMT=
DLPDT= DLSTCD= NEXTDT= PAYDT=. RCRDDT=. SHRFLG=0 HSICMG= HSICIG= DISTCD=. DIVAMT=. FACPR=. FACSHR=. ACPERM= ACCOMP=
SHRENDDT=20170130 NWPERM= DLRETX= DLPRC= DLRET= TRTSCD=. NMSIND=. MMCNT=. NSDINX=. BIDLO=6.86 ASKHI=8.57 PRC=7.24 VOL=46692
RET=-0.137068 BID=7.24 ASK=7.25 SHROUT=23707 CFACPR=1 CFACSHR=1 ALTPRC=7.24 SPREAD= ALTPRCDT=20161230 RETX=-0.137068
vwretd=0.018775 vwretx=0.016633 ewretd=0.015889 ewretx=0.01242 sprtrn=0.018201 year=2016 annual_return=1 month=12 mnth_gmean=.
FIRST.TICKER=0 LAST.TICKER=0 FIRST.date=1 LAST.date=1 _ERROR_=1 _N_=6
NOTE: Invalid argument to function LOG(-300.9914677) at line 78 column 26.
PERMNO=15580 date=20111230 NAMEENDT=. SHRCD=11 EXCHCD=3 SICCD=6320 NCUSIP=4820910 TICKER=AAME COMNAM=ATLANTIC AMERIC SHRCLS=
TSYMBOL=AAME NAICS=524113 PRIMEXCH=Q TRDSTAT=A SECSTAT=R PERMCO=5 ISSUNO=5 HEXCD=3 HSICCD=6320 CUSIP=4820910 DCLRDT=. DLAMT=
DLPDT= DLSTCD= NEXTDT= PAYDT=. RCRDDT=. SHRFLG=0 HSICMG= HSICIG= DISTCD=. DIVAMT=. FACPR=. FACSHR=. ACPERM= ACCOMP=
SHRENDDT=20120308 NWPERM= DLRETX= DLPRC= DLRET= TRTSCD=1 NMSIND=5 MMCNT=21 NSDINX=5 BIDLO=1.89 ASKHI=2.027 PRC=1.97 VOL=1103
RET=-0.01005 BID=1.98 ASK=1.99 SHROUT=21274 CFACPR=1 CFACSHR=1 ALTPRC=1.97 SPREAD= ALTPRCDT=20111230 RETX=-0.01005 vwretd=0.003702
vwretx=0.001454 ewretd=-0.003561 ewretx=-0.00702 sprtrn=0.008533 year=2011 annual_return=1 month=12 mnth_gmean=. FIRST.TICKER=0
LAST.TICKER=0 FIRST.date=1 LAST.date=1 _ERROR_=1 _N_=9
NOTE: Invalid argument to function LOG(-149.7160054) at line 78 column 26.
PERMNO=15580 date=20141231 NAMEENDT=. SHRCD=11 EXCHCD=3 SICCD=6320 NCUSIP=4820910 TICKER=AAME COMNAM=ATLANTIC AMERIC SHRCLS=
TSYMBOL=AAME NAICS=524113 PRIMEXCH=Q TRDSTAT=A SECSTAT=R PERMCO=5 ISSUNO=5 HEXCD=3 HSICCD=6320 CUSIP=4820910 DCLRDT=. DLAMT=
DLPDT= DLSTCD= NEXTDT= PAYDT=. RCRDDT=. SHRFLG=0 HSICMG= HSICIG= DISTCD=. DIVAMT=. FACPR=. FACSHR=. ACPERM= ACCOMP=
SHRENDDT=20150316 NWPERM= DLRETX= DLPRC= DLRET= TRTSCD=1 NMSIND=5 MMCNT=21 NSDINX=5 BIDLO=3.83 ASKHI=4.03 PRC=4.03 VOL=818
RET=0.046753 BID=4.01 ASK=4.03 SHROUT=20582 CFACPR=1 CFACSHR=1 ALTPRC=4.03 SPREAD= ALTPRCDT=20141231 RETX=0.046753 vwretd=-0.003616
vwretx=-0.005754 ewretd=-0.00019 ewretx=-0.003562 sprtrn=-0.004189 year=2014 annual_return=1 month=12 mnth_gmean=. FIRST.TICKER=0
LAST.TICKER=0 FIRST.date=1 LAST.date=1 _ERROR_=1 _N_=12
NOTE: Invalid argument to function LOG(-1935.048557) at line 78 column 26.
PERMNO=15580 date=20161230 NAMEENDT=. SHRCD=11 EXCHCD=3 SICCD=6320 NCUSIP=4820910 TICKER=AAME COMNAM=ATLANTIC AMERIC SHRCLS=
TSYMBOL=AAME NAICS=524113 PRIMEXCH=Q TRDSTAT=A SECSTAT=R PERMCO=5 ISSUNO=5 HEXCD=3 HSICCD=6320 CUSIP=4820910 DCLRDT=. DLAMT=
DLPDT= DLSTCD= NEXTDT= PAYDT=. RCRDDT=. SHRFLG=0 HSICMG= HSICIG= DISTCD=. DIVAMT=. FACPR=. FACSHR=. ACPERM= ACCOMP=
SHRENDDT=20170330 NWPERM= DLRETX= DLPRC= DLRET= TRTSCD=1 NMSIND=5 MMCNT=23 NSDINX=5 BIDLO=3.6733 ASKHI=4.1 PRC=4.1 VOL=800
RET=0.025 BID=4.1 ASK=4.2 SHROUT=20447 CFACPR=1 CFACSHR=1 ALTPRC=4.1 SPREAD= ALTPRCDT=20161230 RETX=0.025 vwretd=0.018775
vwretx=0.016633 ewretd=0.015889 ewretx=0.01242 sprtrn=0.018201 year=2016 annual_return=1 month=12 mnth_gmean=. FIRST.TICKER=0
LAST.TICKER=0 FIRST.date=1 LAST.date=1 _ERROR_=1 _N_=14
NOTE: Invalid argument to function LOG(-1579.877759) at line 78 column 26.
PERMNO=15580 date=20171229 NAMEENDT=. SHRCD=11 EXCHCD=3 SICCD=6320 NCUSIP=4820910 TICKER=AAME COMNAM=ATLANTIC AMERIC SHRCLS=
TSYMBOL=AAME NAICS=524113 PRIMEXCH=Q TRDSTAT=A SECSTAT=R PERMCO=5 ISSUNO=5 HEXCD=3 HSICCD=6320 CUSIP=4820910 DCLRDT=. DLAMT=0
DLPDT= DLSTCD=1 NEXTDT= PAYDT=. RCRDDT=. SHRFLG=0 HSICMG= HSICIG= DISTCD=. DIVAMT=. FACPR=. FACSHR=. ACPERM= ACCOMP=
SHRENDDT=. NWPERM=0 DLRETX=A DLPRC=0 DLRET=A TRTSCD=1 NMSIND=5 MMCNT=23 NSDINX=5 BIDLO=3.1 ASKHI=3.85 PRC=3.4 VOL=1411 RET=-0.055556
BID=3.3 ASK=3.4 SHROUT=20439 CFACPR=1 CFACSHR=1 ALTPRC=3.4 SPREAD= ALTPRCDT=20171229 RETX=-0.055556 vwretd=0.012163 vwretx=0.010303
ewretd=0.01422 ewretx=0.011149 sprtrn=0.009832 year=2017 annual_return=1 month=12 mnth_gmean=. FIRST.TICKER=0 LAST.TICKER=1
FIRST.date=1 LAST.date=1 _ERROR_=1 _N_=15
NOTE: Invalid argument to function LOG(-2350.148392) at line 78 column 26.
PERMNO=10517 date=20151231 NAMEENDT=. SHRCD=11 EXCHCD=1 SICCD=7359 NCUSIP=253530 TICKER=AAN COMNAM=AARONS INC SHRCLS= TSYMBOL=AAN
NAICS=443141 PRIMEXCH=N TRDSTAT=A SECSTAT=R PERMCO=5674 ISSUNO=7016 HEXCD=1 HSICCD=7359 CUSIP=253530 DCLRDT=. DLAMT= DLPDT=
DLSTCD= NEXTDT= PAYDT=. RCRDDT=. SHRFLG=0 HSICMG= HSICIG= DISTCD=. DIVAMT=. FACPR=. FACSHR=. ACPERM= ACCOMP= SHRENDDT=20160128
NWPERM= DLRETX= DLPRC= DLRET= TRTSCD=5 NMSIND=2 MMCNT=2 NSDINX=1 BIDLO=21.74 ASKHI=24.58 PRC=22.39 VOL=179446 RET=-0.077462
BID=22.39 ASK=22.4 SHROUT=72593 CFACPR=1 CFACSHR=1 ALTPRC=22.39 SPREAD= ALTPRCDT=20151231 RETX=-0.077462 vwretd=-0.022257
vwretx=-0.024498 ewretd=-0.035887 ewretx=-0.039462 sprtrn=-0.01753 year=2015 annual_return=1 month=12 mnth_gmean=. FIRST.TICKER=0
LAST.TICKER=0 FIRST.date=1 LAST.date=1 _ERROR_=1 _N_=21
NOTE: Invalid argument to function LOG(-329.6591721) at line 78 column 26.
PERMNO=14593 date=20151231 NAMEENDT=. SHRCD=11 EXCHCD=3 SICCD=3571 NCUSIP=3783310 TICKER=AAPL COMNAM=APPLE INC SHRCLS= TSYMBOL=AAPL
NAICS=334111 PRIMEXCH=Q TRDSTAT=A SECSTAT=R PERMCO=7 ISSUNO=8 HEXCD=3 HSICCD=3571 CUSIP=3783310 DCLRDT=. DLAMT= DLPDT= DLSTCD=
NEXTDT= PAYDT=. RCRDDT=. SHRFLG=0 HSICMG= HSICIG= DISTCD=. DIVAMT=. FACPR=. FACSHR=. ACPERM= ACCOMP= SHRENDDT=20160107 NWPERM=
DLRETX= DLPRC= DLRET= TRTSCD=1 NMSIND=6 MMCNT=63 NSDINX=1 BIDLO=105.26 ASKHI=119.03 PRC=105.26 VOL=9237703 RET=-0.110228
BID=105.22 ASK=105.23 SHROUT=5544487 CFACPR=1 CFACSHR=1 ALTPRC=105.26 SPREAD= ALTPRCDT=20151231 RETX=-0.110228 vwretd=-0.022257
vwretx=-0.024498 ewretd=-0.035887 ewretx=-0.039462 sprtrn=-0.01753 year=2015 annual_return=1 month=12 mnth_gmean=. FIRST.TICKER=0
LAST.TICKER=0 FIRST.date=1 LAST.date=1 _ERROR_=1 _N_=37
NOTE: Invalid argument to function LOG(-82.58607451) at line 78 column 26.
PERMNO=12513 date=20111230 NAMEENDT=. SHRCD=18 EXCHCD=1 SICCD=6798 NCUSIP=2401310 TICKER=AAT COMNAM=AMERICAN ASSETS SHRCLS=
TSYMBOL=AAT NAICS=531190 PRIMEXCH=N TRDSTAT=A SECSTAT=R PERMCO=53645 ISSUNO=0 HEXCD=1 HSICCD=6798 CUSIP=2401310 DCLRDT=20111108
DLAMT= DLPDT= DLSTCD= NEXTDT= PAYDT=20111229 RCRDDT=20111215 SHRFLG=0 HSICMG= HSICIG= DISTCD=1232 DIVAMT=0.21 FACPR=0 FACSHR=0
ACPERM= ACCOMP= SHRENDDT=20120130 NWPERM= DLRETX= DLPRC= DLRET= TRTSCD=. NMSIND=. MMCNT=. NSDINX=. BIDLO=19.96 ASKHI=20.99
PRC=20.51 VOL=58936 RET=0.003876 BID=20.5 ASK=20.51 SHROUT=39284 CFACPR=1 CFACSHR=1 ALTPRC=20.51 SPREAD= ALTPRCDT=20111230
RETX=-0.006298 vwretd=0.003702 vwretx=0.001454 ewretd=-0.003561 ewretx=-0.00702 sprtrn=0.008533 year=2011 annual_return=1 month=12
mnth_gmean=. FIRST.TICKER=1 LAST.TICKER=0 FIRST.date=1 LAST.date=1 _ERROR_=1 _N_=40
NOTE: Invalid argument to function LOG(-226.9453475) at line 78 column 26.
PERMNO=12513 date=20151231 NAMEENDT=. SHRCD=18 EXCHCD=1 SICCD=6798 NCUSIP=2401310 TICKER=AAT COMNAM=AMERICAN ASSETS SHRCLS=
TSYMBOL=AAT NAICS=531190 PRIMEXCH=N TRDSTAT=A SECSTAT=R PERMCO=53645 ISSUNO=0 HEXCD=1 HSICCD=6798 CUSIP=2401310 DCLRDT=20151103
DLAMT= DLPDT= DLSTCD= NEXTDT= PAYDT=20151223 RCRDDT=20151209 SHRFLG=0 HSICMG= HSICIG= DISTCD=1232 DIVAMT=0.25 FACPR=0 FACSHR=0
ACPERM= ACCOMP= SHRENDDT=20160128 NWPERM= DLRETX= DLPRC= DLRET= TRTSCD=. NMSIND=. MMCNT=. NSDINX=. BIDLO=38.14 ASKHI=40.11
PRC=38.35 VOL=44040 RET=-0.030394 BID=38.35 ASK=38.36 SHROUT=45346 CFACPR=1 CFACSHR=1 ALTPRC=38.35 SPREAD= ALTPRCDT=20151231
RETX=-0.036674 vwretd=-0.022257 vwretx=-0.024498 ewretd=-0.035887 ewretx=-0.039462 sprtrn=-0.01753 year=2015 annual_return=1
month=12 mnth_gmean=. FIRST.TICKER=0 LAST.TICKER=0 FIRST.date=1 LAST.date=1 _ERROR_=1 _N_=44
NOTE: Invalid argument to function LOG(-946.6704474) at line 78 column 26.
PERMNO=12513 date=20171229 NAMEENDT=. SHRCD=18 EXCHCD=1 SICCD=6798 NCUSIP=2401310 TICKER=AAT COMNAM=AMERICAN ASSETS SHRCLS=
TSYMBOL=AAT NAICS=531190 PRIMEXCH=N TRDSTAT=A SECSTAT=R PERMCO=53645 ISSUNO=0 HEXCD=1 HSICCD=6798 CUSIP=2401310 DCLRDT=20171025
DLAMT=0 DLPDT= DLSTCD=1 NEXTDT= PAYDT=20171221 RCRDDT=20171207 SHRFLG=0 HSICMG= HSICIG= DISTCD=1232 DIVAMT=0.27 FACPR=0 FACSHR=0
ACPERM= ACCOMP= SHRENDDT=20171229 NWPERM=0 DLRETX=A DLPRC=0 DLRET=A TRTSCD=. NMSIND=. MMCNT=. NSDINX=. BIDLO=37.66 ASKHI=39.54
PRC=38.24 VOL=44327 RET=-0.021596 BID=38.22 ASK=38.24 SHROUT=47128 CFACPR=1 CFACSHR=1 ALTPRC=38.24 SPREAD= ALTPRCDT=20171229
RETX=-0.028455 vwretd=0.012163 vwretx=0.010303 ewretd=0.01422 ewretx=0.011149 sprtrn=0.009832 year=2017 annual_return=1 month=12
mnth_gmean=. FIRST.TICKER=0 LAST.TICKER=1 FIRST.date=1 LAST.date=1 _ERROR_=1 _N_=46
NOTE: Invalid argument to function LOG(-4103.108691) at line 78 column 26.
PERMNO=91038 date=20111230 NAMEENDT=. SHRCD=12 EXCHCD=2 SICCD=1041 NCUSIP=2028310 TICKER=AAU COMNAM=ALMADEN MINERAL SHRCLS=
TSYMBOL=AAU NAICS=212221 PRIMEXCH=A TRDSTAT=A SECSTAT=R PERMCO=50013 ISSUNO=0 HEXCD=2 HSICCD=1041 CUSIP=2028330 DCLRDT=. DLAMT=
DLPDT= DLSTCD= NEXTDT= PAYDT=. RCRDDT=. SHRFLG=0 HSICMG= HSICIG= DISTCD=. DIVAMT=. FACPR=. FACSHR=. ACPERM= ACCOMP=
SHRENDDT=20120130 NWPERM= DLRETX= DLPRC= DLRET= TRTSCD=. NMSIND=. MMCNT=. NSDINX=. BIDLO=2.03 ASKHI=2.66 PRC=2.52 VOL=36682
RET=-0.073529 BID=2.5 ASK=2.52 SHROUT=57942 CFACPR=1 CFACSHR=1 ALTPRC=2.52 SPREAD= ALTPRCDT=20111230 RETX=-0.073529 vwretd=0.003702
vwretx=0.001454 ewretd=-0.003561 ewretx=-0.00702 sprtrn=0.008533 year=2011 annual_return=1 month=12 mnth_gmean=. FIRST.TICKER=0
LAST.TICKER=0 FIRST.date=1 LAST.date=1 _ERROR_=1 _N_=48
NOTE: Invalid argument to function LOG(-6519.259662) at line 78 column 26.
PERMNO=91038 date=20131231 NAMEENDT=. SHRCD=12 EXCHCD=2 SICCD=1041 NCUSIP=2028310 TICKER=AAU COMNAM=ALMADEN MINERAL SHRCLS=
TSYMBOL=AAU NAICS=212221 PRIMEXCH=A TRDSTAT=A SECSTAT=R PERMCO=50013 ISSUNO=0 HEXCD=2 HSICCD=1041 CUSIP=2028330 DCLRDT=. DLAMT=
DLPDT= DLSTCD= NEXTDT= PAYDT=. RCRDDT=. SHRFLG=0 HSICMG= HSICIG= DISTCD=. DIVAMT=. FACPR=. FACSHR=. ACPERM= ACCOMP=
SHRENDDT=20140130 NWPERM= DLRETX= DLPRC= DLRET= TRTSCD=. NMSIND=. MMCNT=. NSDINX=. BIDLO=1.03 ASKHI=1.17 PRC=1.17 VOL=24785
RET=0.008621 BID=1.16 ASK=1.17 SHROUT=64493 CFACPR=1 CFACSHR=1 ALTPRC=1.17 SPREAD= ALTPRCDT=20131231 RETX=0.008621 vwretd=0.026126
vwretx=0.024071 ewretd=0.025242 ewretx=0.02201 sprtrn=0.023563 year=2013 annual_return=1 month=12 mnth_gmean=. FIRST.TICKER=0
LAST.TICKER=0 FIRST.date=1 LAST.date=1 _ERROR_=1 _N_=50
NOTE: Invalid argument to function LOG(-1634.601257) at line 78 column 26.
PERMNO=91038 date=20141231 NAMEENDT=. SHRCD=12 EXCHCD=2 SICCD=1041 NCUSIP=2028310 TICKER=AAU COMNAM=ALMADEN MINERAL SHRCLS=
TSYMBOL=AAU NAICS=212221 PRIMEXCH=A TRDSTAT=A SECSTAT=R PERMCO=50013 ISSUNO=0 HEXCD=2 HSICCD=1041 CUSIP=2028330 DCLRDT=. DLAMT=
DLPDT= DLSTCD= NEXTDT= PAYDT=. RCRDDT=. SHRFLG=0 HSICMG= HSICIG= DISTCD=. DIVAMT=. FACPR=. FACSHR=. ACPERM= ACCOMP=
SHRENDDT=20150129 NWPERM= DLRETX= DLPRC= DLRET= TRTSCD=. NMSIND=. MMCNT=. NSDINX=. BIDLO=0.8738 ASKHI=1.05 PRC=0.94 VOL=33222
RET=-0.113208 BID=0.99 ASK=1.01 SHROUT=68728 CFACPR=1 CFACSHR=1 ALTPRC=0.94 SPREAD= ALTPRCDT=20141231 RETX=-0.113208
vwretd=-0.003616 vwretx=-0.005754 ewretd=-0.00019 ewretx=-0.003562 sprtrn=-0.004189 year=2014 annual_return=1 month=12 mnth_gmean=.
FIRST.TICKER=0 LAST.TICKER=0 FIRST.date=1 LAST.date=1 _ERROR_=1 _N_=51
NOTE: Invalid argument to function LOG(-4868.729477) at line 78 column 26.
PERMNO=91038 date=20151231 NAMEENDT=. SHRCD=12 EXCHCD=2 SICCD=1041 NCUSIP=2028330 TICKER=AAU COMNAM=ALMADEN MINERAL SHRCLS=
TSYMBOL=AAU NAICS=212221 PRIMEXCH=A TRDSTAT=A SECSTAT=R PERMCO=50013 ISSUNO=0 HEXCD=2 HSICCD=1041 CUSIP=2028330 DCLRDT=. DLAMT=
DLPDT= DLSTCD= NEXTDT= PAYDT=. RCRDDT=. SHRFLG=0 HSICMG= HSICIG= DISTCD=. DIVAMT=. FACPR=. FACSHR=. ACPERM= ACCOMP=
SHRENDDT=20160128 NWPERM= DLRETX= DLPRC= DLRET= TRTSCD=. NMSIND=. MMCNT=. NSDINX=. BIDLO=0.5199 ASKHI=0.72 PRC=0.678 VOL=20833
RET=0.303846 BID=0.67 ASK=0.69 SHROUT=77655 CFACPR=1 CFACSHR=1 ALTPRC=0.678 SPREAD= ALTPRCDT=20151231 RETX=0.303846
vwretd=-0.022257 vwretx=-0.024498 ewretd=-0.035887 ewretx=-0.039462 sprtrn=-0.01753 year=2015 annual_return=1 month=12 mnth_gmean=.
FIRST.TICKER=0 LAST.TICKER=0 FIRST.date=1 LAST.date=1 _ERROR_=1 _N_=52
NOTE: Invalid argument to function LOG(-2669.475305) at line 78 column 26.
PERMNO=91262 date=20111230 NAMEENDT=. SHRCD=11 EXCHCD=3 SICCD=4522 NCUSIP=4916420 TICKER=AAWW COMNAM=ATLAS AIR WORLD SHRCLS=
TSYMBOL=AAWW NAICS=481212 PRIMEXCH=Q TRDSTAT=A SECSTAT=R PERMCO=13927 ISSUNO=44028 HEXCD=3 HSICCD=4522 CUSIP=4916420 DCLRDT=.
DLAMT= DLPDT= DLSTCD= NEXTDT= PAYDT=. RCRDDT=. SHRFLG=0 HSICMG= HSICIG= DISTCD=. DIVAMT=. FACPR=. FACSHR=. ACPERM= ACCOMP=
SHRENDDT=20120131 NWPERM= DLRETX= DLPRC= DLRET= TRTSCD=1 NMSIND=6 MMCNT=50 NSDINX=6 BIDLO=35.81 ASKHI=40.47 PRC=38.43 VOL=54549
RET=-0.090414 BID=38.41 ASK=38.42 SHROUT=26305 CFACPR=1 CFACSHR=1 ALTPRC=38.43 SPREAD= ALTPRCDT=20111230 RETX=-0.090414
vwretd=0.003702 vwretx=0.001454 ewretd=-0.003561 ewretx=-0.00702 sprtrn=0.008533 year=2011 annual_return=1 month=12 mnth_gmean=.
FIRST.TICKER=0 LAST.TICKER=0 FIRST.date=1 LAST.date=1 _ERROR_=1 _N_=56
NOTE: Invalid argument to function LOG(-928.5454066) at line 78 column 26.
PERMNO=91262 date=20131231 NAMEENDT=. SHRCD=11 EXCHCD=3 SICCD=4522 NCUSIP=4916420 TICKER=AAWW COMNAM=ATLAS AIR WORLD SHRCLS=
TSYMBOL=AAWW NAICS=481212 PRIMEXCH=Q TRDSTAT=A SECSTAT=R PERMCO=13927 ISSUNO=44028 HEXCD=3 HSICCD=4522 CUSIP=4916420 DCLRDT=.
DLAMT= DLPDT= DLSTCD= NEXTDT= PAYDT=. RCRDDT=. SHRFLG=0 HSICMG= HSICIG= DISTCD=. DIVAMT=. FACPR=. FACSHR=. ACPERM= ACCOMP=
SHRENDDT=20140202 NWPERM= DLRETX= DLPRC= DLRET= TRTSCD=1 NMSIND=6 MMCNT=48 NSDINX=6 BIDLO=36.75 ASKHI=41.16 PRC=41.15 VOL=60287
RET=0.071615 BID=41.13 ASK=41.15 SHROUT=25039 CFACPR=1 CFACSHR=1 ALTPRC=41.15 SPREAD= ALTPRCDT=20131231 RETX=0.071615
vwretd=0.026126 vwretx=0.024071 ewretd=0.025242 ewretx=0.02201 sprtrn=0.023563 year=2013 annual_return=1 month=12 mnth_gmean=.
FIRST.TICKER=0 LAST.TICKER=0 FIRST.date=1 LAST.date=1 _ERROR_=1 _N_=58
NOTE: Invalid argument to function LOG(-1716.209187) at line 78 column 26.
PERMNO=91262 date=20151231 NAMEENDT=. SHRCD=11 EXCHCD=3 SICCD=4522 NCUSIP=4916420 TICKER=AAWW COMNAM=ATLAS AIR WORLD SHRCLS=
TSYMBOL=AAWW NAICS=481212 PRIMEXCH=Q TRDSTAT=A SECSTAT=R PERMCO=13927 ISSUNO=44028 HEXCD=3 HSICCD=4522 CUSIP=4916420 DCLRDT=.
DLAMT= DLPDT= DLSTCD= NEXTDT= PAYDT=. RCRDDT=. SHRFLG=0 HSICMG= HSICIG= DISTCD=. DIVAMT=. FACPR=. FACSHR=. ACPERM= ACCOMP=
SHRENDDT=20160210 NWPERM= DLRETX= DLPRC= DLRET= TRTSCD=1 NMSIND=6 MMCNT=46 NSDINX=6 BIDLO=38.37 ASKHI=43.77 PRC=41.34 VOL=53201
RET=0.000484 BID=41.34 ASK=41.36 SHROUT=24637 CFACPR=1 CFACSHR=1 ALTPRC=41.34 SPREAD= ALTPRCDT=20151231 RETX=0.000484
vwretd=-0.022257 vwretx=-0.024498 ewretd=-0.035887 ewretx=-0.039462 sprtrn=-0.01753 year=2015 annual_return=1 month=12 mnth_gmean=.
FIRST.TICKER=0 LAST.TICKER=0 FIRST.date=1 LAST.date=1 _ERROR_=1 _N_=60
NOTE: Invalid argument to function LOG(-1952.454494) at line 78 column 26.
WARNING: Limit set by ERRORS= option reached. Further errors of this type will not be printed.
PERMNO=92766 date=20111230 NAMEENDT=. SHRCD=73 EXCHCD=3 SICCD=6726 NCUSIP=46428818 TICKER=AAXJ COMNAM=ISHARES TRUST SHRCLS=
TSYMBOL=AAXJ NAICS=525990 PRIMEXCH=Q TRDSTAT=A SECSTAT=R PERMCO=37493 ISSUNO=61774 HEXCD=3 HSICCD=6726 CUSIP=46428818
DCLRDT=20101222 DLAMT= DLPDT= DLSTCD= NEXTDT= PAYDT=20111229 RCRDDT=20111222 SHRFLG=0 HSICMG= HSICIG= DISTCD=1242
DIVAMT=0.39113 FACPR=0 FACSHR=0 ACPERM= ACCOMP= SHRENDDT=. NWPERM= DLRETX= DLPRC= DLRET= TRTSCD=1 NMSIND=5 MMCNT=39 NSDINX=1
BIDLO=48.45 ASKHI=52.81 PRC=49.9 VOL=152528 RET=-0.041526 BID=49.85 ASK=49.87 SHROUT=40100 CFACPR=1 CFACSHR=1 ALTPRC=49.9 SPREAD=
ALTPRCDT=20111230 RETX=-0.04898 vwretd=0.003702 vwretx=0.001454 ewretd=-0.003561 ewretx=-0.00702 sprtrn=0.008533 year=2011
annual_return=1 month=12 mnth_gmean=. FIRST.TICKER=0 LAST.TICKER=0 FIRST.date=1 LAST.date=1 _ERROR_=1 _N_=64
NOTE: Missing values were generated as a result of performing an operation on missing values.
Each place is given by: (Number of times) at (Line)Smiley SadColumn).
11016 at 78:22 11016 at 78:44 11016 at 78:48
NOTE: Mathematical operations could not be performed at the following places. The results of the operations have been set to
missing values.
Each place is given by: (Number of times) at (Line)Smiley SadColumn).
11016 at 78:26
NOTE: There were 28648 observations read from the data set PAPER.CSRP_MONTHLY_STOCK.
NOTE: The data set PAPER.CSRP_MONTHLY_STOCK has 28648 observations and 68 variables.
NOTE: DATA statement used (Total process time):
real time 0.07 seconds
user cpu time 0.02 seconds
system cpu time 0.02 seconds
memory 3658.40k
OS Memory 31404.00k
Timestamp 03/15/2018 01:03:05 AM
Step Count 29 Switch Count 1
Page Faults 0
Page Reclaims 1070
Page Swaps 0
Voluntary Context Switches 432
Involuntary Context Switches 2
Block Input Operations 24096
Block Output Operations 24080
 
 
83
84 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
96
 
 
Let me know what you think--Thanks again!
 
-SAStuck

 

 

Trusted Advisor
Posts: 1,337

Re: Geometric mean of monthly return values

You annual_return values are wholly unrealistic - they're way out of scale.   For instance ticker AA has an annual_return of 1533755.5247, which when divided by 100 to see it as a ratio instead of a percentage is 15337.55... - suggesting that in one year the value of the holding (presumably including reinvested dividends) is over 15 thousand times its value at the beginning of the year.  No way.

 

This is also revealed by the fact that annual_return should never be lower than minus 100%.  Yet you have values like -470522.6346.  Something is very wrong.  That is why you have missing geometric means.

 

You need to study what is happening to your data.  Take one ticker and one year.  Look at it record by record and compare your results to what it should be (calculated manually or with a spreadsheet if you wish).

Regular Contributor
Posts: 184

Re: Geometric mean of monthly return values

[ Edited ]

I looked at AA in excel. The geometric mean for the year 2017 using the monthly return values is 0.29791846. This does not match up to the output for annual_year:


row year     ticker  ret              annual_year 
220171229AA0.2977691.844825974

 

but it is close to the value for ret.

 

Could it be that, given the way my data is sorted, I can use ret as my annual variable? Let me know what you think. In the meantime, I will calculate the geomean for other companies and see if the result is similar. I agree, though, the annual return variable is not looking good. 

 

Thanks for the help!

 

EDIT: I am checking over the geomean function I did in excel--I think I did it improperly, using a comma instead of a colon. I don't think I can take the geometric mean if the growth rates are negative?

Trusted Advisor
Posts: 1,337

Re: Geometric mean of monthly return values

If your annual_year=91.84482974, representing a 91.84% return for 2017, then your geomean monthly return is not 0.299 as you appear to think.

 

  • A       annual_year return percent = 91.84482974/100
  • B       A/100 = .91... annual return as a ratio
  • C       B+1 = 1.91 .... actual value relative to starting amount
  • D       D=log(C)
  • E       E=D/12     (log of 1 plus monthly return)
  • F       F=exp(E)    monthly return plus 1
  • G       G=F-1      monthly return
  • E       100*G = 5.5793964357%   monthly return percent

 

To test calculate      (1+.055793964357)**12 - 1    ===>  .9184.....

Regular Contributor
Posts: 184

Re: Geometric mean of monthly return values

Thank you for checking on that for me. I just confirmed on Yahoo finance that AA saw 92.81% change over the course of 2017, so these numbers look good 

 

I am not sure why the results were so unrealistic earlier? I will check my code now

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 179 views
  • 2 likes
  • 4 in conversation