How to Get Exchange Rates in Excel Automatically Using a Free API (VBA Step-by-Step)

Automate exchange rates in Excel in minutes. In this guide, you’ll learn how to retrieve currency data from multiple countries (including African currencies) using a free API and VBA.

This example is perfect if you work with financial reports, currency analysis, or need updated data without manual input.

🚀 What will you learn?

How to get real-time exchange rates in Excel
How to use a free API (no API key required)
How to automate Excel with VBA
How to retrieve multiple currencies (USD, MXN, EUR, ZAR, NGN, etc.)

🌍 Free Exchange Rate API

For this example, we will use the following free API:

👉 https://open.er-api.com/v6/latest/USD

Advantages:

No API key required
150+ currencies available
Includes African currencies
Daily updated data (not historical)

💻 VBA Macro to Get Exchange Rates in Excel

Copy and paste this code into a VBA module:


Sub GetExchangeRates()

    Dim http As Object
    Dim json As Object
    Dim url As String
    
    url = "https://open.er-api.com/v6/latest/USD"
    
    Set http = CreateObject("MSXML2.XMLHTTP")
    http.Open "GET", url, False
    http.Send
    
    If http.Status = 200 Then
    
        Set json = JsonConverter.ParseJson(http.responseText)
        
        Range("A1").Value = "Currency"
        Range("B1").Value = "Exchange Rate vs USD"
        
        Range("A2").Value = "MXN"
        Range("B2").Value = json("rates")("MXN")
        
        Range("A3").Value = "EUR"
        Range("B3").Value = json("rates")("EUR")
        
        Range("A4").Value = "ZAR"
        Range("B4").Value = json("rates")("ZAR")
        
        Range("A5").Value = "NGN"
        Range("B5").Value = json("rates")("NGN")
        
    Else
        MsgBox "API request failed"
    End If

End Sub

⚠️ Requirement: JSON Parser for VBA

To make this work, you need to import the JsonConverter.bas library.

Download it here:

👉 https://github.com/VBA-tools/VBA-JSON

Then:

Open VBA (Alt + F11)
Go to File → Import File
Select JsonConverter.bas

🔹 Enable Reference

In VBA, go to:

Tools > References

And enable:

✅ Microsoft Scripting Runtime

📊 Excel Output

After running the macro, you’ll get a table with updated exchange rates:

MXN → Mexican Peso
EUR → Euro
ZAR → South African Rand
NGN → Nigerian Naira

💡 What can you use this for?

Automated financial reports
Currency conversion in Excel
International market analysis
API integrations in Excel

📥 Download Ready-to-Use Excel Template

Want to save time? Get the ready-to-use Excel file:

✔ One-click update
✔ 150+ currencies
✔ VBA code included

👉 Download here

🔗 Related: Banxico API (Mexico)

If you need official exchange rates in Mexico, check this guide:

👉 How to use Banxico API step by step

❓ FAQ

Can Excel get exchange rates automatically?
Yes, by using an API and VBA you can update exchange rates instantly.

Do I need Power Query?
No, this solution works using VBA only.

🎯 Conclusion

With this macro, you can fully automate exchange rate retrieval in Excel using a free API.

This type of solution is widely used in professional environments where up-to-date data is required without manual work.

TIP: You can expand this to build dashboards or automate financial reports 😉