La finalidad de este artículo es mostrar el motivo por el que resulta más práctico para este fin la utilización de PowerQuery de MS Excel en lugar de consultas dento de una base de datos.
- Por las características de las aplicaciones Zoho Books y A3ECO no podemos acceder a la base de datos en el caso de la primera y decir que no existe tal cosa en la segunda.
- De lo comentado en el primer punto se deduce que trabajaremos con ficheros exportados en Excel con los cuales se puede conectar de forma directa, sin necesidad de importar, con aplicaciones como
- La decisión final por MS Excel se ha debido a la potencia y flexibilidad que me permite para trabajar los datos del propio libro de excel como tablas mediante rangos de datos nombrados.
El esquema de datos de la contabilidad de Zoho Books ya lo teníamos comentado en la entrada Esquema contable de Zoho Books para importar contabilidad externa
Y ahora vayamos con la explicación de la problemática resuelta.
Casos «sencillos»
Normalmente nos encontramos con casos de importaciones parciales que, por las revisiones posteriores que requieren, podrían llamarse sencillas:
- facturas de venta
- facturas de compra
- movimientos bancarios
- plan de cuentas
- fichas de clientes y proveedores
En el caso de querer importar una contabilidad la única manera de evitar problemas es hacer una importación completa sobre una contabilidad previamente en blanco.
Una segunda posibilidad es realizar importaciones incrementales, por periodos a lo largo del tiempo con el riesgo que se tiene de que haya habido cambios en la contabilidad de los periodos anteriores ya contabilizados, de manera que hay que establecer un sistema de control de cambios: El más sencillo sería
- Ver qué falta o qué sobra en la contabilidad actual del periodo anterior respecto a lo que se importó previamente
- Comparar la importación que se hizo con la que resultaría en el momento «después».
Caso complejo
La complicación mayor se tiene cuando se pide sincronizar dos contabilidades ya existentes
Este es el caso que estamos analizando en esta entrada y sobre el que se podría establecer el sisguiente esquema de trabajo:
- Comparar el libro de facturas emitidas y recibidas
- Que no falta ninguna factura contabilizada
- Que las bases y tipos de impuestos coincidan
- Revisar que se han interpretado correctamente los datos con los modelos de impuestos presentados
- Comprar los diarios de movimientos contables en búsqueda de diferencias
- Podemos encontrar que en lo ya contabilizado se han usado cuentas de ingreso / gasto diferentes
- Evaluar si cambiar o no cambiar y editar el fichero para que no vuelva a aparecer como diferencia
- Diferencias por asientos de inmovilizado / amortizaciones
- Diferencias por deudas / gastos financieros
- Diferecnias por tesorería / comisiones e intereses
- Diferencias por ausencia de asientos de liquidación de impuestos
- Diferencias por tesorería en cobros, pagos de lo anteriormente expuesto (a excepción de las amortizaciones)
- Podemos encontrar que en lo ya contabilizado se han usado cuentas de ingreso / gasto diferentes
¿Por qué usar PowerQuery con MS Excel?
Estamos consultando datos de archivos Excel tanto de la exportación de Zoho Books, a la que asignando cuentas contables según cliente / proveedor se puede comparar con la contabilidad en A3ECO exportada a Excel (hubiera sido preferible en fichero MDB de MS Access, pero para el caso es lo mismo)
No se necesita, por tanto, perder tiempo en importar los ficheros a una BBDD si luego, por lo que sea, tenemos que rectificar algo (por ejemplo contrapartidas o cuentas asignadas) y queremos que tenga efecto inmediato (con sólo refrescar la consulta) y además necesitamos guardar copia de ese cambio de criterio precisamente en el fichero que estamos usando (y no que quede en una BBDD)
Problema resuelto
Observamos que tenemos que importar cantidad de asientos de cantidad de cuentas diferentes. Lo confirmamos en los extractos de dichas cuentas. Lo importante será, por tanto, tener identificados los asientos a importar.
- Desde consultas SQL se puede hacer una consulta unión de todos los registros de una tabla (la contabilidad externa) y coincidentes de la contabilidad actual. Estableciendo un valor a la coincidencia puedo filtrar los no coincidentes como aquellos que no tienen valor de coincidencia.
- Lo mismo puede hacerse con Power Query.
La diferencia es que necesitamos SEPARAR los no coincidentes según la cuenta y convertir el resultado en el fichero importable en ZOHO BOOKS
- no queremos hacer múltiples consultas según cuenta con sus múltiples resultados.
- tampoco queremos importar todas las cuentas de golpe pues por lo delicado del proceso queremos verificar a medida que se hacen cambios.
De manera que con PowerQuery obteníamos desde la consulta TOTAL de no coincidentes los asientos para una cuenta y lo convertíamos en el filtro de la consulta de asientos a importar. Ese filtro es un rango nombrado dentro del propio libro de Excel, de manera que se podía cambiar / actualizar de forma dinámica cada vez.
De esa manera se pudo resolver satisfactoriamente el problema de poder realizar una importación para cada cuenta contable (y posteriormente poder ir comprobando los resultados) sin perder la potencia de importar cientos de asientos cada vez y que el proceso no fuera eterno.
De esa manera se pudo resolver satisfactoriamente el problema de poder realizar una importación para cada cuenta contable (y posteriormente poder ir comprobando los resultados) sin perder la potencia de importar cientos de asientos cada vez y que el proceso no fuera eterno.
Para usar un conjunto de valores como filtro de una consulta primero hay que transformarla en forma de lista. Este truco lo encontramos por un foro casi casualmente y resulta algo muy potente por las posibilidades que da al trabajar directamente sobre el fichero de datos (con importante ahorro de tiempo de importar exportar)
EXTRA
Al haber usado como identificador de la importación el número de asiento de la contabilidad externa Zoho Books no permite usar 2 veces el mismo número. BINGO! de esta manera estoy prevenido de no duplicar asientos.
