How do I add a column of the ticker symbols for BDH?

[HHaj]

Hello,

I am using a= bdh(securities=COMP, fields=paste(FIE), start.date=as.Date("1970-01-01")

Essentially, I am trying to output one data frame with all of the COMPanies and all of the FIElds of interest. I have thousands of companies, and ten fields, enough to not hit the limit. The output of a= is a list of data frames, and there is no field in fields to give a historical value for the stock symbol. I am trying to convert the name of a data frame in the list a, and I want to create a new column "Ticker" where the name of the company is recorded in each row.

I tried this but it did not work. COMP is a character vector of the many companies I need. stock <- as.list(COMP) data=Map(cbind, df, Tickers = stock) FIN_US_EQUITY=rbindlist(data,fill = TRUE)

Is there a correct way of doing this? Essentially, my output has bdh values, but that row does not correspond to my ticker column. So, PX_LAST and EBIT are values in my row, but those are not the numbers for that company.

I do not know what's going wrong, especially since the COMP input and unique(a$Ticker) output are identical.

Thanks in advance!

[eddelbuettel]

You are misreading the documentation. Methinks what you think is supported, is in fact not supported.

But this is an open source project so you too can change it.

[HHaj]

I didn't necessarily assume it was supported. I was just wondering if anyone else has encountered the issue of wanting to have one data frame output with a column to indicate what stock everything refers to. Unfortunately, I am not capable of changing anything here. Still seeking help, so any ideas appreciated!

[HHaj]

For anyone interested in a very slow solution, note that downloading one security at a time includes the ticker name in the data frame. So just use an lappy, :

dfs<-lapply(COMP_1, function(x){ df<- bdh(securities=x, fields= FIN, start.data=as.Date("y-m-d"))

df$Ticker<-paste(x) return(df) } )

FINAL<- do.call("rbind", dfs)

Note that this method is about 5x longer than if you call every company at once into bdh.

Oh well.

[johnlaing]

I'm not sure what you are asking. If you could provide a reproducible example I would try to help, but I'm not going to try and guess what companies/fields you're using.

[HHaj]

Hi John,

If you know how to speed this process up instead of lapply, that would be very helpful! I am simply trying to use BDH but the output lacks a stock column. The output for:

STOCKS<-c("BKS US Equity", "ATW US Equity", "EGL US Equity", "NTK US Equity", "SRPT US Equity", "BHLB US Equity", "FNDA US Equity", "BOE US Equity", "ARA US Equity", "MMLP US Equity", "CLDT US Equity", "DENN US Equity", "QLD US Equity", "FCF US Equity", "NANR US Equity", "UUP US Equity", "TRS US Equity", "TBI US Equity", "PBFX US Equity", "APLP US Equity", "HSTM US Equity", "XXIA US Equity", "CVCO US Equity", "MDIV US Equity", "VTA US Equity", "CROX US Equity", "FBT US Equity","AIR US Equity", "SDIV US Equity", "CAPL US Equity", "BUSE US Equity", "FNFV US Equity", "SBSI US Equity", "IGV US Equity", "CUDA US Equity", "GTN US Equity", "BRKS US Equity", "IRDM US Equity", "IIM US Equity", "CWI US Equity", "SPXU US Equity", "UBA US Equity", "CIE US Equity", "CLLY US Equity", "WIRE US Equity")

FIN<- c( "NET_ASSETS", "BS_TOT_ASSET", "PX_LAST", "BS_DISCLOSED_INTANGIBLES", "BS_CUR_LIAB", "BS_TOT_LIAB2", "CUR_RATIO", "TOT_MKT_VAL", "MARKET_CAPITALIZATION_TO_BV", "RETURN_ON_ASSET", "ROA_TO_ROE", "IS_OPER_INC", "RETURN_ON_INV_CAPITAL", "WACC_COST_EQUITY", "SALES_REV_TURN")

data<- bdh(securities=COMP, fields=FIN, start.date=as.Date("1970-01-01")

The output for the above is a list of data frame. I tried to add a column of tickers before transforming the list into a data frame and I had incorrect mapping of codes to the data. For example,

Ticker_Names<- as.list (STOCKS) data<-Map(cbind, df, Ticker=Ticker_Names)

FIN_US_EQUITY=rbindlist(data, fill=TRUE)

Since this method, applied to thousands of companies, ended up shuffling the data where the Field values did not correspond to the actual company, I resorted to the slower sapply that works correctly but takes 4 hours to download.

If you don't have an immediate answer, I don't need to waste your time since I am almost done downloading it the longer way. However, I believe this capability is very helpful for many others.

Thanks,

[pgarnry]

I hope I have understood your question. Otherwise disregard my lengthly answer.

Working with quantitative equity research for six years using Bloomberg data I have stumbled upon many different issues unique to equities. In order not to rewrite the same code after output from Rblpapi (and former package Rbbg) I simply made a new package, RblpExtension, that extends the capabilities of the output from Rblpapi and which I believe solves your problem. I briefly talked to @eddelbuettel about my work but we agreed that it does not fit into the Rblpapi package - things should be contained as much as possible.

Coming back to your problem. Try this...

library(devtools)

install_github("pgarnry/RblpExtension")

library(RblpExtension)

stocks <- c("BKS US", "ATW US", "EGL US", "NTK US", "SRPT US", "BHLB US", "FNDA US",
            "BOE US", "ARA US", "MMLP US", "CLDT US", "DENN US", "QLD US", "FCF US",
            "NANR US", "UUP US", "TRS US", "TBI US", "PBFX US", "APLP US", "HSTM US",
            "XXIA US", "CVCO US", "MDIV US", "VTA US", "CROX US", "FBT US", "AIR US",
            "SDIV US", "CAPL US", "BUSE US", "FNFV US", "SBSI US", "IGV US",
            "CUDA US", "GTN US", "BRKS US", "IRDM US", "IIM US", "CWI US", "SPXU US",
            "UBA US", "CIE US", "CLLY US", "WIRE US")

fin <- c("NET_ASSETS", "BS_TOT_ASSET", "PX_LAST", "BS_DISCLOSED_INTANGIBLES",
         "BS_CUR_LIAB", "BS_TOT_LIAB2", "CUR_RATIO", "TOT_MKT_VAL",
         "MARKET_CAPITALIZATION_TO_BV", "RETURN_ON_ASSET", "ROA_TO_ROE",
         "IS_OPER_INC", "RETURN_ON_INV_CAPITAL", "WACC_COST_EQUITY", "SALES_REV_TURN")

dat <- bloomberg_historical_data(tickers = stocks,
                                 type = "Equity",
                                 fields = fin,
                                 freq = "MONTHLY",
                                 start.date = "2010-01-01")

This creates an array which is the only data structure (for numeric data of course) that makes sense if you have multiple tickers and multiple API fields across time and want to have your data in a condensed form. So the output below is for the first 10 months of ticker one across all fields.

The picture below shows the net assets for the first 10 months across the first six tickers. Notice how all data is aligned correctly. NA shows up if it is not available.

As you can see the bloomberg_historical_data function does a lot of things. It makes it easier to apply options and override fields and for monthly time series the yearmon class must be used - the reason for this goes deep into Bloomberg's data management, something I have digged up over the years in talks with them.

Another function you might find interesting is the bloomberg_historical_index_members function which creates a binary matrix for a specified period on an equity index for when tickers were a member in the index. The makes it possible to remove surviourship bias in backtesting of factor models or short-term trading strategies. So basically you just call which(x[y, ] == 1) and it will give you the tickers that were part of the index in period y.

[johnlaing]

I'm not really sure where your issue is. This seems to work fine for me:

data <- bdh(securities=STOCKS, fields=FIN, start.date=as.Date("1970-01-01"))
data <- Map(cbind, data, ticker=as.list(STOCKS))
data <- do.call(rbind, data)

The code you provided before didn't run as-is: renamed variables, missing parentheses, etc. So there is reason to believe you may not be doing exactly what you think you're doing. Check again, and if there is a real issue that you can demonstrate concisely then let us know.

[eddelbuettel]

I briefly talked to @eddelbuettel about my work but we agreed that it does not fit into the Rblpapi package - things should be contained as much as possible.

I do not stand by that statement. I think I always said that I do not understand the reason for your package.

If something is generally useful then of course it should be in a pull request for the main package (for which you'd get full credit etc pp). If it is of marginal interest, then it is better to keep it outside.

The open source way of things is to make existing package better, more robust and more featureful.

[pgarnry]

@eddelbuettel sorry for the misquote

You're right that I am making some marginal things. I do it because it fits better into my research framework. For example always output data in xts format, no lists, date alignments on active vs inactive equity tickers, function variables for period frequency (don't want to set it in options all the time) and currency. But I agree they don't fit into a general purpose package as Rblpapi.

I have turned the package into a private repository as the functions are indeed mostly thought as in-house functions to optimise Rblpapi output for specific equity research. Making it private also avoids the notion that is somehow suggests a better way than Rblpapi because that is not the case or intention unless you do very specific equity things.

[eddelbuettel]

I return xts as well whenever possible (and when everything is numeric). See eg

returnAs = getOption("blpType", "matrix")

in getBars() and getTicks(). With a startup options for blpType="xts" it is all xts for me. But I am warming up more and more to data.table and may try that for some of the more data.frame-alike returns which have non-numeric values.

I would still welcome contributions by you to the package. Many good ideas come from good users. Ideally it should be something of more general than specific interest...