Este tutorial te enseñará cómo desarrollar una macro en Excel que consuma datos de una API pública y los presente en un dashboard interactivo. Al final, tendrás una herramienta poderosa que podrá actualizar datos automáticamente y presentarlos de manera visual y efectiva.
Conceptos Fundamentales de APIs y VBA
Antes de sumergirnos en el código, es importante entender qué es una API y cómo funciona con VBA. Una API (Interfaz de Programación de Aplicaciones) es un conjunto de reglas y protocolos que permite a diferentes aplicaciones comunicarse entre sí. En términos simples, una API actúa como un mensajero que lleva tu solicitud al sistema que quieres acceder y luego devuelve la respuesta.
¿Cómo funciona el proceso?
Cuando trabajamos con APIs desde VBA, el proceso consiste en:
- Enviar una solicitud HTTP a la API
- Recibir una respuesta (generalmente en formato JSON)
- Parsear la respuesta para extraer los datos
- Procesar y presentar los datos en nuestro dashboard
Configuración Inicial del Proyecto
Paso 1: Habilitar la pestaña Desarrollador
Si no tienes habilitada la pestaña Desarrollador en Excel:
- Ve a Archivo → Opciones → Personalizar cinta
- Marca la casilla "Desarrollador" en el panel derecho
- Haz clic en Aceptar
Paso 2: Crear un nuevo libro y guardarlo como macro habilitado
Para guardar las macros, necesitas un formato específico:
- Crea un nuevo libro de Excel
- Ve a Archivo → Guardar como
- Selecciona "Libro de Excel habilitado para macros (*.xlsm)"
Paso 3: Preparar las referencias necesarias
Necesitarás algunas referencias para trabajar con HTTP y JSON:
- Ve a la pestaña Desarrollador → Visual Basic (o presiona Alt+F11)
- Ve a Herramientas → Referencias y selecciona:
- Microsoft Scripting Runtime (para Dictionary)
- Microsoft WinHTTP Services
Realizando Solicitudes a una API
Para conectarnos a una API, utilizaremos el objeto WinHttp.WinHttpRequest
. Este objeto nos permite realizar solicitudes HTTP a servidores web.
Estructura básica de una solicitud API en VBA
Sub ConsumirAPI()
Dim objHTTP As Object
Dim URL As String
Dim respuesta As String
' Definir la URL de la API
URL = "https://api.ejemplo.com/datos"
' Crear objeto HTTP
Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")
' Realizar la solicitud GET
objHTTP.Open "GET", URL, False
objHTTP.Send
' Obtener la respuesta
respuesta = objHTTP.responseText
' Aquí procesaríamos la respuesta
Debug.Print respuesta
End Sub
Utilizando parámetros en la API
Muchas APIs requieren parámetros para filtrar resultados. Podemos agregarlos a la URL o enviarlos como parte de la solicitud:
' Para una API de clima por ciudad
URL = "https://api.openweathermap.org/data/2.5/weather?q=Madrid&appid=TU_API_KEY"
Autenticación con API Key
Muchas APIs requieren una clave de autenticación (API Key):
' Añadir encabezado de autorización
objHTTP.setRequestHeader "Authorization", "Bearer TU_API_KEY"
Procesando Respuestas JSON
La mayoría de las APIs modernas devuelven datos en formato JSON, que debemos procesar para utilizarlos en Excel.
Instalando una librería para procesar JSON
El método más sencillo es utilizar la librería VBA-JSON:
- Descarga el archivo JsonConverter.bas desde GitHub: https://github.com/VBA-tools/VBA-JSON/57
- Importa el archivo en tu proyecto VBA (Archivo → Importar archivo)
Parseando JSON con VBA-JSON
Una vez importada la librería, podemos convertir fácilmente una respuesta JSON en un objeto Dictionary de VBA:
' Asegúrate de haber importado JsonConverter.bas
Dim json As Object
Set json = JsonConverter.ParseJson(respuesta)
' Acceder a un valor simple
MsgBox json("nombre")
' Acceder a un valor anidado
MsgBox json("ubicacion")("ciudad")
' Recorrer un array
Dim item As Object
For Each item In json("items")
Debug.Print item("nombre")
Next item
Creando un Dashboard con los Datos
Con los datos ya disponibles, podemos crear nuestro dashboard utilizando diferentes elementos visuales.
Paso 1: Crear una hoja para el dashboard
' Crear y nombrar una hoja para el dashboard
Sub CrearHojaDashboard()
Dim ws As Worksheet
On Error Resume Next
Set ws = Sheets("Dashboard")
If ws Is Nothing Then
Sheets.Add.Name = "Dashboard"
End If
Sheets("Dashboard").Activate
End Sub
Paso 2: Crear tablas para mostrar los datos
' Insertar datos en una tabla
Sub CrearTabla(datos As Object, fila As Integer, columna As Integer)
Dim i As Integer, j As Integer
' Escribir encabezados
For i = 0 To datos(1).Count - 1
Cells(fila, columna + i).Value = datos(1).Keys()(i)
Next i
' Escribir datos
For i = 1 To datos.Count
For j = 0 To datos(i).Count - 1
Cells(fila + i, columna + j).Value = datos(i).Items()(j)
Next j
Next i
' Formatear como tabla
Range(Cells(fila, columna), Cells(fila + datos.Count, columna + datos(1).Count - 1)).Select
ActiveSheet.ListObjects.Add(xlSrcRange, Selection, , xlYes).Name = "Tabla1"
End Sub
Paso 3: Crear gráficos a partir de los datos
Los gráficos son esenciales para un buen dashboard, ya que permiten visualizar tendencias y patrones:
' Crear un gráfico de columnas
Sub CrearGrafico(rangoFuente As Range, titulo As String)
Dim grafico As Object
' Crear gráfico de columnas
Set grafico = ActiveSheet.Shapes.AddChart2(201, xl3DColumn)
' Configurar fuente de datos
grafico.Chart.SetSourceData Source:=rangoFuente
' Configurar título y leyenda
grafico.Chart.HasTitle = True
grafico.Chart.ChartTitle.Text = titulo
grafico.Chart.HasLegend = True
' Posicionar y dimensionar el gráfico
grafico.Left = rangoFuente.Left
grafico.Top = rangoFuente.Top + rangoFuente.Height + 20
grafico.Width = 450
grafico.Height = 250
End Sub
Paso 4: Implementar filtros interactivos usando controles de formulario
Para hacer el dashboard interactivo, podemos agregar controles como cuadros combinados:
' Crear un cuadro combinado para filtrar
Sub CrearFiltro(hoja As Worksheet, valores As Variant, nombre As String, fila As Integer, columna As Integer)
Dim cmbBox As OLEObject
' Añadir el cuadro combinado
Set cmbBox = hoja.OLEObjects.Add(ClassType:="Forms.ComboBox.1", _
Left:=hoja.Cells(fila, columna).Left, _
Top:=hoja.Cells(fila, columna).Top, _
Width:=120, Height:=20)
' Nombrar el control
cmbBox.Name = nombre
' Añadir valores a la lista
With hoja.OLEObjects(nombre).Object
.Clear
For i = LBound(valores) To UBound(valores)
.AddItem valores(i)
Next i
End With
End Sub
Paso 5: Crear una tabla dinámica para análisis avanzado
Las tablas dinámicas son herramientas poderosas para análisis interactivo:
' Crear una tabla dinámica
Sub CrearTablaDinamica(rangoFuente As Range)
Dim ws As Worksheet
Dim pc As PivotCache
Dim pt As PivotTable
' Crear nueva hoja para la tabla dinámica
Set ws = Sheets.Add
ws.Name = "Análisis"
' Crear cache y tabla dinámica
Set pc = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rangoFuente)
Set pt = pc.CreatePivotTable(TableDestination:=ws.Range("A3"), TableName:="TablaDinamica1")
' Configurar campos (personaliza según tus datos)
With pt
.PivotFields("Categoría").Orientation = xlRowField
.PivotFields("Categoría").Position = 1
.PivotFields("Fecha").Orientation = xlColumnField
.PivotFields("Fecha").Position = 1
.PivotFields("Valor").Orientation = xlDataField
.PivotFields("Valor").Function = xlSum
.PivotFields("Valor").NumberFormat = "#,##0"
End With
End Sub
Ejemplo Completo: Dashboard con Datos de una API Pública
Ahora, juntemos todo en un ejemplo completo utilizando una API pública. Usaremos una API ficticia de datos climáticos como ejemplo:
Sub CrearDashboardClimatico()
' Declaraciones
Dim objHTTP As Object
Dim URL As String
Dim respuesta As String
Dim json As Object
Dim wsDatos As Worksheet
Dim wsDashboard As Worksheet
Dim ultFila As Integer, ultColumna As Integer
Dim rangoTabla As Range
Dim i As Integer
Dim ciudad As String
' Configurar ciudad para consulta
ciudad = "Madrid"
' Definir URL de API (ejemplo ficticio)
URL = "https://api.ejemplo.com/clima?ciudad=" & ciudad
' Preparar hojas
Application.ScreenUpdating = False
' Comprobar/crear hojas
On Error Resume Next
Set wsDatos = Sheets("Datos")
If wsDatos Is Nothing Then
Set wsDatos = Sheets.Add
wsDatos.Name = "Datos"
End If
Set wsDashboard = Sheets("Dashboard")
If wsDashboard Is Nothing Then
Set wsDashboard = Sheets.Add
wsDashboard.Name = "Dashboard"
End If
On Error GoTo 0
' Realizar solicitud a la API
Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")
objHTTP.Open "GET", URL, False
objHTTP.Send
respuesta = objHTTP.responseText
' Parsear JSON
Set json = JsonConverter.ParseJson(respuesta)
' Preparar datos en hoja Datos
wsDatos.Cells.Clear
wsDatos.Range("A1").Value = "Fecha"
wsDatos.Range("B1").Value = "Temperatura"
wsDatos.Range("C1").Value = "Humedad"
wsDatos.Range("D1").Value = "Condición"
' Insertar datos del pronóstico
For i = 1 To json("forecast").Count
wsDatos.Range("A" & i + 1).Value = json("forecast")(i)("date")
wsDatos.Range("B" & i + 1).Value = json("forecast")(i)("temp")
wsDatos.Range("C" & i + 1).Value = json("forecast")(i)("humidity")
wsDatos.Range("D" & i + 1).Value = json("forecast")(i)("condition")
Next i
' Determinar rango de la tabla
ultFila = wsDatos.Cells(wsDatos.Rows.Count, "A").End(xlUp).Row
ultColumna = 4
Set rangoTabla = wsDatos.Range(wsDatos.Cells(1, 1), wsDatos.Cells(ultFila, ultColumna))
' Crear Dashboard
wsDashboard.Cells.Clear
' Título del Dashboard
With wsDashboard.Range("A1")
.Value = "DASHBOARD CLIMÁTICO: " & UCase(ciudad)
.Font.Size = 18
.Font.Bold = True
End With
' Información actual
wsDashboard.Range("A3").Value = "CONDICIONES ACTUALES:"
wsDashboard.Range("A3").Font.Bold = True
wsDashboard.Range("A4").Value = "Temperatura:"
wsDashboard.Range("B4").Value = json("current")("temp") & " °C"
wsDashboard.Range("A5").Value = "Humedad:"
wsDashboard.Range("B5").Value = json("current")("humidity") & " %"
wsDashboard.Range("A6").Value = "Condición:"
wsDashboard.Range("B6").Value = json("current")("condition")
' Crear gráfico de temperaturas
Dim graficoTemp As ChartObject
Set graficoTemp = wsDashboard.ChartObjects.Add(Left:=100, Width:=450, Top:=100, Height:=250)
With graficoTemp.Chart
.SetSourceData Source:=wsDatos.Range("A1:B" & ultFila)
.ChartType = xlColumnClustered
.HasTitle = True
.ChartTitle.Text = "Pronóstico de Temperatura"
.Axes(xlCategory).TickLabels.Orientation = 45
End With
' Crear gráfico de humedad
Dim graficoHum As ChartObject
Set graficoHum = wsDashboard.ChartObjects.Add(Left:=100, Width:=450, Top:=380, Height:=250)
With graficoHum.Chart
.SetSourceData Source:=wsDatos.Range("A1:A" & ultFila & ",C1:C" & ultFila)
.ChartType = xlLine
.HasTitle = True
.ChartTitle.Text = "Pronóstico de Humedad"
.Axes(xlCategory).TickLabels.Orientation = 45
End With
' Crear tabla resumen
wsDashboard.Range("G3").Value = "RESUMEN PRONÓSTICO"
wsDashboard.Range("G3").Font.Bold = True
' Copiar datos de pronóstico
wsDatos.Range("A1:D" & ultFila).Copy
wsDashboard.Range("G4").PasteSpecial xlPasteValues
' Formatear como tabla
wsDashboard.ListObjects.Add(xlSrcRange, wsDashboard.Range("G4").CurrentRegion, , xlYes).Name = "TablaPronostico"
' Activar la hoja de Dashboard
wsDashboard.Activate
wsDashboard.Range("A1").Select
Application.ScreenUpdating = True
MsgBox "Dashboard creado correctamente para " & ciudad, vbInformation
End Sub
Mejoras para tu Dashboard
Tabla de componentes comunes para un dashboard
Componente | Propósito | Función VBA para crear |
---|---|---|
Tablas | Mostrar datos detallados | Range().ListObjects.Add |
Gráficos | Visualizar tendencias | Shapes.AddChart2 o ChartObjects.Add |
Tablas dinámicas | Análisis interactivo | PivotCaches.Create |
ComboBox | Filtros interactivos | OLEObjects.Add |
Botones | Ejecutar acciones | ActiveSheet.Buttons.Add |
Etiquetas | Mostrar KPIs y métricas | Range("A1").Value |
Automatización de actualizaciones
Puedes programar actualizaciones regulares del dashboard usando el evento Workbook_Open()
o mediante temporizadores:
Private Sub Workbook_Open()
' Actualizar al abrir
CrearDashboardClimatico
End Sub
Sub ActualizarPeriodicamente()
' Configurar un temporizador para actualizar cada hora
Application.OnTime Now + TimeValue("01:00:00"), "CrearDashboardClimatico"
End Sub
Implementación de un Dashboard con una API Real
Vamos a ver un ejemplo más concreto utilizando la API de universidades que se menciona en15. Esta API devuelve información sobre universidades por país:
Sub DashboardUniversidades()
' Declaraciones
Dim objHTTP As Object
Dim URL As String
Dim respuesta As String
Dim json As Object
Dim wsDatos As Worksheet
Dim wsDashboard As Worksheet
Dim wsConfig As Worksheet
Dim i As Integer, fila As Integer
Dim pais As String
' Configuración inicial
Application.ScreenUpdating = False
' Crear hoja de configuración
On Error Resume Next
Set wsConfig = Sheets("Configuración")
If wsConfig Is Nothing Then
Set wsConfig = Sheets.Add
wsConfig.Name = "Configuración"
' Preparar opciones de países
wsConfig.Range("A1").Value = "País (en inglés)"
wsConfig.Range("A2").Value = "Spain"
wsConfig.Range("A3").Value = "United States"
wsConfig.Range("A4").Value = "Germany"
wsConfig.Range("A5").Value = "France"
wsConfig.Range("A6").Value = "United Kingdom"
' Selección actual
wsConfig.Range("C1").Value = "País seleccionado:"
wsConfig.Range("D1").Value = "Spain"
' Crear botón para actualizar
Dim btnActualizar As Button
Set btnActualizar = wsConfig.Buttons.Add(200, 30, 120, 30)
With btnActualizar
.Caption = "Actualizar Dashboard"
.OnAction = "DashboardUniversidades"
End With
End If
' Comprobar/crear hojas
Set wsDatos = Sheets("Datos")
If wsDatos Is Nothing Then
Set wsDatos = Sheets.Add
wsDatos.Name = "Datos"
End If
Set wsDashboard = Sheets("Dashboard")
If wsDashboard Is Nothing Then
Set wsDashboard = Sheets.Add
wsDashboard.Name = "Dashboard"
End If
On Error GoTo 0
' Obtener país seleccionado
pais = wsConfig.Range("D1").Value
' Crear URL de la API
URL = "http://universities.hipolabs.com/search?country=" & WorksheetFunction.EncodeURL(pais)
' Realizar solicitud a la API
Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")
objHTTP.Open "GET", URL, False
objHTTP.Send
respuesta = objHTTP.responseText
' Parsear JSON
Set json = JsonConverter.ParseJson(respuesta)
' Limpiar hoja de datos
wsDatos.Cells.Clear
' Escribir encabezados
wsDatos.Range("A1").Value = "Nombre"
wsDatos.Range("B1").Value = "País"
wsDatos.Range("C1").Value = "Dominio"
wsDatos.Range("D1").Value = "Página Web"
' Escribir datos
fila = 2
For i = 1 To json.Count
wsDatos.Range("A" & fila).Value = json(i)("name")
wsDatos.Range("B" & fila).Value = json(i)("country")
' Dominios (puede haber varios)
If json(i)("domains").Count > 0 Then
wsDatos.Range("C" & fila).Value = json(i)("domains")(1)
End If
' Páginas web (puede haber varias)
If json(i)("web_pages").Count > 0 Then
wsDatos.Range("D" & fila).Value = json(i)("web_pages")(1)
End If
fila = fila + 1
Next i
' Formatear como tabla
wsDatos.Range("A1").CurrentRegion.Select
wsDatos.ListObjects.Add(xlSrcRange, Selection, , xlYes).Name = "TablaUniversidades"
' Crear Dashboard
wsDashboard.Cells.Clear
' Título del Dashboard
With wsDashboard.Range("A1")
.Value = "UNIVERSIDADES DE " & UCase(pais)
.Font.Size = 18
.Font.Bold = True
End With
' Mostrar estadísticas
wsDashboard.Range("A3").Value = "ESTADÍSTICAS:"
wsDashboard.Range("A3").Font.Bold = True
wsDashboard.Range("A4").Value = "Total universidades:"
wsDashboard.Range("B4").Value = json.Count
' Crear gráfico de dominios más comunes
wsDashboard.Range("A6").Value = "Análisis de Dominios:"
wsDashboard.Range("A6").Font.Bold = True
' Crear tabla resumen
wsDashboard.Range("D3").Value = "LISTADO DE UNIVERSIDADES"
wsDashboard.Range("D3").Font.Bold = True
' Copiar los primeros 20 registros (o menos si no hay tantos)
Dim copiarFilas As Integer
copiarFilas = WorksheetFunction.Min(20, json.Count)
wsDatos.Range("A1:B" & copiarFilas + 1).Copy
wsDashboard.Range("D4").PasteSpecial xlPasteValues
' Formatear como tabla
wsDashboard.ListObjects.Add(xlSrcRange, wsDashboard.Range("D4").CurrentRegion, , xlYes).Name = "TablaResumen"
' Crear una tabla dinámica para análisis
Dim pc As PivotCache
Dim pt As PivotTable
Dim rangoDatos As Range
Set rangoDatos = wsDatos.Range("A1").CurrentRegion
' Crear cache y tabla dinámica
Set pc = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rangoDatos)
Set pt = pc.CreatePivotTable(TableDestination:=wsDashboard.Range("A20"), TableName:="TablaDinamicaUniv")
' Configurar campos
With pt
.PivotFields("País").Orientation = xlRowField
.PivotFields("País").Position = 1
.AddDataField .PivotFields("Nombre"), "Conteo de Universidades", xlCount
End With
' Crear un gráfico basado en la tabla dinámica
Dim graficoPT As ChartObject
Set graficoPT = wsDashboard.ChartObjects.Add(Left:=300, Width:=400, Top:=300, Height:=250)
With graficoPT.Chart
.SetSourceData Source:=pt.TableRange1
.ChartType = xlPie
.HasTitle = True
.ChartTitle.Text = "Distribución de Universidades"
End With
' Activar la hoja de Dashboard
wsDashboard.Activate
wsDashboard.Range("A1").Select
Application.ScreenUpdating = True
Application.CutCopyMode = False
MsgBox "Dashboard de universidades actualizado para " & pais, vbInformation
End Sub
Conclusión
En este tutorial, has aprendido a crear un dashboard en Excel que consume datos de una API pública utilizando VBA. Los pasos esenciales para este proceso son:
- Realizar una solicitud HTTP a la API
- Procesar la respuesta JSON
- Organizar los datos en una estructura lógica
- Crear visualizaciones como tablas, gráficos y tablas dinámicas
- Implementar controles interactivos
Recuerda que este es solo un punto de partida, y puedes personalizar y mejorar tu dashboard según tus necesidades específicas. Con estas técnicas, podrás automatizar la recopilación de datos y presentarlos de manera profesional e interactiva.
Referencias y Recursos Adicionales
Para profundizar en estos temas, puedes consultar las fuentes citadas en este artículo y explorar la documentación oficial de Microsoft sobre VBA y Excel.
También puedes encontrar bibliotecas adicionales y ejemplos en GitHub, especialmente para el procesamiento de JSON y la creación de interfaz de usuario avanzada en Excel.