cotacoes-bcb/getCotacoes.pq at main · MaxSuendre/cotacoes-bcb · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
(dataInicial as date, dataFinal as date, moeda as text, pagina as number) as table =>

// https://olinda.bcb.gov.br/olinda/servico/PTAX/versao/v1/odata/CotacaoMoedaPeriodo(moeda=@moeda,dataInicial=@dataInicial,dataFinalCotacao=@dataFinalCotacao)?@moeda='USD'&@dataInicial='10-01-2024'&@dataFinalCotacao='10-27-2024'&$top=100&$skip=0&$format=json&$select=cotacaoCompra,dataHoraCotacao,tipoBoletim

let
    url = "https://olinda.bcb.gov.br/olinda/servico/PTAX/versao/v1/odata",

    endpoint = "CotacaoMoedaPeriodo(moeda=@moeda,dataInicial=@dataInicial,dataFinalCotacao=@dataFinalCotacao)",

    query = [
        #"@moeda" = "'" & moeda & "'",
        #"@dataInicial" = "'" &  Date.ToText(dataInicial,"MM-dd-yyyy") & "'",
        #"@dataFinalCotacao" = "'" & Date.ToText(dataFinal,"MM-dd-yyyy") & "'",
        #"$top" = Number.ToText(100),
        #"$skip" = Number.ToText((pagina-1)*100),
        #"$format"= "json",
        #"$select" = "cotacaoCompra,dataHoraCotacao,tipoBoletim"
    ],

    request = Web.Contents(
        url,
        [ RelativePath = endpoint, Query = query ]
    ),

    response = Json.Document(request, 65001),

    lista = response[value],

    tabela = Table.FromRecords(lista),

    apenasFechamento = Table.SelectRows(tabela, each ([tipoBoletim] = "Fechamento"))[[dataHoraCotacao], [cotacaoCompra]],

    colunasFormatadas = Table.TransformColumns(
        apenasFechamento,
        {
            {"dataHoraCotacao", each DateTime.Date(DateTime.From(_)), type date},
            {"cotacaoCompra", Number.From, Currency.Type}
        }
    ),

    colunasRenomeadas = Table.RenameColumns(
        colunasFormatadas,
        {
            {"dataHoraCotacao", "DataCotacao"},
            {"cotacaoCompra", "Cotacao"}
        }
    )

in
    colunasRenomeadas