Re: WLSRegression using Excel VBA
The function works using the range of cells in Sheet2; however, some of my beta values were extremely high.

They were: 6.8125
-0.1953125
0.000973804
-275
6976
-0.5625

Is it possible to have such large values? When you run the function, is this what you are getting? Thanks.

Re: WLSRegression using Excel VBA
Actually, the 1's column is no longer needed - that was just used for the weights when my function was originally weighted least squares. All you need for PBS Betas, is the Implied Vol., the strike, and maturity. I removed the dimming of the variable types, and still getting the #Value. Maybe you... Actually, the 1's column is no longer needed - that was just used for the weights when my function was originally weighted least squares. All you need for PBS Betas, is the Implied Vol., the strike, and maturity.
I removed the dimming of the variable types, and still getting the #Value.
Maybe you can post exactly what your functions look like, maybe there is a slight difference to mine? Read More

Re: WLSRegression using Excel VBA
Here is some data to work with: Strike Maturity Implied Volatility 85 14 1.1300 95 14 0.6267 96 14 0.5783 98 14 0.5210 99 14 0.4899 99.5 14 0.4692 100 14 0.4613 101 14 0.4287 102 14 0.4012 103 14 0.3721 104 14 0.3413 105 14 0.3136 106 14 0.3081 107 14 0.2908 108 14 0.2634 109 14... Here is some data to work with:


Strike Maturity Implied Volatility
85 14 1.1300
95 14 0.6267
96 14 0.5783
98 14 0.5210
99 14 0.4899
99.5 14 0.4692
100 14 0.4613
101 14 0.4287
102 14 0.4012
103 14 0.3721
104 14 0.3413
105 14 0.3136
106 14 0.3081
107 14 0.2908
108 14 0.2634
109 14 0.2492
110 14 0.2404
111 14 0.2423
112 14 0.2380
113 14 0.2269
114 14 0.2264
115 14 0.2155
116 14 0.2268
118 14 0.2305
120 14 0.2137 Read More

Re: WLSRegression using Excel VBA
I modified the regression function for OLS, and determined it is working: Function OLSregression(y As Variant, X As Variant) As Variant Dim Xtrans As Variant, XX As Variant, XXinv As Variant, Xy As Variant Dim m1 As Variant, m2 As Variant, m3 As Variant Dim output() As Variant Xtrans = Applic... I modified the regression function for OLS, and determined it is working:

Function OLSregression(y As Variant, X As Variant) As Variant
Dim Xtrans As Variant, XX As Variant, XXinv As Variant, Xy As Variant
Dim m1 As Variant, m2 As Variant, m3 As Variant
Dim output() As Variant
Xtrans = Application.WorksheetFunction.Transpose(X)
XX = Application.WorksheetFunction.MMult(Xtrans, X)
XXinv = Application.WorksheetFunction.MInverse(XX)
Xy = Application.WorksheetFunction.MMult(Xtrans, y)
b = Application.WorksheetFunction.MMult(XXinv, Xy)
k = Application.WorksheetFunction.Count(b)
ReDim output(k) As Variant
For bcnt = 1 To k
output(bcnt - 1) = b(bcnt, 1)
Next bcnt
OLSregression = Application.WorksheetFunction.Transpose(output)
End Function

My ultimate goal is to use this function to calculate the implied volatility of a stock over the life of an option, but making the volatility a function of Strike and Maturity. I attempted to use the OLSRegression function in the below function:

Function PBSBetas(ImpliedVol As Double, Strike As Single, Maturity As Integer) As Variant
n = Application.Count(ImpliedVol)
Dim IndVar() As Double
ReDim IndVar(n, 6) As Double
For Cnt = 1 To n
IndVar(Cnt, 1) = 1
IndVar(Cnt, 2) = Strike(Cnt)
IndVar(Cnt, 3) = Strike(Cnt) ^ 2
IndVar(Cnt, 4) = Maturity(Cnt) / 252
IndVar(Cnt, 5) = (Maturity(Cnt) / 252) ^ 2
IndVar(Cnt, 6) = Strike(Cnt) * Maturity(Cnt) / 252
Next Cnt
Betas = OLSregression(ImpliedVol, IndVar)
PBSBetas = Betas
End Function

Do you see anything wrong with how this is set up? I'm getting a #value error again.

The inputs would be a vector of implied volatilities, strike prices, and time to maturity. Read More

Re: WLSRegression using Excel VBA
I see. Is it normal to have a 0 in the first cell? Or, does it mean the function can be tweaked to run properly?