Why does application.linest imply collinearity?

[MrRenstrom]

Hi,

I am totally puzzled by the behaviour of VBA application.linest. I am trying to create a function that is dynamic in respect of the order of a polynom that is fitted to a known set of data. That is, with a least square approximation trying to fit f(x)=a*x^i+b*x^(i-1)+...+constant term. Now, it works fine for polynoms up to quadratic order but then it implies that there is collinearity by consistently returning zeros for all orders lower than x^(i-1), except for the constant term. I can not figure out why, can you? The function below does not give me any errors or so otherwise...


Function LeastSquarePoly(Data() As Variant, Xindx As Integer, Yindx As Integer, Posindx As Integer, _
order As Integer, Period As Integer) As Variant
' Calculates the least square approximation with a polynom of order "order"
' Returns the coefficients to the polynom, or Empty if the period is longer than the
' target array of values or if any value is Empty
' The Calculation is done backwards from each data value, i.e. the actual position
' and the n-1 previous values of InArray
Dim FoundEmpty As Boolean
FoundEmpty = False

If Period > Posindx Then
LeastSquarePoly = Empty
Else

Dim Coeff, XkPow As Variant
Dim Xk(), Yk() As Variant
Dim PolyArr() As Variant
ReDim PolyArr(1 To order)

Dim i As Integer

For i = 1 To order
PolyArr(i) = i
Next

ReDim Xk(1 To Period)
ReDim Yk(1 To Period)

For i = 1 To Period
Xk(i) = CDbl(Data(Xindx, Posindx - Period + i))
Yk(i) = CDbl(Data(Yindx, Posindx - Period + i))
If Xk(i) = Empty Or Yk(i) = Empty Then
LeastSquarePoly = Empty
FoundEmpty = True
End If
Next

If Not FoundEmpty Then
XkPow = Application.Power(Application.Transpose(Xk), PolyArr)

Coeff = Application.LinEst(Yk, Application.Transpose(XkPow))
LeastSquarePoly = Coeff
End If

End If
End Function

 

[MrRenstrom]

I should add that the set of data is stock prices vs. dates. To my eyes these would represent an unpredictable set of data where polynoms of different orders should be possible to be fitted.

 

[shg]

Have you tried a formula-based solution to see how the results correlate with your UDF?

 

[MrRenstrom]

Well, the output up to the 2nd degree polynom looks correct and as expected, representing the data well. My problem is that it doesn't matter if I ask for a higher order, the results still are identical to the 2nd order representation.

 

[shg]

Dunno. Put a workbook on box.net and post a link?

 

[MrRenstrom]

After some, actually a lot, thinking I came to the conclusion that linest does everything right. The problem lies within the nature of the large numbers that represent date values. This actually produces the effect of collinearity when creating powers of those numbers to use as input as new independent variables. I substituted the variable value X with (X-m), m being the average of X over the sample period, and this solved the problem and reduced the collinearity. Could be good for someone else to think of if you run into the same trouble as I did. Over and out and thanks for your engagement shg.

 

[shg]

A comment on using dates as independent variables in polynomial regression: They are large. Today()^3 is on the order of 10^14, so you can end up summing numbers of very different sizes, resulting in a loss of precision.

You may get better results by normalizing them (0..1), or using a categorical series {1,2,3,...}.

 

[Aladin Akyurek]

You could also run a logarithmic (LN) transfornation your data.