lunes, 25 de agosto de 2008

Auto Open y Auto Close

Existe una macro de autoarranque que se ejecuta cuando se abre el libro. Se llama auto_open().

Y existe otra que se ejecuta justo antes de cerrar el libro que se llama auto_close().

Código:

Sub Auto_Open()
Dim hora As Double
Dim saludo As String
hora = (Now - Int(Now)) * 24
Select Case hora
Case 6 To 14
saludo = "Buenos días"
Case 14 To 21
saludo = "Buenas tardes"
Case Else
saludo = "Buenas noches"
End Select
MsgBox saludo & " Amo"
End Sub

Equivalente a auto_open existe otra macro Workbook_Open, pero ésta ha de ser guardada no en un módulo normal, sino en ThisWorkbook. Pruebe lo siguiente:

El siguiente procedimiento permite abrir automáticamente el libro Balance.xls al abrir el libro Informe.xls. El procedimiento ha de estar en ThisWorkbook del libro Informe.xls.

Código:

Sub Workbook_Open()
'Apertura de libro Balance
Workbooks.Open Filename:="C:/Temp/Balance.xls"
'Activación del libro Informe
Windows("Informe.xls").Activate
End Sub

Impedir Repetidos

Descargar el fichero: NoRepe.xlsx

Mediante Datos, Validación podemos conseguir que dentro de un rango de celdas se impida escribir un dato repetido. Para ello será necesario que utilicemos una condición que utiliza la fórmula =CONTAR.SI. Lo que haremos es crear una validación personalizada, esto es, con fórmula.


Dentro de Datos, Validación se elige 'Personalizada' y la fórmula que se utiliza es la siguiente:

=CONTAR.SI($B$8:$G$15;+B8)=1

Si en la pestaña 'Mensaje de Error' se elige 'Advertencia' no se impide la entrada de un dato repetido y simplemente se avisa.

En este caso, Excel 2007, se puede lugo utilizar Datos, Validación de Datos, Rodear con un círculo datos no válidos y así podemos identificar los datos repetidos.Otro aspecto a considerar es que la Validación de Datos únicamente es efectiva cuando se escriben los valores en las celdas, pero si éstas se copian la validación no es efectiva.

Función que elige una al azar

Descargar el fichero: TomaUna.xlsm

Disponemos de un rango de celdas con diferentes valores, por ejemplo, ciudades. Se trata de diseñar una función que permita elegir al azar un valor de esa lista. Programaremos la función en el Editor de Visual Basic, de esta forma estará disponible en el Asistente de Funciones en la categoría de Funciones creadas por el usuario.


La función es la siguiente.



Function UNA(LISTA As Range)
Dim n As Long
Dim ale As Long
Randomize
n = LISTA.Count
ale = Int(Rnd * n) + 1
UNA = LISTA.Value2(ale, 1)
End Function

Randomize hace que los valores sean algo más aleatorios. Es aconsejable utilizarlo siempre que se use Rnd que es para calcular un valor aleatorio uniforme entre cero y uno.

Randomize se pronuncia [Randomais]

miércoles, 20 de agosto de 2008

Tablas en Excel

Excel 2007 incorpora una nueva utilidad para crear tablas. En Insertar, Tabla podemos indicar el rango de nuestra tabla ya creada o de nuestra futura tabla. Si la tabla lleva encabezado (cosa totalmente recomendable) se ha de indicar marcando la casilla de verificación donde pone: "La tabla tiene encabezados".



Primero creamos los encabezados (si es que no estan ya creados). Y veras que los convierte en desplegables, añadiéndoles un Autofiltro.


Veras que ha aparecido una nueva ficha en la cinta de herramientas denominada 'Herramientas de Tabla' justo encima de Diseño.

lunes, 18 de agosto de 2008

La función TEXTO

La función TEXTO permite convertir un valor numérico en un texto. La función contraria es =VALOR. Podemos comprobar que al escribir un número en una celda queda alineado a la derecha, y si se escribe como texto (anteponiéndole una comilla simple) quedará alineado a la izquierda. Ejemplo: '54

La función TEXTO tiene dos argumentos que según la ayuda son:

=TEXTO(valor;formato)

valor: es el número que queremos convertir a texto
formato: es el formato que queremos darle. Es un argumento optativo

Pero la ayuda no se corresponde con la realidad de la función, ya que en la práctica esta función tiene los argumentos cambiados de orden.

Pruebe =TEXTO(;"43")
Pero si prueba =TEXTO("43") obtendrá un error

Otro ejemplo.

Prube a introducir la expresión siguente:
=TEXTO("2-11";"mmm-dd")
obtendrá: nov-02. Aquí si funciona bien la función TEXTO, tal y como esta definida en la ayuda.

Pero si introduce la expresión siguiente:
=TEXTO(;"2-11")
obtendrá: 2-11

Quite las comillas y vea lo que se obtiene con la expresión:
=TEXTO(;2-11)
obtendrá: -9.

Organizar la vista de varios libros (Extensión .xlw)

Podemos tener varios libros abiertos y verlos simultáneamente en mosaico, en vista horizontal o vetical, en cascada. Si deseamos podemos grabar esta forma personalizada de verlos para la próxima vez. Esto se hace grabando un fichero con extensión .xlw. La extensión es el acrónimo de eXceL Workbook


Para organizar las ventanas:
* En Excel 2007: Vista, Organizar todo


Para guardar el fichero xlw
* En Excel 2007: Vista, Guardar área de trabajo

viernes, 15 de agosto de 2008

Random Walk LogNormal

Un Random Walk es un paseo aleatorio o camino aleatorio. Se genera con alguna función cuya función de distribución de probabilidad nos permita generar números aleatorios que se apoyan en el valor anteriormente calculado. Podríamos utilizar una simple distribución uniforme (vease otro post anterior), pero en este caso vamos ha utilizar una ditribución LogNormal, o tambíen denominada LogoNormal. En Excel existe una función que genera números que se ajustan precisamente a una distribución Lognormal.
=DISTR.LOG.INV(probabilidad;media;desv_estándar)
=DISTR.LOG.INV(0,6;LN(10);0,5)

cuyo resultado es: 11,3504642

Con esta función podríamos crear una serie temporal que se comporte como un Random Walk (paseo aleatorio). Por ejemplo, si en la celda B1 ponemos el primer valor (10) y en la celda B2 ponemos la fórmula:

=DISTR.LOG.INV(ALEATORIO();LN(B1);0,02)
copiando la celda B2 hasta la B1000, obtendríamos una serie temporal de mil datos cuyos incrementos se comportan según una distribución Lognormal.

Esto que hemos realizado se podría hacer con una macro como la siguiente.


Código:

Sub serie1()
Dim A() As Double
Dim n
Dim i
n = 1000
ReDim A(n)
Randomize
A(0) = 10
For i = 1 To n
A(i) = Application.WorksheetFunction.LogInv(Rnd, LN(A(i - 1)), 0.02)
Next i
For i = 0 To n
Cells(i + 1, "B") = A(i)
Next i
End Sub

Function LN(x)
LN = Log(x) / Log(Exp(1))
End Function

Una variante de la macro anterior es la siguiente.


Código:

Sub serie2()
Dim A As Variant
Dim n
Dim i
n = 1000
Randomize
Range("B1") = 10
A = [B1:B1001]
For i = 2 To n
A(i, 1) = Application.WorksheetFunction.LogInv(Rnd, LN(A(i - 1, 1)), 0.02)
Next i
[B1:B1001] = A
End Sub

Function LN(x)
LN = Log(x) / Log(Exp(1))
End Function

jueves, 14 de agosto de 2008

Simulación de una Distribución Normal

En Excel disponemos de una función de distribución que genera directamente valores ajustados a una distribución normal, pero en esta ocasión vamos a generarlos sin recurrir a las funciones de Excel. La denominada transformada de Box-Müller permite generar una distribución Normal. Para ello se utilizan dos distribuciones Uniformes [0;1], que en Excel se obtienen con la función:

=ALEATORIO()


La función es la siguiente:

=+RAIZ((-2*LN(ALEATORIO())))*SENO(2*PI()*ALEATORIO())


Podríamos programar una función que nos de una Normal de media mu y desviación típica sigma.


Código:

Function xNORMAL(mu, sigma)
Dim NORMAL01
Const Pi As Double = 3.14159265358979
Randomize
NORMAL01 = Sqr((-2 * LN(Rnd))) * Sin(2 * Pi * Rnd)
xNORMAL = mu + sigma * NORMAL01
End Function

Function LN(x)
LN = Log(x) / Log(Exp(1))
End Function

Por ejemplo, la función =xNORMAL(100;2) nos proporcionará un número aleatorio que se ajusta a una distribución normal de media 100 y desviación típica 2.

Edad

Para determinar la edad de alguien podemos poner su fecha de nacimiento en la celda A1 y emplear la siguiente fórmula:
=SIFECHA(A1;HOY();"Y")&" Años, "&SIFECHA(A1;HOY();"Ym")&" Meses, "&SIFECHA(A1;HOY();"Md")&" Dias""

La función =HOY() nos da la fecha del sistema.

Último día del mes

Para determinar el último día del mes de febrero de 2008, podemos emplear esta fórmula:

=FECHA(2008;3;1)-1


o bien, esta otra:

=FECHA(2008;3;0)


Observar, que el día que se pone es CERO.

El resultado es: 29/02/2008

y así nos damos cuenta de que el año 2008 es bisiesto.

Actualización


En versiones más recientes de Excel ya disponemos de una función que calcula el último día del mes. Se llama:

=FIN.MES(fecha_inicial;mes)

Si en el argumento mes ponemos cero nos estamos refiriendo al mes de la fecha_inicial, y si ponemos 2 nos referimos a dos meses más respecto a la fecha_inicial.

Por ejemplo, si usted escribe la siguiente función de función sabrá cuantos días tiene el mes actual.

=FIN.MES(HOY();0)

La función =HOY() nos da la fecha del sistema. Si su ordenador está correctamente puesto en fecha la función HOY proporciona el día actual.

jueves, 7 de agosto de 2008

Eliminar Filas con Macro

Para eliminar filas alternas, bien sean las pares o bien las impares, se puede utilizar esta macro.




Sub EliminaFilas()
'Elimina filas alternas (pares o impares)
For i = 5 To 3000
    Cells(i, "A").EntireRow.Delete
Next i
End Sub


El contador i va de uno en uno ya que al eliminar una fila la que se encontraba en la siguiente posición alterna, ahora es ha convertido en contígua.

Cells(fila, columna). En este caso ponemos la columna A, en lugar de con el número 1, que es lo habitual, lo hacemos con la expresión "A".

Comenzamos en i=5, esto hace que se eliminen las filas impares que comienzan en la fila 5. Si hubiéramos puesto i=6 entonces se comenzaría en la fila 6 y se eliminarían las filas pares.

Si quieres hacer una prueba puedes obtener las cotizaciones de algún valor del Ibex desde la página de Invertia. Verás que al pasarlo a Excel te quedan filas en blanco interpuestas entre las filas que contienen las cotizaciones. Puedes probar la macro para eliminar las filas en blanco. Para ello elige un valor de Ibex y en 'Cotizaciones históricas' podras ver una tabla con los datos de las cotizaciones. Esta tabla la puedes seleccionar con el ratón, copiay y pegar en Excel. Otro inconveniente es que vienen muchos objetos incrustados que puedes eliminar con F5 (ir a), Especial, Objetos y luego pulsa la tecla Suprimir, de esta forma eliminaras todos estos molestos objetos.

miércoles, 6 de agosto de 2008

Random Walk

Descargar el fichero: RandomWalk.xlsm


Para que el fichero se abra correctamente debe tener habilitadas las macros.

Un Random Walk es un Paseo Aleatorio o Camino aleatorio. Se trata de una serie temporal de valores obtenidos de forma aleatoria que se caracterizan por que son independientes unos respecto a los previos.


Este concepto es muy importante en finanzas. Si la bolsa se comporta según un Random Walk se cumple que el mercado es eficiente y se dice que la bolsa no tiene memoria.


  • Hoja1. Resuelto con la función SI donde se analiza si la función ALEATORIO() es mayor o menor de 0,5. Esto equivale a tirar una moneda a cara o cruz, para decidir si el valor se incrementa o se reduce.
  • Hoja2. Resuelto con la función que nos proporciona la distribución normal inversa. Ahora el resultado es más suave, no es tan drástico.
  • Hoja3. Es un caso similar al anterior pero ahora se utiliza la inversa de la distribución lognormal. La ventaja de este sistema es que se evita que aparezcan valores negativos. Por ejemplo, si no deseamos que aparezca un precio negativo esta sería la función adecuada.
  • Hoja4. Este es un ejemplo donde se utiliza la inversa de la distribución normal aplicada a tipos de interés.