The Correlation Code

We’ve discussed how correlations can be useful in planning strategic forex trades. Getting proper correlation information is also important – and surprisingly, it’s something you can do for yourself, for free. It’s not even that difficult, though you will want a spreadsheet program and a charting program. We’ll give examples of what to do in both Excel and Open Office.

You’ll need a charting program that downloads daily currency prices; there are plenty of them, and most will save the data in either comma separated values or native .xls formats. You’ll need to import the data into Excel; the simple way to do this is to simply open the file and cut and paste. You can also get a little bit clever and tell your charting program to overwrite the existing file after each download, and link to the file within Excel, by opening the data file and Excel at the same time, typing the equals sign in a cell, and then clicking on the appropriate cell in the data file. Repeat this for each currency price that you want to track.

What you’ll end up with is a series of columns pulled from the currency price pairs. (Be sure to label them). You’ll want to pull historical data running back a day, a week, two weeks, a month, three months and six months.

Now, underneath each of the columns, you’re going to use the Excel =CORREL() function. Enter =CORREL( and then select the first column of data, then enter a comma and select the second column of data.

If your data were in the columns A1 through A7 and B1 through B7, the final formula would look like this:
=CORREL(A1:A7,B1:B7)

The way that CORREL works is that it takes the values in the ranges and runs a standard statistical correlation (which is X-X * Y-Y over the square root of X-X^2 * Y-Y^2).  The CORREL function requires that both sets of data have the same number of values in them, otherwise it will return an #N/A error. There are a few cases where CORREL will provide a #DIV/0 error, but you’re unlikely to run into them.

Now, to be real clever about this, repeat it for every set of currency pairs you want to track the correlations on. This will take about an hour or so to set up in most cases, and if you do it right, can automatically update every time you pull down a new range of currency prices.

If you’re deep into analysis, instead of using full ranges on the =CORREL() function, you can do this for every item that comes through and build a general trend analysis tool. Feeding these numbers into Excel’s charting functions can even give you regular charts on correlations – and setting up charts means going to the Charts and Graphs Wizard and selecting which cells you want to show on the data plot.

The aim here is to give you a quick glance picture that you understand about the correlation involved in a set of currency pairs, so that you can make informed decisions in a short period of time; though correlations aren’t going to change much on the ‘five minute plot’ that day traders live on, having the data update automatically, and having the summary in place (along with date and time traces for when things changed) can let you back track the correlation’s changes with news announcements, and see what, if anything, is driving them. This can be useful information for making a future forex trade later.

You can also do this sort of correlation mapping with tools other than Excel. Open Office also has a =CORREL() function, but it uses a semicolon (;) rather than a comma (,) for its formatting. It has the same limitations that the =CORREL() function in Excel does.

Technorati Tags: Forex Correlation, fx correlation table