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:
- 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.
- 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".
- 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.
- 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.
blpapito get data,
pandasto 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$
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:
FREE_FLOAT_MARKET_CAP. Now, some of the values, which are of interest from 5 years historic up to 2 years estimated:
BEST_SALES(both to calculate sales growth);