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://download.finance.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 Human Resources professional based in Hong Kong, specialized in business analysis, project management, data transformation with Access and Excel.

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

4 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 company network too, probably because the router is blocking the connection.

  2. Hi Wyman, Just tried again with no success. There must be something in the work network blocking the access. Do you know if it uses any particular ports? Do you know of any other ways of achieving the same result of downloading the rates?

Leave a Reply

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