2
$\begingroup$

I have identified around 20 interesting statistics for a universe of stocks, regarding metrics of size, growth, valuation, quality, risk. Think market cap, free float, average daily volume. The universe encompasses around 200 stocks. I'd like to build a scoring model based on data from Bloomberg. This model is supposed to help target stocks from within the universe for further analysis.

For some of the statistics historical and projected values are also of interest, where I'd like to use the Bloomberg estimates. Some of the statistics will have annual values from 5 or 3 years historic up to 3 years projected. Think sales growth, EBIT, ev to sales, cashflow yield.

Ideally, I want to have an Excel sheet where I can edit the universe and run a macro to recalculate the scoring or just add an item and expand the calculations.

After considering several options, I'm stuck on how to get started. I want to keep things simple and right now my biggest problem is that the data will likely be unhandy to work with, once pulled into an Excel sheet. I have worked with the Bloomberg V3COM API wrapper before. Also, I have considered working with BQL or BDH. I have looked at some of the sheets in XLTP <GO> or considered pulling data out of Bloomberg using blpapi in Python.

I have following questions:

  1. How to obtain this data most conveniently? Is it more advisable to use BQL or BDH? The V3COM wrapper seems to work just fine for the fields which I tried, also for historical values.
  2. What is a good way to store suchlike data? An excel sheet seems obvious, but I have read a often that "Excel is not a data base".
  3. The obvious goal seems to have a sheet with one row per title, but then again for example EBIT (from 5y historic up to 3y estimated) alone takes up 8 columns in the sheet and I cannot imagine this being an easy to handle representation of the data. I have considered one sheet per stock, which could greatly increase clarity, but I am not even sure I Excel could handle that many sheets nor that it is easy to handle either for this many stocks.
  4. The goal is to set up some kind of scoring on the data, say for following aspects: growth, valuation, quality, risk. Maybe one sheet per category could be handled well. And then I could build a score for each and summarize it in one sheet for all.

Thanks to anyone willing to share their experiences.

Improve this question
$\endgroup$
3
  • 3
    $\begingroup$ Without knowing what can be done from within the Bloomberg environment: 1) Roughly, 200 stocks, 40 statistics (20 actual, 20 projected), daily resolution (30 years ~ 7500 days) will net you about 60m rows of raw storage (300k per stock). You may dump the data in csv files (one per stock). 2) I would suggest doing your development in Excel (or whatever tool you fancy), but bring the scripting / production version into something like Python or R. For that small amount of data, both should be sufficiently fast. 3) For presentation, you could use simple (Excel) report, or plots in R/python. $\endgroup$
    – Kermittfrog
    Mar 30, 2021 at 15:00
  • 1
    $\begingroup$ Please share a couple Bloomberg fields interested in to help me visualize.blpapi to get data, pandas to manipulate it, and `SQLite as your database sounds like the way to go. I’d run (not walk) away from Excel and sheets for something like this $\endgroup$
    – cpage
    Apr 4, 2021 at 2:54
  • $\begingroup$ I have actually started development using Rblpapi, but it shouldn't make much of a difference. Here are some of the fields, which I have identified so far. Firstly, ones that only require one value returned: AVG_DAILY_VALUE_TRADED_20D; AVG_DAILY_VALUE_TRADED_3M; CRNCY_ADJ_MKT_CAP, EUR; FREE_FLOAT_MARKET_CAP. Now, some of the values, which are of interest from 5 years historic up to 2 years estimated: EBIT, BEST_EBIT (estimated); SALES_REV_TURN, BEST_SALES (both to calculate sales growth); T12_FCF_MARGIN, BEST_ESTIMATE_FCF; PX_TO_SALES_RATIO, BEST_PX_TO_SALES_RATIO. $\endgroup$
    – Friedrich
    Apr 4, 2021 at 7:29

1 Answer 1

Reset to default
1
$\begingroup$

I would say you might want to ask the help desk F1 F1 or you sales representative at BBG.

If you use R - or any v3 API based solution WAPI<GO>, you cannot use BQL.

Yet, I am almost certain that BQL would allow you to do this pretty much all within BQL syntax. Massive benefit will be that you will not constantly hear from a help desk rep because you (b)reached your daily data limit again. BQLX has some good examples in the BQL for equities section. There are even specific video tutorials for screening and scoring.

Improve this answer
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.