Excel Q&A
Ask your geeky hard core Excel questions here!
Questions/Discussions
Sort by Date ▼ / Top Rated
Register for free or log in at the top right of this page to join the discussion
Re: WLSRegression using Excel VBA
Hi Brendan,
Just to make sure we understand the desired effect: how do you envision the usage of the Diagonal function? It looks like you're giving it a range of cells (I suppose it would be any NxN matrix on your spreadsheet), and return the elements of the main diagonal.
For example, you mig...
Hi Brendan,
Just to make sure we understand the desired effect: how do you envision the usage of the Diagonal function? It looks like you're giving it a range of cells (I suppose it would be any NxN matrix on your spreadsheet), and return the elements of the main diagonal.
For example, you might have a matrix:
4 0 2
0 7 9
5 5 1
So you want to write a function that somehow returns [4, 7, 1]? If so, this could be implemented as a Collection.
Just trying to understand the first one before moving to the second. Let us know if this is on the right track!
Read More
Just to make sure we understand the desired effect: how do you envision the usage of the Diagonal function? It looks like you're giving it a range of cells (I suppose it would be any NxN matrix on your spreadsheet), and return the elements of the main diagonal.
For example, you might have a matrix:
4 0 2
0 7 9
5 5 1
So you want to write a function that somehow returns [4, 7, 1]? If so, this could be implemented as a Collection.
Just trying to understand the first one before moving to the second. Let us know if this is on the right track!
Read More
Re: WLSRegression using Excel VBA
Well, I really am using it for an ordinary least squares because all of the weights are 1's. So the diagonal matrix, would have all 1's in the diagonal and all 0's in all other cells. Yes, the matrix would be an nxn matrix with n corresponding to the number of 1's in a vector (the weights) I copied ...
Well, I really am using it for an ordinary least squares because all of the weights are 1's. So the diagonal matrix, would have all 1's in the diagonal and all 0's in all other cells. Yes, the matrix would be an nxn matrix with n corresponding to the number of 1's in a vector (the weights) I copied pasted some random numbers you could hypothetically run an regression on, I'm using these only to determine if my regression function will return a value.
1 29.04 1 15.75 4.08
1 17.84 1 8.45 4.83
1 6.22 1 9.37 7.21
1 21.49 1 18.85 0.88
1 10.49 1 17.83 3.3
1 11.93 1 12.68 2.37
1 19.92 1 27.83 4.4
1 24.61 1 26.96 0.54
1 3.51 1 34.75 8.79
1 28.28 1 22.26 5.89
1 11.03 1 17.39 9.88
1 12.38 1 5.32 2.01
1 29 1 6.52 8.42
1 8.96 1 21.49 6.64
1 17.26 1 18.1 5.31
1 27.91 1 31.67 1.05
The first column are the weights (n), the second, the y value, the 3rd the y-intercept, and the last 2 the independent variables. Read More
1 29.04 1 15.75 4.08
1 17.84 1 8.45 4.83
1 6.22 1 9.37 7.21
1 21.49 1 18.85 0.88
1 10.49 1 17.83 3.3
1 11.93 1 12.68 2.37
1 19.92 1 27.83 4.4
1 24.61 1 26.96 0.54
1 3.51 1 34.75 8.79
1 28.28 1 22.26 5.89
1 11.03 1 17.39 9.88
1 12.38 1 5.32 2.01
1 29 1 6.52 8.42
1 8.96 1 21.49 6.64
1 17.26 1 18.1 5.31
1 27.91 1 31.67 1.05
The first column are the weights (n), the second, the y value, the 3rd the y-intercept, and the last 2 the independent variables. Read More
Re: WLSRegression using Excel VBA
Let's start with that random data you have there. We could do a few useful things, like generate a covariance matrix, etc.
What's the desired behavior of the WLSregression function? Should it calculate and then return the three calculated coefficients, or something else?
What's the desired behavior of the WLSregression function? Should it calculate and then return the three calculated coefficients, or something else?
Re: WLSRegression using Excel VBA
Yes, the desired outcome is to produce the 3 beta coefficients. One for the y-intercept, and for the 2 independent variables.
Re: WLSRegression using Excel VBA
Hi Brendan,
After looking through your code a bit more, it looks like it actually does work as intended, as long as you first select three cells going across, paste in the formula, and hit Ctrl + Shift + Enter to make it an array formula.
The formula I tested with is:
=TRANSPOSE(WLSregression...
Hi Brendan,
After looking through your code a bit more, it looks like it actually does work as intended, as long as you first select three cells going across, paste in the formula, and hit Ctrl + Shift + Enter to make it an array formula.
The formula I tested with is:
=TRANSPOSE(WLSregression(B2:B17,C2:E17,A2:A17))
(I entered the data you supplied in A2:E17)
Let me know if trying those steps works for you.
Read More
After looking through your code a bit more, it looks like it actually does work as intended, as long as you first select three cells going across, paste in the formula, and hit Ctrl + Shift + Enter to make it an array formula.
The formula I tested with is:
=TRANSPOSE(WLSregression(B2:B17,C2:E17,A2:A17))
(I entered the data you supplied in A2:E17)
Let me know if trying those steps works for you.
Read More
Re: WLSRegression using Excel VBA
Thanks. Yes, I finally got some actual data. For some reason though, the first cell is still 0. The other 2 are 22.14 and .01171. Is this what your output equaled? I did however, highlight 3 rows (down) instead of across the columns, since my code does include the transpose command as the last step ...
Thanks. Yes, I finally got some actual data. For some reason though, the first cell is still 0. The other 2 are 22.14 and .01171. Is this what your output equaled? I did however, highlight 3 rows (down) instead of across the columns, since my code does include the transpose command as the last step of the function.
Read More
Re: WLSRegression using Excel VBA
Actually, if you extend to a fourth cell, you should get the missing number.
Using the data and code you provided, we're getting these four numbers:
0
22.11698721
0.01370646
-1.032383816
Using the data and code you provided, we're getting these four numbers:
0
22.11698721
0.01370646
-1.032383816
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?
Re: WLSRegression using Excel VBA
You could actually simplify this by removing (try commenting first, of course) every line after your declaration of b. Then, your last line will just be:
WLSregression = b
Or you can just skip the need for b completely and just have:
WLSregression = Application.WorksheetFunction.MMult(XwXin...
You could actually simplify this by removing (try commenting first, of course) every line after your declaration of b. Then, your last line will just be:
WLSregression = b
Or you can just skip the need for b completely and just have:
WLSregression = Application.WorksheetFunction.MMult(XwXinv, Xwy)
(By the way, you could just use Application.MMult here.)
If, for some reason, you needed the k and output and all that, then you have to fix the indexing. Basically, VBA arrays start with index 0 by default. So the first element in output would be output(0). However, note that you are initializing bcnt = 1 in your loop. Thus, the first iteration will be setting output(1) to b(1, 1). The second iteration will work on output(2). But what about index(0)?
In essence, you skipped index(0) and that's why an empty 0 is sitting there in the first spot. You could fix this by simply changing that line to:
output(bcnt - 1) = b(bcnt, 1)
Now it works as expected! Read More
WLSregression = b
Or you can just skip the need for b completely and just have:
WLSregression = Application.WorksheetFunction.MMult(XwXinv, Xwy)
(By the way, you could just use Application.MMult here.)
If, for some reason, you needed the k and output and all that, then you have to fix the indexing. Basically, VBA arrays start with index 0 by default. So the first element in output would be output(0). However, note that you are initializing bcnt = 1 in your loop. Thus, the first iteration will be setting output(1) to b(1, 1). The second iteration will work on output(2). But what about index(0)?
In essence, you skipped index(0) and that's why an empty 0 is sitting there in the first spot. You could fix this by simply changing that line to:
output(bcnt - 1) = b(bcnt, 1)
Now it works as expected! Read More
Re: WLSRegression using Excel VBA
Works perfectly. Thanks so much.
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
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
It looks like you can get rid of the #VALUE error by removing the bcnt loop as we discussed previously. In other words, try setting OLSregression = b in your final line of the first function.
With some admittedly unrealistic data, we managed to just get a bunch of 0s for the values. Would you by ...
It looks like you can get rid of the #VALUE error by removing the bcnt loop as we discussed previously. In other words, try setting OLSregression = b in your final line of the first function.
With some admittedly unrealistic data, we managed to just get a bunch of 0s for the values. Would you by any chance have some sample data to paste in here and test together? Read More
With some admittedly unrealistic data, we managed to just get a bunch of 0s for the values. Would you by any chance have some sample data to paste in here and test together? Read More
Re: WLSRegression using Excel VBA
Yes, I do have some sample data. I will try to post by tomorrow. Thanks for the help.
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
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 did modify OLSRegression as you mentioned, and made the final line OLSRegression = b, but I'm still getting the #VALUE error for the PBSBetas function output. Does modifying the OLSRegression also require any changes to the PBSBetas function?
Re: WLSRegression using Excel VBA
Right now we are getting all 0s (but no #VALUE) with the OLSRegression function when feeding it two parameters:
1. one column of Implied Vol numbers (I2:I17 in our case)
2. three adjacent columns: one full of 1s, one with the Strike numbers, and one with the Maturity numbers (F2:H17 in our case)
...
Right now we are getting all 0s (but no #VALUE) with the OLSRegression function when feeding it two parameters:
1. one column of Implied Vol numbers (I2:I17 in our case)
2. three adjacent columns: one full of 1s, one with the Strike numbers, and one with the Maturity numbers (F2:H17 in our case)
Now the PBSBetas function you have might be problematic because of the data types in each parameter. Try removing those first. Do you still get the #VALUE? Read More
1. one column of Implied Vol numbers (I2:I17 in our case)
2. three adjacent columns: one full of 1s, one with the Strike numbers, and one with the Maturity numbers (F2:H17 in our case)
Now the PBSBetas function you have might be problematic because of the data types in each parameter. Try removing those first. Do you still get the #VALUE? Read More
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
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
Sure thing:
Function Diagonal(W) As Variant
Dim n, i, j, k As Integer
Dim temp As Variant
n = W.Count
ReDim temp(1 To n, 1 To n)
For i = 1 To n
For j = 1 To n
If j = i Then temp(i, j) = W(i) Else temp(i, j) = 0
Next j
Next i
...
Sure thing:
Function Diagonal(W) As Variant
Dim n, i, j, k As Integer
Dim temp As Variant
n = W.Count
ReDim temp(1 To n, 1 To n)
For i = 1 To n
For j = 1 To n
If j = i Then temp(i, j) = W(i) Else temp(i, j) = 0
Next j
Next i
Diagonal = temp
End Function
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.Transpose(x)
XX = Application.MMult(Xtrans, x)
XXinv = Application.MInverse(XX)
Xy = Application.MMult(Xtrans, y)
b = Application.MMult(XXinv, Xy)
k = Application.Count(b)
ReDim output(k) As Variant
For bcnt = 1 To k
output(bcnt - 1) = b(bcnt, 1)
Next bcnt
OLSregression = Application.Transpose(output)
End Function
Function PBSBetas(ImpliedVol, Strike, Maturity)
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
PBSBetas = OLSregression(ImpliedVol, IndVar)
End Function
Using this, if you try this formula...
=PBSBetasTest(I2:I17,G2:G17,H2:H17)
...you get a bunch of 0s (but, for better or for worse, no #VALUE).
However, you can get to the right numbers if you write a new, similar sub that simply writes the actual IndVar array to a new worksheet. For example, it'd end up writing the numbers to A1:F16 on Sheet2.
Then, if you change that last line in PBSBetas to...
PBSBetas = OLSregression(ImpliedVol, Sheets("Sheet2").Range("A1:F16"))
...the function works as expected!
So right now the question is: why does calling OLSregression work fine on a range of cells, but not an array? Read More
Function Diagonal(W) As Variant
Dim n, i, j, k As Integer
Dim temp As Variant
n = W.Count
ReDim temp(1 To n, 1 To n)
For i = 1 To n
For j = 1 To n
If j = i Then temp(i, j) = W(i) Else temp(i, j) = 0
Next j
Next i
Diagonal = temp
End Function
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.Transpose(x)
XX = Application.MMult(Xtrans, x)
XXinv = Application.MInverse(XX)
Xy = Application.MMult(Xtrans, y)
b = Application.MMult(XXinv, Xy)
k = Application.Count(b)
ReDim output(k) As Variant
For bcnt = 1 To k
output(bcnt - 1) = b(bcnt, 1)
Next bcnt
OLSregression = Application.Transpose(output)
End Function
Function PBSBetas(ImpliedVol, Strike, Maturity)
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
PBSBetas = OLSregression(ImpliedVol, IndVar)
End Function
Using this, if you try this formula...
=PBSBetasTest(I2:I17,G2:G17,H2:H17)
...you get a bunch of 0s (but, for better or for worse, no #VALUE).
However, you can get to the right numbers if you write a new, similar sub that simply writes the actual IndVar array to a new worksheet. For example, it'd end up writing the numbers to A1:F16 on Sheet2.
Then, if you change that last line in PBSBetas to...
PBSBetas = OLSregression(ImpliedVol, Sheets("Sheet2").Range("A1:F16"))
...the function works as expected!
So right now the question is: why does calling OLSregression work fine on a range of cells, but not an array? Read More
Re: WLSRegression using Excel VBA
Thanks. I plan to test this with the range of cells tonight.
As far as your question, OLSRegression will not work with an array because OLSRegression uses matrix multiplication?
As far as your question, OLSRegression will not work with an array because OLSRegression uses matrix multiplication?
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.
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.
I'm using the following code to create a diagonal matrix and then to produce the Betas for a regression analysis. For some reason my output ends as 0, or I receive an #Value error stating one of my values is of the wrong data type. My analysis involves a y-intercept and 2 independent variables. To be honest, I really am using this as an ordinary least squares, so essentially my weights are all 1's, or an identity matrix. Below are the 2 functions:
Function Diagonal(W) As Variant
Dim n, i, j, k As Integer
Dim temp As Variant
n = W.Count
ReDim temp(1 To n, 1 To n)
For i = 1 To n
For j = 1 To n
If j = i Then temp(i, j) = W(i) Else temp(i, j) = 0
Next j
Next i
Diagonal = temp
End Function
&
Function WLSregression(y As Variant, X As Variant, W As Variant) As Variant
Wmat = Diagonal(W)
n = W.Count
Dim Xtrans, Xw, XwX, XxXinv, Xwy As Variant
Dim m1, m2, m3, m4 As Variant
Dim output() As Variant
Xtrans = Application.WorksheetFunction.Transpose(X)
Xw = Application.WorksheetFunction.MMult(Xtrans, Wmat)
XwX = Application.WorksheetFunction.MMult(Xw, X)
XwXinv = Application.WorksheetFunction.MInverse(XwX)
Xwy = Application.WorksheetFunction.MMult(Xw, y)
b = Application.WorksheetFunction.MMult(XwXinv, Xwy)
k = Application.WorksheetFunction.Count(b)
ReDim output(k) As Variant
For bcnt = 1 To k
output(bcnt) = b(bcnt, 1)
Next bcnt
WLSregression = Application.WorksheetFunction.Transpose(output)
End Function Read More