Fórmulas SERVICIOWEB y XMLFILTRO

En este (no tan) breve tutorial veremos algunos ejemplos de la utilización de las fórmulas SERVICIOWEB y XMLFILTRO. Estas fórmulas pueden ayudarte a obtener fácilmente información de un sitio web, actualizada automáticamente en tiempo real. Hasta hace poco, las opciones para realizar esta tarea de forma eficiente, requerían al menos, un conocimiento básico de VBA. Pero ya no más. En este tutorial te mostraré cómo utilizarlas y verás lo útiles que pueden llegar a ser en el uso cotidiano de Excel. El tutorial está dividido en dos partes: primero haremos un convertidor de divisas a partir de los tipos de cambio de www.x-rates.com y posteriormente haremos un visualizador de pronóstico del clima con los datos de https://www.tutiempo.net.

Esta es una de las preguntas clásicas que dan vueltas y vueltas en los foros de Excel. ¿Cómo hacer un convertidor de divisas con tipos de cambio actualizados en tiempo real a partir de un sitio web? Como comentamos en la introducción, ya no necesitas tener conocimientos de VBA. Solo necesitas una fórmula: SERVICIOWEB. Vamos a comenzar con un libro en blanco, le añadimos los siguientes encabezados de columna y algo de formato para identificar las celdas.

Podemos agregarle algunos datos de prueba en las celdas A2, B2 y C2. Lo primero que necesitamos para aplicar la fórmula SERVICIOWEB, es el link del sitio web desde el cual queremos extraer la información. En nuestro caso, para el convertidor de divisas, un buen sitio web es https://www.x-rates.com/calculator/. Si haces un par de consultas de divisas en este sitio web, te darás cuenta que la barra de dirección se actualiza con los datos que ingresas. Por ejemplo, al realizar la consulta de la equivalencia de 2 USD a MXN, verás que la barra de dirección pasa a ser:

Podemos aprovechar esta característica del sitio web para que la cantidad y las divisas pasen como variables de nuestra fórmula. Tomando esto en cuenta, escribe la siguiente fórmula en la celda D2:

="https://www.x-rates.com/calculator/?from=" &B2&"&to="&C2&"&amount="&A2

Lo que hace esta fórmula es concatenar el texto fijo de la página web con el texto variable que en este caso son las celdas A2, B2 y C2. En la celda E2 utilizaremos ahora sí, la fórmula SERVICIO WEB:

=SERVICIOWEB(D2)

El resultado que arroja la fórmula puede ser un poco intimidante, sin embargo, no hay razón para tener miedo. En este caso, el texto arrojado es el código HTML de la página web aunque en otros casos el resultado podría ser código XML o bien ASPX. Sin embargo, este detalle no será relevante para este tutorial, lo que sí nos interesa de este texto, es que, escondido entre todos estos caracteres, se encuentra el valor de la conversión de divisas que necesitamos. Si logramos encontrar su posición, podremos utilizar la fórmula EXTRAE para devolver este valor.

Tal vez te interese también: Descarga: Libros de Interés, HTML, VBA,

 Para encontrar la posición del valor convertido, necesitaremos los nombres de los tags entre los que se encuentra. Una forma en la que podemos encontrar los tags adecuados, es copiar el valor de la celda y pegarlo en un editor de texto en el que podamos buscar más fácilmente el valor convertido, como en la siguiente imagen:

Entonces los tags que estábamos buscando son ccOutputRslt y ccOutputTrail. Ahora resta encontrar las posiciones en las que se encuentran estos dos tags dentro del código html. Esto lo podemos hacer utilizando la función ENCONTRAR. Además, también necesitaremos contar dos números de caracteres. Primero, el número de caracteres que hay entre el inicio del tag ccOutputRslt y el inicio del valor convertido (en este caso 18.87). Segundo, el número de caracteres que hay entre el inicio del tag ccOutputTrail y el valor convertido. Esto puede resultar un poco confuso a primera vista, pero se lo vuelves a leer seguramente te quedará más claro.

Si haces las cuentas, verás que el número de caracteres son 14 y 13 respectivamente. Entonces podemos colocar las siguientes fórmulas en las celdas F2 y G2 respectivamente:

=ENCONTRAR("ccOutputRslt",E2)+14

=ENCONTRAR("ccOutputTrail",E2)-13

Finalmente estamos en posición de estraer el valor convertido. Utilizamos la fórmula EXTRAE para extraer los caracteres del valor convertido, esto es, en la celda H2 colocamos esta fórmula:

=EXTRAE(E2,F2,G2-F2)

Si colocas valores de prueba, podrás corroborar que nuestro convertidor de divisas funciona perfectamente bien:

Esta es otra pregunta clásica de los usuarios de Excel. ¿Cómo visualizar el pronóstico del clima en mi ciudad a partir de la información de un sitio web? Esto es lo que haremos en este tutorial. Primero, aclarar que en este ejemplo específico lo haremos para una ciudad. Hacerlo para más ciudades solo requerirá un poco de creatividad de tu parte. Lo primero que haremos será copiar la siguiente página dirección web en la celda A1 de una hoja en blanco:

http://xml.tutiempo.net/xml/54964.xml

En esta dirección, el número 54964 corresponde al identificador de ciudad que manejan en esta página. Esta corresponde a la ciudad de San Luis Potosí en México. Puedes ver los identificadores de otras ciudades entrando a la página https://www.tutiempo.net, buscando la ciudad y dando click en el botón XML que aparece justo a un lado del símbolo de grados centígrados (°C).

Ahora utilizamos la fórmula SERVICIOWEB en la celda A2:

=SERVICIOWEB(A1)

Igual que en el caso anterior, el resultado será un texto muy extenso que ahora es un código XML. En el ejemplo anterior, vimos cómo podemos obtener información de esta cadena de texto utilizando las fórmulas de texto de las que dispone Excel. Sin embargo, en este caso, podemos utilizar un fórmula adicional para obtener información del código XML. Esta fórmula es XMLFILTRO, la cual requiere dos parámetros: un código XML y la ruta dentro del código XML de la información que queremos devolver. El código XML es por supuesto, el que aparece en la celda A2. Para entender a qué nos referimos con la "ruta" de la información dentro del código XML, es útil visualizar el código en un navegador:

Aquí puede observarse que el código está indentado, es decir, está escrito por niveles definidos mediante un tag. Siguiendo esta indentación, podemos obtener la ruta de la información deseada. Por ejemplo, en la imagen podemos ver que la ruta del nombre de la localidad (San Juan del Río en este caso) es:

/datos/localidad

Con esto ya podemos utilizar la fórmula XMLFILTRO. En las celdas B4 y B5 escribimos respectivamente:

=XMLFILTRO(A2,"/datos/localidad/nombre")

=XMLFILTRO(A2,"/datos//pronostico_horas/hora/fecha[1]")

El resultado debería ser el siguiente:

Lo que sigue es analizar la estructura del archivo XML y determinar las rutas de toda la información que deseemos obtener. Por ejemplo, podemos obtener el pronóstico completo por horas del día actual:

En esta imagen hemos utilizado las siguientes fórmulas en las columnas de la A a la F:

=XMLFILTRO(A2,"/datos//pronostico_horas//hora//hora_datos")

=XMLFILTRO(A2,"/datos/pronostico_horas//temperatura")

=XMLFILTRO(A2,"/datos/pronostico_horas//texto")

=XMLFILTRO(A2,"/datos/pronostico_horas//humedad")

=XMLFILTRO(A2,"/datos/pronostico_horas//viento")

=XMLFILTRO(A2,"/datos/pronostico_horas//dir_viento")

Y las hemos utilizado como fórmulas matriciales de la fila 8 a la 32. Recuerda que para crear una fórmula matricial, debes seleccionar el número de celdas en las que deseas insertar la fórmula, y con ellas seleccionadas, das click en la barra de fórmulas y presionas la combinación de teclas Ctrl+Shift+Enter.

También podríamos preferir obtener solo la información para la hora actual y que esta se vaya actualizando automáticamente conforme van avanzando las horas del día. En este caso, las fórmulas a utilizar serían las siguientes:

=XMLFILTRO(A2,"/datos/pronostico_horas/hora[hora_datos='"&HORA(AHORA())&":00']/temperatura")

=XMLFILTRO(A2,"/datos/pronostico_horas/hora[hora_datos='"&HORA(AHORA())&":00']/texto")

=XMLFILTRO(A2,"/datos/pronostico_horas/hora[hora_datos='"&HORA(AHORA())&":00']/humedad")

=XMLFILTRO(A2,"/datos/pronostico_horas/hora[hora_datos='"&HORA(AHORA())&":00']/viento")

=XMLFILTRO(A2,"/datos/pronostico_horas/hora[hora_datos='"&HORA(AHORA())&":00']/dir_viento")

El resultado debería verse como en la siguiente imagen:

Este pronóstico se irá actualizando automáticamente con la hora del día y con la información del sitio web original. 

Esto ha sido todo por hoy. De sobra está decir que este sencillo ejemplo puede volverse muy vistoso agregando un poco más de formato al archivo y algunas imágenes propias o del mismo sitio web. Esto queda a tu gusto. Como siempre, si tienes algún comentario adicional, no dudes en hacérnoslo saber.

Nota.- Si necesitas respuesta a tu comentario, por favor déjame tu correo en el cuerpo del mensaje.