Function to Get FX Rates

[neodjandre]

I am using this function to get FX rates predominantly from USD to GBP. However, it is not always accurate to the specified date. Does anyone use something else which could be more accurate please?

Function GetFxRate(CurrencyIn As String, CurrencyOut As String, Dt As Date) As Double
Dim strURL As String
Dim StartAtPos As String
Dim objHTTP
Dim WebResponse
Dim d As Long
Dim DtTxt As String
Dim DtPos As String
Dim ValTxt As String

'https://api.exchangeratesapi.io/history?start_at=2017-12-25&end_at=2018-01-02&symbols=CAD&base=EUR

GetFxRate = 1
'Get 7 days of data as some days might not have data (new year, christmas, etc)
strURL = "https://api.exchangeratesapi.io/history?"
strURL = strURL & "start_at=" & Format(Dt - 7, "yyyy-mm-dd") & "&end_at=" & Format(Dt, "yyyy-mm-dd")
strURL = strURL & "&symbols=" & CurrencyIn & "&base=" & CurrencyOut

'Debug.Print strURL  'The Url that is being called

Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")
objHTTP.Open "GET", strURL, False
objHTTP.send
If objHTTP.Status = "200" Then
    objHTTP.WaitForResponse
    WebResponse = objHTTP.responseText
    'e.g. "{"rates":{"2018-12-27":{"EUR":0.6451612903},"2018-12-31":{"EUR":0.6408202499},"2018-12-28":{"EUR":0.6409434688}},"start_at":"2018-12-25","base":"CAD","end_at":"2019-01-01"}"
    StartAtPos = InStr(WebResponse, "start_at")
    For d = Dt To Dt - 7 Step -1
        DtTxt = Format(d, "yyyy-mm-dd")
        DtPos = InStr(WebResponse, DtTxt)
        If DtPos > 0 And DtPos < StartAtPos Then
            ValTxt = Mid(WebResponse, 1 + InStr(DtPos, WebResponse, "{"), InStr(DtPos, WebResponse, "}") - InStr(DtPos, WebResponse, "{") - 1)
            GetFxRate = Val(Right(ValTxt, Len(ValTxt) - InStr(ValTxt, ":")))
            Exit For
        End If
    Next d
End If
Set objHTTP = Nothing

End Function

many thanks,
Andrew

 

[Rijnsent]

Hi neodjandre,
that code looks familiar . What is the main issue with the data, is it missing completely or are their numbers wrong? You could get e.g. a https://fixer.io/ account (free) and get your historical data there. Or use the source I suggested, like: https://min-api.cryptocompare.com/data/histoday?fsym=GBP&tsym=USD (also with that source: get a free API key).
Cheers,
Koen

 

[Rijnsent]

Hi neodjandre,
one addition, you could also pull in the data from a long period back (1999) with e.g. PowerQuery. Go for Data->Get Data->PowerQuery editor->Advanced editor and copy-paste this code, it pulls in the data from a chart from ukfx.co.uk.
Cheers,
Koen

let
    Src = Json.Document(Web.Contents("https://api.ukfx.co.uk/pairs/gbp/usd/candle?t=10000")),
    #"ConvertToTable" = Table.FromList(Src, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"ExtractedValues" = Table.TransformColumns(#"ConvertToTable", {"Column1", each Combiner.CombineTextByDelimiter("=")(List.Transform(_, Text.From)), type text}),
    #"ColSplitBy" = Table.SplitColumn(#"ExtractedValues", "Column1", Splitter.SplitTextByDelimiter("=", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5"}),
    #"TypeChange" = Table.TransformColumnTypes(#"ColSplitBy",{{"Column1.1", Int64.Type}, {"Column1.2", type number}, {"Column1.3", type number}, {"Column1.4", type number}, {"Column1.5", type number}}),
    #"AddCalculatedColumn" = Table.AddColumn(TypeChange, "ExcelTime", each #datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [Column1.1]/1000)),
    #"TypeChange2" = Table.TransformColumnTypes(#"AddCalculatedColumn",{{"ExcelTime", type datetime}})
in
    #"TypeChange2"