Finding corrolation between 2 (or more data sets - stockprices)

[pudle]

Im just getting to grips with macros and VBA in excel im trying to find an automated way to see if there are any correlations between 2 (or more stock) prices

ie. Stock A's price moves -> Stock B's price tends to do this

Im at a bit of a loss as to were to start with this, could someone point me in the right direction ?

--

I was also looking at doing this with R, but i feel allot more comfortable with with VBA at the moment so id like to try to do it with them if possible

 

[ChrisM]

Excel has a number of built in stats functions, there's no need to use VBA for this. Just use the =CORREL() function

 

[AhoyNC]

Attached link may be of some help.
How to Calculate Stock Correlation Coefficient: 16 Steps

Also, even though it has kind of a steep learning curve R is very good program to use for stock analysis and with a google search you will find a number of R packages already written for portfolio and stock analysis.

 

[pudle]

Thanks @ahoyNC and @ChrisM - Ive gave both of those a try aswell as the PEARSON() function and with my data they reported that there is a correlation (around 0.8), but is there a way to get a report back that could show me triggers and patterns in the data

ie.
- In the simplest terms im trying to find if there are reliable or predictable instances where dataA moves and it triggers a move in dataB
- or if dataA does this dataB tends to do this..