Tabla de amortización de préstamos con Power Query

Hace varios días que venía dándole vueltas a la idea de mostrar cómo crear una tabla de amortización de préstamos, como el modelo que publiqué en el pasado,  pero usando Power Query en lugar de las funciones de Excel.
A diferencia de los ejemplos anteriores de Power Query que he mostrado en este blog, en este caso no partimos de una fuente de datos, de la hoja o remota, que cargamos a una consulta en forma de tabla. De hecho, tenemos que crear la tabla de amortización "de la nada". Es decir, una vez definidos los parámetros iniciales (monto del préstamo, tasa de interés, número de cuotas) tenemos que crear una tabla en Power Query que luego cargaremos a una hoja de Excel.
Para crear la tabla "de la nada" tendremos que usar funciones del lenguaje M (el lenguaje del Power Query). En este post no entraré en los detalles técnicos de M (objetos, funciones, etc.) y me limitaré a mostrar cómo fue construido el modelo.
Investigando las distintas funciones de Power Query llegué rápidamente a la conclusión que la función indicada era List.Generate. Lamentablemente la ayuda en la hoja de funciones de Microsoft es oscura (por lo menos para el usuario promedio). En mi ayuda vino este  post publicado por Andrew Todd en PowerPivot(Pro).

Y ahora, manos a la obra! Empezamos por definir los tres parámetros iniciales: monto del préstamo (Capital), tasa de interés anual (Interes) y el número de pagos o cuotas (Pagos). Los ingresamos en celdas de una hoja y creamos nombres definidos que se refieran a esas celdas. La forma más rapida de hacerlo es seleccionando el rango y usando Fórmulas-Nombres definidos-Crear desde la selección:

Ahora creamos una consulta para cada parámetro. Como hemos creado nombres definido Power Query no carga el rango A1:B3 a la consulta, sino sólo la celda del nombre definido

Power Query cambia el tipo de datos s Entero automáticamente. Con un clic del botón derecho abrimos el menú contextual y usamos "Rastrear desagrupando datos" (Drill down)

Vamos a Inicio, cerramos y cargamos como "sólo conexión"
Hacemos lo mismo con los otros dos parámetros

Para crear la tabla de amortización empezamos por crear una consulta en blanco (Desde otras fuentes-Consulta en blanco). En la venta del editor pegamos este código, que paso a explicar

 let  
 P=Capital,  
 i=Interes,  
 n=Pagos,  

 Mes=Table.ExpandRecordColumn(  
     Table.FromList(  
           List.Generate(  
           ()=>[Counter=1],  
           each [Counter]<=n,  
           each [Counter=[Counter]+1]  
           ),  
     Splitter.SplitByNothing(), {"Balance Inicial"},   
    null, ExtraValues.Error),"Balance Inicial", {"Counter"}, {"Mes"}),  

   BalanceInicial = Table.AddColumn(Mes, "Balance Inicial",   
     each P*(Number.Power(1+(i/12),n)-Number.Power(1+(i/12),[Mes]-1))/(Number.Power(1+(i/12),n)-1),   
     type number),  

   PagoMensual = Table.AddColumn(BalanceInicial, "Pago Mensual",   
     each P*((i/12)*Number.Power(1+(i/12),n))/(Number.Power(1+(i/12),n)-1), type number),  

   InteresMensual=Table.AddColumn(PagoMensual , "Interes Mensual",   
     each (i/12)*[Balance Inicial], type number),  

   CapitalMensual = Table.AddColumn(InteresMensual, "Capital Mensual",   
     each [Pago Mensual]-[Interes Mensual], type number),  

   BalanceFinal = Table.AddColumn(CapitalMensual, "Balance Final",   
     each [Balance Inicial]-[Capital Mensual], type number)  
 in  
   BalanceFinal  

Las primeras tres líneas crean las variables que reciben el valor de las consultas de los parámetros. Luego creamos la columna "Mes" (el número de cuota) para lo cual anidamos tres funciones de M; de adentro hacia afuera:

• List.Genetate: crea una lista (columna) con los números de mes; empieza con [Counter] = 1 y va agregando 1 hasta que el valor sea igual a n (Pagos);
• Table.FromList: convierta la lista que acabamos de generar en Tabla de manera que podamos usarla en Power Query;
• Table.ExpandRecordColumn: expandimos la tabla

A esta altura de los acontecimientos nuestra consulta ser verá así

En Power Query no disponemos de las funciones para calcular el pago fijo, el interés y la parte del capital en cada pago como tenemos en Excel (PAGO, PAGO.INT, PAGOPRIN), así que tendremos que hacer los cálculos con operadores. Por ejemplo, para calcular el balance inicial de cada período usamos

P*(Number.Power(1+(i/12),n)-Number.Power(1+(i/12),[Mes]-1))/(Number.Power(1+(i/12),n)-1)

Para crear la columna podemos usar el menú en la interfaz del usuario (Agregar Columna-Columna Personalizada) o pegar el código en el editor. Lo mismo para las demás columnas.  Al final de proceso tenemos la tabla 

La descargamos en la hoja donde pusimos los parámetros y nuestro modelo está listo 

Este modelo es absolutamente dinámico. Todo lo que hay que hacer es cambiar los parámetros y apretar Datos-Actualizar todo como puede verse en este video

El archivo del ejemplo puede descargarse aquí.

Crear tablas dinámicas con datos externos

No sé cuántos de mis lectores usan o conocen la opción "Utilice una fuente de datos externa" para crear tablas dinámicas

Cuando no existe la necesidad de hacer transformaciones a los datos originales, esta opción es muy conveniente (cuando necesitamos transformar datos Power Query es la herramienta más apropiada).
La idea del post surgió a partir de la consulta de un colega en el trabajo. Su problema: tenía que construir un reporte con una tabla dinámica, pero la tabla con los datos de origen tenía varios cientos de miles de filas, número que iba incrementándose cada mes. A corto plazo la base de datos superaría el millón de filas, más allá de la capacidad de una hoja de Excel.
Inmediatamente comprendí que mi colega importaba los registros (filas) de la base de datos a una hoja de Excel, lo cual es innecesario.
Las ventajas de crear una conexión sin importar físicamente los datos a una hoja de Excel son evidentes:

• evitamos duplicar los datos (también en a base de datos y también en la hoja);
• nuestro archivo será mucha más liviano;
• cuando hay cambios en la base de datos (nuevos registros, por ejemplo) basta con apretar el botón Actualizar para refrescar los datos;
• podemos desconectar las tablas dinámicas creadas del origen y así proteger datos discretos. Sobre este tema pondré un ejemplo al final del post.

Para crear una tabla dinámica con esta técnica empezamos con una hoja vacía. Abrimos el menú Insertar-Tabla dinámica-Utilice una fuente..." y apretamos el botón "Elegir conexión".

Si la conexión que buscamos no aparece en la lista de conexiones disponibles, usamos el botón "Examinar en busca de más" (en la parte inferior izquierda del formulario). En este ejemplo voy a crear una conexión a la base de datos Northwind que puede estar en mi máquina, en la red a la cual estoy conectado o en la Web. Al apretar el botón aceptar veremos l plantilla de la tabla dinámica vacía y la lista de campos que podemos utilizar para crear el reporte dinámico

A partir de aquí podemos crear nuestros reportes sin que los datos de base estén físicamente en la hoja. Por ejemplo, este reporte de ventas donde usamos el campe de Filtro para filtrar los informes por vendedor

Una de las ventajas, como señalamos, es que nuestro archivo será más liviano. El archivo de este ejemplo peso 132 KB. Si hubiéramos importado los datos a una hoja del cuaderno pesaría 427 KB, tres veces más.

La otra ventaja de este método surge cuando trabajamos con datos que requieren discreción. Siguiendo con nuestro ejemplo supongamos que generamos un reporte para cada agente de ventas (que hacemos con facilidad con la técnica que muestro en este post) pero no queremos que puedan ver los resultados de los otros colegas.

Una posibilidad es copiar y pegar los datos en otra hoja con la opción "sólo valores". Pero esta técnica tiene dos inconvenientes:

• es trabajosa si se trata de muchas hojas (agentes en nuestro ejemplo);
• al pegar los datos los formatos de la tabla desaparecen (podemos usar el tip que muestro en este post, pero también es trabajoso).

Si creamos los reportes dinámicos en base a una tabla dinámica "conectada", todo lo que tenemos que hacer es cancelar al conexión

Excel transforma la tabla dinámica en una matriz de valores, sin fórmulas, sin conexiones y manteniendo los formatos. Veamos el proceso en este video

Reducción de valores mostrados en lista desplegable con criterio de búsqueda

Como ya hemos mostrado en este blog, hay muchas formas de crear listas desplegables en Excel:

• con Validación de Datos, Lista;
• incrustando en la hoja un cuadro combinado (combobox) o un cuadro de lista (listbox) de la colección de formularios;
• programando un cuadro combinado o un cuadro de lista en un Userform en el editor de Vba (macros).

Todas las técnicas pueden verse en mi e-book "Listas Desplegables - la guía JLD".

Uno de los requerimientos de los usuarios es la posibilidad de reducir los valores que aparecen en la lista de acuerdo a algún criterio. Supongamos que tenemos una lista de varios cientos de productos y queremos ver sólo aquellos que contengan la palabra "aceite" antes de seleccionar los valores deseados.

En este post voy a mostrar cómo hacerlo usando un ListBox para crear la lista desplegable y un cuadro de texto para introducir el texto del criterio de búsqueda. Todo ésto lo armamos en un Userform y, por supuesto, usaremos código de Vb para activar el modelo.

Esta captura de pantalla  muestra como funciona nuestro modelo. Cada vez que ingresamos un valor en la casilla de textos, la lista desplegable se reduce a los valores que contienen ese texto

El origen de los datos de la lista desplegable es una lista de precios que se encuentra en una tabla que llamaremos "tbl_Productos"

No me voy a extender aquí sobre las bondades de usar Tablas para organizar nuestros datos en las hojas de Excel, tema que he tocado varias veces en este blog.

Creamos un Userform con un cuadro de lista (Listbox), un cuadro de texto (Textbox) y dos botones de comando

Cuando incrustamos el cuadro de lista (Listbox) en el formulario, definimos ciertas propiedades en la ventana de propiedades

Como puede apreciarse RowSource (la fuente de los datos de la lista) se refiere directamente a la tabla con su nombre.
Para facilitar el código que mostramos más adelante, creamos también un nombre definido que se refiere a la tabla

Los códigos detrás de los objetos (que van en el módulo del Userform) son los siguientes:

# - un evento Change para el ListBox. Este evento se dispara cada vez que tecleamos algún valor en la casilla de texto; cuando esta vacía vemos todos los valores de la lista de precios

 Private Sub tboxCriterio_Change()  
   Dim v As Variant, i As Long  
   v = Range("lstProductos").Value  
   With Me.lbxProductos  
   If Len(Me.tboxCriterio.Value) = 0 Then  
     .RowSource = "lstProductos"  
   Else  
     .RowSource = ""  
     For i = LBound(v, 1) To UBound(v, 1)  
       If LCase(v(i, 1)) Like "*" & LCase(tboxCriterio.Value) & "*" Then  
         .AddItem v(i, 1)  
         .List(.ListCount - 1, 1) = v(i, 2)  
       End If  
     Next i  
   End If  
   End With  
 End Sub  

# - un evento Click para el botón Cancelar

 Private Sub cbtCancelar_Click()  
   Unload ufProductos  
 End Sub  

Si estuviéramos usando este formulario en un modelo real tendríamos que escribir código para el botón Aceptar. Aquí estamos mostrando solamente cómo crear la lista desplegable así que dejaremos ese código para algún post en el futuro (en caso que algunos de mis lectores quieran ver como aplicar esta técnica a un ejemplo).

Para activar el Userform usamos el botón "Lista de productos" al cua tiene asociado este código

 Sub listaProductos()  
   ufProductos.Show  
 End Sub