Hi Jerry,
I am not trying to predict, I am trying to find the specific data points
associated with a 6th degree polynomial. I have the two things only a Date
and a Close. These are compiled on a weekly basis for a stock each Monday.
Today I will add a close and a date to a stock after the market closes.
Nothing more or less.
I sometimes chart that stock, very simple, the dates and the closings. I can
add to that chart a 6th degree poly. But I do not know what the data points
are that are plotted and that is what I would like to know. I usually have
about 5 years worth of data, but I commonly only plot 2 years worth of data.
The data I use is actual closes for the stock on each Monday. After adding
the 6th degree poly, I would like to know where the line is on the chart for
any specific Monday and would like to add a column to the simple File that
says "Poly Close."
Date Close
1/1/1999 10.00
.... ...
.... ...
4/24/06 15.00
(Above is what I have)
(What I would like is)
Date Close PloyClose
1/1/1999 10.00 9.88
.... ... ...
.... ... ....
.... ... ...
4/24/06 15.00 14.65
--
David
"Jerry W. Lewis" wrote:
Presumably the stock closes are what you want to predict. That is largely
irrelevant for determining the numeric difficulty of the problem. The issue
is what you are trying to use to predict them (a 6th degree polynomial in
what?), and whether you have sampled a wide enough range to have any hope of
accurately estimating those coefficients.
As I also tried to communicate, the numerical problems are not an Excel
issue, per se, since the accuracy of the Excel chart trendline and Excel
2003's LINEST function is comparable to that of dedicated statistics
programs. Accurately fitting 6th degree polynomials is often very difficult
numerically.
Potentially even more problematic is the question of whether accurately
fitting this 6th degree polynomial would tell you anything useful. Unless
you have some theoretical basis for expecting that the form of your model (a
6th degree polynomial in whatever) is right, then accurately fitting the past
gives no assurance that the model will accurately predict the future, even in
the near term. The more empirical parameters you have to include to fit the
past, greater the chance that the fitted model will have little or no
predictive power (recall the von Neumann quote).
Jerry
"David" wrote:
Hi Again Jerry,
What I am recording are weekly closes of stocks and the precision displayed
is to the 100ths. And it is graphed based on the time line of a week. I do
not know what orthogonal
polynomials are, so I would not know how to implement that, but the 6th
degree polynomial that is displayed appears very good to me. My understanding
is that it is based on a least squares and that the numbers are so large that
Excel has a difficult time calculating out as far as the equation requires?
Thansk again for your help.
--
David
"Jerry W. Lewis" wrote:
How widely spaced are these 375 x-values?
If the x-values are 1,2,3,... then fitting a 6th degree polynomial is a very
difficult problem numerically (condition number ~3E31). The direct algorithm
used by LINEST prior to 2003 would likely produce meaningless results. The
chart trendline and LINEST might be accurate to a few figures, but the
problem could be challenging for them as well. To reliably fit a problem
this numerically difficult, you might need a package that uses quadruple
precision (I don't know of any statistics programs that do) or arbitrary
precision (cf.
http://groups.google.com/group/micro...fdea49d5c999a7
). You could probably do this in double precision by fitting orthogonal
polynomials, assuming that the x-values themselves are really accurate enough
to be worth the effort.
Your mention of "the formula provided by the graph" raises another issue:
while the chart trendline (unchanged with 2003) has always been quite good
numerically (better than almost all dedicated statistical packages, except
where they fit by orthogonal polynomials), by default very few figures of
this high quality fit get displayed on the graph. You need to right click on
the equation and change the numeric format to display scientific notation
with 14 decimal places.
Jerry
"David" wrote:
Hi Jerry,
I usually have somewhere around 375 data points, which goes up by 1 each
week, as I am tracking a stock closing, but only on a weekly basis. Yes, it
is the 6th degree polynomial that I add to the graph. I tried to figure out
the individual data points represented by the graph, using the formula
provided by the graph, but the "point" was not very accurate and I am given
the understanding that this was due to rounding errors and the precion of
Excel, prior to Office 2003.
Maybe this has all changed? I had also run across a third party add-on that
was claiming to have increased the accuracy, but did not purchase it and have
not heard anyone else mention it or vouch for it. But if I could find a way
to figure out the individual points represented by the graph, I would be
interested in doing that.
Thanks for your help.
--
David
"Jerry W. Lewis" wrote:
If by "6th degree", you mean a 6th degree polynomial, then you should be
conserned about whether you have sampled a wide enough range of data to be
able to reliably estimate the parameters. Even with independent parameters
and/or adequate data range, validation of the formula could be interesting.
Remember the famous quote of von Neumann "With four parameters I can fit an
elephant, and with five I can make him wiggle his trunk."
Jerry
"David" wrote:
I just started reading your thread and have become interested. I was at one
time trying to create data points from an anaysis of Stock closiings, that
are represented by a regression (6th degree) line. I was not able to use the
formula supplied in previous versions of Excel, prior to Ver 2003. But I
would be interested in trying this again, since i have the new version. I
would like to actually create data points as respesented by the regression
line, fi that is possible?
Thanks,
--
David