Excel Forex Currency Converter

In this post we learn to create a foreign exchange currency converter in Excel. We do not necessarily have to use macros to do so, but may need to code macros in some scenarios. First we need to get the current exchange rates from the internet. We can get that from a website through a web query, or scraping the data, or from an API. In this article we will see how to do it with a web query by either manually importing web data into a query table or using a macro to add the web query to a worksheet (which was already covered in this other post: Add Web Query To Worksheet).

Exchange Rates

Excel Forex Currency Converter needs to get first the current up-to-date exchange rates. We can get that from any website that shows the exchange rates in a table that can be retrieved through a web query. If the data is not in tabular format, we would need to scrape the site to get the rates using any of the various methods to scrape data. See more about data scraping in Excel in this article: Scrape Web Data in Excel.

The following website contains exchange rates in relation to the Euro. You may want to use other website to get rates against the US Dollar, or your home currency. We can import the data manually from Data -> New Query -> From Other Sources -> From Web, or using a macro to automatically add the web query to the worksheet.

Here’s the macro to add a web query for the website above to the active worksheet. The macro targets the worksheet “query” and deletes any existing query table first, to add the new query table to the worksheet. The query table is set to update on file open to get the latest exchange rates and make an accurate currency conversion in Excel every time we open the workbook. You can find more details about the macro in this other post: Add Web Query ToWorksheet.

 

  Sub GetExchangeRates()

      Dim qtCount As Integer, qt As QueryTable, website As String

   

      website = "https://www.iban.com/exchange-rates"

   

      With Sheets("query")

          qtCount = .QueryTables.Count

          If qtCount > 0 Then .QueryTables(1).Delete

          .Cells.Clear

   

          Set qt = .QueryTables.Add(Connection:="URL;" & website, _

          Destination:=.Range("A1"))

      End With

       

      With qt

          .WebSelectionType = xlAllTables

          .RefreshOnFileOpen = True

          .Refresh

      End With

  End Sub

 

 

When running the macro, the query table is added to worksheet “query”. The table has the currency symbol in column A, the full name of the currency in column B, and the current exchange rate in relation to 1 Euro in column C.

The currency symbol appears duplicated (e.g. USDUSD instead of USD), so we will crop that and put it in a separate table/worksheet (Currencies) and sort the currencies alphabetically. We are also going to use that as a validation list to select the currencies in the main converter Excel worksheet.

 

Currency Converter (no macros)

Excel Forex Currency Converter allows to select each currency from a drop-down in cells B5 and D5, and add the amount to be converted in cell B4, either manually or using the buttons. When selecting a currency, the price of that currency in relation to the Euro is added below on B6 or D6.

That’s taken from the Currencies worksheet through a VLookup formula. Below is the formula used in cell B6, which looks up the value in cell B5 (the currency symbol) in range A1:B32 in sheet “Currencies”, and gets the price for a given currency.

=IF(B5<>"",VLOOKUP(B5,Currencies!$A$1:$B$32,2,0),"")

 

Another formula calculates de conversion of the amount added by multiplying and dividing by the quote and base price of the currencies respectively. The formula in the example above is as follows:

=IF(AND(D6<>"",B6<>""),B4*D6/B6,"")

 

The worksheet can be protected to avoid users delete or change the formulas. In that case, the cells that are changing need to be unlocked first. Formulas can also be hidden in the same place where cells are unlocked under Format Cells -> Protection.

 

Currency Converter (using macros)

Instead of using formulas on the worksheet, we can code macros to get the rates and calculate the conversion. This could be useful if we want to add buttons to convert the value, for example. In that case, the currency conversion in Excel for the example in this post would occur through this macro:

 

  Sub CalculateConversion()

      Dim amount As Single, convertvalue As Single

      Dim baseprice As Single, quoteprice As Single

      amount = Sheet1.Range("B4").Value

      baseprice = Sheet1.Range("B6").Value

      quoteprice = Sheet1.Range("D6").Value

      convertvalue = amount * quoteprice / baseprice

      Sheet1.Range("D4").Value = convertvalue

  End Sub

 

 

We could also update the currency price with a macro following the procedure below. That’s basically using the VLookup worksheet function to get the price of the selected currency (in cell), in a similar way is done with the VLookup formula directly on the worksheet.

 

  Sub UpdatePrice(cell)

      Dim cur As String, curprice As Single

      cur = cell.Value

      curprice = WorksheetFunction.VLookup(cur, Sheet3.Range("A1:B34"), 2, 0)

      cell.Offset(1, 0).Value = curprice

  End Sub

 

 

But this macro needs to be called from an event procedure that triggers when the currency field changes. That could either be cell B5 or D5. The event procedure is added to the worksheet CONVERTER.

 

  Private Sub Worksheet_Change(ByVal Target As Range)

      If Target = Range("B5") Or Target = Range("D5") Then

          Call UpdatePrice(Target)

      End If

  End Sub

 

 

Click Here to download Excel Forex Currency Converter.