He mencionado en algunas ocasiones que hace algunos meses empecé a trabajar para una pequeña firma financiera en el sureste de los Estados Unidos. Siempre que menciono esto de forma casual con personas que me preguntan acerca de mi situación laboral, rápidamente surge la pregunta acerca de nuestra relación con los mercados financieros. Después de todo es algo natural, no deja de llamar la atención que alguien tenga relación con ese misterioso mundo del dinero que a nosotros los latinos nos parece algo tan lejano y distanciado de nuestra realidad. Y antes de continuar, respondo la pregunta que inevitablemente surgirá: No, no somos corredores de bolsa, no tenemos relación directa con nada relacionado a la compra y venta de acciones. Si bien los pasados meses he estado en modo de estudio intensivo para conocer la forma en que funciona dicho mercado, es para fines de análisis y otro tipo de actividades indirectas que dependen del comportamiento del mercado en vez de participar activamente en él.

Un detalle que me llamó la atención de la industria financiera es la cantidad ridícula de información que se genera cada minuto. Literalmente se generan cantidades absurdas de información cada segundo derivado de las miles de transacciones de compra y venta que se generan en todos los distintos mercados alrededor del mundo. El mercado más famoso y al que todo mundo asocia automáticamente con los mercados financieros es el New York Stock Exchange (NYSE), sin embargo existen varios otros que también generan su propia cantidad de información.

Es acá cuando empieza a tener sentido que alguien con un trasfondo de tecnología y desarrollo de software se meta de lleno a explorar y conocer este peculiar ecosistema. Mi primer contacto con un sistema dedicado a la información financiera fue Morningstar Office, una suite de información, análisis y reportería que lleva muchos años en el mercado y es de los más confiables.

Morningstar Office pone una cantidad absurda de información al alcance de cualquiera con los recursos suficientes para costearse una suscripción…

Y si bien Morningstar ofrece una robusta suite de reportes, no deja de ser una aplicación monolítica en un mundo donde todo se mueve a velocidad relámpago y las necesidades han ido evolucionando a tal punto que se espera que la información esté disponible con un par de clicks y accesible desde cualquier parte del globo. Morningstar cumple su función de forma adecuada pero salvo que se tenga una instalación adecuada y una computadora disponible en todo momento, acceder a su información puede resultar inconveniente en el mundo actual. Nuestra firma depende en gran medida de este software y lo seguirá haciendo por mucho tiempo, sin embargo una de las necesidades puntuales que estoy abordando es tener mayor flexibilidad en cuanto a tener un dataset propio con información histórica del mercado financiero para fines de análisis y minería de datos.

Este es el tipo de problemas que usualmente nos produce un cosquilleo en la palma de las manos a los informáticos. Rápidamente empezamos a analizar y jugar con la idea de una estructura de datos que pueda almacenar volúmenes importantes de información y todas las ramificaciones que esto conlleva. Pasé varios días analizando diferentes escenarios y cuando finalmente creí estar listo para empezar a darle forma encontré el primer obstáculo: ¿cómo cargar un dataset histórico gigantesco a una base de datos? En mi afán por responder esta pregunta rápidamente entendí que la pregunta iba precedida por otra aún más importante: ¿dónde conseguir un dataset histórico confiable sin que cueste un ojo de la cara?

Una de las realidades que no es aparente desde un principio es que en el mundo financiero todo es negocio. Esto incluye también la información que genera el mismo mundo financiero. Existen varias decenas de APIs que ofrecen en tiempo real toda la información que se genera día con día en todos los mercados mundiales. No son opciones baratas y para fines prácticos tienen un límite diario de consultas. Por tratarse de un proyecto de research & development no cuento con un presupuesto dedicado para costear este tipo de servicios, por lo que tuve que recurrir a una opción menos elegante: encontrar un set de datos lo suficientemente completo en formato Excel o CSV para luego intentar cargar su contenido a una estructura relacional de base de datos para su posterior consumo.

Luego de varias horas de búsqueda encontré finalmente un set de datos bastante completo con información de precios diarios desde 1970 hasta 2018. Una vez cargada toda esta información tendré que cerrar el gap con toda la información del 2019 pero es un buen comienzo.

La información clave es que tengamos los precios de apertura y cierre de cada día para un símbolo (ticker) específico a lo largo de una línea de tiempo lo suficientemente larga. Este dataset es gigantesco, con cerca de 25 millones de tuplas.

Una vez contamos con un dataset adecuado el siguiente paso lógico es empezar a pensar en una estructura de base de datos relacional para contenerlo. Si bien puede residir en un archivo de Excel o un CSV, no es práctico para estos volúmenes de información y como veremos más adelante, deseamos poder extender su uso mediante otro tipo de aplicaciones, para lo cual una base de datos relacional es el escenario óptimo.

Teniendo el histórico de precios y combinándolo con un catálogo de más de 20,000 acciones comunes del mercado financiero de los Estados Unidos (mercados extranjeros será tarea para otro día) proveniente de Morningstar me di a la tarea de entonces finalmente crear una estructura de base de datos adecuada. Empecé por una tabla maestra de países, puesto que si bien de momento estoy abarcando únicamente acciones que se cotizan en los mercados americanos, dichas acciones pueden y usualmente pertenecen a negocios fuera de los Estados Unidos.

CREATE TABLE `country` (
	`id`            INT(11) AUTO_INCREMENT NOT NULL,
	`name`          VARCHAR(100) NOT NULL,
	`short`         VARCHAR(2) NOT NULL,
	
	PRIMARY KEY (`id`),
	UNIQUE KEY `country_unique` (`id`)
)ENGINE=InnoDB;

Dejo algunos inserts de ejemplo:

INSERT INTO `country` (`name`, `short`) VALUES ('Japan', 'JP');
INSERT INTO `country` (`name`, `short`) VALUES ('United States', 'US');
INSERT INTO `country` (`name`, `short`) VALUES ('United Kingdom', 'GB');

A continuación creé una tabla maestra de mercados financieros:

CREATE TABLE `exchange` (
	`id`            INT(11) AUTO_INCREMENT NOT NULL,
	`country`       INT(11) NOT NULL,
	`name`          VARCHAR(100) NOT NULL,
	`short`         VARCHAR(20) NOT NULL,
	`active`        TINYINT(1) DEFAULT 1,
	`created`       TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	
	PRIMARY KEY (`id`),
	FOREIGN KEY (`country`)
		REFERENCES `country` (`id`)
		ON UPDATE CASCADE ON DELETE RESTRICT,
	UNIQUE KEY `exchange_unique` (`id`)
)ENGINE=InnoDB;

INSERT INTO `exchange` (`name`, `short`, `country`) VALUES ('BATS Z-Exchange', 'BATS Z', 236);
INSERT INTO `exchange` (`name`, `short`, `country`) VALUES ('Nasdaq', 'Nasdaq', 236);
INSERT INTO `exchange` (`name`, `short`, `country`) VALUES ('New York Stock Exchange, Inc.', 'NYSE', 236);
INSERT INTO `exchange` (`name`, `short`, `country`) VALUES ('NYSE Arca', 'NYSE Arca', 236);
INSERT INTO `exchange` (`name`, `short`, `country`) VALUES ('NYSE Mkt LLC', 'NYSE Mkt LLC', 236);
INSERT INTO `exchange` (`name`, `short`, `country`) VALUES ('OTC Grey Market', 'OTC Grey Market', 236);
INSERT INTO `exchange` (`name`, `short`, `country`) VALUES ('OTC Markets', 'OTC Markets', 236);
INSERT INTO `exchange` (`name`, `short`, `country`) VALUES ('OTCBB', 'OTCBB', 236);

Luego creé un catálogo maestro con industrias y sectores posibles a asociar a una acción común. Esto será necesario posteriormente para hacer análisis basados en comportamientos y patrones por industria y / o sectores:

CREATE TABLE `industry` (
	`id`            INT(11) AUTO_INCREMENT NOT NULL,
	`name`	        VARCHAR(200) NOT NULL,
	`short`         VARCHAR(50) NULL,
	`active`        TINYINT(1) DEFAULT 1,
	`created`       TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	
	PRIMARY KEY (`id`),
	UNIQUE KEY `industry_unique` (`id`)
)ENGINE=InnoDB;
CREATE TABLE `sector` (
	`id`            INT(11) AUTO_INCREMENT NOT NULL,
	`name`          VARCHAR(200) NOT NULL,
	`active`        TINYINT(1) DEFAULT 1,
	`created`       TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	
	PRIMARY KEY (`id`),
	UNIQUE KEY `sector_unique` (`id`)
)ENGINE=InnoDB;

Adicionalmente es necesario un catálogo maestro de monedas:

CREATE TABLE `currency` (
	`id`            INT(11) AUTO_INCREMENT NOT NULL,
	`name`          VARCHAR(100) NOT NULL,
	`short`         VARCHAR(5) NOT NULL,
	`symbol`        VARCHAR(3) NOT NULL,
	`active`        TINYINT(1) DEFAULT 1,
	`created`       TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	
	PRIMARY KEY (`id`),
	UNIQUE KEY `currency_unique` (`id`)
)ENGINE=InnoDB;

Todo lo anterior, salvo industrias y sectores, es información genérica que se puede llenar de cualquier manera y se puede ser tan específico como se desee. Sin embargo acá es donde la información empieza a ser escasa y se requiere de recursos para obtenerla. Si bien me encantaría compartir estos catálogos de forma íntegra, es información privilegiada que pertenece a mi empresa y no puedo diseminarla de forma libre. Sin embargo comparto las estructuras para que quien desee seguir mis pasos tenga una idea del concepto general.

Creamos también un catálogo maestro de compañías. De momento nuestro interés por este catálogo es sencillamente hacer una asociación entre una acción y la compañía a la que pertenece.

CREATE TABLE `company` (
	`id`            INT(11) AUTO_INCREMENT NOT NULL,
	`name`          VARCHAR(500) NOT NULL,
	`short`	       VARCHAR(200) NULL,
	`active`        TINYINT(1) DEFAULT 1,
	`created`       TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	
	PRIMARY KEY (`id`),
	UNIQUE KEY `company_unique` (`id`)
)ENGINE=InnoDB;

Y como última parada antes de definir finalmente nuestro catálogo de acciones, es necesario definir un catálogo con tipo de acciones. Esto va más encaminado a describir el comportamiento de la acción, recordemos que tenemos catálogos con industrias y sectores para análisis de segmentación.

CREATE TABLE `stock_type` (
	`id`            INT(11) AUTO_INCREMENT NOT NULL,
	`name`          VARCHAR(100) NOT NULL,
	`active`        TINYINT(1) DEFAULT 1,
	`created`       TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	
	PRIMARY KEY (`id`),
	UNIQUE KEY `stock_type_unique` (`id`)
)ENGINE=InnoDB;

INSERT INTO `stock_type` (`name`) VALUES ('Aggressive Growth');
INSERT INTO `stock_type` (`name`) VALUES ('Classic Growth');
INSERT INTO `stock_type` (`name`) VALUES ('Cyclical');
INSERT INTO `stock_type` (`name`) VALUES ('Distressed');
INSERT INTO `stock_type` (`name`) VALUES ('Hard Assets');
INSERT INTO `stock_type` (`name`) VALUES ('High Yield');
INSERT INTO `stock_type` (`name`) VALUES ('Slow Growth');
INSERT INTO `stock_type` (`name`) VALUES ('Speculative Growth');

Finalmente creamos la tabla maestra de acciones con todas las relaciones a las tablas anteriores:

CREATE TABLE `stock` (
	`id`                    INT(11) AUTO_INCREMENT NOT NULL,
	`exchange`              INT(11) NOT NULL,
	`business_country`      INT(11) NOT NULL,
	`company`               INT(11) NOT NULL,
	`industry`              INT(11) NOT NULL,
	`sector`                INT(11) NOT NULL,
	`currency`              INT(11) NULL,
	`type`                  INT(11) NULL,
	`name`                  VARCHAR(200) NOT NULL,
	`ticker`                VARCHAR(10) NOT NULL,
	`return_date`           DATE NULL,
	`last_close`            DECIMAL(15, 3) NULL,
	`market_cap`            DECIMAL(15, 3) NULL,
	`earnings_yield`        DECIMAL(15, 3) NULL,
	`pe_ratio`              DECIMAL(15, 3) NULL,
	`latest_dividend_date`	DATE NULL,
	`latest_dividend_usd`   DECIMAL(15, 3) NULL,
	`dividend_yield`        DECIMAL(15, 3) NULL,
	`current_return`        DECIMAL(15, 3) NULL,
	`ipo_date`              DATE NULL,
	
	PRIMARY KEY (`id`),
	FOREIGN KEY (`exchange`)
	        REFERENCES `exchange` (`id`)
	        ON UPDATE CASCADE ON DELETE RESTRICT,
	FOREIGN KEY (`business_country`)
	        REFERENCES `country` (`id`)
	        ON UPDATE CASCADE ON DELETE RESTRICT,
	FOREIGN KEY (`company`)
	        REFERENCES `company` (`id`)
	        ON UPDATE CASCADE ON DELETE RESTRICT,
	FOREIGN KEY (`industry`)
	        REFERENCES `industry` (`id`)
	        ON UPDATE CASCADE ON DELETE RESTRICT,
	FOREIGN KEY (`sector`)
	        REFERENCES `sector` (`id`)
	        ON UPDATE CASCADE ON DELETE RESTRICT,
	FOREIGN KEY (`currency`)
                REFERENCES `currency` (`id`)
	        ON UPDATE CASCADE ON DELETE RESTRICT,
	FOREIGN KEY (`type`)
	        REFERENCES `stock_type` (`id`)
	        ON UPDATE CASCADE ON DELETE RESTRICT,
	UNIQUE KEY `stock_unique` (`id`)
)ENGINE=InnoDB;

Acá no hemos hecho más que describir el escenario. Nos falta lo más importante que es cargar toda la información a nuestras tablas de acciones y crear quizás el elemento más importante: el histórico de precios para cada acción en el maestro de acciones.

Hasta acá esta primera parte de este interesante tema. En la siguiente parte veremos el tedioso proceso de carga que tuve que idear para semejante cantidad de datos.