Importar y Limpiar Actividad Bancaria

Si alguna vez descargó la actividad bancaria del sitio web de su banco, sabrá que debe realizar cierta limpieza antes de que los datos estén listos para usarse. Quizás el siguiente paso sea resumir la actividad bancaria con una tabla dinámica o, quizás, realizar una registro bancario. Independientemente de su objetivo final, deberá dedicar tiempo a importarlo a Excel y luego limpiarlo. Esta publicación demuestra cómo utilizar una consulta Obtener y transformar para importar la actividad bancaria descargada a Excel y prepararla para su uso.

Objetivo

Antes de llegar demasiado lejos, echemos un vistazo a nuestro objetivo. Iniciamos sesión en nuestra cuenta bancaria y exportamos la actividad para un rango de fechas. El sitio web del banco lo exporta a un archivo CSV, del cual se muestra un ejemplo a continuación.

Antes de que podamos usarlo en nuestro siguiente paso, sea cual sea, necesitaremos ordenarlo un poco. Por ejemplo, debemos eliminar las columnas C y D, debemos distinguir entre depósitos y cargos, y debemos clasificar el tipo de cargo según el texto de la descripción. Luego, sólo porque a los contadores nos gusta este tipo de cosas, lo resumiremos en una tabla dinámica para confirmar que coincide con nuestro extracto bancario. He creado un video corto y una narrativa escrita a continuación para demostrar cómo lograr todo esto.

Video

Narrativo

Repasaremos esto siguiendo los siguientes pasos:

• Importar CSV
• Limpio
• Cargar en tabla dinámica

Entremos de lleno.

Nota: Los pasos a continuación se presentan con Excel para Windows 2016. Si está utilizando una versión diferente de Excel, tenga en cuenta que es posible que las funciones presentadas no estén disponibles o que deba descargar e instalar el complemento Power Query.

Importar CSV

Primero, descarga la actividad bancaria del sitio web del banco como un archivo CSV. Luego, en un nuevo libro de Excel en blanco, haga clic en el comando Datos Desde texto/CSV en el grupo de cinta Obtener y transformar . En el cuadro de diálogo Importar datos resultante, busque el archivo CSV. Excel proporciona una vista previa de los datos, como se muestra a continuación.

Haga clic en el botón Editar para abrir el Editor de consultas, donde limpiaremos los datos.

Limpio

Los datos CSV se cargan en el Editor de consultas, como se muestra a continuación.

Dentro del Editor de consultas podemos aplicar una serie de pasos para limpiar los datos.

1) Eliminemos las columnas innecesarias, en nuestro caso, la tercera y cuarta columnas. Para hacer esto, hacemos clic en la columna a eliminar y usamos el comando Inicio Eliminar columnas .

Nota: también podemos seleccionar en grupo varias columnas manteniendo presionada la tecla Mayús o Ctrl mientras seleccionamos las columnas.

2) Definamos las etiquetas de nuestras columnas. Para hacer esto, hacemos doble clic en la etiqueta de columna existente, ingresamos la nueva etiqueta y presionamos Enter. En nuestro caso, nuestras nuevas etiquetas de columna son Fecha , Importe y Descripción .

3) Ahora podemos crear cualquier columna nueva que necesitemos. En nuestro caso, queremos una nueva columna que describa el tipo de transacción. Esto será útil cuando devolvamos los datos a Excel y los resumamos con una tabla dinámica.

Hay muchas opciones en la pestaña de la cinta Agregar columna que vale la pena explorar, pero la que buscamos ahora es el comando Agregar columna Columna condicional . Esto abre el cuadro de diálogo Agregar columna condicional , donde podemos definir las reglas.

La primera regla etiquetará las cantidades positivas como depósitos. Entonces, en el cuadro de diálogo, creamos la primera regla: si la columna Monto es mayor que el valor 0 , entonces queremos que la Salida sea Depósito . Luego hacemos clic en el botón Agregar regla y pasamos a la siguiente regla.

La segunda regla identificará las transacciones que tengan una descripción que contenga el texto BILL PAY como Cheque. Entonces, creamos la siguiente regla: si la columna Descripción contiene el valor PAGO DE FACTURA , entonces la salida es Cheque . Hacemos clic en el botón Agregar regla para crear nuestra siguiente regla.

La tercera regla identificará las transacciones que tengan una descripción que contenga el texto COMPRA AUTORIZADA como compra con tarjeta de débito. Entonces, la siguiente regla es: Si la columna Descripción contiene el valor COMPRA AUTORIZADA entonces la Salida es Tarjeta de Débito .

Si ninguna de estas condiciones es verdadera, establecemos el valor De lo contrario en Cargo .

Nota: estas reglas distinguen entre mayúsculas y minúsculas, por lo tanto, si la descripción contiene PAGO DE FACTURAS, entonces el cuadro de diálogo debe ser PAGO DE FACTURAS y no Pago de facturas o pago de facturas.

Las reglas finales se ven así:

Haga clic en Aceptar y bam… la nueva columna aparece en el editor de consultas como se muestra a continuación.

Ahora estamos listos para devolver los datos a Excel.

Cargar en tabla dinámica

Para devolver los resultados de la consulta a Excel, podemos hacer clic en el comando Inicio Cerrar y cargar en .

Para ver los resultados en una tabla de Excel, seleccionamos la opción Tabla . Ahora podemos resumir los datos con una tabla dinámica haciendo clic en cualquier celda de la tabla de resultados y haciendo clic en Insertar Tabla dinámica .

Luego estructuramos el informe de tabla dinámica como deseemos colocando los campos en el área de diseño deseada. Por ejemplo, podemos insertar el campo Monto en el área Valores y la nueva columna personalizada en el área Filas, y la tabla dinámica resultante se muestra a continuación.

Y la mejor parte es que el próximo mes simplemente podremos descargar un nuevo archivo CSV del sitio web del banco, eliminar el CSV anterior y guardar el nuevo en la misma carpeta con el mismo nombre de archivo. Luego, usa el comando Datos Actualizar todo y bam… ¡ya está! El PT ahora incluye todos los datos del nuevo archivo CSV.

Si tienes otros trucos divertidos de Obtener y Transformar, ¡compártelos publicando un comentario a continuación!

Archivo CSV de muestra: SampleFile