VBA Coding for Logit Regression.

T

Toadhall

I need to run a logit regression in Excel but can't afford statistical
packages. I have managed to program (with a lot of help) a standard linear
regression macro but am not sure how to convert it for a logit regression.

Does 1) anyone have any coding for a logit regression? 2)know how to
manipulate underlying data so that it can be used in a standard linear
regression model 3) Know how to change linear coding to work for logit
regressions?

Here is the linear coding if anyone wants to have a crack.

Thanks

Toby

'Multiple Regression
Sub MultOLS()
Dim n As Integer, k As Integer, i As Integer, j As Integer, dw, z
As Long, m As Range, Autocor
k = Sheets("Regression Input").Cells(1, 7) 'number of
variables
n = Sheets("Regression Input").Cells(2, 7) 'number of
observations
ReDim x(n, k) As Double, XTransp(k, n) As Double, y(n, 1) As
Double, e(n, k) As Double, Residuals(n, 1) As Double, f(k, 1) As Double,
Names(1, k)
ReDim XTranspX(k, k) As Double, invXTranspX(k, k) As Double,
XTranspY(k, 1) As Double
ReDim beta(k, 1) As Double, betaTransp(1, k) As Double,
namesTransp(k, 1)
ReDim YTransp(1, n) As Double
ReDim betaVar(k, k) As Double, SEBeta(k) As Double, tStat(k) As
Double, pValue(k) As Double, confLfive(k) As Double, confHfive(k) As Double,
confLzero(k) As Double, confHzero(k) As Double
Dim YTranspY As Double, betaTranspXTranspY As Double
Dim RSS As Double 'Residual Sum of Square
Dim TSS As Double 'total sum of squares
Dim ESS As Double 'residual sum of squares
Dim RMS As Double 'Sq[Standard error of Est Y] - residual
variance
Dim meanY As Double, rSq As Double, sumY As Double, adjRSq As Double
Dim fStat As Double
Dim LaggedResiduals() As Double
Dim ReducedResiduals() As Double
Dim ResidualDifferenceSquared() As Double
Dim SumNumerator As Double
Dim SumDenominator As Double
ReDim LaggedResiduals(1 To n - 1)
ReDim ReducedResiduals(1 To n - 1)
ReDim ResidualDifferenceSquared(1 To n - 1)

Delete_Output

'1. Read in data - assign array and compute mean

For i = 1 To k ' Names
Names(1, i) = Sheets("Regression Input").Cells(3, i + 1)
Next i

sumY = 0
For i = 1 To n
For j = 1 To k - 1 'K-1 independent variables
x(i, j + 1) = Sheets("Regression
Input").Cells(i + 3, j + 1)

Next j
x(i, 1) = 1
y(i, 1) = Sheets("Regression Input").Cells(i + 3, 1) '
Independent

sumY = sumY + y(i, 1)
Next i
meanY = sumY / n
'2. Transpose X and Y arrays - X' and Y'
For i = 1 To k
For j = 1 To n
XTransp(i, j) = x(j, i)
YTransp(1, j) = y(j, 1)
Next j
f(i, 1) = Sheets("Regression Input").Cells(2000 * i, 1)
+ 1
Next i
'3. Compute X'X
For i = 1 To k
For j = 1 To k
XTranspX(i, j) =
Application.Index((Application.MMult(XTransp, x)), i, j) 'X'X
Next j
Next i
'4. Compute INV(X'X) and X'y
For i = 1 To k
For j = 1 To k
invXTranspX(i, j) =
Application.Index((Application.MInverse(XTranspX)), i, j) 'INV(X'X)
XTranspY(i, 1) =
Application.Index((Application.MMult(XTransp, y)), i, 1) 'X'Y
Next j
Next i
'5. Compute beta = INV(X'X)* X'Y
For i = 1 To k
For j = 1 To k
beta(i, 1) =
Application.Index((Application.MMult(invXTranspX, XTranspY)), i, 1)
Sheets("Regression Output").Cells(10 + i, 2) =
beta(i, 1)

Next j
Next i
'6. Compute beta'
For i = 1 To k
betaTransp(1, i) = beta(i, 1)

Next i
'7. Compute RSS = residual sum of squares = sum(sqr(e))=ee' = YY' - beta'X'Y
YTranspY = Application.Index((Application.MMult(YTransp, y)), 1, 1)
betaTranspXTranspY =
Application.Index((Application.MMult(betaTransp, XTranspY)), 1, 1)
RSS = YTranspY - betaTranspXTranspY 'regression sum of
squares - Explained
'8. Compute RMS, TSS, ESS, rSq, AdjRSq, fStat
RMS = RSS / (n - k) 'residual variance
TSS = YTranspY - n * meanY ^ 2 'total sum of squares
ESS = betaTranspXTranspY - n * meanY ^ 2 'residual sum of
squares
rSq = ESS / TSS
adjRSq = 1 - (1 - rSq) * ((n - 1) / (n - k))
fStat = (rSq / (k - 1)) / ((1 - rSq) / (n - k))

Sheets("Regression Output").Cells(3, 2) = (rSq) ^ 0.5
Sheets("Regression Output").Cells(4, 2) = rSq
Sheets("Regression Output").Cells(5, 2) = adjRSq
Sheets("Regression Output").Cells(6, 2) = Sqr(RMS)
Sheets("Regression Output").Cells(7, 2) = fStat
Sheets("Regression Output").Cells(8, 2) = n


Sheets("Regression Output").Cells(3, 5) = k - 1
Sheets("Regression Output").Cells(4, 5) = n - k
Sheets("Regression Output").Cells(5, 5) = n - 1
Sheets("Regression Output").Cells(3, 8) = fStat
Sheets("Regression Output").Cells(4, 6) = RMS * (n - k)
Sheets("Regression Output").Cells(5, 6) = TSS
Sheets("Regression Output").Cells(3, 6) = ESS
Sheets("Regression Output").Cells(3, 7) = ESS / (k - 1)
Sheets("Regression Output").Cells(4, 7) = RMS
Sheets("Regression Output").Cells(3, 9) = Application.FDist(fStat,
k - 1, n - k)

'9. Compute Standard Error, t Stats, P values, Confidence intervals for the
betas
For i = 1 To k 'Covariance matrix for beta
For j = 1 To k
betaVar(i, j) = RMS * invXTranspX(i, j)
'var-cov matrix for beta = RSS*INV(X'X)
Next j

SEBeta(i) = Sqr(Abs(betaVar(i, i)))
tStat(i) = Abs(beta(i, 1)) / SEBeta(i)
pValue(i) = Application.TDist(tStat(i), n - k, 2)
confLfive(i) = beta(i, 1) - SEBeta(i) * Application.TInv(0.05, n -
k)
confHfive(i) = beta(i, 1) + SEBeta(i) * Application.TInv(0.05, n -
k)
confLzero(i) = beta(i, 1) - SEBeta(i) * Application.TInv(0.1, n - k)
confHzero(i) = beta(i, 1) + SEBeta(i) * Application.TInv(0.1, n - k)


Sheets("Regression Output").Cells(10 + i, 3) = SEBeta(i) '
standard errors
Sheets("Regression Output").Cells(10 + i, 4) = tStat(i) ' t Stats
Sheets("Regression Output").Cells(10 + i, 5) = pValue(i) ' p Value
Sheets("Regression Output").Cells(10 + i, 6) = confLfive(i) ' 95%
confid Low
Sheets("Regression Output").Cells(10 + i, 7) = confHfive(i) ' 95%
confid High
Sheets("Regression Output").Cells(10 + i, 8) = confLzero(i) ' 90%
confid Low
Sheets("Regression Output").Cells(10 + i, 9) = confHzero(i) ' 90%
confid High

Next i


'10. Compute e = residuals = Y - betaX, Duban Watson Statistic

For i = 1 To n 'Residuals
For j = 1 To k
e(i, j) = beta(j, 1) * x(i, j)
Residuals(i, 1) = y(i, 1) -
Application.Index((Application.MMult(e, f)), i, 1)
Sheets("Regression Output").Cells(i + 3, 16) =
Residuals(i, 1)
Next j

Next i

For i = 1 To n - 1 'DW Statistic
j = 1
LaggedResiduals(i) = Residuals(i + 1, 1)
ResidualDifferenceSquared(i) = (Residuals(i, 1) -
LaggedResiduals(i)) ^ 2
SumNumerator = SumNumerator + ResidualDifferenceSquared(i)
Next i

For i = 1 To n
SumDenominator = SumDenominator + Residuals(i, 1) ^ 2
Next i

dw = SumNumerator / SumDenominator
Sheets("Regression Output").Cells(7, 5) = dw

'11 Compute autocorrellations of lagged residuals
For j = 1 To 6
ReDim LaggedResiduals(1 To n - j)
ReDim ReducedResiduals(1 To n - j)
For i = 1 To n - j 'Auto Correlation Statistic

LaggedResiduals(i) = Residuals(i + j, 1)
ReducedResiduals(i) = Residuals(i, 1)
Sheets("Regression Output").Cells(i + 3, 27 +
j) = LaggedResiduals(i)
Sheets("Regression Output").Cells(i + 48, 27 +
j) = ReducedResiduals(i)

Next i
Autocor = Application.Correl(ReducedResiduals,
LaggedResiduals)
Sheets("Regression Output").Cells(3 + j, 12) = Autocor
Sheets("Regression Output").Cells(3 + j, 14) = Autocor *
Sqr(n - j)
Next j

'12 Compute t stat, SE of autocorrelations
For i = 1 To 7

Sheets("Regression Output").Cells(2 + i, 11) = i - 1
If i > 1 Then Sheets("Regression Output").Cells(2 + i,
13) = 1 / Sqr(n - i + 1)

For j = 1 To k
namesTransp(j, 1) = Names(1, j)
Sheets("Regression Output").Cells(11 + j, 1) =
namesTransp(j, 1)
Next j
Next i



End Sub
 
T

Tom Ogilvy

http://www.xs4all.nl/~ednieuw/Logit/logit.htm

--
Regards,
Tom Ogilvy

Toadhall said:
I need to run a logit regression in Excel but can't afford statistical
packages. I have managed to program (with a lot of help) a standard linear
regression macro but am not sure how to convert it for a logit regression.

Does 1) anyone have any coding for a logit regression? 2)know how to
manipulate underlying data so that it can be used in a standard linear
regression model 3) Know how to change linear coding to work for logit
regressions?

Here is the linear coding if anyone wants to have a crack.

Thanks

Toby

'Multiple Regression
Sub MultOLS()
Dim n As Integer, k As Integer, i As Integer, j As Integer, dw, z
As Long, m As Range, Autocor
k = Sheets("Regression Input").Cells(1, 7) 'number of
variables
n = Sheets("Regression Input").Cells(2, 7) 'number of
observations
ReDim x(n, k) As Double, XTransp(k, n) As Double, y(n, 1) As
Double, e(n, k) As Double, Residuals(n, 1) As Double, f(k, 1) As Double,
Names(1, k)
ReDim XTranspX(k, k) As Double, invXTranspX(k, k) As Double,
XTranspY(k, 1) As Double
ReDim beta(k, 1) As Double, betaTransp(1, k) As Double,
namesTransp(k, 1)
ReDim YTransp(1, n) As Double
ReDim betaVar(k, k) As Double, SEBeta(k) As Double, tStat(k) As
Double, pValue(k) As Double, confLfive(k) As Double, confHfive(k) As Double,
confLzero(k) As Double, confHzero(k) As Double
Dim YTranspY As Double, betaTranspXTranspY As Double
Dim RSS As Double 'Residual Sum of Square
Dim TSS As Double 'total sum of squares
Dim ESS As Double 'residual sum of squares
Dim RMS As Double 'Sq[Standard error of Est Y] - residual
variance
Dim meanY As Double, rSq As Double, sumY As Double, adjRSq As Double
Dim fStat As Double
Dim LaggedResiduals() As Double
Dim ReducedResiduals() As Double
Dim ResidualDifferenceSquared() As Double
Dim SumNumerator As Double
Dim SumDenominator As Double
ReDim LaggedResiduals(1 To n - 1)
ReDim ReducedResiduals(1 To n - 1)
ReDim ResidualDifferenceSquared(1 To n - 1)

Delete_Output

'1. Read in data - assign array and compute mean

For i = 1 To k ' Names
Names(1, i) = Sheets("Regression Input").Cells(3, i + 1)
Next i

sumY = 0
For i = 1 To n
For j = 1 To k - 1 'K-1 independent variables
x(i, j + 1) = Sheets("Regression
Input").Cells(i + 3, j + 1)

Next j
x(i, 1) = 1
y(i, 1) = Sheets("Regression Input").Cells(i + 3, 1) '
Independent

sumY = sumY + y(i, 1)
Next i
meanY = sumY / n
'2. Transpose X and Y arrays - X' and Y'
For i = 1 To k
For j = 1 To n
XTransp(i, j) = x(j, i)
YTransp(1, j) = y(j, 1)
Next j
f(i, 1) = Sheets("Regression Input").Cells(2000 * i, 1)
+ 1
Next i
'3. Compute X'X
For i = 1 To k
For j = 1 To k
XTranspX(i, j) =
Application.Index((Application.MMult(XTransp, x)), i, j) 'X'X
Next j
Next i
'4. Compute INV(X'X) and X'y
For i = 1 To k
For j = 1 To k
invXTranspX(i, j) =
Application.Index((Application.MInverse(XTranspX)), i, j) 'INV(X'X)
XTranspY(i, 1) =
Application.Index((Application.MMult(XTransp, y)), i, 1) 'X'Y
Next j
Next i
'5. Compute beta = INV(X'X)* X'Y
For i = 1 To k
For j = 1 To k
beta(i, 1) =
Application.Index((Application.MMult(invXTranspX, XTranspY)), i, 1)
Sheets("Regression Output").Cells(10 + i, 2) =
beta(i, 1)

Next j
Next i
'6. Compute beta'
For i = 1 To k
betaTransp(1, i) = beta(i, 1)

Next i
'7. Compute RSS = residual sum of squares = sum(sqr(e))=ee' = YY' - beta'X'Y
YTranspY = Application.Index((Application.MMult(YTransp, y)), 1, 1)
betaTranspXTranspY =
Application.Index((Application.MMult(betaTransp, XTranspY)), 1, 1)
RSS = YTranspY - betaTranspXTranspY 'regression sum of
squares - Explained
'8. Compute RMS, TSS, ESS, rSq, AdjRSq, fStat
RMS = RSS / (n - k) 'residual variance
TSS = YTranspY - n * meanY ^ 2 'total sum of squares
ESS = betaTranspXTranspY - n * meanY ^ 2 'residual sum of
squares
rSq = ESS / TSS
adjRSq = 1 - (1 - rSq) * ((n - 1) / (n - k))
fStat = (rSq / (k - 1)) / ((1 - rSq) / (n - k))

Sheets("Regression Output").Cells(3, 2) = (rSq) ^ 0.5
Sheets("Regression Output").Cells(4, 2) = rSq
Sheets("Regression Output").Cells(5, 2) = adjRSq
Sheets("Regression Output").Cells(6, 2) = Sqr(RMS)
Sheets("Regression Output").Cells(7, 2) = fStat
Sheets("Regression Output").Cells(8, 2) = n


Sheets("Regression Output").Cells(3, 5) = k - 1
Sheets("Regression Output").Cells(4, 5) = n - k
Sheets("Regression Output").Cells(5, 5) = n - 1
Sheets("Regression Output").Cells(3, 8) = fStat
Sheets("Regression Output").Cells(4, 6) = RMS * (n - k)
Sheets("Regression Output").Cells(5, 6) = TSS
Sheets("Regression Output").Cells(3, 6) = ESS
Sheets("Regression Output").Cells(3, 7) = ESS / (k - 1)
Sheets("Regression Output").Cells(4, 7) = RMS
Sheets("Regression Output").Cells(3, 9) = Application.FDist(fStat,
k - 1, n - k)

'9. Compute Standard Error, t Stats, P values, Confidence intervals for the
betas
For i = 1 To k 'Covariance matrix for beta
For j = 1 To k
betaVar(i, j) = RMS * invXTranspX(i, j)
'var-cov matrix for beta = RSS*INV(X'X)
Next j

SEBeta(i) = Sqr(Abs(betaVar(i, i)))
tStat(i) = Abs(beta(i, 1)) / SEBeta(i)
pValue(i) = Application.TDist(tStat(i), n - k, 2)
confLfive(i) = beta(i, 1) - SEBeta(i) * Application.TInv(0.05, n -
k)
confHfive(i) = beta(i, 1) + SEBeta(i) * Application.TInv(0.05, n -
k)
confLzero(i) = beta(i, 1) - SEBeta(i) * Application.TInv(0.1, n - k)
confHzero(i) = beta(i, 1) + SEBeta(i) * Application.TInv(0.1, n - k)


Sheets("Regression Output").Cells(10 + i, 3) = SEBeta(i) '
standard errors
Sheets("Regression Output").Cells(10 + i, 4) = tStat(i) ' t Stats
Sheets("Regression Output").Cells(10 + i, 5) = pValue(i) ' p Value
Sheets("Regression Output").Cells(10 + i, 6) = confLfive(i) ' 95%
confid Low
Sheets("Regression Output").Cells(10 + i, 7) = confHfive(i) ' 95%
confid High
Sheets("Regression Output").Cells(10 + i, 8) = confLzero(i) ' 90%
confid Low
Sheets("Regression Output").Cells(10 + i, 9) = confHzero(i) ' 90%
confid High

Next i


'10. Compute e = residuals = Y - betaX, Duban Watson Statistic

For i = 1 To n 'Residuals
For j = 1 To k
e(i, j) = beta(j, 1) * x(i, j)
Residuals(i, 1) = y(i, 1) -
Application.Index((Application.MMult(e, f)), i, 1)
Sheets("Regression Output").Cells(i + 3, 16) =
Residuals(i, 1)
Next j

Next i

For i = 1 To n - 1 'DW Statistic
j = 1
LaggedResiduals(i) = Residuals(i + 1, 1)
ResidualDifferenceSquared(i) = (Residuals(i, 1) -
LaggedResiduals(i)) ^ 2
SumNumerator = SumNumerator + ResidualDifferenceSquared(i)
Next i

For i = 1 To n
SumDenominator = SumDenominator + Residuals(i, 1) ^ 2
Next i

dw = SumNumerator / SumDenominator
Sheets("Regression Output").Cells(7, 5) = dw

'11 Compute autocorrellations of lagged residuals
For j = 1 To 6
ReDim LaggedResiduals(1 To n - j)
ReDim ReducedResiduals(1 To n - j)
For i = 1 To n - j 'Auto Correlation Statistic

LaggedResiduals(i) = Residuals(i + j, 1)
ReducedResiduals(i) = Residuals(i, 1)
Sheets("Regression Output").Cells(i + 3, 27 +
j) = LaggedResiduals(i)
Sheets("Regression Output").Cells(i + 48, 27 +
j) = ReducedResiduals(i)

Next i
Autocor = Application.Correl(ReducedResiduals,
LaggedResiduals)
Sheets("Regression Output").Cells(3 + j, 12) = Autocor
Sheets("Regression Output").Cells(3 + j, 14) = Autocor *
Sqr(n - j)
Next j

'12 Compute t stat, SE of autocorrelations
For i = 1 To 7

Sheets("Regression Output").Cells(2 + i, 11) = i - 1
If i > 1 Then Sheets("Regression Output").Cells(2 + i,
13) = 1 / Sqr(n - i + 1)

For j = 1 To k
namesTransp(j, 1) = Names(1, j)
Sheets("Regression Output").Cells(11 + j, 1) =
namesTransp(j, 1)
Next j
Next i



End Sub
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA Code for Logit regression 1
Help in sorting data 2
Loop returns only one value, does not loop & an assignment to columnhow-to problem. 26
Runtime Error 1004 on Linest in VBA 1
Runtime Error 1004 on Linest 7
Runtime Error 1004 2
Modify working code to expand the output. 5
Excel seems to hang while running a UDF 1

Top