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]

Anyone? There must be an easy way to do this.

Thanks!
Michael

 

[pgc01]

Hi Michael

Solution in 2 steps.

First: build a table with all the unique combinations of Year and IndustryCode.

Use text to columns with delimeter semicolon to split the strings
Use advanced filter with options Copy to another table and unique records.

Second: write a macro to calculate the values of linest.

In the macro:

Loop through the unique combinations

For each combination:
....Use autofilter on original table to filter only the relevant records
....Feed linest with the values
....Write in the worksheet the results for that combination

Hope this helps
PGC

P. S. I'm leaving now, but if you have doubts on the implementation post again and I'll answer later.

 

[realtylerdurdon]

Thanks for your reply! I have no problems with the importing of the data, but I am not skilled at VBA. I think I could try to record the macro, but I would need help to create a loop.

I will post again when I have recorded it. Thanks!
Michael

 

[pgc01]

Edit : replaced by a following post

 

[pgc01]

Edit: Erased.

 

[pgc01]

Hi Michael

Here is the code and the example with the data. As you can see, the confirmation values and the calculated values match.

Hope it helps
PGC

Option Explicit
Option Base 1

Sub LinestYrCd()
Dim rY As Range, rCY As Range, rFilt As Range, vLin

ActiveSheet.AutoFilterMode = False
Set rY = Range("G2", Range("G" & Rows.Count).End(xlUp))

For Each rCY In rY
    Range("A:D").AutoFilter Field:=1, Criteria1:=rCY.Value
    Range("A:D").AutoFilter Field:=2, Criteria1:=rCY.Offset(, 1).Value
    Set rFilt = Range("C2:D" & Range("C" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeVisible)
  
    vLin = WorksheetFunction.LinEst(rFilt.Columns(1), rFilt.Columns(2))
    rCY.Offset(, 2) = vLin(1)
    rCY.Offset(, 3) = vLin(2)
    ActiveSheet.AutoFilterMode = False
Next
End Sub

Consolidate1.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Year	Code	Y	X			Year	Code	Slope	Intercept		Confirmation
2	1999	25	12	1			1999	25	0.808126	11.23702
3	1999	25	16	5			1999	23	1.218274	12.4467		Year	Code	Slope	Intercept
4	1999	25	18	10			2000	23	0.75	13.08333		1999	25	0.808126	11.23702
5	1999	25	24	15								1999	23	1.218274	12.4467
6	1999	23	14	1								2000	23	0.75	13.08333
7	1999	23	16	3
8	1999	23	19	6
9	1999	23	24	9
10	1999	23	27	12
11	2000	23	13	1
12	2000	23	18	5
13	2000	23	24	15
14
15
Sheet1

 

[realtylerdurdon]

Thank you very much, unfortunately I am not able to test it right now, but I will do this on the weekend and come back to you. I will have to adjust the code for more independent / right hand side variables, but I hope I can do this by myself.

Thanks again!
Michael

 

[realtylerdurdon]

Hi pgc01,

thanks again for your help. Your original code works very well. I tried to adjust it to my needs, i.e. implementing more right hand side variables. Sadly I failed...

Could you please help me and adjust your code so that I can use more explaining variables? I will have to estimate equations with 3-5 right hand side variables, perhaps you can indicate with a comment in your code, which parts I have to adjust?

One other thing: Is it possible to include all statistics in the regression output? Right now your code puts out slope and intercept, but if you use LINEST by hand you get many other statistics like R², F statistic and standard errors for the coefficients, etc.

That would be very helpful. Hope you can help me out.

Thanks in advance and best regards!
Michael

 

[pgc01]

Hi Michael

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

I need some input from you.

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?

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?

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).

Kind regards
PGC