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
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>