0
$\begingroup$

I am trying to find correlations between multiple data sets, for this example i am using two pieces of data, the closing stock price for Coca-Cola and Pepsi for the last year.

I've been pointed in the direction of using the =CORREL() function in excel which has given me an indication that there is a correlation as it returns the value 0.869783523 but does not give me any clue as to triggers or patterns in the data which is what i am actually interested in.

Is there function or a workflow that i can implement in excel to give me a more detailed read out giving me an idea of triggers and/or patterns?

Improve this question
$\endgroup$
5
  • $\begingroup$ Not sure I understand the question, but if you need more stats output then add Data Analysis Toolkit to Excel. $\endgroup$
    – zx8754
    Commented Sep 16, 2013 at 15:29
  • $\begingroup$ @zx8754 - In a nutshell im trying to find if there are any triggers and / or patterns in the data, ie data A does this data B tends to do this. Is there a function or workflow in excel to analyze help this ? - Thanks for the Data analysis toolkit, ill check that out now $\endgroup$
    – sam
    Commented Sep 16, 2013 at 15:32
  • $\begingroup$ I think what you need is Display the trendline equation on the chart $\endgroup$
    – zx8754
    Commented Sep 16, 2013 at 15:42
  • 1
    $\begingroup$ I think you need to better define what you mean by triggers/patterns. There are a million statistical analyses you could do so telling us exactly what you are after would be much better. $\endgroup$
    – user25658
    Commented Sep 16, 2013 at 15:52
  • 1
    $\begingroup$ @BabakP - Im not particularly well versed in statistics, but ill give articulating it a try - In the simplest terms in trying to find if there are reliable or predictable instances where dataA moves and it triggers a move in dataB $\endgroup$
    – sam
    Commented Sep 16, 2013 at 15:56

1 Answer 1

Reset to default
1
$\begingroup$

I'm not an econometrician, so forgive any abuse of terminology.

From your question and further comments, you are not looking for classic correlation, you are looking for some kind of causal, or at least temporal correlation. At first, the data you have seems to be just a vector of two variables; Coke and Pepsi closing stock prices. Looking at the Pearson product-moment correlation coefficient , which is what CORREL calculates, will give you the linear relationship of one variable with the other, but no more.

If you are looking for a trigger or some other relationship, you need more information—you need some other variable or variables which affect both of your "top-level" outcomes which can explain some relationship.

You may actually already have some data, in that if you have closing prices, you probably have the closing dates as well. A first step may be to simply see if one closing price tends to lag the other, in which case some form of regression may be appropriate.

Also, and this is not based on anything authoritative outside of a hunch, so take it for what it is worth, you may wish to regress Coke on time ane Pepsi, and then Pepsi on time and Coke, and see if one or the other regressions have the company variable as significant as well as the time variable.

Your best bet, in my opinion, though would be to try and develop some database of statistics that may affect both (time, S&P close on same date, CPI on same date, spot/future price of corn (for corn syrup) on given date) and see if you can develop a model using those statistics.

Improve this answer
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.