Consulting

Results 1 to 6 of 6

Thread: How to use WSDL/SOAP to Get Forex Rates into Excel

  1. #1
    VBAX Regular
    Joined
    May 2009
    Location
    Johannesburg
    Posts
    69
    Location

    How to use WSDL/SOAP to Get Forex Rates into Excel

    Good Day All,

    I am actually a Delphi Programmer and have written a program some time ago with it to assist Diamond Dealers collect official RAPAPORT Price lists via Web Services into the Program.

    Due to high demand, however, I have been asked to write a similar (yet "lighter") for Excel 2003/2007 users.

    Can Excel/VBA work with SOAP/WSDL from, say Google Finance or something similar - Right now I simply want to download a full range of Forex Rates and place the data into Excel columns.

    Any help would be appreciated!
    Deyken
    DeezineTek
    South Africa

  2. #2
    Yes, it is possible. But you need to know the exact SOAP envelope you have to pass to the webservice.
    If you have a working webservice which is publicly available, post back with the url.
    Regards,

    Jan Karel Pieterse
    Excel MVP jkp-ads.com

  3. #3
    VBAX Regular
    Joined
    May 2009
    Location
    Johannesburg
    Posts
    69
    Location
    Dankie, Jan

    Het jy een voorbeeld van kode?
    Deyken
    DeezineTek
    South Africa

  4. #4
    Jazeker, alleen niet met een goed werkende webservice:

    [VBA]Option Explicit
    Sub DoIt()
    Dim sURL As String
    Dim sEnv As String
    Dim xmlhtp As New MSXML2.XMLHTTP40
    Dim xmlDoc As New DOMDocument
    sURL = "http://webservices.gama-system.com/e...ntConvertToEUR"

    sEnv = "<?xml version=""1.0"" encoding=""utf-8""?>"
    sEnv = sEnv & "<soap:Envelope xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xmlns:xsd=""http://www.w3.org/2001/XMLSchema"" xmlns:soap=""http://schemas.xmlsoap.org/soap/envelope/"">"
    sEnv = sEnv & " <soap:Body>"
    sEnv = sEnv & " <CurrentConvertToEUR xmlns=""http://www.gama-system.com/webservices"">"
    sEnv = sEnv & " <dcmEUR>100</dcmEUR>"
    sEnv = sEnv & " <strBank>ING</strBank>"
    sEnv = sEnv & " <strCurrency>USD</strCurrency>"
    sEnv = sEnv & " <intRank>1</intRank>"
    sEnv = sEnv & " </CurrentConvertToEUR>"
    sEnv = sEnv & " </soap:Body>"
    sEnv = sEnv & "</soap:Envelope>"

    With xmlhtp
    .Open "post", sURL, False
    .setRequestHeader "Host", "webservices.gama-system.com"
    .setRequestHeader "Content-Type", "text/xml; charset=utf-8"
    .setRequestHeader "soapAction", "http://www.gama-system.com/webservic...ntConvertToEUR"
    .setRequestHeader "Accept-encoding", "zip"
    .send sEnv
    xmlDoc.loadXML .responseText
    MsgBox .responseText
    End With
    'xmlDoc.Save ThisWorkbook.Path & "\WebQueryResult.xml"
    End Sub
    [/VBA]
    Regards,

    Jan Karel Pieterse
    Excel MVP jkp-ads.com

  5. #5
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,900
    Location
    Nice example Jan!

    Here is a tweak where I used your example with MSXML2 v6.0. I changed your strEUR to strvalue. I also changed the value of strBank to BS. I did not see ING in the list of values for strBank values BS, NLB, SKB, and NKBM.

    Of course it would easy enough to parse using MSXML2 or Chilkat to get values from the XML. Stanl discussed using logparser in some past threads so that might be worth exploring as well.

    [VBA]'Set Reference to Microsoft XML, v6.0
    Sub DoIt2()
    Dim sURL As String
    Dim sEnv As String
    Dim xmlhtp As New MSXML2.XMLHTTP
    Dim xmlDoc As New DOMDocument
    sURL = "http://webservices.gama-system.com/exchangerates.asmx?op=CurrentConvertToEUR"

    sEnv = "<?xml version=""1.0"" encoding=""utf-8""?>"
    sEnv = sEnv & "<soap:Envelope xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xmlns:xsd=""http://www.w3.org/2001/XMLSchema"" xmlns:soap=""http://schemas.xmlsoap.org/soap/envelope/"">"
    sEnv = sEnv & " <soap:Body>"
    sEnv = sEnv & " <CurrentConvertToEUR xmlns=""http://www.gama-system.com/webservices"">"
    sEnv = sEnv & " <dcmValue>100</dcmValue>"
    sEnv = sEnv & " <strBank>BS</strBank>"
    sEnv = sEnv & " <strCurrency>USD</strCurrency>"
    sEnv = sEnv & " <intRank>1</intRank>"
    sEnv = sEnv & " </CurrentConvertToEUR>"
    sEnv = sEnv & " </soap:Body>"
    sEnv = sEnv & "</soap:Envelope>"

    With xmlhtp
    .Open "post", sURL, False
    .setRequestHeader "Host", "webservices.gama-system.com"
    .setRequestHeader "Content-Type", "text/xml; charset=utf-8"
    .setRequestHeader "soapAction", "http://www.gama-system.com/webservices/CurrentConvertToEUR"
    .setRequestHeader "Accept-encoding", "zip"
    .send sEnv
    xmlDoc.LoadXML .responseText
    'xmlDoc.Save ThisWorkbook.Path & "\WebQueryResult.xml"
    MsgBox .responseText
    End With
    End Sub

    [/VBA]

  6. #6
    Great!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •