Hi Michael
This solution includes a variable number of explaining variables and returns also the auxilliary stats of the OLS.
Assuming the following setup:
- Columns A:M house your raw data, with the headers in Row 1.
- Column A:Year, column B: Code, Column C: Y values, columns D:M X values of the explaining variables for the regression, maximum of 10 explaining variables.
- The raw data is sorted first key Year, second key Code.
- In N2: The number of explaining variables you want to use. The code will use this number of explaining variables, in columns from D to the right.
- In columns O:P, the table with the unique combinations of Year and Code
The code will write the OLS values, including the stats, in the columns to the right of column P.
Remarks
1 - I changed the order of the values in the result.
The default order in LINEST for the coefficients is the reverse order of the explaining variables. Ex you have the variables X1, X2, X3 and the line parameters come in this order m3, m2, m1, b
I changed this and give you the line coefficients in the same order as the explaining variables, with the independent term at the end. So, for the variables X1, X2, X3 the order from left to right in the cells of the worksheeet is m1, m2, m3, b. I find this more logical. I hope you agree.
2 - Why is it a fixed number of columns for the explaining variables
I find it easier like this because when I did stats I remember we had to run lots of regressions with not only different numbers of explaining variables, but also different sets of explaining variables. With this setup you can import all the raw data into the spreadsheet and just by moving the columns around and changind the value in N2 you can run a lot of different regressions without importing data again.
3 - Each time the code starts the results of the previous OLS are erased.
Please try the code and let me know how it behaves.
PGC
P. S. The results part of the worksheet comes in the next post.
Code:
Sub LinestYrCd()
Dim rY As Range, rCY As Range, rFilt As Range, vLin
Dim iEV As Integer, i As Integer
Application.ScreenUpdating = False
ActiveSheet.AutoFilterMode = False
Columns("Q:AP").ClearContents
iEV = Range("N2").Value ' Number of explaining variables
With Range("q1") ' Write the headers
For i = 1 To iEV
.Offset(, i - 1) = "m" & i
.Offset(, iEV + i) = "se" & i
Next
.Offset(, iEV) = "b"
.Offset(, 2 * iEV + 1).Resize(1, 7) = Array("seb", "r2", "sey", "F", "df", "ssreg", "sresid")
End With
Set rY = Range("o2", Range("o" & Rows.Count).End(xlUp))
For Each rCY In rY
Range("A:M").AutoFilter Field:=1, Criteria1:=rCY.Value
Range("A:M").AutoFilter Field:=2, Criteria1:=rCY.Offset(, 1).Value
Set rFilt = Range("C2:M" & Range("C" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeVisible)
vLin = WorksheetFunction.LinEst(rFilt.Columns(1), rFilt.Columns(2).Resize(, iEV), True, True)
' Writes the coefficients and their standard errors
For i = 1 To iEV + 1
rCY.Offset(, 2 + iEV - i + IIf(i = iEV + 1, i, 0)) = vLin(1, i)
rCY.Offset(, 3 + 2 * iEV - i + IIf(i = iEV + 1, i, 0)) = vLin(2, i)
Next
'Writes the other stats
For i = 1 To 3
rCY.Offset(, 2 + 2 * i + 2 * iEV) = vLin(2 + i, 1)
rCY.Offset(, 3 + 2 * i + 2 * iEV) = vLin(2 + i, 2)
Next
ActiveSheet.AutoFilterMode = False
Next
Application.ScreenUpdating = True
End Sub
ols.xls |
---|
|
---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N |
---|
1 | Year | Code | Y | X1 | X2 | X3 | X4 | X5 | X6 | X7 | X8 | X9 | X10 | NExplVars |
---|
2 | 1999 | 25 | 12 | 1 | 23 | 111 | | | | | | | | 2 |
---|
3 | 1999 | 25 | 16 | 5 | 25 | 123 | | | | | | | | |
---|
4 | 1999 | 25 | 18 | 10 | 29 | 143 | | | | | | | | |
---|
5 | 1999 | 25 | 24 | 15 | 66 | 177 | | | | | | | | |
---|
6 | 1999 | 25 | 26 | 16 | 69 | 198 | | | | | | | | |
---|
7 | 1999 | 25 | 28 | 17 | 72 | 224 | | | | | | | | |
---|
8 | 1999 | 23 | 14 | 1 | 45 | 111 | | | | | | | | |
---|
9 | 1999 | 23 | 16 | 3 | 45 | 134 | | | | | | | | |
---|
10 | 1999 | 23 | 19 | 6 | 48 | 136 | | | | | | | | |
---|
11 | 1999 | 23 | 24 | 9 | 55 | 178 | | | | | | | | |
---|
12 | 1999 | 23 | 27 | 12 | 56 | 211 | | | | | | | | |
---|
13 | 2000 | 23 | 13 | 1 | 33 | 104 | | | | | | | | |
---|
14 | 2000 | 23 | 18 | 5 | 35 | 136 | | | | | | | | |
---|
15 | 2000 | 23 | 24 | 15 | 41 | 167 | | | | | | | | |
---|
16 | | | | | | | | | | | | | | |
---|
17 | | | | | | | | | | | | | | |
---|
|
---|