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.

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”

Samson
January 15, 2013 at 9:50 am
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
- Samir
  January 17, 2013 at 4:01 pm
  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
Daniel
February 13, 2013 at 4:40 pm
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
- Samir
  February 14, 2013 at 2:00 pm
  Just tried the spreadsheet and it’s still works
  Reply
Kelley
August 22, 2013 at 3:29 pm
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
- Samir Khan
  August 24, 2013 at 7:22 pm
  Thank! I’ve changed the datatype to Long in the VBA and spreadsheet. Much appreciated!
  Reply
Hammer
September 25, 2013 at 2:08 pm
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
- Samir Khan
  October 3, 2013 at 12:26 am
  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
Hammer
September 30, 2013 at 7:37 am
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
Samson
November 12, 2013 at 2:29 am
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
Mindaugas
November 26, 2013 at 12:53 pm
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
Anonymous
January 22, 2014 at 11:40 am
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
Carlo
December 1, 2014 at 4:40 pm
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
Peter
December 30, 2014 at 8:36 pm
This doesn’t work for me. Either the original code or the update from anonymous on Jan 22, 2014
Reply
marc
March 27, 2015 at 8:17 am
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
Bernardo
April 22, 2015 at 6:44 pm
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
Brent
July 29, 2015 at 6:17 pm
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
Vincent
December 30, 2015 at 3:50 pm
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
Sikadur
January 18, 2017 at 7:18 am
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
- Eric
  February 14, 2017 at 5:18 pm
  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
  - Eric
    February 14, 2017 at 5:19 pm
    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
Eric
February 14, 2017 at 5:23 pm
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
Alex
April 21, 2017 at 2:45 pm
I just tried the new code but it stops with a compile error at line:
i1 = (i – 1) * 500 + 1
Reply
Evan
April 24, 2017 at 2:59 pm
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
- Samir Khan
  April 27, 2017 at 1:53 am
  sure, it’s on my to do list
  Reply
Ian
May 5, 2017 at 6:48 pm
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
Aviv
August 20, 2017 at 6:14 am
Hi Samir,
Your code is great, when do you think you will fix it?
Thanks
Reply

27 thoughts on “Foreign Exchange Rate Function in Excel”

Leave a Comment Cancel reply