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.
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.