Posts by: Brendan K
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?
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?
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... Read More
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... 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?
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
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... Read More
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... 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.
Yes, I do have some sample data. I will try to post by tomorrow. Thanks for the help.
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... Read More
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... Read More
Re: WLSRegression using Excel VBA
Works perfectly. Thanks so much.
Works perfectly. Thanks so much.
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?
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
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 ... Read More
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 ... Read More
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.