# 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. • 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.

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.

• 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

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!

• Just tried the spreadsheet and it’s still works

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.

• Thank! I’ve changed the datatype to Long in the VBA and spreadsheet. Much appreciated!

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.

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

• 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

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

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

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)

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

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

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

10. This doesn’t work for me. Either the original code or the update from anonymous on Jan 22, 2014

11. 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
.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

12. 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)

Bernardo

13. 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

14. 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”.

15. 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.

• 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)
ropen = Mid(temp, openStart, openEnd – openStart)
rclose = Mid(temp, closeStart, closeEnd – closeStart)

If rateType = "ask" Then
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

• 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

16. 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)
ropen = Mid(temp, openStart, openEnd – openStart)
rclose = Mid(temp, closeStart, closeEnd – closeStart)

If rateType = "ask" Then
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

17. I just tried the new code but it stops with a compile error at line:

i1 = (i – 1) * 500 + 1

18. 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

• sure, it’s on my to do list