Automated approach to find patterns and correlations between multiple sets of data

I'm trying to find a correlation or repeated patterns between several sets of data, at this stage 15, but I plan to expand this to several hundred data sets.

At the moment I am plotting the data in sets of 3s on a line chart similar to the image below and trying to see if there are any patterns or correlation. This has very obvious downsides, mostly because a) it's manual; I could easily miss something especially if it's subtle b) doing this 3 (or even 4 or 5) at a time and trying to cover several hundred sets of data and all the combinations will take forever.

Is there an automated approach I can take to this, namely I'm looking for historical patterns or correlations between 2 or more of the data sets? I was looking into the correl function in Excel, but that won't show me patterns: it just tells me the correlation coefficient. (I'm not limited to Excel; I'm open to any program / language.)

Ive attached an example of the sort of data im working with in a Google Spreadsheet HERE

• By "data set" you appear to mean "variable". – Nick Cox Dec 9 '13 at 12:10
• I had similar questio as @NickCox, when you say dataset do you mean a variable or unique identifiable row like customer/product etc ? – forecaster Dec 9 '13 at 14:56
• By data set i meant, a block of data like daily prices for a stock over a half year period - so you would have the daily closing price for that stock for 6 months which you could plot on a chart similar to above. Ive made an example Google Spreadsheet showing to sort of data here. – sam Dec 9 '13 at 17:44

first of all when you calculate correlations, don't just use the default correlation functions in any package, they all refer to pearson correlations. also calculate the kendall's tau and spearman's rho. (Use R).

Next for the case of stock prices, only correlation may be one property, but a more useful property is called cointegration which is used in a better manner if you consider things like pairs trading.

Next these properties in case of the financial data do not stay constant, so what you should do is divide that data into slices which could be monthly or yearly or quarterly and then calculate these values for each of these slices and then come to a conclusion as to how do the two stocks behave together.

Next calculate the cross correlation function for any two stocks but once again after dividing the prices into different windows.

Next, do not use wavelets as mentioned in one of the other answers if you do not understand them, first go through the theory before even thinking about it.

Next define the local maximum and local minimum of each of the stocks and then check out the behavior of the other stock around the point when one stock has formed the local maximum or minimum. this can be done by calculating the maximum value for a group of prices for the max and the minimum for the min. this will resemble your support and resistance analysis.

Next calculate the beta between the returns of the stocks versus one common index and then group the ones that are of similar beta.

All of this can be done very easily using R.

Unfortunately, answering this question in full really requires you to be more specific about what type of pattern you are hoping to uncover.

One type of pattern that you may want to consider is one which often arises in the context of time series data (e.g., data sets consisting of a single quantity which rises and falls over time, such as the price of a stock or commodity): one of the data sets could be correlated with another via a time lag. (If your data sets are not time series, don't worry; the technique that I'm about to describe should work in principle on any x-vs.-y data set that is amenable to display in a line chart, it's just that it's most common to use it in cases where the x-axis represents a time variable.)

As a simple illustrative example of a time-lagged correlation, consider the relationship over the course of a few years observation time between day length and daily temperature at so-called "temperate" middle latitudes (e.g., roughly ~40 to 50 degrees N or S, a region which covers most of Europe and quite a lot of the major population centers of North America and Asia). If you happen to live in that part of the world, you will have probably noticed by now that, although the longest and shortest days of the year are usually June 21 and December 21, the coldest and warmest temperatures of the year typically peak a few weeks later, around mid-January and mid-July. (This is because large masses such as the ocean act effectively as "heat reservoirs", and require a few weeks to catch up to changing trends in solar conditions.)

An analysis paradigm which is capable of "catching" these types of time-lagged patterns is cross correlation; essentially it works a lot like the correl function that you mentioned in Excel, but with the additional twist that pairs of time series curves are shifted forward and backward with respect to one another over a range of input time lags, and you search for an "optimal" choice of time lag which tends to maximize the correlation effect, if indeed there is one.

Although the illustrative example that I chose (length of day vs. daily temperature) has an obvious annual periodicity, the cross correlation formalism doesn't actually require periodicity in the data in order to uncover a signal. Any ostensibly correlated data (e.g., labor participation rate vs. national GDP) will work as long as it has some bumps and wiggles in it, even if the large scale pattern in the data is that it goes up and down erratically as opposed to periodically over time. Furthermore, the time lag need not be limited to just a few weeks as in the example that I chose; in principle this type of analysis is capable of uncovering any time-lagged correlation, even one that takes months or years to materialize.

If your data does have some periodicity to it, a host of other methods potentially become available to you. You could try a form of time-frequency analysis such as classic Fourier analysis or the more modern techniques of wavelet analysis. In this paradigm, the idea is to use automated algorithms to uncover hidden periodic behavior which is difficult for you to see with the naked eye because of noise in the data. For purposes of your particular problem, you would declare any two data sets to share the same "pattern" if you uncovered shared evidence of similar periodicities (e.g., a given pair of data sets might display a weekly pattern of variation, or lunar, or monthly, or annual, or every eleven years like the sunspot cycle, or whatever).

• Thanks @stachyra - Ill have a look into those - The data is in fact stock ticker data, ive updated the chart axis with titles to reflect this – sam Dec 9 '13 at 11:52