Get exchange rate using VBA Access Function

This tutorial demonstrates how to create a VBA custom function (UDF) in Microsoft Access in order to get the real time exchange rate.

You may also want to read

Get Foreign Exchange Rate in Excel

Get exchange rate using VBA Access Function

Yahoo Finance is designed in the way to facilitate you to get data. You can establish a connection to Yahoo and directly get the exchange rate using User Defined Function.

Open Access, press ALT+F11 > Tools > References > enable “Microsoft WinHTTP Services”

Get Foreign Exchange Rate in Excel 09

Insert a Module and then insert the below code. This Function contains two arguments, the first argument is the target currency, the second argument is the original currency.

Public Function wConvertCurrency(currency1, currency2)
    Dim yahooHTTP As New WinHttp.WinHttpRequest
    yahooHTTP.Open "GET", "http://quote.yahoo.com/d/quotes.csv?s=" & currency1 & currency2 & "=X&f=l1"
    yahooHTTP.send
    wConvertCurrency = CDbl(yahooHTTP.ResponseText)
End Function

Example – Get exchange rate using VBA Access Function

Before you start, check yahoo finance what the currency codes they use for exchange rate, such as EUR, AUD, GBP, USD.

http://finance.yahoo.com/currency-converter/

Suppose you have a staff list that contains employee salary, but the salary is in different currency. You want to standardize them in USD.

Employee Name Monthly Salary Currency
Amy 10000 USD
Cat 10000 CNY
Dorothy 10000 EUR
Susan 10000 JPY

Now create a Query and add an Expression called Salary in USD

Salary in USD: wConvertCurrency("USD",[Currency])*[Monthly Salary]

Run the Query and you get the below result.

Get exchange rate using VBA Access Function 01

 

Wyman W
Wyman is a Business Analyst based in Hong Kong, specialized in creating custom Function and Sub solutions, and is proficient in report automation with Access.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist
- Microsoft Specialist
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

2 thoughts on “Get exchange rate using VBA Access Function

  1. This was exactly what I needed however when i run it it gets to yahooHTTP.send and come up with an error saying that a connection could not be made to the server. Any ideas?

    1. Hi Ryan, I just tried connecting and its working for me. I had the connection problem using computer network too, probably because the router is blocking the connection.

Leave a Reply

Your email address will not be published. Required fields are marked *