COMO CREAR UN CONTROL DE FINANZAS PERSONALES

Aprovechando que estamos iniciando un nuevo año, quiero enseñarles a crear su propio Control de Finanzas Personales desde Google Sheets, considero que es muy importante hacer un seguimiento a nuestros ingresos y gastos de tal manera que podamos administrar mucho mejor nuestro dinero, o simplemente saber a donde van todos nuestros ingresos.

El diseño que hice para este control es básico, fácil de entender y funciona de forma dinámica o interactiva, es decir, podemos filtrar los datos por categorías, meses y/o años. También incluye un Historial de Ahorro para hacer un seguimiento a las metas que tenemos para este año.

HOJAS DE DATOS

Hacer un tutorial escrito de todo el proceso va a ser bastante aburrido y pesado para ustedes como lectores, por eso es recomendable que vean el VIDEO TUTORIAL, y aquí solo les hare un pequeño resumen de los que lleva el libro de calculo, las formulas y herramientas básicas que necesitan para diseñar su Control de Finanzas.

En nuestro libro vamos a necesitar hojas de calculo con los siguientes nombres: I/G Mes, I/G Año, Ahorros, Registro Finanzas, Registro Ahorros y Desplegables. Las fuentes, colores, tamaño de fuente y demás va a depender del gusto de cada usuario, yo solo quiero recomendar que no usen colores fuertes para no cargar el diseño final.

REGISTRO DE FINANZAS Y AHORROS

En la hoja Registro de finanzas, en las celdas de nuestra preferencia, ingresaremos los títulos Fecha, Precio, Concepto, Categoría y Tipo, esto será nuestra guía para crear el Historial de Ingresos y Gastos. Debemos crear un menú desplegable para los Conceptos, Categoría, Tipo, de tal forma que los datos se actualicen de forma automática y podamos reutilizarlos.

Para automatizar el ingreso de nuevos datos, debemos crear una Macro con lo siguiente: insertar celdas desplazando hacia abajo los datos antiguos y pegar los nuevos datos en la celdas correspondientes, a esta macro la nombraremos registro. En AppScript a esta macro le agregaremos la siguiente comprobación para evitar que ingrese datos vacíos:

var fecha = spreadsheet.getRange("C6"); var precio = spreadsheet.getRange("C7"); var concepto = spreadsheet.getRange("C8"); var categoria = spreadsheet.getRange("C9"); var tipo = spreadsheet.getRange("C10"); if(fecha.isBlank() || precio.isBlank() || concepto.isBlank() || categoria.isBlank() || tipo.isBlank()) { Browser.msgBox("ERROR: CELDA VACIA","Una de las celdas del rango C6:C10 está vacía",Browser.Buttons.OK); return; }

Una vez lista esta hoja, la vamos a duplicar y la renombramos como Registro Ahorros. Ahora, para poder usar la macro en las dos hojas, creamos en cada una un botón desde la pestaña Insertar > Dibujo, una vez creado el botón lo seleccionamos y en la opción de Asignar secuencia de comandos escribimos registro.

DASHBOARD DE INGRESOS Y GASTOS

En la hoja I/G Mes, vamos a insertar un menú desplegable para MES y AÑO, en la sección de nuestra preferencia ingresamos los siguientes textos: Ingresos, Egresos, Diferencia. A los Ingresos le asignaremos una suma con sumar.si.conjunto de la siguiente forma:

=SUMAR.SI.CONJUNTO('Registro Finanzas'!$F:$F,'Registro Finanzas'!$J:$J,$F$4,'Registro Finanzas'!$K:$K,$H$4,'Registro Finanzas'!$I:$I,"Ingreso")

Para los Egresos usamos la misma formula, solo debemos cambiar la parte de "Ingreso" por "Egreso". Para Diferencia solo debemos restar los Egresos de los Ingresos, es decir, =C8-C9. Por ultimo vamos a asignar los porcentajes y crear unos mini gráficos en celdas para tener de forma mas visible los datos.

% Ingresos: =SI(C8>C9, 100%,C8/C9) % Egresos: =SI(C9>C8, 100%,C9/C8) % Diferencia: =(C8-C9)/C8 Barra Ingresos: = SPARKLINE(C8,{"charttype","bar";"max",C9;"color1","#45818e"}) Barra Egresos: =SPARKLINE(C9,{"charttype","bar";"max",C8;"color1","#76a5af"}) Barra Diferencia: =SPARKLINE(C10,{"charttype","bar";"max",C8;"color1","#a2c4c9"})

DETALLES DE INGRESOS Y GASTOS CON QUERY

En la celda de nuestra preferencia ingresaremos el texto Gastos por Categoría, sumaremos los valores de los Egresos y los agruparemos por categoría usando QUERY, esta función o formula no ayuda a realizar consultas y obtener datos. Ejemplo:

=QUERY('Registro Finanzas'!$E:$K, "SELECT H, SUM(F) WHERE K='"&$H$4&"' AND J='"&$F$4&"' AND I='Egreso' GROUP BY H LABEL SUM(F) 'Precio'")

En otra celda ingresamos el texto Detalles de Gastos, debemos crear un Filtro con las opciones Si/No, de esta forma podemos filtrar los detalles por categoría o mostrar todos los registros del mes y año seleccionado. Para eso usaremos la siguiente formula:

=SI(F19="Si",QUERY('Registro Finanzas'!$E:$K,"SELECT E,G,F WHERE K='"&$H$4&"' AND J='"&$F$4&"' AND H='"&$G$19&"' AND I='Egreso' ORDER BY E ") ,QUERY('Registro Finanzas'!$E:$K, "SELECT E, G, H, F WHERE K ='"&$H$4&"' AND J ='"&$F$4& "' AND I = 'Egreso' ORDER BY E"))

Para los Ingresos haremos exactamente los mismo del paso anterior, a las formulas solo debemos cambiarle la parte AND I='Egreso' por AND I='Ingreso'. Opcional podemos crear unos gráfico circulares seleccionando el rango B8:C10 para obtener un detalle general del porcentaje de Ingresos y Egresos según el mes y año seleccionados.

DASHBOARD DE AHORROS

Una vez creado el Dashboard de ingresos y egresos, vamos a duplicar esa hoja y la renombramos por Ahorros. Las formulas son básicamente las mismas lo único que cambia es la dirección de la hoja y que debemos eliminar el filtro del mes por ejemplo, para sumar los ingresos de los Ahorros:

=SI($F$4="Si", SUMAR.SI.CONJUNTO('Registro Ahorros'!$F:$F,'Registro Ahorros'!$L:$L,$G$4,'Registro Ahorros'!$J:$J,"Ingreso" ),SUMAR.SI.CONJUNTO('Registro Ahorros'!$F:$F,'Registro Ahorros'!$J:$J,"Ingreso" ))

Para los Ahorros por categoría:

=SI($F$4="Si", QUERY('Registro Ahorros'!$E:$L, "SELECT I, SUM(F) WHERE L='"&$G$4&"' AND J='Egreso' GROUP BY I LABEL SUM(F) 'Precio'"),QUERY('Registro Ahorros'!$E:$L, "SELECT I, SUM(F) WHERE J='Egreso' GROUP BY I LABEL SUM(F) 'Precio'") )

Para los detalles de Ahorros:

=SI($F$4="Si", SI($F$14="Si",QUERY('Registro Ahorros'!$E:$L,"SELECT K,G,H,F WHERE L='"&$G$4&"' AND I='"&$G$14&"' AND J='Egreso' ORDER BY E ") ,QUERY('Registro Ahorros'!$E:$L, "SELECT K,G,H,I,F WHERE L ='"&$G$4&"' AND J = 'Egreso' ORDER BY E")),SI($F$14="Si",QUERY('Registro Ahorros'!$E:$L,"SELECT L,K,G,H,F WHERE I='"&$G$14&"' AND J='Egreso' ORDER BY E ") ,QUERY('Registro Ahorros'!$E:$L, "SELECT L,K,G,H,I,F WHERE J = 'Egreso' ORDER BY E")))