Currency correlation using Excel

Note: You might need to install the Data Analysis pack from your Office Installation CD if it is not loaded by default. To install it go to Tools>Add-Ins...., then select Analysis Tool Pack and hit OK to start creating your currency correlations matrix.

With currency correlations fluctuating with the time period you choose, it's best to have a local copy of the correlation results for ease of use. We will show you how it is done with excel. Simply follow the steps below:

1. Use you trading platform of forex charting software to export the currency pairs you plan to study. Choose "csv" format.

2. Now use the Import functionality in excel (you reach it by going to Data>Import External Data>Import Data) to get all data into Excel.

3. Ensure the data in the imported time series agree for each row.

4. Delete the columns for Open, High and Low. Change the names of the columns with the closing prices to the names of the currency pairs to which they belong.

5. Use the CORREL function to calculate the correlation. This function works on two arrays, which will be same-length ranges of closing prices for the two pairs. Simply type into one of the empty cells "=correl(" then press the "fx" button next to the formula bar and select the two ranges. The resultant formula will look like this =CORREL(A1:A40;B1:B40) and will calculate the value of the correlation coefficient between the pairs for the chosen time period. In this example it will be 40 hours, days or weeks depending on the time scale of the charts being analyzed.

To calculate the correlation matrix of any number of pairs repeat the above steps 1 to 3 for each pair. Crop the whole table so that the names of the currency pairs are in the first row and the closing prices are only for the time period that you wish to analyze. Instead of using the CORREL function go to Tools>Data Analysis... and select "Correlation" from the list of analysis tools. Press the button next to the "Input Range" and then highlight the contents of all the columns. Check the mark next to "Labels in First Row". Select the output range by picking a cell to the right of the table. Press "OK".

Forex Educational Articles & News

Featured
Top 5 factors that affect exchange rates ...

There are many factors that affect exchange rates of currencies. However some are more important in currency trading than others. These are; Interest and Inflation rates, Trade balance, Currency market speculation, Foreign investment and Central bank market intervention. Learn how to use these factors in your forex tra ...

Forex Navigation