Bueno, como siempre, tras un par de conversaciones sueltas por ahí, siempre te das cuenta de que hay cosas que uno da por sabidas y que en realidad por internet es dificil encontrar. Precisamente para esas cosas tengo este blog así que he pensado en crear este artículo explicar unas pocas acciones en Excel que te permiten traer desde internet gran cantidad de datos desde internet para crear nuestros cuadros y dashboards en excel.

¿Por qué importar datos automáticos de internet?

Existen 2 formas de trabajar con Excel distintas:

1- Crear una serie de informes a medida para solucionar un caso puntual.

2- Crear una serie de informes, cuadros o paneles que vayamos actualizando con nuevos datos cada cierto tiempo.

En este segundo tipo de datos es donde muchos analistas se conforman con crear ciertas hojas con fuentes de datos (revisa el artículo de creación de dashboards si no sabes de que hablo) e ir actualizándolos de forma bastante manual y rudimentaria.

Para mi, el automatismo en estos cuadros es una máxima a cumplir. Si somos capaces de crear informes que por si solos son capaces de recoger la información básica y luego tratarla para presentarla de forma ordenada vamos a ahorrar muchisimo tiempo y es más, vamos a desentendernos de ciertos informes creados para perfiles de empresa muy concretos (y que por tanto con KPIs que a nosotros en realidad no nos interesan).

La recogida de estos datos en Excel es posible pero bastante limitada en tipos de fuentes y tratamiento de los datos de origen. Al final podemos instalar algunos plugins o macros para importar datos de las herramientas más importantes pero siempre habrá ciertos datos (por costumbre los más importantes para nuestro negocio) que no salen de herramientas conocidas sino de desarrollos propios o herramientas muy puntuales.

Para estos casos se hace necesario importar datos desde internet de la forma más cómoda posible.

Importando datos «Desde Web»

Ya vimos que una de las formas básicas de trabajar con Excel era mediante la pestaña «Datos» y el icono «Desde Web». De esta forma podemos navegar por una web y seleccionar la tabla de contenido a cargar en nuestro excel.

Pero esto, en un principio, parece muy limitado…

• No se trata de fuentes de datos organizadas
• Y Las consultas a estas webs no se asocian a celdas de nuestro excel por lo que en un principio no podemos hacer extracciones con parámetros, sino solo estáticas

Lo que realmente necesitamos

Pensemos en el tipo de cosas que podemos querer cargar en Excel si deseamos que sea usable por personas con distintos niveles de conocimiento. Querremos cargar la información de negocio organizada en alguno de los formatos estándar que imperan en la web y sin duda querremos importar datos en relación al valor de ciertas celdas concretas de nuestro Excel.

Pongamos un caso muy típico… quiero recoger las ventas reales de una base de datos de una web para poder tener una información más fiable que la que por ejemplo me dan los objetivos o transacciones de Google Analytics (u otros sistemas de analitica web). Lo normal sería querer:

– Definir un rango de fechas mediante dos celdas de Excel.
– Definir incluso algún fitro sobre las ventas (categorías, provincias, etc….)
– Definir de forma inequivoca al equipo técnico que tipo de exportación tienen que crearme…

El formato de la fuente en internet

Seguramente estarás pensando: «Un momento.. ¿yo? ¿yo tengo que definir a un técnico como hacer su trabajo? Iñaki, se te va la pinza! Tú no conoces a mis técnicos… «. Y tienes toda la razón. Un desarrollador no está ahí para que tu le digas como hacer su trabajo, sino para hacer el tuyo más fácil. No cometas nunca el error de suponer que algo es simple o que puedes definirlo mejor que él. Creo que hay pocas cosas que toquen más la moral a un desarrollador que eso (y lo digo por propia experiencia, claro! )

La solución? Hablemos de cosas estándar y ya definidas. Es decir, hablemos de XML. Todo desarrollador sabe crear un XML, es la estructura más tonta del mundo (bueno no, ese el csv pero es que da más problemillas).

Incluso dentro de los XML hay esquemas estandarizados que pueden crearse facilmente. El ejemplo más claro de estos esquemas sería el RSS. El sistema que se usa para los lectores de noticias en las webs (puedes ver por ejemplo el mio y de paso suscribirte en el botoncito amarillo al principio del sidebar del blog).

Un XML tiene un formato como este:

  
    1
    Pepito Perez
    35
    ES
  
  
    2
    Mike Wasausky
    23
    US
  

¿Es bastante fácil de leer verdad? Pues por suerte, con XMLs sencillos hasta Excel es capaz de interpretar esta estructura. Eso sí, no le pidamos demasiado… hay estructuras con las que no va a poder. No olvidemos que Excel solo puede representar datos en tablas de 2 dimensiones (ejes X e Y) por lo que si no eres capaz de visualizar esa tabla al ver el XML resultante es posible que Excel tampoco.

Bien… hemos resuelto el tema del formato: XML al poder!

– Tu lo entienes
– Cualquier desarrollador lo entiende y sabe crearlo fácilmente.
– Y Excel lo entiende

Cómo debe ser la petición

Por desgracia aquí si que encontramos trabajas… Excel no puede hacer según que peticiones al cargar los datos… No va a poder conectarse por OAuth2.0 sin ayuda de macros ni siquiera pasar los sistemas de password más sencillos. Necesita que toda nuestra petición quede en una URL escrita.

Esto nos supone un problema de seguridad relativo ya que no podemos crear peticiones que queden tras un login. Aún así recordemos que queremos cargar datos internos de negocio y estos deben tener seguridad. Seguidamente os indico las dos vías más comunes para dotar a estos sistemas de seguidad:

• La protección por IP: Toda conexión a internet, al pedir una página se identifica con una IP de conexión. Si sumamos esto a que las empresas suelen tener IP’s fijas (que no cambian nunca) y a que cualquier sistema de programación es capaz de recoger estas IP’s antes de dedidir que contenido da para responder a la petición de URL ya tenemos un sistema 100% seguro: Solo dar los resultados a la IP de nuestra empresa, siendo imposible acceder sin ella. Este sistema es muy recomendable, pero no funciona si tienes colaboradores desperdigados por el mundo
• Uso de un token: Se trata de un identificador alfanumerico que nos permita en una sola linea de texto saber quienes somos. Los token suelen usarse para identificar conexiones en sistemas de acceso seguro… nosotros no podemos hacer esto a nivel de cada conexión pero si podemos al menos proteger las URLs de carga de datos con un token complejo.

Ejemplo de uso de tocken:

– En lugar de cargar:

//midominio.com/mis-jugosos-datos.xml

Con un token cargaríamos algo como esto:

//midominio.com/mis-jugosos-datos.xml?token=ijdaswghasuighw83utyg2893wtgyfadsghqiwghoqawghewasdgiasgafhasdasd

token=ijdaswghasuighw83utyg2893wtgyfadsghqiwghoqawghewasdgiasgafhasdasd

El problema es que siempre pueden robarnos el token… pero bueno, siempre será más dificil que que nos roben un email o una contraseña facilona para acceder.

—

Solucionado el tema de las seguridad de los datos (o no, para gustos: colores). Lo que nos falta es definir como deben ser las peticiones que hagamos.

Y para ello, nada tan simple como saber de antemano que celdas de configuración requeriremos en nuestro excel. Para cada una de ellas necesitaremos poder pasar ese dato a una url.

Pongamos el ejemplo clásico de las fechas: Yo necesito fecha de inicio y fecha de fin de la petición y por lo tanto la url de exportación de datos deberá tener esos dos parámetros. Es decir, pasamos a:

//midominio.com/mis-jugosos-datos.xml?inicio=2012-06-01&fin=2012-06-08&token=ijdaswghasuighw83utyg2893wtgyfadsghqiwghoqawghewasdgiasgafhasdasd

inicio=2012-06-01
fin=2012-06-08
token=ijdaswghasuighw83utyg2893wtgyfadsghqiwghoqawghewasdgiasgafhasdasd

Aquí debéis tener muy claro que queréis poder recoger. Para un desarrollador seguramente será más fácil poner un par de filtros que ir exportando a medida cualquier cosa que se os vaya ocurriendo sobre la marcha.

Una vez lo tengáis ya estáis preparados para importar datos.

Manos a la obra, nuestra primera importación basada en celdas de configuración y XML

Vamos a poner en práctica esta forma de hacer las cosas pues creo que es como mejor se entienden las cosas.

Dado que no puedo dar ninguna fuente de datos internos y que ya hemos hablado de las implicaciones a tener en cuenta vamos a coger una fuente libre de internet en XML y a importarla basandonos en una celda. A partir de ahí realizar la configuración a medida para nuestros datos no debería ser un problema.

En este caso vamos a crear una hoja de datos que cargue las ultimas 100 menciones en twitter de nuestra marca directamente en excel. Sí, va a ser una fuente poco trabajable, pero es de las más conocidas por todos.

El feed de búsqueda de twitter

Twitter a día de hoy sigue ofreciendo un RSS (formato XML dedicado a noticias) para busquedas concretas que podemos crear con URLs sencillas. El formato es el siguiente:

http://search.twitter.com/search.rss?q=mibusqueda

q=mibusqueda

Pero esta url, solo nos da 15 tweets y nosotros queremos 100, así que metemos un parametro más:

http://search.twitter.com/search.rss?rpp=100&q=mibusqueda

rpp=100
q=mibusqueda

Ya tenemos una fuente válida. Ahora vamos a Excel y vamos a crear unas celdas con las que controlar esta búsqueda:

Creamos una hoja nueva e incluimos el siguiente texto:

A1 : "Busqueda en Twitter:"
A2 : "Ver ReTweets"
A3 : "Busqueda final"

B1 : "ikhuerta"
B2 : FALSO
B3 : =SI(B2;B1;B1&" -rt")

Con todo esto lo que hemos hecho ha sido configurar una búsqueda en twitter mediante la busqueda en si y si queremos ReTweets o no. Al final lo que nos importa es el resultado de la celda C3 que es la que contiene realmente la búsqueda a realizar.

Creando la conexión a los datos

En este punto tenemos que crear la importación automática de datos. Esto deberíamos hacerlo en una configuración sencilla de un único paso pero ya os avanzo que yo no he conseguido que sea asi. Excel hace cosas raras… presupone pasos que queremos dar y que no resultan los más convenientes por lo que en realidad no vamos a crear una conexión de a la Internet sino 2: una primera que luego desecharemos y una segunda que será la que realmente usemos.

Seguramente dentro de un tiempo, en los comentarios, alguien nos contará el truco para poder saltarnos uno de los pasos. Yo a día de hoy no he averiguado el cómo.

Primera conexión: definiendo la lectura del XML/RSS

Seleccionamos la celda D1 (para no mezclar los datos con la configuración) y vamos a «Datos» > «Desde Web».

Se nos abrirá una ventana donde tenemos que indicar la web a cargar (la url de twitter con 100 tweets) y un navegador donde seleccionar que contenido es el que queremos (es decir, le damos a la flecha de la esquina superior izquierda).

Le damos a «Importar» y el solo se dará cuenta de que es un XML y nos preguntará donde crear la tabla XML. Si teniamos seleccionado D1 nos dará «$D$1» como opción, sino pues lo seleccionamos ahora. Le damos a aceptar.

Excel rellenará entonces las filas de Excel creando una tabla XML… Este es el problema. Nosotros no queremos una tabla XML sino los datos en bruto para actualizaros y trabajarlos por lo que una vez se crean… vamos a borrarlos. Asi es la vida… si lo hace mal habrá que borrarlo.

Pero bueno, ya hemos conseguiro cargar datos del XML, que ya es algo, ¿no? Pues vamos a configurar esta carga para que tenga en cuenta la celda que queríamos:

Configurando con parámetros la conexión

Para hacer esta configuración entraremos en «Conexiones» y veremos que solo existe un item: «Conexión», entramos en sus propiedades y le cambiamos el nombre de la conexión por «Busqueda Twitter». Esto no es obligatorio pero si vais a trabajar con varias fuentes de datos es muy recomendable poner el nombre correcto a las cosas.

Ahora, dentro de esa misma ventana vamos a «Definición» y dentro a editar la consulta para agregarle parámetros desde celdas. Veremos como se vuelve a abrir esa ventana/navegador.

Lo que tenemos que hacer es editar la URL que habíamos puesto y añadirle parametros. Estos se definen de la siguiente forma:

["NOMBRE_PARAMETRO","Texto a preguntar al usuario"]

Así pues, la URL que tenemos que terminar incluyendo es la siguiente:

http://search.twitter.com/search.rss?rpp=100&q=["BUSQUEDA","Indica el texto a buscar"]

Nos volverá a pedir que seleccionemos el área a importar y seguidamente veremos como en la ventana anterior ya nos aparece como activo el cuadro de «Parámetros…»

Si no tocásemos nada, al realizar una importación se nos preguntaría cada vez «Indicar el texto a buscar», lo cual no está nada mal para aplicaciones puntuales pero recordemos que nosotros queremos indicarlo en una celda del Excel (la B3 que ya habíamos definido). Así que clicamos en parámetros y se los editamos:

Seleccionamos «Tomar el valor de la siguiente celda» y elegimos la celda con la búsqueda. Además como queremos hacer el tema un poco dinámico marcamos la casilla de «Actualizar automáticamente cuando cambie el valor de las celdas». Así realmente crearemos un buscador que cambie en función de las celdas.

Le damos a aceptar y ya tendremos nuetra configuración terminada… ¿El problema? Que como Excel lo que nos ha importado ha sido una tabla XML y no los datos directamente estos no se actualizarán… ¡vaya gracia!

Segunda conexión: definiendo la carga de datos automatizada

Ahora vamos con la conexión que vamos a usar de verdad… la primera nos la ha fastidiado el XML pero somos gente de recursos… (seguramente este problema puede solucionarse de otra forma, peor llevo tiempo haciendo estas importaciones y la verdad es que yo no he encontrado como, si alguien lo sabe, repito: que lo ponga en los comentarios).

Seleccionamos una celda que no haya sido afectada por la primera carga (en mi caso la D2 ya vale) y le damos a «Conexiones Existentes».

Aparecerá nuestra nueva conexión de «Busqueda Twitter» con varias conexiones de MSN… un poco inútiles para el caso, pero que no molestan. Seleccionamos La Búsqueda de twitter y le damos a abrir. Nos preguntará en que celda hacer la importación y seleccionamos la que deseemos.

y… MAGIA!!!!!

Veremos como se recupera automáticamente el feed de datos y se importa en nuestro exel.

Ahora ya podemos ir a nuestra celda «B1» y «B2» y comprobar como al cambiar los valores nuestros datos importados también van haciéndolo (con un poco de retraso mientras e pide la página, claro).

Ya solo nos queda volver a acceder a «Conexiones» y eliminar la vieja conexión (la nueva tendrá un «1» detrás al ser una copia) para que no nos moleste y acudir a la celda D2 y sucesivas para eliminar la tabla XML que nos había creado anteriormente.

Necesitas el archivo

No hay problema, en este caso no he preparado un archivo con cada pasito dado pero si que puedo dejaros el archivo final con la importación de la búsqueda twitter para que podáis jugar un poco con él.

• Descargar el archivo Excel con la búsqueda de twitter

Conclusión

El ejemplo que he mostrado no sirve de gran cosa… si Seguro que alguno de Social Media le saca utilidad, pero no son datos que realmente se puedan trabajar. Lo importante de todo esto es aprender a sacar datos estructurados mediante una conexión a Internet. Las posibilidades son enormes… con un poco de ayuda de los desarrolladores podemos ir creando nuestras importaciones más importantes de forma automática en Excel. Si unimos esto a otras herramientas que hemos visto como por ejemplo la importación de datos de Google Analytics podemos llegar a crear archivos Excel que sean por si solos una herramienta de valor para la empresa.

Como siempre, solo quería mostrar un poco el camino, luego cada uno, que lo desarrollo como quiera…

Extra: fuentes de XML por la web…

Si quereis ir probando cosillas con XML’s por internet (ya que no todos podemos tener listos XML esperando a ser cargados) os dejo algunos recursos con los que probar.

• Yahoo YQL: Un sistema que te permite acceder a gran cantidad de APIs publicas y trabajarlas como si de SQL se tratase para objetener archivos json o XML. En su día desarrollé una explicación sobre como trabajar con jquery e YQL, tal vez te ayude a entenderlo un poco
• Yahoo Pipes: Una herramienta gráfica con la cual acceder a multitud de fuentes de datos (incluido el propio YQL), filtrarlos, mezclarlos formatearlos. Es muy, muy potente, pero aviso: nada intuitivo, hay que dedicarle horas
• Como funcionan los feeds (XML) de wordpress y Blogger: Formas fáciles de seguir todo lo que publica la competencia o los comentarios que les hacen.
• Geonames: Nos permitirá con un usuario gratuito trabajar con información geográfica

Si conoces alguno más, no dudes en decirlo en los comentarios y lo añadiré con mucho gusto.