topic Re: Geometric mean of monthly return values in SAS Studio

Geometric mean of monthly return values

sastuck — Tue, 13 Mar 2018 15:46:24 GMT

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!

Re: Geometric mean of monthly return values

Rick_SAS — Tue, 13 Mar 2018 19:03:49 GMT

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.

Re: Geometric mean of monthly return values

Ksharp — Wed, 14 Mar 2018 13:33:05 GMT

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;

Re: Geometric mean of monthly return values

sastuck — Wed, 14 Mar 2018 15:49:40 GMT

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!

Re: Geometric mean of monthly return values

mkeintz — Wed, 14 Mar 2018 20:15:04 GMT

@Rick_SAS

I suspect what the user wants is

geometric mean of (1+return) - 1

in which case all the arguments will be positive.

Re: Geometric mean of monthly return values

mkeintz — Wed, 14 Mar 2018 21:27:09 GMT

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

Re: Geometric mean of monthly return values

sastuck — Thu, 15 Mar 2018 01:11:28 GMT

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

20171229

1387210

0.29776

2017

1533755.5247

123.26228808

20171229

AABA

2134610

-0.002997

2017

270363.46362

93.200857856

20141231

AAC

30710

0.071379

2014

1341124.3459

120.77935099

20151231

AAC

30710

-0.223309

2015

-239255.8353

20161230

AAC

30710

-0.137068

2016

-470522.6346

20171229

AAC

30710

-0.014239

2017

226261.23298

90.356117157

20101231

AAME

4820910

0.068421

2010

658075.50432

108.06313737

20111230

AAME

4820910

-0.01005

2011

-30199.14677

20121231

AAME

4820910

0.064407

2012

674676.12071

108.49547895

20131231

AAME

4820910

0.022474

2013

336319.96608

96.746408621

20141231

AAME

4820910

0.046753

2014

-15071.60054

20151231

AAME

4820910

0.043933

2015

255878.34089

92.31667199

20161230

AAME

4820910

0.025

2016

-193604.8557

20171229

AAME

4820910

-0.055556

2017

-158087.7759

20101231

AAN

253530

0.023594

2010

488518.92321

102.96174595

20111230

AAN

253530

0.015221

2011

310436.47712

95.438168973

20121231

AAN

253530

-0.014634

2012

50543.867176

68.026290038

20131231

AAN

253530

0.026536

2013

34175.875429

62.648119455

20141231

AAN

253530

0.077167

2014

38808.653302

64.375536486

20151231

AAN

253530

-0.077462

2015

-235114.8392

20161230

AAN

253530

0.099502

2016

514200.17148

103.82997714

20171229

AAN

253530

0.057264

2017

268176.22716

93.070171567

20101231

AAON

36020

0.099805

2010

555974.3979

105.16081156

20111230

AAON

36020

-0.064811

2011

57587.136275

69.859528806

20121231

AAON

36020

-0.008551

2012

26075.447722

59.034437296

20131231

AAON

36020

0.038349

2013

1402764.9079

121.607602

20141231

AAON

36020

0.080598

2014

58960.563067

70.192910485

20151231

AAON

36020

-0.060299

2015

31807.299406

61.680448027

20161230

AAON

36020

0.004559

2016

428507.3052

100.75743154

20171229

AAON

36020

0.006859

2017

109790.77357

79.231127104

20101231

AAPL

3783310

0.03667

2010

559830.31805

105.27898829

20111230

AAPL

3783310

0.059655

2011

276286.77103

93.54996822

20121231

AAPL

3783310

-0.090738

2012

259682.5333

92.553239238

20131231

AAPL

3783310

0.008902

2013

71553.009455

72.956227117

20141231

AAPL

3783310

-0.071891

2014

338907.49601

96.872070344

20151231

AAPL

3783310

-0.110228

2015

-33065.91721

20161230

AAPL

3783310

0.047955

2016

125929.81381

81.289551502

20171229

AAPL

3783310

-0.015246

2017

459754.50674

101.93814875

20111230

AAT

2401310

0.003876

2011

-8358.607451

20121231

AAT

2401310

0.033419

2012

424850.97291

100.61415321

20131231

AAT

2401310

0.014748

2013

150688.44426

84.019567242

20141231

AAT

2401310

0.018893

2014

300225.40733

94.894390609

20151231

AAT

2401310

-0.030394

2015

-22794.53475

20161230

AAT

2401310

0.081877

2016

165216.92386

85.435598202

20171229

AAT

2401310

-0.021596

2017

-94767.04474

20101231

AAU

2028330

0.060538

2010

3931243.7099

141.47847134

20111230

AAU

2028330

-0.073529

2011

-410410.8691

20121231

AAU

2028330

0.148551

2012

328675.90271

96.369935846

20131231

AAU

2028330

0.008621

2013

-652025.9662

20141231

AAU

2028330

-0.113208

2014

-163560.1257

20151231

AAU

2028330

0.303846

2015

-486972.9477

20161230

AAU

2028330

-0.102963

2016

336060.49185

96.733758589

20171229

AAU

2028330

0.157303

2017

72934.985852

73.231784479

20101231

AAWW

4916420

0.023277

2010

511949.62871

103.75549879

20111230

AAWW

4916420

-0.090414

2011

-267047.5305

20121231

AAWW

4916420

0.02403

2012

150377.86052

83.987951473

20131231

AAWW

4916420

0.071615

2013

-92954.54066

20141231

AAWW

4916420

0.079956

2014

220093.09057

89.918369866

20151231

AAWW

4916420

0.000484

2015

-171720.9187

20161230

AAWW

4916420

0.055668

2016

280758.80453

93.809028051

20171229

AAWW

4916420

0.015584

2017

118298.23357

80.348320345

20101231

AAXJ

46428818

0.056304

2010

170325.81625

85.906517378

20111230

AAXJ

46428818

-0.041526

2011

-195345.4494

20121231

AAXJ

46428818

0.049159

2012

248619.80256

91.856211188

20131231

AAXJ

46428818

-0.00442

2013

4897.6202206

38.536306307

20131231

AAXJ

46428818

-0.00442

2013

-14436.81355

20141231

AAXJ

46428818

-0.021851

2014

17023.247318

53.50841143

20151231

AAXJ

46428818

-0.021094

2015

-108511.0023

20161230

AAXJ

46428818

-0.026487

2016

34497.82972

62.774887829

20171229

AAXJ

46428818

0.024908

2017

427820.41603

100.73060055

20101231

ABAX

256710

-0.005556

2010

40271.210881

64.881772674

20111230

ABAX

256710

2011

20440.463512

55.853844618

20121231

ABAX

256710

0.009539

2012

373972.62618

98.493517253

20131231

ABAX

256710

0.112062

2013

85847.89325

75.598015301

20141231

ABAX

256710

-0.00993

2014

410747.49059

100.0506898

20151231

ABAX

256710

0.049276

2015

-23939.31161

20161230

ABAX

256710

0.022873

2016

-54985.51838

20171229

ABAX

256710

0.016003

2017

-62144.5521

20101231

ABB

37520

0.159607

2010

262051.51496

92.698957139

20111230

ABB

37520

-0.00738

2011

-148237.2988

20121231

ABB

37520

0.070546

2012

164741.89671

85.391136548

20131231

ABB

37520

0.03953

2013

334546.0983

96.659748981

20141231

ABB

37520

-0.056646

2014

-167259.9219

20151231

ABB

37520

-0.057917

2015

-126271.0164

20161230

ABB

37520

0.031326

2016

234601.98079

90.930977606

20171229

ABB

37520

0.041958

2017

328152.49948

96.343865472

20101231

ABC

3.07E13

0.105997

2010

385181.18902

98.982469817

20111230

ABC

3.07E13

0.001077

2011

93041.022334

76.778081639

20121231

ABC

3.07E13

0.022738

2012

176883.39416

86.49235934

20131231

ABC

3.07E13

-0.003119

2013

639304.68716

107.56206589

20141231

ABC

3.07E13

-0.009775

2014

283120.61466

93.944322993

20151231

ABC

3.07E13

0.051399

2015

170520.34431

85.92419131

20161230

ABC

3.07E13

0.002564

2016

-244457.2604

20171229

ABC

3.07E13

0.082528

2017

216890.38835

89.686624421

20101231

ABCW

3283910

0.363636

2010

1668667.2807

124.83624042

20111031

ABCW

3283910

0.030505

2011

-620932.9121

20101231

ABG

4343610

0.174079

2010

819065.45448

111.89187423

20111230

ABG

4343610

0.093306

2011

188186.2748

87.456953737

100

20121231

ABG

4343610

0.060947

2012

535908.11737

104.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;

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

84 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

Let me know what you think--Thanks again!

-SAStuck

Re: Geometric mean of monthly return values

mkeintz — Thu, 15 Mar 2018 02:28:05 GMT

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

Re: Geometric mean of monthly return values

sastuck — Thu, 15 Mar 2018 17:03:21 GMT

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

20171229

0.29776

91.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?

Re: Geometric mean of monthly return values

mkeintz — Thu, 15 Mar 2018 18:03:49 GMT

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

Re: Geometric mean of monthly return values

sastuck — Thu, 15 Mar 2018 20:34:43 GMT

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