[R-sig-finance] Import from Bloomberg

[Enrique Bengoechea]

Jordi, I'm downloading data from Bloomberg to R sessions every day. It is relatively easy using the Bloomberg ActiveX control and the R(D)Com client package (instead of the lower-level C APIs). I don't know whether something like this is included in
rMetrics. Maybe it would be useful to add it or to bundle it properly and release a specific package (if someone is interested please tell me, when I searched for it some time ago I found nothing). Here's how (to be run only from a PC with Bloomberg
Workstation installed)

The following code within a R session opens the connection and sets some parameters. Timeout is important as sometimes the download hangs. The other parameters are for time series:

      require("RDCOMClient");
      require("chron"); # For Date-to-ComDate transformations

      blCon <<- try(blCon <- COMCreate("Bloomberg.Data.1"), silent=TRUE);
      if (class(blCon) == "try-error") {
            warning(paste("Seems like this is not a Bloomberg Workstation: ", blCon));
      } else {
            blCon[["Timeout"]] <<- 12000;
            # Constants for "DisplayNonTradingDays": Omit=0, Week=64, AllCalendar=128
            blCon[["DisplayNonTradingDays"]] <<- 64;
            # Constants for "NonTradingDayValue": BloombergHandles=0, PreviousDays=256, ShowNoNumber=512
            blCon[["NonTradingDayValue"]] <<- 512;
            # Constants for Periodicity: Daily=1, Weekly=6, Monthly=7, Annual=9
            blCon[["Periodicity"]] <<- .paOptions$frequencies[.paOptions$frequency, "BloombergPeriodicity"];
      }

I normally  open the connection and keep it open on a global variable for subsequent downloads during the session, because closing it and opening again doesn't work straighforwardly. It seems the COM object is not really released until the garbage
collector is run, and if you run the code above twice the Bloomberg connection no longer works. For a safe closing of the connection use:

      rm(blCon, envir=.GlobalEnv);
      # Explicitly invoking the garbage collector is necessary. Otherwise the COM object is not really released
      # and prevents any new connection to Bloomberg to be established later.
      gc();

To download invidual indicators:

      blpFields <- c("Long Comp Name", "Name", "Short Name", "Crncy", "Id Isin", "Ticker", "Exch Code", "Market Sector Des");
      blpTicker <- "TEF SM Equity";

      figures <- try(blCon$BlpSubscribe(Security=blpTicker, Fields=blpFields));

      if (class(figures) != "try-error") {
            blCon$Desubscribe(ids);       # Just in case...

            # Enable access to fields by name. Spaces are substituted by dots.
            names(figures) <- make.names(blpFields);

            # Flatten the list for easier access (figures["Short Name"] instead of figures["Short Name"][[1]])
            figures <- unlist(figures, recursive=FALSE);

            # Turn Bloomberg codes such as '#N/A N Ap' to NAs
            figures <- lapply(figures, FUN=function(x) {  if (x %in% c("#N/A N Ap", "#N/A N.A.")) NA else x; })

            # Check the ticker has been found
            if (figures[1] == "#N/A Sec")
                  stop(paste("Bloomberg ticker", sQuote(blpTicker), "not found"));
      }

Then you can access the downloaded data with figures$Name, figures$Market.Sector.Des, etc.

To download time series, you first need to specify the date range. I use the R 1.9.0 new Date object, which needs to be transformed to COMDate objects, for what I use the following functions adapted from R(D)Com client examples (using POSIXlt instead of
Date requires trivial changes, I used it before R 1.9.0 was released):

      # FUNCTION: as.Date.comDate
      # DESCRIPTION: Transform a Date object to a COMDate object
      # NOTE: Adapted from RCOM client code examples
      as.Date.comDate <- function(comDate, date1904 = FALSE) {
            if(date1904){
                  orig <- c(month=12, day=31, year=1903);
                  off <- 0;
            }
            else {
                  orig <- c(month=12, day=31, year=1899);
                  off <- 1;
            }

            as.Date(chron(as.numeric(comDate) - off, origin = c(month=12, day=31, year=1899)));
      }


      # FUNCTION: as.comDate.Date
      # DESCRIPTION: Tranforms a COMDate object to a Date object
      # NOTE: Adapted from RCOM client code examples
      as.comDate.Date <- function(aDate, date1904 = FALSE)
      {
            chronDate <- chron(unclass(aDate));
            if (date1904){
                  orig <- c(month=12, day=31, year=1903);
                  off <- 0;
            }
            else {
                  orig <- c(month=12, day=31, year=1899);
                  off <- 1;
            }

            if(any(origin(chronDate)!=orig))
                  origin(chronDate) <- orig;

            result <- new("COMDate");
            result[1] <- round(as.numeric(chronDate) + off);

            result;
      }


And then, to download time series into an R matrix:

      # Turn dates to COMDate's
      from <- as.Date("2000-12-31");
      to <- Sys.Date()-1;     # Yesterday
      comFrom <-  as.comDate.Date(from);
      comTo <- as.comDate.Date(to);
      blpTicker <- "TEF SM Equity";

      histData <- try(blCon$BLPGetHistoricalData(Security=blpTicker, Fields="PX_LAST",
            StartDate=comFrom, EndDate=comTo));
      if (class(histData) != "try-error") {
            # Check the ticker has been found
            if (histData[[2]][[1]][1] == "#N/A History") {
                  warning(paste("History not available for Bloomberg ticker", blpTicker, "between", from, "and", to));
            } else {
                  # Transform Bloomberg result to a R matrix whose row names are strings with the dates
                  price <- matrix(as.numeric(histData[[2]][[1]]), nrow=length(histData[[2]][[1]]), ncol=1,
                        dimnames=list(format(as.Date.comDate(histData[[1]][[1]]), "d/M/yyyy"), code));
            }
      }



Then you can move your data in R objects to a database using the RODBC package. This has be adapted to your particular database schema.

Hope this helps.

Enrique
___________________________________________________________________________

Enrique Bengoechea
Investment Consulting - CREDIT SUISSE Spain