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