Automatización de la gestión financiera (III): Normalización de extractos

Hoy, en esta serie dedicada a la automatización financiera casera, hablamos de la normalización de extractos bancarios (convertir los distintos archivos de extractos bancarios a un formato común).

Como comentamos en el pasado artículo, los 2 bancos con los que opero (ING y N26) no sólo proveen de extractos en formatos distintos (xls para ING y csv para N26), sino que su estructura no tiene ni los mismos campos, ni la misma disposición, ni el mismo nombre ni el mismo formato de datos.

El objetivo de este bloque del proceso es la normalización de extractos, es decir, convertirlos a un formato común (nuestro propio formato). Así, si trabajásemos con 3, 4 o más bancos, la idea es que todos los movimientos se representen de la misma forma, independientemente del banco del que proceden para, así, poder seguir con el resto de bloques del proceso trabajando con un único formato.

Formato de movimiento de las entidades bancarias

ING

Los archivos de movimientos de ING son archivos en formato Microsoft Excel (xls) y su nombre, al descargarlos, es unknown.

Si descargas uno de estos archivos observarás que sus movimientos disponen de los siguientes campos:

F. VALOR, para la fecha valor
CATEGORÍA, para la categoría otorgada por ING, según su forma de clasificar
SUBCATEGORÍA, para el segundo nivel de categorización otorgada por ING, según su forma de clasificar
DESCRIPCIÓN, para la descripción del movimiento
COMENTARIO, donde se nos indica, de manera más o menos entendible, a qué se debe ese movimiento
IMPORTE (€), para el importe (positivo o negativo) del movimiento
SALDO (€), para el saldo de la cuenta una vez descontado o sumado el importe

Ejemplo de movimiento:

F. VALOR	CATEGORÍA	SUBCATEGORÍA	DESCRIPCIÓN	COMENTARIO	IMAGEN	IMPORTE (€)	SALDO (€)
30/11/2019	Ocio y viajes	Ocio y viajes (otros)	Pago en SMAP ORA PALMA PALMA 07		No	-1,55	548,92

Es importante que sepáis que el excel de ING necesita que le hagáis lo siguiente, una vez descargado, antes de ser procesado o, de lo contrario, el proceso no funcionará:

Eliminar el logo de ING
Eliminar las primeras filas, con excepción de la fila de encabezados de columna.

Para ello, abrís el archivo (que estará seguramente en el directorio de descargas de vuestro ordenador, efectuáis las 2 operaciones comentadas y guardáis el archivo.

N26

Los archivos de movimientos de N26 son archivos en formato texto con valores separados por comas (csv) y su nombre, al descargarlos, es n26-csv-transactions.

Si descargas uno de estos archivos observarás que sus movimientos disponen de los siguinetes campos:

Fecha, para la fecha valor
Beneficiario, donde se nos indica de manera más o menos entendible, a quién se le atribuye esa transacción, es decir, viene a ser el equivalente a Descripción en el caso de ING
Número de cuenta, usado para los movimientos entre cuentas, es la cuenta origen en caso de recibir una transferencia o de destino en caso de ordenar una transferencia
Tipo de transacción, describe la naturaleza de la transacción (un ingreso, un pago, …)
Referencia de pago, complementa al Tipo de transacción
Categoría, para la categoría otorgada por N26, según su forma de clasificar
Cantidad (EUR), para el importe (positivo o negativo) del movimiento en EUR
Tipo de divisa extrangera. en caso de que el movimiento se haya realizado en moneda exrangera (te compras algo en USA, por ejemplo), la moneda en la que se ha realziado la transacción (USD, por ejemplo)
Tipo_de_cambio, el cambio de Tipo_de_divisa_extrangera a EUR

Ejemplo de movimiento:

Fecha	Beneficiario	Número de cuenta	Tipo de transacción	Referencia de pago	Categoría	Cantidad (EUR)	Cantidad (Divisa extranjera)	Tipo de divisa extranjera	Tipo de cambio
2019-11-04	LA MUSCLESALA TIENDA		Pago con MasterCard		Salud y droguería	-120.0	-120.0	EUR	1.0

Definición de un formato propio de movimiento

Como has visto, hay campos que no aparecen en el fichero de un banco u otro, otros que pueden ser equivalentes y otros que son el mismo.

También puede ocurrir que el tipo de datos o el formato del campo en sí mismo difieran (fíjate en cómo el campo Fecha en N26 y el de Fecha valor en ING contien ambos una fecha pero expresadas de forma diferente).

Por ello necesitamos la normalización de extractos, definiendo nuestro propio formato, uno que “iguale” los formatos particulares de cada banco.

Yo he optado por los siguientes campos:

Origin: banco origen del movimiento (ING, N26, …)
Date: fecha valor del movimiento
Description: descripción del movimiento
Amount: importe (positivo o negativo) del movimiento
Subcategory: subtagegoría del movimiento. Sí, es cierto, no lo he llamado categoría, sino subcategoría. ¿A qué se debe? Muy fácil: ING dispone de una categoría y una subcategoría (donde cada subcategoría pertenece a una y sólo una categoría), mientras que N26 sólo dispone de un nivel de categorización. He decidido que tomaré la “Categoría” de N26 y la “Subcategoría” de ING para normalizar, y que ambos irán a parar a mi campo “Subcategory”.

Ejemplo de movimiento propio:

Origin	Date	Description	Amount	Subcategory
N26	04/11/2019 00:00	LA MUSCLESALA TIENDA	-120,00	Deporte

Flujo del proceso

El proceso realiza 3 acciones principales:

Coger el formato de ING y convertirlo al formato normalizado
Coger el formato de N26 y convertirlo al formato normalizado
Escupir todos los movimientos, ya normalizados, a un archivo excel

Verás que hay una rama por cada entidad bancaria, para generar los movimientos normalizados de cada una de ellas, y que luego las ramas convergen a la hora de escupir los movimientos ya normalizados.

Si observas desde Pentaho Data Integration el archivo .ktr adjunto verás que, dependiendo de la entidad bancaria, conseguir un formato normalizado para esa entidad no es necesariamente igual que para otra, es decir, cada entidad bancaria se normaliza de forma distinta. Esto es algo que deberás tener en cuenta si quieres añadir un nuevo actor al proceso (Bankinter, BBVA, …).

El proceso sirve para incorporar tantos archivos de movimientos de una misma entidad bancaria como sean necesarios, gracias al uso de expresiones regulares en el nodo de entrada de datos de tipo Microsoft Excel Input.

Va a ser necesario que modifiques los nodos de tipo Microsoft Excel input, para que apunten al directorio de descargas de tu ordenador (o donde tengas los extractados descargados.

Igualmente deberás modificar el nodos de tipo Microsoft Excel output, para que apunten al directorio de descargas de tu ordenador (o cualquier otro directorio de tu elección).

Añadiendo un nuevo actor

Imaginemos que en tu automatización de la gestión financiera necesitamos añadir una nueva entidad. ¿Qué deberíamos hacer sobre el proceso de normalización de extractos?

Como hemos dicho, hay una rama por cada entidad bancaria a efectos de poder realziar la normalización ex profeso de dicha entidad. Si añadimos una nueva entidad bancaria al proceso, deberemos generar una nueva rama, con su nodo de tipo Microsoft Excel input y las transformaciones que el formato de esa entidad requiera para ser normalizado. Finalmente haremos converger esa nueva rama con el resto en el nodo de tipo Sorted merge.

Próximos pasos

En futuros artículos os explicaré, con detalle, (y os haré llegar los archivos para su descarga) los 2 bloques que quedan para completar el proceso.

Os recomiendo que juguéis con el proceso, que tratéis de entender el funcionamiento de los nodos de entrada y salida de datos con excel (hay muchos recursos en Internet). A partir de ahí podéis falimiliarizaros con el resto de nodos que se usan en la transformación.

Aquí tienes el enlace de descarga al archivo ktr