viernes, 24 de octubre de 2008

Transferir los datos de un Rango a un Array

Descargar el fichero pasa_matriz.xlsm.

Deseamos transferir los datos que existen en el rango de una hoja a una matriz (ARRAY) mediante una macro. Vamos a verlo por varios métodos. El primer método es el clásico, cargando dato a dato en la matriz. Otro método nos permite cargar la matriz de una sola vez. También vamos a conocer cómo se determina la dimensión de una matriz.

Método Clásico

Podemos utilizar el método clásico que consiste en tomar los datos uno a uno del rango e ir alimentando la matriz. Esto se puede hacer con bucles FOR..NEXT.

Imagine que el rango es B4:D13 y esta lleno de datos (algunos numéricos y otros alfanuméricos). Queremos trasladar todos los datos a la matriz X de dimensión X(1 to 10, 1 to 3).

Utilicemos el método clásico consistente en pasar los datos del rango a la matriz de uno en uno. Esto se hace utilizando, en este caso, dos bucles FOR..NEXT anidados.
Sub pasardatos()
Dim MiMatriz(1 To 10, 1 To 3)
Dim i As Integer, j As Integer
For i = 1 To 10
  For j = 1 To 3
    MiMatriz(i, j) = Worksheets("Hoja1").Cells(3 + i, j + 1).Value
  Next j
Next i
End Sub
El otro método es pasar el rango de datos de una sola vez a la matriz.

Pruebe con esto:
MiMatriz = [B4:D13]
o bien
MiMatriz = Range("B4:D13")
y tambien funciona al reves:
[F4:H13] = MiMatriz
o bien
Range("F4:H13") = MiMatriz
Se ha de tener en cuenta que si declaras la matriz como una matriz dará error.

Declarando la variable como una matriz no funciona.

Me explico. Si prueba las cuatro siguientes macros:
Sub Matrix1()
Dim X(11, 5)
X = Range("B4:D13")
End Sub

Sub Matrix2()
Dim X As Double
X = Range("B4:D13")
End Sub

Sub Matrix3()
Dim X As Variant
X = Range("B4:D13")
End Sub

Sub Matrix4()
Dim X
X = Range("B4:D13")
End Sub
comprobara que las dos primeras macros dan error. Las que funcionan bien son la tercera y la cuarta.

Si lo que queremos es tomar un valor de la matriz (por ejemplo el primero, el de la celda B4) y luego dejarlo en la celda H1 hacemos lo siguiente.
Sub test()
Dim x
x = [B4:D13]
[H1]= x(1, 1)
End Sub

¿Cómo se puede conocer la dimensión de la matriz creada?

Supongamos que creo una función, como esta:
Function MiRef(Rng)
Dim X
X = [Rng]
'......
End Function
Para tratar los elementos de la matriz debo conocer su dimensión. Por
ejemplo, sería interesante saber si tiene 10 filas y 4 columnas, o la
dimensión de que se trate. ¿Cómo saber esto?.

Disponemos de dos métodos:

1) Tomar las dimensiones del rango a la hora de asignar:
Function test1(rng As Range) As String
Dim Matriz, x As Long, y As Long
Matriz = rng
x = rng.Columns.Count
y = rng.Rows.Count
test1 = "Matriz ( 1 To " & y & " , 1 To " & x & ")"
End Function
2) Tomar las dimensiones de la matriz:
Function test2(rng As Range) As String
Dim Matriz, x As Long, y As Long
Matriz = rng
x = UBound(Matriz, 2)
y = UBound(Matriz, 1)
test2 = "Matriz ( 1 To " & y & " , 1 To " & x & ")"
End Function

También puede interesarle ...

jueves, 16 de octubre de 2008

Ordenar Abecedario

Descargar el fichero: abecedario.xlsm

Se trata de un juego desarrollado en Excel. Consiste en ordenar afabéticamente 5 letras (ABCDE) que previamente nos dan desordenadas. Existen 10 niveles de dificultad. Lo primero que hemos de hacer es elegir el nivel de dificultad y luego ordenar las letras utilizando los bonotes: 1, 2, 3 y 4, que permuta cada uno de ellos un par de letras contíguas.


En la Hoja 2 disponemos de todas las permutaciones posibles que se pueden llegar a conseguir con las cinco letras. Se encuentran agrupadas según nivel de dificultad. Así el nivel 1 supone que el juego se resuelve pulsando un único botón (1, 2, 3 ó 4). De nivel 1 existen 4 posibles permutaciones (en color amarillo). De nivel 2 existen 9 posibles permutaciones (color rosa). De nivel 3 existen 15 posibles permutaciones (color verde). Y así hasta llegar a nivel 10 en el que la única combinación de letras es: EDCBA.


Para poder hacer operativo el juego se han de utilizar diversas macros. Unas para generar de forma aleatoria la combinación de letras elegida inicialmente. Por ejemplo, si el jugador elige nivel 3, el juego presenta de forma aleatoria una de entre las 15 posibles permutaciones que corresponden a este nivel.La macro 'genera' rellena la columna K de la Hoja 2 con las 120 permutaciones posibles que se obtienen al combinar 5 caracteres.



Las permutaciones en este caso se realizaan con números y no con letras. Posteriormente se transformaran esos números en letras.



Existe otra forma de obtener las 120 permutaciones de 5 letras. Esto se hace en la Hoja 3 utilizando la macro denominada 'MisPermutaciones'. No es un método muy ortodoxo pero funciona. Se basa en exigir que se cumpla que el producto sea el factorial de 5 (5!=120) y que la suma sea 15.






En el siguiente audio se comenta este juego.

sábado, 4 de octubre de 2008

Simulación de Montecarlo: aplicación financiera

Descargar el fichero: simulabono.xls

En este fichero de Excel realizamos un caso de simulación de Montecarlo aplicado a Renta Fija. Más concretamente, lo que hacemos es trabajar con bonos en los que el cupón anual no es de cuantía cierta sino aleatoria. El cupón se ajusta a una distribución normal de media 500 y desviación 50. N(500;50).



=DISTR.NORM.INV(ALEATORIO();500;50)






Conocida la ETTI (Estructura Temporal de los Tipos de Interes) o Curva de Tipos podemos calcular el precio de un bono. Para el bono A (celda amarilla) el precio se calcula con la siguiente expresión:




{=-SUMA((Flujosa/(1+ETTI)^Tiempo))}



Los corchetes indican que se trata de una fórmula matricial que se introduce no pulsando INTRO, sino pulsando Control+Mayúsculas+INTRO.



En la celda F5 (color azul) indicamos en cuantos años madura el Bono B, utilizamondo la fórmula:



=ALEATORIO.ENTRE(8;9)


Esta fórmula genera números aleatorios enteros entre un mínimo y un máximo. En este caso hemos escrito entre 8 y 9. Se utiliza para indicar la duración variable en años del bono B.


Si la fórmula fuera =ALEATORIO.ENTRE(9;9) no supondría variación aleatoria, ya que el número de años de duración sería siempre 9.


Al pulsar la tecla F9 (recálculo manual) los valores aleatorios se recalculan. Esto supone que cambien el importe de los cupones de los bonos e incluso la duración del Bono B. Lo que implica que la TIR (Tasa Interna de Rentabilidad) que expresa la rentabilidad del bono sea una variable aleatoria.



Vamos a estudiar la TIR del Bono B. Al tratarse de una variable aleatoria la identificaremos estudiando su distribución de probabilidad. Por el Teorema Central del Límite sabemos que al trabajar con múltiples funciones de distribución, en el caso extremo (cuando n tiende a infinito, o en este caso cuando el número de iteraciones es suficientemente grande) casi siempre la distribución que se obtienen en una Normal o Campana de Gauss.


Para generar las iteraciones, recalculamos la TIR del Bono B un elevado número de veces, por ejemplo 10.000 y anotamos en al columna H las concrecciones de la TIR así obtenida. Con esos 10.000 valores de TIR efectuamos un análisis de frecuencias, creando un histograma de frecuencias tal y como se vió en un post anterior. El análisis del histograma nos permite ajustar la distribución a una Normal, cuya media y varianza podemos calcular.

En el caso de que la duración del Bono B sea aleatoria entre 8 y 9, obtenemos dos Campanas de Gauss.




Utilizamos una macro para ir anotando en la columna H las diferentes TIR que se obtienen en las iteraciones.



Sub montecarlo()
Dim n 'número de interaciones
Dim i
Application.ScreenUpdating = False
n = InputBox("Introduzca el número de iteraciones", _
"Entrada de datos", 10000)
For i = 1 To n
Cells(i + 5, 8) = Range("F19")
Next i
Application.ScreenUpdating = True
End Sub

miércoles, 1 de octubre de 2008

Glosario: Traducción de funciones


Muchas empresas trabajan con Excel en inglés para poder reportar informes a la matriz. Otro motivo por el que se necesita conocer la traducción de funciones español-inglés es porque al programar Macros todas las funciones a las que llamemos han de estar en inglés.

Los ficheros glosario.xls y glosarioALL.xls tienen la traducción entre español y diversos idiomas. La diferencia entre ambos ficheros radica en que el pimero tien únicamente las funciones básicas, y el segundo (glosarioALL.xls) contiene además de las básicas las complementarias. Por ejemplo, la función TIR.NO.PER que es una función financiera que calcula la TIR con fechas, es una función que complementaria.

Seguidamente damos un listado de las funciones básicas.



English Spanish
ABS ABS
ACOS ACOS
ACOSH ACOSH
NOW AHORA
RAND ALEATORIO
AREAS AREAS
ASIN ASENO
ASINH ASENOH
ATAN ATAN
ATAN2 ATAN2
ATANH ATANH
YEAR AÑO
DCOUNT BDCONTAR
DCOUNTA BDCONTARA
DSTDEV BDDESVEST
DSTDEVP BDDESVESTP
DGET BDEXTRAER
DMAX BDMAX
DMIN BDMIN
DPRODUCT BDPRODUCTO
DAVERAGE BDPROMEDIO
DSUM BDSUMA
DVAR BDVAR
DVARP BDVARP
CRITBINOM BINOM.CRIT
LOOKUP BUSCAR
HLOOKUP BUSCARH
VLOOKUP BUSCARV
CHAR CARACTER
CELL CELDA
CODE CODIGO
CORREL COEF.DE.CORREL
SKEW COEFICIENTE.ASIMETRIA
RSQ COEFICIENTE.R2
MATCH COINCIDIR
COLUMN COLUMNA
COLUMNS COLUMNAS
COMBIN COMBINAT
CONCATENATE CONCATENAR
COUNT CONTAR
COUNTBLANK CONTAR.BLANCO
COUNTIF CONTAR.SI
COUNTA CONTARA
COS COS
COSH COSH
COVAR COVAR
GROWTH CRECIMIENTO
QUARTILE CUARTIL
KURT CURTOSIS
DB DB
DDB DDB
FIXED DECIMAL
RIGHT DERECHA
OFFSET DESREF
STDEV DESVEST
STDEVP DESVESTP
DEVSQ DESVIA2
AVEDEV DESVPROM
DAY DIA
DAYS360 DIAS360
WEEKDAY DIASEM
ADDRESS DIRECCION
WEIBULL DIST.WEIBULL
BETADIST DISTR.BETA
BETAINV DISTR.BETA.INV
BINOMDIST DISTR.BINOM
CHIDIST DISTR.CHI
EXPONDIST DISTR.EXP
FDIST DISTR.F
FINV DISTR.F.INV
GAMMADIST DISTR.GAMMA
GAMMAINV DISTR.GAMMA.INV
HYPGEOMDIST DISTR.HIPERGEOM
LOGINV DISTR.LOG.INV
LOGNORMDIST DISTR.LOG.NORM
NORMDIST DISTR.NORM
NORMSDIST DISTR.NORM.ESTAND
NORMSINV DISTR.NORM.ESTAND.INV
NORMINV DISTR.NORM.INV
TDIST DISTR.T
TINV DISTR.T.INV
VDB DVS
CHOOSE ELEGIR
FIND ENCONTRAR
INT ENTERO
STEYX ERROR.TIPICO.XY
ISBLANK ESBLANCO
ISERR ESERR
ISERROR ESERROR
ISLOGICAL ESLOGICO
ISNA ESNOD
ISNONTEXT ESNOTEXTO
ISNUMBER ESNUMERO
TRIM ESPACIOS
ISREF ESREF
ISTEXT ESTEXTO
LINEST ESTIMACION.LINEAL
LOGEST ESTIMACION.LOGARITMICA
EXP EXP
MID EXTRAE
FACT FACT
DATE FECHA
DATEVALUE FECHANUMERO
ROW FILA
ROWS FILAS
FISHER FISHER
FREQUENCY FRECUENCIA
GAMMALN GAMMA.LN
DEGREES GRADOS
SEARCH HALLAR
HOUR HORA
TIMEVALUE HORANUMERO
TODAY HOY
REGISTER.ID ID.REGISTRO
EXACT IGUAL
INDEX INDICE
INDIRECT INDIRECTO
INFO INFO
INTERCEPT INTERSECCION
CONFIDENCE INTERVALO.CONFIANZA
LEFT IZQUIERDA
RANK JERARQUIA
LARGE K.ESIMO.MAYOR
SMALL K.ESIMO.MENOR
LEN LARGO
CLEAN LIMPIAR
CALL LLAMAR
LN LN
LOG LOG
LOG10 LOG10
MAX MAX
UPPER MAYUSC
MDETERM MDETERM
TRIMMEAN MEDIA.ACOTADA
HARMEAN MEDIA.ARMO
GEOMEAN MEDIA.GEOM
MEDIAN MEDIANA
MONTH MES
MIN MIN
LOWER MINUSC
MINUTE MINUTO
MINVERSE MINVERSA
MMULT MMULT
MODE MODA
DOLLAR MONEDA
FLOOR MULTIPLO.INFERIOR
CEILING MULTIPLO.SUPERIOR
N N
NEGBINOMDIST NEGBINOMDIST
NOT NO
NA NOD
PROPER NOMPROPIO
STANDARDIZE NORMALIZACION
NPER NPER
TIME NSHORA
ROMAN NUMERO.ROMANO
OR O
PMT PAGO
IPMT PAGOINT
PPMT PAGOPRIN
PEARSON PEARSON
SLOPE PENDIENTE
PERCENTILE PERCENTIL
PERMUT PERMUTACIONES
PI PI
POISSON POISSON
POWER POTENCIA
PROB PROBABILIDAD
PRODUCT PRODUCTO
AVERAGE PROMEDIO
FORECAST PRONOSTICO
CHITEST PRUEBA.CHI
CHIINV PRUEBA.CHI.INV
FTEST PRUEBA.F
FISHERINV PRUEBA.FISHER.INV
TTEST PRUEBA.T
ZTEST PRUEBA.Z
RADIANS RADIANES
SQRT RAIZ
PERCENTRANK RANGO.PERCENTIL
ODD REDONDEA.IMPAR
EVEN REDONDEA.PAR
ROUND REDONDEAR
ROUNDUP REDONDEAR.MAS
ROUNDDOWN REDONDEAR.MENOS
REPLACE REEMPLAZAR
REPT REPETIR
MOD RESIDUO
SECOND SEGUNDO
SIN SENO
SINH SENOH
IF SI
SIGN SIGNO
SLN SLN
SUBTOTAL SUBTOTALES
SUM SUMA
SUMSQ SUMA.CUADRADOS
SUMPRODUCT SUMAPRODUCTO
SUMIF SUMAR.SI
SUMX2PY2 SUMAX2MASY2
SUMX2MY2 SUMAX2MENOSY2
SUMXMY2 SUMAXMENOSY2
SUBSTITUTE SUSTITUIR
SYD SYD
T T
TAN TAN
TANH TANH
RATE TASA
TREND TENDENCIA
TEXT TEXTO
TYPE TIPO
ERROR.TYPE TIPO.DE.ERROR
IRR TIR
MIRR TIRM
TRANSPOSE TRANSPONER
TRUNC TRUNCAR
PV VA
VALUE VALOR
VAR VAR
VARP VARP
FV VF
NPV VNA
AND Y
FALSE FALSO
TRUE VERDADERO