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

[deyken]

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!

[Jan Karel Pieterse]

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.

[deyken]

Dankie, Jan

Het jy een voorbeeld van kode?

[Jan Karel Pieterse]

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]

[Kenneth Hobs]

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]

[Actuary1010]

Great!