Estadísticas Actualizadas Coronavirus- Scraping Web – Raspado Web – Insertar Graficos – Parte 5
Haciendo caso a pedidos de suscriptores del canal de You Tube, se introdujo una mejora, la cual consiste en una macro de Excel para agregar gráficas con los datos obtenidos por cada país, es decir se podrá seleccionar un país en especifico, la macro recuperará los datos guardado en Access y los mostrará insertando gráficos en Excel para observar la tendencia de los datos estadísticos guardados en Access desde Excel.
Esta es la quinta parte del ejemplo Estadísticas Mundiales Actualizadas de Coronavirus – Covid 19, donde se muestra entre otras cosas: como INSERTAR GRAFICOS en Excel, como hacer scraping web o raspado web a un página de internet con Excel – VBA y como guardar datos en Access desde Excel, y muchas macros que ya están explicadas en esta Web y en nuestro canal de YouTube, por ello se explicará en forma puntual lo contenido en este libro a pedido de los suscriptores del canal.
Los links a las otras partes del ejemplo macro de Excel para realizar web scraping se muestra a continuación:
Necesitas manejar Excel como un experto?, haz click acá, si quieres aprender sobre Excel en inglés, entonces debes hacer click here. Si lo que necesitas es aprender o profundizar sobre la programación de macros con VBA este es unos de los mejores cursos on line que he visto en internet, te lo recomiendo no te arrepentirás.
Formatos Fecha y Hora Excel Conectar Excel y Access Enviar Mail con Excel Seleccionar o Referencia en VBA Excel
Suscribe a nuestro canal de You Tube, mira el playlist con vídeos relacionados donde podrás ver la macro en acción con una explicación en forma visual que ayudará a entender el ejemplo en forma más fácil.
Macro para INSERTAR GRAFICA en Libro Excel Estadísticas Mundiales Actualizadas Coronavirus en Excel – Scraping Web
El libro de Excel se incorpora una macro que permite agregar grafica a una hoja de Excel, la cual está en la hoja «Tendencia», para ello se debe presionar en el menú personalizado el ícono con un gráfico, ello nos lleva hasta la hoja mencionada llenando un combobox, control ActiveX, con el nombre de todos los países listados en la base de datos de Access.
Recordemos que el ejemplo hace un Scraping Web o Raspado Web a una base de datos de estadísticas mundiales de excelente reputación, extrae los datos de los casos de coronavirus actualizados y los muestra en la hoja de Excel, luego se puede guardar estos datos en una base de datos de Access, con dichos datos vamos a construir una Gráfica para insertar en Excel, sugiero ver las partes anteriores de este ejemplo para un mejor entendimiento, al principio está el link a las diferentes partes, recuerda que cada una tiene un vídeo explicativo para facilitar aún más el entendimiento de esta macro.
En la hoja tendencia se debe seleccionar el país y la macro extraerá los datos desde la base de datos de Access mostrando los datos en Excel e Insertando gráficas en la hoja de Excel con macro, en este caso se insertarán tres gráficos dos de linea de tendencia y uno con el mapa.
Quizás también interese leer:
Como copiar un Grafico de Excel a Word conectando Excel con Word
Como saber y cambiar el nombre de un grafico de Excel
Como copiar imagenes y graficos de Excel a Word conectando Excel con Word
Explicación de la Macro que AGREGA GRAFICOS en EXCEL del Libro Estadísticas Actualizadas Mundiales Covid-19
En primer para que funcione el ejemplo en forma integra, se debe modificar o agregar una macro que es llamada por el nuevo menú «Tendencia» de la barra Ribbon de Excel Personalizada, ello se logra agregando la siguiente macro en un modulo, en este caso se ingresó en el modulo llamado Menu, que se ejecutará cuando se presione el botón personalizado de la Barra Ribbon:
Sub macro19(control As IRibbonControl)
Sheets(«Tendencia»).Select
bb.Range(«A2:P1000»).Clear
With ActiveSheet.ChartObjects.Delete: End With
Call Llenacombo1
End Sub
Si se observa al presionar el botón de la cinta de opciones personalizada denominado «Tendencia», se borran todos los datos o limpia la hoja Tendencia, se eliminan todos los gráficos existentes en la hoja y se llama a la macro que llena el Combobox con los países.
La macro para llenar el Combobox con los países, se conecta a la base de datos de Access a través de una consulta SQL obtiene todos los países distintos, es decir datos únicos, ya que el país está repetido varias veces en la base de datos, la conexión se crea con la siguiente string (ver código completo en el ejemplo):
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
cn.Open «Provider=Microsoft.ACE.OLEDB.12.0; » & «data source=» & mybookindice & «;»
El combobox se carga con los datos obtenidos de la consulta sql de la siguiente forma: primero se debe limpiar el combobox:
d.ComboBox1.Clear
Luego se genera la SQL con los países datos únicos, sin duplicar, así:
Sql = «SELECT DISTINCT Pais FROM DB_COVID_19»
Los datos obtenidos se almacenan en el objeto recordsest «rs» que se creo y se carga al combobox recorriendo cada uno de los datos almacenados en el recordset, de la siguiente forma:
Set rs = cn.Execute(Sql)
Do While rs.EOF = False
d.ComboBox1.AddItem rs.fields(0)
rs.MoveNext
Loop
Luego en la hoja «Tendencia» ya se tiene el Combobox con el nombre de los países, se debe seleccionar país y la macro buscará sus datos insertando gráficos en Excel, la macro está en el Modulo «Consulta».
La macro se conecta a la base de datos y a través de una SQL consulta todos los datos coincidentes con el país seleccionado, el código utilizado es el siguiente:
Set d = Sheets(«Tendencia»)
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
mybookindice = Sheets(«Parametros»).Range(«C2»)
cn.Open «Provider=Microsoft.ACE.OLEDB.12.0; » & «data source=» & mybookindice & «;»
‘Country = «USA»
Sql = «SELECT * FROM DB_COVID_19 WHERE Pais = ‘» & Country & «‘»
Set rs = cn.Execute(Sql)
Los datos obtenidos se pegan desde la fila 22 columna A con la siguiente sentencia:
d.Cells(22, 1).CopyFromRecordset Data:=rs
La macro ordena los datos en caso que se hayan recuperado de la base de datos en forma revuelta, ordena por cantidad de casos, la macro para ordenar datos es la siguiente:
Estos códigos determinan el rango variable tanto filas como columnas, es decir no interesa la cantidad de filas o columnas la macro determina el rango, aunque en este caso ya se sabe de entrada la cantidad de columnas a ordenar, se incorpora a los fines de mostrar como determinar un rango variable o dinámico, obteniendo r2 que es el rango donde está la columna por la cual se ordenarán los datos y r1 es el rango donde se encuentran los datos a ordenar:
pf = 21
uf = d.Range(«A» & Rows.Count).End(xlUp).Row – 1
If d.Range(«A» & uf + 1) <> «Total:» Then uf = d.Range(«A» & Rows.Count).End(xlUp).Row
uc = d.Cells(21, Columns.Count).End(xlToLeft).Address
pc = d.Cells(21, Columns.Count).End(xlToLeft).End(xlToLeft).Address
wc = Mid(uc, InStr(uc, «$») + 1, InStr(2, uc, «$») – 2)
wc1 = Mid(pc, InStr(pc, «$») + 1, InStr(2, pc, «$») – 2)
r2 = wc1 & pf & «:» & wc & uf
r1 = «A» & pf & «:A» & uf
‘sorts the data
ActiveWorkbook.Worksheets(«Tendencia»).Sort.SortFields.Clear
ActiveWorkbook.Worksheets(«Tendencia»).Sort.SortFields.Add Key:=Range(r1) _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets(«Tendencia»).Sort
.SetRange Range(r2)
.header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Por último la macro inserta graficos en excel, básicamente para insertar un gráfico se debe seleccionar el rango con los datos y luego presionar insertar gráfico, es lo que se haría en forma manual, de la misma forma se van a seleccionar los datos sobre los que se requiere graficar en Excel con macro y luego se insertará la gráfica en Excel con código VBA.
La última fila con datos se determina de la siguiente forma (quieres saber como se determina la última fila con datos, pero de una selección), es útil para establecer el rango de datos a graficar.
uf = d.Range(«A» & Rows.Count).End(xlUp).Row
Luego el rango de datos sobre los que se requiere insertar un gráfico en Excel se determina con el siguiente código:
d.Range(«A21:A» & uf & «,C21:C» & uf & «,H21:H» & uf).Select
Luego se determinar la posición de la celda A2 a los fines de determinar el valor top o donde está posicionada en la pantalla la fila 2, se desea obtener ese valor para posicionar el gráfico en ese lugar.
Set ran = d.Range(«A2»)
Ahora se inserta el gráfico dando tamaño y lugar donde se debe ubicar el gráfico, se cambia el tipo de gráfico, el layaout (disposición de datos en e gráfico), se usan los siguientes códigos, se recomienda ejecutar la macro con F8 para ir viendo como se va modificando el gráfico de tamaño posición, titulo, layout, etc..
Set myChart = ActiveSheet.ChartObjects.Add(100, 200, 350, 242)
With myChart
.Chart.SetSourceData Source:=Selection
.Chart.ChartType = xlLineMarkers
‘.Chart.SeriesCollection(1).XValues = Range(«A22:A36»)
.Chart.ApplyLayout (3)
.Chart.ChartTitle.Select
.Chart.ChartTitle.Text = «Total de Casos Coronavirus»
.Top = ran.Top
.Left = 1 ‘ ran.Top
.Width = 350
.Height = 242
End With
Con el segundo gráfico se procede la misma manera, se debe tener en cuenta que la posición izquierda, top , alto y ancho del gráfico se pueden establecer directamente cuando se inserta el gráfico y también con los comando Left,Top, Widht, Height, pero también como se dijo se puede indicar directamente al insertar el gráfico en este caso sería Left=100, Top=200, Widht = 350 y Height=200, de la siguiente forma:
Set myChart = ActiveSheet.ChartObjects.Add(100, 200, 350, 200)
Para el segundo gráfico el código es el siguiente:
d.Range(«A21:A» & uf & «,E21:E» & uf & «,G21:G» & uf & «,I21:I» & uf).Select
Set ran = d.Range(«E2»)
Set myChart = ActiveSheet.ChartObjects.Add(100, 200, 350, 200)
With myChart
.Chart.SetSourceData Source:=Selection
.Chart.ChartType = xlLineMarkers
‘.Chart.SeriesCollection(1).XValues = Range(«A22:A36»)
.Chart.ApplyLayout (3)
.Chart.ChartTitle.Select
.Chart.ChartTitle.Text = «Recuperados, Criticos, Muertos»
.Top = ran.Top
.Left = 351 ‘ ran.Top
.Width = 350
.Height = 242
End With
Para insertar el gráfico con el mapa, si se fijan en el vídeo o ejemplo al buscar el país se busca se muestra la gráfica en Excel con el mapa y el dato de la serie dentro en el caso que el lugar del país en el gráfico lo permita, el codigo es el siguiente:
d.Range(«B21:C21,B» & uf & «:C» & uf).Select
Set ran = d.Range(«J2»)
ActiveSheet.Shapes.AddChart2(494, xlRegionMap, 702, ran.Top, 350, 242).Select
ActiveSheet.ChartObjects(«Gráfico 29742»).Activate
ActiveChart.ChartTitle.Select
ActiveChart.SetElement (msoElementChartTitleNone)
ActiveChart.SetElement (201)
Descarga el Libro Excel Como Hacer Scraping Web o Raspado Web – Insertar Gráficos
Seguidamente se muestra el código de la macro que INSERTAR GRÁFICOS en Excel y al final se encuentra el link para descargar el Libro Excel con el ejemplo de macro, aporta a sostener la esta web si está dentro de tus posibilidades, desde ya muchas gracias.
Código que se ingresa en Modulo «CONSULTA»
Public Country
Sub searchcountry()
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Dim cn As ADODB.Connection, rs As ADODB.Recordset
On Error Resume Next
Set d = Sheets(«Tendencia»)
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
uf = d.Range(«A» & Rows.Count).End(xlUp).Row
d.Range(«A22:P1000»).Clear
With ActiveSheet.ChartObjects.Delete: End With ‘Borra todos los graficos de la hoja
mybookindice = Sheets(«Parametros»).Range(«C2»)
cn.Open «Provider=Microsoft.ACE.OLEDB.12.0; » & «data source=» & mybookindice & «;»
‘Country = «USA»
Sql = «SELECT * FROM DB_COVID_19 WHERE Pais = ‘» & Country & «‘»
Set rs = cn.Execute(Sql)
If rs.EOF = True Then
Set rs = Nothing
cn.Close
Set cn = Nothing
Else
d.Cells(22, 1).CopyFromRecordset Data:=rs
col = col + 1
Set rs = Nothing
cn.Close
Set cn = Nothing
End If
d.Range(«A21») = «Id»
d.Range(«B21») = «Fecha»
d.Range(«C21») = «País»
d.Range(«D21») = «Casos»
d.Range(«E21») = «Nuevos Casos»
d.Range(«F21») = «Muertes»
d.Range(«G21») = «Nuevas Muertes»
d.Range(«H21») = «Recuperados»
d.Range(«I21») = «Casos Activos»
d.Range(«J21») = «Casos Criticos»
d.Range(«K21») = «Casos/1M Pob»
‘Ordena por casos en caso que se recuperen desordenados
‘rango de datos a ordenar
pf = 21
uf = d.Range(«A» & Rows.Count).End(xlUp).Row – 1
If d.Range(«A» & uf + 1) <> «Total:» Then uf = d.Range(«A» & Rows.Count).End(xlUp).Row
uc = d.Cells(21, Columns.Count).End(xlToLeft).Address
pc = d.Cells(21, Columns.Count).End(xlToLeft).End(xlToLeft).Address
wc = Mid(uc, InStr(uc, «$») + 1, InStr(2, uc, «$») – 2)
wc1 = Mid(pc, InStr(pc, «$») + 1, InStr(2, pc, «$») – 2)
r2 = wc1 & pf & «:» & wc & uf
r1 = «A» & pf & «:A» & uf
‘sorts the data
ActiveWorkbook.Worksheets(«Tendencia»).Sort.SortFields.Clear
ActiveWorkbook.Worksheets(«Tendencia»).Sort.SortFields.Add Key:=Range(r1) _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets(«Tendencia»).Sort
.SetRange Range(r2)
.header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
d.Range(«A:A»).Delete
uf = d.Range(«A» & Rows.Count).End(xlUp).Row
fil = 23
col = «L»
col1 = «N»
d.Range(col & fil – 1) = «Totales País: » & d.Range(«B» & uf)
d.Range(col & fil – 1 & «:» & col1 & fil – 1).Merge
d.Range(col & fil) = «Total Casos»
d.Range(col1 & fil) = d.Range(«C» & uf) ‘casos
d.Range(col & fil + 1) = «Total Casos Activos»
d.Range(col1 & fil + 1) = d.Range(«H» & uf) ‘casos activos
d.Range(col & fil + 2) = «Total Recuperados»
d.Range(col1 & fil + 2) = d.Range(«G» & uf) ‘recuperados
d.Range(col & fil + 3) = «Total Muertos»
d.Range(col1 & fil + 3) = d.Range(«E» & uf) ‘muertes
d.Range(col & fil + 4) = «Total Casos Criticos»
d.Range(col1 & fil + 4) = d.Range(«I» & uf) ‘casos criticos
d.Range(«C1:O1»).UnMerge
d.Range(«C1») = «HISTORIAL CASOS DE CORONAVIRUS – COVID 19»
With d.Range(«C1»)
.Font.Size = 16
.Font.Color = 16777215 ‘blanco
.Interior.Color = 0
End With
With d.Range(«C1:O1»)
.Merge
.Interior.Color = 0 ‘negro
.Font.Color = 16777215 ‘blanco
.Font.Bold = True
.HorizontalAlignment = xlCenter
End With
With d.Range(«A21:J21»)
.Interior.Color = 0 ‘negro
.Font.Color = 16777215 ‘blanco
.Font.Bold = True
.HorizontalAlignment = xlCenter
End With
d.Range(«K21:P» & uf).Interior.Color = 16777215 ‘blanco
With d.Range(«L22:M22»)
.Interior.Color = 0 ‘negro
.Font.Color = 16777215 ‘blanco
.Font.Bold = True
.HorizontalAlignment = xlCenter
End With
d.Range(«L23:N23,L25:N25,L27:N27»).Interior.Color = 13082801 ‘Morado claro
d.Range(«L23:N27»).Font.Bold = True
uf = d.Range(«A» & Rows.Count).End(xlUp).Row
If uf < 21 Then uf = 21
For x = 23 To uf Step 2
d.Range(«A» & x & «:J» & x).Interior.Color = 5296274 ‘verde claro ‘morado claro 13082801 ‘5296274 verde claro
Next x
d.Range(«C22:I» & uf & «,N23:N27»).NumberFormat = «#,##0»
d.Range(«J22:J» & uf & «,N23:N27»).NumberFormat = «#,##0.00»
d.Range(«A:A»).ColumnWidth = 17
d.Range(«B:B»).ColumnWidth = 10.71
d.Range(«C:C»).ColumnWidth = 12.14
d.Range(«D:D»).ColumnWidth = 12
d.Range(«E:E»).ColumnWidth = 14
d.Range(«F:F»).ColumnWidth = 11.86
d.Range(«G:G»).ColumnWidth = 12.43
d.Range(«H:H»).ColumnWidth = 12.57
d.Range(«I:I»).ColumnWidth = 12.43
d.Range(«J:J»).ColumnWidth = 11.43
d.Range(«A21:A» & uf & «,C21:C» & uf & «,H21:H» & uf).Select
Set ran = d.Range(«A2»)
Set myChart = ActiveSheet.ChartObjects.Add(100, 200, 350, 242)
With myChart
.Chart.SetSourceData Source:=Selection
.Chart.ChartType = xlLineMarkers
‘.Chart.SeriesCollection(1).XValues = Range(«A22:A36»)
.Chart.ApplyLayout (3)
.Chart.ChartTitle.Select
.Chart.ChartTitle.Text = «Total de Casos Coronavirus»
.Top = ran.Top
.Left = 1 ‘ ran.Top
.Width = 350
.Height = 242
End With
d.Range(«A21:A» & uf & «,E21:E» & uf & «,G21:G» & uf & «,I21:I» & uf).Select
Set ran = d.Range(«E2»)
Set myChart = ActiveSheet.ChartObjects.Add(100, 200, 350, 200)
With myChart
.Chart.SetSourceData Source:=Selection
.Chart.ChartType = xlLineMarkers
‘.Chart.SeriesCollection(1).XValues = Range(«A22:A36»)
.Chart.ApplyLayout (3)
.Chart.ChartTitle.Select
.Chart.ChartTitle.Text = «Recuperados, Criticos, Muertos»
.Top = ran.Top
.Left = 351 ‘ ran.Top
.Width = 350
.Height = 242
End With
d.Range(«B21:C21,B» & uf & «:C» & uf).Select
Set ran = d.Range(«J2»)
ActiveSheet.Shapes.AddChart2(494, xlRegionMap, 702, ran.Top, 350, 242).Select
ActiveSheet.ChartObjects(«Gráfico 29742»).Activate
ActiveChart.ChartTitle.Select
ActiveChart.SetElement (msoElementChartTitleNone)
ActiveChart.SetElement (201)
d.Range(«A1»).Select
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
Código que se ingresa en Modulo «MENU» Asociado Icono del Menu Ribbon
Sub macro19(control As IRibbonControl)
‘Menu llena combobox con países
On Error Resume Next
Set bb = Sheets(«Tendencia»)
Sheets(«Tendencia»).Select
Application.DisplayAlerts = False
Application.ScreenUpdating = False
bb.Range(«A2:P1000»).Clear
With ActiveSheet.ChartObjects.Delete: End With ‘Borra todos los graficos de la hoja
Call Llenacombo1
End Sub
Código que se ingresa en la hoja Tendencia
Private Sub ComboBox1_Change()
‘Private Sub ComboBox1_Click()
If ComboBox1.ListIndex = -1 Then Exit Sub
Country = ComboBox1
Call searchcountry
End Sub
Código que se ingresa en el Menu Herramientas
Sub Llenacombo1()
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Application.EnableEvents = False
Dim cn As ADODB.Connection, rs As ADODB.Recordset
On Error Resume Next
Set d = Sheets(«Tendencia»)
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
mybookindice = Sheets(«Parametros»).Range(«C2»)
cn.Open «Provider=Microsoft.ACE.OLEDB.12.0; » & «data source=» & mybookindice & «;»
Application.EnableEvents = False ‘Anula el evento change del combbox al escribir en el
‘Carga combobox
d.ComboBox1.Clear
Sql = «SELECT DISTINCT Pais FROM DB_COVID_19»
Set rs = cn.Execute(Sql)
Do While rs.EOF = False
d.ComboBox1.AddItem rs.fields(0)
rs.MoveNext
Loop
‘Application.EnableEvents = True
d.ComboBox1 = Country
Set rs = Nothing
cn.Close
Set cn = Nothing
Call searchcountry
Application.EnableEvents = True
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
Si te fue de utilidad puedes INVITARME UN CAFÉ y de esta manera ayudar a seguir manteniendo la página, CLICK para descargar en ejemplo en forma gratuita.
If this post was helpful INVITE ME A COFFEE and so help keep up the page, CLICK to download free example.
Donate:
Cuenta Paypal: https://paypal.me/programarexcel
Cuenta Bitcoin: 1KBGGb8fyDzyR3X1Rie6m7VguzaAfngNbd
Cuenta Ether: 0x41Bbd24556914C83a31217eBb3BC49789b66e407
