How to fetch 3-years historical price serie from Oanda with R?

Question

I would like to process Bitcoin price in R but I'm unable to download time serie from Yahoo and Google.

From Yahoo the BTCUSD historical time serie is missing and the Google doesn't recognize the URL formated by getSymbols when symbol is "CURRENCY:EURUSD". I know R expect the ":" to be a list so I applied a workaround I found in Stakeoverflow to turn CURRENCY:EURUSD in CURRENCY.EURUSD but still Google cannot process the request.

Download from Oanda works like a charm but request cannot exceed 500 days. I try this workaround to bypass the limitation but it fails to populate correctly the prices object in which I have others symbols :

for some reason BTCUSD prices are missing for 2012 and part of 2013
also there are symbols from symbols's list that get NA with the wo.

tail(prices) (with the loop bellow)

             UUP    FXB    FXE    FXF   FXY   SLV    GLD     BTC
2014-08-31    NA     NA     NA     NA    NA    NA     NA 506.809
2014-09-30 22.87 159.33 124.48 102.26 88.80 16.35 116.21 375.386
2014-10-31 23.09 157.20 123.49 101.45 86.65 15.50 112.66 341.852
2014-11-30    NA     NA     NA     NA    NA    NA     NA 378.690
2014-12-31 23.97 153.06 119.14  98.16 81.21 15.06 113.58 312.642
2015-01-24    NA     NA     NA     NA    NA    NA     NA 229.813

Extract of print(prices) (with the loop bellow)

2013-06-28 22.56 150.17 128.93 103.92 98.63 18.97 119.11      NA
2013-07-31 22.09 150.12 131.74 105.99 99.93 19.14 127.96      NA
2013-08-30 22.19 152.93 130.84 105.45 99.63 22.60 134.62      NA
2013-09-30 21.63 159.70 133.85 108.44 99.47 20.90 128.18 133.794
2013-10-31 21.63 158.10 134.29 108.03 99.38 21.10 127.74 203.849
2013-11-30    NA     NA     NA     NA    NA    NA     NA 1084.800
2013-12-31 21.52 163.30 135.99 109.82 92.76 18.71 116.12 758.526
2014-01-31 21.83 161.95 133.29 108.00 95.58 18.45 120.09 812.097

tail(prices) (without the loop bellow)

             UUP    FXB    FXE    FXF   FXY   SLV    GLD
2014-08-29 22.02 163.23 129.54 106.42 93.61 18.71 123.86
2014-09-30 22.87 159.33 124.48 102.26 88.80 16.35 116.21
2014-10-31 23.09 157.20 123.49 101.45 86.65 15.50 112.66
2014-11-28 23.47 153.46 122.46 101.00 82.01 14.83 112.11
2014-12-31 23.97 153.06 119.14  98.16 81.21 15.06 113.58
2015-01-23 25.21 147.23 110.33 110.95 82.57 17.51 124.23

What is wrong with this code ? Tx !

require(quantmod)
require(PerformanceAnalytics)

symbols <- c(
  "UUP",
  "FXB",
  "FXE",
  "FXF",
  "FXY",
  "SLV",
  "GLD"
)

getSymbols(symbols, from="2004-01-01")

prices <- list()
for(i in 1:length(symbols)) {
  prices[[i]] <- Cl(get(symbols[i]))  
}

BTC <- list()
for(i in 1:2) {
  BTC[[1]] <- getFX("BTC/USD",
                       from = Sys.Date() -499 * (i + 1),
                       to = Sys.Date() - 499 * i,
                       env = parent.frame(),
                       auto.assign = FALSE)
}
BTC[[1]] <- getFX("BTC/USD",
                  from = Sys.Date() -499,
                  to = Sys.Date(),
                  env = parent.frame(),
                  auto.assign = FALSE)

prices[[length(symbols)+1]] <- BTC[[1]]
prices <- do.call(cbind, prices)

colnames(prices) <- gsub("\\.[A-z]*", "", colnames(prices))
ep <- endpoints(prices, "months")

prices <- prices[ep,]
prices <- prices["1997-03::"]

Is it possible there simply are no prices for those dates? Can you actually see the data on the webpage and confirm it is there? — kristang, Jan 24 '15 at 12:32
Did you mean for it to be `BTC[[i]] <-` instead of `BTC[[1]] <-`? — GSee, Jan 24 '15 at 14:01
I think I get it. Symbols from symbols's list have N/A because the corresponding date point to a Saturday or a Sunday, and I suppose Oanda API doesn't provide data before the Sept-2013. — Florent, Jan 24 '15 at 17:52

GSee · Accepted Answer · 2015-01-24T18:54:18.487

6

Your for loop isn't using i, and then after the for loop you're overwriting the results (the list was of length 1 because BTC[[1]] was hardcoded)

Try this

btc <- do.call(rbind, lapply(0:2, function(i) {
  getFX("BTC/USD", 
        from = Sys.Date() -499 * (i + 1),
        to = Sys.Date() - 499 * i,
        env=NULL)
}))

prices <- do.call(cbind, c(prices, list(btc)))

Edit: Here's a more complete example

library(quantmod)
# Use tryCatch() in case we try to get data too far in the past that 
# Oanda doesn't provide. Return NULL if there is an error, and Filter
# to only include data that has at least 1 row.
btc <- do.call(rbind, Filter(NROW, lapply(0:5, function(i) {
  tryCatch(getFX("BTC/USD", 
                 from = Sys.Date() -499 * (i + 1),
                 to = Sys.Date() - 499 * i,
                 env=NULL), error=function(e) NULL)
})))

symbols <- c(
  "UUP",
  "FXB",
  "FXE",
  "FXF",
  "FXY",
  "SLV",
  "GLD"
)
e <- new.env()
getSymbols(symbols, from=start(btc), env=e)
prices <- do.call(cbind, c(eapply(e, Cl)[symbols], list(btc)))
colnames(prices) <- gsub("\\.[A-z]*", "", colnames(prices))
head(na.locf(prices)[endpoints(prices, "months")])
#             UUP    FXB    FXE    FXF    FXY   SLV    GLD     BTC
#2010-07-31 23.74 156.15 129.88  95.38 114.60 17.58 115.49 0.06386
#2010-08-31 24.12 152.60 126.25  97.80 117.83 18.93 122.08 0.06441
#2010-09-30 22.84 156.33 135.81 101.00 118.57 21.31 127.91 0.06194
#2010-10-31 22.37 159.45 138.69 100.81 122.93 24.17 132.62 0.18530
#2010-11-30 23.50 154.72 129.30  98.87 118.16 27.44 135.42 0.27380
#2010-12-31 22.71 155.77 133.09 106.25 121.75 30.18 138.72 0.29190

edited Jan 24 '15 at 18:54

answered Jan 24 '15 at 18:25

GSee

48,880
13
125
145

Thank you very much GSee. It works like a charm but still it happens that endpoints(prices, "months")] returns a Saturday or a Sunday with value for BTC but N/A for all others currencies. For example the 2014-11-30 and 2014-11-30. Do you think there is possibility to exclude these weekend days ? – Florent Jan 30 '15 at 23:52
I used `na.locf` in my Answer to fill in NAs with the previous value. If you want to remove all rows that have NAs, you could use `na.omit`, or you could remove the rows that are weekends like this `prices[!timeDate::isWeekend(index(prices))]` or `prices[!weekdays(index(prices)) %in% c("Saturday", "Sunday")]` or `prices[!as.POSIXlt(index(prices))$wday %in% c(0, 6)]` – GSee Jan 31 '15 at 00:16
Hi GSee, for some reason the complete code above returns an error when downloading the data, I have no idea what has change with Oanda. `1: In download.file(paste(oanda.URL, from.date, to.date, "exch=", currency.pair[1], : ouverture impossible : le statut HTTP était '404 Not Found` – Florent Feb 07 '15 at 13:35
@user3423825 I can confirm that I am also currently getting a 404 Not Found error whenever I call `getFX` with any pair (e.g. `getFX("USD/JPY")` gives the same error). Looks like they may have changed their API. – GSee Feb 07 '15 at 18:18
@user3423825 see [here](http://stackoverflow.com/questions/28411713/can-getsymbols-still-work-with-oanda) – GSee Feb 09 '15 at 15:04

How to fetch 3-years historical price serie from Oanda with R?

1 Answers1