How to Convert Currency in Excel Using an API: Complete Guide

Finance professionals, accountants, and business analysts spend countless hours dealing with currency conversions in Excel. Manually updating exchange rates is tedious, error-prone, and quickly becomes outdated. Static rate tables from the internet are unreliable and lack historical data for proper financial reporting.

The solution is integrating exchange rate APIs directly into Excel. This tutorial shows you three methods: a no-code Power Query approach for beginners, VBA functions for intermediate users, and advanced automation for power users. Choose the method that matches your skill level and requirements.

1. Why Use an API Instead of Manual Entry?

Real-World Use Cases

• Financial Reporting: Convert foreign subsidiary revenues to reporting currency with correct historical rates
• Expense Tracking: Automatically convert international transactions in corporate credit card statements
• Sales Analysis: Consolidate sales from multiple countries into a single currency for comparison
• Budget Planning: Project international expenses using current and forecasted exchange rates
• Invoice Generation: Create invoices in customer's local currency with up-to-date rates

2. Method Comparison: VBA vs Power Query

3. Power Query Method (No Code)

Step 1: Get Your API Key

Sign up for a free exchange rate API and get your API key. Most providers offer 1000+ free requests per month.

Step 2: Create Power Query Connection

Follow these steps in Excel:

1. Go to Data tab → Get Data → From Other Sources → From Web
2. Enter your API URL (replace YOUR_API_KEY):

   https://api.unirateapi.com/v1/latest/USD?api_key=YOUR_API_KEY

3. Click OK - Excel will connect to the API
4. In Power Query Editor, click on rates to expand the JSON object
5. Click Close & Load to import data into Excel

Step 3: Transform the Data

The imported data needs formatting. In Power Query Editor:

1. Click on the expand icon next to rates column
2. Select Expand to New Rows
3. Rename columns: "Currency" and "Rate"
4. Set data types: Currency as Text, Rate as Decimal Number
5. Click Close & Load

Step 4: Use VLOOKUP for Conversions

Now use VLOOKUP to convert currencies:

=A2 * VLOOKUP(B2, RatesTable, 2, FALSE)

Where:
- A2 = Amount to convert
- B2 = Target currency code
- RatesTable = Your imported rate table
- 2 = Column with rates
- FALSE = Exact match

Step 5: Auto-Refresh the Data

Set up automatic refresh:

1. Right-click on your rate table
2. Select Query → Properties
3. Check Refresh data when opening the file
4. Check Refresh every X minutes (set to 60 for hourly updates)
5. Click OK

4. VBA Basic Implementation

Enabling Developer Tab

First, enable the Developer tab in Excel:

1. Go to File → Options → Customize Ribbon
2. Check Developer in the right panel
3. Click OK

Basic VBA Code to Fetch Rates

Press Alt + F11 to open VBA editor, insert a new module, and paste this code:

Sub FetchExchangeRates()
    ' Configuration
    Const API_KEY As String = "YOUR_API_KEY_HERE"
    Const BASE_URL As String = "https://api.unirateapi.com/v1/latest/USD"

    ' Create HTTP request
    Dim http As Object
    Set http = CreateObject("MSXML2.XMLHTTP")

    ' Build URL with API key
    Dim url As String
    url = BASE_URL & "?api_key=" & API_KEY

    ' Make request
    http.Open "GET", url, False
    http.send

    ' Check if successful
    If http.Status = 200 Then
        ' Parse JSON response
        Dim json As Object
        Set json = JsonConverter.ParseJson(http.responseText)

        ' Get the rates object
        Dim rates As Object
        Set rates = json("rates")

        ' Write to worksheet starting at A1
        Dim ws As Worksheet
        Set ws = ThisWorkbook.Sheets("Rates")

        ' Clear existing data
        ws.Range("A:B").ClearContents

        ' Write headers
        ws.Range("A1").Value = "Currency"
        ws.Range("B1").Value = "Rate"

        ' Write rates
        Dim row As Long
        row = 2

        Dim currency As Variant
        For Each currency In rates.Keys
            ws.Cells(row, 1).Value = currency
            ws.Cells(row, 2).Value = rates(currency)
            row = row + 1
        Next currency

        MsgBox "Exchange rates updated successfully!", vbInformation
    Else
        MsgBox "Error fetching rates: " & http.Status, vbCritical
    End If
End Sub

Alternative: Without JSON Library

If you can't install additional libraries, use this simpler approach with text parsing:

Function GetExchangeRate(fromCurrency As String, toCurrency As String) As Double
    ' Simple rate fetcher without JSON library
    Const API_KEY As String = "YOUR_API_KEY_HERE"

    ' Build URL
    Dim url As String
    url = "https://api.unirateapi.com/v1/latest/" & fromCurrency & _
          "?api_key=" & API_KEY & "¤cies=" & toCurrency

    ' Create HTTP request
    Dim http As Object
    Set http = CreateObject("MSXML2.XMLHTTP")

    ' Make request
    http.Open "GET", url, False
    http.send

    ' Parse response (simple text search)
    Dim response As String
    response = http.responseText

    ' Find the rate value (looks for "EUR":0.9234 pattern)
    Dim searchStr As String
    searchStr = """" & toCurrency & """:"

    Dim pos As Long
    pos = InStr(response, searchStr)

    If pos > 0 Then
        ' Extract number after the colon
        Dim startPos As Long
        startPos = pos + Len(searchStr)

        Dim endPos As Long
        endPos = InStr(startPos, response, ",")
        If endPos = 0 Then endPos = InStr(startPos, response, "}")

        Dim rateStr As String
        rateStr = Mid(response, startPos, endPos - startPos)

        GetExchangeRate = CDbl(rateStr)
    Else
        GetExchangeRate = 0
        MsgBox "Currency " & toCurrency & " not found", vbExclamation
    End If
End Function

5. Creating Custom Excel Functions

FXRATE Function

Create a custom function that works like any Excel formula:

Function FXRATE(fromCurrency As String, toCurrency As String, Optional amount As Double = 1) As Variant
    '
    ' FXRATE - Convert currency using live exchange rates
    ' Usage: =FXRATE("USD", "EUR", 100)
    ' Returns: Converted amount
    '

    On Error GoTo ErrorHandler

    ' Configuration
    Const API_KEY As String = "YOUR_API_KEY_HERE"
    Const BASE_URL As String = "https://api.unirateapi.com/v1/latest/"

    ' Validate inputs
    If fromCurrency = "" Or toCurrency = "" Then
        FXRATE = CVErr(xlErrValue)
        Exit Function
    End If

    ' Same currency
    If UCase(fromCurrency) = UCase(toCurrency) Then
        FXRATE = amount
        Exit Function
    End If

    ' Check cache first (optional - see caching section)
    Dim cacheKey As String
    cacheKey = UCase(fromCurrency) & "_" & UCase(toCurrency)

    Dim rate As Double
    rate = GetCachedRate(cacheKey)

    If rate = 0 Then
        ' Fetch from API
        Dim http As Object
        Set http = CreateObject("MSXML2.XMLHTTP")

        Dim url As String
        url = BASE_URL & fromCurrency & "?api_key=" & API_KEY & _
              "¤cies=" & toCurrency

        http.Open "GET", url, False
        http.send

        If http.Status = 200 Then
            ' Simple parsing (adjust for your API response format)
            Dim response As String
            response = http.responseText

            Dim searchStr As String
            searchStr = """" & toCurrency & """"

            Dim pos As Long
            pos = InStr(response, searchStr)

            If pos > 0 Then
                ' Extract rate (simplified parsing)
                Dim startPos As Long
                startPos = InStr(pos, response, ":") + 1

                Dim endPos As Long
                endPos = InStr(startPos, response, ",")
                If endPos = 0 Then endPos = InStr(startPos, response, "}")

                Dim rateStr As String
                rateStr = Trim(Mid(response, startPos, endPos - startPos))

                rate = CDbl(rateStr)

                ' Cache the rate
                CacheRate cacheKey, rate
            Else
                FXRATE = CVErr(xlErrNA)
                Exit Function
            End If
        Else
            FXRATE = CVErr(xlErrNA)
            Exit Function
        End If
    End If

    ' Calculate conversion
    FXRATE = amount * rate
    Exit Function

ErrorHandler:
    FXRATE = CVErr(xlErrValue)
End Function

Using the Custom Function

After adding the VBA code, use it like any Excel function:

Examples:

' Convert 100 USD to EUR
=FXRATE("USD", "EUR", 100)

' Convert value in cell A1 from GBP to JPY
=FXRATE("GBP", "JPY", A1)

' Get just the exchange rate (default amount = 1)
=FXRATE("EUR", "USD")

6. Auto-Refreshing Rates

Implementing Rate Caching

Cache rates to avoid hitting API limits and improve performance:

' Module-level variables for caching
Private rateCache As Object
Private cacheTimestamp As Date
Private Const CACHE_MINUTES As Long = 60

Function GetCachedRate(cacheKey As String) As Double
    ' Initialize cache if needed
    If rateCache Is Nothing Then
        Set rateCache = CreateObject("Scripting.Dictionary")
        cacheTimestamp = Now
    End If

    ' Check if cache is expired (older than 1 hour)
    If DateDiff("n", cacheTimestamp, Now) > CACHE_MINUTES Then
        rateCache.RemoveAll
        cacheTimestamp = Now
        GetCachedRate = 0
        Exit Function
    End If

    ' Return cached rate or 0 if not found
    If rateCache.Exists(cacheKey) Then
        GetCachedRate = rateCache(cacheKey)
    Else
        GetCachedRate = 0
    End If
End Function

Sub CacheRate(cacheKey As String, rate As Double)
    If rateCache Is Nothing Then
        Set rateCache = CreateObject("Scripting.Dictionary")
    End If

    rateCache(cacheKey) = rate
End Sub

Auto-Refresh on Workbook Open

Private Sub Workbook_Open()
    ' Automatically refresh rates when workbook opens
    Application.StatusBar = "Fetching exchange rates..."

    On Error Resume Next
    Call FetchExchangeRates
    On Error GoTo 0

    Application.StatusBar = False
End Sub

Scheduled Refresh

Private refreshTimer As Date

Sub StartAutoRefresh()
    ' Refresh rates every hour
    Call RefreshRates
    refreshTimer = Now + TimeValue("01:00:00")
    Application.OnTime refreshTimer, "RefreshRates"
End Sub

Sub RefreshRates()
    Call FetchExchangeRates

    ' Schedule next refresh
    refreshTimer = Now + TimeValue("01:00:00")
    Application.OnTime refreshTimer, "RefreshRates"
End Sub

Sub StopAutoRefresh()
    On Error Resume Next
    Application.OnTime refreshTimer, "RefreshRates", , False
    On Error GoTo 0
End Sub

7. Advanced Features and Templates

Historical Rate Function

Function FXRATE_HISTORICAL(fromCurrency As String, _
                            toCurrency As String, _
                            rateDate As Date, _
                            Optional amount As Double = 1) As Variant
    '
    ' Get historical exchange rate for a specific date
    ' Usage: =FXRATE_HISTORICAL("USD", "EUR", DATE(2023,3,15), 100)
    '

    Const API_KEY As String = "YOUR_API_KEY_HERE"
    Const BASE_URL As String = "https://api.unirateapi.com/v1/historical/"

    On Error GoTo ErrorHandler

    ' Format date as YYYY-MM-DD
    Dim dateStr As String
    dateStr = Format(rateDate, "yyyy-mm-dd")

    ' Build URL
    Dim url As String
    url = BASE_URL & dateStr & "/" & fromCurrency & _
          "?api_key=" & API_KEY & "¤cies=" & toCurrency

    ' Make request
    Dim http As Object
    Set http = CreateObject("MSXML2.XMLHTTP")

    http.Open "GET", url, False
    http.send

    If http.Status = 200 Then
        ' Parse response (simplified)
        Dim response As String
        response = http.responseText

        ' Extract rate
        Dim pos As Long
        pos = InStr(response, """" & toCurrency & """:")

        If pos > 0 Then
            Dim startPos As Long
            startPos = pos + Len(toCurrency) + 3

            Dim endPos As Long
            endPos = InStr(startPos, response, ",")
            If endPos = 0 Then endPos = InStr(startPos, response, "}")

            Dim rate As Double
            rate = CDbl(Mid(response, startPos, endPos - startPos))

            FXRATE_HISTORICAL = amount * rate
        Else
            FXRATE_HISTORICAL = CVErr(xlErrNA)
        End If
    Else
        FXRATE_HISTORICAL = CVErr(xlErrNA)
    End If

    Exit Function

ErrorHandler:
    FXRATE_HISTORICAL = CVErr(xlErrValue)
End Function

Currency Converter Template

Create a user-friendly currency converter sheet:

A1: Amount:           B1: [Input box]
A2: From Currency:    B2: [Dropdown: USD, EUR, GBP, etc.]
A3: To Currency:      B3: [Dropdown: USD, EUR, GBP, etc.]
A4: Exchange Rate:    B4: =FXRATE(B2, B3)
A5: Converted Amount: B5: =FXRATE(B2, B3, B1)
A6: Last Updated:     B6: =NOW()

Batch Conversion Table

Convert a table of transactions all at once:

Setup:
Column A: Transaction Date
Column B: Amount
Column C: Currency
Column D: Formula: =FXRATE_HISTORICAL(C2, "USD", A2, B2)

This converts all transactions to USD using historical rates for each date.

8. Troubleshooting Common Issues

Conclusion

You now have multiple methods to integrate real-time exchange rates into Excel. Whether you choose the no-code Power Query approach or the flexible VBA custom functions, you've eliminated manual rate entry and ensured your financial calculations use accurate, up-to-date data.

Key benefits you've gained:

• Automatic rate updates - no more manual entry
• Access to 150+ currencies instantly
• Historical rates for accurate financial reporting
• Custom functions that work like built-in Excel formulas
• Caching to minimize API calls and costs
• Auto-refresh capabilities for always-current data

Start with Power Query if you're new to automation. As your needs grow, migrate to VBA for more control and custom functions. Both approaches save hours of manual work and eliminate human error in currency calculations.

Related Articles