domingo, 31 de marzo de 2019

El correcto uso de INDIRECTO (I)

En ocasiones tenemos fórmulas en nuestras hojas de Excel y estas casi siempre hacen referencia a otras celdas e incluso en ocasiones hacen referencia a otros libros de Excel. Pero ¿qué pasa cuando cambiamos de ubicación los datos de la celda de la referencia? Menudo problema que en ocasiones solucionamos cambiando o editando todas nuestras fórmulas lo que muchas veces nos toma más tiempo del que quisiéramos o que incluso a veces ocasiona que cometamos errores.

¿Hay alguna forma de facilitar todo esto? Por suerte para todos/as existe INDIRECTO. Dicha función, al ser aplicada en una fórmula, nos devuelve el contenido de la referencia especificada y nos muestra su contenido y/o no es útil al ser “entendida” por dicha fórmula. Sí, sí, quizá resulte un poco enredado entenderlo así que mejor vayamos con los ejemplos. Ah, pero antes, veamos que dice Microsoft sobre los argumentos que usa dicha función:

INDIRECTO (Ref, A1)

Ref (obligatorio): Es la referencia especificada como una cadena de texto.

A1 (opcional): Valor lógico que indica el tipo de referencia especificada: A1 (verdadero) o F1C1 (falso).

Ahora si vamos con los ejemplos, uno bastante simple para comenzar a entender INDIRECTO. Primero escribamos lo que sea en A10, yo colocaré “Hola a todos/as”:


Ahora, vamos a colocar la siguiente fórmula en A1:

=INDIRECTO("A10")

Como la referencia debe ser una cadena de texto, colocamos el A10 entre comillas ¿cuál es el resultado? Pues:

Aquí hemos hecho referencia directamente a una celda (A10) pero ¿qué pasa si lo que queremos es colocar la dirección de una celda que vaya variando? Para ese caso vamos ahora a escribir “B1” (sin las comillas) en la celda A10 y en B1 colocaremos “Hola a todos/as”. Por último, colocaremos lo siguiente en A1:

=INDIRECTO(A10)

Como lo que queremos obtener es el contenido de B1 y en la celda A10 hay texto, nuestra referencia es al contenido de dicha celda A10 no a A10 en específico, por lo que lo he colocado sin las comillas en la formula.

Sé que se leyó medio enredado, pero como para que se entienda, y aunque no es exacto, podríamos decir lo siguiente:

  • Si queremos obtener el contenido de una celda con INDIRECTO, la referencia (ref) va entre comillas
  • Si queremos obtener el contenido de una celda indicada en otra con INDIRECTO, la referencia (ref) va sin comillas

Un ejemplo más sobre ese punto para que se entienda mejor. Usaremos los mismos datos solo que en la celda A10 en lugar de “B1”, dejemos solo el número uno (1) y en A1 modifiquemos la fórmula así:

=INDIRECTO("B" & A10)

¿Mejor? Espero que sí. En un siguiente artículo seguiremos con el uso de esta función. Hasta la próxima.

Abraham Valencia

martes, 26 de marzo de 2019

Cálculos con fechas: El uso de SIFECHA

Es muy cotidiano que nuestros datos en Excel contengan fechas y que tengamos que realizar algún tipo de operación con ellas; si bien Excel tiene varias funciones para ello hay una que no aparece en la lista de aquellas (no está “documentada”) y que incluso al ser usada en una celda no despliega ningún tipo de “ayuda” (“Intellisense”). Estamos hablando de “SIFECHA”. Al no ser una función "nativa" de Excel (es, por decirlo de un modo, una función "heredada" de Lotus) dicha función no está "documentada" como ya comenté (sí fue “documentada” en Excel 2000) pero a pesar de eso es compatible con todas las versiones de Excel.

Probablemente “SIFECHA” es la función que requiere menos trabajo, en su uso, para los cálculos con fechas. Dicha función nos puede dar el resultado de la diferencia de días, meses, años, o la combinación de ellos, que hay entre dos fechas. Para que se entienda, vamos a ver que argumentos usa dicha función para lo que usaremos la información que brinda Microsoft:

SIFECHA(fecha_inicial;fecha_final;unidad)

Fecha_inicial: Una fecha que representa la primera fecha del período o la fecha inicial. Las fechas pueden escribirse como cadenas de texto entre comillas (por ejemplo, "30/01/2001") como números de serie (por ejemplo, 36921, que representa el 30 de junio de 2001, si usa el sistema de fechas de 1900), o bien como resultado de otras fórmulas o funciones (por ejemplo FECHANUMERO("30/01/2001")).

Fecha_final: Una fecha que representa la última del período o la fecha de finalización.

Unidad: El tipo de información que desea obtener

Unidad

Devuelve

"Y"

El número de años completos en el período.

"M"

El número de meses completos en el período.

"D"

El número de días en el período.

"MD"

La diferencia entre los días en fecha_inicial y fecha_final. Los meses y años de las fechas se pasan por alto.
Nota de Microsoft: No se recomienda usar el argumento "MD", ya que su uso presenta limitaciones.

"YM"

La diferencia entre los meses de fecha_inicial y fecha_final. Los días y años de las fechas se pasan por alto

"YD"

La diferencia entre los días de fecha_inicial y fecha_final. Los años de las fechas se pasan por alto.

Vamos a ver algunos ejemplos de su uso. Ingresaremos una fecha en A1 y usaremos la función HOY() en A2:

Luego usaremos las siguientes fórmulas en celdas de la columna B:

Estas fórmulas nos darán los siguientes resultados, aunque hemos agregado algunas indicaciones en celdas de la columna C para que se entienda de mejor modo:

Ahora vamos a hacer algo con una fórmula anidada. Coloquemos esta fórmula en alguna celda:

="Han transcurrido  "&SIFECHA(A1;A2;"Y")&" años  "&SIFECHA(A1;A2;"Ym")&" meses y  "&SIFECHA(A1;A2;"md")&" días desde que  naciste" 

Si todo salió bien, debes tener algo como esto en la celda:

Han transcurrido 41 años 2 meses y 9 días desde que naciste

Interesante ¿no creen? Ah, un detalle más, no olviden que en este caso estoy usando el “punto y coma” como separador de lista o argumentos, si tú usas la “coma”, solo reemplaza. Listo, eso es todo por hoy.

Abraham Valencia

jueves, 14 de marzo de 2019

Interactuando con internet (III): Actualizar y extraer datos de tablas web

Ya en artículos anteriores hemos aprendido como interactuar con una página web (Enlace) y también a como extraer datos de tablas ubicadas en webs (Enlace). En el último caso lo hicimos con tablas cuyos datos eran estáticos, es decir, no cambiaban. Lo que haremos ahora es extraer datos de tablas cuyos datos pueden cambiar.

Para el ejercicio vamos a usar esta web como ejemplo: Enlace. Como vemos, hay una tabla, llamémosla, principal, cuyos datos dependen de una lista desplegable con tres opciones y un control de fechas (“picker”). Si bien hay un botón “Descargar datos”, en esta ocasión lo que nos interesa es extraer los datos directamente desde la web.

Lo primero es obtener el nombre, id o clase de los objetos que queremos usar así que usaremos la herramienta “Inspeccionar” de Chrome.

Entonces, el id del combobox es “data_interval” y el del picker es, justamente, “picker”. Unos detalles importantes a tomar en cuenta son qué; en el combobox hay tres opciones por ende, y viendo el orden, usaremos la propiedad “SelectedIndex” para elegir lo que deseamos (no olvidar que los índices comienzan en 0) y lo otro es que, veremos la forma en que en el picker están ingresados los valores de fechas predeterminadas, lo que nos da luces de como ingresarlos nosotros. Otro detalle es que cuando se elige una opción en el combobox o en el picker, los datos se actualizan de manera automática. Esto último es algo a lo que debemos tomar mucha atención pues, como verán, si la web no detecta estás acciones, no actualizará los datos de la tabla.

Entonces, si queremos elegir “Mensual” y fechas entre 01/01/2017 y el 01/03/2019, nuestro código quedaría así (no olvidar activar las referencias a “Microsoft HTML Object Library”):

Dim IE As Object
Dim doc As HTMLDocument 
Dim htmTable As HTMLTable

Set IE = CreateObject("InternetExplorer.Application") 
IE.navigate "https://es.investing.com/equities/cellnex-telecom-historical-data" 

Do Until IE.ReadyState = 4
     DoEvents 
Loop 

With IE
     .document.getElementById("data_interval").selectedIndex = 2
     .document.getElementById("picker").Value = "01/01/2017 - 01/03/2019"
     .Visible = True 
End With 

Set IE = Nothing 

La web cargada se verá así:

Como ven el combobox nos muestra “Mensual” pero ¡el picker no cambió las fechas! ¿Qué pasó? Si le damos un simple click al botón del picker que despliega los calendarios, veremos esto:

¡Aparecen las fechas enviadas con VBA! Es decir, sí fueron ingresadas pero no mostradas. Ah, otro dilema es que ¡la tabla no actualizó los datos! Pero si presionamos el botón “Aceptar” que aparece en la imagen anterior lograremos actualizar los datos. Entonces, usando el “Inspeccionar” de Chrome veremos que el id del botón para desplegar el picker es “widget” y el del botón “Aceptar” es “applyBtn” y para que las fechas se vean tal cual las mandamos y se actualice la tabla lo que haremos ahora es desplegar los calendarios del picker, enviarle un click al botón “Aceptar” y replegar el picker. Entonces, en nuestro código la parte del “With” debería quedar así:


With IE
     .document.getElementById("data_interval").selectedIndex = 2
     .document.getElementById("picker").Value = "01/01/2017 - 01/03/2019"
     .document.getElementById("widget").Click
     .document.getElementById("applyBtn").Click
     .document.getElementById("widget").Click
     .Visible = True 
End With 

Corremos la macro y ahora sí tenemos la tabla actualizada:

Ahora sí, solo nos falta extraer los datos de la tabla. Como ya hemos visto cómo hacerlo en un artículo anterior, básicamente usaremos los mismos códigos para lograrlo, pero en esta ocasión en lugar de usar el objeto “Tag” y el índice de la tabla (aunque también podríamos usarlo) lo haremos a través de su id que ya vimos es “curr_table”. Como no en realidad no es de nuestro interés ver la web, nos bastará trabajar en ella sin mostrarla e incluso cerraremos el Internet Explore usando “Quit”. Ah, por cierto, agregaremos un “Application.Wait” para dar un segundo para asegurar de que los datos de la tabla tengan tiempo de cargar. Nuestra macro quedará así:

Sub ExtraerDatos()

Dim IE As Object 
Dim doc As HTMLDocument 
Dim htmTable As HTMLTable 
Dim nFilas As Integer, nColumnas As Integer, x As Integer, y As Integer 

Set IE = CreateObject("InternetExplorer.Application") 
IE.navigate "https://es.investing.com/equities/cellnex-telecom-historical-data" 

Do Until IE.ReadyState = 4
     DoEvents 
Loop 

With IE
     .document.getElementById("data_interval").selectedIndex = 2
     .document.getElementById("picker").Value = "01/01/2017 - 01/03/2019"
     .document.getElementById("widget").Click
     .document.getElementById("applyBtn").Click
     .document.getElementById("widget").Click 
End With 

Application.Wait (Now + TimeValue("00:00:01")) 

Set doc = IE.document 
Set htmTable = doc.getElementById("curr_table") 
Let nFilas = htmTable.Rows.Length 
Let nColumnas = htmTable.Rows(0).Cells.Length 

For x = 1 To nFilas
      For y = 1 To nColumnas
          Cells(x, y).Value = htmTable.Rows(x - 1).Cells(y - 1).innerText
      Next y 
Next x 

IE.Quit 

Set IE = Nothing: Set doc = Nothing: Set htmTable = Nothing 

End Sub  

Como verán, en nuestra hoja activa hallaremos todos los datos que hemos elegido y ni siquiera fue necesario que se vea el Internet Explorer. Espero se haya entendido. Hasta la próxima.

Abraham Valencia