提交或绕过Web查询的表单 [英] Submit or bypass form for a Web Query

问题描述

我正在尝试从

输出:

结果:

您可以看到1美元= 3,7048巴西雷亚尔

---

①使用JSON对象:

发出请求的示例字符串:

 "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" 

我在字符串中包括开始日期，结束日期和货币，并将响应格式指定为JSON.我选择的日期与上图中显示的网站视图相匹配.

JSON响应如下:

我将响应读取到一个字符串变量中，然后使用 JsonConverter.ParseJson(strJSON)转换为存储在 json 变量中的JSON对象.快速检查结构:

开头的"{"告诉我 json 是字典.

我还可以看到 json("value")是词典的集合，而我感兴趣的值 3,7048 -请记住该网站上面的图片存储为"cotacaoCompra" .

因此，我可以使用以下脚本访问该值.JSON响应实际上在该日期的5个不同时间给出了费率.这些都已打印出来.我们可以看到 Fechamento (结算)公告比率为3,4848.

---

代码:

 选项显式公共子GetInfo()Dim将strURL作为字符串，将strJSON作为字符串，将项目作为变量，将HTTP作为对象，将json作为对象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"设置http = CreateObject("MSXML2.XMLHTTP")http.Open"GET"，strURL，Falsehttp.sendstrJSON = http.responseText设置json = JsonConverter.ParseJson(strJSON)对于json("value")中的每个项目Debug.Print"rate"&item("cotacaoCompra")&在&item("dataHoraCotacao")下一项结束子 

脚本输出:

---

注释:

需要添加JSONConverter bas和VBE>工具>引用> Microsoft脚本运行时)

---

②使用正则表达式解析responseText以获取费率:

我将使用的正则表达式是

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

这将查找文字字符串"cotacaoCompra":，后跟1个或多个数字，然后是."，然后是多个数字之一.

然后，我必须用直接替换删除字符串"cotacaoCompra":.理想情况下，我只用(?< =""cotacaoCompra":)\ d {1，}.\ d {1，}" ;提取数字.基本上就是说，但不包括"cotacaoCompra":.但这似乎不受支持.

请记住，使用正则表达式获取汇率的脚本:

代码:

 公共子GetInfo2()Dim将strURL作为字符串，将strJSON作为字符串，将项目作为变量，将HTTP作为对象，将json作为对象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"设置http = CreateObject("MSXML2.XMLHTTP")http.Open"GET"，strURL，Falsehttp.sendstrJSON = http.responseText暗淡匹配作为对象使用CreateObject("VBScript.RegExp").Global =真.MultiLine =真.IgnoreCase =假.Pattern =""cotacaoCompra":\ d {1，}.\ d {1，}"'我真正想要的模式，(?< =""cotacaoCompra":)\ d {1，}.\ d {1，}，似乎不受支持如果不是.test(strJSON)，则退出Sub设置匹配= .Execute(strJSON)暗淡匹配为对象对于每场比赛Debug.Print Replace(match，""cotacaoCompra":"，vbNullString)下一个结束于结束子 

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">

解决方案

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:

Output:

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:

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:

The begining "{" tells me that json is a 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:

---

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.

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

这篇关于提交或绕过Web查询的表单的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！