The Bloomberg excel formumla =BDH() only retrieves the prices for 1 security. If I want to get other securities, I'll need to repeat the formula which is no issue as I've written a script for that.
The problem comes when the dates of the securities doesn't match up, either due to trading days or contract expiry.
E.g. =BDH(name_of_commod,"PX_LAST","19/12/2015","2/5/2017") for two separate tickers produces:
QWV8 Comdty QWZ8 Comdty
#NAME? 495.2 #NAME? 479.7
14/2/2017 496.7 18/4/2017 462.2
15/2/2017 494.4 19/4/2017 457.1
16/2/2017 495.3 20/4/2017 456.6
17/2/2017 495 21/4/2017 457
20/2/2017 498.7 24/4/2017 454.9
21/2/2017 498.4 25/4/2017 453.5
22/2/2017 498.1 26/4/2017 445
23/2/2017 491.6 27/4/2017 439.9
24/2/2017 489.5 28/4/2017 450
27/2/2017 481.6 2/5/2017 448.4
The mismatch here is due to that QWZ8 is not available till 18th Apr, which kind of screws over my calculations as I've got about a hundred other securities in the data set.
Is there a way to output bloomberg data such that all the dates align to the same row?
Like such:
QWV8 Comdty QWZ8 Comdty
18/4/2017 461.3 18/4/2017 462.2
19/4/2017 456.2 19/4/2017 457.1
20/4/2017 455.7 20/4/2017 456.6
21/4/2017 456.1 21/4/2017 457
24/4/2017 454 24/4/2017 454.9
25/4/2017 452.6 25/4/2017 453.5
26/4/2017 444 26/4/2017 445
27/4/2017 438.9 27/4/2017 439.9
28/4/2017 449 28/4/2017 450
2/5/2017 447.4 2/5/2017 448.4
You can use overrides to specify how missing dates are handled. For example:
=BDH(name_of_commod,"PX_LAST","19/12/2015","2/5/2017","Days=W,Fill=N")
will have one datapoint for each workday and if no data was available for a date will leave the "price" cell blank.
The possible values are for Days are:
and for Fill:
You can find a more exhaustive list of valid overrides in the help for the function. (In Excel, go to the cell with the formula and click "More Functions..." and "Help on this function")