Creating a VBA Macro to Individually Regress Columns B-J All Against Column O with DataAnalysis Tookpack

[Rejexted]

Hi guys and gals!

I've scoured the internet for a solution, but have yet to find a proper solution. What I want to do is quite simple: individually regress columns B-J (RAD to TPD) against Column O (Mkt-Rf) in my spreadsheet. I really want to use DataAnalysis, too, because it gives me everything I need for the CAPM finance model (Beta, Alpha, & T-Stat). My current code doesn't work, this is what it looks like:

Dim i As Integer
Dim stock_name As String
Dim y_Range As Range
Dim x_Range As Range

i = 1
Set y_Range = Range("B2:B123")
Set x_Range = Range("O2:O123")

For i = 1 To 9

stock_name = y_Range.Cells(1, 1)


Application.Run "ATPVBAEN.XLAM!Regress", ActiveSheet.Range(y_Range), _
ActiveSheet.Range(x_Range), False, True, , stock_name, False, False, False _
, False, , False

y_Range = y_Range.Offset(0, 1).Select

Next i

End Sub



This is what my data looks like (I'm doing a linear regression for CAPM, for all you finance heads out there)

[TABLE="width: 920"]
<tbody>[TR]
[TD]Date[/TD]
[TD]RAD[/TD]
[TD]WAG[/TD]
[TD]CVS[/TD]
[TD]ARBR[/TD]
[TD]FAY[/TD]
[TD]MSII[/TD]
[TD]ECK[/TD]
[TD]BIGB[/TD]
[TD]TPD[/TD]
[TD][/TD]
[TD]Date[/TD]
[TD]S&P 500[/TD]
[TD]3-Mo T-Bill[/TD]
[TD]Mkt-Rf[/TD]
[/TR]
[TR]
[TD]5/29/1987[/TD]
[TD]-4.53%[/TD]
[TD]1.03%[/TD]
[TD]3.05%[/TD]
[TD]-15.49%[/TD]
[TD]-1.45%[/TD]
[TD]4.76%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5/29/1987[/TD]
[TD]0.60%[/TD]
[TD="align: right"]5.67%[/TD]
[TD]-5.07%[/TD]
[/TR]
[TR]
[TD]6/30/1987[/TD]
[TD]-4.06%[/TD]
[TD]12.24%[/TD]
[TD]8.50%[/TD]
[TD]0.00%[/TD]
[TD]2.94%[/TD]
[TD]6.82%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]6/30/1987[/TD]
[TD]4.79%[/TD]
[TD="align: right"]5.69%[/TD]
[TD]-0.90%[/TD]
[/TR]
[TR]
[TD]7/31/1987[/TD]
[TD]10.81%[/TD]
[TD]2.12%[/TD]
[TD]8.52%[/TD]
[TD]6.67%[/TD]
[TD]-1.43%[/TD]
[TD]-3.19%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]7/31/1987[/TD]
[TD]4.82%[/TD]
[TD="align: right"]6.04%[/TD]
[TD]-1.22%[/TD]
[/TR]
[TR]
[TD]8/31/1987[/TD]
[TD]1.49%[/TD]
[TD]-1.19%[/TD]
[TD]-1.57%[/TD]
[TD]-4.69%[/TD]
[TD]2.90%[/TD]
[TD]6.59%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]8/31/1987[/TD]
[TD]3.50%[/TD]
[TD="align: right"]6.40%[/TD]
[TD]-2.90%[/TD]
[/TR]
[TR]
[TD]9/30/1987[/TD]
[TD]-22.62%[/TD]
[TD]-4.21%[/TD]
[TD]-4.94%[/TD]
[TD]-16.39%[/TD]
[TD]-7.04%[/TD]
[TD]3.61%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]9/30/1987[/TD]
[TD]-2.42%[/TD]
[TD="align: right"]6.13%[/TD]
[TD]-8.55%[/TD]
[/TR]
[TR]
[TD]10/30/1987[/TD]
[TD]-22.40%[/TD]
[TD]-23.51%[/TD]
[TD]-28.02%[/TD]
[TD]-29.41%[/TD]
[TD]-22.73%[/TD]
[TD]-29.35%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]10/30/1987[/TD]
[TD]-21.76%[/TD]
[TD="align: right"]5.69%[/TD]
[TD]-27.45%[/TD]
[/TR]
[TR]
[TD]11/30/1987[/TD]
[TD]-0.16%[/TD]
[TD]-13.11%[/TD]
[TD]-12.35%[/TD]
[TD]-9.72%[/TD]
[TD]0.00%[/TD]
[TD]5.63%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]11/30/1987[/TD]
[TD]-8.53%[/TD]
[TD="align: right"]5.77%[/TD]
[TD]-14.30%[/TD]
[/TR]
</tbody>[/TABLE]

I appreciate your help!

-Rejexted

 

[RickXL]

Hi and welcome to the MrExcel Message Board.

A couple of tweaks were required:

Sub Regression()

    Dim i As Integer
    Dim stock_name As String
    Dim y_Range As Range
    Dim x_Range As Range
    
    i = 1
    Set y_Range = Range("B2:B8")
    Set x_Range = Range("O2:O8")
    
    For i = 1 To 9
    
        stock_name = y_Range.Cells(1, 1).Offset(-1, 0)
        
        
        Application.Run "ATPVBAEN.XLAM!Regress", y_Range, _
        x_Range, False, True, , stock_name, False, False, False _
        , False, , False
        
        Set y_Range = y_Range.Offset(0, 1)

    Next i

End Sub

You tried to get the stock name from the first data row not the heading row mand you forgot the Set command when changing y_value to point to the next set of data.

 

[Rejexted]

Thanks for the help, RickXL. I very much appreciate it.

I do have one more question for you, and it only just occurred to me after running this working code that you so kindly provided.

My returns data for MSII, ECK, BIGB, and TPD all start, end, or start and end at different dates than the full 5/30/1987 - 5/30/1997 time horizon. For example, ECK returns data was only available from 9/30/1993 to 2/28/1997.

How could I could tweak this code so that it will dynamically search for the first and last row in each column and only regress the data for that time horizon? For example, VBA would register that ECK starts late at 9/30/1993 and ends early on 2/28/1997, automatically changing the x_range & y_range to regress over the same shorter time horizon?

Again, thank you for your help. I really appreciate this community and will make sure to resolve this four year old thread which was my only previous resource beforehand.

 

[RickXL]

OK, try this:

Sub Regression()

    Dim i As Integer
    Dim stock_name As String
    Dim y_Range As Range
    Dim x_Range As Range
    Dim x_Temp As Range
    
    ' Set the name of the data sheet
    With Worksheets("Sheet1")
    
        ' Set the maximum X range
        Set x_Range = .Range("O2:O" & .Cells(.Rows.Count, "O").End(xlUp).Row)
        
        ' Step through columns
        For i = 1 To 9
        
            ' Set y range to first column, minus header
            Set y_Range = .Range("B2:B" & .Rows.Count).Offset(, i - 1)
            
            ' Get the stock name from the cell above the y range
            stock_name = y_Range.Cells(1).Offset(-1).Value
            
            ' Set the y range to only those cells that have data
            Set y_Range = y_Range.SpecialCells(xlCellTypeConstants)
            
            ' Make a temporary x range the same size as the y range
            Set x_Temp = Intersect(x_Range, y_Range.Offset(, x_Range.Column - y_Range.Column))

            ' Run the regression analysis
            Application.Run "ATPVBAEN.XLAM!Regress", y_Range, x_Temp, _
                False, True, , stock_name, False, False, False, False, , False
            
        Next i
        
    End With

End Sub

It assumes the x-range is in column O but works out the number of rows itself.
It needs to match the number of observations in the x and y ranges or else the regression analysis fails.
I have added some comments.