ACTUALIZACION - HOW TO CREATE A PORTFOLIO OPTIMIZER - COMO CREAR UN OPTIMIZADOR DE CARTERAS

En esta actualización de la entrada anterior; se incorporan las funciones auxiliares necesarias para completar el optimizador de carteras y para los que no queráis perder mucho tiempo con la fase de diseño, os incluyo mas abajo un link en el que podéis descargaros el optimizador para Excel ya completo y funcional.

In this update of the previous post, are incorporated auxiliary functions necessary to complete the portfolio optimizer and for those who do not wish to lose a lot of time with the design phase, I include below a link where you can download the Excel optimizer and fully functional.

MDA Portfolio Optimizer Download

FUNCIONES AUXILIARES QUE UTILIZA EL OPTIMIZADOR:

'PRODUCTO DE DOS MATRICES
'PRODUCT OF TWO MATRIX
Public Function MATRIZP(MATRA(), MATRB()) As Variant
ReDim MATRIZ(UBound(MATRA, 1), UBound(MATRB, 2))
For Z = 1 To UBound(MATRB, 2)
For X = 1 To UBound(MATRA, 1)
SUMA = 0
For Y = 1 To UBound(MATRA, 2)
SUMA1 = MATRA(X, Y) * MATRB(Y, Z)
SUMA = SUMA1 + SUMA
Next Y
MATRIZ(X, Z) = SUMA
Next X
Next Z
MATRIZP = MATRIZ
End Function

'SUMA DE DOS MATRICES
'SUM OF TWO MATRIX
Public Function MATRIZS(MATRA(), MATRB()) As Variant
ReDim MATRIZ(UBound(MATRA, 1), UBound(MATRB, 2))
For X = 1 To UBound(MATRA, 1)
For Y = 1 To UBound(MATRA, 2)
MATRIZ(X, Y) = MATRA(X, Y) + MATRB(X, Y)
Next Y
Next X
MATRIZS = MATRIZ
End Function

'TRASPUESTA DE UNA  MATRIZ
'TRANSPOSE OF A MATRIX
Public Function MATRIZT(MATRA()) As Variant
ReDim MATRIZ(UBound(MATRA, 2), UBound(MATRA, 1))
For X = 1 To UBound(MATRA, 1)
For Y = 1 To UBound(MATRA, 2)
MATRIZ(Y, X) = MATRA(X, Y)
Next Y
Next X
MATRIZT = MATRIZ
End Function

'INVERSA DE UNA  MATRIZ
'INVERSE OF A MATRIX
Public Function MATRIZI(MATRA()) As Variant
ReDim MATRIZ(UBound(MATRA, 1), (UBound(MATRA, 2) * 2))
For X = 1 To UBound(MATRA, 2)
For Y = 1 To UBound(MATRA, 1)
MATRIZ(Y, X) = MATRA(Y, X)
Next Y
Next X
For X = 1 To UBound(MATRA, 2)
MATRIZ(X, UBound(MATRA, 2) + X) = 1
Next X
'Proceso de arriba a abajo
'Process from top to bottom
For T = 1 To UBound(MATRA, 2)
F = T
DIVISOR = MATRIZ(F, F)
For X = 1 To UBound(MATRIZ, 2)
MATRIZ(F, X) = MATRIZ(F, X) / DIVISOR
Next X
For Y = F + 1 To UBound(MATRIZ, 1)
MULT = MATRIZ(Y, F)
For X = 1 To UBound(MATRIZ, 2)
MATRIZ(Y, X) = MATRIZ(Y, X) - MULT * MATRIZ(F, X)
Next X
Next Y
Next T
'Proceso de abajo a arriba
'Bottom-up process
For T = UBound(MATRA, 2) - 1 To 1 Step -1
F = T
For Y = F To 1 Step -1
MULT = MATRIZ(Y, F + 1)
For X = UBound(MATRIZ, 2) To 1 Step -1
MATRIZ(Y, X) = MATRIZ(Y, X) - (MULT * MATRIZ(F + 1, X))
Next X
Next Y
Next T
For X = 1 To UBound(MATRA, 2)
For Y = 1 To UBound(MATRA, 1)
MATRA(Y, X) = MATRIZ(Y, X + UBound(MATRA, 2))
Next Y
Next X
MATRIZI = MATRA
End Function

'PRODUCTO DE UNA MATRIZ POR UN VECTOR
'PRODUCT OF A MATRIX BY A VECTOR
Public Function MATVECTP(MATRA(), MATRB()) As Variant
ReDim MATRIZ(UBound(MATRA, 1), UBound(MATRB, 2))
For Z = 1 To UBound(MATRB, 2)
For X = 1 To UBound(MATRA, 2)
SUMA = 0
For Y = 1 To UBound(MATRA, 2)
SUMA1 = MATRA(X, Y) * MATRB(Y, Z)
SUMA = SUMA1 + SUMA
Next Y
MATRIZ(X, Z) = SUMA
Next X
Next Z
MATVECTP = MATRIZ
End Function

'DIFERENCIA DE DOS MATRICES
'DIFFERENCE OF TWO MATRIX
Public Function MATRIZD(MATRA(), MATRB()) As Variant
ReDim MATRIZ(UBound(MATRA, 1), UBound(MATRB, 2))
For X = 1 To UBound(MATRA, 1)
For Y = 1 To UBound(MATRA, 2)
MATRIZ(X, Y) = MATRA(X, Y) - MATRB(X, Y)
Next Y
Next X
MATRIZD = MATRIZ
End Function

Espero os sea de utilidad ... I hope you find it useful.