VBA: Importar Table HTML desde Web a Excel

Tiempo atrás publiqué una forma de interactuar con formularios de páginas Web (ver).
Relacionado con este post y sobre todo con el del día anterior, donde necesitamos recuperar datos en una Table (de HTML) de una Web y llevarlos a nuestra hoja de Excel, expongo el siguiente artículo.

Es importante identificar en el código fuente de la Web el nombre o Id de la 'Table'.
Si accedemos a la Web en cuestión del ejemplo:
http://es.investing.com/indices/spain-35-components
y desde la Table presionamos botón derecho y Inspeccionar elemento, veremos el Id buscado:

haz clic en la imagen

En nuestro ejemplo la Table se identifica con el Id:"cr1", y así nos referiremos en el código de nuestra macro.

A continuación añadimos rótulos a nuestro encabezado en la hoja 'IBEX35', e incorporamos en la ventana de código de nuestro módulo incluimos el siguiente procedimiento:

Sub ImportarTableHTML_a_Excel()
'Tener REFERENCIA ACTIVA Microsoft Internet Controls !!!

Dim IE As Object
Dim fila As Integer, col As Integer
Dim elemCollection As Object, curHTMLRow As Object

Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True
IE.navigate ("http://es.investing.com/indices/spain-35-components")
 
'nos aseguramos que la web se ha descargado completamente
While IE.Busy Or IE.readyState <> READYSTATE_COMPLETE
    DoEvents
Wend

'Limpiamos el rango destino de nuestra hoja de calculo
ThisWorkbook.Sheets("IBEX35").Activate
Range("A2:I36").ClearContents

'identificamos la Table, mediante su Id, para cargarla
Set elemCollection = IE.Document.getElementById("cr1")

' Rows(0) sería el título de la tabla, por eso lo saltamos
'(Ya tenemos nuestros rótulos)
'ahora recorremos todas las filas y columnas de la Table en HTML
For fila = 1 To elemCollection.Rows.Length - 1
    Set curHTMLRow = elemCollection.Rows(fila)
    For col = 0 To curHTMLRow.Cells.Length - 1
        'llevamos los datos a nuestra hoja de cálculo
        Set celda = Sheets("IBEX35").Cells(fila + 1, col + 1)
        celda.Value = "'" & curHTMLRow.Cells(col).InnerText
        'tratamos los valores numéricos como número...
        If IsNumeric(celda) Then celda.Value = Replace(celda.Value, ",", ".")
    Next col
Next fila

'Cerramos Internet Explorer
IE.Quit
'y limpiamos memoria
Set IE = Nothing
Set curHTMLRow = Nothing
Set celda = Nothing
End Sub

Al lanzar la macro el resultado es:

Debemos tener mucha precaución, ya que la carga se realiza, según la Web, intercambiando los separadores decimales!!!.
En el código de más arriba se le da un tratamiento para que los números sean almacenados como números, empleando nuestro sistema de separadores.

Seguir leyendo...

Exportar datos desde Web a Excel

Todos conocemos la opción de Importar Datos Web desde las herramientas de Excel (ver post).

Pero también todos hemos sufrido que en ciertas páginas Web (con 'banners' u otras funciones html) el método se hace complejo yen ocasiones imposible.

Hoy veremos como salvar algunos de estos contratiempos, Exportando a Excel.
Necesitaremos emplear el navegador Internet Explorer (no Chrome, no Firefox, etc).

Accedemos desde IE a la web deseada, por ejemplo:
http://es.investing.com/indices/spain-35-components
donde aparece la cotización del IBEX35 de las Bolsas españolas.

Desde esa Web
1-haremos clic derecho sobre los datos que deseamos importar a Excel
2-desde el menú contextual abierto, haremos clic en la opción 'Exportar a Excel'
3-una vez ejecutado se abrirá un Libro de Excel nuevo donde incorporará la conexión de datos a dichos datos de la Web.

El paso uno, clic derecho sobre los datos:

haz clic en la imagen

En el siguiente paso se genera la conexión en un libro de Excel

Finaliza el proceso, de manera automática, con la actualización de los datos en nuestra hoja de cálculo:

Con la conexión generada en nuestro libro de Excel, podemos configurarla como cualquier otra conexión...

Seguir leyendo...

Círculos de Validación

Un sencillo paso para indicar qué celdas no cumplen las reglas de validación en nuestra hoja de cálculo.

Partimos de un rango con ciertos valores (introducidos antes de crear la regla de validación):

A continuación creamos nuestra Validación de datos sobre esas celdas, configuramos una regla simple, donde únicamente permitimos valores positivos:

Obviamente existen varias celdas que incumplen la regla de validación. Para resaltar cuáles son existen los Círculos de validación.
Accedemos a ellos desde la Ficha Datos > grupo Herramientas de datos > botón Validación > desplegable Rodear con un círculo los datos no válidos:

haz clic en la imagen

Automáticamente se remarcarán aquellas celdas que incumplan las reglas de validación aplicadas.

Para quitar los círculos basta presionar el botón correspondiente desde:
Ficha Datos > grupo Herramientas de datos > botón Validación > desplegable Borrar círculos de validación

Esta situación se puede generar bien por haber añadido valores a las celdas antes de configurar la Regla de validación, y también por que se haya pegado desde otro lugar datos sobre celdas con validación...
Esto es un problema no solventado de esta funcionalidad... ya que deja abierta la puerta al incumplimiento de nuestras reglas.

Seguir leyendo...

Vincular Imagen a un Desplegable

Frecuentemente me llegan preguntas de cómo vincular al valor de una celda una imagen, guardada en un banco de fotos del libro de Excel.
Hoy veremos una manera de conseguirlo atemporal, ya que por desgracia, los métodos con los que es posible conseguirlo, han ido cambiando a lo largo de las versiones (ver post para versión 2007)

Partiremos del siguiente banco de fotos en nuestro libro de trabajo, en otra hoja 'Banderas', al que nos hemos preocupado de asignarles diferentes Nombres definidos:

Los nombres definidos aplicados corresponden a:
Colombia =Banderas!$C$2
España =Banderas!$C$3
México =Banderas!$C$4
Ecuador =Banderas!$C$5
Argentina =Banderas!$C$6

Un punto importante, previo a la resolución del problema, es desmarcar la opción de Lineas de división de la hoja:
Ficha Vista > grupo Mostrar > check de Lineas de cuadrícula

De esta manera evitaremos la futura imagen mostrada tenga un borde visible no deseado.

También es importante que las imágenes estén centradas en el interior de las celdas...

Siguiente paso, en nuestra hoja de información, en la hoja 'Report' en la celda A2 añadimos una validación de datos con origen en:
=Banderas!$B$2:$B$6
que nos permite desplegar los nombres de los diferentes países de nuestra lista.

A continuación marcamos la celda de cualquiera de las banderas (ojo LA CELDA!!, no la imagen!), la copiamos y pegamos como imagen vinculada en nuestra hoja 'Report' a la derecha de nuestra validación, por ejemplo en la celda C2.
IMPORTANTE pegarla como imagen vinculada:

Una vez pegada generamos un nuevo nombre definido, al que llamaré 'PaisElegido' y al que asignaremos la fórmula:
=INDIRECTO(Report!$A$2)

Este paso es fundamental, ya que es precisamente el paso donde asociamos el valor desplegado en la celda A2 con la imagen a mostrar.

Último paso.
Seleccionamos la imagen vinculada pegada en la hoja 'Report', y en la barra de fórmula reemplazamos la fórmula que aparece:
=Banderas!C2
(o similar)
por
=PaisElegido

Y hemos finalizado, ya podemos desplegar cualquiera de los países en la celda A2, que la imagen corresponderá a dicho país.

Seguir leyendo...

Añadir Imágenes al Eje de un Gráfico

Se trata hoy de incorporar unas imágenes en el eje de nuestro gráfico.
Como siempre tendremos que engañar a nuestra hoja de cálculo favorita.

Nuestro objetivo es lograr un gráfico como el siguiente:

Partiremos de los siguientes datos de población, sobre los cuales construiremos un gráfico primigenio del tipo de Columnas Agrupadas.
El primer truco, como tantas otras veces, es añadir una serie de datos Auxiliar con la fórmula igual para todos los puntos:
=-40%*MIN($B$2:$B$6)
Esto nos permitirá colocar las imágenes de las banderas por debajo del eje...

Al seleccionar nuestro rango A1:C6 e Insertar Gráfico de Columnas Agrupadas obtendremos lo siguiente.
Después de:
1- Eliminar la Leyenda
2- Modificar el texto del Título del gráfico ('Población')
3- Cambiar el tipo o formato de número de las etiquetas del eje vertical por: #.##0;
4- Añadir un efecto de Iluminado a las columnas
5- Añadirle un efecto de Bisel.
6- Cambiamos las etiquetas del eje horizontal (desde formato de eje), dando un distancia del eje de 100 y cambiando la Posición de etiqueta a Bajo. (MUY IMPORTANTES!)

El siguiente paso es muy importante. Debemos seleccionar la Serie Auxiliar y cambiarle el Tipo de gráfico por uno de Dispersión

Una vez incorporado el cambio de la serie 'Auxiliar' a nuestro gráfico, quitaremos las dos etiquetas de los ejes secundarios. Basta seleccionarlos y presionar el botón del teclado Suprimir:

Último paso.
Necesitamos en imagen nuestras banderas (de los distintos países).
Es importante que el tamaño de éstas no sea muy grande. En mi caso el tamaño ronda el 0.5 cm de alto y 0.6 cm de ancho.

Seleccionamos una a una cada bandera y marcando exclusivamente el punto correspondiente de la serie 'Auxiliar' pegamos

Repetiremos el proceso de seleccionar, copiar y pegar sobre cada uno de los puntos de la serie auxiliar hasta completar nuestro gráfico.
Tal como se mostraba al inicio del post.

Seguir leyendo...

VBA: Eliminando Columnas en Excel

Un lector preguntaba por una forma de eliminar columnas por grupos en Excel de manera masiva:

[...]No soy muy experto en programación de VB y he tratado de basarme en esta rutina para eliminar columnas de una hoja de excel donde hay 7 columnas ocultas que no requiero y el archivo es bastante extenso en numero de columnas. Es decir que la rutina debe eliminar 7 columnas y luego se salta una y elimina otras 7 teniendo como limite e inicio una ubicacion especifica. En la rutina anterior esta la base pero no logro descifrar como puedo organizar lo que necesito. Agradezco toda ayuda que me puedan dar[...]

La solución la obtendremos con una sencilla macro de Excel, que nos permitirá eliminar esos grupos de 7 en 7 columnas indicadas por el usuario empezando por la derecha o por la izquierda.

En la ventana de código de nuestra hoja de trabajo incluimos el siguiente evento:

Sub ElimnarColumnas()
Dim ColEliminar() As Integer    'definimos la Matriz/Array
Dim Start As Integer, Final As Integer
Dim col As Integer

'Preguntamos por el rango de columnas...
Set Rango = Application.InputBox("Selecciona el rango (fila) de análisis", "Selecciona rango", Selection.Address, , , , Type:=8)
'identificamos el número de columna de Inicio y Final
Start = Rango.Item(1).Column
Final = Rango.Columns.Count + Start - 1
 
'redimensionamos el tamaño de la Array
ReDim ColEliminar(1 To Final - Start + 1) As Integer

'comprobamos se ha seleccionado un rango continuo y único.
If Rango.Areas.Count > 1 Then
 MsgBox "No es posible seleccionar un rango discontinuo!!."
 Exit Sub
End If

inicio:
'preguntamos por el sentido del barrido
Sentido = InputBox("Indica el sentido o inicio del rango (Izda-Dcha o Dcha-Izda)", "Inicio del borrado", "Izda-Dcha")

If Sentido = "Izda-Dcha" Then
    'en primer lugar identificamos los números de columna a eliminar
    x = 0
    For i = Start To Final Step 1
    x = x + 1
        If (x Mod 8) <> 0 Then
            ColEliminar(x) = i
        End If
    Next i
    'recorremos los valores de la matriz de derecha a izquierda
    For col = (Final - Start + 1) To 1 Step -1
            On Error Resume Next
            ActiveSheet.Columns(ColEliminar(col)).Delete Shift:=xlShiftToLeft
            On Error GoTo 0
    Next col

ElseIf Sentido = "Dcha-Izda" Then
    x = 1
    For i = Final To Start Step -1
        If (x Mod 8) <> 0 Then
            ActiveSheet.Columns(i).Delete Shift:=xlShiftToLeft
        End If
    x = x + 1
    Next i

Else
    'en caso de no coincidir la respuesta con nada
    'volvemos a preguntar
    GoTo inicio
End If

End Sub

Podemos comprobar como para los datos del ejemplo, seleccionando las columnas E:Y, según la selección de Izda-Dcha o bien Dcha-Izda, el resultado de las columnas eliminadas (o que permanecen) es diferente.

Si elegimos Izda-Dcha deberán permanecer las columnas L y T (con valores 8 y 16):

Si elegimos de Dcha-Izda deberían permanecer las columnas J y R (con valores 6 y 14):

Seguir leyendo...

Personalizar los separadores del sistema en Excel

Un lector preguntaba por una forma de personalizar los separadores decimal y de miles para nuestros números en Excel:

[...] tengo un problema para escribir números grandes, es decir millones, según lo que APA exige para investigaciones científicas como tesis y demás... es un problema porque APA no permite separador de miles con puntos o comillas, sino con espacio en blanco y coma decimal y no logro configurar ambos en el Excel, solo los espacio para los miles... Lo que requiero es configurar números de esta manera: 3 474 526,84[...]

Obviamente podríamos intentar primero empleando los formatos personalizados (ver categoría del blog); si bien en el caso planteado, donde se exige el uso del 'espacio en blanco', no sería posible.

Por ello se plantea una alternativa desde la Opciones de Excel.

El objetivo es entonces cambiar el separador de miles general de Windows (el punto) por un espacio en blanco.

Accedemos a las Opciones de Excel desde la Ficha Archivo > menú Avanzadas > sección Opciones de Edición > apartado Separadores del Sistema. Aquí deberemos desmarcar la opción por defecto (uso de los generales), para proceder a personalizarlos por los caracteres requeridos:

haz clic en la imagen

El resultado, por supuesto, es el esperado:

Seguir leyendo...

Algoritmo de Dijkstra, en busca de la ruta más corta

Aplicaremos hoy la herramienta Solver para solucionar un problema muy típico.
¿cuál es la ruta más corta entre dos puntos?.
Para esto analizaremos el Algoritmo de Dijkstra (lee algo más en Wikipedia)

Nuestro punto de partida es conocer la distribución de los puntos y distintos nodos intermedios entre el Inicio y el Final del camino.
Para nuestro ejemplo tomaremos el siguiente mapa:

Lo más importante ahora, puesto que vamos a aplicar la herramienta Solver es plantear un modelo correcto.
Detallamos, paso a paso, cada uno de las posibles combinaciones para avanzar por las distintas rutas.
Así, manualmente, comenzando desde el punto de Inicio detallamos cada punto, y así sucesivamente en cada nodo:
Inicio => A
Inicio => B
Inicio => C
A => D
A => C
B => C
B => E
C => D
C => E
D => Final
E => Final

Lo vemos trasladado a nuestra hoja de cálculo:

Para facilitar el trabajo he asignado nombres definidos a los rangos de trabajo:
Desde =Ruta!$I$3:$I$13
Distancia_entre_puntos =Ruta!$L$3:$L$13
Hasta =Ruta!$J$3:$J$13
Ruta_elegida =Ruta!$K$3:$K$13

Concretamos en la celda K15 la que será nuestra función objetivo:
=SUMAPRODUCTO(Ruta_elegida;Distancia_entre_puntos)

Dicha celda será en Solver nuestra celda a minimizar.

Finalmente, para completar nuestro modelo, incorporamos un rango donde disponer las restricciones. En estas restricciones se debe verificar que en cada nodo intermedio el número de entradas sea igual al de salidas, excepto al punto de Inicio y punto Final que solo de salir y entrar una única vez.
Este se consigue sumando los valores que devolverá Solver en K3:K13 (serán nuestras celdas cambiantes).

la fórmula empleada en este rango de restricciones, que recoge la idea es:
=SUMAR.SI(Hasta;$I19;Ruta_elegida)-SUMAR.SI(Desde;$I19;Ruta_elegida)

Seguir leyendo...