Очистка данных с сайта с помощью VBA
Я пытаюсь собрать данные с веб-сайта: http://uk.investing.com/rates-bonds/financial-futures через vba, например, в режиме реального времени, например, немецкий 5 лет Бобл, 30-летняя облигация США, я пробовал Excel веб-запрос, но он только царапает весь сайт, но я хотел бы только оценить скорость, есть ли способ сделать это?
5 ответов
Есть несколько способов сделать это. Это ответ, который я пишу, надеясь, что все основы автоматизации Internet Explorer будут найдены при поиске по ключевым словам "очистка данных с веб-сайта", но помните, что ничто не стоит вашего собственного исследования (если вы не хотите придерживаться предварительно написанные коды, которые вы не можете настроить).
Обратите внимание, что это один из способов, который я не предпочитаю с точки зрения производительности (так как это зависит от скорости браузера), но это хорошо, чтобы понять причину автоматизации Интернета.
1) Если мне нужно просматривать веб-страницы, мне нужен браузер! Поэтому я создаю браузер Internet Explorer:
Dim appIE As Object
Set appIE = CreateObject("internetexplorer.application")
2) Я прошу браузер просмотреть целевую веб-страницу. Используя свойство ".Visible", я решаю, хочу ли я, чтобы браузер выполнял свою работу или нет. При сборке кода приятно иметь Visible = True, но когда код работает для очистки данных, приятно не видеть его каждый раз, поэтому Visible = False,
With appIE
.Navigate "http://uk.investing.com/rates-bonds/financial-futures"
.Visible = True
End With
3) Веб-странице потребуется некоторое время для загрузки. Итак, я буду ждать пока он занят...
Do While appIE.Busy
DoEvents
Loop
4) Ну, теперь страница загружена. Допустим, я хочу отсканировать изменение T-облигации US30Y: я просто нажму F12 в Internet Explorer, чтобы увидеть код веб-страницы, и, следовательно, используя указатель (в красном круге), я нажму на элемент, который Я хочу очистить, чтобы увидеть, как я могу достичь своей цели.
5) То, что я должен сделать, просто. Прежде всего, я получу по идентификатору собственности tr элемент, содержащий значение:
Set allRowOfData = appIE.document.getElementById("pair_8907")
Здесь я получу коллекцию td элементы (в частности, tr строка данных, а td это его клетки. Мы ищем 8-го, поэтому я напишу:
Dim myValue As String: myValue = allRowOfData.Cells(7).innerHTML
Почему я написал 7 вместо 8? Поскольку наборы ячеек начинаются с 0, индекс 8-го элемента равен 7 (8-1). Кратко анализируя эту строку кода:
.Cells()заставляет меня получить доступ кtdэлементы;innerHTMLэто свойство ячейки, содержащей значение, которое мы ищем.
Как только мы получим нашу ценность, которая теперь сохраняется в myValue переменная, мы можем просто закрыть браузер IE и освободить память, установив для нее значение Nothing:
appIE.Quit
Set appIE = Nothing
Ну, теперь у вас есть ваша ценность, и вы можете делать с ней все, что захотите: поместите ее в ячейку (Range("A1").Value = myValue) или в метку формы (Me.label1.Text = myValue).
Я просто хотел бы отметить, что Stackru работает не так: здесь вы публикуете вопросы о конкретных проблемах кодирования, но сначала вам нужно выполнить собственный поиск. Причина, по которой я отвечаю на вопрос, который не требует слишком больших усилий по исследованию, заключается в том, что я вижу, что его задавали несколько раз, и, возвращаясь к тому времени, когда я узнал, как это сделать, я помню, что мне бы хотелось иметь немного лучше поддержка, чтобы начать с. Поэтому я надеюсь, что этот ответ, который является просто "вводным материалом для изучения" и вовсе не лучшим / наиболее полным решением, может помочь следующему пользователю, имеющему ту же проблему. Потому что я научился программировать благодаря этому сообществу, и мне нравится думать, что вы и другие новички могли бы использовать мой вклад, чтобы открыть прекрасный мир программирования.
Приятной практики;)
Были упомянуты и другие методы, поэтому позвольте нам признать, что на момент написания статьи мы находимся в 21 веке. Давайте запаркуем браузер локальной шины и откроем запрос XMLHTTP GET (XHR GET для краткости).
XHR - это API в форме объекта, методы которого передают данные между веб-браузером и веб-сервером. Объект предоставляется JavaScript-средой браузера.
Это быстрый метод для получения данных, который не требует открытия браузера. Ответ сервера может быть считан в HTMLDocument, и оттуда процесс получения таблицы продолжается.
В приведенном ниже коде таблица захватывается своим идентификатором cr1,
В вспомогательной суб, WriteTableмы зациклили столбцы (td теги), а затем строки таблицы (tr тэги) и, наконец, проследите длину каждой строки таблицы, ячейка таблицы ячейка таблицы. Поскольку нам нужны только данные из столбцов 1 и 8, Select Case Выписка используется, чтобы указать, что выписано на листе.
Пример просмотра веб-страницы:
Пример вывода кода:
VBA:
Option Explicit
Public Sub GetRates()
Dim sResponse As String, html As New HTMLDocument '<== Tools > References > HTML Object Library
Dim hTable As HTMLTable
Application.ScreenUpdating = False
With CreateObject("MSXML2.XMLHTTP")
.Open "GET", "https://uk.investing.com/rates-bonds/financial-futures", False
.setRequestHeader "If-Modified-Since", "Sat, 1 Jan 2000 00:00:00 GMT"
.send
sResponse = StrConv(.responseBody, vbUnicode)
End With
sResponse = Mid$(sResponse, InStr(1, sResponse, "<!DOCTYPE "))
With html
.body.innerHTML = sResponse
Set hTable = .getElementById("cr1")
WriteTable hTable, 1, ThisWorkbook.Worksheets("Sheet1")
End With
Application.ScreenUpdating = True
End Sub
Public Sub WriteTable(ByVal hTable As HTMLTable, Optional ByVal startRow As Long = 1, Optional ByVal ws As Worksheet)
Dim tSection As Object, tRow As Object, tCell As Object, tr As Object, td As Object, r As Long, C As Long, tBody As Object
r = startRow: If ws Is Nothing Then Set ws = ActiveSheet
With ws
Dim headers As Object, header As Object, columnCounter As Long
Set headers = hTable.getElementsByTagName("th")
For Each header In headers
columnCounter = columnCounter + 1
Select Case columnCounter
Case 2
.Cells(startRow, 1) = header.innerText
Case 8
.Cells(startRow, 2) = header.innerText
End Select
Next header
startRow = startRow + 1
Set tBody = hTable.getElementsByTagName("tbody")
For Each tSection In tBody
Set tRow = tSection.getElementsByTagName("tr")
For Each tr In tRow
r = r + 1
Set tCell = tr.getElementsByTagName("td")
C = 1
For Each td In tCell
Select Case C
Case 2
.Cells(r, 1).Value = td.innerText
Case 8
.Cells(r, 2).Value = td.innerText
End Select
C = C + 1
Next td
Next tr
Next tSection
End With
End Sub
Вы можете использовать объект winhttprequest вместо Internet Explorer, так как полезно загружать данные, исключая изображения и рекламу, вместо загрузки полной веб-страницы, включая рекламу и картинки, которые делают объект Internet Explorer тяжелым по сравнению с объектом winhttpRequest.
Этот вопрос задал задолго до этого. Но я думал, что следующая информация будет полезна для новичков. На самом деле вы можете легко получить значения из имени класса, как это.
Sub ExtractLastValue()
Set objIE = CreateObject("InternetExplorer.Application")
objIE.Top = 0
objIE.Left = 0
objIE.Width = 800
objIE.Height = 600
objIE.Visible = True
objIE.Navigate ("https://uk.investing.com/rates-bonds/financial-futures/")
Do
DoEvents
Loop Until objIE.readystate = 4
MsgBox objIE.document.getElementsByClassName("pid-8907-last")(0).innerText
End Sub
И если вы новичок в веб-очистке, пожалуйста, прочитайте этот блог.
А также есть различные методы для извлечения данных из веб-страниц. В этой статье некоторые из них объясняются примерами.
Я изменил кое-что, что выскакивало для меня, и в итоге получилось то, что отлично работало для извлечения данных по мере необходимости:
Sub get_data_web()
Dim appIE As Object
Set appIE = CreateObject("internetexplorer.application")
With appIE
.navigate "https://finance.yahoo.com/quote/NQ%3DF/futures?p=NQ%3DF"
.Visible = True
End With
Do While appIE.Busy
DoEvents
Loop
Set allRowofData = appIE.document.getElementsByClassName("Ta(end) BdT Bdc($c-fuji-grey-c) H(36px)")
Dim i As Long
Dim myValue As String
Count = 1
For Each itm In allRowofData
For i = 0 To 4
myValue = itm.Cells(i).innerText
ActiveSheet.Cells(Count, i + 1).Value = myValue
Next
Count = Count + 1
Next
appIE.Quit
Set appIE = Nothing
End Sub