Regression In Excel

I am trying to produce a parabolic regression (quadratic spline) for the closing price a stock. Please see below for the equation and click on the link for an examples of how it would look. I have no idea how to apply it to an Excel spreadsheet so I can study it against the stock's closing price and need someone's help to get things started.

This script calculates a parabolic regression (Quadratic Spline) of the data series based on the following 2nd order polynomial function: y(x) = b0 + b1*x + b2*x*x where b0, b1 and b2 are the calculated regression coefficients.

Sample of what the end result should look like:
http://www.sr-analyst.com/i/ParabolicReg.gif

For example I would want to see what a 34 period parabolic regression plotted on top of the closing price of a stock. I have also enclosed a sample of the stock's closing price for review.

Thanks, Steven

Good Afternoon,

I am a college student working on a project for a finance class and have started to become somewhat desperate because I cannot figure out a certain problem. I am sorry to be a bother, but I just would like some advice as to where to start.

I have a large amount of data on options prices and need to run a regression for each day in the past and then use the beta coefficients to create a column of data for each day.

I have attached a couple days of the data. Basically, I need to regress column M on columns N-R for each date (the first one is 1/25/06 in the attached file) and then output the beta coefficients columns Z-AD. All options for a given day should have the same beta coefficients. So, I need to be able to go through the data and run a regression for each day and then output the beta coefficients for that day in the appropriate columns. I have been using an add-in OLS reg to do the regressions, and it works well, I just don't know how to make it dynamically chose the appropriate data fields to regress for each day and then put these coefficients into the right columns. Each day has a different number of options available and thus a different amount of data to regress so that complicates the matter.

If anyone could take a look, I would greatly appreciate it.

Thanks a lot,
Austin

I have a set of data that makes a perfectly horizontal line. When I plot it, add the trendline and display R squared, I get 0. When I use the regression (under tools, data analysis), it gives an R2 of 1! I know the R2 should be zero, but why is the regression tool saying 1? I am using Excel 2003 and am not checking "constant equals zero". Thanks to anyone who can help!

hello

ive not done much with regression analysis in general, but to all the pros and gurus out there, question regarding regression analysis.

i have sales revenue for sales employees over the past year (each month).

is it worth doing some form of regression analysis or chart?...and if so, how do i go about doing it? thxs alot!

Hey.

I was wondering if you guys could help me with an excel problem? I really hope there are some excel fanatics in here...

I know how to obtain the values from the net and get the lines. What I don't know how to do is forecast 20 periods into the future using the regression equation. Could you guys instruct me on that?

1) This is the problem.

From yahoo, obtain a series of prices for an individual stock or for the Dow Jones Industrial or some other standard average.(at least 100 data points). Create an appropriate chart of the data. Fit three trend lines to the data, using technology. Include the equation and R2 for all models. Also, use the model that best fits the data to forecast 20 periods into the future, using technology and the regression equation. I am suppose to have a seperate chart for each regression model.

I have used excel 07 to run a regression of 4 independent variables and a Y variable. I am looking to test for multicollinearity from the regression output given. Does anyone know if this is possible? What is the best way to go about doing this?

Thanks,

I want to make kind of a 3d regression and from a set of 3d points (XYZ - Coords) i want to calculate the interpolated plane,that fits best between the set of points.

So far I've only thought of making 3 linear regressions,one on each plane (XY,XZ,YZ) and combining them to create the plane.I don't know if there's an easier,more accurate and better way to do this.

Also what's the precision of the regression equation.it seems like it provides 4 decimals for the a coefficient and 2 for the b constant.Is that good,can i make it provide more accurate equation for the regression it draws?I mean does it use this exact equation or does it display an approximation of the regression it drew?

Hi,

I would like to automate regression analysis, as done in XL 2007 .. Data/Data Analysis/Regression using vba. When I change drop-down lists, the results automatically appear in a pre-defined area of worksheet.

Your assistance would be appreciated.

Thanking you in advance.

Thank you

Hi, I am trying to create a macro that will run a regression over a specific amount of data. Each row in the sheet corresponds to a month. I want the regression to start out with a range over the last 18 months, in my case D54:D71, and increment by one everytime the regression in run. The way the sheet is set up I would need D53:D71 for 19 months, D52:D71 for 20 months and so on. I have already written the macro to work for 18 months (seen below) but if you have any ideas on how to change the range each time it would be helpful. Also the total macro has 9 other regressions on 9 different sheets so it would have to be able to change the range for all of them.

VB:

Application.Run "ATPVBAEN.XLAM!Regress", Worksheets("Home Equity").Range("$D$54:$D$71"), Worksheets("Home Equity").Range("$F$54:$K$71"), False, False, , Worksheets("Regression Data").Range("$A$29:$G$54"), False, False, False, False, , False 

If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines

I borrowed the function from shg in this thread to test if a sheet exists but it is always returning false.

http://www.excelforum.com/excel-prog...et-exists.html

I am including the entire code. It does a regression analysis on data for a second degree polynomial. In doing so, it creates a worksheet called Regression. So I test first to see if this sheet already exists within the workbook and then ask the user to delete it if it does. I've stepped through the function and the function is always returning False whether Regression exists or not. Thanks in advance. Code:

Sub Regression()
'
' Regression Macro
'
Dim OrgSheet As String
    If SheetExists("Regression") Then
        MsgBox ("Regression Worksheet exists. Delete that worksheet before continuing")

    Else
        OrgSheet = ActiveSheet.Name

        Application.Run "ATPVBAEN.XLAM!Regress", Range("B11:B17"), Range("D11:E17"), False, False, , _
            "Regression", False, False, False, False, , False 'requires VBA Analysis add in
        Sheets(OrgSheet).Select
        Range("L2").Select
    End If
End Sub

Function SheetExists(sWks As String, Optional wkb As Workbook) As Boolean
    On Error Resume Next
    SheetExists = IIf(wkb Is Nothing, ActiveWorkbook, wkb).Sheets(sWks) Is Nothing
End Function

I have been reading around the forum trying to figure out the solution to this, but was unable to, so I thought I would join and post!

I am doing cash projections for work, and I use a regression to do them. I want to make this work via macro in order to avoid doing it every month. I would like to have my Macro use a Y and X range that automatically goes to the bottom of the data (i.e. Month 1 to row 54, month 2 to row 55, etc. or wherever the end of the data happens to be)

The regression output needs to have "labels" checked.

My X and Y values come from the worksheet called "Regression Data"
The Y data is currently C1:C54
The X data is currently D1:S54
(Rows will be added to these columns each month)

The output is on the worksheet "Regression Output"
The Output range is just this entire worksheet. (1:1048576)

I attempted to simply record the macro, but kept getting errors about having no "Y" data, and when I looked at the code, I was unable to fix this and it did not appear that the macro was recording my input of the data range.

I am not sure if this will happen automatically, but the new regression (ran via macro) needs to simply replace the old macro (i.e. go right on top of it, with the same data in the same cells)

I will post my code below, if anyone is able to help me out with this. I would greatly appreciate it! Thank you!

Code:

Sub Regression()
'
' Regression Macro
'

'
     Application.Run "ATPVBAEN.XLAM!Regress", , , False, True, 95, , False _
        , False, False, False, , False
    Range("H13").Select
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    Cells.Select
    Cells.EntireColumn.AutoFit
End Sub

(Running XP/2007, very new to formulas):
I have a cell (I12) that performs a calculation: =IF(E9=0,0,(E11-E13)/((E9-0.25*E9)*0.00018939)).

I have a hyperlink in another cell that goes to another sheet in the same workbook:'D-Region 3 Delta'.

On D-Delta page, B2 has a calculation: =171*('Rural Regression Calc'!J7^0.87)*('Rural Regression Calc'! I12 ^0.25)*('Rural Regression Calc'!J9^-0.52).

This is fine except when cell I12 =0. The error is: #DIV/0!. I corrected this with: =IF( I12 =0,0,(171*('Rural Regression Calc'!J7^0.87)*('Rural Regression Calc'! I12 ^0.25)*('Rural Regression Calc'!J9^-0.52))), it works.

My Problem: when I12 does not=0 , the hyperlink to the D-Delta sheet /B2 cell isn't updated to reflect the computation change. It remains 0. My goal is to have B2 dynamically update/display correct information as I12 changes.

Can someone please help me? I don't know VB. I appreciate it very much!

Hi everyone,
I would like to run a multiple linear regression in vba. I have one dependent and three explanatory variables. I will have to use a macro of some kind, since I need to run too many regressions to do it manually. To simplify things a little bit:
- There will always be exactly three independent variables
- There are no missing values
- The data is allways numerical
I've already got four ranges defined: Yrange, X1range, X2range, X3range. I would like to take these ranges as input parameters for the regression model. The only two parameters I need are Sum Square for Regression (SSR) and the degrees of freedom. I understand that you can use excel's matrix formulas to calculate some of the input parameters, but one doesn't really get around vba. Does anybody know where I can find a (simple) source code allowing me to conduct a regression with three input parameters? Many thanks in advance

Who can help me with three questions on analyse-it toolpack when performing passing and bablok orthogonal regression.

1. There are two menus by which to start with passing and bablok A. via agreement and B. via regression. They give similar but not identical results. Which one should i use
2. How can I get a correlation coefficient using passing bablok regression
3. How can I calculate the error in slope and iontercept using passing bablok regression?

Thanx for your help.

Helen

Hello,

i have a problem with a regression.

I have a standard regression with about 10 years of data.

ex:

r = a + Beta*X + e

and i wish to calculate the beta, this is pretty simple, but what i want to do is at any given periode, only calculate the Beta based on the previous 100 days. Is this possible to do in excel? And is it possible to get the results graphically?

If not in excel, is it easily enough to do in another program?

thanks for your help

Hi All,

I am trying to approximate a surface with the following equation:

ax^3 + by^3 +cx^2y^2 + dx^2y + exy^2 + fx^2 + gy^2 + hxy + ix + jy + k.

As you have here 2 independent variables, does anybody of you, know how you can solve for the coefficients with a multidimensional regression in excel. I know how the regression tool works more or less and where to find it, but I don't know how I can apply it to a multidimensional equation??

A step by step example would be wonderful! You can find the raw data in the attachement.

Many thanks in advance and cheers,
Martin

P.s. I use Exel 2003 and 2007.

I'm trying to run a regression analysis in excel, but it keeps giving me this message: "Regression - LINEST () function returns error. Please check input ranges again." Basically, I have a whole bunch of historical data I'm running the Regression on. With all the historical data there, the regression runs just fine, but when I deleted some of the old data, it gave me this error message. I looked online for some solutions, and saw that it gives this error when there are blank cells, but I checked over and over again in the input ranges and there are NO blank cells. All of them are either populated with a 1 or 0. I even copied only the data I needed to run the regression analysis on to another spreadsheet, but it still gave me the same error. PLEASE HELP!!!! This is driving me crazy.

In Excel 2003 there used to be a way of getting the program to take 2 arrays and have excel return a regression which included intercepts, T-stats, standard deviations, etc. (i.e. standard format). You could access it through the "data" toolbar.

Anyways, I've recently switch to the 2010 version of excel, and while I'm sure it has something that returns a regression table in standard format i'm having a difficult time finding it. Does anyone know where I can find it?

Hi All,

I am trying to do multivariate polynomial regression in excel, trying to correlate data of the form y=f(x1,x2) with second order polynomials:
Y = c + a1*x1 + a2*x1^2 + a3^x1^3 + b1*x2 + b2*x2^2 + b3*x2^3

Using the following command, I have achieved this:
=LINEST(A2:A10,B2:B10^{1,2,3,0,0,0}*C2:C10^{0,0,0, 1,2,3},TRUE,TRUE)

Can anybody tell me the theory Excel uses for this kind of regression?

Thank you very much in advance!

athanasioskolios

Hi everyone,
I'm looking for a vba-based stepwise regression model that I can use and amend for regression analysis. Does anybody know a link where I can find a source code, or, alternatively, a model spreadsheet that conducts the steps manually. I'm sure it must be possible to write an appropriate code for this. Cheers