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

Automated
Imagen "Automated" por Mike Lawrence (CC BY)

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

La serie completa de “Automatización de la gestión financiera”

  1. Problema y solución
  2. Esquema de la solución
  3. Normalización de extractos (este artículo)