ࡱ>  A>* !"#$%&'()+,-./0123456789:;<=?@BDPFGHIJKLMNOCQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxyz{|}~Root Entry FTkU'pDWorkbook,4_VBA_PROJECT_CUR"*&pU'pVBA(&pP'p \pHumberto Barreto Ba= ThisWorkbook=eE<X@"1Geneva1Geneva1Geneva1Geneva1Geneva"$"#,##0_);\("$"#,##0\)!"$"#,##0_);[Red]\("$"#,##0\)""$"#,##0.00_);\("$"#,##0.00\)'""$"#,##0.00_);[Red]\("$"#,##0.00\)7*2_("$"* #,##0_);_("$"* \(#,##0\);_("$"* "-"_);_(@_).))_(* #,##0_);_(* \(#,##0\);_(* "-"_);_(@_)?,:_("$"* #,##0.00_);_("$"* \(#,##0.00\);_("$"* "-"??_);_(@_)6+1_(* #,##0.00_);_(* \(#,##0.00\);_(* "-"??_);_(@_) 0.00000 0.0000 0.0000.0 0.0000000 0.000000                + ) , *  ( (     ` 0 DocPDocRegResults3beta0:beta1: ShowRegForm"ZR3  @@  ABRegression Statistics for BNumber of observations"Number of missing observations = 0Mean of Dep VarRMSECoefficient EstimatesVariableEstimateSE Intercept Robust SEAction8Altered Add-In so that Davidson & McKinnon's HC2 is usedDatePut in ref edit formsDPut in options for different types of RSE's; fixed no intercept casegNoncontiguous X's; option for output on same sheet; R2, Fstat, TSS, SSR; report residuals option added.VThis workbook handles robust SEs but is also a general purpose OLS regression add-in.TChanged name to OLS Regression; fixed f stat problem; added missing variables report%Changed properties to make title workAdded foreign language checkeAdded OLSReg function; corrected problem with F stat in cases with no intercept or just one regressorFixed Controls("Tools") problemCAdded inverse via Cholesky instead of MINVERSE to allow > 50 X vars"J ] Gcc    dMbP?_*+%MBaxter Mita printerS odXXLetterPRIV0''''\KhC0'ϊ"dXX??U} } $}  } m>} m         ~ ?jM?!tEA?~ @D_hlm?)A?~ @0Q |?)~ @6l2?)~ @^t?)~ @Rfh?)~ @ ~??)  ~  @I?)   @"@ M>?)   @$@ hɟ?)   @&@ =I^?)   @(@ Y?)    @*@ 27wǾ?)~ ,@hm?)~ .@@7 Sheet1     dMbP?_*+%"??#U}        ~ .@ ? s"?? Cԕ"?? VUUUUU?     """"""?  dgd?&5?wU?ݽ? l+2R?N?e_Ԋ?xwwwww?? XUUUUU? ?   ݭTUUUUU$,<22 JVV>@7 Sheet3 ThisWorkbook Sheet15MatrixRoutines  B__SRP_e E  !"#$%&'()*+,-./012346789:;<=>?@ABCDFGHIJKLMNOPQRSTUVWXYZ[\^_`abcdefgijkmnoprtuvwxz{|}~8 Z#<KsCCۗ,#FOs{kNaExOs{kNaEKsCCۗ,#MEPSLSS6"<<<<N0{00020819-0000-0000-C000-000000000046}8 @`|@T `X` %@ %0 %@@\ThH$*\Rffff*1Q488d9fad4"  >P X" ` 8h   D`$h L A@8 Display a message to the user that the add-in is ready6kA@o @1 Delete the new menu command from the Tools menu.Xcheck languageim] "!$$Won * "!&%( ,pTools' D=it's not English, try to get the word for Tools from the userftdThe language of this installation of Excel does not appear to be EnglishUS. Please enter the word that Excel is using for Tools on the main menu across the top of the screen. For example, in Spanish, it is Herramientas.Get the word for TOOLS Herramientas "%.' khd`Tools' kHOLS Regression ... Worksheet Menu Bar "%0%2%2B@4o\Attribute VB_Name = "ThisWorkbook" Bas0{00020P819-0C$0046} |GlobalSpacFalse dCreatablPredeclaIdTru BExposeTemplateDerivBustomizD2Sub _AddinInstall()  RegressionMenuItem ' Dis:y a mage to the uHseratadd-in is dy M!.End @ CUniD' Dele/new8nu commBa from5Tools.  On Error Resu0NextC@0check langu/ Dim myAs S@ng If Left(ApplicatA.Oper@ngSys!C, 1)W"` Then LSettings.ID(ms2oGUIEnglishUS5E)<@'it's not  , tryAdgeaword fBC8MAk EB 0InputBox("@8M of^vjBGExc@el doe$appeao be(US. Pl"eentBe 9)thCusL.one m am acrosstopCe screen. F@exa de, `un*`tRHerrami@as.", "G%OOLS!I, #2 eYIf(".`4,P),CYBars("Arshe@An ").Cohntr \(?("@\t ...fd l!n'#xME (SLSS<N0{00020820-0000-0000-C000-000000000046}@(%H@0hHxAttribute VB_Name = "She@et1" Bast0{00020820- C$0046} |Global!SpacFalse dCreatablPre declaIdTru BExposeTemplateDeriv$Bustom izD2rU~|  !B<x`"dXt`"dPx@t@(0%l p`"dx 4 8 $ 60 Fh :  8 .@ 0p & B D X 6p & < H X >h <  8 8 $P *x "       08 Hh  <p( h x  "  4 +  0 " 8 @ H /`   "    .   "  L BP    &  &  '  8 (H   N7xME ( < :H` x+@%I< ` FI>  OI@ SOIB t ID ecIF `Hxtipl`Jtft(N`Lpght N`( + <IP ds tIR n n IT si`Hx V` k@EIX `trixIT se o`Hxto y \P`0+KIX  SI^ ds sIT elem`Hxecto ``kQIb cMulIT ; Id  intIR x`Hxto t ftXvectalar@` h`MultultVecby j` (dIb H in IL hcIT y deId ixIR  Rec`Hp. 8 fhold used to hp ing purpXx l` **w nft (% <0t is(NumI by Dhne dmatricesP Frk f have on`>`@ >`B @  @ `H`J`L v ` 11Ix  IT 0 Iz P I| `H`J`L`~ n % `  h H    H P8 %0@` @p ` @ \  @ X 8 @H T ` @ P H @ @ @ 0 @P @  @ @  @8 @ @h @ @ @ @ @ @x  H  `@0h @d @` @HP @x@ @0 @ `@@Xp@h @d X@` Z@P @@ @0 @ 88@Ph @d (@` Ent@hP @@ @0 @ @ (`x@d @\ x @8X  @H P :@ 8 @( B\@`$ x8@  `@ @ `  hH$*\Rffff*1>488d9e2b*\R0*#15%  2 X D`   8 N X h p $;   "  4 D 8P           @ X p   " " (   (08Xp0&H p  ,  0H `    "   & 6 2 H HX    (  (08@Hh  (    0 8 @ H X ` h  ,  P ` px" & (  @P `p Matrix Routines ModuleIC, WARNING: pay close attention to dimensions!= Author: Frank Howland (though most all of this is obvious!)X Date: April 13, 20011 MatMult(result, NumI, NumJ, NumK, MLeft, Mright)G multiplies two matrices MLeft(NumI x NumK) into Mright (NumK x NumJ) u result is the outcomeIn Finddiag(diag, M, n)In- finds the diagonal elements in M, an n x n1 square matrix. Puts results into vector diag. MatVecMult(outcome, n, x, M)/ This routine multiplies (k x n) matrix into? the transpose of a (1 x n) vector to yield a k x 1 matrix3 outcome = a k length vector, not an k x 1 matrix SqrtVec(vec, n)1 finds square roots term by term of elements in' a vector vec of length n and returns* the new vector back in place of the old MatVecMult(outcome, k, n, x, M); multiplies a (1 x n) vector into a square (n x n) matrixx> and then into the transpose of the vector to yield a scalar outcome = xMx'0 **** WARNING: I haven't tested MatVecMult **** MultVecbyScalar(vec, scalar, n)6 multiplies each element of a vector vec of length nB by a scalar and returns the new vector back in place of the old choldc8 obtains the Cholesky decomposition of a square matrix6 based on Numerical Recipes, 2nd Edition, pp. 89-91 Testcholdc()1 used to test choldc--puts stuff on a worksheet TestMult() Purely for testing purposes$ Requires a sheet called TestSheet Inputs are Mright (NumI by NumK) Mleft (NumK by NumJ) Output is result (NumI by NumJ)* Calling routine dimensions these matricesB This routine won't work for vectors which have only one dimension]]]0 H > J @ H J+< L B H L$D L J$F H J$< H J+< L J Ho`H- Get the diagonal elements of an n x n matrix] H T H H$R H+P Ho] H T H$X$Z H+X Hoh]8 H T H$X ^ H+X HoPF This routine multiplies a (1 x n) vector into a square (n x n) matrix< and then into the transpose of the vector to yield a scalar outcome = xMx']]( Tf] Th H T H$d H+h H f T T R hA@:'b H T b H$f H$d 'b Ho8- This routine multiplies (k x n) matrix into> the transpose of a (1 x n) vector to yield a k x 1 matrix1 outcome = a k length vector, not an k x 1 matrix]] Lf8]@ Th H T H$d H+h H f L T R hA@: Here tempv H L H$f H+b Ho` Purely for testing purposes" Requires a sheet called TestSheet]]]8]p]]] TestSheet$r.n  n%t'> n%t'@ n%t'B > @< > BD8 B @F`]h]] H > L B H L n%t H L+D L H J @ L B L  J n%t L J+F L J < > @ B D FA@: H > J @ H J$< H J n,t J HoX performs Cholesky decomposition/ useful for finding out if matrix is invertible+ and for then efficiently inverting the x'x matrix!A Reference: Numerical Recipes in Fortran, 2nd Edition, pp. 89-91 Dim n As LongDim np As Long!Dim a(1 To np, 1 To np) As DoubleDim p(1 To n) As Double]p ] ] ] ] CholeskyTest$.n H T J T H J$x'~ H 'L L ~ H L$x J L$x '~ L 'L H J ~ Help!A@ k ~$Z H+|dx ~ H$| J H+xkP J H H T H$| H n,t J T H J$x H J n,t J H o  ]0 ]p CholeskyTest$.n H J H J n%t H J+MsgBox mat(i, j) J H H+ H  A@vop h Attribute VB_Name = "MaxRoutine@s" ' H L ModuledWARNING: pay close attention to dimens s!\bAuthor: Frank Howland (ugh most all of this obvious!)?Date: April 13, 2001Mult(res, N(umIJK,@ MLeftrightN miplies two mc%(! x%K) iniM :K J$;XthDe comFinddiag(?, nf ; onal (elet|n M, an n3np squareRx. Pu;Ovectorag.8VecH>, xCATYr{IC (k)DB?C!7tosp|@jpa (1 ~yield a k 1C"alengthV 'onxB I Sqrt2(  PErooCterm by 'TaDnZd ret0urnsC :neBw backA pplacxAld Yk)ZȚLaEx(}^C#@$nBb@e a+PI_scalarG]xMx' * xI ha ven't`'sted AAAIbyS" 1 2eachZ-/a 6` " 1?1"1choldc obtain#lCesky des@WiWjCp bas@ `eri` Recipes@Ed, pp. 89-9 Test#(DTu st " C--pwstuff a worksheeta"v$Pure0ly fzin`g puroTRpequim -l S Sub: R_FIn#I5#KMlD p cOut` # %C @sq7Sn(IM I 6 S Z_  ZI0L3ar@P"=i &* EW> 18b, XEy/yOcScOc/c/cJy{ '5@*cPtempq)a2DoubRe2,314x(_~ >P2p11$X@ % אl 1RM y p`9 +a+`* k+, ;Nnq~Z^!ߗyW noORmV"ro "o 1_oCnal' He@1 v,]S$S=rec&;{{{zoz14my}jW'e qbob 70zo,pXpS`e = s("=">_T.Cells(rm4@KF5cEb%EA3C/m$5*diq~*aK@ '1 ~= 7 + gBN*OьABMD Mright(K, J) = mysheet.Cells(7( + d4J) Next K J MatMult Res , NumI J MLeft,  For Im1 To-[JZr Yl(>I En`d Sub  choldc(AJ, np, pj' perfo rms Cesky decomposition ' useful  finding out if matrix is invertible a=then efficiepntly Xx'x"!Reference: ericaBlcipe2 tran, 21EdQep. `89-91!w'Dim n As` Long p a(`B)ADou1 pJIJKsumE uWork SetEs("gTest"@nG= A KI - @MWhile K > 0 A-K) *{@K@#KendIf¾xJ T , @r%px@t@x@p@lp@t@l x@t@l %l 6 pfg tfP2 xfX$ Txh"d`O+x@x@x@l0%l 50 xf`\T$ 0Xxh"d`S/x@x@l PB9 k0%l 5P xf` \X$ PXxh"d`R.x@x@l P@0%l 5P xf`lX$ P0|,&d"t&d"`xd"dX'x@lHBlxl`5H xfXf(`M(`ll(HltMd` 5H (db xd"d &; (lxltk8x@lHb 5H xf |,8 `tH0   0   0   0  CrU @` !9Qi//////0` __SRP_f]OLSFunctionE__SRP_3h__SRP_2 l*///,`//0` ///8!`////<I`/////$q`8`y!////$`nx(@> 6`68$0@p x  6- 8 gP fp       ~=( " ^ H (2@-X h3x , Hl3X 4 ( 0!Ppx "w& `      (`HRxME48> <8<< <<<<< <$<(<,<0<<< +`&N@ )`h%)x%i  ihwabash h-361% d %`b `` B`^artm`\ `PZ `T `8P4793`L `H`hF`D @X@`<`8 `4 0`H,`J(`x&`$  x @````H`( `L`x    (X@ `&x `4J )    ) X` @iii)  i$ )(X` @ i, i0``l `H h`Ld```^ `\`ZP`,X `.`T`0HP(`2L`4Hues `@@`B0<H D8 mismof obs i `Fx0trac`H(able J$ k of howp `L `N`ber  s%   whi%` `(  i` ` ing `0 area` C`  P ensuactual r  d`  !'  RH  H0 `T!h `V H X@  H  ZP (  3 0  \ thanvariableh  `^H`` smat  dX X er on X area@ ` f A g  |< l found  `l $  r 'hYou p t he Ye try ag `v  tha`x col`z   onlythe Y vax  . P ( @(H l Xh ` ``pX`HN 0H % hH*\R1*#1c8$*\Rffff*1O488d9fad.B   (08@HPX`hp  @     H 0H  x     2 < @ "8`  1 4   .   ( @  X   x   0   $s   &y( P X hx       n+X` .h   "6%@HP "  X`h   . PX`hpx        @ H h   ( 0 P  p   8 &  0H `"&h        *0 H h      & 0 ,@ Np : F "H  p J     !8!@7`7  '  0 z@  <  8@ `p(? 2!7 8Phpx&# <(*  HXx   * 08X` x (   0  F@  @ 0  00 4`     *  @ P X  p     &:   "  !  !  !0! H!X!(`!! !! !!0!!! " "" "!("H" `"x""><"" " # # * #P#!X# x#(C##2##  0#$!$ 8$(CH$p$2x$$  L$ $ Z$ @X% 2% 4% .& R8& J& 8& ' B ' \h' ' ' B' $0( X( `(h(x( ( (6$((0(()0) H) X) x) &) D) ) *(*"@*!h** * * * * * *+ ++ . + .P+ .+ .++,,0,H,`,x,,, >, -  -0-H-h---- -"---.# . 8.H.P.X.p.x.$."/.%. / // /(/0/H/ `/ Dx// / / / <0 P0 *`0&-0 00 %0 1 1 101x]]] ]8]P]h]]]]]]]](]`]x]The actual values go here]]]0]H2indicator for mismatch between no of obs in X cols]`0 keep track of how many x variables in each area]( keep track of how many obs in each area ]' count the number of valid observations]* keep track of which X areas have problems]] index for x variables]0"searching through cells in an area]H Check the X Var Range/ These checks ensure that we have actual ranges ]` !!'''   H  H %!! H+ H %!! H+  H$ ' H H$ H $' H ' H'kk H 3 `Unfortunately, this function cannot handle more than 51 independent variables. You've selected . Sorry!A@PkH Warning if mismatch is true The number of rows in X area 5 does not equal the number of observations in X area  . Please try again.  Data ProblemA@`kXA go through cells in each area (determine how many there will be) labels need to be found$ '  !! ' !!'  hYou must select the same number of rows for both the X variable(s) and the Y variable. Please try again.  Data ProblemA@k% Check that we have just one Y column  EYou must select only one column for the Y variable. Please try again.  Data ProblemA@0k(. Pass this information to the MatrixWork macrouW Check on labels '    ' H  J H$  ' J'  H %%t + $$The X variable label in column I you've chosen is a number. Do you really want the variable label to be  $? Potential Label Problem$'  Gjk  J H$' $]The Y variable label you've chosen is a number. Do you really want the variable label to be ? Potential Label Problem$'  Gjk  Start reading the dataA Must read in one row at a time across Y variable and X variables* Data is assumed to be in columnar format!' H ` Read y data first  '+remember first row is label so must add one5 We are sent to error handling if this isn't a number Now check for blanks H $ + H $$  '  we are going to skip this obs.k' If we've passed, go to the x variables' J  L J$ H J$ L '  'p* xvalues(countobs, j) = xRange(i + 1, j) Check for empty values  J %%t$  'd  J %%t  +k L Jx  '`X H End reading in data   cThere aren't enough observations with non-missing values to obtain parameter estimates. Try again.A@k' The Results matrix will contain output](`   'd0  'k $$   $ $  A 'i]]]]0On Error Resume Next]H]`]x]]]]]]( ]@ ]X ] ] ' stuff for placing output on same sheet] ] ] ] ]0 ]H ]` ]x ]  Error HandlingOn Error GoTo MsgBox_Err] ] ] Dim Y() As DoubleDim ytemp() As DoubleReDim ytemp(1 To nrows, 1)]P  Y, X and X' loading R ] ]  X ]  ZH ]P  \ ] ]   '`d '`k   `d  ` P   'B1 Nk tells us the number of parameters to estimate .  Y(intI, 1) = ytemp(intI, 1) 0  0 . 0+d 0 .+Pd . 0 $  . 0+d . 0$d 0 .+PkH 0 .d 'B . # Y(intI, 1) = ytemp(intI, 1) 0  . 0$  . 0+d . 0$d 0 .+P 0 .kP 'T .  T .$  'T . T 'T vector of coefficient estimates Needed:% (1) Run the regression [done]H (2) Obtain the residuals; call them e [handle multivariate case]3 (3) Obtain squared residuals; call them esq.? (4) Check on what to do if there is no intercept [to do] (1) a) Get the Y variables Run the regressionCOLSestimates = Application.WorksheetFunction.LinEst(Y(), x(), 0, 1) 2) X'X work Get X'X](  ` `d ]  ` `f d B B  P dA@: f B B  P dA@:]  B|8 H B H H+| H', f B | ,Ah (X'X)-1 work ,tMsgBox "There is perfect or near-perfect multicollinearity in the independent variables. Thus the regression fails."j8k0 (X'X)-1 work Get (X'X)-1]@ d "!n%p'l Get estimates]X B r B H $l H$P H+r Hd8 r B  B l PA@:k] Bt t B  r A@: Get residuals]0H'v'x'z H 'F x H$  T  'x6 Need to enter a loop here to sum up the fitted values L B F L$t H L$d 'F L H$  F H+R H$R H+X v H$X 'v z H$  'z H  z'xk$Time to choose where to put the data v'V v  B $Z'v]` B B$l$Z v+dP  l BA@N  BA@V  v BA@\k Get S0 matrix* It has the same dimensions as XTransposeX@ First , following Davidson and MacKinnnon, p. 553, divide each esq by (1-ht)9 to get ht, multiply the intI'th row of X into X'XInverse* and the transpose of the intI'th row of X] B) robust SE using Davidson and MacKinnon's- various approaches (p. 553) implemented here  .  .$X .+\ . e .  .$X   B  .+\ .d .   B . $d +  B$$l$'Hd H B  lA@`k H .+Z  .$Z .+\d .$X .$Z  .+\kpdh .$Z .+\d8 .$X .$Z  .+\kk .k] ` `h 0 `  `'@ .  . 0$d . $d .$\ @ 0 + 0 $'@ .  0$ Form the NOBS * X'X -1 S0 matrix.]p ` ` B$l$+dH  B B B l A@:k ) Form the RSE = X'X -1 S0 X'X - 1 matrix.] ` `( B$$l+d  B B B  lA@:k`E Now get the robust standard errors, square roots of diagonal entriesT Davidson and MacKinnon textbook (1993) p. 553 recommends using a correction factor9 in which one divides the estimate of the standard error+ by (1- ht) where ht is the square root of. the t'th diagonal entry in the "hat matrix".' This hat matrix is sometimes called PK because it projects orthogonally onto the space spanned by the columns ofC X. In this module, we denote P by XXTransposeXInverseXTranspose,1 which is a name reflecting the definition of P.< Stata uses a much simpler correction namely sqrt(N/(N-K)).U Davidson and MacKinnon (553-54) say that Stata's correction is inferior to dividing by (1-ht).< I don't compute the P matrix because it can get too large, with dimensions Nobs x Nobs]0H V x 'If Nk > 1 Then  x V  B  V  B 'd x V  B V  B 'k +ElseResults(4, 4) = "n/a"End If If IncludeIntercept = False Then= Results(4, 5) = "R2 Inappropriate with no intercept."End IfNo. obs.+ +No. missing obs.+   +F stat.+ HC0'N eHC1'N eHC2'NdHC3'Nk( regrange.Columns(4).ColumnWidth = 10( regrange.Columns(1).ColumnWidth = 18' regrange.Columns(2).ColumnWidth = 8' regrange.Columns(3).ColumnWidth = 8 Mean Dep Var+ T+RMSE+ v+SSR+ V+TSS+ x+R2+7With Results(4, 3).Characters(Start:=2, Length:=1).Font.Superscript = TrueEnd With +Variable+Estimate+SE+  ` Intercept+d $+k$t+$+ Bk H B H'^  B G H '^j ^$ H + H$t H + H$ H + H]`']x B . ` . .$ >Results(5 + Nk + marker, 1) = "RSE (" & intI & ") set to zero" . .+  'k . .$$Z .+ 6/ (1 - Sqr(XXTransposeXInverseXTranspose(intI, intI))) .$Put down a description of the matrixRSE ( N)+ Put down RSE . B .$ . + .joAttribute VB_Name = "OLSFunction " Op Explicit l Reg(xRange As , y al IncludeIntercepthBooleanxTrue, ,RobustChoic]5Hger(2)8V arianDim upperlLefR regr col'BRow siz ? occupJ$8low^aLong9 numxrows"NumYR 8varfCo l Xlabel(S#YTǂA nobG#xvaluesDouble 'The actu@z go hereC;y IE:, JCmyc0heckJ7mismatch 'indica`tor fbetween n o of 2in( X dsCnox3 ' keep tra hmany $x @Pia@ `henϣ <> - 1) 5!(&G \=` n!W`End8 IfeG I > 5)MsgBox "Untunately,@His f$ cannotInd`mocLn! ependentg. You' nselected " & f& ". Sorry!aGoDtryag ai %' Warniif F&truL`(,-%tEMA A doest eq^+IPKH/ P 9  .", Buttons:=vbE0xcla^, Title:="Data PSJ3gth,FV (de@mipR] will beY-' pePtoPPNund=b~=,`,(;gv%o:w&}=4.G7q1 V|0}i<+`55%@' m c'P,e s'CysbothXv+(s) a`4YV. o-;]X[jQ nY1|B<GR7 only@aoqQ3k ea_*_*on_*tT* Pass`:iFc p& MaxWork macronont)`(Q (cՙ'0,YX(%1/~k/garqIJ a`U1s+ 1WY= ~J7g3ll s>(` A10Is@ eric(mqOadfPw#= "("P/L0&P Py\chos aTp^Do  `l&wq壔sE1`?QvbYesNoG&Pot0eipLVQ$= vbNo (15eq`ErqYI("v?OBwEea7 _f_oRisn'6 nuZ7NWcheck fblankyvalues(, 1)UyRange(I U WIf IsEmpty( True Th,enn-' 2wego\esklip_. finished End IfkHwe've passed, gothe x variabletvx qJmya " K noxcols(J)@cellno@ I *@ @RK "'@ojAbxoioj@(' CeApGA$x.Area8.C57)T{%F= ?@} wEl>sE;@ri<,ENext DgJI~:+  ResxumeE[\@ Ia\0d"|n 5 <} soMsgBoxx "qZAe@nough lervations withTn-pmissa ho btain`krametestimates. @Try ag.F"%try  e,'sults rixll@Cn outDpuDim(@) As Vxnt, r@%ltdIntegaUncludeArceptnG=$+ 6Lt5ReB%a4lCaMat@WorkNewF(3l(),. e@7,, Y", X`B,'#, I ob ChoiceOLSReg!aL. 2`Ka3Func?! Sub s0Dou4Long, Num!P3S"(@xsa?# D&AMBoolean $"7yourseLtta\K aIKintasstay&f8ailSnewsuffp.*!!invertip Ja! 7OJ\sto@P C Nkcoef>yfittdhM,$ 3nY#myty0pehcQ' st placAsB sH eTCupperlef oregrqh`{:` ?owsize~am occuwT' \' "f b !'{s]N_pcXTPsppose;XmAKid*>*Y'1ya|3QߓQâ2m@9"' YM  X' loaam d j;ssresqO ahtm Pqbh "Ri[w@ActualRX_p1N'/l/l =?V"wCYmlX(fk x%kߴq`"Y=`6OaF< ' 0t us@eQqQofׇ#Qzq Y(0 C*Ulpt a1Xa!pQ, PI A qsH p9OntB'u! cЩ%RkcA 1X b qab="WS d= X(intI, PJ) NextH8I8 End If ymean = 0 ForbI1 To nobs\H+ Y1Y &/@' vect[of coefficient estimatec ' Ne`eded: (1) Run the regression [ done]'2)@ Obtaisiduals; callm ehandle multivarVi=s %3%squared<'sq.@4) C heck Owhat to do if>re isercept [a "ta) GeuY Nblc OLSQmApplicatI.Workshe@etFuncBLinEst(Y(), x0, 1Ap' 2X'X w&'&@Dim XTransposeX() As Dou,CReActeNumXVars !XX(lMatM}-, Nk,2G X` A-pUCpC,Nk83Nk( @$RoChoice*0v|? b +1@htq'G"71#a'6iQU11-S55uholOP KXֵOtUTPTpzatemph1">Sx>q4:nQA:`/ X q.:_2&!8A_dp $$#10YeALo 5   v -  n End If  Next` intI xDim S0() As DoubleFRe21 To ActualNumXVars4, )7FormJ =('+K!+/store-,0 #%I%no,ÍURSEUXAV sqOO K%Aq)( =&xS0,0-D HS' Now getTrobusteandard errosqua`xroots of diagonal enWe`v' ' Davidson  MacKinntbook (1993) p. 553 recommends using a cortifac in which on&e`` eses4tiP!Stata`6@a mu3simpler7 sqrt(N/(N-K))!D(`B-54) 8say - 's `inferi%diA`8(1- = "I don'tmpu"P8 b2canc`oo large#i"A@T s Rs@ebs xo BArI,t f Qt u1v- (ssr / tsspx'cX>wfX0IncludeInterceptpNTrue TZ= (( - )(]- 1)(vNk)E6FKr_-[VhERes^s(4, ='-"n/a"!dFa 7V 5i"R21ap`6priapwnos.qUlp"No. 2Q2^mis2M(pumxro'3TSS<2C; 3R'WQ4 har pz`(Zrt:=Length:=1).Fonts'.SupcriL' E@ S5yPia`Ь 2"~ET1xvar13 . 6Y gNXlabel7*p/S( " coef(@1Z SEvec LGo@skipnowq;o[iA= m<  %J1{\I\ 5 /`8Ip : xmarkЇxger $@Caг߿cq+a ;!wp@vI) <Z2 'Results(5 + Nk marker, 1) = "RSE (" @& intI") set to zero" `sq(Z,f=0Lea+ 1 $End If [YSSqr(k) /'/ (1 - +XXTransposeXInverW)"Nextb+'Put down a description of the m`atrix, 4Wmyty0pehc' dž,j For=1 To΃ މ?4a !Now: Sub rU~| 0   0 (  frU D`!9Q ==\`(!AaQkk(&0p(o#&nrU~| \rU 4` yk4` yk4` yk4` ykXPrimeXInverse&\__SRP_8 qr__SRP_9sJGeneral" has Cholesky Decomposition code, but uses MINVERSE in OLSReg add-inReB Limit of 51 Xs in Excel 2003 and 59 Xs in Excel 2007 (brilliant!)C Found Visual Basic 6.0 code below that takes inverse of a Cholesky2C) I accept (and meet) copyright conditions I downloaded the code from: http://www.alglib.net/matrixops/symmetric/invcholesky.php# I imported the .bas files into VBAtware I'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''JCopyright (c) 1992-2007 The University of Tennessee. All rights reserved. Contributors:ITY D * Sergey Bochkanov (ALGLIB project). Translation from FORTRAN toHE COP pseudocode.3See subroutines comments for additional copyrights.EXEMPLBRedistribution and use in source and binary forms, with or withoutFmodification, are permitted provided that the following conditions areTERRmet:WEVER @- Redistributions of source code must retain the above copyright, ? notice, this list of conditions and the following disclaimer.F C- Redistributions in binary form must reproduce the above copyright''''''E notice, this list of conditions and the following disclaimer listeds '= in this license in the documentation and/or other materials''''! provided with the distribution.@- Neither the name of the copyright holders nor the names of itss:F contributors may be used to endorse or promote products derived from : this software without specific prior written permission.CTHIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS' NA"AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOTELIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR DA PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT = FalEOWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,tria@SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOTe ELIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,, ifEDATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANYnot CTHEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORTubroutE(INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE''''DOF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.) As DI'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''RoutinesReI'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''@Inversion of a symmetric positive definite matrix which is given_ by Cholesky decomposition.Input parameters:D A - Cholesky decomposition of the matrix to be inverted:True # A=U*U or A = L*L'.ME~< Output of CholeskyDecomposition subroutine.1 Array with elements [1..N, 1..N].!TZFxME(6 << <+`@ tt)xx$ `iT OLSi of `< and `H(bri`J`elow`of a` ) I`"opyr`$ I+&` )x.phpINDO iT i `< `H`J`L`(` `"`*```, .x` 0`$  +2`P )x piTi `< +4`(  )x`h HiTi `< %hH$*\Rffff*1;488d9c33T   2p) R@ H J 00 VH* "` @ ( * H) L( T) P PH   J    :( h Hp L   F  Fh  J L DX (  F L @h  J H LH J L F8 L L J Lp J P ` Pp F  ( 0 JH * B 8 (H &p L 8 D h p R .   ,( JX *  N 0 H PPB   & (  0 $8 `hp   4   > X ` x (     ( 0 @ X (`  "( 8"H p"       4( `h  >    (  H X p     *  " H`p( " 0"H p    B (0 8  @ "H px   2  8@H P X (p   .   ( (8`p(   " 0 @  P X x  ( (@(X( (   "@ h x    2   (0 8 (@ h    .  ("8`p$  "  (  8  @  P X x       (  !(!@!(X!!!(! !(! " " ""@" h" x" "" "" P" 8" 0# P8# P# # # $ F$ 2`$ ($ &$ P$ D8% % % *% 8% P& PX& L& & ' 4' LH' &' ' P'B(0( 8(@(X( p(x(( (( B() ))() @)H)P) X)`) * Barreto\ 13 Oct 2007V on 13 Oct 2007UpL Howland N - size of matrix A. IsUpper storage format.E If IsUpper = True, then matrix A is given as A = U'*U1 (matrix contains upper triangle).= Similarly, if IsUpper = False, then A = L*L'.Output parameters:K A - upper or lower triangle of symmetric matrix A^-1, dependingbels( on the value of IsUpper.Result:% True, if the inversion succeeded. HC False, if matrix A contains zero elements on its main diagonal.%# Matrix A could not be inverted.GThe algorithm is the modification of DPOTRI and DLAUU2 subroutines fromlyLAPACK library.elI'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''   ]]]]] ]8]P]h Test the input parameters. Quick return if possible'< T <'z k - Compute the Cholesky factorization A = U'*U. J? T 7 Compute U(J,J) and test for non-positive-definiteness. J? ' ' $?   $ J$x $ J$x ' $ J J$x  ' '< <'z k $Z'  J J+x ! Compute elements J+1:N of row J. J T H J? T J? ' ' $?   $ H$x $ J$x ' $ J H$x  J H+x H? ' J? '" $ " T  J $$x J $+x $k Jd - Compute the Cholesky factorization A = L*L'. J? T 7 Compute L(J,J) and test for non-positive-definiteness. J? ' ' $?   J $$x J $$x ' $ J J$x  ' '< <'z k $Z'  J J+x $ Compute elements J+1:N of column J. J T H J? T J? ' ' $?   H $$x J $$x ' $ H J$x  H J+x H? ' J? '" $ " T  $ J$x $ J+x $k0 Jk <'i  ]@]X]p]]]]]]]]0]H]]'< Test the input parameters.? T.? T0 , Compute inverse of upper triangular matrix. J? T J J$x'< <'&zhk` J? ' ? J J$x J J+x J J$x' ' Compute elements 1:j-1 of j-th column. $?  $ J$x $+. $ H? J? ' $ H   H $$x $ J$x ' $  H J+x H $?   $ J$x $ J+x $ J InvA = InvU * InvU' H? T H H$x', H T ' $ H T  H $$x H $$x ' $  H H+x H? '* L? H? ' $ * T  L $$x H $$x ' $ L H$x ,  L H+x Ld $? H , $ H$x $ H+x $k8 Hd , Compute inverse of lower triangular matrix. J T? J J$x'< <'&zkx? J J$x J J+x J J$x' J T ' Compute elements j+1:n of j-th column. T J '( J? '" $ " T $ J$x $+. $ H J? T' $ " H  H $$x $$. ' $  H J+x H $ " T  $ J$x $ J+x $kx J InvA = InvL' * InvL H? T H H$x', H T ' $ H T  $ H$x $ H$x ' $  H H+x H? '* L? H? ' $ * T  $ L$x $ H$x ' $ , H L$x  H L+x Ld $? H , H $$x H $+x $k8 Hk  <'&iI'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''2Inversion of a symmetric positive definite matrix.IGiven an upper or lower triangle of a symmetric positive definite matrix,Ithe algorithm generates matrix A^-1 and saves the upper or lower triangledepending on the input.Input parameters:@ A - matrix to be inverted (upper or lower triangle).+ Array with elements [1..N].! N - size of matrix A. IsUpper - storage format.I If IsUpper = True, then the upper triangle of matrix A is= given, otherwise the lower triangle is given.Output parameters:$ A - inverse of matrix A.1 Array with elements [1..N, 1..N].I If IsUpper = True, then the upper triangle of matrix A^-1I is used, and the elements below the main diagonal are notF used nor changed. The same applies if IsUpper = False.Result:- True, if the matrix is positive definite.F False, if the matrix is not positive definite (and it could not be inverted by this algorithm).I'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''  ]'< x T $ x T $&'<k8k0 <'2i F If you send it IsUpper = True it outputs only the lower half invertedto  ]'< x T $ x T $&'<k8k0 <'4i , Need (X'X)-1 with 100s of Xs and 1000s of NB Note that the InverseCholesky leaves the other triangle unchangedN The documentation belows says this, but I missed it so I am repeating it hereO I leave the Copyright statement in this documentation as a citation and source()ɹAttribute VB_Name = "XPrimeXInverse" ' Barreto(13 Oct 20078Need (X'X)-1 with 100s of Xs andN\Howl*has Cholesky Decomposition @code, uses MINVERSE in OLSReg @add-inNLimitl51oA"Excel3 }59 7 (brilliantD!)"FouSV isualtsi c 6.0C below that takGi 9a`"I ac@cept (omeet)!pyrightndosleave+e Costa@tementuthis docu aa ci2source(downloDadthe8d@e fromhttp://www.alglib.net/maxops/symmec/7cep.phpd|I$ ikrt.b@&fir.toA ANoN@$ēF}@s@o@,r angle unch@edIf you sehzIsUpperTrue Autput8nlyB@lhalf#)T Tws say!isCI misFs6so um repeangher\8'?C((c) 1P992- -UDniityTenne(e. All s)served. n@Tors: * Sergey Bochkanov (ALGLIB project). TranslcN!H FORTRAN  pseu)@u See subroutiO@Jn2forv+_kc]sRexdisZ~ 7 [xbinary ` m4Œ `modificb, are `D@ATvidbUxQ@fol`Di9cor eta% <- +bDA must`.ta!n8abo@z@ notice,Ryl7Za'р!clar# !ducc__g G AqK3blns pnwn+k/4zerialBL18ρ ?Ne`xn a+ ~d`,@:4it# , ` mayAdX e 'm( V md7sof tw!KN specN iwrittenOss`YdTHIS SOFTWARE `PROVIDED BY THE COPYRIGH@T HOLD` ANDNTRI@BUTORS`I"AS`"BANY EXPRESS OR IMPLI tTIES, INCLUD ING, @ NOT`LIMIT1TO, OF MERCHANTABILITYFITN@pA PARTICULAR PURPOy DISCLAIMED.P EVENT SHALL` OWNER CO BE LIABLEaGY D`IRECT1 C L,pSPECIAL,0EARY,TSEQU DAMAGES ( CUREM SUBSTITUTE GOODSERVICES; LO1F USEDATA@0BUSIINTERRU@PTION)WR CADbON`!ORYr, WHEpN!AAS@"CT"d0# NE@^GENC EOqWISE) ARI Ga%WAPUT0?a PJ,` IF ADVPD SId#CH0n_ Rtf'3qhFa v ө]definipӓ which M]g0C1byd@InЊ paZr@MtKyAru -p6 c!P}bWeTbA=U*U1NApL*L'1O g}r&rU\els [1..N, Q]5Nt sizWf@YZx A5ŝstorag0orDb_۠qvXAV U'*U@9(b|s u ) Slarly, if gF`n~p!E`tdC5(^-1, d@ng #val-t  ResulQ Sq0suPeUPenQs zero V x mdiagonal%UM Auldqyb0Q ouhm 1aDPO PRDL0AUU2jLAPACK`brp?/@CPub Fˢ<4(ByRef A(PWs Do0"_ 2ByVal @`s Lo0ng, &As0anBoDim g IQ  Dim J As Long  AJDouhbleXv PJ$M1JP(i_7P'} Test the input parameters. , '9Quick return if pos sidResult = Tru!lIf N0#` Then,(CholeskyDecom&ition!Exit Func End If/IsUpper/O4M C6u teA fac@torizaAAaBU'*U !FDor= 1homStep 16 U(J,J) a3teBfnon-CCve-definiteneshABk=%-%Kv@30#011v + A(i_, @+*Next i_+ A= J- voA<= 0Fals C0Sqr(@) 7oAD8JJK@elements J+1:N of row JO&J < Oi%a;I%J6yT7G%1He?I%+JI) 5J%2KA0L LAbI2$/ ;ᬡj+jafTu!ai_)@3vB`bIw NE c F' n L*L'aVgL`6 W!; 4V65I<"Of& Oa3RtOOsSO% O!7` O , wOcolumnO O69Oy#O$?C?$tI,rO@G%`uPs(O2O  O"ObDOi=@^OO?${c"?1;1 %P0ic Invаe(BydRe:()ù, YByValx3Tl  BoyPan Rw3?gI !K_NM *P[ɡNIm@pCo AI T gDoq S4G'ΟΑReT(1$SD\GA[(Oni3%utriangular max/wQ6Jpd j@0 ef_A2Rd\/0 d :&ɑW 1# @HCe Sh-xf1:j-1!j-th fhR;FN ^6TёNb Next i_  For I = 1# @To J -$S`tep 1 hvP0#.i_8bIM1 Ev + A(I,) * i_, E?K? @A(@IgE@w+ EBKKYElse]5$sC_@rmKvEnd If$qgcCompute inverse of lower triangular max.2J B5^o- @cA(6J 02dResult Fa+ wACholes|ky#$aExit FuncxtioLq &g1x# / B = -DkJ 7_(0B %1elements j+1:n2j-th columNn0& NMC2- J"|J8|rT hGa a I!,bo)ja©85 rs _B`sA7sL'sL 3#@!s Ks /d&(2q*VV/sG s2s?6_<Kf3? R0w@= Qs@ss2R A>D )@U|it's not , t4ryg@q0wo(rd J #1fr|omf@H6 +putgf& of`minstall"Excel doecappHearbeUS. Pleare Nxthag"@ usafo2nma@|Iacros@-topCOcreen. For`oam ple, `Splan,`t!QZrami@as."lG%O@o!I4, #2`paIf("e4cCbO@see i(kaewmJO1 ; cfHunt @U_ W (J ap!Xb(Y 'M 8' !5alr(ead`Zh@%, ~m"c! a=BiCEF^Ak irHso[hS A@Next zJ UIf iBPv  @/&S@Unewaz?Poxdx (Type:=msotButton-1With ó .OnAJ$.Show )mJq.C!0xn!3Q4Q` Tempqm0ooos00o0a~no0Rs`@o0o0PJeX.!! _3 j;-, _3`d Y3a$$r%V6@cQbook_p,inUnhQ' 8+u8c3 fpSq .&On ErrcResu`q=[ٯm D5S﯃?wL䉰>s.ID(:UI!*USX&U oG1Oos@@Ao?0he03Dex[ "as"05?Bq2!8S(>>< (j WxME8> <<<<< <<<<< <$<(<,<0<4< @`|J `  `LJ H`J//`Jzelem`x ` \ix. `  > `|JH `K` comeH@x%0 8 P mulplxt int@d atripp` theX of @H a sc@8 come@( WARN@ d Ma@ @ @ @ @ ( @ @  0"@( @`'8 08 8 xl @\ @L @< @, @ @  @ @  @ H @ x @| (l \ @L 8 `8 artmen\xP hH$*\Rffff*1R488d9fad_  " (0" 8P@" 0  "   *"2 H8 K ; DP$RX 6<jNs( Rx @  ( 68 p>p     " B(Pp R<X @` "  "  8  ( @ H h  D  $    0 L8   General ModulesionA@oH@RobustRegression ...ToolsWorksheet Menu Bar$0%2%2B@4oh* Message to the user when add-in is loaded7You have successfully loaded the OLS Regression add-in. $ $+You may access OLS Regression by executing  : OLS Regression ... OLS Regression Add-In$'oX define variables]]'$ Make sure the Tools menu is visible\Worksheet Menu Bar$0(BCommandBars("Worksheet Menu Bar").Controls("Tools").Visible = Truecheck language ]0 "!$$WThanks to John Talyorlls  "%ametTools' e=it's not English, try to get the word for Tools from the user0dThe language of this installation of Excel does not appear to be EnglishUS. Please enter the word that Excel is using for <W(y ).5?V__SRP_by__SRP_aEDocumentation#Sheet3 MrU $ `$ `$ `$ `$! `$I `$q `nGB#!3 h f@wabash.edu$ 765-361-6317D6bRh5othDepart oEco@nomics  W? Colleg P.O. Box 352Crawfo rdsvi, IN 47933 Added OLSReg func  fix problem wit`h F sل!OctoControls("T"o")   18 Marche4 chang@so that Davidson & McKinnon's RobuwStd ErrorHC2 is usDr (FH) wrogeneral max routines (repe%dca@Fd $inth@set\E )@the+B!s@!eA! cPompuîsHe al2che ck@5B esulA@again1ata @e one dBn@rYC;s (ugh we en iup>ing pa slA@2fer measXureAT5S(a vers@ 4--see below!mo \}A?)V11FormBC@=@L,s qdlAIn addiMD %,@M esti3`2@\coe:f`wi@\aOSE"+$j (HB)!?ponsiR3-id@@hindinclud&niftVy!GRP ~gs>1inB7F,KoinJwork,"FK:}Lg!@@B/lO>"c@5nub&6Oe 4saQtuff@?4in !TPvalue.xDla,-in"wh? exte",ve^.ama4deBTa $ wouldn'tprk i[dbr&A $!Visu!Basic 0from"sheet@#e HB solF`fMess`@ial ass"inceAmakCu4abM` A ASo`$i!^was dm\GlickDn'LS`aBboo>kI@0-dcl(3)A)llaps!d@og box`0[U#eHdxR4YK r(4P{d d C Cdard#= R sqzuQdG0o SeCП*impNql_, uaNSQRecip 00tP 77H: TqArSp>nta]&S@%plEdR%, WPiam H. P4, Saul A. Teu2kkyruT. VetterpgVg@BGn F`Бy pp.@ 89-91"scuss -q-6675ftechniqu^Ayc 2(d S6Vapl D3(SVnD70pl_tTjfaiM{ #  m 1sp0%f ourBHTMa t tr`(199)$+KxMExphHii   0@ p         0 H X x  "  H 2  LX  H D HP V 4 D( Jp   D B  >h * F 4  DX J " 0 H P 0X @ X R  x P    ( B( p   " , < ,P F >  6 *H (x L .  @ H :P H < 4 0P 8 < "   ( 0 68 Zp @ 2 4H . R J 8X  B \ @ X B` $   Documentation Module All rights reserved.* Date of latest modification: 25 July 2006 Authors: Frank Howlandh Humberto Barreto Howland: howlandf@wabash.edu 765-361-6317 Barreto: barretoh@wabash.edumay  765-361-6315by Both:  Department of Economics Wabash College P.O. Box 352 Crawfordsville, IN 47933A 25 July 2006 Added OLSReg function and fixed problem with F stat+ 25 October fixed Controls("Tools") problemF 18 March 2004 changed so that Davidson & McKinnon's Robust Std Error HC2 is used.A Howland (FH) wrote general matrix routines (repeatedly called in= this set of routines) and the robust std error computations.B He also checked the results against Stata for one data set on theO robust std errors (though we ended up using a slightly different measure than- Stata version 4--see below for more on this)= He also modified the RegForm and put in the labels handlers.D In addition he put in computations of OLS estimates of coefficients and SEs.= Barreto (HB) is responsible for the ideas behind the RegForm; including nifty use of Range to get data into the routine,7 the original matrix work, and routines for putting the# Regression line on the Tools menu.? Original versions of this stuff were in his Pvalue.xla add-in,. where extensive matrix computations are made.> This routine wouldn't work if we couldn't bring the data intoD Visual Basic from the worksheet, a problem HB solved in Pvalue.xla. HB also provided essential* assistance in making this into an add-in.* Some validation was done on GlickData.xls: See that workbook for stats on how close this add-in cameR to JMP in estimating a model with ca. 30 indep variables and 6610 obvservations.K Discrepancies on order of 10^(-6) in estimates and proportionally as well.I Also, a tiny comparison was made with Stata's robust std error option on a bivariate problem." This add-in is still in progress:; We eventually want to make this a general-teaching-purpose regression package. Key tasks remaining: (1) Error handling (DONE)& check for singular X'X matrices6 Use Cholesky decomposition to find them% See MatrixRoutines@ check for non-numeric data in the body of the data matrix7 check that y has same dimensions vertically as X0 (2) put in other test statistics (Not yet done)$ DW stat, exact P-value for DW" make RSE, DW stats optionalF (3) collapsing dialog boxes for entering X matrix and Y vector (DONE)( (4) add other standard regression stats R squared (DONE)4 For ideas on how to implement the OLS routines, seeB Numerical Recipes in Fortran 77: The Art of Scientific Computing,5 Second Edition, William H. Press, Saul A. Teukolsky,- William T. Vetterling, and Brian P. Flannery) pp. 89-91 discuss Cholesky decomposition2 pp. 665-675 different techniques for solving OLS.5 They recommend Singular Value Decomposition (SVD) as in principle it can't fail.0 More on the specifics of our Robust Std Error:T Davidson and MacKinnon textbook (1993) p. 553 recommends using a correction factor9 in which one divides the estimate of the standard error+ by (1- ht) where ht is the square root of. the t'th diagonal entry in the "hat matrix".' This hat matrix is sometimes called PK because it projects orthogonally onto the space spanned by the columns ofthC X. In this module, we denote P by XXTransposeXInverseXTranspose,the sh1 which is a name reflecting the definition of P.< Stata uses a much simpler correction namely sqrt(N/(N-K)).d resiU Davidson and MacKinnon (553-54) say that Stata's correction is inferior to dividing use by (1-ht). A< I don't compute the P matrix because it can get too large,ote or with dimensions Nobs x Nobs assPAttribute VB_Name = "Documentation" ' D Module\All rights reserPved.^aof lst m@odific: 25 July 2006ut hors: Frank HowlandHumberto Barreto553 1 a*sf`/s3whigdi!ms"$be;v)Ҥ by (1- ht |h8;*@oA#t'3ฐ2go0)ry"A"bp YWQsr0"Peca ibt!|jecfhorPCPoR s0Xe Pnn{ columnX. @@ ݢ,denaPa(XXT1s1^XInAeR- S a nLrefl 1de0WW>Pӯ @w mus?@ABCDEFGHIJKLMNOPQRSTUVXYZ[\e^_`abcdWfgijmnopqzstuvwxyl{|}~  Data ProblemA@XkP! Check to see where to put output !! put data on current sheet0 check to see if anything is in the ref edit box !NYou haven't selected a range where you wish to put the data. Please try again.  Data ProblemA@(k ? Again we need to check if OutpurRangerefedt is indeed a Range via error trapping ****" compute range where data is to go !$. QYou haven't selected a valid range for where output should go. Please try again.A@ ([k !'4 !'P''   '   ' check for room on the sheet  1Not enough rows to display histogram. Try again.A@k  4Not enough columns to display histogram. Try again.A@k  $t  $t$.0 Check to see if anything already occupies range' H  J @ H J %t$  'k J H  ePutting the regression output where you suggested will cause overwriting of existing data. Try again.A@&(k k Check the X Var Range/ These checks ensure that we have actual ranges !$. ]` !!' H6You haven't selected a valid range. Please try again.A@ (( k !$. 6You haven't selected a valid range. Please try again.A@ (k> Check that we have the same number of rows for both variables ''    H  ( H %!! H+ H %!! H+  H$ ' H  H$ H $' H ' H'k@k8 H If numxvars > 51 Then MsgBox "Unfortunately, this Add-In cannot handle more than 51 independent variables. You've selected " & numxvars & ". Sorry!" GoTo tryagainEnd If Warning if mismatch is true The number of rows in X area 5 does not equal the number of observations in X area  . Please try again.  t @NT`A_B܇xefRxHp2 xME@SPSSS<6X68$08h *p&f3 &(f38 (-  6&Hx3X F (H (P4x $\+     0@H P ` "p<+ >+ H P (X 6     E       (  8  H t+X     6 @ <H ` 0h  $x @+      <  $ @+  P  ` h Dp       j  i0 P  h x          T(#    " ^ H (5@0X h3x , Hl3X 4 ( 0!Ppx &"z& `      0@@   H  0h    2 <  `"; 44 . @H ` x    6  $v0@ H&|P x        n+ @ . D PH (   "  "  :H  :  &8Pfh  (0 @ X h " px $ :    ( ( ^@   (    ! ! "! ! @! P!`! " h! 6p!  ! ! *!! " ("8" @" " H" .P"  " " *"" " ("## # 5/13/04 Version RobustReg.xla FH computer; Revisions 6/5/04 designed to make noncontigous selectionsX v`V The code for this form has been adapted from the DWForm code in the PValue.xla Add-In.* That code was written by Humberto Barreto] ] A The user has inputted the cel Data ProblemA@@k8A go through cells in each area (determine how many there will be) labels need to be found$ '  !! ' !!'  hYou must select the same number of rows for both the X variable(s) and the Y variable. Please try again.  Data ProblemA@k% Check that we have just one Y column  EYou must select only one column for the Y variable. Please try again.  Data ProblemA@k. Pass this information to the MatrixWork macro Check on labels '    ' H  J H$  ' J'(j - 1) * noxrows(i) + 1  H %%t + $$The X variable label in column I you've chosen is a number. Do you really want the variable label to be  $? Potential Label Problem$'  Gjk J H$' $]The Y variable label you've chosen is a number. Do you really want the variable label to be ? Potential Label Problem$'  Gjk  Start reading the dataA Must read in one row at a time across Y variable and X variables* Data is assumed to be in columnar format!' H   Read y data first  '+remember first row is label so must add one5 We are sent to error handling if this isn't a number Now check for blanks H $ + H $$  '  we are going to skip this obs.k' If we've passed, go to the x variables' J  L J$ H J$ L '  '0/ xvalues(countobs, j) = mySelXRange(i + 1, j) Check for empty values  J %%t$  'd  J %%t  +k` L J80  ' H End reading in data   cThere aren't enough observations with non-missing values to obtain parameter estimates. Try again.A@XkP9MatrixWork SelXRange:=mySelXRange, SelYRange:=mySelYRange'***** stopped working here 5/12/04 ****= creating a new routine that already has xvalues and yvaluesJ but stopped to create and test a more general approach to reading in data$$   $ A Close the Regression form Unload Meo(]h] Get the user's X data input4 Hide RegForm so user can see the cells on the sheet B@3 Get the range of cells as a Range object -- that's what the Type:=8 is Input the range of cells that contain the X data either by selecting them with the mouse or typing in the cell range. INCLUDE labels. $ $1You may move this dialog box if it is in the way.l "%.. !!' check to make sure it's a label H  H$$;One of the label cells contains a number. Please try again.  Data ProblemA@k H! Show the user the selected range ! ! ( ! ( Show the Reg Form B@op` Get the user's y values input4 Hide RegForm so user can see the cells on the sheet B@ Input the range of cells that contain the y values either by selecting them with the mouse or typing in the cell range. DO include labels. $ $1You may move this dialog box if it is in the way.l "%..! check to make sure it's a number$$3The first cell should be a lable. Please try again.  Data ProblemA@8k0! Show the user the selected range ! !( !( B@o This is the Cancel button B@ Unload Meox/ Default is put output into new worksheet (ply)  (#WorksheetNametxtbox.Enabled = Falsed ("WorksheetNametxtbox.Enabled = TruekoX( The default is the new worksheet (ply)  (#WorksheetNametxtbox.Enabled = Falsed ("WorksheetNametxtbox.Enabled = TruekoMBarreto modification for 14 Oct 2007 upgrade enabling alternative to MINVERSE#sAttribute VB_Name = "RegForm" Basx0{E8370027-C177-4D25-A804-E7CE652F23F8}{6DF88848-2750-4FF0-B@BD2-5A F3FDE46} d@GlobalSpacoFalse CreatablPredeHclaIdTru BExpose0TemplateDerivCustomizD ' 5/13/04 Ve rsion Rob.xlaFH comp"revis 6/5 design>to make noncontigous select.Bs Op klicit A' The2de for thism has been adaptfromae DW~ inPValue- Add-I3PThatC wwrittby @Humber,B@arretoB''A#Pub& mySelXRang e As  } Y Abd Sub OKBu@tton_Ck ()@:userYB7in@VA6tA@ell rAs v iaB5D dialog boxHiJ KmL1ains d@ ente DHA unliiUnload M .0Dim upperleft>reg((colAIge owsizT occupanB! low(@3Long Dnumxro wsgNumYRavarb QColXl@abel() S YVarB nob xvWscDo@Pe 'bactual  go h :6yoI a , JE#myche ckmismatchBoolean 'indicat@r GB fbetwrn`o of AKXll`|0nox: ' kee`p trahCmany xrisUea@d a7c a v lcountAE@`"e@> A)idervadabadon#VwhiX s{ve pro mD"@twoO.`C2 ?ri 'q dexBh#c@.searching`rouxgh A")`8(KA 'C@B ;s If XRefedtv.A"a0M sgBoxmpt:="Youn'taÙ c . PKt ry ag .", s:=vbEx`Q, TPitle DLP3xGoTo$y2 End If Y 4 {j  o e w > 0j0b out` [PutOROnCuPlntSheetbtn&3!D' a on c sAsee if> 9xp%ecref editSh @"p"T/#-/# %#yP%wi|shUO$O$O$O$e?q1 1$!$' A w`ep! r *da'|erBrrappA6 F*Аn EResuPNex 'I3FgЊA Setz=(;1  <> 0 65536X41"Not\.disнy to`mT. 1+ +@0E25/\)o__p_V **Cms( ,a),  R=)³kQYJal!dy oies32Q= 4 I#1 d!J G1 IsEoy*( . I0))?+ >1AA J3IpF=Q;VbtGA*a esܔefWsugg$es?wica overaexisqI. !!!%Xo 0 Then MsgBox "Youn't selected a valid.  Plwry again."D; ]" 0Go@To try End If {Y {Y {] 51]' C the saunumber of r ows f{bothSriabl xvar s0@pismatch@Fals e ReA{noxcols(1 Py FI CjEI)#(I)@lumnsBo B R@9J um6F9+ #@uI > @)HF - 1)A mNTruN@badone;ICtwoE IA 'Barreto modificationx14 Oct 2007 upgrade =ing altern@MINVERSE %> 5M!'sUn`HtBuely,Nidd-In c annotwnd,len indepentGOx}'|" & 4& ". Sorry!x! lx'E#"' Warnif 1@t1,4l8AIgin X a d9@ does Jt eqAo@Dobserv"3, Buttons:=vbExclar;, Title:="Data ProxmȜT' g hrough cellea|(deBmiThow m(any;e`8will ba' labe ne``GbeMundCAs XdddXC<)Str@6 NumYx"c.gzfC @酄Y@wf 3xmusA'remema"Q3C%s@add[W{5t :ehA!th isq֥7qNã/lankM%Q S*,IIqR-Empty(_--=`ly 'Њb o skip 0.C!bfinisheЉ!%%' fpGpPfed,e x{41sK= ]K`JG_FI^'s(J^K{sz`aOn Error GoToHand@ler ' xvalues(countobs, j) = mySelXRange(i + 1. ' Ch eck fempty |@If IsE<(G.Areas(J).Cellqno)mTrue Then GN - 1(finished Else|ri~Z 0nd If Next KJQ:y&{Resu8me z& 5New((),E cEq, YVarlabel,X XvDan 7)" Close@e Regre@7on{m e0H'Unload FM h2SubP$ri?e  Comm!Button2_Click( On TR Dim I As Intega!O ' Get"user's XbW`XputHidFor m so  can seccaB the @ew. ra of  =a J Yject --at'sEwB Type:p=8 if'`Rt App#af.Inl( _ǣ_A@:=""Pconbj!ep`kbylIm#smo# typL qINCLUDE Fs." &r(13)*"You may`YvA,is di@alog bipf it `4"way.",E$`C=J.Colum|ns"r`imTak7ugi/ 2a 9I= 1 `ܥ IsNric(I Ĝ/One@#"!DA.s  berp. Pl@ ǘ, `s:=vbE0xcla``, Titl@D"D` Problem?%F'E! ShowB)B[se<3`"7 Yf.VgaMddA>e.Visi x1f 2 A@@3@@>>%$_>_>!(e4[> u>9lsb9ox(9A;B9f 9fg9r 99DOcluN9999b 99O7sH7ˣ/5e(15R5Єrst2Yould bPr=leo5Co51To5o5i0*-55! 54A5T5pV:3tanPYbtn3'@PV ?C~ v=.zz  iPutOutA6OnCurt`Sheetzq' Defaul2] ai2bu (ply]QmoeF3 wrefedt.Ena@d'᠂NxtgFa6ۿH/t-R%PkePly dGb9ѧ1 q r oxutrkx : <( h  @  F ( .@ "p    D 8 6@ x 8 . > ( 8@ x 2 : : H X B`  * L B8  $     8 @ `  "  * ( 0 @ P ` "h  T]lxME(' r`@LINE`B D idual = `F Y).`H tha Jlumnxcel worp`Lks o`Nbutt n in% ayed%`dial`ata `6/ `` bec`ow w`crip P# Hand Fran deto ode for  RHnce Howland.0`Tregrh `Ves. X: J E Z(361-AX: (7650 \ p63151 Toh`^``ts,   dX80@` f"& | x   Vp 8 X x `l  r N F  tp  X x `v`x`z7``` 0  Xh   <  0  H 8  x   8  `H`x`  -  h''Ix  IT @I| `I,  `H `J `L `~ n%  hH<   ( ,8 *h 2    ( .0 ` h p x             @ ,`     " * ,@ p (x ,     &0 <X  2 4  0 @ P p *      (@HPhx   80 &Hp  "   0 @ HP *h    8 P` &p  , N :( Fh "   J H  X h x(@ P`h  v ( 0 H $` @   @ P` <x   8 @ P ` h &( @ ` p   ( (0P X` & <0p(  8H`h *p    ( H `hp x  &  F 0@ ,Hx  . $("8 ` Zx "    0  8H `x         L ` p 2 . . .(! *X! ! "! ! ! ,! " " $H"0p" "" "("$" #"8#(`# ###### # #$ $ 8$H$`$ h$ 0$  F$ $ @% 0P% %% 0% 4%& & 8& P&`&x& *& &&& &' ' 0'&@' h'p' ' '  ''''('( ( (8(H(0P(( ( ((((( ())> )`) x) ) ) *))) *(*8*@*`*  0h*** *(**+ +  L(+ x+ Z+ @+ 2 , 4X, ., R, J- 8h- - B- \- P. h. Bp. $. . ../ /6(/`/x////:/ 000080@06H00"00*01"01X1p11 11 11 12 2 2(2@2X2p22&2223383P3p33033333484P4 h4 444 44 45550585"X5&5"5 5 555566 06 HH666 6 6 <6 7 *074`7 77 "7 7  88p888 8  8 888 " 8 8 8 (9 89 ,X9 >9 99999 .9 :8:P:h: x: (: :: :: : : ; ; ;(;H; P; `; p;x; ;xRegression Module To Do:&Need to deal with labels for variables#Also add more regression statisticsram+ Output range as well as new worksheet ply.ox Variants of this:4 (1)A version that doesn't put down all the matricesMic6 (2) A version that's built on the residuals and skipsff Linest. DOCUMENTATION: This code is modified from Humberto Barreto's PValue.xla? Original purpose of code is to produce robust standard errors, but can also( be used for general regression results, e.g. can add DW Stat option User Input> It is assumed that the user wishes to a run a regression of y1 on x1, x2, . . . xK (called NumXVar in the code)/ with N observations (called NObs in the code).1This code will run the regression and produce the' residuals (by using LINEST to get the8 predicted values and then calculating residual = actualt Y - Predicted Y).1 It is assumed that the data are in columns on an Excel worksheet., The user clicks on the show Reg Form button) set 4 and an informational message is displayed, followed3 by a dialog box where the X data and residuals are 6/ entered.<This code may become an Add-In, for now we'll use a button.escrip AUTHORtr# Humberto Barreto and Frank Howland F Barreto wrote original code for PValue.xla with some assistance from ; Howland. Howland modified code for regression and robustxpecte standard error purposes. Last modified: July 19, 2000the E -mail: howlandf@ wabash.edu  phone: (765) 361-6317 FAX: (765) 361-6277 barretoh@ wabash.eduDi phone: (765) 361-63151 To FAX: (765) 361-6277Di]]]ghts, # Variables related to naming sheetsai]]8Px ]h] "! 'tege " "( ng = "($on.Cal& Data comes from the RegFormulatio]]]]]]]0]H]`]x]]]]]](]@]X]]' stuff for placing output on same sheet D]]]]]0]H]`]x]]]]Dim Y() As DoubleDim ytemp() As Doubleong ReDim ytemp(1 To nrows, 1)]P Y, X and X' loading coefeDim xtemp() As DoubleDoub%ReDim xtemp(1 To nrows, 1 To Numxvar)Stri ymean = 0nobs = 0ypFor intI = 2 To nrowslaci missing = 0t  For intJ = 1 To Numxvarnge As$ Test = SelXRange(intI, intJ)eger & If Test = "." Then missing = 1er ! Test = SelYRange(intI, 1)& If Test = "." Then missing = 1oubl Next intJDim  If missing = 0 Thens  nobs = nobs + 1  For intJ = 1 To Numxvar A5 xtemp(nobs, intJ) = SelXRange(intI, intJ)e ' Next intJ+ ytemp(nobs, 1) = SelYRange(intI, 1)2 To n. ymean = ymean + ytemp(nobs, 1) '  ElseSe End If Next intIymean = ymean / nobs 1 ' ReDim Y(1 To nobs, 1 To 1)$ReDim X(1 To nobs, 1 To Numxvar + 1) 1 '  R If]] X nobs ] ZH To Nu]P \nobs, ]] !b!  '` d '`kp  `d  ` P bs, 1) !b!End If  'B'y1 Nk tells us the number of parameters to estimate . To Num Y(intI, 1) = ytemp(intI, 1)s) 0  0uble . 0+dobs)  0 .+P ReDid0 . 0 $  . 0+dDouble . 0$d 0 .+Pk 0 .d 'B . # Y(intI, 1) = ytemp(intI, 1) 0  . 0$  . 0+d . 0$d 0 .+P? 0 .k'T .  T .$  'T . which T 'Ter vector of coefficient estimatesnd Needed:an% (1) Run the regression [done]H (2) Obtain the residuals; call them e [handle multivariate case]3 (3) Obtain squared residuals; call them esq.? (4) Check on what to do if there is no intercept [to do] (1) a) Get the Y variables Run the regressionCOLSestimates = Application.WorksheetFunction.LinEst(Y(), x(), 0, 1) 2) X'X work Get X'X]( ` `d] ` `f d B B  P dA@: f B B  P dA@:] B|8 H B H H+| H', f B | ,Ah (X'X)-1 work ,kThere is perfect or near-perfect multicollinearity in the independent variables. Thus the regression fails.A@j k (X'X)-1 work !J! Cholesky and matrix inversion9 Functions change the matrix passed, so set up the matrix]@  B BV H B J B H J$d H J+V J H$V BA@45routine has inverted values in lower half of diagonalmust adjust upper half H B J B H J J H$V H J+Vk J Hduse MINVERSE, limited Xs] d "!n%p'lkPPass Chol other marix if needed !J! B Bl H B J B H J$V H J+l J Hkx Get estimates]  B r8 B H $l H$P H+r Hd r B  B l PA@:k]@  Bt t B  r A@: Get residuals]   'v'x'z H 'F x H$  T  'x6 Need to enter a loop here to sum up the fitted values L B F L$t H L$d 'F L H$  F H+R H$R H+X v H$X 'v z H$  'z H !b! z'xk$Time to choose where to put the data !! !!$. !' !'P''   '   '  $t  $t$. $(| ~ 9 9qdx] If publicnumvars = 1 Then Regression'*# & Varlabel(1)Else@sheetstring = "HistogramFor" & Varlabel(1) & "And" & Varlabel(2)End If'& check to see if any sheets named this]  ' Need to know how many sheets there are r!' H  r ! $ Regression  ' !' !  $ + MsgBox suffixT Need to count how many there are, identify which ones have numbers at end, and then find out what max value isk '  ''    $ X   ''kh  ''d0'k   * '*kE Use application.value function to convert text to a numerical value. !.regsheet.Name = "test2", regsheet.Columns("A:A").ColumnWidth = 15' regsheet.Columns(2).ColumnWidth = 9' regsheet.Columns(3).ColumnWidth = 9( regsheet.Columns(4).ColumnWidth = 15$regsheet.Columns(5).ColumnWidth = 10 * (NewSheet.Name = sheetstringDim histsheet As Worksheet regsheet&Set regsheet = Worksheets(sheetstring) Lay down basic information *** SumStats routine needed %tP( %t$. B@kH !! Observation B  ,tResidual B  ,t H  H H B  ,t H$R H B  ,t Hk` v'V v  B $Z'v]P  B B$l$Z v+d  l BA@N  BA@V  v BA@\k Get S0 matrix* It has the same dimensions as XTransposeX@ First , following Davidson and MacKinnnon, p. 553, divide each esq by (1-ht)9 to get ht, multiply the intI'th row of X into X'XInverse* and the transpose of the intI'th row of X]  B ) robust SE using Davidson and MacKinnon's- various approaches (p. 553) implemented here !! .  .$X .+\ . !!e .  .$X   B  .+\ .d( .   B . $d +  B$$l$'Hd H B  lA@`kp H .+Z !! .$Z .+\d .$X .$Z  .+\kd .$Z .+\d .$X .$Z  .+\kxkp .kX]  ` `X 0 `  `'@ .  . 0$d . $d .$\ @ 0 + 0 $'@ .  0$ Form the NOBS * X'X -1 S0 matrix.]`  ` ` B$l$+d  B B B l A@:k) Form the RSE = X'X -1 S0 X'X - 1 matrix.]  ` ` B$$l+d  B B B  lA@:kE Now get the robust standard errors, square roots of diagonal entriesT Davidson and MacKinnon textbook (1993) p. 553 recommends using a correction factor9 in which one divides the estimate of the standard error+ by (1- ht) where ht is the square root of. the t'th diagonal entry in the "hat matrix".' This hat matrix is sometimes called PK because it projects orthogonally onto the space spanned by the columns ofC X. In this module, we denote P by XXTransposeXInverseXTranspose,1 which is a name reflecting the definition of P.< Stata uses a much simpler correction namely sqrt(N/(N-K)).U Davidson and MacKinnon (553-54) say that Stata's correction is inferior to dividing by (1-ht).< I don't compute the P matrix because it can get too large, with dimensions Nobs x Nobs] 8  V x ' B x V  B  V  B '  ,tdn/a ,tkX !b!#R2 Inappropriate with no intercept. ,t %t%!9qkRegression Statistics for  ,t %t!( No. of obs. ,t  ,tNo. of missing obs. ,t    ,t No. of obs. ,t  ,tF stat. ,t !!HC0'N !!eHC1'N !!eHC2'Nd HC3'Nk  %(| %(| %(| %(| %t(Mean of Dep Var ,t T ,tRMSE ,t v ,tSSR ,t V ,tTSS ,t x ,tR2 ,t %t%!9q  ,tVariable ,tEstimate ,tSE ,t  ` Intercept ,tdp $ ,tkH $t ,t$ ,t B k H B H'^  B G H '^j ^$ H  ,t H$t H  ,t H$ H  ,t H ]P ']h  B . ` . .$ Robust SE ( . ) set to zero B   ,t . .+  'k@ . .$$Z .+ 6/ (1 - Sqr(XXTransposeXInverseXTranspose(intI, intI))) .$Put down a description of the matrix Robust SE ( N) %t( Put down RSE . B .$ .  ,t .&eAn unexpected problem occurred. The next message box will describe it and then the routine will end.A@ !A@h "(  "(  "($j0o( Dim n As IntegerDim np As Integer!Dim a(1 To np, 1 To np) As DoubleDim p(1 To n) As Double%Calling: choldc weights, nvar, np, r8 This routine is designed to test for multicollinearity]]]]](Set mysheet = Worksheets("CholeskyTest") H T J T H J$x'~ H 'L L ~ H L$x J L$x '~ L 'L8 H J ~Hz> ',k ~$Z H+|d ~ H$| J H+xk J HoxAttribute VB_Name = "Regression" Opt Explicit  'x Modu le& To Do:Need to deal with labels for variablesPAlso add more rv stat istic% Output range as wellnew workshee@t ply.S V`nts of this (1)A verat does n'6,down a*the m acO (2) 's bui`lt on#Zsiduanand skipY @LinestJ  ' DOCUMENTATIONV TP co de isdififrom @HumberB@arreto=PValue.xlaOriginpurposNez&prcARobustndard err0ors,  ca?soe us jgenerGHfulte.g.pDW rS@m o‹@ User Inp It9assumthb\@Br@shesa ru@$Isy]x1, x2, .BxK (cCnNumX@ 6ifA) N ob"vons Obs @k' ewi#*HB{WG(byQing LINEST5g@D predict-vAts AnSlcul@)act@Y -d PY),Ud`ata aրDc0olum@>@PanExcehT`1c}k`hC) show ~ Form`ttoB`Ginf"+Em`a uZdisplayed,`llowed!#a dialog box whe` X C`i)entd4#gmay becoAdd-Inr nwe'8 a$  AUTHOR!-m1Fk HXowl p wrooEom v ss"nϠpz . '{ ,Mr"x x s' La|mo: July 19, 2000CE -m8ail:f@ wabash.eduA phone: (765) 361-631"7FAX27+ bCh? 3156 !aSub MxWSNew(xds(s`̢u, Yn{Long, @@rIFgero`ѡSXт numxprowsA! Dim yoursHett{AsE a6@|ECI, KGintJ# @`nam ash DyBoolea?!a=$!csuffixijay= Ap>.q8= 2F SmiPwngGAJ T.SelX"(!<:,!JQ!1If" "."en 1/ Y)-q' NexntJpXtb+bmrS q%s1)C9s+ [ ElsqElIfp%IK/%bRe(Y('@p-#APX;"  q- 6ss2>%2!esq6ahtm P 8( D_Dim ivar As Long ActualNumXVarslInteger If Re@gForm.LrceptCheckBox.Value = Tr Then ) = x+ 1 Else'#nd If ReX(1 To nobs,  a)(Transpose1 &;'#/m [Nky i' tells us the number of parameteto estimat intI'p 'Y( Y)ytempBJ J߀'iIX ojXGWA ,?. xI ls - 1e D EDNext+J It+ u g9hA]GF WAgnymean J0 S |+ !@)-+/Bx\vectoccoefficihentcs3`je`eded:b(@1) Runlregression [done]2) ObtDaiEsids@; callm ehandl@e multai+r sJ 3F squared  sq.4) what @ do ifreX is@!ARe [@#a) LGeY iable"#/a O!QOLSes8Applicat$.Workshe@etFunc"LinEst(Y(), x0a 2NaX'X w`aa'bX()!Dou#]ĸH@N?A# 8 D2 MatM> X,c#G, X p!pCԜNk$-amNk ap(IixdinvertiG, =a C\c@holdct@(XTp ' (P)-1SRb(Faz w}MdsgP "3,p(erf< 6-. dsX=fails."sGo`]NowJ/ }LargeX ':olesky A zrix 'F3' u3s 2cCge" passed, so s`;up3,XIseC/"33Nk2$Q4 $%'%$/9I, JQ'(2 ()uT)dVia BOc6+!'rout0# hajstWvQ low@halfdiagonal# 'must adjaup(Pl05Q0a0I > J(07C=J,| I$? /'use MINVERSE, limiXb7a&eqYiantC?cY$.- Hesq(^ 2;msee6r+ )ysq+ U6zI If RDeggm.IceptCheck`Box.VyFal0Then*tss* End If 'Timchoowput0data9PutOutOnCurrSheetbtn=True =>Set̀perleftCRange(b'e rdt$c$olup.Colum5rowsizX\80CD5low@=|l A~lACE@@G3regr1 3Cells( ,), BBMB)D6C2s(&)A5Width@1')WiActiveWindowC).ScrollRD@!NCA}Dim s c As ge r '@publicnumvar/@ֆsstring"{sion" '& Varlabel(1C'EA'Histoe\m" "And2 ]a = B' cafee if any  nam1thi Leng0s Long,t`lb#@k NeAo knu@2h`m a$re)tog = Ejs@BuntRe0uffix("($" Each``55L o`(sh.N 0aJ2G&%%+ 1C$ = e bsca Rig htD- 10!('Msg s"&co` &%, id@ify whiones have Qbers atôd,9da@'1@outmaxC ;shE`stayX㡚cnew#i$pfail@LWhile @ a5ibM9scc7a@6c (a*CStr()D%g 9EH 0<H& #' UpappMatK. fun Xon1CconverPra'@erical%.rbWorkc.Ad$ 'd29@"t2"0b"A:A"Gcqrs(29˟3 '.4j 5?FIPc'N|ewPuAQ0ZtϒAX"' E<~'JPjJ' L?vn basic infor!$ *** SumStats r FinH}_e31, .S80,8 40Ղ&batc5+ jRui_n 7Nka(Observ0((2In"E!NI`MnobtobI0aRJ1ssr A #uqr(/ r(a -A$S@Evec()$D>o@w Nk!GNk 64S@XTsppXIBse E1Fdiag , ,#atVecMDulabySHar% ' "GS0 ,riBxP' It0ts%"s dpns;p.ts First , f owDavQavMacKinnnon, p. 553, @8e e02@ by (1-ht  'aUg ht, Bm iply" i0ntI'P of X8WX'uMQP tE'2xtm GPGCobupS0E usOCon '-'P_riouappro0p() implem 1@$5HC0ڴ"C'o +5q$ !((|0#w*1///4/*6=7 7nobs For intK = 1 To Nk xxt(J)X$I,b)a PNext, *Ifhen 4Y`tempha1) * XTransposeXInverse(C =ElseNV ecMatult \,, x t,[ (nd Ijf h{I{%  [nRegm.HC2btn.ValuehTrTy2 ,G0esq0M@ ) / (1 - pA?1 1 2 ^ 2d3 8IWEDim S0() As@ DoublReDBActualNumXVars, H&JPx  ARstorAI#1}Jd|=h~JtiviM+ bO + o,2A[aK'EJs' m the NOBS'X -18 matrix.7t=r?CB?fNk/J~s H4@1AhM cNk/,$ Dž`*RSEB*X!^n+sqg''B %%A?q)( =&S0 S' Now geTrobusteandard errosqua`xroots of diagonal e nWe`' ' Davidson  MacPKinntbook993) p. 553 recommends using@ a corPtbi@facCbin which one0es5estiPe1 6st byPe)0e@ hX At'thy "hat$;"0;This W some`  called Pecause it projecrtho$ly toRspace Pnn Q columnX. Inmodule, wenote P PX4,RS a namefl 1defini@PbStataC0 a musimplerP sqrt(N/(N-K))_"T"(0!-54) 8say r'sY inferidi b 0`(1-htbI@ don'tm,pupA P bcan30oo largei  *s)s2bs xb"![r$x, f(t zu1@- (ssrtssXSX>QzVX3= ((0aZ- 1)3(#qsNk)T]reg0ge.Cel@ls(5, p=qc EvEr"n/a"pW InterceptCheckBoxƥFaP ۥ4, 5c"R2 +app0priaewno.7u5W5)`.CharB`("rt:=2, Length:=1).FontT!.Supcript@cVA_Wm`"rTesQ! A*ipHcs f@)" & Y labelOd .BoldZ = "No.1N`҅2 3hmi 0W/E##umxrxows&& s ( * b0Fqhtb(HC0/mytypehcq$1/j1oof1oorֽ2h!Q&;31gyz3`(4P/W|idJD&C8=)s(2=h(3r6!G.WrapT91= 6PAĵ1) = "Mean of Dep Var"  regrange.Cells (4, 2ym#5, RMSEDrmsBe2, 3S,SRB!4!ssrA3ATSSA!AtssAtAR2 With 3).Characters(S@tart:=OLength:=1).Font2.SupcriDptTruE8nd 2GWrsquar6iable}"EstimatDaqIf Numxvar + 1-ActualX@s TheJn.7%In@Hcept@% El  Xlabe0l(1) FIf3coefesrt4(1 5SEvecB6k310GoTo s@kipnowFor I 2 @NkiD= ICCI< NkB a - 1 6@TIDIG= >A / =>IDNe$xt/ : Dim marker As 6lgelD=RSE()DouMReE@*o d !a(int(?Ha) sq(, ) <| 04%r*/+D+"Robust SE (" & ") set to zero$f& = 0K#d{EXa` aSqr(n h''/ (F1IaXXT@sposeXInv@e'&)>A)d'Put down a desiothe(0trixcP, 4).Valueb+mytypehc,*'  BjaP_a?'  B{O% MsgBox_Err^a  "An @unexpe@d pro@[m occurred. P t message bo@x willD(be it a@`(n )routineend.d"d.D/ AApplicat@2.DisplayAlerts iCalcul your`[gScr@eenUpd ngf *Now`*Sub `@ choldst(A, N, fp8@ti%'?nB#p?a,Tn0"@npDrp4 ling:  weights, nPXP J'is %-ignedQ>t0b f\mul ticol0earityOIj  JKsum1PamysheCETWork 'S = s("CesktyTP "1RN,1J@4+= A(`J!51KIg!While K >L #Q-K) *aJ, K(eKl-# [Q JZ`@\.0 vA1iL% Fav"numxvars޿NumYCols@ Xvarlabel} YVarlabelrnobs<xvaluesbyvalues0&mycheckvmismatch@noxcolsZnoxrowsˀcountobsbadone badtwo&xvarikcellnoXRefedtValueKpromptmButtons] vbExclamationTitle~tryagainYRefedtgPutOutputOnCurrentSheetbtn,OutputRangerefedtErroColumniIsEmpty myareasmAreasKCount0vColumnsp9RowsU IsNumeric$*vbYesNovbNo% ErrorHandlerOfinished8 MatrixWorkNewZCancelbtn_ClickC PutOutputOnCurrentSheetbtn_ClickEnabled۪MakeNewPlybtn_ClickUserForm_Click RegressionaxvalsjYlNumxvarR yoursettingFregsheetNintK\sh_stay2fail newsuffixiisheet1 CalculationhxlCalculationManualScreenUpdating!  MsgBox_ErrcNewSheet  sheetstring۟ invertibleyintI\intJ\intIIintJJ lastRowUsedOV mystartrowՊ mystartcolumn_myendrow myendcolumn6storeQNkf^coefest}yfitttemphcC Varlabels$8nrowsomytypehc XTransposekHresids 1ymeanssrresqht]esqhtivarActualNumXVarsOInterceptCheckBox<\ XTransposeX'XTXZ choldctest&EndNowXTransposeXInverse:WorksheetFunction&MInverseXTransposeXInverseXI coefestmat%rmserJtssysq# ColumnWidth ActiveWindow+ ScrollRow" ScrollColumnLHsc_LengthY totalsheetsZsuffix_MRight AddrActivate|ResidualsCheckBox݄SEvecxt_HC0btnHC1btnHC2btn-S0_XTransposeXInverseS0fRSEsqrsquare_fstatV; CharactersrgStartFontU SuperscriptBoldWrapText}skipnow>markerSRSE Over' Description  DisplayAlertsGeneralj7 auto_openV deleteRegressionresponseChrK~ vbInformationnRegressionIsHeredVisible InternationalxlNonEnglishFunctionsCaptionxnewItemImsoControlButtonnOnActionTempR ShowRegFormShow Documentation(Sheet3 OLSFunctionmOLSRegxRangeWyRangeIncludeIntercept RobustChoiceYTResults resultdimMatrixWorkNewFyWorkbookk AddinInstallay  *\G{000204EF-0000-0000-C000-000000000046}#4.0#9#C:\PROGRA~1\COMMON~1\MICROS~1\VBA\VBA6\VBE6.DLL#Visual Basic For Applications*\G{00020813-0000-0000-C000-000000000046}#1.5#0#C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE#Microsoft Excel 11.0 Object Library*\G{00020430-0000-0000-C000-000000000046}#2.0#0#C:\WINDOWS\system32\stdole2.tlb#OLE Automation&*\G{2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}#2.3#0#C:\Program Files\Common Files\Microsoft Shared\office11\mso.dll#Microsoft Office 9.0 Object Library*\G{0D452EE1-E08F-101A-852E-02608C4D0BB4}#2.0#0#C:\WINDOWS\system32\FM20.DLL#Microsoft Forms 2.0 Object Library*\G{AA9352D3-EAFB-4BF5-8201-FCBFCE5BFA7C}#2.0#0#C:\DOCUME~1\user\LOCALS~1\Temp\VBE\MSForms.exd#Microsoft Forms 2.0 Object Library.E .`M   rAdd in which performs OLS Regression ModifiedApr-13-2001hH  ThisWorkbook1Q488d9fadThisWorkbookZ Sheet10k488d9a1e7 Sheet1-MatrixRoutines1>488d9e2b8MatrixRoutines07OLSFunction1O488d9fadOLSFunctionHR`XPrimeXInverse1;488d9c33XPrimeXInverse`FGeneral1R488d9fadGeneralWxDocumentation0p488d9a1eDocumentationQ Sheet30q488d9a1e Sheet3BMRegForm1S488d9fadRegFormNH@Regression1V488da068Regression`l`0xHD QŌЍd1H-4wsɃԣUK8{1zBM~1m lm.Fa"S q*rHa.T!AloC",/DCPGeFR04]`M2G+,QExcel+VBAWin16~Win32MacVBA6#OLSRegressionAddIn.stdole`OfficeuMSFormsC ThisWorkbook| _EvaluateWorkbook_AddinInstallAddRegressionMenuItemGMessageWorkbook_AddinUninstallmyTools=' Application*OperatingSystem[LanguageSettings LanguageIDmsoLanguageIDUI5msoLanguageIDEnglishUSuInputBox  CommandBars ControlsKDeletexSheet1MatrixRoutinesMatMultResulturNumAddinUninstallItemz _B_var_LeftQ_B_var_newItem͢ _B_var_Chr\;_B_var_myTools*_B_var_response CommandButton2_Click SelXRangeWAddressCommandButton3_ClickFA SelYRangeModule1bXPrimeXInverseMCholeskyDecompositionYIsUpperOfAJJsvmJM1RJP1i_]InverseCholeskyiNMJϹIP1hAIIsTkD[ InverseSymmetricPositiveDefinite1InverseViaCholeskyUserFormNOKButton|Label1,Label4,Label5,Frame1HC3btnQFrame4 MakeNewPlybtnY CancelbtneLargeXCheckBoxClickNumber-_Defaultj _B_var_suffix _B_var_Right9XTransposeXInverseChol"   7 9  @  D0* pHdOLSRegressionAdd`In9. in which performs Z \ ModifiedApr-13-20 01@r|d in Pwh c pe!fomsOLTSRges/o; MU+dfeUACr-13201u=v  hH <stdole> st2olG" Y^*\G{00020430-C 0046}#2.0#0#C:\WINDOWS\system32\22.tlb#OLE AutoXmatEEOfficEOfQicEE2DF8D04C-5BFA-101B-BDER5EAA!42"Program Files\C ommonMicrosoft Shared\o 11\mso.0dll#G& 9.0 Object LibrarFy@%@MSFA>vJFF{3@RD452EE1-E08F/A-8-02608C4 D0BB4RFM20.DLL'BF _&/;"1?Cj qAq00}#0q# 5h0 AAA9352D3-EAFB-4BF5-8201-F CBFCEk7C6DOCUME~1\user\L OCALS@Temp\VBE\Sp.exd ; .E .`M " ]ThisWorkbookG`T`^i`pW6kbok*2` H1o ",Z""+qShePet1G Seՠ_t}2 d- bTatrixRout@inesG! Mar`xR u#[&27!! FunJcGCF4u@cM2`HR%XPrimeXInvers"e%XP im 5XIv rHeF%d`FGeneralG4GnBaM2W  `4DocuPment¹GDFc`4"n4Ji`nN2\/Q/+Qqu434343,;M4B4XC}`' _@_NS(pC G=2P?5l9PrU~~~~~~~~~m a   9Add in which performs OLS Regressi__SRP_0r$__SRP_1RegForm'i*WJOP'p0ѱ'pfInterceptCheckBox!-6S` ȏ[qJ eUserForm+m)cK`i F3Dynamic  {lFWJOOKButton"-?@Label1Label4Label5?F`Frame1ҋB `XRefedt}dF:O5ZRefEdit  yRҋB `HC3btnFrame4 Cancelbtn MakeNewPlybtnResidualsCheckBoxLargeXCheckBoxYRefedtPutOutputOnCurrentSheetbtnHC0btnHC2btnHC1btnOutputRangerefedtOKButton_ClickCommandButton2_ClickCommandButton3_ClickCancelbtn_Click  PutOutputOnCurrentSheetbtn_ClickMakeNewPlybtn_Click 48  xYou haven't selected a range for the data. Please try again. Data ProblemCҋB `) You haven't selected a range where you wish to put the data. Please try again.fĤIx8< 4Regression Statistics for Bold No. of obs.* You haven't selected a valid range for where output should go. Please try again. bNot enough rows to display histogram. Try again. hNot enough columns to display histogram. Try again. j does not equal the number of observations in X area 4 Putting the regression output where you suggested will cause overwriting of existing data. Try again.`F lYou haven't selected a valid range. Please try again. :The number of rows in X area &. Please try again. 6 You must select the same number of rows for both the X variable(s) and the Y variable. Please try again.$ You must select only one column for the Y variable. Please try again. >The X variable label in column Superscript & you've chosen is a number. Do you really want the variable label to be  ? .Potential Label Problem0 The Y vaK*yrU ~~~~~~~~~~~~~~~~~~~~~~~~~~~~% =..Fp4    !a q P A7Y7y7777Q3q339a q  q `!Ya q a  9  ay  OLSRegressionAddIn ThisWorkbookSheet1MatrixRoutinesRegForm RegressionGeneral DocumentationSheet3 OLSFunctionF /C:\PROGRA~1\COMMON~1\MICROS~1\VBA\VBA6\VBE6.DLLVBA  90F4C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXEExcel W Tools 1@0FC:\WINDOWS\system32\stdole2.tlbstdole aPL-[DR?C:\Program Files\Common Files\Microsoft Shared\office11\mso.dllOffice Ai`.E .`M C:\WINDOWS\system32\FM20.DLLMSForms aapRK[| .C:\DOCUME~1\user\LOCALS~1\Temp\VBE\MSForms.exdF Aai auto_opendeleteRegressionMessageAddRegressionMenuItemTemp ShowRegFormWorkbook_AddinUninstall FKsCCۗ,#FOs{kNaEDFWorkbookWorkbook_AddinInstall @ $Worksheet Menu BarFF1 Y  F Fp The language of this installation of Excel does not appear to be EnglishUS. Please enter the word that Excel is using for Tools on the main menu across the top of the screen. For example, in Spanish, it is Herramientas. ,Get the word for TOOLS Herramientas FControls CountCaption $OLS Regression ...TypeAdd &General.ShowRegFormOnAction1 VBE6.DLL 1i  nYou have successfully loaded the OLS Regression add-in.بlN8H>$1T`A_B܇xef VYou may access OLS Regression by executing (: OLS Regression ... *OLS Regression Add-In 1`  1S (Module1XPrimeXInverseOLSRegFFMatrixWorkNewFCholeskyDecompositionInverseCholesky  InverseSymmetricPositiveDefiniteInverseViaCholeskyLL>jEvՃAlHmP'OZ/?FRxHp2 BҋB `riable label you've chosen is a number. Do you really want the variable label to be TestMultcholdc TestcholdcCharactersFont3 There aren't enough observations with non-missing values to obtain parameter estimates. Try again. MatrixWorkNew choldctest 1  1.  11 0MatMultFinddiagSqrtVecMultVecbyScalar VecMatVecMult MatVecMult7 There is perfect or near-perfect multicollinearity in the independent variables. Thus the regression fails.ColumnWidth RegressionFF Observation Residual FR2 Inappropriate with no intercept. Start Length &No. of missing obs. F stat. HC0 HC1 HC2 HC3WrapText Mean of Dep Var RMSE SSR TSS R2 Variable Estimate SE Intercept Robust SE ( ) set to zero ) Value 4 An unexpected problem occurred. The next message box will describe it and then the routine will end. 1k h 1f .2 Unfortunately, this function cannot handle more than 51 independent variables. You've selected  . Sorry! No. obs. No. missing obs. Mean Dep Var RSE (%on ModifiedApr-13-2001 1  xRangeyRangeIncludeIntercept RobustChoicexvalsYnobsNumxvar YVarlabel XvarlabelnumxrowsResultsANIsUpper ( Y8 AH X h 9x a mySelXRange mySelYRangep invertibleResultNumINumJNumKMLeftMrightdiagMvecscalaroutcomeXKnpZ$  Z}+-! ¯NCh08KEFҋB `EF l$0OKButtona$(2Label1on0 `InterceptCheckBoxls P 8XRefedtnInput Data Range"Enter the data rangeP 8YRefedtnInput Data RangeqEnter the data range(24Label4tn(20Label5tn$#Frame1tn$#Frame4tn0T ResidualsCheckBoxnge] ( 4 Cancelbtna R,T LargeXCheckBoxge o!i22 n``P'pP'pfo (OKOqTahoma,}Click on the buttons below to input the cell ranges containing the X values and the Y values. Be sure to include the labels.({1TahomaDFa1 Include an Intercept Term 1TahomaH$,1TahomaH$,1Tahoma$(X Variable(s) Rangeg 0 (Y Variable Rangeg 08F a0Report Residuals1Tahoma(CancelOqTahoma8F a0More than 50 Xss1Tahoma@H }5%"Type of Robust SEge$<HC0btn{$<HC1btnq$<HC3btn]$<HC2btng ,Fa0HC00,Fa0HC10,Fa0HC30,Fa1HC20 n``Microsoft Forms 2.0 FrameEmbedded ObjectForms.Frame.19q@H .}5%ELocation of Outpute, TMakeNewPlybtn geCompObjpi276 %# n``P'pP'pfo"$8dPutOutputOnCurrentSheetbtn"08OutputRangerefedteW 8{1New Worksheet ge1TahomaHa0Output Range on Current Sheet1TahomaH$,3 @Tahoma n``Microsoft Forms 2.0 FrameEmbedded ObjectForms.Frame.19qCompObjpCompObj&nVBFrame)PROJECTwm)L i*WJOMicrosoft Forms 2.0 FormEmbedded Object Forms.Form.19qVERSION 5.00 Begin {C62A69F0-16DC-11CE-9E98-00AA00574A4F} RegForm Caption = "OLS Regression Form" ClientHeight = 4815 ClientLeft = 0 ClientTop = 0 ClientWidth = 6375 StartUpPosition = 1 'CenterOwner TypeInfoVer = 90 End ThisWorkbookThisWorkbookSheet1Sheet1GeneralGeneralMatrixRoutinesMatrixRoutinesDocumentationDocumentationSheet3Sheet3OLSFunctionOLSFunctionRegFormRegFormXPrimeXInverseXPrimeXInverseRegressionRegressionID="{A0AC861D-7652-417A-84EA-443C22AD233A}" Document=ThisWorkbook/&H00000000 Document=Sheet1/&H00000000 Module=General Module=MatrixRoutines Module=Documentation Document=Sheet3/&H000000PROJECTPROJECTlkSummaryInformation(+hDocumentSummaryInformation8 00 Module=OLSFunction BaseClass=RegForm Module=XPrimeXInverse Module=Regression HelpFile="" Name="OLSRegressionAddIn" HelpContextID="0" Description="Add in which performs OLS Regression ModifiedApr-13-2001" VersionCompatible32="393222000" CMG="5B599CCD8DD18DD18DD18DD1" DPB="484A8F1090109010" GC="3537F2133201330133FE" [Host Extender Info] &H00000001={3832D640-CF90-11CF-8E43-00A0C911005A};VBE;&H00000000 [Workspace] ThisWorkbook=0, 0, 505, 367, C Sheet1=0, 0, 0, 0, C General=22, 29, 527,     396, C MatrixRoutines=154, 203, 759, 687, C Documentation=110, 145, 615, 512, C Sheet3=0, 0, 0, 0, C OLSFunction=88, 116, 693, 600, C RegForm=132, 174, 737, 658, C, 66, 87, 671, 571, C XPrimeXInverse=132, 174, 737, 658, C Regression=110, 145, 715, 629, C EFOh+'08 PXp  $0OLS RegressionBarreto/HowlandTReports Robust SEs; handles missing values; contains OLSReg function 14 Oct 2007Humberto BarretoMicrosoft Excel@dg)@