r - Exact time stamp on quantmod currency (FX) data

Question

we may collect daily data from Oanda and Yahoo finance with the quantmod package as such:

getFX("USD/JPY",from="2007-01-01", to = Sys.Date())
getSymbols("EUR=X",src="yahoo",from="2002-01-01",auto.assign=F)

After checking data carefully, you may notice that values from these sources are significantly different. I then wonder what exactly was the time taken the time stamp of the day for each source? It does not look to be at midnight GMT.

I would appreciate if you have a clue. Thank you.

Answer 1

FX is an OTC market, so there is no central exchange which sets the rate at any time, but arbitrage considerations means all the major players (banks, hedge funds, real money) are pricing at approximately the same rates at any given time during liquid market hours (which is 5pm Sunday EST (America/New_York) to 5pm Friday EST approximately). Therefore different sources can offer slightly different pricing even for the same timestamps. With this in mind, here are some recent Oanda and Yahoo rates for EUR/USD:

library(quantmod)
getFX("EUR/USD",from="2007-01-01", to = Sys.Date())
ya2 <- getSymbols("EUR=X",src="yahoo",from="2002-01-01",auto.assign=F)

# Yahoo reports the unconventional pricing of USDEUR = 1 / EURUSD, so lets get in the conventional form EUR/USD:
ya2[, c(1, 4, 6)] <- 1 / coredata(ya2)[, c(1, 4, 6)]
ya2[, c(2, 3)] <- 1 / coredata(ya2)[, c(3, 2)]

tail(ya2, 5)
# > tail(ya2, 5)
# EUR=X.Open EUR=X.High EUR=X.Low EUR=X.Close EUR=X.Volume EUR=X.Adjusted
# 2016-12-20   1.040474   1.041992  1.035518    1.040583            0       1.040583
# 2016-12-21   1.039393   1.045151  1.038529    1.039047            0       1.039047
# 2016-12-22   1.042753   1.049759  1.042753    1.042862            0       1.042862
# 2016-12-23   1.043950   1.046792  1.042970    1.043765            0       1.043765
# 2016-12-26   1.045588   1.047011  1.044600    1.045478            0       1.045478
colnames(EURUSD) <- "Oanda"
compare <- merge(ya2, EURUSD)
indexFormat(compare) <- "%Y-%m-%d, %a"

tail(round(compare, 4), 15)
# EUR.X.Open EUR.X.High EUR.X.Low EUR.X.Close EUR.X.Volume EUR.X.Adjusted  Oanda
# 2016-12-13, Tue     1.0643     1.0653    1.0607      1.0642            0         1.0642 1.0629
# 2016-12-14, Wed     1.0630     1.0667    1.0615      1.0629            0         1.0629 1.0632
# 2016-12-15, Thu     1.0515     1.0525    1.0404      1.0514            0         1.0514 1.0468
# 2016-12-16, Fri     1.0418     1.0472    1.0404      1.0419            0         1.0419 1.0435
# 2016-12-17, Sat         NA         NA        NA          NA           NA             NA 1.0451
# 2016-12-18, Sun         NA         NA        NA          NA           NA             NA 1.0451
# 2016-12-19, Mon     1.0448     1.0482    1.0413      1.0450            0         1.0450 1.0446
# 2016-12-20, Tue     1.0405     1.0420    1.0355      1.0406            0         1.0406 1.0391
# 2016-12-21, Wed     1.0394     1.0452    1.0385      1.0390            0         1.0390 1.0413
# 2016-12-22, Thu     1.0428     1.0498    1.0428      1.0429            0         1.0429 1.0443
# 2016-12-23, Fri     1.0440     1.0468    1.0430      1.0438            0         1.0438 1.0445
# 2016-12-24, Sat         NA         NA        NA          NA           NA             NA 1.0455
# 2016-12-25, Sun         NA         NA        NA          NA           NA             NA 1.0455
# 2016-12-26, Mon     1.0456     1.0470    1.0446      1.0455            0         1.0455 1.0455
# 2016-12-27, Tue         NA         NA        NA          NA           NA             NA 1.0449

Yahoo data:

• First, we see yahoo returns OHLC data. I can tell you that the close price offered by yahoo (EUR.X.Close) corresponds to around midnight UTC. I've checked this against other reliable (proprietary) FX tick data pricing sources.

• Also, you can clearly see that the Open price (EUR.X.Open) is different from the previous close price one bar back so we can conclude the open price is set at some arbitrary time window during the 24 hour period ending midnight UTC on any given trading day (the high and low would also be set over this period). This is just Yahoo's convention for bar creation, and is not "right" or "wrong", just how they choose to disseminate data.

• In reality FX trades 24/5 so the yahoo data has gaps in it.

Oanda data:

• Notice Oanda returns prices for every day, including weekends and public holidays, and only one column of values. Oanda is returning a weighted average price for each day in the time series. (It isn't returning Close data at 5pm EST, or midnight UTC). Why would this be useful then? Well, because many people want to use daily FX data rates to value business transactions in different currencies etc, and Oanda is considered a trusted name with reliable pricing data (https://www.oanda.com/fx-for-business/)

Liquidity is very poor at 5pm EST each day as this is when rollover interest is paid in the FX market, therefore this time is often used as the end of an FX trading day. A reasonable way to create daily FX data is assume weekday one starts at 5pm Sunday EST and ends 5pm Monday EST, weekday two starts at 5pm Monday EST and ends at Tuesday 5pm EST, etc. This gives 5 even 24-hour trading bars each week.

On a related topic, clearly neither of the above sources are usable for backtesting interday FX strategies. If you happen to be looking for free FX data at daily or higher frequencies, some options include:

1. Free aggregated FX tick data from http://www.truefx.com/?page=downloads
2. Set up a paper trading account with Interactive Brokers. You can get up to a year of rolling daily OHLC history, and data at shorter time frames of 5, 30 sec, 1 min bars (at least last time I checked, which was a while ago).
3. DIY: Open an account with a reputable broker and start storing streaming ticks yourself