Trabajar con Datos de Forex con R y SQLite (5/5)

En esta ultima entrega de esta serie vamos a ver cómo podemos acceder a los datos almacenados en nuestras bases de datos SQLite desde R. Para ello vamos a utilizar el paquete DBI de R. DBI es un paquete que permite acceder de manera unificada a diferentes bases de datos, entre ellas Oracle, MySQL, y SQLite. Cada base de datos cuenta con su propia librería que debe ser previamente cargada en R:

library(“DBI”)

library(“RSQLite”)

A continuación abrimos una conexión con la base de datos. Para ello primero tenemos que crear un driver que nos gestione la conexión, y después abrir la conexión propiamente dicha con el fichero donde se encuentre la base de datos:

drv <- dbDriver(“SQLite”)

db <- dbConnect(drv, “eurusd.db”)

En este momento podemos enviar nuestras consultas SQL a la base de datos, como por ejemplo:

rs <- dbSendQuery(db, “SELECT MAX(high) AS High, MIN(low) as Low, date as Date
    FROM eurusd

    WHERE date > '2010-01-01' AND date < '2011-01-01'

    GROUP BY round(unix / (8 * 60))

    “)

Para recuperar los datos tenemos que utilizar la función fetch(), a la que le debemos indicar cuantas barras queremos recuperar, o -1 para recuperar la totalidad:

EURUSD <- fetch(rs, n=25)

En este momento la variable EURUSD contiene los datos listos para ser analizados.

Cuando no necesitemos recuperar más datos de la consulta, debemos liberar esta con:

dbClearResult(rs)

Y finalmente, para cerrar la conexión con la base de datos hacemos:

dbDisconnect(db)

Y con esto damos por cerrada esta serie de entradas sobre cómo trabajar con datos de Forex desde R y SQLite.

Trabajar con Datos de Forex con R y SQLite (4/5)

En la entrada anterior de este blog vimos como crear barras de cualquier longitud utilizando SQLite. Sin embargo, nos encontramos con el problema de que el tiempo de respuesta de las consultas era demasiado elevado. Esto nos puede crear ciertos problemas en operativa real para estrategias que trabajen en barras muy cortas (por ejemplo de un minuto), y sobre todo, supone un gran problema cuando queremos realizar análisis de tipo walk forward, ya que este tipo de análisis requiere de múltiples consultas.

Pero aquí es donde entra en juego las optimizaciones que permite SQLite, y sobre todo, ese misterioso atributo unix que hemos añadido a nuestra tabla.

Utilizando el Tiempo Unix

Para poder mejorar el rendimiento utilizando el atributo unix, lo primero que tenemos que hacer es crear un índice sobre esta columna:

CREATE INDEX unix_index ON eurusd (unix);

Y a continuación veamos cómo podemos consultar el máximo y el mínimo en barras de 8 minutos utilizando este nuevo campo:

SELECT MAX(high) AS High, MIN(low) as Low, date as Date
FROM eurusd

WHERE date > '2010-01-01' AND date < '2011-01-01'

GROUP BY round(unix / (8 * 60));

Y de igual manera se podrían consultar los precios de apertura y de cierre.

Consulta Final

En este momento ya tenemos todo lo que necesitamos para crear barras de cualquier longitud, para cualquier periodo de tiempo, y de una manera eficiente. La consulta final para barras de 8 minutos (8*60=480) sería:

SELECT STRFTIME('%Y-%m-%d %H %M', MIN(date)) AS Date,
    (SELECT open FROM eurusd e2
         WHERE e2.unix >= e1.unix / 480 * 480
         ORDER BY e2.unix ASC LIMIT 1) AS Open,
    MAX(high) as High,
    MIN(low) as Low,
    (SELECT close FROM eurusd e3
         WHERE e3.unix < (e1.unix / 480 + 1) * 480
         ORDER BY e3.unix DESC LIMIT 1) AS Close
FROM eurusd e1
WHERE date > '2010-01-01' AND date < '2011-01-01'
GROUP BY e1.unix / 480;

Otras optimizaciones

Estas consultas se pueden optimizar aun más, pero para ello habría que entrar a programar directamente en el API C de SQLite, un API que nos permite extender la funcionalidad del lenguaje SQL. SQLite dispone de las funciones agregadas MAX y MIN que nos permiten calcular el máximo y el mínimo alcanzado en una barra, pero no dispone de nada equivalente para calcular los precios de apertura y cierre de dicha barra. Es por ello que tenemos que realizar complejas sub-queries que nos retrasan mucho todo el proceso. Este problema se podría solucionar creando nuestras propias funciones agregadas OPEN y CLOSE utilizando el mencionado API de SQLite. Pero esto es un tema demasiado complejo, y que a menos que los lectores demuestren interés, no lo cubriré en este blog.

Trabajar con Datos de Forex con R y SQLite (3/5)

En la entrada anterior de esta serie vimos como hacer algunas consultas sobre nuestros datos de Forex. En esta entrada lo que vamos a ver es cómo hacer otras consultas más avanzadas, y más útiles.

Si por ejemplo queremos ver el máximo y el mínimo de barras de 8 minutos (sí, sí, de 8 minutos), podemos hacer:

SELECT MAX(high) AS High, MIN(low) as Low, date as Date

    FROM eurusd

    WHERE date > '2010-01-01' AND date < '2011-01-01'

    GROUP BY round(STRFTIME('%s', date) / (8 * 60));

Es decir, podemos agrupar los datos en barras de cualquier longitud, y no sólo las longitudes estándar de 5 minutos, 15 minutos, 1 hora, etc. que ofrecen la mayoría de las plataformas de trading.

Uniendo el ejemplo anterior con las consultas de apertura, máximo, mínimo y cierre de la entrada anterior de este blog, lo que podemos conseguir es agrupar nuestros datos sobre barras de cualquier longitud. Esto nos permite hacer trading en barras de longitudes no convencionales, lo que nos puede suponer una importante ventaja sobre otros traders. Además, también podemos identificar mejor el tamaño de barra óptimo para nuestras estrategias de trading, optimizándolas para cada una las longitudes de barra, desde 1 minuto, hasta por ejemplo 1440 minutos (1 día).

Por ejemplo, para ver los datos agrupados en barras de 13 minutos utilizamos:

SELECT STRFTIME('%Y-%m-%d %H %M', MIN(date)) as Date,
    MAX(CASE WHEN STRFTIME('%s', date) % (13 * 60) = 0 THEN open ELSE 0 END) AS Open,
    MAX(high) as High,
    MIN(low) as Low,
    MAX(CASE WHEN STRFTIME('%s', date) % (13 * 60) = 4 * 60 THEN close ELSE 0 END) AS Close
FROM eurusd

WHERE date > '2010-01-01' AND date < '2011-01-01'
GROUP BY STRFTIME('%s', date) / (13 * 60);

Desgraciadamente esta consulta sólo funciona si nuestros datos no tienen huecos, lo cual no siempre se cumple. Para conseguir el mismo resultado pero con datos que potencialmente pueden tener huecos, tendríamos que utilizar otra consulta algo más compleja:

SELECT STRFTIME('%Y-%m-%d %H %M', MIN(date)) AS Date,
    (SELECT open from eurusd e2
         where CAST(STRFTIME('%s', e2.date) AS INTEGER) >=
         CAST(STRFTIME('%s', e1.date) AS INTEGER) / (13 * 60) * 13 * 60
         order by e2.date asc limit 1) AS Open,
    MAX(high) as High,
    MIN(low) as Low,

    (SELECT close from eurusd e3
        where CAST(STRFTIME('%s', e3.date) AS INTEGER) <
        (CAST(STRFTIME('%s', e1.date) AS INTEGER) / (13 * 60) + 1) * 13 * 60

        order by e3.date desc limit 1) AS Close
    FROM eurusd e1

    WHERE date > '2010-01-01' AND date < '2011-01-01'
    GROUP BY CAST(STRFTIME('%s', e1.date) / (13 * 60) AS INTEGER);

Sin embargo, el lector habrá notado que el tiempo de respuesta de esta última consulta es demasiado elevado. Esto puede crear ciertos problemas en operativa real para estrategias que trabajen en barras muy cortas (de por ejemplo un minuto), y sobre todo, cuando queramos realizar análisis de tipo walk forward, que requieren de múltiples consultas.

Pero aquí es donde entra en juego las optimizaciones que permite SQLite, y sobre todo, ese misterioso atributo unix de nuestra tabla. Pero este es tema para otra entrada de blog.

Trabajar con Datos de Forex con R y SQLite (2/5)

En la primera entrada de esta serie vimos como crear una tabla para almacenar los datos históricos del cambio euro-dólar. En esta ocasión vamos a empezar a trabajar con estos datos.

Trabajar con los datos

Lo primero que vamos a hacer es darle algo de formato a la salida que nos proporciona el intérprete de sqlite para que sea algo más legible, para ello escribimos:

.echo on
.mode column
.headers on
.nullvalue NULL

Básicamente, lo que le hemos dicho a SQLite es que queremos ver los datos formateados por columnas, y con una cabecera de tabla.

Para mostrar todos los elementos contenidos en la base de datos (que recomiendo no hacer porque llevaría un rato visualizarlos, ya que son muchos) se haría:

SELECT * FROM eurusd;

Para contar el número de barras almacenadas en la base de datos escribimos:

SELECT count(*) FROM eurusd;

que por cierto, debe coincidir con el número de líneas del fichero CSV desde el que hemos importado los datos, y que se pueden contar con la orden Unix:

wc -l EURUSD.csv

Para insertar una nueva barra se haría con:

INSERT INTO eurusd (date, unix, open, high, low, close) VALUES (
'2011-11-01 00:01:00', 0, '1.1212', '1.2323', '1.3434', '1.4545'
);

Que a su vez se puede borrar con:

DELETE FROM eurusd WHERE date = '2011-11-01 00:01:00';

Finalmente, podemos consultar cualquier barra con una orden como:

SELECT * FROM eurusd WHERE date = "2001-01-05 02:26";

Consultas avanzadas

La verdad es que las consultas que hemos hecho hasta ahora no son especialmente útiles. Pero en esta sección vamos a empezar a ver la potencia de almacenar los datos Forex en una base de datos relacional.

Por ejemplo si queremos ver el máximo y el mínimo alcanzado en cada mes escribimos:

SELECT MAX(high) AS High, MIN(low) as Low, 

  STRFTIME("%Y-%m", date) as Month 

  FROM eurusd

  WHERE date > '2010-01-01' AND date < '2011-01-01'

  GROUP BY STRFTIME("%Y-%m", date);

Si queremos ver el máximo y mínimo alcanzado cada día:

SELECT MAX(high) AS High, MIN(low) as Low,
  STRFTIME("%Y-%m-%d", date) as Date
  FROM eurusd

  WHERE date > '2010-01-01' AND date < '2011-01-01'

  GROUP BY STRFTIME("%Y-%m-%d", date);

Si queremos ver el máximo y mínimo alcanzado durante un periodo cualquiera de tiempo, por ejemplo 40 minutos, hacemos:

SELECT high, low FROM eurusd WHERE date >= "2001-01-05 02:00" AND date <= "2001-01-05 02:40";

Y finalmente, si queremos ver el precio de apertura en barras diarias, haríamos:

SELECT open, date FROM eurusd WHERE date IN
  (SELECT MIN(date)

    FROM eurusd

    WHERE date > '2010-01-01' AND date < '2011-01-01'

    GROUP BY STRFTIME("%Y-%m-%d", date));

Y esto es todo para esta entrega. En la siguiente entrada de este blog veremos cómo realizar algunas consultas más avanzadas.

Trabajar con Datos de Forex con R y SQLite (1/5)

El entorno R es extremadamente rápido analizando datos siempre y cuando estos quepan en la memoria de nuestro ordenador, pero si esto no es así, el rendimiento de R se puede degradar hasta niveles inaceptables. Existen múltiples soluciones a este problema (que en algún momento analizaremos en este blog), pero para el caso del análisis de datos Forex la que a mi particularmente más me convence es utilizar una base de datos externa, como por ejemplo SQLite, en la que almacenamos la totalidad de los datos, y a continuación, desde R, los vamos consultando según los vayamos necesitando para nuestro análisis.

Iniciamos aquí una serie de entregas en la que vamos a aprender cómo se pueden analizar grandes volúmenes de datos desde R con la ayuda de SQLite. Esto nos será de gran ayuda a la hora de analizar y optimizar estrategias de trading.

SQLite es un sistema gestor de bases de datos relacionales muy popular y ampliamente utilizado (de hecho, Bloomberg es uno de sus patrocinadores). SQLite es una librería que se distribuye de manera gratuita, y cuyo código fuente está disponible para su integración en otros programas (y que evidentemente, está integrada en la plataforma Entropycs). SQLite no necesita de ningún tipo de configuración o de mantenimiento, y actualmente está siendo utilizada para gestionar bases de datos de varios gigabytes (suficiente para trabajar con barras de 1 minuto, aunque quizás se quede algo corta para trabajar con datos tick a tick).

Creando una Tabla de Datos

Lo primero que vamos a hacer es crear una tabla que permita almacenar los datos históricos, en barras de un minuto, para el símbolo EURUSD. Esta tabla almacena la fecha y hora de cada barra en formato texto (campo date), y los valores que se han dado en esa barra (apertura, máximo, mínimo y cierre) como números con decimales. También contiene el capo unix, que es la misma fecha que contiene el campo date, pero en formato tiempo de unix (que cuenta el número de segundos que han transcurrido desde el 1 de Enero de 1970). El campo unix, desde un punto de vista formal, es redundante y debería ser eliminado. Sin embargo, y como veremos más adelante, nos va a venir muy bien para mejorar el rendimiento de nuestras consultas.

Utilizando el editor de comandos de sqlite creamos un fichero de datos para el símbolo EURUSD:

sqlite3 eurusd.db

y a continuación creamos la tabla con:

CREATE TABLE eurusd (
    date TEXT NOT NULL PRIMARY KEY,

    unix INTEGER NOT NULL,
    open REAL NOT NULL,
    high REAL NOT NULL,
    low REAL NOT NULL,
    close REAL NOT NULL
);

Nuevamente, desde un punto de vista formal hubiera sido más correcto crear una única tabla para almacenar los datos de todos los símbolos, pero por cuestiones de rendimiento, hemos optado por crear un único fichero que contiene una única tabla para cada uno de los símbolos.

Importar un fichero de datos

Para los datos históricos vamos a utilizar los datos proporcionados por Forex Tester. Estos datos tienen una calidad más que suficiente para nuestros análisis. De hecho, tienen una calidad mucho mayor que los datos proporcionados por la mayoría de los brokers ;-)

Si abrimos el fichero (una vez descomprimido) vemos que el formato en el que vienen los datos de las barras es el siguiente:

<TICKER>,<DTYYYYMMDD>,<TIME>,<OPEN>,<HIGH>,<LOW>,<CLOSE>,<VOL>

EURUSD,20010102,230100,0.9507,0.9507,0.9507,0.9507,4

EURUSD,20010102,230200,0.9506,0.9506,0.9505,0.9505,4

EURUSD,20010102,230300,0.9505,0.9507,0.9505,0.9506,4

...

Este formato no es el que SQLite espera, por lo que hay que hacer un pre-procesamiento del mismo. Aquí nos encontramos con el problema de que necesitamos un editor de textos que sea lo suficientemente potente como para poder cargar este fichero de datos en memoria, y que sea lo suficientemente versátil para permitir hacer cambios complejos en el mismo. En el mundo Linux tenemos el editor vi que cumple ambas funciones (existe una versión para Windows de este editor de textos llamada Vim). Alternativamente, aquellos que tengan conocimientos de programación pueden escribir un pequeño script en Perl, Python, TCL o similar, que haga la tarea.

Así que editamos el fichero con vi, borramos la primera línea y escribimos el siguiente galimatías:

:1,$s/\w\{6},\(\d\{4}\)\(\d\{2}\)\(\d\{2}\),\(\d\{2}\)\(\d\{2}\)\(\d\{2}\),\(.*\),\d$/\1-\2-\3 \4:\5,0,\7/

Grabamos el resultado como EURUSD.cvs, y comprobamos que el fichero tiene ahora el formato deseado (el indicado por la tabla que hemos creado):

2001-01-02 23:01,0,0.9507,0.9507,0.9507,0.9507

2001-01-02 23:02,0,0.9506,0.9506,0.9505,0.9505

2001-01-02 23:03,0,0.9505,0.9507,0.9505,0.9506

A continuación abrimos SQLite y escribimos:

.mode csv
.import EURUSD.csv eurusd

Esto nos importa la totalidad de los datos de las barras en nuestra tabla. Tan sólo nos quedaría corregir el campo de tiempo en formato unix, y para ello escribimos:

UPDATE eurusd SET unix = CAST(STRFTIME('%s', date) AS INTEGER);

Y con esto ya tendríamos completamente cargados los datos en la base de datos.