Foreign Exchange Rate Function in Excel

This Excel spreadsheet has a VBA function (UDF) that scrapes live foreign exchange rates from Yahoo Finance. Read on for the free Excel spreadsheet and VBA.

The Excel function is perfect for whenever you need a currency conversion. I use it to keep track of the British Pound value of a US and Canadian Dollar bank account.

The rates are scraped from Yahoo Finance, and are real-time (but delayed by 15 or 20 minutes).

The UDF is simple to use.  Here’s the syntax.

FXRate(“currency1”, “currency2”,[“close”, “open”, “bid”, “ask”])

This is an example of how to use FXRate() in Excel for a currency conversion between GBP and USD.

Currency Conversion Function in Excel

  • The first two arguments are the currency you’re converting from and the currency you’re converting to. These are three-letter currency codes (e.g. GBP for British Pounds, or CAD for Canadian Dollars) entered as strings.
  • The third argument is one of “close”, “open”, “bid” or “ask” (again as a string). The close price is the previous close.

The link to the Excel spreadsheet is at the bottom of this post, but here’s the VBA.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
Option Explicit
 
Function FXRate(currency1 As String, currency2 As String, rateType As String) As Double
 
Dim str As String
Dim temp As String
 
Dim bidStart As Long
Dim bidEnd As Long
Dim askStart As Long
Dim askEnd As Long
Dim openStart As Long
Dim openEnd As Long
Dim closeStart As Long
Dim closeEnd As Long
 
Dim bid As Double
Dim ask As Double
Dim ropen As Double
Dim rclose As Double
 
str = "http://finance.yahoo.com/q?s=" & currency1 & currency2 & "=X"
 
temp = ExecuteWebRequest(str)
 
bidStart = InStr(temp, "Bid:")
bidEnd = InStr(bidStart, temp, "")
bid = Mid(temp, bidStart + 65, bidEnd - bidStart - 72)
 
askStart = InStr(temp, "Ask:")
askEnd = InStr(askStart, temp, "")
ask = Mid(temp, askStart + 65, askEnd - askStart - 72)
 
openStart = InStr(temp, "Open:")
openEnd = InStr(openStart, temp, "")
ropen = Mid(temp, openStart + 38, openEnd - openStart - 38)
 
closeStart = InStr(temp, "Prev Close:")
closeEnd = InStr(closeStart, temp, "")
rclose = Mid(temp, closeStart + 44, closeEnd - closeStart - 44)
 
If rateType = "ask" Then
    FXRate = ask
ElseIf rateType = "bid" Then
    FXRate = bid
ElseIf rateType = "open" Then
    FXRate = ropen
ElseIf rateType = "close" Then
    FXRate = rclose
End If
 
End Function
 
Function ExecuteWebRequest(ByVal url As String) As String
 
Dim oXHTTP As Object
 
If InStr(1, url, "?", 1) <> 0 Then
    url = url & "&cb=" & Timer() * 100
Else
    url = url & "?cb=" & Timer() * 100
End If
 
Set oXHTTP = CreateObject("MSXML2.XMLHTTP")
oXHTTP.Open "GET", url, False
oXHTTP.send
ExecuteWebRequest = oXHTTP.responseText
Set oXHTTP = Nothing
 
End Function

ExecuteWebRequest() was copied and modified from here. ExecuteWebRequest() retrieves the HTML of a web page, while FXRate() processes the HTML to extract the exchange rates.

Bear in mind that FXRate() scrapes the data from Yahoo Finance with some simple string processing. So, if the structure of the data on Yahoo Finance changes, then this function may not work anymore. Let me know if this happens, and I’ll update the VBA so that it works again.

If you want historical currency conversion rates over a date range, then check out this spreadsheet. You can also use a QueryTable to extract exchange rates.

Leave a comment if you have questions or comments.

Download Excel Spreadsheet with Foreign Exchange UDF (Scrape Yahoo Finance)


27 thoughts on “Foreign Exchange Rate Function in Excel”

  1. Thanks for this handy template. Found it very useful but seems there is problem with Hong Kong dollars conversion.

    Works with:
    =FXRate(G2,”USD”,”close”)

    But not:
    =FXRate(G2,”HKD”,”close”)

    I have checked that the abbreviation for Hong Kong dollars in yahoo finance is HKD.

    Appreciate if you can let me know if to fix that.

    Reply
    • Samson

      Not sure exactly what you’re doing, but the following conversion from USD to HKD works for me (HKD is recognized as an abbreviation)

      =FXRate(“USD”,”HKD”,”close”)

      Samir

      Reply
  2. It looks like this template is no longer working. I used it last week and it worked perfectly (and was a great UDF tool thanks!).

    Any reason yahoo would change its coding?

    Thanks!

    Reply
  3. The function breaks when the locations of the substring searches exceed the 32767 maximum value for the Integer data type. If you change the declarations for bidStart, bidEnd, etc. to Long instead of Integer, the function will work reliably when the length of the Temp variable is extremely large. For a page returned today, the length of the Temp variable was 76104 with the bidStart equalling 33422. The Long declaractions enable the function to again work as expected.

    Reply
  4. Hi Samir,

    Thanks for the code above. I’m having issues with the position of the fx rates in the HTML source.

    I’m trying to retrieve the GBPSGD fx rate.

    url = http://finance.yahoo.com/q?s=GBPSGD=X

    Below are the positions for the “bid” (the first price the code searches for in the html source)

    bidStart=31180
    bidEnd = 31180
    bidStart + 65 = 31245
    bidEnd – bidStart – 72 = -72

    So because the bidStart and BidEnd are the same the Mid function can’t find the bid rate.

    Why do you the bidEnd position by searching for “” ?

    bidEnd = InStr(bidStart, temp, “”)

    Thanks

    Reply
    • Ian

      I’ve just tried using the FXRate() function in the spreadsheet to grab the GBPSGD exchange rate with

      =FXRate(“GBP”,”SGD”,”bid”)

      It appears to work fine – the UDF correctly scrapes the bid price from Yahoo Finance.

      >So because the bidStart and BidEnd are the same the Mid function can’t find the bid rate

      Seems to find the bid rate for me

      Samir

      Reply
  5. Hi Samir, if i’m missing the obvious reason why you set bidEnd’s position in the source using “” can you give me a pointer? 🙂
    Thanks

    Reply
  6. This great template had been working for me until a few weeks back. I did not change anything recently but now it just reports “#VALUE!” in the cell, and mouse over the little error icon says “A value used in the formula is of the wrong data type”. Any suggestions as to how to solve this? thanks

    Reply
  7. Hi,

    Something has changed in the Yahoo structure.
    But I agree that starting with bidStart and ending with closeEnd, they should be Long better.

    Also, this might work better:

    bidStart = InStr(temp, “Bid:”)
    bidEnd = InStr(bidStart, temp, “”)
    bid = Mid(temp, bidStart + 65, bidEnd – bidStart – 72)

    askStart = InStr(temp, “Ask:”)
    askEnd = InStr(askStart, temp, “”)
    ask = Mid(temp, askStart + 65, askEnd – askStart – 72)

    openStart = InStr(temp, “Open:”)
    openEnd = InStr(openStart, temp, “”)
    ropen = Mid(temp, openStart + 38, openEnd – openStart – 38)

    closeStart = InStr(temp, “Prev Close:”)
    closeEnd = InStr(closeStart, temp, “”)
    rclose = Mid(temp, closeStart + 44, closeEnd – closeStart – 44)

    Mindaugas

    Reply
  8. Option Explicit

    Function FXRate(currency1 As String, currency2 As String, rateType As String) As Double

    Dim url As String
    Dim response As String

    url = “http://finance.yahoo.com/q?s=” & currency1 & currency2 & “=X”

    response = ExecuteWebRequest(url)

    If rateType = “ask” Then
    FXRate = GetValue(response, “Ask:”)
    ElseIf rateType = “bid” Then
    FXRate = GetValue(response, “Bid:”)
    ElseIf rateType = “open” Then
    FXRate = GetValue(response, “Open:”)
    ElseIf rateType = “close” Then
    FXRate = GetValue(response, “Prev Close:”)
    End If

    End Function

    Function GetValue(ByVal temp As String, ByVal label As String) As Double
    Dim strStart As Long
    Dim strEnd As Long

    strStart = InStr(temp, “Prev Close:”)
    strStart = InStr(strStart, temp, “””>”)
    strEnd = InStr(strStart, temp, “<")

    GetValue = Mid(temp, strStart + 2, strEnd – strStart – 2)
    End Function

    Function ExecuteWebRequest(ByVal url As String) As String

    Dim oXHTTP As Object

    If InStr(1, url, "?", 1) 0 Then
    url = url & “&cb=” & Timer() * 100
    Else
    url = url & “?cb=” & Timer() * 100
    End If

    Set oXHTTP = CreateObject(“MSXML2.XMLHTTP”)
    oXHTTP.Open “GET”, url, False
    oXHTTP.send
    ExecuteWebRequest = oXHTTP.responseText
    Set oXHTTP = Nothing

    End Function

    Reply
  9. Hi Samir,

    is there a way to get one value only (say “bid”) in a defined cell? I just need the most recent trade to be pushed into a cell.

    Thanks,
    Carlo

    Reply
  10. Hi there,

    I tried to use this conversion, but is no longer working or giving no longer a result value.
    I used before similar function: (see herebelow) , but when I use the below one and convert from IDR to EUR it gives 5 euro conversion instead of 3,5Euro (which I get when I go online to the Yahoo converter side)

    So I looked for a better solution and came on your side, however, no result is given:

    Function YahooCurrencyConverter(ByVal strFromCurrency, ByVal strToCurrency, Optional ByVal strResultType = “Value”)
    On Error GoTo ErrorHandler

    ‘Init
    Dim strURL As String
    Dim objXMLHttp As Object
    Dim strRes As String, dblRes As Double

    Set objXMLHttp = CreateObject(“MSXML2.ServerXMLHTTP”)
    ‘strURL = “http://finance.yahoo.com/d/quotes.csv?e=.csv&f=c4l1&s=” & strFromCurrency & strToCurrency & “=X”

    strURL = “http://finance.yahoo.com/q?s=” & strFromCurrency & strToCurrency & ” = X”

    ‘Send XML request
    With objXMLHttp
    .Open “GET”, strURL, False
    .setRequestHeader “Content-Type”, “application/x-www-form-URLEncoded”
    .send
    strRes = .responseText
    End With

    ‘Parse response
    dblRes = Val(Split(strRes, “,”)(1))

    Select Case strResultType
    Case “Value”: YahooCurrencyConverter = dblRes
    Case Else: YahooCurrencyConverter = “1 ” & strFromCurrency & ” = ” & dblRes & ” ” & strToCurrency
    End Select

    CleanExit:
    Set objXMLHttp = Nothing
    Exit Function

    ErrorHandler:
    YahooCurrencyConverter = 0
    GoTo CleanExit
    End Function

    Reply
  11. Please, can anyone help me?

    I need some excel spreadsheet to show the exchange rates at an specific date.
    Examples: USD to GBP exchange rate in October 10th 2014.
    USD to BRL exchange rate in October 10th 2014.

    I think it could be a function, for example:
    FXRateatDate(“currency1″, “currency2″,”Date”)
    Is this possible?

    The other thing is that it must work with my Brazilian laptop excel, which uses , as decimal separator (International Units System)

    Thanks in advance!

    Bernardo

    Reply
  12. VB works great.
    Along with the OPEN exchange rate (which is the only part I am using), I would like to also extract the exchange date into a cell.
    Can you suggest something?

    Thank you .

    BGG

    Reply
  13. Does this still work? Because it doesn’t appear to be working when i use it?

    I tried the following formula: FXRate(“EUR”,”USD”,”bid”) and it returned “#VALUE”.

    Reply
  14. Dear all, it worked fine till end of december 2016. It seems Yahoo changed something on their site. Could somebody please look into the code and give a solution what needs to be changed, thanks.

    Reply
    • This adjusted code seems to work. I didn’t test it with all possible combos, but it worked for the few I did test!

      Option Explicit

      Function FXRate(currency1 As String, currency2 As String, rateType As String) As Double

      Dim str As String
      Dim temp As String

      Dim bidStart As Long
      Dim bidEnd As Long
      Dim askStart As Long
      Dim askEnd As Long
      Dim openStart As Long
      Dim openEnd As Long
      Dim closeStart As Long
      Dim closeEnd As Long

      Dim bid As Double
      Dim ask As Double
      Dim ropen As Double
      Dim rclose As Double
      Dim i As Long, i1 As Long, i2 As Long
      Dim myArray() As Variant

      str = “http://finance.yahoo.com/q?s=” & currency1 & currency2 & “=X”

      temp = ExecuteWebRequest(str)
      ReDim myArray(1 To Len(temp) / 500)
      For i = 1 To Len(temp) / 500
      i1 = (i – 1) * 500 + 1
      i2 = i1 + 500 – 1
      If (i2 > Len(temp)) Then i2 = Len(temp)
      myArray(i) = Mid(temp, i1, 500)
      Next i
      ‘ActiveSheet.Range(“$A$50”) = myArray

      bidStart = InStr(1, temp, “Bid:”)
      bidStart = InStr(bidStart, temp, “=x””>”) + 4
      bidEnd = InStr(bidStart + 1, temp, “”) + 4
      askEnd = InStr(askStart + 1, temp, “”) + 7
      openEnd = InStr(openStart + 1, temp, “”) + 7
      closeEnd = InStr(closeStart + 1, temp, “<")

      bid = Mid(temp, bidStart, bidEnd – bidStart)
      ask = Mid(temp, askStart, askEnd – askStart)
      ropen = Mid(temp, openStart, openEnd – openStart)
      rclose = Mid(temp, closeStart, closeEnd – closeStart)

      If rateType = "ask" Then
      FXRate = ask
      ElseIf rateType = "bid" Then
      FXRate = bid
      ElseIf rateType = "open" Then
      FXRate = ropen
      ElseIf rateType = "close" Then
      FXRate = rclose
      End If

      End Function

      Function ExecuteWebRequest(ByVal url As String) As String

      Dim oXHTTP As Object

      If InStr(1, url, "?", 1) 0 Then
      url = url & “&cb=” & Timer() * 100
      Else
      url = url & “?cb=” & Timer() * 100
      End If

      Set oXHTTP = CreateObject(“MSXML2.XMLHTTP”)
      oXHTTP.Open “GET”, url, False
      oXHTTP.send
      ExecuteWebRequest = oXHTTP.responseText
      Set oXHTTP = Nothing

      End Function

      Reply
      • Oops – left in some debug code. You can delete this stuff:

        ReDim myArray(1 To Len(temp) / 500)
        For i = 1 To Len(temp) / 500
        i1 = (i – 1) * 500 + 1
        i2 = i1 + 500 – 1
        If (i2 > Len(temp)) Then i2 = Len(temp)
        myArray(i) = Mid(temp, i1, 500)
        Next i
        ‘ActiveSheet.Range(“$A$50”) = myArray

        Reply
  15. Grrr! Moderator – please delete the above comments. The cut and paste did not work correctly and left some code out. Hopefully what is below is complete and correct. Thanks!

    Option Explicit

    Function FXRate(currency1 As String, currency2 As String, rateType As String) As Double

    Dim str As String
    Dim temp As String

    Dim bidStart As Long
    Dim bidEnd As Long
    Dim askStart As Long
    Dim askEnd As Long
    Dim openStart As Long
    Dim openEnd As Long
    Dim closeStart As Long
    Dim closeEnd As Long

    Dim bid As Double
    Dim ask As Double
    Dim ropen As Double
    Dim rclose As Double
    Dim i As Long, i1 As Long, i2 As Long

    temp = ExecuteWebRequest(str)
    ReDim myArray(1 To Len(temp) / 500)
    For i = 1 To Len(temp) / 500
    i1 = (i – 1) * 500 + 1
    i2 = i1 + 500 – 1
    If (i2 > Len(temp)) Then i2 = Len(temp)
    myArray(i) = Mid(temp, i1, 500)
    Next i
    ‘ActiveSheet.Range(“$A$50”) = myArray

    bidStart = InStr(1, temp, “Bid:”)
    bidStart = InStr(bidStart, temp, “=x””>”) + 4
    bidEnd = InStr(bidStart + 1, temp, “”) + 4
    askEnd = InStr(askStart + 1, temp, “”) + 7
    openEnd = InStr(openStart + 1, temp, “”) + 7
    closeEnd = InStr(closeStart + 1, temp, “<")

    bid = Mid(temp, bidStart, bidEnd – bidStart)
    ask = Mid(temp, askStart, askEnd – askStart)
    ropen = Mid(temp, openStart, openEnd – openStart)
    rclose = Mid(temp, closeStart, closeEnd – closeStart)

    If rateType = "ask" Then
    FXRate = ask
    ElseIf rateType = "bid" Then
    FXRate = bid
    ElseIf rateType = "open" Then
    FXRate = ropen
    ElseIf rateType = "close" Then
    FXRate = rclose
    End If

    End Function

    Function ExecuteWebRequest(ByVal url As String) As String

    Dim oXHTTP As Object

    If InStr(1, url, "?", 1) 0 Then
    url = url & “&cb=” & Timer() * 100
    Else
    url = url & “?cb=” & Timer() * 100
    End If

    Set oXHTTP = CreateObject(“MSXML2.XMLHTTP”)
    oXHTTP.Open “GET”, url, False
    oXHTTP.send
    ExecuteWebRequest = oXHTTP.responseText
    Set oXHTTP = Nothing

    End Function

    Reply
  16. Hello,

    Not sure why this macro isnt working anymore.
    Maybe there is a change in the structure of yahoo’s data.
    Would you mind updating it please?

    Cheers,
    Evan

    Reply
  17. Hi Samir,

    I hope you can find the fix soon. your code is awesome and super helpful. I have been using it for almost 4 years now and would love to continue using it.

    Thanks!
    Ian

    Reply

Leave a Comment

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.