[Medium] BCB Excel Function

Public Function getValor(seriesId As Integer, refDate As Date)
    
    Dim sURL As String
    Dim sEnv As String
    Dim xmlhtp As New MSXML2.XMLHTTP60
    Dim xmlDoc As New DOMDocument60
    sURL = "https://www3.bcb.gov.br/wssgs/services/FachadaWSSGS?method=getValor"
    
    sEnv = "<?xml version=""1.0"" encoding=""utf-8""?>"
    sEnv = sEnv & "<soapenv:Envelope xmlns:soapenv=""http://schemas.xmlsoap.org/soap/envelope/"" xmlns:xsd=""http://www.w3.org/2001/XMLSchema"" xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"">"
    sEnv = sEnv & " <soapenv:Body>"
    sEnv = sEnv & " <getValor xmlns=""https://www3.bcb.gov.br/wssgs/services/FachadaWSSGS"">"
    sEnv = sEnv & " <codigoSerie>" & seriesId & "</codigoSerie>"
    sEnv = sEnv & " <data>" & refDate & "</data>"
    sEnv = sEnv & " </getValor>"
    sEnv = sEnv & " </soapenv:Body>"
    sEnv = sEnv & "</soapenv:Envelope>"
    
    With xmlhtp
    .Open "post", sURL, False
    .setRequestHeader "Host", "https://www3.bcb.gov.br/sgspub/JSP/sgsgeral/FachadaWSSGS.wsdl"
    .setRequestHeader "Content-Type", "text/xml; charset=utf-8"
    .setRequestHeader "soapAction", "https://www3.bcb.gov.br/wssgs/services/FachadaWSSGS/getValor"
    .setRequestHeader "Accept-encoding", "zip"
    .send sEnv
    xmlDoc.LoadXML .responseText
    End With
    
    Set NodeList = xmlhtp.responseXML.SelectSingleNode("//multiRef")
    getValor = NodeList.Text * 1

End Function