Como Hacer WEB SCRAPING RASPADO WEB Obtener COTIZACION CEDEARS con VBA EXCEL #587

MACROS DE EXCEL VBA PARA OBTENER DATOS DE LA WEB

Este post muestra como realizar un Web Sraping o Raspado Web para obtener Datos de Internet, utilizado Macros de Excel VBA. La macro a través de código VBA se conecta a la WEB de INVERTIR ONLINE y obtiene las cotizaciones de títulos denominados CEDEARS, que son certificados de Depósitos Argentinos de acciones que cotizan en Estados Unidos, esto se puede realizar con cualquier WEB, yo elegí esta por la particularidad que tiene al extraer los datos, ya que con una sola consulta se extrae todo el listados de cotizaciones.

En los tres ejemplos relacionados es decir el 586, 587 y 588 se muestra como extraer cotizaciones del Ibex 35 (ejemplo 586) y del Nasdaq (ejemplo 588), en cada uno verán las diversas dificultades que expone extraer las cotizaciones de webs distintas.

Requieres aprender a manejar Excel, 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.

  

El ejemplo consiste en obtener las cotizaciones de títulos denominados CEDEARS, que son Certificados de Depósitos Argentinos de Acciones que cotizan en Estados Unidos, esto es extensible a cualquier extracción de cotizaciones de acciones e incluso de cualquier tipo de datos.

Se deben activar dos referencias en el editor de VBA:

1) Microsoft XML,v6.0

2) Microsotf HTML Object Library

Lo que se consigue es la codificación HTML de la web, donde están los datos, luego se debe depurar o limpiar para obtener los datos requeridos, no es necesario conocer el código de programación «HTML», pero sería optimo un mínimo conocimiento sobre el mismo.

En este ejemplo se hará dos raspado web, obteniendo dos datos puntuales, como es la cotización del Dolar «Contado con Liquidación», denominado así en ese país, que es similar a extraer cualquier cotización de una divisa.

Luego se extraerá la cotización de todo el padrón de CEDEARS que existen listados en la página WEB, invertironline.com luego de extraídas todas las cotizaciones, se dividirá la cotización que está en Pesos Argentinos por la cotización del Contado con Liquidación, para obtener expresado en Dólares Estadounidenses la cotización de la acción.

Una vez extraídas las cotizaciones tanto de las acciones como del valor del dólar «Contado con Liquidación» o «Contado con Liqui» como se lo denomina en la jerga, se procede a buscar la cotización de cada acción que se posee en cartera en el listado extraído de la WEB, colocando dicha cotización en la celda correspondiente para tener el valor actualizado de nuestra acción en cartera.

El ejemplo incluye también un resumen de las acciones en cartera, ya que según este ejemplo se han comprado diversas acciones en distintos días, la macro procede a agrupar acciones iguales y hacer un resumen agregando un grafico que muestra como está compuesta la cartera de inversión.

El ejemplo contiene varias macros que se han mostrado en muchos ejemplos en nuestro canal y nuestra web, existen macros para búsqueda, macros para insertar un gráfico, etc. todo eso se utiliza en este ejemplo, en este post nos centraremos en explicar como se extraen los datos de la WEB el resto de código del ejemplo completo es código VBA que ya sabemos, por eso si se quiere saber como se realiza, sugiero ver el vídeo asociado.

En este post, para que no sea demasiado extenso, no se explican todas las macros que surgen de código VBA que ya se conoce, solo se explica como extraer los datos de la web o como se hace web scraping – raspado web, en el caso que se necesite una explicación mas detallada, se deberá realizar la pregunta en los comentarios del vídeo o en este post que con gusto será respondida.

Explicación del código para Obtener Cotizaciones de Acciones de Invertir OnLine con Macro VBA

El código para obtener la cotización del  dolar «contado con liquidación» 

Se declaran variables que se usarán en la extracción de datos

Se utilizará el Objeto «MSXML2.XMLHTTP60» para obtener los datos de la Web, con dicho objeto se hará la petición al servidor de datos para obtener la información que requerimos:

Dim xmlp As New MSXML2.XMLHTTP60, htmld As New MSHTML.HTMLDocument, htmlE As MSHTML.IHTMLElement, htmlEs As MSHTML.IHTMLElementCollection, url As String

Se crea un objeto con la hoja donde colocaremos las cotizaciones:

Set a = Sheets(«Cedears»)

Se establece la url o web o pagina de web o de internet de donde se requieren obtener los datos:

url = «https://www.dolarhoy.com/cotizaciondolarcontadoconliqui»

Con la siguiente codificación se establece el método de envío de datos que puede ser «GET» o «POST, básicamente la diferencia visual que podemos observar si se usa uno u otro método, es con el metodo GET la URL va variando, en cambio con el método post por mas que nosotros selecciones cietos item en la pagina y se cambie lo que estamos observando en la web en ese momento, la URL no varia.

En este caso se usa GET, se agrega la variable que contiene la url

xmlp.Open «GET», url, False

Con estos códigos se envía la petición al servidor, más precisamente se hacen con «Send», pero se necesitan todos estos códigos:

xmlp.setRequestHeader «Content-Type», «text/xml»
xmlp.send

El siguiente código es para determinar si se pudo conectar o no, en caso que exista problemas en la comunicación, la macro finaliza sin realizar nada y sale un mensaje de dicha situación, lo que se verifica es que el status sea igual a 200, ya que es el código que establece que la conexión es exitosa.

If xmlp.Status <> 200 Then MsgBox («Error en la Conexión se canceló la ejecución de la macro»), vbCritical, «AVISO»: Exit Sub

Si la conexión fue exitosa, ya se han obtenido los datos y agregamos la respuesta en una variable así:

htmld.body.innerHTML = xmlp.responseText

Si se quiere ver los datos en el debug «Ventana Inmediato de VBA» se debe agregar este código, se copia todo la codificación HTML obtenida en la pantalla inmediato, caso contrario se comenta o borra la línea de código.

Debug.Print htmld.body.innerHTML

Se crea luego un objeto con el objeto que contiene los datos que necesito en este caso se es ClasName, pero se puede recorrer los datos por Tag, Name, ID, etc. lo cual lo veremos en otros ejemplos más adelante, en este caso usaremos ClassName «pull-right».

Para obtener el nombre del ClassName se debe fijar en la codificación html de la página web de donde extrajimos los datos, para ello se debe usar Chorme o Mozilla Firefox, botón derecho del mouse y luego en inspeccionar, ver el vídeo asociado para entender más fácil.

así:

Set htmlEs = htmld.getElementsByClassName(«pull-right»)

Luego se recorren con un bucle for todos los elementos de la colección de datos que están contenidos en el objeto que creamos anteriormente:

fila = 1
For Each htmlE In htmlEs

Podemos obtener la propiedad ID o Name, pero en este caso no se hará sólo buscamos la propiedad innerText que es la que contiene el dato con la cotización del dólar «contado con liqui» (ver vídeo asociado).

‘a.Cells(2, «W») = htmlE.ID
‘a.Cells(2, «x») = htmlE.Name

‘Con estos códigos se agregan en la fila 1 columna Y y Z las cotizaciones para la compra y para la venta del «contado con liquidación».

If fila = 1 Then a.Cells(1, «Y») = «Compra» Else a.Cells(2, «Y») = «Venta»
If fila = 1 Then a.Cells(1, «z») = htmlE.innerText Else a.Cells(2, «z») = htmlE.innerText
fila = fila + 1
Next htmlE

El código para obtener la cotización de los «CEDEARS»

Con la codificación anterior obtuvimos dos datos puntuales que es la cotización para la compra y para la venta, ahora lo que se pretende es sacar todo el padrón donde se Listan los CEDEARS con todos sus datos, en otras palabra todos los datos donde está la tabla con las cotizaciones.

Declaramos variables que necesitamos usar para el web scraping o raspado web

Microsotf HTML Object Library
Dim xmlp As New MSXML2.XMLHTTP60, htmld As New MSHTML.HTMLDocument, htmlE As MSHTML.IHTMLElement, htmlEs As MSHTML.IHTMLElementCollection, url As String

Creaos un objeto que es la hoja donde se colocarán las cotizaciones de los CEDEARS obtenidas de la web invertironline.com, así:

Set a = Sheets(«CotizActualCedears»)

Establecemos la url a donde se encuentran las cotizaciones requeridas:
url = «https://www.invertironline.com/mercado/cotizaciones/argentina/acciones/cedears»

En este caso usamos el método «POST» usar este método y no «GET» no es por capricho, sino que la WEB esta diseñada así:

xmlp.Open «POST», url, False
xmlp.setRequestHeader «Content-type», «application/x-www-form-urlencoded»
xmlp.setRequestHeader «X-Requested-With», «XMLHttpRequest»

Cuando se envía la petición al servidor con «Send» al usar el método post se debe enviar una cadena con todos los párametros solicitados, ver el vídeo relacionado para una explicación más gráfiaca.

xmlp.send «pais=Argentina&instrumento=Acciones&panel=CEDEARs&actualizar=true»

Aca se verifica si la conexión fue exitosa en caso contrario detiene la macro.

If xmlp.Status <> 200 Then MsgBox («Error en la Conexión se canceló la ejecución de la macro»), vbCritical, «AVISO»: Exit Sub

Se carga en una variable la respuesta obtenida del servidor al que se ralizaó la petición.

htmld.body.innerHTML = xmlp.responseText

Se carga en la pantalla inmediato es opcional, después se puede comentar, pero cuando se está programando es útil para ver que extrajo la consulta enviada.

Debug.Print htmld.body.innerHTML

Se crea un objeto con todos los elemoentos cuyo «TagName» sea «td»

Set htmlEs = htmld.getElementById(«cotizaciones»).getElementsByTagName(«td»)

Como lo obtenido es una tabla con las cotizaciones, se debe recorrer todos los td, que serían las celdas de la tabla e ir pegando cada dato en una celda distinta, mira el vídeo explicativo.

Para crear en Excel la tabla con los datos contenidos en el html extraído se usan los siguientes código:

fila = 1
col = 1
For Each htmlE In htmlEs
a.Cells(fila, col) = htmlE.innerText
col = col + 1
If col = 15 Then fila = fila + 1: col = 1 ‘Hay 8 columnas que rellenar, rellenadas vuelve a la columna 1 y fila siguiente para el siguiente registro
Next htmlE

Luego de extraídos los datos, es decir a esta altura de la macro ya se han extraído y se tiene en las celdas de Excel todas las cotizaciones y demás datos de cada CEDEAR que se extrajo de la web incertironline.com, lo que sigue es buscar cada una de nuestras acciones en la hoja con las cotizaciones extraídas y pegar el dato en la celda correspondiente para actualizar automáticamente las cotizaciones, esto se puede automatizar para que la macro se ejecute cada minuto o cada 5 minutos, etc., este ejemplo sol extrae y apretando un botón de actualizan las cotizaciones (Ver el Vídeo Asociado).

Código VBA para WEB Scraping – Raspado Web

Código que se inserta en un formulario

Private Sub UserForm_Activate()
Tpo = «00:00:02»
Label1.Font = arial
Label1.Font.Size = 20
Select Case llamaform
Case Is = 0
Label1.Caption = «CCL Actualizado con Éxito»
Case Is = 1
Label1.Caption = «Cotizaciones Actualizadas con Éxito»
Case Is = 2
Label1.Caption = «Resumen Actualizado con Éxito»
End Select
Application.Wait Now + TimeValue(Tpo)
Unload UserForm1
End Sub

Código que se inserta en un módulo

Public llamaform, llamares
Sub Resumen()
On Error Resume Next
Dim Cedears As New Collection, celda As Object, Cedear
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set a = Sheets(«Cedears»)
Set b = Sheets(«Cartera»)
ufa = a.Range(«A» & Rows.Count).End(xlUp).Row
ufb = b.Range(«A» & Rows.Count).End(xlUp).Row
With ActiveSheet.ChartObjects.Delete: End With ‘Borra todos los graficos de la hoja
llamares = 1
Call CotiCCL ‘Antes que nada actualiza contado con liqui

Application.ScreenUpdating = False
Application.DisplayAlerts = False

b.Range(«A3:N» & ufa).Clear
r1 = «B3» & «:B» & ufa
For Each celda In a.Range(r1)
Cedears.Add celda.Value, CStr(celda.Value)
Next celda

fila = 3
j = 1
For Each Cedear In Cedears
Dim TotcedeCant As Integer, TotAccCant As Variant, TotcedePeso As Currency, CotiActual As Currency

For x = 3 To ufa
If a.Cells(x, «B») = Cedear Then
TotcedeCant = Application.WorksheetFunction.SumIf(a.Range(«B3» & «:B» & ufa), «=» & Cedear, a.Range(«I3» & «:I» & ufa))
TotAccCant = Application.WorksheetFunction.SumIf(a.Range(«B3» & «:B» & ufa), «=» & Cedear, a.Range(«J3» & «:J» & ufa))
TotcedePeso = Application.WorksheetFunction.SumIf(a.Range(«B3» & «:B» & ufa), «=» & Cedear, a.Range(«N3» & «:N» & ufa))
CotiPromCed = Round((TotcedePeso / TotcedeCant), 2)
CotiActual = a.Cells(x, «Q»)
CCL = a.Cells(x, «T»)
GanPer = (TotcedeCant * CotiActual) – (TotcedeCant * CotiPromCed)
b.Cells(fila, «A») = j
b.Cells(fila, «B») = a.Cells(x, «B»)
b.Cells(fila, «C») = a.Cells(x, «C»)
b.Cells(fila, «D») = TotcedeCant
b.Cells(fila, «E») = TotAccCant
‘b.Cells(fila, «F») = 1
b.Cells(fila, «G») = CotiPromCed
b.Cells(fila, «H») = CotiActual
b.Cells(fila, «I») = GanPer
If GanPer < 0 Then
b.Range(«I» & fila & «:J» & fila).Interior.Color = 255
Else
b.Range(«I» & fila & «:J» & fila).Interior.Color = 65280
End If

b.Cells(fila, «J») = (CotiActual – CotiPromCed) / CotiPromCed
b.Cells(fila, «K») = TotcedeCant * CotiPromCed
b.Cells(fila, «L») = TotcedeCant * CotiActual
b.Cells(fila, «M») = CCL
b.Cells(fila, «N») = b.Cells(fila, «L») / CCL
fila = fila + 1
Exit For
End If

Next x

j = j + 1
Next Cedear

ufb = b.Range(«A» & Rows.Count).End(xlUp).Row
b.Range(«E:I,K:N»).NumberFormat = «#,##0.00»
b.Range(«F:F,J:J»).NumberFormat = «0.00%»

b.Range(«D1»).Formula = «=sum(D3:D» & ufb & «)»
b.Range(«E1»).Formula = «=sum(E3:E» & ufb & «)»
b.Range(«I1»).Formula = «=sum(I3:I» & ufb & «)»
b.Range(«K1»).Formula = «=sum(K3:K» & ufb & «)»
b.Range(«L1»).Formula = «=sum(L3:L» & ufb & «)»
b.Range(«N1»).Formula = «=sum(N3:N» & ufb & «)»

mycolor = b.Range(«C3»).Interior.Color
For x = 4 To ufb Step 2
b.Range(«A» & x & «:H» & x & «,K» & x & «:N» & x).Interior.Color = 15261367 ‘celeste ‘13082801 morado claro 13082801 ‘5296274 verde claro
Next x

For x = 3 To ufb
b.Cells(x, «F») = b.Cells(x, «L») / b.Cells(1, «L»)
Next x

mytop = b.Range(«A» & ufb + 2).Top
myleft = b.Range(«D» & ufb + 2).Left

b.Range(«C3: C» & ufb & «,F3:F» & ufb).Select

Set myChart = ActiveSheet.ChartObjects.Add(100, 200, 400, 400)
With myChart
.Chart.SetSourceData Source:=Selection
.Chart.ChartType = xlPie
‘.Chart.SeriesCollection(1).XValues = Range(«C3:C» & ufb)
.Chart.ApplyLayout (4)
‘.Chart.ChartTitle.Select
‘.Chart.ChartTitle.Text = «Total de Ventas»
.Top = mytop
.Left = myleft
End With

With b.Range(«A1:N1»)
.HorizontalAlignment = xlCenter
.Font.Bold = True
.Font.Size = 14
.Interior.Color = 14281213
End With

b.Range(«I1:J1»).Merge
b.Range(«I1:J1»).HorizontalAlignment = xlCenter

If b.Range(«I1») < 0 Then
b.Cells(1, «I»).Interior.Color = 255
Else
b.Cells(1, «I»).Interior.Color = 65280
End If

b.Range(«A:N»).EntireColumn.AutoFit

‘MsgBox («El resumen se realizó con éxito), vbInformation, «AVISO»
b.Range(«C2»).Select

llamaform = 2
UserForm1.Show
llamares = 0
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

Sub CotiCCL()
On Error Resume Next
Application.ScreenUpdating = False
‘Con referencias activadas las refeencias son: 1) Microsoft XML,v6.0 y 2) Microsotf HTML Object Library
Dim xmlp As New MSXML2.XMLHTTP60, htmld As New MSHTML.HTMLDocument, htmlE As MSHTML.IHTMLElement, htmlEs As MSHTML.IHTMLElementCollection, url As String

‘Sin referencias activadas ***** Estabas variables se deben declarar y crear objetos xmlp y htmld para que funciones sin referencias
‘Dim xmlp As Object, htmld As Object, htmlE As Object, htmlEs As Object, url As String
‘Set xmlp = CreateObject(«MSXML2.XMLHTTP.6.0»)
‘Set htmld = CreateObject(«htmlfile»)
‘******************************* Estas variables y objetos se deben declarar para que funcione sin referencia*****************

Set a = Sheets(«Cedears»)
‘uf = a.Range(«A» & Rows.Count).End(xlUp).Row
‘If uf = 1 Then uf = 2
‘a.Range(«A2:H» & uf) = Clear
‘fila = 2
url = «https://www.dolarhoy.com/cotizaciondolarcontadoconliqui»

xmlp.Open «GET», url, False
xmlp.setRequestHeader «Content-Type», «text/xml»
xmlp.send

If xmlp.Status <> 200 Then MsgBox («Error en la Conexión se canceló la ejecución de la macro»), vbCritical, «AVISO»: Exit Sub

htmld.body.innerHTML = xmlp.responseText
‘Debug.Print htmld.body.innerHTML

Set htmlEs = htmld.getElementsByClassName(«pull-right»)

fila = 1
For Each htmlE In htmlEs
‘a.Cells(2, «W») = htmlE.ID
‘a.Cells(2, «x») = htmlE.Name
If fila = 1 Then a.Cells(1, «Y») = «Compra» Else a.Cells(2, «Y») = «Venta»
If fila = 1 Then a.Cells(1, «z») = htmlE.innerText Else a.Cells(2, «z») = htmlE.innerText
fila = fila + 1
Next htmlE

uf = a.Range(«S» & Rows.Count).End(xlUp).Row
a.Range(«T3:T» & uf) = CDec(a.Range(«Z2»))

If llamares = 1 Then GoTo salta: ‘Si lo llama de hoja resumen no sale mensaje que si sale si se ejecuta hoja cedears
llamaform = 0
UserForm1.Show
salta:
‘MsgBox («Los datos se han extraído con éxito»), vbInformation, «AVISO»
Application.ScreenUpdating = True
End Sub

Sub CotizCedears()
On Error Resume Next
Application.ScreenUpdating = False
‘Con referencias activadas las refeencias son: 1) Microsoft XML,v6.0 y 2) Microsotf HTML Object Library
Dim xmlp As New MSXML2.XMLHTTP60, htmld As New MSHTML.HTMLDocument, htmlE As MSHTML.IHTMLElement, htmlEs As MSHTML.IHTMLElementCollection, url As String

‘Sin referencias activadas ***** Estabas variables se deben declarar y crear objetos xmlp y htmld para que funciones sin referencias
‘Dim xmlp As Object, htmld As Object, htmlE As Object, htmlEs As Object, url As String
‘Set xmlp = CreateObject(«MSXML2.XMLHTTP.6.0»)
‘Set htmld = CreateObject(«htmlfile»)
‘******************************* Estas variables y objetos se deben declarar para que funcione sin referencia*****************

Set a = Sheets(«CotizActualCedears»)
Set b = Sheets(«Cedears»)

a.Cells.Clear
url = «https://www.invertironline.com/mercado/cotizaciones/argentina/acciones/cedears»

xmlp.Open «POST», url, False
‘xmlp.setRequestHeader «Content-Type», «text/xml» ‘Cabecera con Metodo GET
xmlp.setRequestHeader «Content-type», «application/x-www-form-urlencoded»
xmlp.setRequestHeader «X-Requested-With», «XMLHttpRequest»

xmlp.send «pais=Argentina&instrumento=Acciones&panel=CEDEARs&actualizar=true»

If xmlp.Status <> 200 Then MsgBox («Error en la Conexión se canceló la ejecución de la macro»), vbCritical, «AVISO»: Exit Sub

htmld.body.innerHTML = xmlp.responseText
Debug.Print htmld.body.innerHTML

Set htmlEs = htmld.getElementById(«cotizaciones»).getElementsByTagName(«td»)
fila = 1
col = 1
For Each htmlE In htmlEs
a.Cells(fila, col) = htmlE.innerText
col = col + 1
If col = 15 Then fila = fila + 1: col = 1 ‘Hay 8 columnas que rellenar, rellenadas vuelve a la columna 1 y fila siguiente para el siguiente registro
Next htmlE

ufa = a.Range(«A» & Rows.Count).End(xlUp).Row
a.Range(«B1»).EntireColumn.Insert
For j = 2 To ufa
mycede = a.Cells(j, «A»)
pos = InStr(mycede, » «)
mycede = Mid(mycede, 1, pos – 1)
a.Cells(j, «B») = mycede
Next j

ufb = b.Range(«A» & Rows.Count).End(xlUp).Row
For x = 3 To ufb
pf = 2
r2 = «B» & pf & «:B» & ufa
busco = b.Cells(x, «B»)
Set codigo = a.Range(r2).Find(busco, LookIn:=xlValues, LookAt:=xlPart)

If Not codigo Is Nothing Then
mifila = codigo.Row
b.Cells(x, «Q») = CDec(a.Cells(codigo.Row, 3))
End If
Next x

b.Range(«P3: P» & ufa) = Date

llamaform = 1
UserForm1.Show

Application.ScreenUpdating = True

End Sub

Descarga el Libro Scraping Web – Descargar Cotizaciones de CEDEAR

Descarga desde acá el archivo usado como ejemplo en este post y en el vídeo explicativo, el mismo es totalmente gratuito y libre su uso, solicito aportar para sostener esta web, si está dentro de tus posibilidades, desde ya muchas gracias.

Donate:
Cuenta Paypal: https://paypal.me/programarexcel
Cuenta Bitcoin: 1KBGGb8fyDzyR3X1Rie6m7VguzaAfngNbd
Cuenta Ether: 0x41Bbd24556914C83a31217eBb3BC49789b66e407