Could I make "nonTradingDayFillMethod" produce NA instead of blanks?

[Nicktz]

Hi, is it possible to set nonTradingDayFillMethod to make NA's as opposed to blanks in r using Rblpapi?

This will make rewriting the list as dataframe easier, as at the moment it doesn't read any data points for tickers with no closing prices for a given period.

[johnlaing]

Can you provide an example of what you mean? This works as-is:

> bdh("GOOG Equity","PX_LAST", as.Date("2015-09-25"), include.non.trading.days=TRUE)
        date PX_LAST
1 2015-09-25  611.97
2 2015-09-26      NA
3 2015-09-27      NA
4 2015-09-28  594.89
5 2015-09-29      NA

[Nicktz]

The above only works if there are any values to start with. For the following stock and date the top code only produces blanks:

bdh("VNF SJ Equity","PX_LAST", as.Date("2013-09-25"), include.non.trading.days=TRUE)

[Nicktz]

Is there some way to make it produce NA if none of the chosen dates has closing prices? Thanks for the help!

[Nicktz]

Here follows an example of what I am doing (I have a vector of stocks, but suppose it is only now CPI and VNF, with the latter not having closing prices during this time):

opts <- structure(c("ACTUAL", freq,cur,"ALL_CALENDAR_DAYS","NIL_VALUE"),
                        names = c("periodicityAdjustment",
                                  "periodicitySelection","currency",
                                  "nonTradingDayFillOption","nonTradingDayFillMethod"))

All <- bdh(securities = c("CPI SJ EQUITY","VNF SJ EQUITY"), 
       fields = "PX_LAST", 
       start.date = as.Date("2013-09-25"),
       options=opts)

I need all the values of VNF for the dates set above to be NA. Is there an option to do so?

[eddelbuettel]

The code is, as the saying goes, 'open'. Try firing up the queries that give you difficulties, capture the output and then try to rework aggregation in a way that suits you and leaves the interface and behavior unchanged. That would be the preferred way forward.

[Nicktz]

So just to make sure, there is no built-in option to make all trading days NA if the stock did not trade in the defined period using bdh in rblpapi? I.e., the option NIL_VALUE only produces NA if there are at least one closing price in the date set?

[f1nasf1]

You figure it out as you go along

On Sep 29, 2015, at 7:36 AM, Nicktz notifications@github.com wrote:

So just to make sure, there is no built-in option to make all trading days NA if the stock did not trade in the defined period using bdh in rblpapi? I.e., the option NIL_VALUE only produces NA if there are at least one closing price in the date set?

— Reply to this email directly or view it on GitHub.

[eddelbuettel]

@Nicktz We are not Bloomberg. Hit the F1 key and ask there. What we do is utterly transparent. We provide source code.

[eddelbuettel]

In

opts <- structure(c("ACTUAL", freq,cur,"ALL_CALENDAR_DAYS","NIL_VALUE"),

what are freq and cur? Minimally reproducible examples ...

[Nicktz]

Sorry I see I didn't include that part. Below I now use WEEKLY and USD:

opts <- structure(c("ACTUAL", "WEEKLY","USD","ALL_CALENDAR_DAYS","NIL_VALUE"),
              names = c("periodicityAdjustment",
                        "periodicitySelection","currency",
                        "nonTradingDayFillOption","nonTradingDayFillMethod"))

All <- bdh(securities = c("CPI SJ EQUITY","VNF SJ EQUITY"), 
       fields = "PX_LAST", 
       start.date = as.Date("2013-09-25"),
       options=opts)

[eddelbuettel]

Thanks. At first glance, we just pass the query through. You could go into the C++ code for bdh_impl() and see for each callback what is coming and think about alternative. But also note that

R> all <- bdh(securities = c("VNF SJ EQUITY"), fields = "PX_LAST",
           start.date = as.Date("2013-09-25"), options=opts)
R> all
NULL
R> 

[armstrtw]

we could add a verbose method to print the actual bbg messages as we traverse the response...

I think in Ana's version she was serializing it all to some log files, which I found irritating, but having it show up on the console would not be so bad. thoughts?

[Nicktz]

The Bloomberg help desk suggests adding an extra IF condition to check if the value is blank, inserting the custom NA if so. But as @eddelbuettel noted, it produces no values (not even blanks) if there are not at least a single closing price. Is it something that can easily be resolved you think?

[eddelbuettel]

In favour of a logging / verbose / debug option. I may have had it even for some of the accessors I worked. We should figure out what we want first. Logging to stdout is fine by me.

@Nicktz: Well can you not run the requests serially and if you hit NULL insert what you'd rather see?

[Nicktz]

@eddelbuettel dealing with NULL in lists (output source from blpapi), is proving to be somewhat challenging (see e.g. http://www.r-bloggers.com/r-na-vs-null/). If you have any idea how I can make the NULL into NA in this environment the problem would be solved?

[eddelbuettel]

Not that hard:

R> library(Rblpapi)
R> opts <- structure(c("ACTUAL", "WEEKLY","USD","ALL_CALENDAR_DAYS","NIL_VALUE"),
+                   names = c("periodicityAdjustment", "periodicitySelection",
+                   "currency", "nonTradingDayFillOption","nonTradingDayFillMethod"))
R> 
R> sec <- c("CPI SJ EQUITY","VNF SJ EQUITY")
R> 
R> res <- lapply(sec, function(s) {
+                   x <- bdh(securities = s, fields = "PX_LAST", start.date = as.Date("2015-08-01"), options=opts)
+                   if (is.null(x)) x <- NA
+                   x
+               })
R> res
[[1]]
        date PX_LAST
1 2015-08-04 38.5661
2 2015-08-11 36.8949
3 2015-08-18 35.2786
4 2015-08-25 36.6764
5 2015-09-01 34.1276
6 2015-09-08 34.7536
7 2015-09-15 35.6594
8 2015-09-22 34.7377
9 2015-09-29 35.3340

[[2]]
[1] NA

R> 

[Nicktz]

Thanks for your help @eddelbuettel. The problem is that the solution does not produce a consistent output for other tickers. E.g., consider:

opts <- structure(c("ACTUAL", "WEEKLY","USD","ALL_CALENDAR_DAYS","NIL_VALUE"),
              names = c("periodicityAdjustment",
                        "periodicitySelection","currency",
                        "nonTradingDayFillOption","nonTradingDayFillMethod"))

All <- bdh(securities = c("WGR SJ EQUITY","VNF SJ EQUITY"), 
       fields = "PX_LAST", 
       start.date = as.Date("2013-09-25"),
       options=opts)    

The output for WGR produces NA at dates where there's no closing price, yet produces nothing for VNF. I am in contact with Bloomberg API and will post a solution if I get any help on that side. Please let me know if anyone here manages to produce NA values for all the entries for VNF at each date (i.e. something consistent with WGR's output.)

[eddelbuettel]

Try running two individual queries.

[armstrtw]

I'm not in front of the code right now, but I believe this is related to the type inferance. i.e. the type inference is lazy, and since we don't see a single observation for this particular time series, it never actually infers the type (and the column is never created).

Before this version of the code, I made a call to the dictionary (I think that's what it was called) to ask bbg for the type relating to a particular field, but there are issues w/ that too, for instance the type 'Date' is returned whether it's Date or Date/time, etc. Hence, the reason for the switch to lazy inference.

[armstrtw]

Also, I just want to add in this particular case, VNF SJ was acquired in 2006. So, it's not just that the dates are non-trading days. Those dates are in fact trading days, but the company had no prices since it had already been acquired... So, perhaps this is a bad example or perhaps there is another option to fill not only nonTradingDays, but other missing dates as well...

You can try to replicate in Excel perhaps.

[Nicktz]

Hi all, this problem is not asset specific (same problem with e.g. WLN SJ, WET SJ, and many others as well), and the solution should ideally be generic so that we can input a vector of stock tickers - which can be used to produce a data.frame with the columns equal to the vector input space.

E.g. if you run the following code and produce a df, you will see that it omits VNF and WET completely. I will write it such that it fills these columns with NA's in this case, but ideally this should happen automatically?

opts <- structure(c("ACTUAL", "WEEKLY","USD","ALL_CALENDAR_DAYS","NIL_VALUE"),
          names = c("periodicityAdjustment",
                    "periodicitySelection","currency",
                    "nonTradingDayFillOption","nonTradingDayFillMethod"))

All <- bdh(securities = c("CPI SJ EQUITY","WGR SJ EQUITY","VNF SJ EQUITY","WET SJ EQUITY"), 
   fields = "PX_LAST", 
   start.date = as.Date("2013-09-25"),
   options=opts)    

df <- as.data.frame(All)

[armstrtw]

My point was that BBG is not returning any data points for those missing securities at all, so we cannot infer the 'type' of the output vector, and we cannot build an NA vector if we don't know the type.

Hence, is there an option to ask BBG to fill NA's even when a security has ceased trading.

Otherwise, we need to rethink the lazy inference. I had initially written a lookup function, but the types bbg returns are not specific enough to create the actual type in R.

[eddelbuettel]

And as I said before if @Nicktz were to actually loop over one-security-per-call he could deal with at the per-security level. Which as @armstrtw points out is needed.

[Nicktz]

Sure, that's what I did. Simple solution to my problem is to populate the dataframe as follows:

df[setdiff(tickers, names(df))] <- NA

But this is a specific solution - I thought it might be worth your while knowing about this problem when using a vector of tickers where some might not have closing prices for a period.

Thanks for the suggestions though!

[eddelbuettel]

I think we can close this as being more on the client side of the package.

[pgarnry]

I know this thread is closed, but I just wanted to make a small observation for the benefit of future readers.

The issue can indeed be solved by looping over the vector of tickers as suggested by eddelbuettel. However, a system.time() on one bdh call with the full vector of tickers is around 10x faster than looping over each ticker and calling the bdh in each step.

This means that the issue Nicktz has should be solved intelligently on the Bloomberg output from the one call on bdh with the full vector of tickers.