Scrape values from website using VBA

Question

Help needed in order to scrape some data from a website. As a first step i manage to visit the website and import my variables but:

1.i don't know how to press "Convert currencies" button 2.and afterwards to get "Converted Amount" & "Rate" to excel.

any help will be appreciate!!!

Sub Test()

Dim IE As InternetExplorer

Dim Amount As String
Dim Source As String
Dim Target As String
Dim Datestring As String

Amount = 10000
Source = "Euro"
Target = "UK pound sterling"
Datestring = "03-08-2018"

'Open Browser and download data
Set IE = New InternetExplorer

With IE
    .Visible = True
    .Navigate "http://sdw.ecb.europa.eu/curConverter.do?sourceAmount=" & _
            Amount & _
            "&sourceCurrency=" & _
            Source & _
            "&targetCurrency=" & _
            Target & _
            "&inputDate=" & _
            Datestring & _
            "&submitConvert.x=209&submitConvert.y=10"

            submitConvert.Click

    While .Busy Or .readyState < 4: DoEvents: Wend

End With

End Sub

Answer 1

XmlHttpRequest (XHR):

Faster to use XHR where there is no browser opening.

Option Explicit

Public Sub GetRates()
    Dim sResponse As String, i As Long, html As New HTMLDocument, clipboard As Object
    Dim sourceAmount As String, sourceCurrency As String, targetCurrency As String, inputDate As String
    sourceAmount = "10000"
    sourceCurrency = "EUR"
    targetCurrency = "GBP"
    inputDate = "03-08-2018"
    Dim url As String
    url = "http://sdw.ecb.europa.eu//curConverter.do?sourceAmount=" & sourceAmount & "&sourceCurrency=" & sourceCurrency & _
        "&targetCurrency=" & targetCurrency & "&inputDate=" & inputDate & "&submitConvert.x=52&submitConvert.y=8"

    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", url, False
        .send
        sResponse = StrConv(.responseBody, vbUnicode)
    End With

    sResponse = Mid$(sResponse, InStr(1, sResponse, "<!DOCTYPE "))
    With html
       .body.innerHTML = sResponse
        Set clipboard = New MSForms.DataObject
        clipboard.SetText .querySelectorAll("table.tableopenpage").item(1).outerHTML
        clipboard.PutInClipboard
        ActiveSheet.Cells(1, 1).PasteSpecial
    End With
End Sub

---

Less robust but if you just want the

converted amount:

.querySelectorAll("table.tableopenpage").item(1).getElementsbytagname("td")(7).innertext

And the rate:

.querySelectorAll("table.tableopenpage").item(1).getElementsbytagname("td")(10).innertext

---

Output:

---

References:

Microsoft Forms Object Library (or add a userform to your project)
Microsoft HTML Object Library

---

Internet Explorer:

The data is already there due to the query string you are using in the URL. No need for a click.

Just use the correct abbreviations for currencies.

Option Explicit
Public Sub Test()
    Dim IE As InternetExplorer, Amount As String, Source As String, Target As String
    Dim Datestring As String, hTable As HTMLTable

    Amount = 10000
    Source = "EUR"
    Target = "GBP"
    Datestring = "03-08-2018"
    Dim url As String
    url = "http://sdw.ecb.europa.eu/curConverter.do?sourceAmount=" & _
          Amount & _
          "&sourceCurrency=" & _
          Source & _
          "&targetCurrency=" & _
          Target & _
          "&inputDate=" & _
          Datestring & _
          "&submitConvert.x=209&submitConvert.y=10"

    Set IE = New InternetExplorer

    With IE
        .Visible = True
        .navigate url

        While .Busy Or .readyState < 4: DoEvents: Wend
        Dim clipboard As Object
        Set clipboard = New MSForms.DataObject
        clipboard.SetText .document.getElementsByClassName("tableopenpage")(1).outerHTML
        clipboard.PutInClipboard
        ActiveSheet.Cells(1, 1).PasteSpecial
    End With
End Sub

---

If interested in how to click though:

1) Use the correct 3 letter abbreviations for the currencies.

2) You can click the submit button with:

.document.querySelector("input[name=submitConvert]").Click

It uses a CSS selector of

input[name=submitConvert]

This says

element with input tag having an attribute name whose value is submitconvert.

3) You then need a

While .Busy Or .readyState < 4: DoEvents: Wend

to allow the page to refresh.

4) You can then grab the results table with:

.document.querySelectorAll("table.tableopenpage").item(1)

This collects all elements with a tag table and class tableopenpage. You want the second of these, which is 1 on a 0-based index system.

---

References required:

Microsoft Internet Controls
Microsoft HTML Object Library
Microsoft Forms Object Library

---

Other:

I find it simpler to grab the table in one go but you could target the rate, for example, more specifically with a CSS selector of:

a[target*=quickview]

Be aware that Excel may swop Date from dd/mm/yyyy to mm/dd/yyyy on output and so you will need to correct this, or at least be aware of it.