BQL implementation request

[santiago-afonso]

Hello. It appears that it should now be possible to add BQL support to Rblpapi. The blp python library, which also relies on the C++ API, has managed to do so. Here's the specific commit: https://github.com/matthewgilbert/blp/pull/28/commits/700c28036046798c2cfd68f1fcfe041c41c573f7

I can confirm that it works in the Python library. I don't know how hard it would be, but it doesn't seem particularly complex. (I'm sadly not knowledgeable enough to propose a fix to Rblpapi myself.)

Thank you developers for sharing your code, it's been very helpful.

[eddelbuettel]

I think we might have to rely on community contributions here.

[mtkerbeR]

I had some change to look at this - following is a short summary:

While it looks not overly complicated to retrieve data from Bloomberg by copying and slightly modifying exisiting C++ code in functions bds_impl and bdh_impl, the data are in the form of a rather long and complex string in json format.

In the simplest example:

bql("get( px_last() ) for( 'AZUL4 BZ Equity' )")

this json-string can be reduced to essentially:

{"results":
          {"px_last()":
                {"name":"px_last()",
                 "offsets":[0],
                 "namespace":"DATAITEM_DEFAULT",
                 "source":"CR",
                 "idColumn":         {"name":"ID",      "type":"STRING", "rank":0, "values":["AZUL4 BZ Equity"]},
                 "valuesColumn":     {"name":"VALUE",   "type":"DOUBLE", "rank":0, "values":[5.39]},
                 "secondaryColumns":[{"name":"DATE",    "type":"DATE",   "rank":0, "values":["2024-08-31T00:00:00Z"], "defaultDate":true},
                                     {"name":"CURRENCY","type":"STRING", "rank":0, "values":["BRL"]
            }]}}}

In a next step, one probably needs to save/parse the data, using e.g. some R-code/package like RcppSimdJson (or alternatively, some C++ code):

parse_bql <- function(file = "example_result.txt") {
  input <- RcppSimdJson::fload(file)[[1]]
  lapply(input, parse_single_field)
}

parse_single_field <- function(x) {
  field        <- x$name                  
  id_name      <- x$idColumn$name
  id_value     <- x$idColumn$values         
  values_name  <- x$valuesColumn$name
  values_value <- x$valuesColumn$values
  
  sec_col_names  <- x$secondaryColumns$name
  sec_col_values <- x$secondaryColumns$values
  
  result <- as.data.frame(cbind(id_value, values_value, sec_col_values[[1]], sec_col_values[[2]]))
  colnames(result) <- c(id_name, values_name, sec_col_names[1], sec_col_names[2])
  result
}

The result of this function is a list of data.frames (one list element per entry/field in "get") where each entry in each data.frame is a string:

$`px_last()`
               ID VALUE                 DATE CURRENCY
1 AZUL4 BZ Equity  5.39 2024-08-31T00:00:00Z      BRL

Those strings would have to be converted to the correct format (as specified in the json in field type, e.g. STRING, DOUBLE, DATE).

From my point of view, this looks like a rather complex parsing tasks probably requiring additional dependencies - and there might probably be some edge cases to be considered and tested, given the variety of BQL-statements which could be used.

[eddelbuettel]

The additional dependencies are not that much of a concern, really. jsonlite is widely used so most users have. RcppSimdJson is performant if that mattered. Either one is fine, I used RcppSimdJson elsewhere too and it work. And if one really wanted to one could also 'vendor in' the well-known and widely used nlohmann header-only C++ library.

What may be a little trickier is to 'guess' or 'infer' the resulting format when the bql() query can be free-form. Is there any guidance in the documentation?

[eddelbuettel]

@mtkerbeR could I ask you for a different favour though? I posted to the r-sig-finance list yesterday asking for help in testing a new build of Rblpapi. Would you be able to install from the branch (calling the other branch) and then assert that everything still works?

[mtkerbeR]

@eddelbuettel regarding testing the new build: Sure - I will try and revert, probably tomorrow.