Consultar tablas web en Excel

Sin duda alguna Internet es una poderosa herramienta a la hora de desarrollar nuestro trabajo, en este articulo te enseñare a obtener datos en forma de tabla que están en paginas web, de tal forma que puedas trabajar con ella directamente en tus hojas ( Formatos Contables ), sin necesidad de tener que estar copiando y pegando desde la web.

Descargar MegaPack Contables en Excel

Al final también te mostrare como programar una pequeña aplicación para obtener el histórico de tipos de cambio según la moneda y fecha que podrás seleccionar.

Que es una consulta de tabla web en excel

Las consultas de tablas web consiste en poder obtener los datos de tablas que están en paginas web, esto se puede hacer con macros, lo cual hace mucho mas dinámico este trabajo, de tal forma que se obtienen mejores resultados.

Ejemplo de una consulta de tabla web en excel

Codigo VBA para hacer una consulta WEB

El siguiente código es un ejemplo de una consulta de tabla WEB.

Sub TestTablas()
    Dim hj As Worksheet
    Dim Tablas As QueryTable
    Dim url As String

    url = "http://www.economiahoy.mx/indice/IPC-MEXICO"

    Set hj = Worksheets.Add

    Set Tablas = hj.QueryTables.Add(Connection:="URL;" & url, Destination:=Range("A1"))

    With Tablas
        .Name = "Consultas"
        .RefreshOnFileOpen = True
        .WebFormatting = xlWebFormattingRTF
        .WebSelectionType = xlSpecifiedTables
        .WebTables = "2"
        .Refresh
    End With
End Sub

Como funciona el método QueryTables.Add

Lo que hace ese método es crear una coleccione entre la hoja de calculo especificada y la url dada, de tal forma que se pueden obtener ciertos ( tablas )datos de esa url.

Una vez que se crea la conección (Se establece el objeto ), dicho objeto de la coneccion tiene ciertos métodos, que podemos ejecutar, entre ellos los mas importantes son:

.Name = "Consultas" => Para indicar el nombre de la tabla creada
.RefreshOnFileOpen = True => Para indicar si la tabla se actualizara al abrir el libro
.WebFormatting = xlWebFormattingRTF => Tipo de formato de la tabla
.WebSelectionType = xlSpecifiedTables => Para indicar si queremos obtener todas las tablas o alguna en especifico
.WebTables = "2" => Para indicar el numero de tabla a consultar
.RefreshPeriod = 1 => Numero de minutos para refrescar la tabla
.Refresh => Refrescar la tabla

Programando App para consultar tipos de cambio

En algunas paginas web es posible personalizar las consultas, es decir, puedes seleccionar algunas opciones y en base a ello se genera la tabla.

Utilizando macros es posible hacer esas mismas consultas sin necesidad de ir a la pagina web, de tal forma que puedes generar tus propias consultas directamente en tus hojas.

El siguiente ejemplo es para obtener la tabla de tipos de cambio por monedas y fechas, puedes seleccionar en la hoja de calculo la moneda y la fecha a consultar, obtendrás una tabla con los tipo de cambio de esa moneda en relacion a otras monedas y según la fecha especificada.

Para la lista de monedas en la celda puedes usar validación de datos del menú “Datos” en la cinta de opciones de excel, para las fechas también puedes usar validación.

Código VBA de la app de consulta de Tipos de Cambio

Sub TestTipoCambio()
    Dim Tablas As QueryTable
    Dim url As String
    Dim Moneda As String
    Dim Fecha As String

    Moneda = wsTipoCambio.Range("C2")
    Fecha = Format(wsTipoCambio.Range("C3"), "yyyy-mm-dd")

    url = "http://www.xe.com/en/currencytables/?from=" & Moneda & "&date=" & Fecha

    Set Tablas = wsTipoCambio.QueryTables("tablas")

   With Tablas
      .Connection = "URL;" & url
      .RefreshOnFileOpen = True
      .Refresh
   End With
End Sub

La clave en esta macro es la generación dinámica de la url, esto es porque en la pagina web para hacer estas consultas se genera una url dependiendo de la moneda y fecha seleccionada.

Esta es la pagina para las consultas “http://www.xe.com/en/currencytables/” , si seleccionas USD y das click en la flecha para convertir, se genera la siguiente url y se hace la tabla para esa moneda “http://www.xe.com/en/currencytables/?from=USD&date=2017-08-31” , si observas, se puede ver que moneda es y la fecha.

Así que podemos aprovechar eso y generar de manera dinámica la url con los datos indicados en la hoja ( moneda y fecha ), eso se logra con el siguiente código en la macro anterior.

Moneda = wsTipoCambio.Range("C2")
Fecha = Format(wsTipoCambio.Range("C3"), "yyyy-mm-dd")

url = "http://www.xe.com/en/currencytables/?from=" & Moneda & "&date=" & Fecha

Espero que este ejemplo te sea de mucha ayuda y puedas adaptarlo a tus proyectos, pronto estaré publicando ejercicios un poco mas avanzados al respecto.