Rblpapi: Using BDH to pull historical data on multiple securities with only one date column

Question

I am trying to pull historical data for several securities over a single date range. I am able to get the data I want using the code below but the resulting list produces a date column for every security. I would like to see the date in the first, left-most column and then the security field data (px_last, etc) for every security in my list in the columns to the right. I know in BBG with excel I can use an override (dts = H) to hide the date field but I want the left-most column in my list to populate the date,

sec <- c("SPX Index","SX5E Index")
lb <- 5
startdate <- Sys.Date() - lb
opt <- c("periodicitySelection"="DAILY","nonTradingDayFillOption"="NON_TRADING_WEEKDAYS","nonTradingDayFillMethod"="PREVIOUS_VALUE")
dat <- bdh(sec,fields="px_last",start.date = startdate,options=opt)

Returns:

$`SPX Index`
        date px_last
1 2016-12-19 2262.53
2 2016-12-20 2270.76
3 2016-12-21 2265.18
4 2016-12-22 2261.03

$`SX5E Index`
        date px_last
1 2016-12-19 3257.85
2 2016-12-20 3279.41
3 2016-12-21 3270.75
4 2016-12-22 3262.11

I would like to see three columns instead of four. Col1 is date, Col2 is SPX Index px_last, Col3 is SX5E Index px_last. Thank you

Answer 1

You just merge by hand afterwards.

Start by redoing your code:

 R> library(Rblpapi)
 Rblpapi version 0.3.5 using Blpapi headers 3.8.8.1 and run-time 3.8.8.1.
 Please respect the Bloomberg licensing agreement and terms of service.
 R> library(xts)
 Loading required package: zoo 

 Attaching package: ‘zoo’ 

 The following objects are masked from ‘package:base’:

     as.Date, as.Date.numeric

 R> sec <- c("SPX Index","SX5E Index") 
 R> lb <- 5     
 R> startdate <- Sys.Date() - lb  
 R> opt <- c("periodicitySelection"="DAILY",  
 +          "nonTradingDayFillOption"="NON_TRADING_WEEKDAYS",  
 +          "nonTradingDayFillMethod"="PREVIOUS_VALUE")  
 R> dat <- bdh(sec,fields="px_last",start.date = startdate,options=opt)
 R> str(dat)          
 List of 2  
  $ SPX Index :'data.frame':     5 obs. of  2 variables:
   ..$ date   : Date[1:4], format: "2016-12-26" "2016-12-27" "2016-12-28"  ...
   ..$ px_last: num [1:5] 2264 2269 2250 2249 2239
  $ SX5E Index:'data.frame':     5 obs. of  2 variables:   
   ..$ date   : Date[1:4], format: "2016-12-26" "2016-12-27" "2016-12-28" ...
   ..$ px_last: num [1:5] 3274 3279 3279 3272 3291
 R>

We see that we have a list of data.frame types. We will make that a list of xts object (which is why we loaded that package above), then merge the list elements and set column names:

 R> datxts <- lapply(dat, function(d) xts(d[,-1], order.by=as.Date(d[,1])))
 R> res <- do.call(merge, datxts)  
 R> colnames(res) <- sec   
 R> res   
            SPX Index SX5E Index 
 2016-12-26   2263.79    3273.97  
 2016-12-27   2268.88    3278.53
 2016-12-28   2249.92    3278.72
 2016-12-29   2249.26    3271.76
 2016-12-30   2238.83    3290.52 
 R>