CODEWITHSUNDEEP
htmlexcelvbaweb-scraping
Igor Medici
Igor Medici

Reputation: 11

Submit or bypass form for a Web Query

I'm trying to get dollar exchange rate from http://www4.bcb.gov.br/pec/taxas/port/ptaxnpesq.asp?id=txcotacao into a Excel spreadsheet.

I tried to paste as refreshable web query, however, the page opens one step earlier with a form, which has default inputs (that work for me) and then the query copies stuff from this page.

I tried to write a code to submit the form. I tried the .submit, .Click, .FireEvent and many other things I found on internet.

I tried to refer to the button by its name, class, tag, ... 

<input title="Pesquisar" class="botao" onclick="limparVazio()" type="submit" value="Pesquisar">

I tried to trigger the form directly or bypass it

<form name="consultarBoletimForm" action="/ptax_internet/consultaBoletim.do?method=consultarBoletim" method="post">

Upvotes: 1

Views: 233

Answers (1)

QHarr
QHarr

Reputation: 84475

You can use the bcb.gov.br Open Data Portal.

Send a request for a JSON response with the conversion rates from their Exchange rates – daily bulletins.

With the received response, amongst other methods, you can then:

  1. Use the JSON Converter and set the convert the response into a JSON object and work with that;
  2. Parse the response as a string with a regex to get the values

Looking at the results for today's rate on the site:

Input:

Input

Output:

Rates

Result:

You can see USD 1 = 3,7048 BRL

① Using JSON object:

Example string to make request:

"https://olinda.bcb.gov.br/olinda/service/PTAX/version/v1/odata/ExchangeRatePeriod(moeda=@moeda,dataInicial=@dataInicial,dataFinalCotacao=@dataFinalCotacao)?%40moeda=%27" & TARGET_CURRENCY & "%27&%40dataInicial=%27" & START_DATE & "%27&%40dataFinalCotacao=%27" & END_DATE & "%27&%24format=json"

I include the start date, end date and currency in the string as well as specify the response format as JSON. I have selected the date to match the website view shown in the images above.

The JSON response is as follows:

JSON response

I read the response into a string variable and then use JsonConverter.ParseJson(strJSON) to convert to a JSON object, stored in json variable. A quick inspection of the structure:

JSON structure

The begining "{" tells me that json is a dictionary.

dictionary

I can also see that json("value") is a collection of dictionaries and that the value I am interested in, 3,7048 - remember from the website images above, is stored as "cotacaoCompra".

I can thus use the following script to access that value. The JSON response actually gives rates at 5 different times on that date in question. These are all printed out. The Fechamento (Closing) bulletin rate of 3,7048 we can see matches.

Code:

Option Explicit
Public Sub GetInfo()
    Dim strURL As String, strJSON As String, item As Variant, http As Object, json As Object
    Const TARGET_CURRENCY As String = "USD"
    Const START_DATE As String = "06-13-2018"
    Const END_DATE As String = "06-13-2018"

    strURL = "https://olinda.bcb.gov.br/olinda/service/PTAX/version/v1/odata/ExchangeRatePeriod(moeda=@moeda,dataInicial=@dataInicial,dataFinalCotacao=@dataFinalCotacao)?%40moeda=%27" & TARGET_CURRENCY & "%27&%40dataInicial=%27" & START_DATE & "%27&%40dataFinalCotacao=%27" & END_DATE & "%27&%24format=json"

    Set http = CreateObject("MSXML2.XMLHTTP")
    http.Open "GET", strURL, False
    http.send
    strJSON = http.responseText
    Set json = JsonConverter.ParseJson(strJSON)

    For Each item In json("value")
        Debug.Print "rate " & item("cotacaoCompra") & " at " & item("dataHoraCotacao")
    Next item
End Sub

Script output:

Script output

Notes:

Requires JSONConverter bas added and VBE > Tools > References > Microsoft Scripting RunTime)

② Parsing the responseText with a regex to get the rates:

The regex I will use is 

"cotacaoCompra":\d{1,}.\d{1,}

This looks for the literal string "cotacaoCompra":, followed by 1 or more numbers then a ".", then one of more numbers.

Example matches

I then have to remove the string "cotacaoCompra": with a straight forward replace. Ideally, I would just extract the numbers with "(?<=""cotacaoCompra"":)\d{1,}.\d{1,}"; basically, that says after, but not including "cotacaoCompra":. But that doesn't appear to be supported.

With that in mind the script to get the rates with regex:

Code:

Public Sub GetInfo2()

    Dim strURL As String, strJSON As String, item As Variant, http As Object, json As Object
    Const TARGET_CURRENCY As String = "USD"
    Const START_DATE As String = "06-13-2018"
    Const END_DATE As String = "06-13-2018"

    strURL = "https://olinda.bcb.gov.br/olinda/service/PTAX/version/v1/odata/ExchangeRatePeriod(moeda=@moeda,dataInicial=@dataInicial,dataFinalCotacao=@dataFinalCotacao)?%40moeda=%27" & TARGET_CURRENCY & "%27&%40dataInicial=%27" & START_DATE & "%27&%40dataFinalCotacao=%27" & END_DATE & "%27&%24format=json"

    Set http = CreateObject("MSXML2.XMLHTTP")
    http.Open "GET", strURL, False
    http.send
    strJSON = http.responseText
    Dim Matches As Object
    With CreateObject("VBScript.RegExp")
        .Global = True
        .MultiLine = True
        .IgnoreCase = False
        .Pattern = """cotacaoCompra"":\d{1,}.\d{1,}"  'The pattern I really wanted, "(?<=""cotacaoCompra"":)\d{1,}.\d{1,}", doesn't appear to be supported

        If Not .test(strJSON) Then Exit Sub
        Set Matches = .Execute(strJSON)

        Dim match As Object
        For Each match In Matches
            Debug.Print Replace(match, """cotacaoCompra"":", vbNullString)
        Next
    End With
End Sub

Upvotes: 1

Related Questions