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 😉