Hi,

I need to perform a regression analysis on three different stock quotes in order to identify a potential trend and future prices. I have past price information for all of them, I simply do not know how to perform the specific statistics fictions on excel. I have installed the "data analysis" tool on excel, and have been able to generate accurate correlations between them. But, I need something that will allow me to determine future prices based on movements (percent change) of these three stocks compared to each other. Excel is, what I think, the best way to do this.

Can anyone help me out?

Thanks! :D

Yes, it can be done.

"Andrew Arce" wrote:
I need to perform a regression analysis on three different
stock quotes in order to identify a potential trend and
future prices.

What does that mean exactly? There are many interpretations of what you
might be talking about.

For example, "identify a potential trend" sounds like so-called linear
regression channels. But that does not involve correlations among stock
returns, AFAIK.

Are you looking for a formula of the form y = b1*x1 + b2*x2 + b3*x3, where
x1, x2 and x3 are prices (or returns?) of 3 stocks? But then what is y:
the price (or return?) of a portfolio composed of the 3 stocks?

Or are you, in fact, asking __us__ what kind of analysis __we__ would use to
predict future prices (or returns)?


"Andrew Arce" wrote:
I have past price information for all of them, I simply do
not know how to perform the specific statistics fictions on
excel.

"Fictions"? A prophetic typo! ;-)

Seriously, is it simply the functions and other tools that you do not know?

Or is it the statistical process (methodology) that you do not know; that
is, what kind of statistical analysis to use in the first place?

Again, if you have a particular statistical methodology in mind, it would
help if you shared that with us.


"Andrew Arce" wrote:
I have installed the "data analysis" tool on excel, and
have been able to generate accurate correlations between
them.

Exactly what did you do? That might give us some insight into what kind of
analysis you are talking about.

For example, do you have 3 columns of historical returns
(price[t-1]/price[t]; or log returns?), one for each stock? And did you use
the Regression data analysis tool? If so, what did you specify as X and Y
inputs?


"Andrew Arce" wrote:
But, I need something that will allow me to determine
future prices based on movements (percent change) of these
three stocks compared to each other.

"We cannot predict the future based on the past". I'm sure you have heard
that said many times. Nevertheless, we all engage in this fantasy. So
let's move on.

If this is for a class, it would be helpful to know some of the "parameters"
of the assignment. For example, perhaps the assignment offers some hints or
other guidance about how the instructor wants you to do the analysis. Or
perhaps there are topics of discussion that have come up in class that might
offer some guidance indirectly. You might elaborate.


"Andrew Arce" wrote:
Excel is, what I think, the best way to do this.

"Best way"? Most knowledgable people would disagree. Nevertheless, it
might be the "best" tool that most of us have at our disposal.


"Andrew Arce" wrote:
Can anyone help me out?

Statistical analysis in the hands of __experts__ is an art as much as a
science. It is "bad art" in the hands of the statistical novice.

There are oh-so many methods used by so-called "technical analysts". Some
of them have the __appearance__ of being statistical in nature because they
use statistical jargon. But most are really based on "junk science".

True linear regression (not the so-called linear regression channels) can be
used to determine the standard correlation (aka beta) between two assets,
typically an individual stock and a market index.

And that could be used to estimate the __next__ price (or return) based on
assumptions about the independent variable (e.g. the market index) for the
unit time period of the linear regression model. (Of course, how we derive
those assumptions is another matter altogether.)

But that is only a binary relationship.

And to go beyond the next unit time period, I believe we need to use Monte
Carlo simulation methods, which goes far beyond "regression analysis".

Is that what you are asking about?