Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Coding for Logit Regression.
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Coding for Logit Regression.
http://www.xs4all.nl/~ednieuw/Logit/logit.htm
-- Regards, Tom Ogilvy "Toadhall" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Coding for Logit Regression.
You can't afford free?
http://www.r-project.org/ Jerry Toadhall wrote: I need to run a logit regression in Excel but can't afford statistical packages. <snip |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
The multiple regression tool - linear or non-linear regression? | Excel Discussion (Misc queries) | |||
How configure EXCEL to do ordered logit regression | Excel Discussion (Misc queries) | |||
regression | Excel Discussion (Misc queries) | |||
"=ROW()-1" type of coding doesn't appear in a filter / is there coding that does? | Excel Programming | |||
VBA coding of a Logit regression | Excel Programming |