Did you ever want to check currency exchange rates but thought it was too difficult? Here's one method of doing so using web services.
The Currency Convertor from WebserviceX.NET lets you look up exchange rates for 151 different currency types. All you need to do is formulate the URLs to request the two currencies and the web service returns the exchange rate. Let's take a look at just how to do that.
First let's start with the main function, then we'll review the ancillary functions.
Return the exchange rate for two currency types
The following function takes two custom currency types (which we'll see below), parses the web response for the conversion rate and returns the exchange rate as a double type.
Function CurrencyConversionRate(fromCurr As CurrencyType, toCurr As CurrencyType) _
As Double
' http://www.webservicex.net/WCF/ServiceDetails.aspx?SID=18
Dim xml As Object
Dim result As String
Dim lFirstChar As Long
Dim lLastChar As Long
Dim fromCurrency As String
Dim toCurrency As String
fromCurrency = GetCurrencyName(fromCurr)
toCurrency = GetCurrencyName(toCurr)
Set xml = CreateObject("MSXML2.XMLHTTP.6.0")
xml.Open "GET", _
"http://www.webservicex.net/CurrencyConvertor.asmx/ConversionRate?FromCurrency=" & fromCurrency & "&ToCurrency=" & toCurrency, False
xml.Send
result = xml.responsetext
' parse result for response
lFirstChar = InStr(result, "webserviceX.NET")
lFirstChar = InStr(lFirstChar, result, ">") + 1
lLastChar = InStr(lFirstChar, result, "<")
CurrencyConversionRate = Mid$(result, lFirstChar, lLastChar - lFirstChar)
End Function
Get currency name from custom currency type
This function takes the custom currency type (see below) and returns the currency code.
Function GetCurrencyName(cType As CurrencyType) As String
Select Case cType
Case 0: GetCurrencyName = "AFA" ' -Afghanistan Afghani
Case 1: GetCurrencyName = "ALL" ' -Albanian Lek
Case 2: GetCurrencyName = "DZD" ' -Algerian Dinar
Case 3: GetCurrencyName = "ARS" ' -Argentine Peso
Case 4: GetCurrencyName = "AWG" ' -Aruba Florin
Case 5: GetCurrencyName = "AUD" ' -Australian Dollar
Case 6: GetCurrencyName = "BSD" ' -Bahamian Dollar
Case 7: GetCurrencyName = "BHD" ' -Bahraini Dinar
Case 8: GetCurrencyName = "BDT" ' -Bangladesh Taka
Case 9: GetCurrencyName = "BBD" ' -Barbados Dollar
Case 10: GetCurrencyName = "BZD" ' -Belize Dollar
Case 11: GetCurrencyName = "BMD" ' -Bermuda Dollar
Case 12: GetCurrencyName = "BTN" ' -Bhutan Ngultrum
Case 13: GetCurrencyName = "BOB" ' -Bolivian Boliviano
Case 14: GetCurrencyName = "BWP" ' -Botswana Pula
Case 15: GetCurrencyName = "BRL" ' -Brazilian Real
Case 16: GetCurrencyName = "GBP" ' -British Pound
Case 17: GetCurrencyName = "BND" ' -Brunei Dollar
Case 18: GetCurrencyName = "BIF" ' -Burundi Franc
Case 19: GetCurrencyName = "XOF" ' -CFA Franc (BCEAO)
Case 20: GetCurrencyName = "XAF" ' -CFA Franc (BEAC)
Case 21: GetCurrencyName = "KHR" ' -Cambodia Riel
Case 22: GetCurrencyName = "CAD" ' -Canadian Dollar
Case 23: GetCurrencyName = "CVE" ' -Cape Verde Escudo
Case 24: GetCurrencyName = "KYD" ' -Cayman Islands Dollar
Case 25: GetCurrencyName = "CLP" ' -Chilean Peso
Case 26: GetCurrencyName = "CNY" ' -Chinese Yuan
Case 27: GetCurrencyName = "COP" ' -Colombian Peso
Case 28: GetCurrencyName = "KMF" ' -Comoros Franc
Case 29: GetCurrencyName = "CRC" ' -Costa Rica Colon
Case 30: GetCurrencyName = "HRK" ' -Croatian Kuna
Case 31: GetCurrencyName = "CUP" ' -Cuban Peso
Case 32: GetCurrencyName = "CYP" ' -Cyprus Pound
Case 33: GetCurrencyName = "CZK" ' -Czech Koruna
Case 34: GetCurrencyName = "DKK" ' -Danish Krone
Case 35: GetCurrencyName = "DJF" ' -Dijibouti Franc
Case 36: GetCurrencyName = "DOP" ' -Dominican Peso
Case 37: GetCurrencyName = "XCD" ' -East Caribbean Dollar
Case 38: GetCurrencyName = "EGP" ' -Egyptian Pound
Case 39: GetCurrencyName = "SVC" ' -El Salvador Colon
Case 40: GetCurrencyName = "EEK" ' -Estonian Kroon
Case 41: GetCurrencyName = "ETB" ' -Ethiopian Birr
Case 42: GetCurrencyName = "EUR" '-Euro
Case 43: GetCurrencyName = "FKP" ' -Falkland Islands Pound
Case 44: GetCurrencyName = "GMD" ' -Gambian Dalasi
Case 45: GetCurrencyName = "GHC" ' -Ghanian Cedi
Case 46: GetCurrencyName = "GIP" ' -Gibraltar Pound
Case 47: GetCurrencyName = "XAU" ' -Gold Ounces
Case 48: GetCurrencyName = "GTQ" ' -Guatemala Quetzal
Case 49: GetCurrencyName = "GNF" ' -Guinea Franc
Case 50: GetCurrencyName = "GYD" '-Guyana Dollar
Case 51: GetCurrencyName = "HTG" '-Haiti Gourde
Case 52: GetCurrencyName = "HNL" '-Honduras Lempira
Case 53: GetCurrencyName = "HKD" '-Hong Kong Dollar
Case 54: GetCurrencyName = "HUF" '-Hungarian Forint
Case 55: GetCurrencyName = "ISK" '-Iceland Krona
Case 56: GetCurrencyName = "INR" '-Indian Rupee
Case 57: GetCurrencyName = "IDR" '-Indonesian Rupiah
Case 58: GetCurrencyName = "IQD" '-Iraqi Dinar
Case 59: GetCurrencyName = "ILS" '-Israeli Shekel
Case 60: GetCurrencyName = "JMD" '-Jamaican Dollar
Case 61: GetCurrencyName = "JPY" '-Japanese Yen
Case 62: GetCurrencyName = "JOD" '-Jordanian Dinar
Case 63: GetCurrencyName = "KZT" '-Kazakhstan Tenge
Case 64: GetCurrencyName = "KES" '-Kenyan Shilling
Case 65: GetCurrencyName = "KRW" '-Korean Won
Case 66: GetCurrencyName = "KWD" '-Kuwaiti Dinar
Case 67: GetCurrencyName = "LAK" '-Lao Kip
Case 68: GetCurrencyName = "LVL" '-Latvian Lat
Case 69: GetCurrencyName = "LBP" '-Lebanese Pound
Case 70: GetCurrencyName = "LSL" '-Lesotho Loti
Case 71: GetCurrencyName = "LRD" '-Liberian Dollar
Case 72: GetCurrencyName = "LYD" '-Libyan Dinar
Case 73: GetCurrencyName = "LTL" '-Lithuanian Lita
Case 74: GetCurrencyName = "MOP" '-Macau Pataca
Case 75: GetCurrencyName = "MKD" '-Macedonian Denar
Case 76: GetCurrencyName = "MGF" '-Malagasy Franc
Case 77: GetCurrencyName = "MWK" '-Malawi Kwacha
Case 78: GetCurrencyName = "MYR" '-Malaysian Ringgit
Case 79: GetCurrencyName = "MVR" '-Maldives Rufiyaa
Case 80: GetCurrencyName = "MTL" '-Maltese Lira
Case 81: GetCurrencyName = "MRO" '-Mauritania Ougulya
Case 82: GetCurrencyName = "MUR" '-Mauritius Rupee
Case 83: GetCurrencyName = "MXN" '-Mexican Peso
Case 84: GetCurrencyName = "MDL" '-Moldovan Leu
Case 85: GetCurrencyName = "MNT" '-Mongolian Tugrik
Case 86: GetCurrencyName = "MAD" '-Moroccan Dirham
Case 87: GetCurrencyName = "MZM" '-Mozambique Metical
Case 88: GetCurrencyName = "MMK" '-Myanmar Kyat
Case 89: GetCurrencyName = "NAD" '-Namibian Dollar
Case 90: GetCurrencyName = "NPR" '-Nepalese Rupee
Case 91: GetCurrencyName = "ANG" '-Neth Antilles Guilder
Case 92: GetCurrencyName = "NZD" '-New Zealand Dollar
Case 93: GetCurrencyName = "NIO" '-Nicaragua Cordoba
Case 94: GetCurrencyName = "NGN" '-Nigerian Naira
Case 95: GetCurrencyName = "KPW" '-North Korean Won
Case 96: GetCurrencyName = "NOK" '-Norwegian Krone
Case 97: GetCurrencyName = "OMR" '-Omani Rial
Case 98: GetCurrencyName = "XPF" '-Pacific Franc
Case 99: GetCurrencyName = "PKR" '-Pakistani Rupee
Case 100: GetCurrencyName = "XPD" '-Palladium Ounces
Case 101: GetCurrencyName = "PAB" '-Panama Balboa
Case 102: GetCurrencyName = "PGK" '-Papua New Guinea Kina
Case 103: GetCurrencyName = "PYG" '-Paraguayan Guarani
Case 104: GetCurrencyName = "PEN" '-Peruvian Nuevo Sol
Case 105: GetCurrencyName = "PHP" '-Philippine Peso
Case 106: GetCurrencyName = "XPT" '-Platinum Ounces
Case 107: GetCurrencyName = "PLN" '-Polish Zloty
Case 108: GetCurrencyName = "QAR" '-Qatar Rial
Case 109: GetCurrencyName = "ROL" '-Romanian Leu
Case 110: GetCurrencyName = "RUB" '-Russian Rouble
Case 111: GetCurrencyName = "WST" '-Samoa Tala
Case 112: GetCurrencyName = "STD" '-Sao Tome Dobra
Case 113: GetCurrencyName = "SAR" '-Saudi Arabian Riyal
Case 114: GetCurrencyName = "SCR" '-Seychelles Rupee
Case 115: GetCurrencyName = "SLL" ' -Sierra Leone Leone
Case 116: GetCurrencyName = "XAG" ' -Silver Ounces
Case 117: GetCurrencyName = "SGD" '-Singapore Dollar
Case 118: GetCurrencyName = "SKK" '-Slovak Koruna
Case 119: GetCurrencyName = "SIT" '-Slovenian Tolar
Case 120: GetCurrencyName = "SBD" '-Solomon Islands Dollar
Case 121: GetCurrencyName = "SOS" '-Somali Shilling
Case 122: GetCurrencyName = "ZAR" '-South African Rand
Case 123: GetCurrencyName = "LKR" '-Sri Lanka Rupee
Case 124: GetCurrencyName = "SHP" '-St Helena Pound
Case 125: GetCurrencyName = "SDD" '-Sudanese Dinar
Case 126: GetCurrencyName = "SRG" '-Surinam Guilder
Case 127: GetCurrencyName = "SZL" '-Swaziland Lilageni
Case 128: GetCurrencyName = "SEK" '-Swedish Krona
Case 129: GetCurrencyName = "TRY" '-Turkey Lira
Case 130: GetCurrencyName = "CHF" '-Swiss Franc
Case 131: GetCurrencyName = "SYP" '-Syrian Pound
Case 132: GetCurrencyName = "TWD" '-Taiwan Dollar
Case 133: GetCurrencyName = "TZS" '-Tanzanian Shilling
Case 134: GetCurrencyName = "THB" '-Thai Baht
Case 135: GetCurrencyName = "TOP" '-Tonga Pa'anga
Case 136: GetCurrencyName = "TTD" '-Trinidad&Tobago Dollar
Case 137: GetCurrencyName = "TND" '-Tunisian Dinar
Case 138: GetCurrencyName = "TRL" '-Turkish Lira
Case 139: GetCurrencyName = "USD" '-U.S.Dollar
Case 140: GetCurrencyName = "AED" '-UAE Dirham
Case 141: GetCurrencyName = "UGX" '-Ugandan Shilling
Case 142: GetCurrencyName = "UAH" '-Ukraine Hryvnia
Case 143: GetCurrencyName = "UYU" '-Uruguayan New Peso
Case 144: GetCurrencyName = "VUV" '-Vanuatu Vatu
Case 145: GetCurrencyName = "VEB" '-Venezuelan Bolivar
Case 146: GetCurrencyName = "VND" '-Vietnam Dong
Case 147: GetCurrencyName = "YER" '-Yemen Riyal
Case 148: GetCurrencyName = "YUM" '-Yugoslav Dinar
Case 149: GetCurrencyName = "ZMK" '-Zambian Kwacha
Case 150: GetCurrencyName = "ZWD" '-Zimbabwe Dollar
End Select
End Function
Enum Section
Here is the enumerated section that all of the procedures above are counting on. Remember that Enums must be placed at the top of a standard module. Usually I put all my Enums into their own module (and declare them Public) to avoid placement confusion.
Public Enum CurrencyType AFA ' -Afghanistan Afghani ALL ' -Albanian Lek DZD ' -Algerian Dinar ARS ' -Argentine Peso AWG ' -Aruba Florin AUD ' -Australian Dollar BSD ' -Bahamian Dollar BHD ' -Bahraini Dinar BDT ' -Bangladesh Taka BBD ' -Barbados Dollar BZD ' -Belize Dollar BMD ' -Bermuda Dollar BTN ' -Bhutan Ngultrum BOB ' -Bolivian Boliviano BWP ' -Botswana Pula BRL ' -Brazilian Real GBP ' -British Pound BND ' -Brunei Dollar BIF ' -Burundi Franc XOF ' -CFA Franc (BCEAO) XAF ' -CFA Franc (BEAC) KHR ' -Cambodia Riel CAD ' -Canadian Dollar CVE ' -Cape Verde Escudo KYD ' -Cayman Islands Dollar CLP ' -Chilean Peso CNY ' -Chinese Yuan COP ' -Colombian Peso KMF ' -Comoros Franc CRC ' -Costa Rica Colon HRK ' -Croatian Kuna CUP ' -Cuban Peso CYP ' -Cyprus Pound CZK ' -Czech Koruna DKK ' -Danish Krone DJF ' -Dijibouti Franc DOP ' -Dominican Peso XCD ' -East Caribbean Dollar EGP ' -Egyptian Pound SVC ' -El Salvador Colon EEK ' -Estonian Kroon ETB ' -Ethiopian Birr EUR '-Euro FKP ' -Falkland Islands Pound GMD ' -Gambian Dalasi GHC ' -Ghanian Cedi GIP ' -Gibraltar Pound XAU ' -Gold Ounces GTQ ' -Guatemala Quetzal GNF ' -Guinea Franc GYD '-Guyana Dollar HTG '-Haiti Gourde HNL '-Honduras Lempira HKD '-Hong Kong Dollar HUF '-Hungarian Forint ISK '-Iceland Krona INR '-Indian Rupee IDR '-Indonesian Rupiah IQD '-Iraqi Dinar ILS '-Israeli Shekel JMD '-Jamaican Dollar JPY '-Japanese Yen JOD '-Jordanian Dinar KZT '-Kazakhstan Tenge KES '-Kenyan Shilling KRW '-Korean Won KWD '-Kuwaiti Dinar LAK '-Lao Kip LVL '-Latvian Lat LBP '-Lebanese Pound LSL '-Lesotho Loti LRD '-Liberian Dollar LYD '-Libyan Dinar LTL '-Lithuanian Lita MOP '-Macau Pataca MKD '-Macedonian Denar MGF '-Malagasy Franc MWK '-Malawi Kwacha MYR '-Malaysian Ringgit MVR '-Maldives Rufiyaa MTL '-Maltese Lira MRO '-Mauritania Ougulya MUR '-Mauritius Rupee MXN '-Mexican Peso MDL '-Moldovan Leu MNT '-Mongolian Tugrik MAD '-Moroccan Dirham MZM '-Mozambique Metical MMK '-Myanmar Kyat NAD '-Namibian Dollar NPR '-Nepalese Rupee ANG '-Neth Antilles Guilder NZD '-New Zealand Dollar NIO '-Nicaragua Cordoba NGN '-Nigerian Naira KPW '-North Korean Won NOK '-Norwegian Krone OMR '-Omani Rial XPF '-Pacific Franc PKR '-Pakistani Rupee XPD '-Palladium Ounces PAB '-Panama Balboa PGK '-Papua New Guinea Kina PYG '-Paraguayan Guarani PEN '-Peruvian Nuevo Sol PHP '-Philippine Peso XPT '-Platinum Ounces PLN '-Polish Zloty QAR '-Qatar Rial ROL '-Romanian Leu RUB '-Russian Rouble WST '-Samoa Tala STD '-Sao Tome Dobra SAR '-Saudi Arabian Riyal SCR '-Seychelles Rupee SLL ' -Sierra Leone Leone XAG ' -Silver Ounces SGD '-Singapore Dollar SKK '-Slovak Koruna SIT '-Slovenian Tolar SBD '-Solomon Islands Dollar SOS '-Somali Shilling ZAR '-South African Rand LKR '-Sri Lanka Rupee shp '-St Helena Pound SDD '-Sudanese Dinar SRG '-Surinam Guilder SZL '-Swaziland Lilageni SEK '-Swedish Krona TRY '-Turkey Lira CHF '-Swiss Franc SYP '-Syrian Pound TWD '-Taiwan Dollar TZS '-Tanzanian Shilling THB '-Thai Baht Top '-Tonga Pa'anga TTD '-Trinidad&Tobago Dollar TND '-Tunisian Dinar TRL '-Turkish Lira USD '-U.S.Dollar AED '-UAE Dirham UGX '-Ugandan Shilling UAH '-Ukraine Hryvnia UYU '-Uruguayan New Peso VUV '-Vanuatu Vatu VEB '-Venezuelan Bolivar VND '-Vietnam Dong YER '-Yemen Riyal YUM '-Yugoslav Dinar ZMK '-Zambian Kwacha ZWD '-Zimbabwe Dollar End Enum
Sample usage
The following procedure takes two currencies (Euro and U.S. Dollar) and gets the conversion rate from EUR to USD. If the exchange rate is favorable (i.e. EUR is lower than USD), the result will be >= 1. Either way, an appropriate message is printed to the Immediate Window.
The currencies are then swapped and the conversion is done again.
Sub TestCurrencyCheck()
Dim fromCurrency As CurrencyType
Dim toCurrency As CurrencyType
Dim fromCurrencyName As String
Dim toCurrencyName As String
Dim tempCurrencyName As CurrencyType
Dim result As Double
Dim relation As String
fromCurrency = EUR
toCurrency = USD
result = CurrencyConversionRate(fromCurrency, toCurrency)
' Debug.Print result
If result < 1 Then
relation = "less"
Else
relation = "more"
End If
fromCurrencyName = GetCurrencyName(fromCurrency)
toCurrencyName = GetCurrencyName(toCurrency)
Debug.Print fromCurrencyName & " vs. " & toCurrencyName
Debug.Print "---"
Debug.Print fromCurrencyName _
& " is valued " & relation & " than " & toCurrencyName & ":"
Debug.Print result & " " & toCurrencyName & " for each 1 " & fromCurrencyName
' swap currencies to show the reverse calculation
tempCurrencyName = fromCurrency
fromCurrency = toCurrency
toCurrency = tempCurrencyName
result = CurrencyConversionRate(fromCurrency, toCurrency)
' Debug.Print result
If result < 1 Then
relation = "less"
Else
relation = "more"
End If
fromCurrencyName = GetCurrencyName(fromCurrency)
toCurrencyName = GetCurrencyName(toCurrency)
Debug.Print fromCurrencyName _
& " is valued " & relation & " than " & toCurrencyName & ":"
Debug.Print result & " " & toCurrencyName & " for each 1 " & fromCurrencyName
End Sub
Download sample workbook with macros – Excel 2003
There are lots more web services to explore, so stay tuned!
About JP
8 Comments:
I am trying to convert from one currency to another.
A B D E
Amount Convert From Convert To Result
100 USD NZD
How can I utilise your VBa to get latest rate can convert and give me correct answer?
Kind Regards
Bhavik
Just call CurrencyConversionRate like this:
Hi JP,
Sorry I can't make this work. Could you please e-mail me file at bhavik_khatri@hotmail.com
Kind Regards,
Bhavik
Putting the functions into a file for you isn't going to help if you don't know how to use them.
Did you copy and paste the CurrencyConversionRate and GetCurrencyName functions into a standard module?
Did you copy and paste the Enum Section into a separate standard module, or at the top of an existing standard module?
If so, did you copy and paste the TestCurrencyCheck function into a standard module and try to run it? Did you get an error message? If so, what was the message?
You'll need to be more specific.
Hi there,
I've used your macro in a spreadsheet that I'm building, but a having an issue when trying to incorporate a loop function, allowing the user to provide a list of currencies that require a conversation rate into the range A2:A152, being the maximum number of currencies, please see below
Sub ExchangeConversionRates()
Dim CurrRange As Object
Dim fromCurrency As CurrencyType
Dim toCurrency As CurrencyType
Dim Result As Double
Dim fromCurrencyName As String
Dim toCurrencyName As String
Set CurrRange = Sheets("Exchange_Rate_Update").Range("A2:A152")
For n = 1 To CurrRange.Rows.Count
If CurrRange(n, 1) = "" Then
Else
fromCurrency = CurrRange(n, 1)
toCurrency = GBP
Result = CurrencyConversionRate(fromCurrency, toCurrency)
CurrRange(n, 2).Value = Result
End If
Next n
End Sub
Please could you help me
Thanks TJ
Hi
I am trying to convert from USD to Euro per date (past or current)
So, I just need to type the date and the function would return the currency rate of that day.
Is that possible?
Fx.
01-01-2012 2 USD = 0,75 Euro
21-01-2012 1 USD = 0,77 Euro
Regards
Fabio
Please let me know about today Thai Baht and Myanmar Kyats exchange rate.
Try a Google search.