ExecuteWebRequest() retrieves the HTML of a web page, while FXRate() processes the HTML to extract the exchange rates.īear in mind that FXRate() scrapes the data from Yahoo Finance with some simple string processing. Rclose = Mid(temp, closeStart + 44, closeEnd - closeStart - 44) If rateType = "ask" ThenĮnd If End Function Function ExecuteWebRequest(ByVal url As String) As String Dim oXHTTP As Object If InStr(1, url, "?", 1) 0 ThenĮnd If Set oXHTTP = CreateObject("MSXML2.XMLHTTP")ĮxecuteWebRequest() was copied and modified from here. Ropen = Mid(temp, openStart + 38, openEnd - openStart - 38) closeStart = InStr(temp, "Prev Close:") Option Explicit Function FXRate(currency1 As String, currency2 As String, rateType As String) As Double Dim str As Stringĭim rclose As Double str = "" & currency1 & currency2 & "=X" temp = ExecuteWebRequest(str) bidStart = InStr(temp, "Bid:")īid = Mid(temp, bidStart + 65, bidEnd - bidStart - 72) askStart = InStr(temp, "Ask:")Īsk = Mid(temp, askStart + 65, askEnd - askStart - 72) openStart = InStr(temp, "Open:") Rclose = Mid(temp, closeStart + 44, closeEnd - closeStart - 44)Įnd If End Function Function ExecuteWebRequest( ByVal url As String) As String Dim oXHTTP As Object If InStr(1, url, "?", 1) 0 ThenĮnd If Set oXHTTP = CreateObject( "MSXML2.XMLHTTP") Ropen = Mid(temp, openStart + 38, openEnd - openStart - 38) 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īid = Mid(temp, bidStart + 65, bidEnd - bidStart - 72)Īsk = Mid(temp, askStart + 65, askEnd - askStart - 72) The link to the Excel spreadsheet is at the bottom of this post, but here’s the VBA.
GBP for British Pounds, or CAD for Canadian Dollars) entered as strings. These are three-letter currency codes (e.g. The first two arguments are the currency you’re converting from and the currency you’re converting to.
#LIVE CURRENCY CONVERTER EXCEL HOW TO#
This is an example of how to use FXRate() in Excel for a currency conversion between GBP and USD. Here’s the syntax.įXRate(“currency1”, “currency2”,) The rates are scraped from Yahoo Finance, and are real-time (but delayed by 15 or 20 minutes). I use it to keep track of the British Pound value of a US and Canadian Dollar bank account. The Excel function is perfect for whenever you need a currency conversion.
#LIVE CURRENCY CONVERTER EXCEL FREE#
Read on for the free Excel spreadsheet and VBA.
This Excel spreadsheet has a VBA function (UDF) that scrapes live foreign exchange rates from Yahoo Finance.
0 kommentar(er)
