Obtener datos externos de una página Web en Excel

Estándar

Ya anteriormente he publicado el hacer uso de técnicas como el web scraping para extraer información de páginas web por medio de programación en VBA/Excel. Pero hay algunas ocasiones en que extraer información puede hacerse de una manera fácil y rápida que no amerite programación alguna, pudiendo hacer uso de la importación de datos externos de una web en Excel. Este método funciona principalmente si los datos que queremos extraer se encuentran en un formato tabular (tabla HTML) como pueden ser cotizaciones. La ventaja de usar este método es que es muy fácil, no se programa e incluso se puede estar descargando los datos cada “n” minutos especificados en la consulta.

Les comparto un video con el ejemplo.

Anuncios

Ordenación de datos

Estándar

En el mundo de la informática, quizá no haya tareas más fundamentales o tan extensamente analizadas como lo son la ordenación y la búsqueda. Estas rutinas se utilizan virtualmente en todos los programas de bases de datos, así como en compiladores, intérpretes y sistemas operativos.

La ordenación es el proceso de colocar un conjunto de elementos similares de información en un orden ascendente o descendente.

Existen básicamente tres métodos generales que se pueden usar para ordenar arrays:

  • Por intercambio
  • Por selección
  • Por inserción

Imaginemos una baraja de cartas. Para ordenar las cartas por “intercambio”, se extenderían las cartas boca arriba, sobre una mesa y se procedería a intercambiar las cartas que no están en orden hasta que la baraja estuviese ordenada.

Para ordenar por “selección” se extenderían las cartas sobre la mesa, se seleccionaría la carta de menor valor y se sacaría de la baraja. Entonces de las cartas que quedan sobre la mesa, se seleccionaría la carta más baja y se colocaría detrás de la que ya se tiene en la mano. Este proceso continuaría hasta que todas las cartas estuviesen en la mano con lo cual las cartas estarían ordenadas.

Para ordenar por “inserción“, se mantendrían las cartas en la mano cogiendo una cada vez. Mientras se cogen cartas de una mano, se van colocando en un mazo sobre la mesa, siempre insertándolas en la posición correcta. La baraja estaría ordenada cuando ya no queden cartas en la mano. Les comparto una macro con el código de ejemplo de cada uno de los algoritmos de ordenación.

Mas adelante estare publicando algoritmos mas avanzados de ordenación.

Utilizando Arrays en VBA – Parte 1

Estándar

Muchos de los estudiantes de programación se sienten intimidados al escuchar la palabra Array (matriz) y evitan en muchos casos el uso de los mismos en sus proyectos, pero esto se debe al poco o mal conocimiento de los mismos. No se diga si se tiene que programar en C, C++ o Java. En estos lenguajes uno puede quedar frustrado al intentar hacer uso de los Arrays. Sin embargo existen otros lenguajes donde usar Arrays es cosa de niños (fácil e intuitivo).

Para el caso de VBA Excel se está en un punto medio, no es tan difícil hacer uso de ellos y realmente pueden hacer que algunas macros tarden tan poco tiempo, el problema de los Arrays en VBA es que existen pocas herramientas a nivel programación que nos brinda el lenguaje para poder procesarlos fácil y rápidamente.

¿Pero que es un array?, ¿Para qué sirve?  y  ¿Cómo se usan?. Antes que nada un array es un tipo de variable como lo es un integer, un string pero con la capacidad especial de poder albergar más de una variable y en el caso de VBA dichas variables pueden ser de distinto tipo.

Todos aquellos que han usado Excel, sean o no programadores ya han trabajado con una matriz o array, pero por supuesto!. Una hoja de cálculo no es más que una matriz (array) bidimensional de más de un millón de filas y poco más de 16 mil columnas (en el caso de Excel 2007 en adelante) y como todos sabemos podemos introducir un número, fecha o texto dentro de una celda, lo mismo es aplicable a los arrays en VBA.

En la próxima entrega empezaremos a trabajar de manera eficiente con los arrays, veremos más propiedades de los mismos y las herramientas que nos brinda VBA para su manipulación.

Utilizar Access como aplicación de soporte para mejorar el acceso multiusuario a los datos

Estándar

Desde la versión 2007 de Excel, se cuenta con más de un millón de filas, una mejora sustancial de la versión 2003 donde solo se contaban con 65 mil, pero aunque parezca que un millón puede ser suficiente, en algún momento se tiene que admitir que, aunque Excel es el mejor producto del mundo, ha llegado el momento de pasarse a Access y aprovecharse de los archivos ACCDB de Access.

Incluso antes de que tenga que se tenga un millón de filas, otra razón irresistible para utilizar archivos de datos ACCDB es permitir el acceso multiusuario a los datos sin los dolores de cabeza asociados a los libros compartidos.

Excel ofrece una opción para compartir un libro, pero automáticamente pierde un número de importantes características Excel cuando lo hace. Después de compartir un libro, no se puede utilizar subtotales automáticos, tablas dinámicas, agrupaciones, escenarios, protección, autoformato, estilos, imágenes, añadir gráficos o insertar hojas.

Al utilizar la interfaz de Excel VBA y almacenar los datos en una base de datos ACCDB, tiene lo mejor de dos mundos. Tiene la potencia y flexibilidad de Excel y la posibilidad de acceso multiusuario disponible de Access.

ACCDB es el nuevo formato de archivo oficial tanto de Microsoft Access como de Microsoft Visual Basic (en versiones anteriores, el formato era MDB). Esto significa que se puede desarrollar una solución Excel que lea y escriba de un ACCDB para clientes que no tengan Microsoft Access. Por supuesto, ayuda si como desarrollador tiene una copia de Access, porque puede utilizar la interfaz de Access para configurar tablas y consultas.

Caso Práctico

Linda y Virginia son dos compradoras de una cadena minorista de tiendas. Cada mañana importan datos de las cajas registradoras para obtener la información actual de ventas y el inventario para 2000 referencias. Durante el día, cualquier compradora puede incorporar transferencias de inventario de una tienda a otra. Sería ideal si Linda pudiera ver las transferencias pendientes incorporadas por Virginia y viceversa.

Cada compradora tiene una aplicación Excel con VBA ejecutándose en su escritorio. Ambas importan los datos de la caja registradora y tienen rutinas VBA que facilitan la creación de informes de tabla dinámica que les ayudan a tomar decisiones de compra.

Tratar de almacenar los datos de la transferencia en un archivo Excel común genera problemas. Cuando la compradora trata de escribir en el archivo Excel, el archivo se convierte en sólo lectura para la otra compradora. Con un libro compartido, Excel deshabilita la posibilidad de crear tablas dinámicas y esto es necesario en nuestra aplicación.

Ni Linda ni Virginia cuentan con la versión profesional de Office, por lo que no tienen Access ejecutándose en sus PC.

La solución es generar una base de datos Access en un disco de red que puedan ver tanto Linda como Virginia.

La aplicación proporciona acceso multiusuario a ambas compradoras. Tanto Linda como Virginia pueden leer y escribir en la tabla al mismo tiempo. El único momento en el que podría ocurrir un conflicto es si ambas tratan de actualizar el mismo registro al mismo tiempo.

MX Utilities – El complemento de Excel que hace el trabajo más fácil

Estándar

A través de los años, resolviendo dudas, aprendiendo técnicas me he dado cuenta de que Excel a pesar de ser una potente y flexible herramienta no puede resolver o efectuar las tareas que uno desearía realizar o por lo menos no de la manera más fácil, es por esta razón que desde hace tres años comencé a programar un complemento para ampliar las funcionalidades de Excel y realizar tareas que pudieran parecer imposibles de realizar en la interfaz de Excel, dicho complemento le llame: iSoft Utilities

Pero… ¿qué es un complemento? He aquí una explicación detallada de lo que son y como instalarlos.

Actualmente sigo adicionando nuevas características al complemento que comencé a programar hace tres años y al cual he nombrado como: MX Utilities

¿Qué ventajas aporta utilizar MX Utilities en el uso diario de Excel?, algunas de las ventajas son:

  • Insertar comentarios de imagen en celdas
  • Poder listar archivos dentro de una carpeta y subcarpetas de la misma
  • Poder ejecutar herramientas como el bloc de notas y la calculadora
  • Transponer rangos de forma horizontal a vertical y con ello poder emplear tablas dinámicas
  • Filtrado de rangos de datos pudiendo utilizar expresiones regulares
  • Crear lista de validación que no dependan de rangos de datos
  • Crear graficas velocímetro de manera personalizada
  • Crear combinaciones de rangos

Adicionalmente el complemento añade 6 funciones que podemos usar en nuestras hojas de calculo, las cuales son:

  • ConcatenarRango
  • ContarUnicos
  • ContienePatron
  • ExtraerNumeros
  • ExtraerPatron
  • Invertir

En próximas publicaciones ire mostrando ejemplos de cada una de las funcionalidades de dicho complemento además de ir agregando nuevas e ir mejorando las presentación visual del mismo.

Les comparto la liga del complemento MX Utilities

Creación y uso de Listas en VBA/Excel

Estándar

Para todos aquellos que han programado en Python, sabrán la gran ventaja que se obtiene al utilizar Listas o quizás para aquellos que han probado Ruby y han utilizado Arrays, ambos… Listas y Arrays son lo mismo. Son colecciones ordenadas de elementos a las cuales se accede a través de un índice y con dichos objetos la programación se simplifica enormemente, ya que poseen métodos y propiedades que facilitan la labor del programador. Desgraciadamente para todos aquellos que programamos en VBA/Excel tendremos que conformarnos al usar simples matrices las cuales nos brindan pocas posibilidades al respecto, ya que las mismas no son objetos.

Mi fascinación respecto a estos objetos, me llevo a la tarea de tratar de emularlos en VBA/Excel con la creación de una clase llamada clsList la cual incorpora algunos elementos Python y otros de Ruby.

Espero que esta clase sea de gran utilidad para todos aquellos programadores de VBA/Excel que están en busca de herramientas que faciliten su labor diario y al mismo tiempo recibir comentarios y sugerencias para ampliar y mejorar la clase.

Comparto con ustedes el siguiente enlace, donde podrán descargar el módulo de clase y un libro con ejemplos sobre el funcionamiento de la misma.

Clase Lista

Web scraping rudimentario con VBA/Excel

Estándar

Muchos ya sabemos que Excel nos permite importar datos desde diversas fuentes:

  • Archivos de texto (csv)
  • Bases de datos (Access u otras…)
  • Cubos de información
  • Archivos XML (que en teoría seria el reemplazo de los archivos de texto plano)
  • Desde Web

En este caso nos centraremos en las importaciones de datos desde la Web, si bien Excel dispone de un asistente que nos permite seleccionar tablas de datos (HTML) para ser volcadas a una hoja de cálculo y además dicho asistente proporciona la opción de actualizar (descargar los datos) “x” minutos según indiquemos entre otras opciones más… Pero habrá situaciones en los datos que tengamos que extraer de una página web no estén distribuidos en una tabla HTML con lo cual su importación se complica, es justo en estas situaciones cuando debemos echar mano de una técnica llamada Web scraping.

Pero… que es el Web scraping? La respuesta fácil y corta es: es una técnica para la extracción de datos de páginas web usando ciertas herramientas. Pueden consultar mayor información en la wikipedia.

Lenguajes como Python, Ruby y VBA permiten echar mano de esta técnica. En esta ocasión nos centraremos en VBA/Excel.
Continuar leyendo