Merging Multiple XTS Objects into a single XTS object

Question

I would like to merge several xts objects into a single xts object. So that I can get a correlation matrix between the objects on close price.

The Code below pulls down the forex data

require(xts)

symbols <- c("AUDJPY", "AUDUSD", "CHFJPY", "EURCHF", "EURGBP", "EURJPY",
             "EURUSD", "GBPCHF", "GBPJPY", "GBPUSD", "NZDUSD", "USDCAD",
             "USDCHF", "USDJPY", "XAGUSD", "XAUUSD")

fxhistoricaldata <- function(Symbol, timeframe, download = FALSE, bandwidth) {   

  # setup temp folder
  temp.folder <- tempdir()
  filename <- paste(temp.folder, '/',"fxhistoricaldata_",Symbol ,"_" ,timeframe,".csv", sep='')

  if(download) {
    downloadfile <- paste("http://api.fxhistoricaldata.com/v1/indicators?instruments=" ,Symbol ,"&expression=open,high,low,close&item_count=10000&format=csv&timeframe=" ,timeframe,sep='')
    download.file(downloadfile, filename,  mode = 'wb')
  }

  tempdf <- read.csv(filename)
  colnames(tempdf) <- c("Curr","Date","Open","High","Low","Close")
  tempdf <- tempdf[c("Date","Open","High","Low","Close")]
  result <- xts(tempdf[,-1], order.by=as.POSIXct(tempdf[,1]))

  return(result)
}

AUDJPY <- fxhistoricaldata('AUDJPY' ,'day',download=T,5)
AUDUSD <- fxhistoricaldata('AUDUSD' ,'day',download=T,5)
CHFJPY <- fxhistoricaldata('CHFJPY' ,'day',download=T,5)
EURCHF <- fxhistoricaldata('EURCHF' ,'day',download=T,5)
EURGBP <- fxhistoricaldata('EURGBP' ,'day',download=T,5)
EURJPY <- fxhistoricaldata('EURJPY' ,'day',download=T,5)
EURUSD <- fxhistoricaldata('EURUSD' ,'day',download=T,5)
GBPCHF <- fxhistoricaldata('GBPCHF' ,'day',download=T,5)
GBPJPY <- fxhistoricaldata('GBPJPY' ,'day',download=T,5)
GBPUSD <- fxhistoricaldata('GBPUSD' ,'day',download=T,5)
NZDUSD <- fxhistoricaldata('NZDUSD' ,'day',download=T,5)
USDCAD <- fxhistoricaldata('USDCAD' ,'day',download=T,5)
USDCHF <- fxhistoricaldata('USDCHF' ,'day',download=T,5)
USDJPY <- fxhistoricaldata('USDJPY' ,'day',download=T,5)
XAGUSD <- fxhistoricaldata('XAGUSD' ,'day',download=T,5)
XAUUSD <- fxhistoricaldata('XAUUSD' ,'day',download=T,5)

My desired results will look something like this with the close price below the symbol name but for all the symbol names

          AUDJPY    AUDUSD  
2016-01-01   1.200     1.300
2016-01-02   1.21      1.31

Example of one of the xts objects

Please provide a [reproducible example](http://stackoverflow.com/q/5963269/271616). Pictures of data are not helpful. — Joshua Ulrich, Jan 23 '17 at 22:28

score 1 · Answer 1 · answered Jan 24 '17 at 04:32

Consider building a list of xts objects, prefixing column names with corresponding symbol, then running merge.xts:

xtsList <- lapply(symbols, function(s) {
    df <- get(s)
    colnames(df) <- paste0(s, "_", colnames(df))
    return(df)
})

masterxts <- do.call(merge, xtsList)

score 1 · Accepted Answer · answered Jan 24 '17 at 12:32

This can be easily done by using the quantmod add-on qmao. Take for instance several ticker symbols, download data (or import data from file ) as xts-objects and then create one object with prices or returns in one go. Let’s assume you want to create a correlation matrix from the daily returns:

library(qmao)
tickers <- c('MSFT','AAPL','AMZN')
getsSymbols(tickers, from = '2010-01-01')
# xts-object of daily returns (RF stands for `Return Frame`,PF returns a
# Price Frame)
returns <- RF(tickers, silent = TRUE, type = 'discrete')

> tail(returns)
                    MSFT         AAPL         AMZN
2017-01-10 -0.0003192848  0.001008513 -0.001279876
2017-01-11  0.0091025233  0.005373176  0.003920085
2017-01-12 -0.0091786360 -0.004175365  0.018297408
2017-01-13  0.0014374700 -0.001760998  0.004301657
2017-01-17 -0.0027113556  0.008064508 -0.009080505
2017-01-18 -0.0004797537 -0.000083350 -0.002766377

To get the correlation matrix:

> cor(returns,use = 'pairwise.complete.obs')
          MSFT      AAPL      AMZN
MSFT 1.0000000 0.4655617 0.4701170
AAPL 0.4655617 1.0000000 0.4390303
AMZN 0.4701170 0.4390303 1.0000000

Have a look at the functions PF and RF they are compact and incredibly useful.

score 0 · Answer 3 · answered Jan 24 '17 at 09:30

Found a better solution using getSymbols. Thanks for all the help.

require(lubridate)
require(quantmod)


symbols <- c("AUD/JPY",
             "AUD/USD",
             "CHF/JPY",
             "EUR/CHF",
             "EUR/GBP",
             "EUR/JPY",
             "EUR/USD",
             "GBP/CHF",  
             "GBP/JPY",
             "GBP/USD",
             "NZD/USD",
             "USD/CAD",
             "USD/CHF",
             "USD/JPY",
             "XAG/USD",
             "XAU/USD"
)

fixedsymbols <- lapply(symbols, function(x) {
                   gsub("/", "", x)
              })

getSymbols(symbols,src="oanda", from="2014-08-05", to="2017-01-17")

xtsList <- lapply(fixedsymbols, function(s) {
  df <- get(s)
  colnames(df) <- colnames(df)
  return(df)
})

masterxts <- do.call(merge, xtsList)

Merging Multiple XTS Objects into a single XTS object

3 Answers3