VBA Estacionalidad

Posted on by YorsH

Sabemos que una serie de tiempo puede descomponerse en términos de su nivel, tendencia, estacionalidad y un ruido residual, idealmente blanco.

Si bien es fácil obtener estadígrafos para la media y la pendiente asociada a la recta tendencia, no existe una función en Excel para obtener los coeficientes de estacionalidad en forma directa. El objetivo de este artículo es desarrollar un algoritmo para automatizar este cálculo a partir de una serie de datos cualquiera.

Un caso típico sería contar con una serie de datos mensuales y obtener un coeficiente de estacionalidad para cada uno de los meses del año (12 en total). Podría ocurrir, sin embargo, que tuviésemos datos trimestrales y necesitemos coeficientes para cada uno de los trimestres (4 en total). Denominaremos periodo al número de coeficientes de estacionalidad que deseamos calcular.

Los datos reales con los que contamos podrían no comenzar exactamente junto con un nuevo periodo (un mes distinto a enero, un trimestre distinto al primero). Podríamos generalizar entonces el problema a una serie de datos de periodo m cuyo primer dato corresponde a Inicio, con Inicio entre 1 y m.

En el siguiente ejemplo se tiene una serie de datos de periodo m=12 y cuyos datos reales tienen su Inicio en el mes 7. Los datos corresponden a las ventas de un determinado producto entre los meses de julio 2016 y junio 2019 (n=36).

Comenzaremos creando en VBA una función que llamaremos COEFICIENTES_ESTACIONALIDAD, que recibirá una serie de datos (como un rango de Excel) y las variables Inicio y Periodo (m). Nos devolverá un arreglo de m datos con los coeficientes de estacionalidad normalizados desde 1 hasta m.

Public Function COEFICIENTES_ESTACIONALIDAD(rngDatos As Range, _
    intPeriodo As Integer, Optional intInicio As Integer) As Variant

    'Calcula coeficiente de estacionalidad de un rango de datos
    'y los devuelve en un arreglo de intPeriodo (m) valores
    'considerando que el nuevo periodo se inicia en el dato intInicio
    
    Dim arrDatos As Variant
    Dim arrDatosSuavizados As Variant
    Dim arrRatios As Variant
    Dim arrCoeficientes As Variant
    
    'Si no se especifica intinicio, se asume que es igual a 1
    If intInicio = 0 Then intInicio = 1

   'Leer los datos rngDatos -> arrDatos (n datos)
   arrDatos = LEERDATOS(rngDatos)

   'Calcular promedios moviles arrDatos -> arrDatosSuavizados (n-m)
   arrDatosSuavizados = PROMEDIOMOVIL(arrDatos, intPeriodo)

   'Calcular ratios arrDatosSuavizados (n-m) -> arrRatios (n-m)
   arrRatios = CALCULARRATIOS(arrDatos, arrDatosSuavizados, intPeriodo)

   'Calcular mediana por mes arrRatios (n-m) 
   arrCoeficientes = MEDIASPERIODICAS(arrRatios, intPeriodo)

   'Reordenar arreglo de coeficientes arrCoeficientes(m)
   arrCoeficientes = REORDENARARREGLO(arrCoeficientes, intInicio)

   'Normalizar arreglo de coeficientes arrCoeficientes(m)
   arrCoeficientes = NORMALIZARARREGLO(arrCoeficientes)

   'Devolver coeficientes de estacionalidad
   COEFICIENTES_ESTACIONALIDAD = arrCoeficientes

End Function

El algoritmo comienza leyendo los datos en el arreglo arrDatos y obteniendo una versión suavizada de los n datos: un arreglo que tendrá m datos menos, dado que utilizaremos para ello un promedio móvil de orden m. Vale decir, perderemos un periodo de datos, por lo que es imprescindible contar con datos suficientes: mínimo 2 periodos, idealmente 3 o más.

Private Function LEERDATOS(ByVal rngDatos As Range) As Variant

   'Devuelve un arreglo de 1 hasta rngDatos.count datos

   Dim arrAux() As Double
   Dim i As Integer
   Dim rngCelda As Range

   i = 1

   ReDim arrAux(1 To rngDatos.Count)

   For Each rngCelda In rngDatos
      arrAux(i) = rngCelda.Value
      i = i + 1
   Next rngCelda

   LEERDATOS = arrAux

End Function


Private Function PROMEDIOMOVIL(ByVal arrDatos As Variant, _
   ByVal intPeriodo As Integer) As Variant

   'Calcula el promedio móvil de intPeriodo datos

   Dim intCont As Integer
   Dim arrAux() As Double
   Dim dblSuma As Double
   Dim i, j As Integer

   intCont = UBound(arrDatos) - intPeriodo
   ReDim arrAux(1 To (UBound(arrDatos) - intPeriodo))

   For i = 1 To intCont

      dblSuma = 0

      For j = (i + 1) To (i + intPeriodo)
         dblSuma = dblSuma + arrDatos(j)
      Next j

      arrAux(i) = dblSuma / intPeriodo

   Next i

   PROMEDIOMOVIL = arrAux()

End Function

La figura muestra los valores del arreglo desestacionalizado resultante. Como hemos usado los m primeros datos para calcular el promedio móvil del periodo siguiente, el arreglo resultante posee n-m datos.

Ahora podemos obtener los candidatos a coeficientes dividiendo los n-m datos reales correspondientes por cada uno de los n-m datos suavizados que acabamos de calcular. El arreglo resultante tendrá también n-m datos. Programamos este cálculo en la función CALCULARRATIOS.

Private Function CALCULARRATIOS(ByVal arrDatos As Variant, _
   ByVal arrDatosSuavizados As Variant, ByVal intPeriodo As Integer) _
   As Variant

   'Calcula los ratios entre los datos reales y la serie desestacionalizada

   Dim arrAux() As Double
   Dim i As Integer

   ReDim arrAux(LBound(arrDatosSuavizados) To UBound(arrDatosSuavizados))

   For i = LBound(arrDatosSuavizados) To UBound(arrDatosSuavizados)
      arrAux(i) = arrDatos(i + intPeriodo) / arrDatosSuavizados(i)
   Next i

   CALCULARRATIOS = arrAux()

End Function

Para pasar de n-m cocientes a los m coeficientes requeridos, promediamos los cocientes de los distintos periodos. En nuestro ejemplo, el coeficiente de julio lo calcularemos como el promedio de los cocientes correspondientes al mes de julio, para los años 2017, 2018 y 2019.

Private Function MEDIASPERIODICAS(ByVal arrDatos As Variant, _
   ByVal intPeriodo As Integer) As Variant

   'Calcula las intPeriodo medias de los datos del arreglo

   Dim arrAux() As Double
   Dim dblSuma As Double
   Dim i, j, k As Integer

   ReDim arrAux(1 To intPeriodo) As Double

   For i = 1 To intPeriodo

      dblSuma = 0
      k = 0

      For j = i To UBound(arrDatos) Step intPeriodo
         k = k + 1
         dblSuma = dblSuma + arrDatos(j)
      Next j

      arrAux(i) = dblSuma / k

   Next i

   MEDIASPERIODICAS = arrAux()

End Function

Si el mes Inicio es distinto a 1, como en este caso, los coeficientes calculados se encontrarán desordenados. Iniciarán en el coeficiente 7, seguirán hasta el 12 y luego continuarán en el 1 hasta el 6. Creamos una función que nos permita reordenarlos, teniendo como parámetro el mes Inicio.

Private Function REORDENARARREGLO(ByVal arrDatos As Variant, _
   ByVal intPosicion As Integer) As Variant

   'Reordena un arreglo que comienza en la

   Dim arrAux() As Double
   Dim i As Integer
   Dim n As Integer

   n = UBound(arrDatos) - LBound(arrDatos) + 1
   ReDim arrAux(1 To n) As Double

   For i = 1 To n

      If i < intPosicion Then
         arrAux(i) = arrDatos(i + intPosicion - 1)
      Else
         arrAux(i) = arrDatos(i - intPosicion + 1)
      End If

   Next i

   REORDENARARREGLO = arrAux()

End Function

Por último, nos encargamos de normalizar los coeficientes, para que su promedio sea uno. Para ello dividimos cada coeficiente por la sumatoria de ellos y volvemos a multiplicar por el número de coeficientes:

Private Function NORMALIZARARREGLO(ByVal arrDatos As Variant) As Variant

   'Normaliza el arreglo para que su media sea exactamente 1

   Dim arrAux() As Double
   Dim dblSuma As Double
   Dim i As Integer
   Dim n As Integer

   dblSuma = 0
   n = UBound(arrDatos) - LBound(arrDatos) + 1
   ReDim arrAux(1 To n) As Double

   'Primero se suman todos los elementos del arreglo
   For i = 1 To n  
      dblSuma = dblSuma + arrDatos(i)
   Next i

   'Se divide por la suma de los elementos para normalizar
   For i = 1 To n
      arrAux(i) = arrDatos(i) / dblSuma * n
   Next i

   NORMALIZARARREGLO = arrAux()

End Function

De esta forma, hemos creado una función vectorial que nos permitirá calcular de una vez los m coeficientes de estacionalidad de una serie de datos.

¿Cómo saber si los coeficientes de estacionalidad calculados son significativos? Podríamos desestacionalizar la serie original (usando los coeficientes resultantes) y calcular qué fracción de la volatilidad ha sido reducida.

Calcularemos para ello SSE (suma de errores cuadráticos de los valores desestacionalizados y SST (suma de errores cuadráticos de los valores originales) . El cociente entre ambos valores es una medida del ajuste de nuestro modelo, denominado Coeficiente de Determinación o R2.

Public Function AJUSTE_ESTACIONALIDAD_R2(rngDatos As Range, _
   rngCoeficientes As Range, intInicio As Integer)

   Dim arrDatos() As Double
   Dim arrCoeficientes() As Double
   Dim arrDesestacionalizados() As Double
   Dim dblSSEdatos As Double
   Dim dblSSEdesestacionalizados As Double

   'Leer arrDatos
   arrDatos = LEERDATOS(rngDatos)

   'Leer arrCoeficientes
   arrCoeficientes = LEERDATOS(rngCoeficientes)

   'Desestacionalizar los datos usando los coeficientes
   arrDesestacionalizados = DESESTACIONALIZAR(arrDatos, _
   arrCoeficientes, intInicio)

   'Calcular SSE (suma de los errores cuadráticos) de los datos
   dblSSEdatos = SSE(arrDatos)

   'Calcular SSE de los datos desestacionalizados
   dblSSEdesestacionalizados = SSE(arrDesestacionalizados)

   'Calcular R2 del ajuste de estacionalidad
   AJUSTE_ESTACIONALIDAD_R2 = 1 - dblSSEdesestacionalizados / dblSSEdatos

End Function


Private Function DESESTACIONALIZAR(ByVal arrDatos As Variant, _
   ByVal arrCoeficientes As Variant, ByVal intInicio As Integer) As Variant

   'Divide cada dato por el coeficiente de estacionalidad correspondiente
   'según el parámetro intInicio. El primer índice de arrCoeficientes es 1

   Dim arrAux() As Double
   Dim i, n, m, k As Integer

   n = UBound(arrDatos) - LBound(arrDatos) + 1
   m = UBound(arrCoeficientes)
   ReDim arrAux(LBound(arrDatos) To UBound(arrDatos)) As Double

   For i = LBound(arrDatos) To UBound(arrDatos)
      'Se calcula el indice k del coeficiente que corresponde al dato i
      
       k = (i + intInicio - 2) Mod m + 1

      'Revisión de bordes:
      'si intinicio = 1 y i=1, k = (1+1-2) mod m + 1 = 0 + 1 = 1 (ok)
      'si intinicio = 2 y i=m, k = (m+2-2) mod m + 1 = m mod m + 1 = 0 + 1    
      '= 1 (ok)

      'Se divide el dato por el coeficiente correspondiente

      arrAux(i) = arrDatos(i) / arrCoeficientes(k)

   Next i

   DESESTACIONALIZAR = arrAux
End Function


Private Function SSE(ByVal arrDatos As Variant) As Double

   'Entrega la suma de errores cuadráticos respecto de la media
   Dim dblMedia As Double
   Dim dblSuma As Double
   Dim i As Integer

   'Calcular la media de los datos
   dblMedia = Application.WorksheetFunction.Average(arrDatos)

   'Calculamos la distancia cuadrática respecto de la media para cada dato
   dblSuma = 0

   For i = LBound(arrDatos) To UBound(arrDatos)
      dblSuma = dblSuma + (arrDatos(i) - dblMedia) ^ 2
   Next i

   SSE = dblSuma

End Function

El valor de R2 va entre 0 y 1, representando en este caso el porcentaje del error cuadrático de la serie que puede ser explicado por los coeficientes de estacionalidad calculados. Mientras más cercano a 1, mayor significancia del modelo. Si la serie posee estacionalidad, esperaríamos encontrar R2 cercanos o superiores a 0,5. Es importante realizar esta verificación, puesto que nuestro algoritmo siempre encontrará coeficientes de estacionalidad, sin importar que estos valores den cuenta o no de la presencia de estacionalidad en los datos.

Y bueno, hasta aquí la entrega de hoy. ¿Qué nuevas mejoras o aplicaciones se te vienen en mente? Nos vemos pronto.

VBA Remoción de Outliers

Posted on by YorsH

Muchas veces los datos que queremos analizar o proyectar incluyen valores que no son representativos del proceso que queremos modelar. Son los denominados outliers, datos excepcionales que no se originan en el proceso estándar: ventas extraordinarias, rendimientos excepcionales, errores no corregidos, puestas en marcha…, etcétera.

¿Qué es un outlier? Un outlier (valor atípico) es todo número que se encuentra a mucha distancia del resto de los datos y que, no siendo un elemento representativo de la población, tiene el potencial de distorsionar el análisis estadístico o la proyección de la serie.

Antes de continuar, es muy importante distinguir entre outliers y los denominados cisnes negros. El concepto cisne negro fue acuñado por Nassim Nicholas Taleb en su libro homónimo del 2007. En él se compara a los eventos raros, impredecibles y con un impacto desproporcionado, con la primera aparición de un cisne negro, antes que la comunidad científica supiera de la existencia de estas aves.

La principal diferencia entre un outlier y un cisne negro es que este último sí hace parte del proceso que se quiere modelar o analizar y por lo tanto ¡no se puede eliminar!. Un cisne negro es un evento muy raro pero que puede eventualmente ocurrir. Y aunque poco probable, su impacto es tan grande que el riesgo de no incluirlo en el análisis se vuelve intolerable.

Muchas veces lo que determina que un evento excepcional sea un outlier o un cisne negro es el objetivo del estudio. Si deseamos pronosticar ventas futuras de clientes actuales, es probable que cualquier evento atípico del pasado e improbable en el futuro no sea algo que necesitemos contabilizar como parte de nuestros flujos de caja esperados. Sin embargo, el mismo evento inesperado sí puede que sea necesario incluirlo cuando estamos planificando compras o producciones en el mediano o largo plazo, puesto que de repetirse podría generar un quiebre de stock eventualmente oneroso.

De esta manera, surgen dos preguntas claves antes de descartar outliers: ¿cuál puede ser el impacto de no considerarlos? Podría darse el caso que el objetivo del modelamiento se vea comprometido al excluir ciertos datos, como sucede con los modelos de quiebres de stock o de liquidez. La segunda pregunta es: ¿qué información estoy dejando de analizar? Un outlier podría contener las pistas que estamos buscando para tener un desempeño excepcional o para evitar errores costosos. Lo más prudente entonces es excluirlos para luego analizarlos por separado y aprender de ellos.

Nos hemos propuesto diseñar una función en VBA que nos permita leer una serie o rango de datos relacionados entre sí por un proceso en común e identificar aquellos valores que se encuentran muy alejados del resto de la población. El objetivo es utilizar esta función como un proceso de limpieza previo al cálculo de estadígrafos y proyecciones que pretendan modelar el comportamiento de la población central. Y una vez identificados los potenciales outliers, pediremos a nuestra función que los reemplace por el valor más cercano (o por la media de los valores más cercanos). De esta manera devolveremos una nueva serie de datos de igual longitud, pero libre de valores atípicos y lista para su uso.

Diagrama de cajas y bigotes para una serie sin outliers

Usaremos el rango intercuartil (IQR) para establecer qué valores son o no atípicos dentro de una muestra de datos. Si ordenamos todos los valores de menor a mayor y los ubicamos a lo largo de una recta, probablemente observaremos cómo los datos tienden a concentrarse en torno a su mediana, distribuyéndose con mayor o menor homogeneidad a ambos costados de ella. Si encerramos en una caja horizontal los valores que se encuentran entre el 25% y el 75% más bajo (percentiles 25 y 75 respectivamente), y consignamos con una raya vertical la posición de la mediana (percentil 50) , obtenemos un diagrama de cajas y bigotes como el mostrado en la figura anterior.

El ancho de la caja (la distancia entre el percentil 25 y el percentil 75), se denomina rango intercuartil (IQR). Si nos desplazamos 1,5 IQR a la izquierda del borde izquierdo de la caja y 1,5 IQR a la derecha del borde derecho de la caja, tendremos dos límites dentro de los cuales debiera encontrarse la gran mayoría de los datos de la población (idealmente todos).

Los valores que se encuentren al exterior de estas marcas serán denominados outliers. Si consideramos como límites 3,0 IQR a la izquierda y a la derecha de la caja, entonces todos aquellos valores al exterior de estos nuevos límites serán denominados outliers extremos.

Comenzamos definiendo una nueva función pública en VBA que denominaremos LIMPIARDATOS. Como parámetros, especificaremos un rango de datos de entrada y una variable lógica (boolean) opcional que marcaremos como verdadero si queremos considerar solamente outliers extremos (por defecto se asume falsa y consideraremos todos los outliers).

Public Function LIMPIARDATOS(ByVal rngDatos As Range, _
    Optional ByVal bolExtremo As Boolean) As Variant

    'Declaramos las variables que utilizarmos en la lectura de datos
    Dim arrDatos() As Double
    Dim i As Integer
    Dim j As Integer
    Dim rngCelda As Range
    
    'Declaramos el arreglo donde almacenaremos la serie de datos limpia
    Dim arrLimpios() As Double
    
    'Declaramos las variables que utilizaremos en la identificación
    Dim dblP25, dblP50, dblP75, dblIQR, dblFactor As Double

    'Declaramos las variables que utilizaremos en la limpieza de outliers
    Dim dblInferior, dblSuperior As Double
    Dim dblIzquierda, dblDerecha As Double
    Dim bolIzquierda, bolDerecha As Boolean
    
    'Leemos los datos en al arreglo arrDatos (horizontal)
    ReDim arrDatos(1 To rngDatos.Count)
    i = 1
    For Each rngCelda In rngDatos
        arrDatos(i) = rngCelda.Value
        i = i + 1
    Next rngCelda
    ReDim arrLimpios(1 To rngDatos.Count)

Para calcular los percentiles (25, 50 y 75) requeridos para estimar el IQR, utilizaremos el método Percentile incorporado en la hoja de cálculo. Luego estimaremos el límite representativo de la población usando un factor de 1,5 o 3,0 veces el IQR según haya sido especificado en la variable bolExtremo pasada por parámetro.

   'Calculamos los distintos cuartiles y el rango intercuartil
   dblP25 = WorksheetFunction.Percentile(arrDatos, 0.25)
   dblP50 = WorksheetFunction.Percentile(arrDatos, 0.5)
   dblP75 = WorksheetFunction.Percentile(arrDatos, 0.75)
   dblIQR = dblP75 - dblP25

   If bolExtremo Then
      'Solo removeremos outliers extremos (3,0 * IQR)
      dblFactor = 3
   Else
      'Removeremos outliers desde 1,5 * IQR (por defecto)
      dblFactor = 1.5
   End If

   'Calculamos limite inferior y superior a partir de los cuales  
   'removeremos los outliers
   dblInferior = dblP25 - dblFactor * dblIQR
   dblSuperior = dblP75 + dblFactor * dblIQR

La segunda parte de nuestra función consiste en recorrer cada uno de los datos del arreglo buscando valores que estén por debajo o por sobre estos límites definidos: arrDatos(i) < dblInferior OR arrDatos(i) > dblSuperior .

Cada vez que encontramos un nuevo outlier deberemos reemplazarlo por el promedio de los valores típicos más cercanos. Si no existen valores típicos a la izquierda, se usará el valor típico más cercano a la derecha. Si no existen valores típicos a la derecha, se usará el valor típico más próximo a la izquierda. Si existen valores típicos a ambos lados, se reemplaza el outlier por el promedio de ambos.

Para hacer esto, cada vez que se encuentra un nuevo outlier, se inicia una búsqueda hacia la izquierda mediante un ciclo Do…Loop. Se itera hasta alcanzar el límite izquierdo del arreglo o hasta que se dé con un valor representativo que podamos usar para reemplazar. Si encontramos un dato con tales características, lo almacenamos en la variable dblIzquierda y marcamos la búsqueda como exitosa (True) usando la variable bolIzquierda. Finalizado este proceso repetimos la búsqueda, pero esta vez hacia la derecha. Reemplazado el outlier encontrado, continuamos con la revisión del siguiente dato de la serie hasta recorrer todo el arreglo.

   'Recorremos el arreglo buscando outliers
   For i = LBound(arrDatos) To UBound(arrDatos)

      'Por defecto copiamos el mismo valor de la serie
      arrLimpios(i) = arrDatos(i)

         If arrDatos(i) < dblInferior Or arrDatos(i) > dblSuperior Then

           'Hemos encontrado un outlier!
           bolIzquierda = False
           bolDerecha = False

           'Buscamos el primer no outlier a la izquierda
           j = i
           Do While j > LBound(arrDatos) And Not bolIzquierda
             j = j - 1
             If arrDatos(j) >= dblInferior And arrDatos(j) <= dblSuperior _ 
              Then
                 'Encontramos un no outlier a la izquierda!
                 bolIzquierda = True
                 dblIzquierda = arrDatos(j)
              End If
           Loop

          'Buscamos el primer no outlier a la derecha
          j = i
          Do While j < UBound(arrDatos) And Not bolDerecha
            j = j + 1
            If arrDatos(j) >= dblInferior And arrDatos(j) <= dblSuperior _
             Then
                'Encontramos un no outlier a la derecha!
                bolDerecha = True
                dblDerecha = arrDatos(j)
            End If
          Loop

       'Calculamos un nuevo valor promediando los valores izquierda y 
       'derecha, si existen
       If bolIzquierda And bolDerecha Then
          arrLimpios(i) = (dblIzquierda + dblDerecha) / 2
       ElseIf bolIzquierda And Not bolDerecha Then
          arrLimpios(i) = dblIzquierda
       ElseIf bolDerecha And Not bolIzquierda Then
          arrLimpios(i) = dblDerecha
       End If

   End If

Next i

De esta forma, hemos trasvasijado el arreglo arrDatos() en el nuevo arreglo arrLimpios(), que contiene sustitutos para los outliers que pudiera contener la serie original. Finalizamos la función devolviendo esta nueva serie como resultado.

    LIMPIARDATOS = arrLimpios
    
End Function

En la imagen se muestra un ejemplo de uso de la función LIMPIARDATOS. A la izquierda, en la columna B se encuentra una serie de datos que se desea proyectar. Considerando 1,5 * IQR, los límites de la población típica son 69,1 y 102,1 respectivamente. De esta manera, sabemos que la serie posee 3 valores atípicos (outliers) marcados en verde, los cuales serán considerados para su eliminación dado que escogimos la opción 0 (FALSO) como segundo parámetro.

A la derecha, se despliega en la fila 3 el resultado del proceso de limpieza. Se visualiza cómo cada uno de los outliers fue reemplazado por el promedio de los valores representativos más cercanos.

Un segundo ejemplo se muestra a continuación con outliers ubicados justo en los extremos del arreglo. Se observa cómo en este caso sólo se considera el valor típico más cercano para reemplazarlo:

Y bueno, hasta aquí la entrega de hoy, espero que hayas disfrutado de su lectura y que puedas usar esta función para limpiar tus datos en lo sucesivo. ¿Qué potenciales mejoras o modificaciones se te vienen en mente?

VBA Una clase Flujo de Caja

Posted on by YorsH

Definir clases de objetos en VBA nos permite generalizar una estructura de datos y un conjunto de comportamientos (métodos) asociados a ella. Una vez creada, podemos generar infinitas instancias de esa clase que posean las propiedades y métodos que hemos definido, haciendo nuestro código más limpio (y abstracto).

Un ejemplo sería considerar el flujo de caja de un proyecto como una serie de tiempo desde el tiempo 0 hasta el plazo de evaluación, una estructura de datos tipo array, cuyos métodos podrían incluir el cálculo de valores presentes, futuros, tasas de retorno y payback.

Lo primero que hacemos es insertar un nuevo módulo de clase en el editor VBA que llamaremos FlujoCaja.

Comenzamos definiendo en el módulo las variables internas que almacenarán las propiedades de nuestra clase (la estructura de datos propiamente tal):

Option Explicit

Private arrFlujo As Variant
Private sngTasaDescuento As Single
Private dblValorPresente As Double
Private dblValorFuturo As Double
Private sngTIR As Single
Private sngPayback As Single
Private strUnidad As String
Private strPeriodo As String

Utilizaremos el método Initialize de la clase para iniciar todas las variables en cero. Este método se ejecutará cada vez que creemos una nueva instancia de nuestra clase, mediante la instrucción: Set fcNuevo = New FlujoCaja 

Private Sub Class_Initialize()
    arrFlujo = 0
    sngTasaDescuento = 0
    dblValorPresente = 0
    dblValorFuturo = 0
    sngTIR = 0
    sngPayback = 0
    strUnidad = ""
    strPeriodo = ""
End Sub

Para cada una de las propiedades definimos métodos que permitan a su usuario leer los datos (Get) o escribir nuevos datos en ellas (Let), según corresponda.

'Propiedad Flujo
Public Property Get Flujo() As Variant
    Flujo = arrFlujo
End Property

Public Property Let Flujo(vntArreglo As Variant)
    arrFlujo = vntArreglo
End Property


'Propiedad Plazo
Public Property Get Plazo() As Integer
    Plazo = UBound(arrFlujo)
End Property


'Propiedad TasaDescuento
Public Property Get TasaDescuento() As Single
    TasaDescuento = sngTasaDescuento
End Property

Public Property Let TasaDescuento(sngTasa As Single)
    sngTasaDescuento = sngTasa
End Property


'Propiedad ValorPresente
Public Property Get ValorPresente() As Double
    ValorPresente = dblValorPresente
End Property


'Propiedad ValorFuturo
Public Property Get ValorFuturo() As Double
    ValorFuturo = dblValorFuturo
End Property


'Propiedad TIR
Public Property Get TIR() As Single
    TIR = sngTIR
End Property


'Propiedad Payback
Public Property Get Payback() As Single
    Payback = sngPayback
End Property


'Propiedad Unidad
Public Property Get Unidad() As String
    Unidad = strUnidad
End Property

Public Property Let Unidad(strU As String)
    strUnidad = strU
End Property


'Propiedad Periodo
Public Property Get Periodo() As String
    Periodo = strPeriodo
End Property

Public Property Let Periodo(strP As String)
    strPeriodo = strP
End Property

Como vemos, algunas propiedades como FlujoCaja, Periodo, Unidad son de lectura y escritura, mientras que otras que se generan como resultado (Plazo, TIR, Payback, ValorPresente, ValorFuturo) son solo de lectura.

Una vez definida la estructura de datos que contendrá, le «enseñamos» a nuestra clase algunos trucos (métodos) que necesitará para ser útil al usuario.

Añadiremos un método que permita leer los datos del flujo a partir de un determinado rango de Excel (rango fila o columna). Definimos para ello un Public Sub que tendrá como argumento el rango a leer. Nuestro procedimiento almacenará en la variable interna arrFlujo el arreglo leído desde Excel para su posterior uso.

Public Sub LeerRango(ByRef rngRango As Range)
    Dim arrAux() As Double
    Dim i As Integer
    Dim rngCelda As Range
    
    ReDim arrAux(rngRango.Count)
    i = 0
    
    For Each rngCelda In rngRango
        ReDim Preserve arrAux(i)
        arrAux(i) = rngCelda.Value
        i = i + 1
    Next rngCelda
    
    arrFlujo = arrAux
End Sub

Para calcular rentabilidades y valores presentes o futuros, contamos con dos alternativas. La primera es realizar todos los cálculos de una sola vez e ir almacenando los resultados en las variables internas para su posterior consulta. Podríamos crear un nuevo Public Sub que invoque cada uno de los cálculos:

Public Sub Evaluar()
    CalcularValorPresente
    CalcularTIR
    CalcularPayback
    CalcularValorFuturo
End Sub

Desde el código en que usemos una nueva instancia de nuestra clase, deberemos primero ejecutar éste método: fcNuevo.Evaluar y luego recién leer las propiedades respectivas.

Este camino es especialmente útil cuando los cálculos son intensivos en recursos y sólo queremos realizarlos una vez, mientras que los resultados pueden ser invocados en múltiples oportunidades.

En esta aplicación en particular, ninguno de los cálculos es muy intensivo en recursos y es más, queremos que se realicen dinámicamente para poder realizar algunas iteraciones. La segunda alternativa parece ser más eficaz en este caso: Reemplazaremos los métodos Get de cada una de estas propiedades por funciones que nos permitan calcularlas «en tiempo real»

'Propiedad ValorPresente
Public Property Get ValorPresente() As Double
    Dim i As Integer
    dblValorPresente = 0
    For i = 0 To UBound(arrFlujo)
        dblValorPresente = dblValorPresente + _
           arrFlujo(i) / ((1 + sngTasaDescuento) ^ i)
    Next i
    ValorPresente = dblValorPresente
End Property

'Prpiedad ValorFuturo
Public Property Get ValorFuturo() As Double
    Dim i As Integer
    dblValorFuturo = 0
    For i = 0 To UBound(arrFlujo)
        dblValorFuturo = dblValorFuturo + _
           arrFlujo(i) * ((1 + sngTasaDescuento) ^ i)
    Next i
    ValorFuturo = dblValorFuturo
End Property

'Propiedad TIR
Public Property Get TIR() As Single
    sngTIR = Application.WorksheetFunction.IRR(arrFlujo)
    TIR = sngTIR
End Property

Cuando el payback no es un número exacto de periodos, lo calculamos interpolando linealmente entre los dos periodos que se produce el cambio de signo al evaluar el flujo acumulado:

'Propiedad Payback
Public Property Get Payback() As Single
    Dim i As Integer
    Dim dblsuma As Double
    
    dblsuma = 0
    sngPayback = -1  'valor retornado si no se encuentra payback
    For i = 0 To UBound(arrFlujo)
        If dblsuma < 0 And dblsuma + arrFlujo(i) >= 0 Then
            'existe un cambio de signo entre i-1 e i
            'interpolamos el payback
            sngPayback = i - 1 - dblsuma / arrFlujo(i)
        End If
        dblsuma = dblsuma + arrFlujo(i)
    Next i
    Payback = sngPayback
End Property

Ya creada nuestra clase FlujoCaja, estamos listos para usarla en futuros códigos, como en el sencillo ejemplo a continuación:

En un nuevo módulo creamos, por ejemplo, un procedimiento Test en el que creamos un nuevo objeto de nuestra clase FlujoCaja y usamos los procedimientos creados para leer los datos desde el rango señalado en amarillo y para calcular valor presente, futuro, tir y payback.

Sub Test()
    Dim fcFlujo As FlujoCaja
    Set fcFlujo = New FlujoCaja
    fcFlujo.LeerRango Hoja1.Range("B2:E2")
    fcFlujo.TasaDescuento = 0.1
    
    Hoja1.Range("B3") = fcFlujo.ValorPresente
    Hoja1.Range("B4") = fcFlujo.TIR
    Hoja1.Range("B5") = fcFlujo.Payback
    Hoja1.Range("B6") = fcFlujo.ValorFuturo
    Hoja1.Range("B7") = fcFlujo.Plazo
    
End Sub

La simplicidad de este nuevo código es evidente. Nuestra nueva clase podemos exportarla a cada planilla Excel que necesitemos y, por supuesto, podemos seguirla mejorando e incorporando nuevas funcionalidades.

¿Qué usos se te vienen en mente para esta nueva clase? ¿Qué otras posibles mejoras o versiones podrían agregarle valor? ¡Nos vemos pronto!

VBA Newton Raphson

Posted on by YorsH

La gran mayoría de nuestros modelos financieros son construidos para contestar diversas preguntas en torno a puntos de equilibrio: ¿cuál es el nivel de ventas mínimo para que el negocio sea rentable? ¿cuál es la tarifa mínima que consigue pagar los gastos y la inversión?

Lo que hacemos es identificar la función cuyo valor queremos llevar a cierto objetivo (la rentabilidad, por ejemplo) y aislamos una variable cuyo impacto en la función sea relevante (la tarifa, por nombrar alguna).

La herramienta buscar objetivo de Excel es de gran utilidad en este punto, aunque debamos volver a utilizarla cada vez que realizamos un cambio en alguna de las otras variables del modelo para obtener un nuevo punto de equilibrio.

¿Cómo evitar este paso y generar modelos que lleguen dinámicamente al equilibrio definido?

Comencemos recordando el algoritmo de Newton-Raphson para encontrar una raíz de una función determinada:

Dos son los problemas que requieren más atención desde el punto de vista del código: calcular la derivada de la función en el nuevo punto de iteración y evaluar la función en ese punto.

El primero de los problemas, calcular la derivada de una función, lo hemos solucionado en el post anterior, aproximando su valor a la división [f(x+e)-f(x-e)]/2e, para algún valor de e muy pequeño.

Como en el post anterior la función objetivo era explícita respecto de la variable independiente ‘x’, podíamos evaluarla simplemente reemplazando el carácter ‘x’ en la fórmula por el valor correspondiente, cuantas veces apareciera. Sin embargo, no es necesariamente el caso de este ejemplo, en que la variable independiente puede influir indirectamente sobre la función objetivo.

Por este motivo, evaluaremos la función directamente a partir del modelo Excel disponible, leyendo el resultado de la fórmula en la planilla y cambiando para ello en la misma planilla el valor de la variable independiente a ensayar.

Dadas estas restricciones adicionales ya no podemos programar una función iterativa sino un procedimiento que lea y escriba en rangos específicos de la planilla que contiene nuestro modelo. Una versión de este algoritmo en VBA podría ser el siguiente:

Public Sub METODONEWTON()

   On Error Resume Next

   Dim rngFuncion As Range
   Dim rngX As Range
   Dim dblK As Double
   Dim sngDelta As Single
   Dim intIteraciones As Integer

   Dim dblXi As Double
   Dim dblFXi As Double
   Dim dblFXiMAS As Double ' f(Xi + e)
   Dim dblFXiMENOS As Double ' f(Xi - e)
   Dim dblDFDX As Double 'dF/dx evaluado en xi
   Dim intI As Integer

   Const EPSILON = 0.001

   'Itera sobre valores de X mediante el algoritmo de Newton-Raphson 
   'para   encontrar un X* tal que
   '|f(X*)-k| <= delta hasta un máximo de iteraciones especificadas

   'Declaramos variables intermedias
   Set rngFuncion = Range("Funcion")
   Set rngX = Range("X")
   dblK = Range("Objetivo").Value
   sngDelta = Range("Delta").Value
   intIteraciones = Range("Iteraciones").Value

   'Inicializamos las variables
   dblXi = rngX.Value 'valor entregado por el user
   dblFXi = EVALUARFUNCION(rngFuncion, rngX, dblXi)
   intI = 0

   Do Until Abs(dblFXi - dblK) <= sngDelta Or intI >= intIteraciones

      'Calculamos la derivada de f(Xi) en Xi
      dblFXiMENOS = EVALUARFUNCION(rngFuncion, rngX, dblXi - EPSILON)
      dblFXiMAS = EVALUARFUNCION(rngFuncion, rngX, dblXi + EPSILON)
      dblDFDX = (dblFXiMAS - dblFXiMENOS) / (2 * EPSILON)

     'Calculamos el nuevo Xi
      dblXi = dblXi + (dblK - dblFXi) / dblDFDX
      dblFXi = EVALUARFUNCION(rngFuncion, rngX, dblXi)

      intI = intI + 1

   Loop

   'Llegando a este punto ocurrió una de dos: o encontramos el valor    
   'o se acabaron las iteraciones

   If Abs(dblFXi - dblK) <= sngDelta Then
      Range("X").Value = dblXi
   Else
      MsgBox "No se encontró solución"  
      Range("X").Value = dblXi
   End If

End Sub

El núcleo del algoritmo anterior es el método de cálculo de derivadas mencionado dblDFDX = (dblFXiMAS – dblFXiMENOS) / (2 * EPSILON) , mientras que el nuevo método EVALUARFUNCION cuenta con tres parámetros: rngFuncion (la celda en Excel que almacena o redirecciona a la función a evaluar del modelo), rngX (la celda en Excel que contiene la variable independiente del modelo) y dblX (el valor en que queremos evaluar la función).

Un nuevo código para EVALUARFUNCION puede ser el siguiente:

Public Function EVALUARFUNCION(rngFormula As Range, rngX As Range, _
   dblX As Double) As Double

   'Evalúa la formula especificada cambiando donde diga X por dblX

   'Copiamos el valor preexistente en la variable independiente
   Dim dblAnterior As Double
   dblAnterior = rngX.Value

   'Reemplazamos el valor de la variable independiente por dblX
   rngX.Value = dblX

   'Leemos el nuevo valor que toma la función a evaluar
   Application.Calculate
   EVALUARFUNCION = rngFormula.Value

   'Devolvemos el valor preexistente en la variable independiente
   rngX.Value = dblAnterior

End Function

De esta forma, el código puede ser utilizado para encontrar el punto de equilibrio usando nuestra variable independiente cada vez que lo ejecutemos. Para ello, bien podemos usar un botón que lo ejecute cada vez que resulte necesario o podemos vincularlo a alguna otra acción realizada por el usuario para que se ejecute en forma «autónoma».

No es posible vincular la ejecución del procedimiento al método ‘Calculate’ de la hoja, pues genera una redundancia que nos llevaría a desbordar la memoria. El evento que me parece más idóneo es Worksheet_SelectionChange , que invocará al código cada vez que el usuario seleccione un lugar distinto de la hoja. Para ello incrustamos en la hoja que tenemos alojada el modelo las siguientes tres líneas:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

   If Range("Automatico").Value = "Sí" Then METODONEWTON

End Sub

Comparto, por ejemplo, una implementación simple en la evaluación de un flujo de caja. La sección en gris contiene las variables del modelo: X (C2) es la variable independiente y hacia ella apunta en este caso la celda B12 equivalente a la tarifa a cobrar. La celda B18 contiene el VAN como resultante del modelo, hacia ella apunta C3 que es la celda Función del algoritmo.

En la celda C7 hemos ubicado un Flag «Sí» / «No» que permite activar o desactivar la ejecución automática (Range ‘Automatico’), devolviéndole el control al usuario para realizar ajustes (como cambiar la variable independiente o la función) o bien para modelar fuera del punto de equilibrio dado por la variable «Objetivo».

¿Qué otras aplicaciones o mejoras se te vienen en mente? No dudes en dejar tus comentarios.

VBA Evaluar Derivada

Posted on by YorsH

Para ciertas aplicaciones en Excel, como la búsqueda de un valor objetivo a través de una función determinada, nos sería de gran utilidad poder conocer la derivada de un función cualquiera en un punto dado. Imaginemos por ejemplo, el siguiente problema:

Hemos llamado «x» al rango C2 donde podemos cambiar el valor de x sobre el cual queremos evaluar la función y su derivada. Mientras que en F(x), la celda C3, describimos la función a evaluar en términos de la variable anteriormente definida:

Si la fórmula F(x) es una función cualquiera, que puede ser cambiada por el usuario o mediante código, ¿cómo calcular la derivada dF/dx para cualquier x?

Comenzaremos suponiendo que F(x) es una función continua y diferenciable en x, lo que nos permitirá estimar una derivada en x mediante la suma y resta de un diferencial que llamaremos delta:

De forma tal que dF/dx evaluada en x0 estará dada por:

Lo primero que haremos será definir una función en VBA que nos permita evaluar una función cualquiera dada por el usuario para un valor de x arbitrario.

Utilizaremos para ello la función EVALUATE disponible en VBA, que nos permite calcular el valor de una fórmula existente en un rango. El truco consiste en combinar este método con un REPLACE que troque el valor de x por el valor deseado cada vez que lo encuentre en la función. El código en VBA queda como sigue:

Public Function EVALUARFUNCION(Funcion As Range, X As Double) As Double

    'Evalua la formula especificada reemplazando
    'donde diga 'X' por el valor especificado

    Dim Evaluacion As String
    
    Evaluacion = Replace(Funcion.Formula, "x", Str(X))
    EVALUARFUNCION = Evaluate(Evaluacion)
    
End Function

Podemos probar esta nueva función directamente en Excel, verificando que entregue el valor esperado:

Finalmente, podemos usar el código anterior para evaluar F(X) tanto en (x+delta) como en (x-delta) y computar de esta forma la derivada que buscamos. El código en VBA quedará, entonces, como sigue:

Public Function DERIVADA(Funcion As Range, X As Double, Delta As Single) _  
   As Double

   'Aproxima la derivada de la función evaluada en X
   'mediante un Delta suficientemente pequeño

   Dim FXmas As Double
   Dim FXmenos As Double

   FXmas = EVALUARFUNCION(Funcion, X + Delta)
   FXmenos = EVALUARFUNCION(Funcion, X - Delta)

   DERIVADA = (FXmas - FXmenos) / (2 * Delta)

End Function

Podemos probar nuestra nueva función directamente en Excel y ensayarla tanto con nuevos valores de x como con distintas funciones diferenciables en x.

¿Qué nuevas mejoras o aplicaciones se te vienen a la mente? ¡Nos vemos!

VBA Distribución Triangular

Posted on by YorsH

Distribución Triangular

La generación de valores aleatorios en Excel está limitada a las distribuciones que se encuentran disponibles para ser modeladas (Normal, Beta, Binomial, entre otras). Por ejemplo, la siguiente fórmula permite generar números aleatorios distribuidos normalmente en torno a una media y con una desviación estándar especificadas:

=NORM.INV(ALEATORIO(),media,desviacion)

En muchos modelos con pronósticos representados por variables aleatorias, sin embargo, se requiere una distribución sencilla y acotada que permita establecer valores mínimos, máximos y esperados. La Distribución Triangular es la que mejor se acomoda a este requerimiento, pero no se encuentra disponible entre las funciones de Excel.

El siguiente programa en VBA soluciona este problema, creando una nueva función TRIANGULAR(probabilidad, mínimo, máximo, esperado) que genera números aleatorios distribuidos de la forma deseada. El núcleo del algoritmo se basa en las ecuaciones:

Se comienza calculando un número k que representa la posición relativa del punto b (valor esperado) en el intervalo [a,c], por lo cual b pertenecerá al intervalo [0,1]. Luego se genera un valor aleatorio p que también estará entre [o,1[ y se verifica si está antes o después del valor esperado. Si p es menor que k, el valor aleatorio se sitúa a la derecha de a y como función de p. Si p es mayor que k, el valor aleatorio se sitúa a la izquierda de c y como función de (1-p).

De esta forma, la implementación en VBA es muy sencilla y se reduce al siguiente código que define la función TRIANGULAR (no olvidar insertar el código en un Módulo para que pueda ser invocada desde Excel).

Public Function TRIANGULAR(a As Double, b As Double, c As Double, _
   P As Single) As Double

   Dim k As Single
   Dim X As Double

   'Calculamos k
   k = (b - a) / (c - a)

   If P < k Then
      X = a + Sqr(P * (b - a) * (c - a))
   Else
      X = c - Sqr((1 - P) * (b - a) * (c - a))
   End If

   TRIANGULAR = X

End Function

En la planilla de ejemplo vemos un posible uso de la función recién creada, para proyectar valores inciertos de cantidad, costo y margen unitario. Se dispone en las celdas F5 y G5 de los valores máximos y mínimos respectivamente, mientras que en E5 se encuentra el valor esperado. El cuarto parámetro es el valor aleatorio entre [0,1[ que lo generamos mediante la función ALEATORIO() de Excel. La ventaja de hacerlo de esta manera es que generaremos nuevos valores aleatorios cada vez que presionemos F9.

En cambio, si quisiéramos que el valor aleatorio quedara estático (no se modificara en nuestra planilla al presionar al hacer nuevos cálculos), la solución está en eliminar este cuarto parámetro y calcular dentro del procedimiento el valor de P, insertando simplemente la instrucción.

P = RND()

¡Cuéntame qué nuevos usos o mejoras se te vienen en mente!