Calcular correlaciones para muchos mercados

Cómo calcular correlaciones

A la hora de ver las correlaciones entre los distintos mercados es importante darse cuenta que deben ser calculadas con los retornos diarios y no con el precio de los activos. El cálculo de correlaciones es muy tedioso si son muchos mercados ya que debe hacerse "todos con todos" y por tanto se necesita la potencia de un lenguaje de programación orientado a manejar gran volumen de datos, como es Python y en especial la librería Pandas.

Haremos lo siguiente:

1. Desde amibroker exportamos los datos a una carpeta "Quotes" de nuestro disco duro. Podemos usar el siguiente código:



--------------------------------------------------------------------------------------------------------------
/*
Export intraday and EOD data to TXT files 
One file for each stock
In the first line insert the directory you want to save them to, make sure the directory exists
Select your charts to export with the "Apply to" filter in AA window 
Select the timeframe period you want to save as using the AA "Settings"
Press Scan button
by Graham Kavanagh 05 Feb 2004
C:\Users\OSCAR\Documents\Quotes
*/

Plot(C,"Last",colorRed,styleBar, Null, Null, 0, 0, 2);

fh = fopen( "C:\\Users\\OSCAR\\Documents\\Quotes\\"+Name()+".csv", "w"); 
if( fh ) 
{ 
fputs( "Symbol,Date,Time,Open,High,Low,Close,Volume \n", fh ); 
y = Year(); 
m = Month(); 
d = Day(); 


for( i = 0; i < BarCount; i++ ) 
{ 
fputs( Name() + "," , fh );
ds = StrFormat("%02.0f%02.0f%02.0f,", y[ i ], m[ i ], d[ i ] ); 
fputs( ds, fh ); 

//ponemos un cero en el tiempo
fputs("0,",fh);

qs = StrFormat("%.4f,%.4f,%.4f,%.4f,%.0f\n", O[ i ],H[ i ],L[ i ],C[ i ],V[ i ] ); 
fputs( qs, fh ); 
} 

fclose( fh ); 
} 


Buy=Sell=0; // for scan
Filter = Status("lastbarinrange");
AddTextColumn("Exportado!", "Status");
--------------------------------------------------------------------------------------------------------------

Este es el resultado:

2. Ahora que tenemos los ficheros con los datos en la carpeta Quotes los podemos procesar en Python con el siguiente código:

--------------------------------------------------------------------------------------------------------------
# -*- coding: utf-8 -*-
"""
Created on Sun Feb 26 19:34:39 2017
@author: OSCAR
CALCULA LA MATRIZ DE CORRELACIONES DE LOS SÍMBOLOS QUE ESTÉN EN LA CARPETA QUOTES2
"""

import os
import glob
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
import seaborn

# These settings modify the way  pandas prints data stored in a DataFrame.
# In particular when we use print(data_frame_reference); function - all
#  column values of the frame will be printed in the same  row instead of
# being automatically wrapped after 6 columns by default. This will be
# for looking at our data at the end of the program.
#pd.set_option('display.height', 1000)
pd.set_option('display.max_rows', 500)
pd.set_option('display.max_columns', 500)
pd.set_option('display.width', 1000)

# Using glob library to create a list of file names using regular expression.
datafiles= glob.glob("C:/Users/OSCAR/Documents/Quotes\*.csv")

# Create an empty python dictionary which will contain currency pairs' data.
# Keys will be currency pair names, and values- 'pandas' data frames with 
# close prices read from the files.
dataframes = dict()
# In the following loop we'll read each file into a pandas data frame and 
# store them in the 'dataframes' dictionary.
# for each file...
for f in datafiles:
    #SACAMOS EL NOMBRE DEL FICHERO SIN LA EXTENSIÓN
    pair_name = os.path.splitext(os.path.basename(f))[0]
    
    # Using read_csv function read file into a DataFrame 'df'.
    
    print(pair_name)

    # Notice that we are reading only two columns from each file: 'date', and 'close'. 
    # We'll be using 'date' to index each record in data frame (think of it a a primary
    # key value for the record) , and the close price will be used to  calculate correlations. 
    df = pd.read_csv(f, sep=',', header=0, index_col=["Date"], usecols=["Date", "Close"])
    
       
    # Rename 'close' column the the currency pair name pair.
    # This will help us identify each pair's close price below when we join all 
    # data frames into a single fame.
    df.columns = [pair_name]
     
    # cagigas: he cambiado el precio por el retorno porcentual
    dataframes[pair_name] = df.pct_change()
     
     
# In this section we'll join all data frames create above into a single 'final_df' 
# data frame. This data frame will contain a single 'date' column, and 1 column 
# for each currency pair containing that pair's close prices.
final_df = None
for k,v in dataframes.items():
    if (final_df is None):
        final_df = v
    else:
        # Panda's join operation is similar to an SQL join. In this case
        # we are using a 'left' join.
        #http://pandas.pydata.org/pandas-docs/stable/merging.html
        final_df = final_df.join(v, how='left')
         
print("--------------- FINAL DATA FRAME ---------------")
print(final_df.tail(10))

# And now.. the "hard" part- calculating correlations between pairs.
# DataFrames corr() function calculates pairwise correlations using specified 
# algorithm: 'peason, 'kendall', and 'spearman' are supported.
# Correlations are returned in a new DataFrame instance (corr_df below).
corr_df = final_df.corr(method='pearson')
print("--------------- CORRELATIONS ---------------")
print(corr_df.head(len(dataframes)))

print("--------------- CREATE A HEATMAP ---------------")
# Create a mask to display only the lower triangle of the matrix (since it's mirrored around its 
# top-left to bottom-right diagonal).
mask = np.zeros_like(corr_df)
mask[np.triu_indices_from(mask)] = True
# Create the heatmap using seaborn library. 
# List if colormaps (parameter 'cmap') is available here: http://matplotlib.org/examples/color/colormaps_reference.html
seaborn.heatmap(corr_df, cmap='RdYlGn_r', vmax=1.0, vmin=-1.0 , mask = mask, linewidths=2.5)

# Show the plot we reorient the labels for each column and row to make them easier to read.
plt.yticks(rotation=0) 
plt.xticks(rotation=90) 
plt.show()
--------------------------------------------------------------------------------------------------------------

El resultado es la primera imagen, un mapa de calor (heatmap) con las correlaciones cruzadas por colores. Si queremos los datos vamos al visor de Python y podemos copiarlo para exportarlo a Excel o según nos interese.