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 Sep 15 '14 at 20:05
  • $\begingroup$ I know, I wish - $\endgroup$ – Sargera Sep 15 '14 at 20:34
  • 2
    $\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 Sep 16 '14 at 8:18
  • $\begingroup$ assylias is absolutely right. DAPI <go> in Bloomberg. $\endgroup$ – Alexander Didenko Sep 16 '14 at 15:05

Your Answer

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

Browse other questions tagged or ask your own question.