Statistical functions

Hi Group,
This is a little difficult to explain, but the underlying data is simple, so
please bear with me. I have Dates and Closings for the Dow Jones Industrial.
Similar to below:
Date Adj. Close*
01/03/05 10729.43
12/27/04 10776.13
12/20/04 10661.6
12/13/04 10638.32
.............More Data

The above is easy to graph and I have automated the process, which includes
a 6th Order Polynomial Trend line added to the graph. What I am trying to do
is find the Data Points associated with the 6th Order Polynomial Trend line.
It has simply been just too long since I have done this type of statistics. I
believe it may be necessary to create a new table to find these data points,
which I am willing to do. I can calculate the sample mean, number of sample
variables, sample variance, sample standard deviation, etc., but it has just
been too many years to bring the necessary statistical expertise to arrive at
the data points. I am trying to get a table that looks something like this:
Date Adj. Close* Trend
01/03/05 10729.43 10730.25
12/27/04 10776.13 10750.31
12/20/04 10661.6 10765.03
12/13/04 10638.32 10750.00
I have tried using some of the built in functions, but they do not yield the
same data points that have been graphed by the 6th Order Polynomial Trend
line. I have tried Trend and Forecast. I created a table many years ago,
which I think calculated the data points, but it has simply been to many
years and I have lost the statistical expertise. Any help would be greatly
appreciated. I have used the equation that can be presented with the graph,
but it also does not yield the Data Points associated with each Date and Adj
Close.
Thanks in advance.
--
David

Hi David,

You posted this selfsame question in programming and (to date) you have
received 5 responses from Tom Ogilvy and another from Mike Middleton.

In case you have lost track of that thread, here is a link:

http://tinyurl.com/67mpd



---
Regards,
Norman



"David" <[email protected]> wrote in message
news:[email protected]...
> Hi Group,
> This is a little difficult to explain, but the underlying data is simple,
> so
> please bear with me. I have Dates and Closings for the Dow Jones
> Industrial.
> Similar to below:
> Date Adj. Close*
> 01/03/05 10729.43
> 12/27/04 10776.13
> 12/20/04 10661.6
> 12/13/04 10638.32
> ............More Data
>
> The above is easy to graph and I have automated the process, which
> includes
> a 6th Order Polynomial Trend line added to the graph. What I am trying to
> do
> is find the Data Points associated with the 6th Order Polynomial Trend
> line.
> It has simply been just too long since I have done this type of
> statistics. I
> believe it may be necessary to create a new table to find these data
> points,
> which I am willing to do. I can calculate the sample mean, number of
> sample
> variables, sample variance, sample standard deviation, etc., but it has
> just
> been too many years to bring the necessary statistical expertise to arrive
> at
> the data points. I am trying to get a table that looks something like
> this:
> Date Adj. Close* Trend
> 01/03/05 10729.43 10730.25
> 12/27/04 10776.13 10750.31
> 12/20/04 10661.6 10765.03
> 12/13/04 10638.32 10750.00
> I have tried using some of the built in functions, but they do not yield
> the
> same data points that have been graphed by the 6th Order Polynomial Trend
> line. I have tried Trend and Forecast. I created a table many years ago,
> which I think calculated the data points, but it has simply been to many
> years and I have lost the statistical expertise. Any help would be greatly
> appreciated. I have used the equation that can be presented with the
> graph,
> but it also does not yield the Data Points associated with each Date and
> Adj
> Close.
> Thanks in advance.
> --
> David

Yes Norm, but if you read these responses they suggessted I post here and see
if I can get something more than than could provide. Tom's last response was
"See if you can get Harlan Grove to help you or Daniel M. They are very good
with mathematics." That is how I ended up here. Thanks.

"Norman Jones" wrote:

> Hi David,
>
> You posted this selfsame question in programming and (to date) you have
> received 5 responses from Tom Ogilvy and another from Mike Middleton.
>
> In case you have lost track of that thread, here is a link:
>
> http://tinyurl.com/67mpd
>
>
>
> ---
> Regards,
> Norman
>
>
>
> "David" <[email protected]> wrote in message
> news:[email protected]...
> > Hi Group,
> > This is a little difficult to explain, but the underlying data is simple,
> > so
> > please bear with me. I have Dates and Closings for the Dow Jones
> > Industrial.
> > Similar to below:
> > Date Adj. Close*
> > 01/03/05 10729.43
> > 12/27/04 10776.13
> > 12/20/04 10661.6
> > 12/13/04 10638.32
> > ............More Data
> >
> > The above is easy to graph and I have automated the process, which
> > includes
> > a 6th Order Polynomial Trend line added to the graph. What I am trying to
> > do
> > is find the Data Points associated with the 6th Order Polynomial Trend
> > line.
> > It has simply been just too long since I have done this type of
> > statistics. I
> > believe it may be necessary to create a new table to find these data
> > points,
> > which I am willing to do. I can calculate the sample mean, number of
> > sample
> > variables, sample variance, sample standard deviation, etc., but it has
> > just
> > been too many years to bring the necessary statistical expertise to arrive
> > at
> > the data points. I am trying to get a table that looks something like
> > this:
> > Date Adj. Close* Trend
> > 01/03/05 10729.43 10730.25
> > 12/27/04 10776.13 10750.31
> > 12/20/04 10661.6 10765.03
> > 12/13/04 10638.32 10750.00
> > I have tried using some of the built in functions, but they do not yield
> > the
> > same data points that have been graphed by the 6th Order Polynomial Trend
> > line. I have tried Trend and Forecast. I created a table many years ago,
> > which I think calculated the data points, but it has simply been to many
> > years and I have lost the statistical expertise. Any help would be greatly
> > appreciated. I have used the equation that can be presented with the
> > graph,
> > but it also does not yield the Data Points associated with each Date and
> > Adj
> > Close.
> > Thanks in advance.
> > --
> > David
>
>
>

Hi David,

I apologise, you are quite correct - I followed the thread but missed the
last exchange!

---
Regards,
Norman



"David" <[email protected]> wrote in message
news:[email protected]...
> Yes Norm, but if you read these responses they suggessted I post here and
> see
> if I can get something more than than could provide. Tom's last response
> was
> "See if you can get Harlan Grove to help you or Daniel M. They are very
> good
> with mathematics." That is how I ended up here. Thanks.
>

On Sun, 9 Jan 2005 21:05:07 -0800, "David" <[email protected]>
wrote:

>Hi Group,
>This is a little difficult to explain, but the underlying data is simple, so
>please bear with me. I have Dates and Closings for the Dow Jones Industrial.
>Similar to below:
>Date Adj. Close*
>01/03/05 10729.43
>12/27/04 10776.13
>12/20/04 10661.6
>12/13/04 10638.32
>............More Data
>
>The above is easy to graph and I have automated the process, which includes
>a 6th Order Polynomial Trend line added to the graph. What I am trying to do
>is find the Data Points associated with the 6th Order Polynomial Trend line.

If I understand you correctly, you are looking for the y value associated with
particular dates.

If that is the case, you can extract the formula from the graph, or use the
LINEST function.

If you extract the formula from the graph by copying it (after selecting the
display formula option), be sure to set the number format to maximum precision
(15 decimal digits). Or use David Braden's VBA code to extract the
coefficients directly from the chart into cells

http://groups.google.com/groups?selm....microsoft.com

You can use the LINEST function (by regressing against the same variable raised
to different powers), but, at least for versions of XL prior to 2003, it may be
less accurate than the formula from the graph.


--ron

Norman,

Please send me an email to respond to at

[email protected]

--
Regards,
Tom Ogilvy


"Norman Jones" <[email protected]> wrote in message
news:OYO%[email protected]...
> Hi David,
>
> You posted this selfsame question in programming and (to date) you have
> received 5 responses from Tom Ogilvy and another from Mike Middleton.
>
> In case you have lost track of that thread, here is a link:
>
> http://tinyurl.com/67mpd
>
>
>
> ---
> Regards,
> Norman
>
>
>
> "David" <[email protected]> wrote in message
> news:[email protected]...
> > Hi Group,
> > This is a little difficult to explain, but the underlying data is
simple,
> > so
> > please bear with me. I have Dates and Closings for the Dow Jones
> > Industrial.
> > Similar to below:
> > Date Adj. Close*
> > 01/03/05 10729.43
> > 12/27/04 10776.13
> > 12/20/04 10661.6
> > 12/13/04 10638.32
> > ............More Data
> >
> > The above is easy to graph and I have automated the process, which
> > includes
> > a 6th Order Polynomial Trend line added to the graph. What I am trying
to
> > do
> > is find the Data Points associated with the 6th Order Polynomial Trend
> > line.
> > It has simply been just too long since I have done this type of
> > statistics. I
> > believe it may be necessary to create a new table to find these data
> > points,
> > which I am willing to do. I can calculate the sample mean, number of
> > sample
> > variables, sample variance, sample standard deviation, etc., but it has
> > just
> > been too many years to bring the necessary statistical expertise to
arrive
> > at
> > the data points. I am trying to get a table that looks something like
> > this:
> > Date Adj. Close* Trend
> > 01/03/05 10729.43 10730.25
> > 12/27/04 10776.13 10750.31
> > 12/20/04 10661.6 10765.03
> > 12/13/04 10638.32 10750.00
> > I have tried using some of the built in functions, but they do not yield
> > the
> > same data points that have been graphed by the 6th Order Polynomial
Trend
> > line. I have tried Trend and Forecast. I created a table many years ago,
> > which I think calculated the data points, but it has simply been to many
> > years and I have lost the statistical expertise. Any help would be
greatly
> > appreciated. I have used the equation that can be presented with the
> > graph,
> > but it also does not yield the Data Points associated with each Date and
> > Adj
> > Close.
> > Thanks in advance.
> > --
> > David
>
>

Hi Tom,

Sent!

---
Regards,
Norman



"Tom Ogilvy" <[email protected]> wrote in message
news:ug9Uo%[email protected]...
> Norman,
>
> Please send me an email to respond to at
>
> [email protected]
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Norman Jones" <[email protected]> wrote in message
> news:OYO%[email protected]...
>> Hi David,
>>
>> You posted this selfsame question in programming and (to date) you have
>> received 5 responses from Tom Ogilvy and another from Mike Middleton.
>>
>> In case you have lost track of that thread, here is a link:
>>
>> http://tinyurl.com/67mpd
>>
>>
>>
>> ---
>> Regards,
>> Norman
>>
>>
>>
>> "David" <[email protected]> wrote in message
>> news:[email protected]...
>> > Hi Group,
>> > This is a little difficult to explain, but the underlying data is
> simple,
>> > so
>> > please bear with me. I have Dates and Closings for the Dow Jones
>> > Industrial.
>> > Similar to below:
>> > Date Adj. Close*
>> > 01/03/05 10729.43
>> > 12/27/04 10776.13
>> > 12/20/04 10661.6
>> > 12/13/04 10638.32
>> > ............More Data
>> >
>> > The above is easy to graph and I have automated the process, which
>> > includes
>> > a 6th Order Polynomial Trend line added to the graph. What I am trying
> to
>> > do
>> > is find the Data Points associated with the 6th Order Polynomial Trend
>> > line.
>> > It has simply been just too long since I have done this type of
>> > statistics. I
>> > believe it may be necessary to create a new table to find these data
>> > points,
>> > which I am willing to do. I can calculate the sample mean, number of
>> > sample
>> > variables, sample variance, sample standard deviation, etc., but it has
>> > just
>> > been too many years to bring the necessary statistical expertise to
> arrive
>> > at
>> > the data points. I am trying to get a table that looks something like
>> > this:
>> > Date Adj. Close* Trend
>> > 01/03/05 10729.43 10730.25
>> > 12/27/04 10776.13 10750.31
>> > 12/20/04 10661.6 10765.03
>> > 12/13/04 10638.32 10750.00
>> > I have tried using some of the built in functions, but they do not
>> > yield
>> > the
>> > same data points that have been graphed by the 6th Order Polynomial
> Trend
>> > line. I have tried Trend and Forecast. I created a table many years
>> > ago,
>> > which I think calculated the data points, but it has simply been to
>> > many
>> > years and I have lost the statistical expertise. Any help would be
> greatly
>> > appreciated. I have used the equation that can be presented with the
>> > graph,
>> > but it also does not yield the Data Points associated with each Date
>> > and
>> > Adj
>> > Close.
>> > Thanks in advance.
>> > --
>> > David
>>
>>
>
>

Thanks Ron. I went over to Google and started a thread there. This does look
promising.

"Ron Rosenfeld" wrote:

> On Sun, 9 Jan 2005 21:05:07 -0800, "David" <[email protected]>
> wrote:
>
> >Hi Group,
> >This is a little difficult to explain, but the underlying data is simple, so
> >please bear with me. I have Dates and Closings for the Dow Jones Industrial.
> >Similar to below:
> >Date Adj. Close*
> >01/03/05 10729.43
> >12/27/04 10776.13
> >12/20/04 10661.6
> >12/13/04 10638.32
> >............More Data
> >
> >The above is easy to graph and I have automated the process, which includes
> >a 6th Order Polynomial Trend line added to the graph. What I am trying to do
> >is find the Data Points associated with the 6th Order Polynomial Trend line.
>
> If I understand you correctly, you are looking for the y value associated with
> particular dates.
>
> If that is the case, you can extract the formula from the graph, or use the
> LINEST function.
>
> If you extract the formula from the graph by copying it (after selecting the
> display formula option), be sure to set the number format to maximum precision
> (15 decimal digits). Or use David Braden's VBA code to extract the
> coefficients directly from the chart into cells
>
> http://groups.google.com/groups?selm....microsoft.com
>
> You can use the LINEST function (by regressing against the same variable raised
> to different powers), but, at least for versions of XL prior to 2003, it may be
> less accurate than the formula from the graph.
>
>
> --ron
>

A 6th order polynomial is likely to be very ill-conditioned, with the
result that I would not be surprised if LINEST coefficients for pre-2003
Excel versions failed to match those produced by the chart trendline
(which is surprisingly good numerically). However the TREND() worksheet
function might still produce reasonable results.

To use the coefficients from the chart trendline, you must right click
on the equation displayed on the chart, and format to scientific
notation with 14 decimal places.

How many data points do you have? Is it feasible to list all the data
in the body of a reply (not an attachment, please)

Jerry

David wrote:

> Hi Group,
> This is a little difficult to explain, but the underlying data is simple, so
> please bear with me. I have Dates and Closings for the Dow Jones Industrial.
> Similar to below:
> Date Adj. Close*
> 01/03/05 10729.43
> 12/27/04 10776.13
> 12/20/04 10661.6
> 12/13/04 10638.32
> ............More Data
>
> The above is easy to graph and I have automated the process, which includes
> a 6th Order Polynomial Trend line added to the graph. What I am trying to do
> is find the Data Points associated with the 6th Order Polynomial Trend line.
> It has simply been just too long since I have done this type of statistics. I
> believe it may be necessary to create a new table to find these data points,
> which I am willing to do. I can calculate the sample mean, number of sample
> variables, sample variance, sample standard deviation, etc., but it has just
> been too many years to bring the necessary statistical expertise to arrive at
> the data points. I am trying to get a table that looks something like this:
> Date Adj. Close* Trend
> 01/03/05 10729.43 10730.25
> 12/27/04 10776.13 10750.31
> 12/20/04 10661.6 10765.03
> 12/13/04 10638.32 10750.00
> I have tried using some of the built in functions, but they do not yield the
> same data points that have been graphed by the 6th Order Polynomial Trend
> line. I have tried Trend and Forecast. I created a table many years ago,
> which I think calculated the data points, but it has simply been to many
> years and I have lost the statistical expertise. Any help would be greatly
> appreciated. I have used the equation that can be presented with the graph,
> but it also does not yield the Data Points associated with each Date and Adj
> Close.
> Thanks in advance.

Hi Jerry,

Hope you re haing a good day. I appreciate your taking a look this problem.
Tom has helped a lot in trying to solve this and a funtion called Teveval()
has come the closest to doing it. Thanks again.

"Jerry W. Lewis" wrote:

> A 6th order polynomial is likely to be very ill-conditioned, with the
> result that I would not be surprised if LINEST coefficients for pre-2003
> Excel versions failed to match those produced by the chart trendline
> (which is surprisingly good numerically). However the TREND() worksheet
> function might still produce reasonable results.
>
> To use the coefficients from the chart trendline, you must right click
> on the equation displayed on the chart, and format to scientific
> notation with 14 decimal places.
>
> How many data points do you have? Is it feasible to list all the data
> in the body of a reply (not an attachment, please)
>
> Jerry
>
> David wrote:
>
> > Hi Group,
> > This is a little difficult to explain, but the underlying data is simple, so
> > please bear with me. I have Dates and Closings for the Dow Jones Industrial.
> > Similar to below:
> > Date Adj. Close*
> > 01/03/05 10729.43
> > 12/27/04 10776.13
> > 12/20/04 10661.6
> > 12/13/04 10638.32
> > ............More Data
> >
> > The above is easy to graph and I have automated the process, which includes
> > a 6th Order Polynomial Trend line added to the graph. What I am trying to do
> > is find the Data Points associated with the 6th Order Polynomial Trend line.
> > It has simply been just too long since I have done this type of statistics. I
> > believe it may be necessary to create a new table to find these data points,
> > which I am willing to do. I can calculate the sample mean, number of sample
> > variables, sample variance, sample standard deviation, etc., but it has just
> > been too many years to bring the necessary statistical expertise to arrive at
> > the data points. I am trying to get a table that looks something like this:
> > Date Adj. Close* Trend
> > 01/03/05 10729.43 10730.25
> > 12/27/04 10776.13 10750.31
> > 12/20/04 10661.6 10765.03
> > 12/13/04 10638.32 10750.00
> > I have tried using some of the built in functions, but they do not yield the
> > same data points that have been graphed by the 6th Order Polynomial Trend
> > line. I have tried Trend and Forecast. I created a table many years ago,
> > which I think calculated the data points, but it has simply been to many
> > years and I have lost the statistical expertise. Any help would be greatly
> > appreciated. I have used the equation that can be presented with the graph,
> > but it also does not yield the Data Points associated with each Date and Adj
> > Close.
> > Thanks in advance.
>
>