Currency Conversion in Excel

This week I’m going to show you how create a macro in Excel to allow you to convert currency within Excel. The following tip does not feature within the Filtered Excel course, however, there are modules showing you how to create macros and other functions of a similar complexity.

This is not a function that is automatically available within the software and there is more than one way to create a macro to achieve this functionality.
In this instance you will need to customize your toolbar to enable this capability. For a simpler solution to this you can download a currency conversion template for Excel from Office Online.

To create your currency converter you will need to follow a four step process. You’ll need to:

• Enable the Developer Toolbar
• Add Web-posting Functionality
• Creating a Currency Converting Macro
• Using the Currency Converter

Enabling the Developer Toolbar:

To do this you’ll need to alter your ribbon to enable the Developer Toolbar. To do this simply right click on your ribbon and select the Customize Ribbon option. Once the options window pops up you’ll need to check the Developer options within the Main Tabs list on the right of your screen:

Add Web-posting Functionality:

Windows and Office have the ability to access DLL files that contain functionality that Excel can use, but isn’t configured to. Many other programs are able to use these files but in Excel’s case we need to give Excel the ability to post information over the web (using HTTP) and receive a reply in return. It’s possible that your version of Excel is already configured for this but it’s more likely that it’s not.
Within the Developer Toolbar, press the Visual Basic button. The Visual Basic Editor will open:

Click TOOLS > REFERENCES

In the box that appears, scroll down to find Microsoft WinHTTP Services and enable it (the list is VERY long!)

Press OK to return to the Visual Basic Editor. We will now create the macro.

Creating a Currency Converting Macro

Excel has lots of built-in functions that we can use to do calculations. By now you should be familiar with functions such as SUM(), AVERAGE() and COUNT() for example. The following method will create a new function called MYCURRENCYEXCHANGER() that will take our two currencies as parameters, and return the exchange rate.

Click on INSERT then select MODULE

You will see a new module appear called MODULE1.

Double-click MODULE1 to open it in the editor (it may have opened automatically for you).  Now paste in this code:

Option Explicit

Function MYCURRENCYEXCHANGER(SourceCur As String, DestCur As String) As Variant
Dim url As String
‘ http://quote.yahoo.com/d/quotes.csv?s=XXXYYY=X&f=l1 this is the link format where XXX is currency1 and YYY is currency2 ‘
url = “http://quote.yahoo.com/d/quotes.csv?s=” & SourceCur & DestCur & “=X&f=l1”
Dim myHTTP As New WinHttp.WinHttpRequest
myHTTP.Open “GET”, url, False
myHTTP.send “”
If myHTTP.StatusText <> “OK” Then GoTo ServerErrorHandler
If Not (WorksheetFunction.IsNumber(myHTTP.responseText)) Then MYCURRENCYEXCHANGER = 0
MYCURRENCYEXCHANGER = CDbl(myHTTP.responseText)
Exit Function

ServerErrorHandler:
MsgBox “Error. Could not convert currency”

End Function

The window should look like this:

The code within a Module is available throughout the entire workbook (across all spreadsheets), which is why we use this approach. This makes our new function available to use anywhere.

Once the code is pasted in, close the Editor window.

Using the Currency Converter

Within Excel, type in two three-letter ISO currency codes into the top cells. In my case I am converting British Pounds (GBP) into Euros (EUR).

Now within cell B2, paste this formula:

=MYCURRENCYEXCHANGER(A2,A3)

Now press RETURN to confirm the formula. At this stage Excel will freeze and seem to become unresponsive. This is normal, and is what happens while Excel retrieves the live exchange rate – just be patient!

When Excel comes back to life, you will see something like this:

Now we can use this figure to convert some numbers.  Here’s some random numbers I just added and converted using a formula to reference the cell containing the conversion rate:

AAT members have free access to this and many other Excel Tips through their MyAAT account. To continue exploring more Excel tips please click here.

Paolo Lenotti is the Head of Marketing & PR at Filtered.com.