jueves, 12 de mayo de 2022

Calcular fecha futura de cobro

Seguramente habrás tenido que calcular en muchas ocasiones, para realizar previsiones de tesorería, cuál es la fecha de cobro de cierto cliente, con el que firmaste unas condiciones fijas del estilo: el día diez del mes correspondiente a 60 días después de la fecha de factura.

Siempre me ha llamado la atención, en mis años en departamentos administrativos contables, lo engañoso que pueden ser esos 60 días.. ya que en la gran mayoría de los casos esos sesenta días, con suerte serán solo 70 más o menos...
Otros diez días extra de apalancamiento :D (así son los negocios, no?).

¿Y cómo calculo esa fecha mágica?.
He visto a lo largo del tiempo muchas fórmulas, empleando condicionales sobre todo, que dan con la fecha buscada...
pero hoy os contaré una alternativa empleando funciones como LET o SECUENCIA.
Partimos de un listado de facturas, de las cuales necesitamos conocer la fecha futura de cobro
Calcular fecha futura de pago-cobro


La fórmula requerida la vemos en el rango E5:E15
=LET(rngFechas;SECUENCIA(35;1;B5+60;1);
INDICE(rngFechas;COINCIDIR(10;DIA(rngFechas);0)))

Nota la simpleza de la función...
Generamos un listado de 35 fechas a partir de la fecha futura sesenta días después; para luego localizar la posición del siguiente o próximo día 10 de ese listado... y listo, tenemos nuestra fecha.

Si verificas la columna H verás la diferencia real de días entre la fecha de factura y la de vencimiento calculada... los sesenta días quedan lejos ;-)

martes, 10 de mayo de 2022

VBA: Cargando datos en Arrays

Repasaremos algunos conceptos fundamentales para trabajar con VBA como puede ser la carga de datos en una Array.
No vamos a descubrir ahora a nadie las bondades de trabajar con Arrays en nuestros códigos... la rapidez y agilidad a la hora de trasladar o sencillamente trabajar con datos cargados es simplemente 'brutal'.
Imagina una tabla de datos con 51 campos y 240.000 registros... son muchos datos... de hecho el fichero pesa alrededor de 60 Mb (mucho para un fichero solo con datos y cero cálculo).

Veamos algunos de los procesos habituales de carga de datos desde nuestra celdas a una Array.
Accedemos a un módulo estándar del editor de VBA y escribimos:
Sub OptimizandoCargaDatos()

Start = Timer   'inicia el contador de tiempo
Dim Tbl As ListObject
Set Tbl = ActiveSheet.ListObjects(1)

''A)  bien cargamos la matriz directamente
arrDatos1 = Tbl.DataBodyRange.Value

finA = Round(Timer - Start, 3)  'contabilizamos tiempo de carga
Start = Timer   'inicia el contador de tiempo
'
''B)) o alternativamente a través de una función...
'dentro de nuestro módulo estándar
arrDatos2 = CargaArrays

finB = Round(Timer - Start, 3)  'contabilizamos tiempo de carga
Start = Timer 'inicia el contador de tiempo
'
''C) o desde un módulo de clase
Dim arrClase As New clsCargaArrays
arrDatos3 = arrClase.CargaMatrices

finC = Round(Timer - Start, 3)  'contabilizamos tiempo de carga
Start = Timer   'inicia el contador de tiempo

''D) cargándolo usando Properties desde el módulo de clase
Dim arrMatriz As New Clase1
arrDatos5 = arrMatriz.TodaMatriz

Fin_D = Round(Timer - Start, 3) 'contabilizamos tiempo de carga
Start = Timer   'inicia el contador de tiempo

''Tip/bonus: Seleccionar ciertas columnas de la Matriz
Set arrTest = New Clase1
arrDatos1_3 = arrTest.SelectCols(Array(1, 3))

fin_E = Round(Timer - Start, 3) 'contabilizamos tiempo de carga

Debug.Print "A (carga directa clásica): " & finA & vbCrLf & _
"B (a través de UDF): " & finB & vbCrLf & _
"C (1-módulo clase - funcion): " & finC & vbCrLf & _
"D (2-modulo clase Property Get: " & Fin_D & vbCrLf & _
"E (Bonus: Seleccionar columnas de la Array): " & fin_E & vbCrLf & "___________________"
Set Tbl = Nothing
Set arrTest = Nothing
End Sub
''''''''''''''
Function CargaArrays() As Variant
    CargaArrays = ActiveSheet.ListObjects(1).DataBodyRange.Value
End Function


Por otra parte crearemos dos módulos de clase.
El primero llamado 'Clase1' que contiene los siguientos procesos:
Dim m_Array As Range
'--------------------------------------------
Private Sub Class_Initialize()
    Set m_Array = ActiveSheet.ListObjects(1).DataBodyRange
End Sub
'--------------------------------------------
Private Sub Class_Terminate()
    Set m_Array = Nothing
End Sub
'--------------------------------------------
Property Get ColVector(n As Long) As Variant
    'funciona y devuelve el vector completo
    ColVector = Empty
    
    On Error GoTo Err_Handler
    ColVector = m_Array.Columns(n)
    Exit Property
    
Err_Handler:
End Property
'--------------------------------------------
Property Get SelectCols(nCols() As Variant) As Variant
    SelectCols = Empty
    
    On Error GoTo Err_Handler
    SelectCols = Application.Index(m_Array, Evaluate("Row(1:" & m_Array.Rows.Count & ")"), nCols)
    Exit Property
    
Err_Handler:
End Property
'--------------------------------------------
Property Get TodaMatriz() As Variant
    TodaMatriz = Empty

    On Error GoTo Err_Handler
    TodaMatriz = m_Array
    Exit Property

Err_Handler:
End Property

En este módulo de clase creamos un 'objeto matriz' cargando datos de la hoja de cálculo con un procedimiento 'Sub Class_Initializa', el cual carga el objeto m_Array con los datos de la tabla cuando desde un módulo estandar, por ejemplo, se llame a ese 'objeto' Clase1.
Además he generado distintas propiedades del objeto que permiten obtener un vector, un conjunto de vectores o simplemente la totalidad de la matriz, a partir de la Array cargada.

Necesitamos un módulo de clase más... llamado 'clsCargaArrays', con un simple procedimiento Function:
Function CargaMatrices() As Variant
    CargaMatrices = ActiveSheet.ListObjects(1).DataBodyRange.Value
 End Function


Si lanzamos varias veces nuestro procedimiento, y vemos el tiempo requerido por cada forma descrita, veremos lo siguiente:
VBA: Cargando datos en Arrays

Observamos tiempos de carga prácticamente idénticos, por lo que la decisión de optar por uno u otro es decisión personal...
Nota que estamos simplemente cargando 12.240.000 de celdas con datos, sin ningún otro procesamiento... y que una carga cualquiera de ese volumen de información neutra consume alrededor de 2,5 segundos!!! (asombroso!).

Siempre práctico tener alternativas de trabajo... en este claso incluyendo el trabajo (en varias formas) con Módulos de clase.

jueves, 5 de mayo de 2022

Power Query: Columnas a agrupar dinámicamente

Un caso fuera de lo habitual es poder elegir, de acuerdo a una lista, qué campos de una tabla de trabajo queremos agrupar/agregar.
Power Query: Columnas a agrupar dinámicamente

Partimos de una tabla de acciones detalladas, de acuerdo a una serie de conceptos: País, Ciudad, Año... y unas acciones o tareas desglosadas: Comercial, Estado de cuenta o Acción.
De esta tabla principal queremos obtener una comparativa de ciertos campos (comercial o estado o acción) respecto a los años 2022 y 2023 que aparezcan.
Para forzar qué campos emplearemos para una u otra cosa, dispondremos de una segunda tabla (en azul, a la derecha de la imagen) con dos campos...

Comezaremos cargando las dos tablas y ya dentro del editor de power query realizamos algunas tareas...

En primer lugar generaremos un par de listas a partir de la tabla azul 'TblCampos_a_Mostrar'...
Una primera lista solo con los campos que rigen la agrupación ('LstCamposAgrupacion'), y una segunda con los campos que queremos comparar/agrupar ('LstCamposComparar').
Así pues nuestras dos consultas que generan sendas listas serían:
//LstCamposAgrupacion
  let
    Origen = TblCamposMostrar,
    FilasFiltradas = Table.SelectRows(Origen, each ([Agrupar] = "Sí"))[Listado_campos_mostrar]
in
    FilasFiltradas
y
//LstCamposComparar
  let
    Origen = TblCamposMostrar,
    FilasFiltradas = Table.SelectRows(Origen, each ([Agrupar] = "Para_comparar"))[Listado_campos_mostrar]
in
    FilasFiltradas

dos consultas muy simples para filtrar los elementos deseados...

Finalmente creamos una consulta vacía nueva donde escribimos el código que permite la agrupación variable de columnas:
let
    //cargamos en buffer la tabla para evitar alteraciones de orden a la hora de agrupar
    Tabla=Table.Buffer(TblACCIONES),

    //realizamos la acción dinámica de agrupación
    FilasAgrupadas=Table.Group(Tabla, LstCamposAgrupacion, 
                List.Transform(
                    LstCamposComparar,
                    (Col) => {Col & "(2022-2023)", (Local) => Text.Combine(Table.Column(Local, Col),"-"), type nullable text}
                )  )
in
    FilasAgrupadas

En el ejemplo he optado por una transformación diferente a las habituales (de suma, promedio, etc.), donde aplico un concatenado de texto (Text.Combine) que permite mostrar la situación del 2022 vs 2023.

Pero lo realmente interesante es cómo hemos incluido dentro de la acción de agrupar Table.Group, la función List.Transform que se encarga de operar solo sobre la lista de campos a comparar elegida.
La combinación de estas dos funciones de GROUP y TRANSFORM sobre las listas elegidas en los primeros pasos del desarrollo nos retorna lo que queríamos...
Una agrupación sobre los campos de la 'LstCamposAgrupacion' y una agregación (concatenado en el ejemplo= sobre la 'LstCamposComparar'.

martes, 3 de mayo de 2022

Power Query: Nombres de columnas distintas

Hace unos días, durante una formación de Power Query surgió la cuestión de si era posible tratar de alguna forma orígenes de información con iguales conceptos pero con distintos nombres en las columnas (por ejemplo distintos idiomas).

Planteemos el siguiente caso, tenemos un grupo de dos empresas que reportando de un mismo sistema, uno de ellos muestra los encabezados en español y otro en inglés... pero en el proceso de transformación, queremos anexar ambos informes, para evitar los errores previsibles, queremos modificar los de uno de ellos para homogeneizar ambos encabezados.
Power Query: Nombres de columnas distintas


Un intento de anexado de consultas directamente no nos daría un resultado aceptable, ya que duplicaría el número de columnas al no identificarlas con el mismo nombre:
Power Query: Nombres de columnas distintas


Cualquier otro intento, sin el previo tratamiento del nombre de los encabezados, igualmente nos devolvería fallo.

Asi pues nuestro trabajo comienza accediendo a una de las tablas para realizar la transformación necesaria que incluya el reemplazamiento de los encabezados en español por los de inglés... de acuerdo a la tabla de equivalencia:
Power Query: Nombres de columnas distintas

Tabla que cargaremos en nuestro editor de consultas en forma de función...
El código M de este tratamiento es la siguiente función que he llamado 'fxTRATAMIENTO':
(tbl as table)=>
let
    Origen = tbl,
    //obtenemos lista de los nombres de la columnas de la tabla con la que trabajamos
    LstCOLSNAME = Table.ColumnNames(Origen),
    //y por otro lado comvertimos en una Lista la tabla de traducciones
    //es una Lista de listas (español-ingles) una por cada fila
    LstREEMPLAZOS=Table.ToRows(EQUIV),

    //con un conteo identificamos el idioma de los nombres de las columnas
    //la idea es determinar mediante un conteo la lista de elementos coincidentes 
    //entre la lista de nombres de la tabla y la de los nombres en inglés de la tabla de equivalencias
    //en caso afirmativo, procedemosa renombrar las columnas!!
    Reemplazo=if List.Count(List.Intersect({LstCOLSNAME,EQUIV[UK]}))=0 then Table.RenameColumns(Origen, LstREEMPLAZOS) else Origen,

    //una vez aplicado el renombrado aplicamos un tipo de cambio de datos
    TipoCambiado = Table.TransformColumnTypes(Reemplazo,{{"Date", type date}, {"Product", type text}, {"Units", Int64.Type}}),
    
    //y finalizamos nuestra pequeña transformación de ejemplo aplicando un filtro variable..
    //ndPDTO responde a la carga de la celda D1 de la hoja, la cual hemos subido como consulta-parámetro previamente
    FiltroPDTO = Table.SelectRows(TipoCambiado, each ([Product] = ndPDTO))
in
    FiltroPDTO
A continuación cargaremos en una sola consulta todas las tablas del fichero de trabajo.
Para ello emplearemos el método explicado en este otro post.
Por tanto el código de esta consulta sería:
let
    //recuperamos todas las tablas y nombres definidos del libro de trabajo
    Origen = Excel.CurrentWorkbook(),
    //y aplicamos un filtro para quedarnos solo con las que nos interesan
    //en el ejemplo las que comienzan por 'Tbl' (que son las que contienen datos a anexar ES y UK)
    FilasFiltradas = Table.SelectRows(Origen, each Text.StartsWith([Name], "Tbl")),
    //aplicamos a cada fila la función recién creada...
    FunciónInvocada = Table.AddColumn(FilasFiltradas, "DATOS", each fxTRATAMIENTO([Content]))
in
    FunciónInvocada


Solo nos queda expandir la columna recien 'DATOS' recien incorporada y devolver el resultado a la hoja de cálculo..
O bien, en una consulta nueva vacía añadir el siguiente código que se encargará de anexar cuantas tablas haya en la columna DATOS del paso anterior:
let
    Origen = Table.Combine(TblsPAISES[DATOS])
in
    Origen

El resultado sería:
Power Query: Nombres de columnas distintas

donde comprobaríamos que a partir de la celda D1 podemos elegir y actualizar qué producto visualizar, y como sin fallos ni columnas de más, vemos exactamente las tres columnas deseadas...

jueves, 28 de abril de 2022

VBA: Instrucción Implements en módulos de clase

Con sinceridad, llevaba tiempo sin programar en VBA, y lo echaba de menos... :D
Hace unos días tuve necesidad de desarrollar cierto código para un cliente y recorde una intrucción que ayuda a facilitar la programación a implementar, y como tuve que buscar documentación al respecto... había olvidado su uso en concreto, decidí publicar al respecto por si sirviera a alguien ;-)

La verdad es que la documentación de Microsoft al respecto no es muy esclarecedora...
pero la idea fundamental de esta intrucción es que facilita referirnos a nuestra biblioteca de tipos, funciones, procedimientos, propiedades, métodos, etc. generados dentro de nuestros Módulos de clase.

Planteemos el siguiente supuesto.
Partimos de una sencilla tabla ('TblPPAL') con movimientos sobre tres países: ES, FR e IT
VBA: Instrucción Implements en módulos de clase

Por supuesto esta tabla estará en constante crecimiento y además es susceptible de incorporar nuevos países.

Nuestro objetivo es obtener para cada registro de la tabla dada un cantidad en euros correspondiente de aplicar un precio y un descuento fijos, por país, informados en nuestra programación.
Nota: sí, lo se.. hay formas más sencillas de conseguirlo ;-)

Vamos con ello!.
En primer lugar crearemos los distintos Módulos de clase necesarios.
El primer módulo de clase se llamará 'clsPaises' y tendrá el siguiente código
Sub CalculoPVP(ByVal base As Double)
    
End Sub
''''''''''''''''''''''''''''''''''''''
Sub CeldaHojaCalculo(celda As Range)
    
End Sub

Estos dos procedimientos, tal cual, sin código a ejecutar... son los que llenaremos de contenido en los siguientes módulos de clase.
Es fundamental que se llamen exactamente igual, y contengan idénticos parámetros.

Crearemos ahora un nuevo Módulo de clase por cada país existente (en el presente y en el futuro...). Será por tanto una labor de mantenimiento a considerar!!.
Un módulo de clase llamado 'clsES' con el código siguiente:
Private d_calculo As Double
'permite dirigirnos a la biblioteca del módulo de clase 'clsPaises'
Implements clsPaises

'deberemos comenzar el procedimiento con el prefijo del Modulo de clase
Sub clsPaises_CalculoPVP(ByVal base As Double)
    d_calculo = base * (1 - 0.05)
End Sub

'deberemos comenzar el procedimiento con el prefijo del Modulo de clase
Sub clsPaises_CeldaHojaCalculo(celda As Range)
    'haría visible el resultado en
    'la ventana de inmediato
    Debug.Print TypeName(Me) & "-" & Format(d_calculo, "#,##0.00")
    'y en una celda de la hoja de cálculo
    celda.Value = CDbl(d_calculo)
End Sub


Un módulo de clase llamado 'clsFR' con el código siguiente:
Private d_calculo As Double
'permite dirigirnos a la biblioteca del módulo de clase 'clsPaises'
Implements clsPaises

'deberemos comenzar el procedimiento con el prefijo del Modulo de clase
Sub clsPaises_CalculoPVP(ByVal base As Double)
    d_calculo = base * (1 - 0.23)
End Sub

'deberemos comenzar el procedimiento con el prefijo del Modulo de clase
Sub clsPaises_CeldaHojaCalculo(celda As Range)
    'haría visible el resultado en
    'la ventana de inmediato
    Debug.Print TypeName(Me) & "-" & Format(d_calculo, "#,##0.00")
    'y en una celda de la hoja de cálculo
    celda.Value = CDbl(d_calculo)
End Sub


Un módulo de clase llamado 'clsIT' con el código siguiente:
Private d_calculo As Double
'permite dirigirnos a la biblioteca del módulo de clase 'clsPaises'
Implements clsPaises

'deberemos comenzar el procedimiento con el prefijo del Modulo de clase
Sub clsPaises_CalculoPVP(ByVal base As Double)
    'realizamos el cálculo...
    d_calculo = base * (1 - 0.15)
End Sub

'deberemos comenzar el procedimiento con el prefijo del Modulo de clase
Sub clsPaises_CeldaHojaCalculo(celda As Range)
    'haría visible el resultado en
    'la ventana de inmediato
    Debug.Print TypeName(Me) & "-" & Format(d_calculo, "#,##0.00")
    'y en una celda de la hoja de cálculo
    celda.Value = CDbl(d_calculo)
End Sub


Como se observa el código de estos tres módulos de clase son casi iguales a excepción de las condiciones particulares de cada país.
Nota: sigo insistiendo que hay formas más óptimas de conseguirlo... pero me intersa mostrar el uso de la intrucción 'Implements'.

Con los procesos generados en estos módulos de clase podremos generar y llamar en forma de objetos desde nuestros módulos estándar para facilitar los cálculos y procesos.. quedando más límpios y claros!!.

Con nuestros módulos de clase creados ya podemos insertar un módulo estándar que procese la información:
Sub Proceso()
Dim arrDatos As Variant
'cargamos los datos de la tabla en una matriz
arrDatos = Hoja1.Range("TblPPAL")

Dim sPais As clsPaises

'recorremos los datos de esa matriz
For i = LBound(arrDatos) To UBound(arrDatos)
    pais = arrDatos(i, 1)
    uds = arrDatos(i, 2)
    
    If pais = "ES" Then
        'definimos el objeto asociado a ES con sus condiciones de descuento particulares
        Set sPais = New clsES
        importe = uds * 11  'y obtenemos su precio base en euros
        
    ElseIf pais = "FR" Then
        'definimos el objeto asociado a FR con sus condiciones de descuento particulares
        Set sPais = New clsFR
        importe = uds * 9.52    'y obtenemos su precio base en euros
        
    ElseIf pais = "IT" Then
        'definimos el objeto asociado a IT con sus condiciones de descuento particulares
        Set sPais = New clsIT
        importe = uds * 12.13   'y obtenemos su precio base en euros
        
    Else
        'nada
    End If
    
    'Ejecutamos los procesos creados ad-hoc en nuestros módulos de clase
    '1-proceso el cálculo y carga la variable....
    sPais.CalculoPVP importe
    '2-la recuperamos en la hoja de cálculo y en la ventana de inmediato
    sPais.CeldaHojaCalculo Hoja1.Cells(i + 2, "E")
Next i

End Sub

Tras ejecutar la macro recién creado vemos los resultados...
VBA: Instrucción Implements en módulos de clase


Este sería un ejemplo de como con un trabajo previo, con nuestros módulos de clase, el trabajo posterior en los módulos estándar se facilita y queda más legible ;-)