Bloomberg data pull issue in R using bplpapi, data not populating and start date returned is incorrect

For all you Bloomberg and R users out there:

I usually have no problem pulling Bloomberg data into R via the Rblpapi package, but have run across an issue when trying to pull index-level data.

The problem is the code below returns erroneous results as it begins pulling data starting in 1986 (not 1950) and it leaves many values NA that should be populated. Using the excel API, the data pulls in fine, but I need to add "days = a" for some of the fields since they don't begin until after 1950.

Reproducible example (assuming you have Bloomberg access):

    # Load packages  ----------------------------------------------------------
library("Rblpapi")
library("tidyverse")
library("lubridate")

# Connect to Bloomberg --------------------------------------------------
blpConnect()


# Pull equity index-level specific data over time for S&P 500, S&P Mid Cap (400) and S&P Small Cap (600) indices  ---------------------- 

# Index tickers
tickers <- c("SPX Index", "MID Index", "SML Index")


# Bloomberg inputs 
myField <- c("PX_LAST", "TRAIL_12M_EPS", "TRAIL_12M_DILUTED_EPS", "BEST_EPS", "PE_RATIO", "BEST_PE_RATIO", 
             "TRAIL_12M_EBITDA_PER_SHARE",  "PX_TO_EBITDA", "PX_TO_BOOK_RATIO", "PX_TO_SALES_RATIO",
             "PX_TO_FREE_CASH_FLOW", "EQY_DVD_YLD_12M", "TOT_DEBT_TO_EBITDA", "EV_TO_T12M_SALES", "EV_TO_T12M_EBITDA",
             "TRAIL_12M_GROSS_MARGIN", "EBITDA_MARGIN", "TRAIL_12M_OPER_MARGIN", "TRAIL_12M_PROF_MARGIN",
             "RETURN_ON_ASSET", "RETURN_COM_EQY",  "RETURN_ON_CAP", "NET_DEBT_TO_EBITDA", "CUR_MKT_CAP", "AVERAGE_MARKET_CAP"
             )

# Pull data
sp_indices_fundmtls_raw <- as.data.frame(bdh(tickers,
                                             myField,
                                             start.date = as.Date("1950-01-01"),
                                             end.date   = Sys.Date(),
                                             include.non.trading.days = TRUE
                                             )
                                         )

Since this didn't work, I tried just pulling the data using SPX Index only. Same issue. I then tried the formula with fewer tickers

# Bloomberg inputs 
myField <- c("PX_LAST", "TRAIL_12M_EPS", "TRAIL_12M_DILUTED_EPS", "BEST_EPS", "PE_RATIO", "BEST_PE_RATIO", 
             "TRAIL_12M_EBITDA_PER_SHARE",  "PX_TO_EBITDA", "PX_TO_BOOK_RATIO", "PX_TO_SALES_RATIO",
             "PX_TO_FREE_CASH_FLOW", "EQY_DVD_YLD_12M",
             "TOT_DEBT_TO_EBITDA", "EV_TO_T12M_SALES", "EV_TO_T12M_EBITDA"
             )

That worked better, but still started in 1964 not 1950. Again, excel API works fine and will just return NA if data is missing earlier as I expected R to do.

This makes me think that there must be a field that needs an option or an override to pull the data correctly. I tried adding

ovrd <- c("PERIODICITY_OVERRIDE" = "D")


# Pull data
sp_indices_fundmtls_raw <- as.data.frame(bdh(tickers,
                                             myField,
                                             start.date = as.Date("1950-01-01"),
                                             end.date   = Sys.Date(),
                                             include.non.trading.days = TRUE,
                                             overrides = ovrd

                                             )
                                         )

But no luck.

Can anyone figure out the issue?

Thanks!

Solution

After much trial and error, I figured out a way to get the data.

I created a function to pull the data:

      # Function to pull data
sp_indices_pull_fx <- function(myField, index_ticker) {
  df <- as.data.frame(bdh(index_ticker,
                          myField,
                          start.date = as.Date("1950-01-01"),
                          end.date   = Sys.Date(),
                          include.non.trading.days = TRUE
                          )
                      )

Then I used lapply to cycle through each ticker. For example:

    # SP500
sp_500_pull      <- lapply(myField, sp_indices_pull_fx, index_ticker = "SPX Index")

Then I combined those results into a single data frame:

  # Merge
sp_500_fundmtls_raw      = Reduce(function(...) merge(..., all = TRUE), sp_500_pull)

So in short, what worked was creating a function and feeding that function each individual ticker as opposed to trying to pull multiple tickers at once using the bdh function.