I have a list of securities over a thousand entries long that I want to construct a time series of prices for over a specified historical period (e.g. 2/01/10-2/20/10). Doing this manually would take a long time, and it would be nice to have an automated procedure for doing so now and in the future.

The process I have been going through between Excel/Bloomberg is:

  1. Create a new worksheet with the title being the security's CUSIP
  2. ID "CUSIP"
  3. GP0
  4. Enter date range
  5. Page Down to time series data (the first page is a plot)
  6. Export closing price, bid price, and ask price (this involves exporting three times, changing the particular type of price each time) to an Excel workbook
  7. Repeat the previous steps with the next CUSIP in the list (the list is in the first worksheet)

The attached image is an example of the output from Bloomberg (with some formatting and the CUSIP's redacted).

enter image description here

I would like to automate this procedure by executing the above steps with a macro. Unfortunately, due to expensive licensing fees, it is not an option to utilize the Bloomberg API/Excel Add-in (which would allow for a relatively easy solution through utilization of the BDH function and a macro).

The main issue of course is getting Excel to interface with the Bloomberg terminal in order to execute the relevant commands/data export.

  • 1
    $\begingroup$ Just get your firm to pay for the add-in. $\endgroup$
    – John
    Commented Sep 15, 2014 at 20:05
  • $\begingroup$ I know, I wish - $\endgroup$
    – Sargera
    Commented Sep 15, 2014 at 20:34
  • 3
    $\begingroup$ The Excel addin is included in your subscription - so if you have a Bloomberg terminal you also have access to the Excel addin... $\endgroup$
    – assylias
    Commented Sep 16, 2014 at 8:18
  • $\begingroup$ assylias is absolutely right. DAPI <go> in Bloomberg. $\endgroup$ Commented Sep 16, 2014 at 15:05


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.