+ Reply to Thread
Results 1 to 22 of 22

Generalised version of LINEST using QR Decomposition

  1. #1
    Harlan Grove
    Guest

    Re: Generalised version of LINEST using QR Decomposition

    Will wrote...
    >Does anyone know if a generalised version of the Excel 2003 LINEST function
    >using QR decomposition is available, beyond that posted in the knowledge base
    >article(http://support.microsoft.com/kb/828533)? That shows the way in which
    >this can be done with 2 regressors but not for n regressors and I need to
    >know how this can be extended. Ideally, I think this should be available as a
    >free bug fix in Excel, as Microsoft has acknowledged that this is a bug.
    >Also, the Knowledge Base article doesn't show how the standard error of each
    >coefficient can be determined, hence it's impossible to derive the t-stats
    >for each coefficient.


    It's fixed in the sense that if it's really important to you, you can
    upgrade to Excel 2003 (or 2004 for Macs). There's not going to be a
    free fix for earlier versions of Excel.

    As for the QR decomposition, it's an explanation of how it works. From
    skimming it, it could be extended to more than two independent (X)
    variables *if* you know the underlying linear algebraic equations. If
    you don't, you shouldn't be messing with this.


  2. #2
    Will
    Guest

    RE: Generalised version of LINEST using QR Decomposition

    Thanks Harlan, but the biggest problem is that I work at a firm where any
    rollout of new software is done on a corporate-wide basis and takes several
    years to occur. If I could take the upgrade path then I would, but this isn't
    available to me for several years.

    With regards to the given solution, some steps are unclear, for example, why
    a column of 1s is added and why the columns are switched around, hence the
    explanation is not sufficiently clear in order to generalise. I am trying to
    generate an Excel-based solution for some econometrics tests (specifically,
    the Augmented Dickey-Fuller test which is specifically designed to account
    for correlations in the lagged variables, and hence is guaranteed to generate
    problems with multi-collinearity) and I would rather not be forced down the
    route of switching to using Matlab instead of Excel.

  3. #3
    Will
    Guest

    Re: Generalised version of LINEST using QR Decomposition

    "Harlan Grove" wrote:

    > Will wrote...
    > ....
    > >With regards to the given solution, some steps are unclear, for example, why
    > >a column of 1s is added and why the columns are switched around, hence the
    > >explanation is not sufficiently clear in order to generalise. . . .

    >
    > Let me try again. The explanation is clear to anyone who knows the
    > linear algebraic form of the least squares and maximum likelihood
    > estimators and how to implement them brute force in Excel. It can be
    > generalized, but you'd need to be very familiar with Excel array
    > formulas and matrix arithmetic functions. I'm not deliberately trying
    > to be derogatory (some would say it just comes naturally, but I
    > digress), but if you don't see how to generalize the approach in the
    > Microsoft KnowledgeBase, then you wouldn't be likely to be able to spot
    > problems in an Excel implementation of QR decomposition.


    To answer my own question above, the reason for the column of 1s in the
    knowledge base article is that the regression using QR decomposition is of
    the form Rx = Q[T]b, so the regression is fitting a coefficient to a unit
    value, which is equivalent to solving for the constant term based on the
    usual construction of y=ax + b. A better description of what's going on
    (which would have made the knowledge base article far more useful and
    understandable) is available at
    http://en.wikipedia.org/wiki/Linear_least_squares
    >
    > You may need a fully constructed workbook implementation of QR
    > decomposition. I'm not aware of any available on the web, but others
    > may.
    >
    > > . . . I am trying to
    > >generate an Excel-based solution for some econometrics tests (specifically,
    > >the Augmented Dickey-Fuller test which is specifically designed to account
    > >for correlations in the lagged variables, and hence is guaranteed to generate
    > >problems with multi-collinearity) and I would rather not be forced down the
    > >route of switching to using Matlab instead of Excel.

    >
    > You shouldn't be using Excel for this. Even Excel 2003 has its
    > weaknesses, and rolling your own QR decomposition template would result
    > in a less efficient and less robust (in the software sense, not the
    > statistical sense) tool than most dedicated stats packages.
    >
    > There are much cheaper alternatives than MATLAB. There are addon
    > packages for R and gretl that calculate augmented Dickey-Fuller tests,
    > and both R and gretl are GNU software packages. There may also be time
    > series add-ins for Excel that provide this test and cost less than
    > MATLAB. Dunno.
    >
    >


  4. #4
    Harlan Grove
    Guest

    Re: Generalised version of LINEST using QR Decomposition

    Will wrote...
    ....
    >With regards to the given solution, some steps are unclear, for example, why
    >a column of 1s is added and why the columns are switched around, hence the
    >explanation is not sufficiently clear in order to generalise. . . .


    Let me try again. The explanation is clear to anyone who knows the
    linear algebraic form of the least squares and maximum likelihood
    estimators and how to implement them brute force in Excel. It can be
    generalized, but you'd need to be very familiar with Excel array
    formulas and matrix arithmetic functions. I'm not deliberately trying
    to be derogatory (some would say it just comes naturally, but I
    digress), but if you don't see how to generalize the approach in the
    Microsoft KnowledgeBase, then you wouldn't be likely to be able to spot
    problems in an Excel implementation of QR decomposition.

    You may need a fully constructed workbook implementation of QR
    decomposition. I'm not aware of any available on the web, but others
    may.

    > . . . I am trying to
    >generate an Excel-based solution for some econometrics tests (specifically,
    >the Augmented Dickey-Fuller test which is specifically designed to account
    >for correlations in the lagged variables, and hence is guaranteed to generate
    >problems with multi-collinearity) and I would rather not be forced down the
    >route of switching to using Matlab instead of Excel.


    You shouldn't be using Excel for this. Even Excel 2003 has its
    weaknesses, and rolling your own QR decomposition template would result
    in a less efficient and less robust (in the software sense, not the
    statistical sense) tool than most dedicated stats packages.

    There are much cheaper alternatives than MATLAB. There are addon
    packages for R and gretl that calculate augmented Dickey-Fuller tests,
    and both R and gretl are GNU software packages. There may also be time
    series add-ins for Excel that provide this test and cost less than
    MATLAB. Dunno.


  5. #5
    Harlan Grove
    Guest

    Re: Generalised version of LINEST using QR Decomposition

    Will wrote...
    >Does anyone know if a generalised version of the Excel 2003 LINEST function
    >using QR decomposition is available, beyond that posted in the knowledge base
    >article(http://support.microsoft.com/kb/828533)? That shows the way in which
    >this can be done with 2 regressors but not for n regressors and I need to
    >know how this can be extended. Ideally, I think this should be available as a
    >free bug fix in Excel, as Microsoft has acknowledged that this is a bug.
    >Also, the Knowledge Base article doesn't show how the standard error of each
    >coefficient can be determined, hence it's impossible to derive the t-stats
    >for each coefficient.


    It's fixed in the sense that if it's really important to you, you can
    upgrade to Excel 2003 (or 2004 for Macs). There's not going to be a
    free fix for earlier versions of Excel.

    As for the QR decomposition, it's an explanation of how it works. From
    skimming it, it could be extended to more than two independent (X)
    variables *if* you know the underlying linear algebraic equations. If
    you don't, you shouldn't be messing with this.


  6. #6
    Will
    Guest

    RE: Generalised version of LINEST using QR Decomposition

    Thanks Harlan, but the biggest problem is that I work at a firm where any
    rollout of new software is done on a corporate-wide basis and takes several
    years to occur. If I could take the upgrade path then I would, but this isn't
    available to me for several years.

    With regards to the given solution, some steps are unclear, for example, why
    a column of 1s is added and why the columns are switched around, hence the
    explanation is not sufficiently clear in order to generalise. I am trying to
    generate an Excel-based solution for some econometrics tests (specifically,
    the Augmented Dickey-Fuller test which is specifically designed to account
    for correlations in the lagged variables, and hence is guaranteed to generate
    problems with multi-collinearity) and I would rather not be forced down the
    route of switching to using Matlab instead of Excel.

  7. #7
    Will
    Guest

    Re: Generalised version of LINEST using QR Decomposition

    "Harlan Grove" wrote:

    > Will wrote...
    > ....
    > >With regards to the given solution, some steps are unclear, for example, why
    > >a column of 1s is added and why the columns are switched around, hence the
    > >explanation is not sufficiently clear in order to generalise. . . .

    >
    > Let me try again. The explanation is clear to anyone who knows the
    > linear algebraic form of the least squares and maximum likelihood
    > estimators and how to implement them brute force in Excel. It can be
    > generalized, but you'd need to be very familiar with Excel array
    > formulas and matrix arithmetic functions. I'm not deliberately trying
    > to be derogatory (some would say it just comes naturally, but I
    > digress), but if you don't see how to generalize the approach in the
    > Microsoft KnowledgeBase, then you wouldn't be likely to be able to spot
    > problems in an Excel implementation of QR decomposition.


    To answer my own question above, the reason for the column of 1s in the
    knowledge base article is that the regression using QR decomposition is of
    the form Rx = Q[T]b, so the regression is fitting a coefficient to a unit
    value, which is equivalent to solving for the constant term based on the
    usual construction of y=ax + b. A better description of what's going on
    (which would have made the knowledge base article far more useful and
    understandable) is available at
    http://en.wikipedia.org/wiki/Linear_least_squares
    >
    > You may need a fully constructed workbook implementation of QR
    > decomposition. I'm not aware of any available on the web, but others
    > may.
    >
    > > . . . I am trying to
    > >generate an Excel-based solution for some econometrics tests (specifically,
    > >the Augmented Dickey-Fuller test which is specifically designed to account
    > >for correlations in the lagged variables, and hence is guaranteed to generate
    > >problems with multi-collinearity) and I would rather not be forced down the
    > >route of switching to using Matlab instead of Excel.

    >
    > You shouldn't be using Excel for this. Even Excel 2003 has its
    > weaknesses, and rolling your own QR decomposition template would result
    > in a less efficient and less robust (in the software sense, not the
    > statistical sense) tool than most dedicated stats packages.
    >
    > There are much cheaper alternatives than MATLAB. There are addon
    > packages for R and gretl that calculate augmented Dickey-Fuller tests,
    > and both R and gretl are GNU software packages. There may also be time
    > series add-ins for Excel that provide this test and cost less than
    > MATLAB. Dunno.
    >
    >


  8. #8
    Harlan Grove
    Guest

    Re: Generalised version of LINEST using QR Decomposition

    Will wrote...
    ....
    >With regards to the given solution, some steps are unclear, for example, why
    >a column of 1s is added and why the columns are switched around, hence the
    >explanation is not sufficiently clear in order to generalise. . . .


    Let me try again. The explanation is clear to anyone who knows the
    linear algebraic form of the least squares and maximum likelihood
    estimators and how to implement them brute force in Excel. It can be
    generalized, but you'd need to be very familiar with Excel array
    formulas and matrix arithmetic functions. I'm not deliberately trying
    to be derogatory (some would say it just comes naturally, but I
    digress), but if you don't see how to generalize the approach in the
    Microsoft KnowledgeBase, then you wouldn't be likely to be able to spot
    problems in an Excel implementation of QR decomposition.

    You may need a fully constructed workbook implementation of QR
    decomposition. I'm not aware of any available on the web, but others
    may.

    > . . . I am trying to
    >generate an Excel-based solution for some econometrics tests (specifically,
    >the Augmented Dickey-Fuller test which is specifically designed to account
    >for correlations in the lagged variables, and hence is guaranteed to generate
    >problems with multi-collinearity) and I would rather not be forced down the
    >route of switching to using Matlab instead of Excel.


    You shouldn't be using Excel for this. Even Excel 2003 has its
    weaknesses, and rolling your own QR decomposition template would result
    in a less efficient and less robust (in the software sense, not the
    statistical sense) tool than most dedicated stats packages.

    There are much cheaper alternatives than MATLAB. There are addon
    packages for R and gretl that calculate augmented Dickey-Fuller tests,
    and both R and gretl are GNU software packages. There may also be time
    series add-ins for Excel that provide this test and cost less than
    MATLAB. Dunno.


  9. #9
    Will
    Guest

    Re: Generalised version of LINEST using QR Decomposition

    "Harlan Grove" wrote:

    > Will wrote...
    > ....
    > >With regards to the given solution, some steps are unclear, for example, why
    > >a column of 1s is added and why the columns are switched around, hence the
    > >explanation is not sufficiently clear in order to generalise. . . .

    >
    > Let me try again. The explanation is clear to anyone who knows the
    > linear algebraic form of the least squares and maximum likelihood
    > estimators and how to implement them brute force in Excel. It can be
    > generalized, but you'd need to be very familiar with Excel array
    > formulas and matrix arithmetic functions. I'm not deliberately trying
    > to be derogatory (some would say it just comes naturally, but I
    > digress), but if you don't see how to generalize the approach in the
    > Microsoft KnowledgeBase, then you wouldn't be likely to be able to spot
    > problems in an Excel implementation of QR decomposition.


    To answer my own question above, the reason for the column of 1s in the
    knowledge base article is that the regression using QR decomposition is of
    the form Rx = Q[T]b, so the regression is fitting a coefficient to a unit
    value, which is equivalent to solving for the constant term based on the
    usual construction of y=ax + b. A better description of what's going on
    (which would have made the knowledge base article far more useful and
    understandable) is available at
    http://en.wikipedia.org/wiki/Linear_least_squares
    >
    > You may need a fully constructed workbook implementation of QR
    > decomposition. I'm not aware of any available on the web, but others
    > may.
    >
    > > . . . I am trying to
    > >generate an Excel-based solution for some econometrics tests (specifically,
    > >the Augmented Dickey-Fuller test which is specifically designed to account
    > >for correlations in the lagged variables, and hence is guaranteed to generate
    > >problems with multi-collinearity) and I would rather not be forced down the
    > >route of switching to using Matlab instead of Excel.

    >
    > You shouldn't be using Excel for this. Even Excel 2003 has its
    > weaknesses, and rolling your own QR decomposition template would result
    > in a less efficient and less robust (in the software sense, not the
    > statistical sense) tool than most dedicated stats packages.
    >
    > There are much cheaper alternatives than MATLAB. There are addon
    > packages for R and gretl that calculate augmented Dickey-Fuller tests,
    > and both R and gretl are GNU software packages. There may also be time
    > series add-ins for Excel that provide this test and cost less than
    > MATLAB. Dunno.
    >
    >


  10. #10
    Harlan Grove
    Guest

    Re: Generalised version of LINEST using QR Decomposition

    Will wrote...
    >Does anyone know if a generalised version of the Excel 2003 LINEST function
    >using QR decomposition is available, beyond that posted in the knowledge base
    >article(http://support.microsoft.com/kb/828533)? That shows the way in which
    >this can be done with 2 regressors but not for n regressors and I need to
    >know how this can be extended. Ideally, I think this should be available as a
    >free bug fix in Excel, as Microsoft has acknowledged that this is a bug.
    >Also, the Knowledge Base article doesn't show how the standard error of each
    >coefficient can be determined, hence it's impossible to derive the t-stats
    >for each coefficient.


    It's fixed in the sense that if it's really important to you, you can
    upgrade to Excel 2003 (or 2004 for Macs). There's not going to be a
    free fix for earlier versions of Excel.

    As for the QR decomposition, it's an explanation of how it works. From
    skimming it, it could be extended to more than two independent (X)
    variables *if* you know the underlying linear algebraic equations. If
    you don't, you shouldn't be messing with this.


  11. #11
    Will
    Guest

    RE: Generalised version of LINEST using QR Decomposition

    Thanks Harlan, but the biggest problem is that I work at a firm where any
    rollout of new software is done on a corporate-wide basis and takes several
    years to occur. If I could take the upgrade path then I would, but this isn't
    available to me for several years.

    With regards to the given solution, some steps are unclear, for example, why
    a column of 1s is added and why the columns are switched around, hence the
    explanation is not sufficiently clear in order to generalise. I am trying to
    generate an Excel-based solution for some econometrics tests (specifically,
    the Augmented Dickey-Fuller test which is specifically designed to account
    for correlations in the lagged variables, and hence is guaranteed to generate
    problems with multi-collinearity) and I would rather not be forced down the
    route of switching to using Matlab instead of Excel.

  12. #12
    Harlan Grove
    Guest

    Re: Generalised version of LINEST using QR Decomposition

    Will wrote...
    ....
    >With regards to the given solution, some steps are unclear, for example, why
    >a column of 1s is added and why the columns are switched around, hence the
    >explanation is not sufficiently clear in order to generalise. . . .


    Let me try again. The explanation is clear to anyone who knows the
    linear algebraic form of the least squares and maximum likelihood
    estimators and how to implement them brute force in Excel. It can be
    generalized, but you'd need to be very familiar with Excel array
    formulas and matrix arithmetic functions. I'm not deliberately trying
    to be derogatory (some would say it just comes naturally, but I
    digress), but if you don't see how to generalize the approach in the
    Microsoft KnowledgeBase, then you wouldn't be likely to be able to spot
    problems in an Excel implementation of QR decomposition.

    You may need a fully constructed workbook implementation of QR
    decomposition. I'm not aware of any available on the web, but others
    may.

    > . . . I am trying to
    >generate an Excel-based solution for some econometrics tests (specifically,
    >the Augmented Dickey-Fuller test which is specifically designed to account
    >for correlations in the lagged variables, and hence is guaranteed to generate
    >problems with multi-collinearity) and I would rather not be forced down the
    >route of switching to using Matlab instead of Excel.


    You shouldn't be using Excel for this. Even Excel 2003 has its
    weaknesses, and rolling your own QR decomposition template would result
    in a less efficient and less robust (in the software sense, not the
    statistical sense) tool than most dedicated stats packages.

    There are much cheaper alternatives than MATLAB. There are addon
    packages for R and gretl that calculate augmented Dickey-Fuller tests,
    and both R and gretl are GNU software packages. There may also be time
    series add-ins for Excel that provide this test and cost less than
    MATLAB. Dunno.


  13. #13
    Harlan Grove
    Guest

    Re: Generalised version of LINEST using QR Decomposition

    Will wrote...
    ....
    >With regards to the given solution, some steps are unclear, for example, why
    >a column of 1s is added and why the columns are switched around, hence the
    >explanation is not sufficiently clear in order to generalise. . . .


    Let me try again. The explanation is clear to anyone who knows the
    linear algebraic form of the least squares and maximum likelihood
    estimators and how to implement them brute force in Excel. It can be
    generalized, but you'd need to be very familiar with Excel array
    formulas and matrix arithmetic functions. I'm not deliberately trying
    to be derogatory (some would say it just comes naturally, but I
    digress), but if you don't see how to generalize the approach in the
    Microsoft KnowledgeBase, then you wouldn't be likely to be able to spot
    problems in an Excel implementation of QR decomposition.

    You may need a fully constructed workbook implementation of QR
    decomposition. I'm not aware of any available on the web, but others
    may.

    > . . . I am trying to
    >generate an Excel-based solution for some econometrics tests (specifically,
    >the Augmented Dickey-Fuller test which is specifically designed to account
    >for correlations in the lagged variables, and hence is guaranteed to generate
    >problems with multi-collinearity) and I would rather not be forced down the
    >route of switching to using Matlab instead of Excel.


    You shouldn't be using Excel for this. Even Excel 2003 has its
    weaknesses, and rolling your own QR decomposition template would result
    in a less efficient and less robust (in the software sense, not the
    statistical sense) tool than most dedicated stats packages.

    There are much cheaper alternatives than MATLAB. There are addon
    packages for R and gretl that calculate augmented Dickey-Fuller tests,
    and both R and gretl are GNU software packages. There may also be time
    series add-ins for Excel that provide this test and cost less than
    MATLAB. Dunno.


  14. #14
    Will
    Guest

    Re: Generalised version of LINEST using QR Decomposition

    "Harlan Grove" wrote:

    > Will wrote...
    > ....
    > >With regards to the given solution, some steps are unclear, for example, why
    > >a column of 1s is added and why the columns are switched around, hence the
    > >explanation is not sufficiently clear in order to generalise. . . .

    >
    > Let me try again. The explanation is clear to anyone who knows the
    > linear algebraic form of the least squares and maximum likelihood
    > estimators and how to implement them brute force in Excel. It can be
    > generalized, but you'd need to be very familiar with Excel array
    > formulas and matrix arithmetic functions. I'm not deliberately trying
    > to be derogatory (some would say it just comes naturally, but I
    > digress), but if you don't see how to generalize the approach in the
    > Microsoft KnowledgeBase, then you wouldn't be likely to be able to spot
    > problems in an Excel implementation of QR decomposition.


    To answer my own question above, the reason for the column of 1s in the
    knowledge base article is that the regression using QR decomposition is of
    the form Rx = Q[T]b, so the regression is fitting a coefficient to a unit
    value, which is equivalent to solving for the constant term based on the
    usual construction of y=ax + b. A better description of what's going on
    (which would have made the knowledge base article far more useful and
    understandable) is available at
    http://en.wikipedia.org/wiki/Linear_least_squares
    >
    > You may need a fully constructed workbook implementation of QR
    > decomposition. I'm not aware of any available on the web, but others
    > may.
    >
    > > . . . I am trying to
    > >generate an Excel-based solution for some econometrics tests (specifically,
    > >the Augmented Dickey-Fuller test which is specifically designed to account
    > >for correlations in the lagged variables, and hence is guaranteed to generate
    > >problems with multi-collinearity) and I would rather not be forced down the
    > >route of switching to using Matlab instead of Excel.

    >
    > You shouldn't be using Excel for this. Even Excel 2003 has its
    > weaknesses, and rolling your own QR decomposition template would result
    > in a less efficient and less robust (in the software sense, not the
    > statistical sense) tool than most dedicated stats packages.
    >
    > There are much cheaper alternatives than MATLAB. There are addon
    > packages for R and gretl that calculate augmented Dickey-Fuller tests,
    > and both R and gretl are GNU software packages. There may also be time
    > series add-ins for Excel that provide this test and cost less than
    > MATLAB. Dunno.
    >
    >


  15. #15
    Will
    Guest

    RE: Generalised version of LINEST using QR Decomposition

    Thanks Harlan, but the biggest problem is that I work at a firm where any
    rollout of new software is done on a corporate-wide basis and takes several
    years to occur. If I could take the upgrade path then I would, but this isn't
    available to me for several years.

    With regards to the given solution, some steps are unclear, for example, why
    a column of 1s is added and why the columns are switched around, hence the
    explanation is not sufficiently clear in order to generalise. I am trying to
    generate an Excel-based solution for some econometrics tests (specifically,
    the Augmented Dickey-Fuller test which is specifically designed to account
    for correlations in the lagged variables, and hence is guaranteed to generate
    problems with multi-collinearity) and I would rather not be forced down the
    route of switching to using Matlab instead of Excel.

  16. #16
    Harlan Grove
    Guest

    Re: Generalised version of LINEST using QR Decomposition

    Will wrote...
    >Does anyone know if a generalised version of the Excel 2003 LINEST function
    >using QR decomposition is available, beyond that posted in the knowledge base
    >article(http://support.microsoft.com/kb/828533)? That shows the way in which
    >this can be done with 2 regressors but not for n regressors and I need to
    >know how this can be extended. Ideally, I think this should be available as a
    >free bug fix in Excel, as Microsoft has acknowledged that this is a bug.
    >Also, the Knowledge Base article doesn't show how the standard error of each
    >coefficient can be determined, hence it's impossible to derive the t-stats
    >for each coefficient.


    It's fixed in the sense that if it's really important to you, you can
    upgrade to Excel 2003 (or 2004 for Macs). There's not going to be a
    free fix for earlier versions of Excel.

    As for the QR decomposition, it's an explanation of how it works. From
    skimming it, it could be extended to more than two independent (X)
    variables *if* you know the underlying linear algebraic equations. If
    you don't, you shouldn't be messing with this.


  17. #17
    Harlan Grove
    Guest

    Re: Generalised version of LINEST using QR Decomposition

    Will wrote...
    ....
    >With regards to the given solution, some steps are unclear, for example, why
    >a column of 1s is added and why the columns are switched around, hence the
    >explanation is not sufficiently clear in order to generalise. . . .


    Let me try again. The explanation is clear to anyone who knows the
    linear algebraic form of the least squares and maximum likelihood
    estimators and how to implement them brute force in Excel. It can be
    generalized, but you'd need to be very familiar with Excel array
    formulas and matrix arithmetic functions. I'm not deliberately trying
    to be derogatory (some would say it just comes naturally, but I
    digress), but if you don't see how to generalize the approach in the
    Microsoft KnowledgeBase, then you wouldn't be likely to be able to spot
    problems in an Excel implementation of QR decomposition.

    You may need a fully constructed workbook implementation of QR
    decomposition. I'm not aware of any available on the web, but others
    may.

    > . . . I am trying to
    >generate an Excel-based solution for some econometrics tests (specifically,
    >the Augmented Dickey-Fuller test which is specifically designed to account
    >for correlations in the lagged variables, and hence is guaranteed to generate
    >problems with multi-collinearity) and I would rather not be forced down the
    >route of switching to using Matlab instead of Excel.


    You shouldn't be using Excel for this. Even Excel 2003 has its
    weaknesses, and rolling your own QR decomposition template would result
    in a less efficient and less robust (in the software sense, not the
    statistical sense) tool than most dedicated stats packages.

    There are much cheaper alternatives than MATLAB. There are addon
    packages for R and gretl that calculate augmented Dickey-Fuller tests,
    and both R and gretl are GNU software packages. There may also be time
    series add-ins for Excel that provide this test and cost less than
    MATLAB. Dunno.


  18. #18
    Will
    Guest

    Generalised version of LINEST using QR Decomposition

    Does anyone know if a generalised version of the Excel 2003 LINEST function
    using QR decomposition is available, beyond that posted in the knowledge base
    article(http://support.microsoft.com/kb/828533)? That shows the way in which
    this can be done with 2 regressors but not for n regressors and I need to
    know how this can be extended. Ideally, I think this should be available as a
    free bug fix in Excel, as Microsoft has acknowledged that this is a bug.
    Also, the Knowledge Base article doesn't show how the standard error of each
    coefficient can be determined, hence it's impossible to derive the t-stats
    for each coefficient.

  19. #19
    Will
    Guest

    RE: Generalised version of LINEST using QR Decomposition

    Thanks Harlan, but the biggest problem is that I work at a firm where any
    rollout of new software is done on a corporate-wide basis and takes several
    years to occur. If I could take the upgrade path then I would, but this isn't
    available to me for several years.

    With regards to the given solution, some steps are unclear, for example, why
    a column of 1s is added and why the columns are switched around, hence the
    explanation is not sufficiently clear in order to generalise. I am trying to
    generate an Excel-based solution for some econometrics tests (specifically,
    the Augmented Dickey-Fuller test which is specifically designed to account
    for correlations in the lagged variables, and hence is guaranteed to generate
    problems with multi-collinearity) and I would rather not be forced down the
    route of switching to using Matlab instead of Excel.

  20. #20
    Harlan Grove
    Guest

    Re: Generalised version of LINEST using QR Decomposition

    Will wrote...
    >Does anyone know if a generalised version of the Excel 2003 LINEST function
    >using QR decomposition is available, beyond that posted in the knowledge base
    >article(http://support.microsoft.com/kb/828533)? That shows the way in which
    >this can be done with 2 regressors but not for n regressors and I need to
    >know how this can be extended. Ideally, I think this should be available as a
    >free bug fix in Excel, as Microsoft has acknowledged that this is a bug.
    >Also, the Knowledge Base article doesn't show how the standard error of each
    >coefficient can be determined, hence it's impossible to derive the t-stats
    >for each coefficient.


    It's fixed in the sense that if it's really important to you, you can
    upgrade to Excel 2003 (or 2004 for Macs). There's not going to be a
    free fix for earlier versions of Excel.

    As for the QR decomposition, it's an explanation of how it works. From
    skimming it, it could be extended to more than two independent (X)
    variables *if* you know the underlying linear algebraic equations. If
    you don't, you shouldn't be messing with this.


  21. #21
    Will
    Guest

    Re: Generalised version of LINEST using QR Decomposition

    "Harlan Grove" wrote:

    > Will wrote...
    > ....
    > >With regards to the given solution, some steps are unclear, for example, why
    > >a column of 1s is added and why the columns are switched around, hence the
    > >explanation is not sufficiently clear in order to generalise. . . .

    >
    > Let me try again. The explanation is clear to anyone who knows the
    > linear algebraic form of the least squares and maximum likelihood
    > estimators and how to implement them brute force in Excel. It can be
    > generalized, but you'd need to be very familiar with Excel array
    > formulas and matrix arithmetic functions. I'm not deliberately trying
    > to be derogatory (some would say it just comes naturally, but I
    > digress), but if you don't see how to generalize the approach in the
    > Microsoft KnowledgeBase, then you wouldn't be likely to be able to spot
    > problems in an Excel implementation of QR decomposition.


    To answer my own question above, the reason for the column of 1s in the
    knowledge base article is that the regression using QR decomposition is of
    the form Rx = Q[T]b, so the regression is fitting a coefficient to a unit
    value, which is equivalent to solving for the constant term based on the
    usual construction of y=ax + b. A better description of what's going on
    (which would have made the knowledge base article far more useful and
    understandable) is available at
    http://en.wikipedia.org/wiki/Linear_least_squares
    >
    > You may need a fully constructed workbook implementation of QR
    > decomposition. I'm not aware of any available on the web, but others
    > may.
    >
    > > . . . I am trying to
    > >generate an Excel-based solution for some econometrics tests (specifically,
    > >the Augmented Dickey-Fuller test which is specifically designed to account
    > >for correlations in the lagged variables, and hence is guaranteed to generate
    > >problems with multi-collinearity) and I would rather not be forced down the
    > >route of switching to using Matlab instead of Excel.

    >
    > You shouldn't be using Excel for this. Even Excel 2003 has its
    > weaknesses, and rolling your own QR decomposition template would result
    > in a less efficient and less robust (in the software sense, not the
    > statistical sense) tool than most dedicated stats packages.
    >
    > There are much cheaper alternatives than MATLAB. There are addon
    > packages for R and gretl that calculate augmented Dickey-Fuller tests,
    > and both R and gretl are GNU software packages. There may also be time
    > series add-ins for Excel that provide this test and cost less than
    > MATLAB. Dunno.
    >
    >


  22. #22
    Will
    Guest

    Re: Generalised version of LINEST using QR Decomposition

    "Harlan Grove" wrote:

    > Will wrote...
    > ....
    > >With regards to the given solution, some steps are unclear, for example, why
    > >a column of 1s is added and why the columns are switched around, hence the
    > >explanation is not sufficiently clear in order to generalise. . . .

    >
    > Let me try again. The explanation is clear to anyone who knows the
    > linear algebraic form of the least squares and maximum likelihood
    > estimators and how to implement them brute force in Excel. It can be
    > generalized, but you'd need to be very familiar with Excel array
    > formulas and matrix arithmetic functions. I'm not deliberately trying
    > to be derogatory (some would say it just comes naturally, but I
    > digress), but if you don't see how to generalize the approach in the
    > Microsoft KnowledgeBase, then you wouldn't be likely to be able to spot
    > problems in an Excel implementation of QR decomposition.


    To answer my own question above, the reason for the column of 1s in the
    knowledge base article is that the regression using QR decomposition is of
    the form Rx = Q[T]b, so the regression is fitting a coefficient to a unit
    value, which is equivalent to solving for the constant term based on the
    usual construction of y=ax + b. A better description of what's going on
    (which would have made the knowledge base article far more useful and
    understandable) is available at
    http://en.wikipedia.org/wiki/Linear_least_squares
    >
    > You may need a fully constructed workbook implementation of QR
    > decomposition. I'm not aware of any available on the web, but others
    > may.
    >
    > > . . . I am trying to
    > >generate an Excel-based solution for some econometrics tests (specifically,
    > >the Augmented Dickey-Fuller test which is specifically designed to account
    > >for correlations in the lagged variables, and hence is guaranteed to generate
    > >problems with multi-collinearity) and I would rather not be forced down the
    > >route of switching to using Matlab instead of Excel.

    >
    > You shouldn't be using Excel for this. Even Excel 2003 has its
    > weaknesses, and rolling your own QR decomposition template would result
    > in a less efficient and less robust (in the software sense, not the
    > statistical sense) tool than most dedicated stats packages.
    >
    > There are much cheaper alternatives than MATLAB. There are addon
    > packages for R and gretl that calculate augmented Dickey-Fuller tests,
    > and both R and gretl are GNU software packages. There may also be time
    > series add-ins for Excel that provide this test and cost less than
    > MATLAB. Dunno.
    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1