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:
- Create a new worksheet with the title being the security's CUSIP
- ID "CUSIP"
- Enter date range
- Page Down to time series data (the first page is a plot)
- 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
- 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).
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.