## Geometric mean of monthly return values

Solved
Highlighted
Regular Contributor
Posts: 184

# 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
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);

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.

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;
``````

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
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:

 row date ticker cusip ret year annual_return      mnth_gmean
 2 20171229 AA 1387210 0.29776 2017 1.53376e+06 123.262
 3 20171229 AABA 2134610 -0.002997 2017 270363 93.2009
 4 20141231 AAC 30710 0.071379 2014 1.34112e+06 120.779
 5 20151231 AAC 30710 -0.223309 2015 -239256 .
 6 20161230 AAC 30710 -0.137068 2016 -470523 .
 7 20171229 AAC 30710 -0.014239 2017 226261 90.3561
 8 20101231 AAME 4820910 0.068421 2010 658076 108.063
 9 20111230 AAME 4820910 -0.01005 2011 -30199.1 .
 10 20121231 AAME 4820910 0.064407 2012 674676 108.495
 11 20131231 AAME 4820910 0.022474 2013 336320 96.7464
 12 20141231 AAME 4820910 0.046753 2014 -15071.6 .
 13 20151231 AAME 4820910 0.043933 2015 255878 92.3167
 14 20161230 AAME 4820910 0.025 2016 -193605 .
 15 20171229 AAME 4820910 -0.055556 2017 -158088 .
 16 20101231 AAN 253530 0.023594 2010 488519 102.962
 17 20111230 AAN 253530 0.015221 2011 310436 95.4382
 18 20121231 AAN 253530 -0.014634 2012 50543.9 68.0263
 19 20131231 AAN 253530 0.026536 2013 34175.9 62.6481
 20 20141231 AAN 253530 0.077167 2014 38808.7 64.3755
 21 20151231 AAN 253530 -0.077462 2015 -235115 .
 22 20161230 AAN 253530 0.099502 2016 514200 103.83
 23 20171229 AAN 253530 0.057264 2017 268176 93.0702
 24 20101231 AAON 36020 0.099805 2010 555974 105.161
 25 20111230 AAON 36020 -0.064811 2011 57587.1 69.8595
 26 20121231 AAON 36020 -0.008551 2012 26075.4 59.0344
 27 20131231 AAON 36020 0.038349 2013 1.40276e+06 121.608
 28 20141231 AAON 36020 0.080598 2014 58960.6 70.1929
 29 20151231 AAON 36020 -0.060299 2015 31807.3 61.6804
 30 20161230 AAON 36020 0.004559 2016 428507 100.757
 31 20171229 AAON 36020 0.006859 2017 109791 79.2311
 32 20101231 AAPL 3783310 0.03667 2010 559830 105.279
 33 20111230 AAPL 3783310 0.059655 2011 276287 93.55
 34 20121231 AAPL 3783310 -0.090738 2012 259683 92.5532
 35 20131231 AAPL 3783310 0.008902 2013 71553 72.9562
 36 20141231 AAPL 3783310 -0.071891 2014 338907 96.8721
 37 20151231 AAPL 3783310 -0.110228 2015 -33065.9 .
 38 20161230 AAPL 3783310 0.047955 2016 125930 81.2896
 39 20171229 AAPL 3783310 -0.015246 2017 459755 101.938
 40 20111230 AAT 2401310 0.003876 2011 -8358.61 .
 41 20121231 AAT 2401310 0.033419 2012 424851 100.614
 42 20131231 AAT 2401310 0.014748 2013 150688 84.0196
 43 20141231 AAT 2401310 0.018893 2014 300225 94.8944
 44 20151231 AAT 2401310 -0.030394 2015 -22794.5 .
 45 20161230 AAT 2401310 0.081877 2016 165217 85.4356
 46 20171229 AAT 2401310 -0.021596 2017 -94767 .
 47 20101231 AAU 2028330 0.060538 2010 3.93124e+06 141.478
 48 20111230 AAU 2028330 -0.073529 2011 -410411 .
 49 20121231 AAU 2028330 0.148551 2012 328676 96.3699
 50 20131231 AAU 2028330 0.008621 2013 -652026 .
 51 20141231 AAU 2028330 -0.113208 2014 -163560 .
 52 20151231 AAU 2028330 0.303846 2015 -486973 .
 53 20161230 AAU 2028330 -0.102963 2016 336060 96.7338
 54 20171229 AAU 2028330 0.157303 2017 72935 73.2318
 55 20101231 AAWW 4916420 0.023277 2010 511950 103.755
 56 20111230 AAWW 4916420 -0.090414 2011 -267048 .
 57 20121231 AAWW 4916420 0.02403 2012 150378 83.988
 58 20131231 AAWW 4916420 0.071615 2013 -92954.5 .
 59 20141231 AAWW 4916420 0.079956 2014 220093 89.9184
 60 20151231 AAWW 4916420 0.000484 2015 -171721 .
 61 20161230 AAWW 4916420 0.055668 2016 280759 93.809
 62 20171229 AAWW 4916420 0.015584 2017 118298 80.3483
 63 20101231 AAXJ 46428818 0.056304 2010 170326 85.9065
 64 20111230 AAXJ 46428818 -0.041526 2011 -195345 .
 65 20121231 AAXJ 46428818 0.049159 2012 248620 91.8562
 66 20131231 AAXJ 46428818 -0.00442 2013 4897.62 38.5363
 67 20131231 AAXJ 46428818 -0.00442 2013 -14436.8 .
 68 20141231 AAXJ 46428818 -0.021851 2014 17023.2 53.5084
 69 20151231 AAXJ 46428818 -0.021094 2015 -108511 .
 70 20161230 AAXJ 46428818 -0.026487 2016 34497.8 62.7749
 71 20171229 AAXJ 46428818 0.024908 2017 427820 100.731
 72 20101231 ABAX 256710 -0.005556 2010 40271.2 64.8818
 73 20111230 ABAX 256710 0 2011 20440.5 55.8538
 74 20121231 ABAX 256710 0.009539 2012 373973 98.4935
 75 20131231 ABAX 256710 0.112062 2013 85847.9 75.598
 76 20141231 ABAX 256710 -0.00993 2014 410747 100.051
 77 20151231 ABAX 256710 0.049276 2015 -23939.3 .
 78 20161230 ABAX 256710 0.022873 2016 -54985.5 .
 79 20171229 ABAX 256710 0.016003 2017 -62144.6 .
 80 20101231 ABB 37520 0.159607 2010 262052 92.699
 81 20111230 ABB 37520 -0.00738 2011 -148237 .
 82 20121231 ABB 37520 0.070546 2012 164742 85.3911
 83 20131231 ABB 37520 0.03953 2013 334546 96.6597
 84 20141231 ABB 37520 -0.056646 2014 -167260 .
 85 20151231 ABB 37520 -0.057917 2015 -126271 .
 86 20161230 ABB 37520 0.031326 2016 234602 90.931
 87 20171229 ABB 37520 0.041958 2017 328152 96.3439
 88 20101231 ABC 3.07e+13 0.105997 2010 385181 98.9825
 89 20111230 ABC 3.07e+13 0.001077 2011 93041 76.7781
 90 20121231 ABC 3.07e+13 0.022738 2012 176883 86.4924
 91 20131231 ABC 3.07e+13 -0.003119 2013 639305 107.562
 92 20141231 ABC 3.07e+13 -0.009775 2014 283121 93.9443
 93 20151231 ABC 3.07e+13 0.051399 2015 170520 85.9242
 94 20161230 ABC 3.07e+13 0.002564 2016 -244457 .
 95 20171229 ABC 3.07e+13 0.082528 2017 216890 89.6866
 96 20101231 ABCW 3283910 0.363636 2010 1.66867e+06 124.836
 97 20111031 ABCW 3283910 0.030505 2011 -620933 .
 98 20101231 ABG 4343610 0.174079 2010 819065 111.892
 99 20111230 ABG 4343610 0.093306 2011 188186 87.457
 100 20121231 ABG 4343610 0.060947 2012 535908 104.533

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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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)Column).
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)Column).
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

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
 2 20171229 AA 0.29776 91.8448

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?

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.