Multiple OLS Regressions

[realtylerdurdon]

Hi Forum,

I have a long list of variables that looks like this one:

Year;IndustryCode;Sales;EBIT;...
...
...
...


I would like to do simple OLS regressions with LINEST() based on the combination of Year and IndustryCode. For each combination (e.g. Year 1999 and IndustryCode 23) I need a separate regression (all in all over 300 regressions). The number of records for these "blocks" varies and I need a trick to tell the LINEST() to choose the right arrays.

Any ideas?

Thanks a lot in advance!

Michael

 

[realtylerdurdon]

Hi Michael

I'll be glad to help but I can only do it Sunday night or Monday.

Thanks alot. Monday is still fine, I am not in an extreme hurry.

When you use more explaining variables can I consider that the columns are sequential, meaning the first column Y, and the next the Xs, or will you need some different layout?

The layout you propose would work great. The macro would need to be capable of adjusting the number of Xs (e.g. 1 to 10 Xs). With comments in the code I can do this by hand.

Another layout problem, maybe more complex is the result layout. Now with more explaining variables and the stats how do you want to have the result? can you post an example?

It would be ideal if the results would be in a single line. If this is too hard to do, the results could be in the usual 5 rows x (n+1) columns array (n=number of variables + 1 for the potential constant b) and have the combination of Year and SIC as identifier in the columns to the left of this array (sorry, html maker is not working for me):

1999;23;ROW1COL1ofResultArray;ROW1COL2ofResultArray;...
1999;23;ROW2COL1ofResultArray;ROW2COL2ofResultArray;...
1999;23;ROW3COL1ofResultArray;ROW3COL2ofResultArray;...
1999;23;ROW4COL1ofResultArray;ROW4COL2ofResultArray;...
1999;23;ROW5COL1ofResultArray;ROW5COL2ofResultArray;...
1999;24;ROW1COL1ofResultArray;ROW1COL2ofResultArray;...
...

If you want to try to get the stats, just add 2 parameters True to the linest and the variable Lin will be loaded with all the values in a 2-dimentional array(5,2).

Tried that and got runtime error 9, subscript out of range. Sorry, my VBA skills are nearly nonexistent...

Thanks alot!
Michael

 

[pgc01]

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.

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
Sheet1

 

[pgc01]

Now the results:

Remark: as expected, since the combination 2000-23 has only 3 Y values and we are using 2 explaining variables, the result is exact.

ols.xls
	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
1	Year	Code	m1	m2	b	se1	se2	seb	r2	sey	F	df	ssreg	sresid
2	1999	25	0.61157	0.097521	9.527273	0.209682	0.057334	1.125982	0.981103	1.114888	77.87943	3	193.6044	3.728926
3	1999	23	0.856202	0.312843	-0.88804	0.105196	0.087154	3.721402	0.998809	0.265052	838.8281	2	117.8595	0.140505
4	2000	23	4.5	-6.5	223	0	0	0	1	0	0	0	60.66667	6.46E-27
5
6
Sheet1

 

[realtylerdurdon]

Hi pgc01,

thanks alot! All the judgements you made (i.e. changing output order, fixed amount of X columns) are perfect. Unfortunately when I run the macro I get "runtime error 1004: Application defined or object defined error". Just FYI: This happend to me on my Desktop (German settings) ans on my laptop (US settings).

The status quo after the error is:
- Applied Autofilter from colums A to M
- Applied filter criteria that doesn't show any values (seems to be wrong?)
- Wrote headers in line 1 starting in column Q

One more little thing: How can I change whether a constant is used in the LINEST regression or not? Could you insert a switch for this, like the number of Xs? That would be great!

Thanks alot for your valuable help!
Michael

 

[pgc01]

Hi Michael

You are right, the second line (Applied filter criteria that doesn't show any values ) in your status quo should never happen. This would mean that you are choosing a combination of Year-Code that does not exist in your data.

We'll have to debug it. Please do the following test (I just did it and it worked).

Open a new excel. Paste the exact values in my example to columns A:P. You can paste them directly from the post.

Insert a module and paste the code.

Run the code

This should run with no problems.

Also in the test you did before, check if there were Year-Code combinations in the columns O:P that did not exist in the raw data table in the columns A:B.

Please let me know what happens.
PGC

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Year	Code	Y	X1	X2	X3	X4	X5	X6	X7	X8	X9	X10	NExplVars	Year	Code
2	1999	25	12	1	23	111								2	1999	25
3	1999	25	16	5	25	123									1999	23
4	1999	25	18	10	29	143									2000	23
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
Sheet1

 

[realtylerdurdon]

It is working now. You were right, there was a hickup in the unique combinations array. Your macro is a great help for me, I am very thankful.

In order to eliminate the constant in the regression, can I simply replace the first TRUE argument in the LINEST() in line 26 or is it more complicated?

Thanks again!
Michael

 

[pgc01]

Hi Michael

I'm glad it works!

You are right, to include or not the independent term in the ols estimation you just have to change the value of the third parameter of the LINEST.

However you can also specify it in the worksheet and use it in the macro.
With this modified macro you enter in N4 the boolean value TRUE or FALSE and this value will be used in LINEST.

Remark: if N4 = FALSE, the standard error for the independent term will be Not Available.

Hope this solves your problem
PGC

P. S. The results of my example will show in the next post.

Sub LinestYrCd()
Dim rY As Range, rCY As Range, rFilt As Range, vLin
Dim iEV As Integer, i As Integer, bConst As Boolean

Application.ScreenUpdating = False
ActiveSheet.AutoFilterMode = False

Columns("Q:AP").ClearContents
iEV = Range("N2").Value ' Number of explaining variables
bConst = Range("N4").Value ' OLS with constant term?
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), bConst, 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								b
4	1999	25	18	10	29	143								FALSE
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
Sheet1

 

[pgc01]

And here the results.

Remark: As expected, with no independent term the 2 variables can no longer explain completely the 3 Y values in the case 2000-23

ols.xls
	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
1	Year	Code	m1	m2	b	se1	se2	seb	r2	sey	F	df	ssreg	sresid
2	1999	25	0.153038	0.369243	0	0.874722	0.205108	#N/A	0.530145	4.814511	2.256632	4	104.6153	92.71807
3	1999	23	0.879876	0.292082	0	0.028969	0.004263	#N/A	0.998775	0.219473	1223.368	3	117.8555	0.144505
4	2000	23	0.517172	0.404848	0	0.173254	0.043427	#N/A	0.959697	1.563671	11.90593	1	58.2216	2.445067
5
6
Sheet1

 

[realtylerdurdon]

Thanks again! Excellent work!