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

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

Excel y MySQL (I)

Como hemos ya mencionado, hoy en día el uso de internet es tan masivo que no es extraño que se compartan no solo archivos sino también datos a través de su uso. No extraña, tampoco, el uso de sistemas que usan motores de base de datos ubicados en “hosting” que permiten el acceso remoto desde, literal, cualquier lugar del planeta. Por supuesto muchos priorizan el php o java (o similares) para crear sistemas y usarlos en páginas web que trabajen con los motores de base de datos de dichos “hosting”.

Probablemente MySQL debe ser uno de los mencionados motores que más se usa hoy en día y al ser Microsoft Excel una de las aplicaciones de escritorio más usada, y no solo como hoja de calculo que es su función principal, no es raro encontrar muchas personas que preguntan cómo conectar ambos programas. Aunque yo considero que no es lo idóneo usar Excel como “Front End”, entiendo que a muchos les resulta más fácil por uso y por eso la constante pregunta; por ello vamos a explicar cómo lograrlo.

Si bien se puede obtener datos de un servidor MYSQL con el complemento “MySQL for Excel” o a través de Power Query, el uso de VBA permite, desde mi punto de vista, una mejor manipulación de los datos.

Lo primero es comentar que usaremos ADO para ello. ActiveX Data Objects (ADO) es un mecanismo para comunicarse con bases de datos y así poder trabajar con sus datos, es compatible con VBA y, además, permite conexiones con MySQL. Otra cosa importante, hay que descargar un Driver ODBC para lograr la conexión. Si tu sistema operativo es de 32 bits y por ende tu Office igual, no tendrás mayor problema en usar el Driver que hayas instalado porque será para sistemas operativos de 32 bits, pero si tu sistema operativo es de 64 bits hay algo a tomar en cuenta y es que el Driver a usar deberá ser de los mismos bits que tu Office. Para que se entienda:

Sistema Operativo	Office	Driver MySQL
32 bits	32 bits	32 bits
64 bits	32 bits	32 bits
64 bits	64 bits	64 bits

Para el ejemplo yo usé “MySQL ODBC 8.0 Unicode Driver”.

Ahora sí comencemos con el ejemplo propiamente dicho. Lo primero es activar la referencia a “Microsoft ActiveX Data Object 6.1 Library” en donde el 6.1 puede variar dependiendo de tu versión de Office. Luego declararemos las variables, una para la conexión y otra para el recordset:

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset 

Luego vamos a declarar variables en base a los parámetros que usaremos como el nombre del servidor, la base de datos, el usuario, entre otros. Para ver los parámetros ver este enlace.

Dim BD$, Servidor$, User$, Clave$

Ahora procederemos a dar valores a las variables, a crear la cadena de conexión y a establecer dicha conexión:

Let Servidor = "NombreoIPdelServidor": Let BD = "NombreBasedeDatos"
Let User = "UsuariodelServidor": Let Clave = "TuClave" 
cnn.ConnectionString = "DRIVER={MySQL ODBC 8.0 Unicode Driver};" _
     & "SERVER=" & Servidor & ";DATABASE=" & BD & ";" _
         & "UID=" & User & ";PWD=" & Clave & ";PORT=3306;OPTION=131072" 
cnn.Open MsgBox "Conectado a mi BD MySQL" 

Si todo salió bien el MsgBox se habrá activado lo que quiere decir que logramos conectarnos al servidor. Ah, por cierto, el puerto 3306 es el que se prioriza para usar servidores que no sean el “localhost” y en el “Option” el 131072 es para “habilitar opciones seguras” (revisar el enlace de párrafos arriba para más detalles). Si no se logró la conexión sería bueno revisar en el “phpmyadmin” que el acceso parra conexiones remotas del servidor esté activado (ojo que no todos los servidores lo tienen/permiten). Todo junto debe quedar así:

Sub TrabajarconMySQL()
Dim cnn As ADODB.Connection 
Dim rst As ADODB.Recordset 
Dim BD$, Servidor$, User$, Clave$ 

Set cnn = New ADODB.Connection 
Let Servidor = "NombreoIPdelServidor": Let BD = "NombreBasedeDatos" 
Let User = "UsuariodelServidor": Let Clave = "TuClave" 

cnn.ConnectionString = "DRIVER={MySQL ODBC 8.0 Unicode Driver};" _
     & "SERVER=" & Servidor & ";DATABASE=" & BD & ";" _
         & "UID=" & User & ";PWD=" & Clave & ";PORT=3306;OPTION=131072" 

cnn.Open 
MsgBox "Conectado a mi BD MySQL" 
cnn.Close 
Set cnn = Nothing 

End Sub 

Si queremos conectarnos a nuestro servidor de MySQL instalado en nuestra PC (como el que crea WampServer), basta cambiar así:

Let Servidor = "localhost": Let BD = "NombreBasedeDatos"
Let User = "root": Let Clave = "" 

cnn.ConnectionString = "DRIVER={MySQL ODBC 8.0 Unicode Driver};" _
     & "SERVER=" & Servidor & ";DATABASE=" & BD & ";" _
         & "UID=" & User & ";PWD=" & Clave & ";PORT=3306;OPTION=131072" 

En donde lo único que deberán cambiar es el nombre de la base de datos.

Ahora vamos a obtener los datos de una de las tablas. Para este usaremos SQL, obtendremos todos los datos de “Tabla1”, incluido los nombres de los campos, y colocaremos todos los datos en una hoja de nuestro Excel usando bucles con variables para de ese modo no tener que ajustar al número de campos o registros:

Sub Conectar() 

Dim cnn As ADODB.Connection 
Dim rst As ADODB.Recordset 
Dim nCampos&, x As Integer 
Dim nRegistros&, y As Long 
Dim sql$, BD$, Servidor$, User$, Clave$ 

Set cnn = New ADODB.Connection 
Set rst = New ADODB.Recordset 
Let sql = "Select * From Tabla1" 
Let Servidor = "NombreoIPdelServidor": Let BD = "NombreBasedeDatos" 
Let User = "UsuariodelServidor": Let Clave = "TuClave" 

cnn.ConnectionString = "DRIVER={MySQL ODBC 8.0 Unicode Driver};" _
     & "SERVER=" & Servidor & ";DATABASE=" & BD & ";" _
         & "UID=" & User & ";PWD=" & Clave & ";PORT=3306;OPTION=131072" 

cnn.Open 

With rst
     .CursorLocation = adUseClient
     .CursorType = adOpenKeyset
     .LockType = adLockOptimistic
     .Open sql, cnn, , , adCmdText
End With 

Let nCampos = rst.Fields.Count 
Let nRegistros = rst.RecordCount 

For x = 1 To nCampos
     Cells(1, x) = rst.Fields(x - 1).Name 
Next x 

For y = 1 To nRegistros
     For x = 1 To nCampos
         Cells(y + 1, x).Value = rst.Fields(x - 1).Value
     Next x
     rst.MoveNext
 Next y 

cnn.Close 
Set rst = Nothing: Set cnn = Nothing 

End Sub 

Por supuesto también usando SQL podríamos enviar más datos y/o modificar los ya existentes, pero eso será para otra ocasión. Hasta la próxima.

Abraham Valencia

Interactuando con internet (II): Extraer datos de tablas web

Ya hemos visto en un artículo anterior (Enlace) como identificar objetos/elementos de una página web y sobre todo como interactuar con ellos, pero algo de lo que no hablamos fue de cómo extraer datos de por ejemplo las tablas que no es difícil encontrar en ellas.

Por supuesto que cuando hablamos de tablas de una página web lo primero que probablemente se nos viene a la cabeza es usar la herramienta de Excel “Obtener datos externos”, que nos permite elegir las tablas que identifica en dicha web e importarlas a nuestra hoja de Excel. En muchos casos la página quizá tenga usuario y contraseña, pero la herramienta no permite visualizar la web y sus campos, así como ingresar esos datos y seguir navegando hasta que cargue la página con las tablas; una vez que ocurre eso elegimos la tabla que deseemos y listo, tendremos los datos en nuestra hoja de Excel.

:

Es muy probable que queramos que esto sea automático y para ello podemos por supuesto usar VBA. Esto puede lograrse usando el objeto “QueryTable” y obteniendo así los datos de la web deseada a través de macros y de tener usuario y clave, podremos usar su propiedad “PostText” para “enviarlos” y poder seguir navegando. La desventaja de este método (con VBA o no), es que nos importa los datos completos de la tabla, no hay forma de indicar que sean solo algunas filas y/o columnas. Entonces ¿cuál es la solución?.

La forma que voy a sugerir en esta ocasión es trabajar directamente sobre los objetos/elementos que en las webs está definidos por la etiqueta “Table”. Para eso usaremos el método “getElementsByTagName” y debemos recordar/saber también que los índices de las tablas de una web comienzan en cero (0). Aprovecharemos que la Universidad de Valencia (España) tiene una web con varias tablas de ejemplo: Enlace

En esta ocasión no solo usaremos el objeto “Internet Explorer” sino que también haremos lo propio con objetos HTMLTable y HTMLDocument. Para ello lo primero será activar las referencias a “Microsoft HTML Object Library”, en seguida vamos a declarar tres variables

Dim ie As Object
Dim doc As HTMLDocument
Dim htmTable As HTMLTable  

La primera es para nuestro objeto “Internet Explorer”, la segunda es para hacer referencia a la propiedad “Document” (Obtiene, a su vez, el objeto de la web activa) de dicho objeto y la tercera hará referencia a la etiqueta “Table”, es decir a la tabla o tablas de las que extraeremos los datos. Dicho eso, y suponiendo que queremos los datos de la primera tabla de la web de ejemplo, agregaremos estas líneas:

Set ie = CreateObject("InternetExplorer.Application")
ie.Navigate "https://www.uv.es/jac/guia/tablaeje.htm"
Do: DoEvents: Loop Until ie.ReadyState = 4 
Set doc = ie.Document 
Set htmTable = doc.getElementsByTagName("Table")(0) 

Hasta ese momento ya estamos conectados a la tabla, pero para extraer los datos necesitamos saber algunas cosas como por ejemplo que dichas tablas tienen una colección “Rows” (filas) y estás a su vez tienen la propiedad “Lenght” que retorna el número de elementos de la colección correspondiente. Entonces, para obtener el número de filas de la tabla usaremos lo siguiente:

htmTable.Rows.Length

Del mismo modo, para saber cuántas celdas /columnas tiene una fila, usaremos la colección “Cells” que tienen dichas filas y podemos nuevamente usar la propiedad “Lenght”. Ah, no olvidar que, según su índice, la primera fila es la cero (0), entonces para saber/obtener el número de celdas/columnas, usaremos lo siguiente:

htmTable.Rows(0).Cells.Length

Para obtener el valor de las celdas usaremos la propiedad “InnerText”. Por ejemplo, en la misma tabla para obtener el valor de la primera celda de la fila superior (en este caso la letra “A”):

htmTable.Rows(0).Cells(0).innerText

Agreguemos todo en la macro de este modo:

Sub tablaenWeb()

Dim ie As Object 
Dim htmTable As HTMLTable 
Dim doc As HTMLDocument 

Set ie = CreateObject("InternetExplorer.Application") 
ie.Navigate "https://www.uv.es/jac/guia/tablaeje.htm" 
Do: DoEvents: Loop Until ie.ReadyState = 4 

Set doc = ie.Document 
Set htmTable = doc.getElementsByTagName("Table")(0) 

MsgBox "La primera tabla tiene " & htmTable.Rows.Length & " filas" 
MsgBox "Además, su primera fila tiene " & htmTable.Rows(0).Cells.Length & " columnas" 
MsgBox "Y la primera celda de la fila superior tiene el siguiente valor en ella " & htmTable.Rows(0).Cells(0).innerText 

ie.Visible = True 

Set ie = Nothing: Set doc = Nothing: Set htmTable = Nothing 

End Sub 

Ahora vamos a usar la decima tabla de la web (índice 9) para extraer sus datos y llevarlos a nuestra hoja. Ya con las colecciones mencionadas bastará colocar todo en bucles:

Sub tablaenWeb() 
Dim ie As Object 
Dim htmTable As HTMLTable 
Dim doc As HTMLDocument 
Dim nFilas As Integer, nColumnas As Integer, x As Integer, y As Integer 

Set ie = CreateObject("InternetExplorer.Application") 

ie.Navigate "https://www.uv.es/jac/guia/tablaeje.htm" 
Do: DoEvents: Loop Until ie.ReadyState = 4 

Set doc = ie.Document 
Set htmTable = doc.getElementsByTagName("Table")(9) 
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.Visible = True 

Set ie = Nothing: Set doc = Nothing: Set htmTable = Nothing 

End Sub 

Esto último, sobre todo, creo yo, será útil cuando no se sabe la cantidad de filas y columnas de una tabla, sobre todo en las webs de consulta de datos. Y eso es todo por hoy.

Abraham Valencia