1

I am making a tool which plots the historical (12800 dates, 1980-today) stock performance of subsets of 3500 companies based on a set of sustainability rating criteria chosen by the user. For example, one could pick "worker health and safety" and then see the stock performances of the companies with the best and worst ratings in that area compared to the average.

After the user inputs constraints, I produce a list of Bloomberg tickers for which I want to analyze performance. Is there a way to upload such a list of tickers to Bloomberg, and have it return the historical performance data? Or even a well-documented source of help / examples? I asked the help desk but they just told me to read their documentation, which didn't prove to be of much use.

I am avoiding at all costs downloading the ~44,000,000 data points I might theoretically need (35 years of daily last price for all 3500 companies) - so any alternative ideas would be very much appreciated.

S Stephens
  • 21
  • 4
  • Do you want to do it in VBA (probably too complicated for the Stackoverflow format) or are you happy with Excel formulae? – assylias Oct 07 '15 at 14:01
  • I am ok with formulae, though the project is at present VBA based (l am restricted to Excel so my less-technical colleagues can run it in a familiar environment). As long as user-defined criteria can be taken into account it should be fine either way. – S Stephens Oct 07 '15 at 14:32
  • Say you have 10 tickers: put them in B1:K1 on a sheet. In A2 use: `=BDH(B1,"PX LAST",19800101,today(),"Dates=S,Days=W,Fill=n.a.")`. In C2:K2 use: `=BDH(C1,"PX LAST",19800101,today(),"Dates=H,Days=W,Fill=n.a.")`. The cells will contain the data, with empty cells when no price was found, for all working days since January 1980. Note that controlling the data population from VBA can be a bit tricky. You can change Fill to `P` is you want the previous price to be carried over. – assylias Oct 07 '15 at 14:48

1 Answers1

0

Using the Bloomberg Excel Add-In, you can automate functions through Historical Data-set Formulas. Once you have the core formula syntax down, you can easily do things like drag cells to update new tickers with similar data-sets.

Your goal appears to be something like a Stock Screener, however. In your example you mentioned that you would like to make it custom and flexible for a user to screen stocks based on a particular set of criteria.

Maybe you're trying to do something conceptually like this : https://www.google.com/finance/stockscreener

But with a wider breadth of data. What you would have to do to avoid the costs of Network Transactions and Data Storage is to be able to quickly eliminate a solid part of the Stock Screen Universe by using some data-sets or indicators that are stronger in filtering out the noise of stocks that may exhibit criteria that the user may not be interested in.

RasikhJ
  • 601
  • 5
  • 10