How To Add Options To Bloomberg Bdh Query Using Api In Python

I have the following excel-formula: =@BDH('TSLA US Equity';'IVOL_Delta';start_date;end_date;IVOL_MATURITY=Maturity_90D;IVOL_DELTA_LEVEL=DELTA_LVL_25;IVOL_DELTA_PUT_OR_CALL=IVOL_CAL

Solution 1:

The output in Excel of this formula:

=BDH("TSLA US Equity","PX_LAST,IVOL_Delta,VOLUME_TOTAL_CALL",D2,D3,"IVOL_MATURITY","Maturity_90D","IVOL_DELTA_LEVEL","DELTA_LVL_25","IVOL_DELTA_PUT_OR_CALL","IVOL_CALL")

is:

You can get the same data using the xbbg package. There are plenty of wrappers for Bloomberg in Python, but this one is my personal favourite (pip install xbbg)

from xbbg import blp
from datetime import datetime

df = blp.bdh('TSLA US Equity',['PX_LAST','IVOL_Delta','VOLUME_TOTAL_CALL'],datetime(2021,8,1),datetime(2021,8,19),
                 IVOL_DELTA_LEVEL='DELTA_LVL_25',
                 IVOL_MATURITY='MATURITY_90D',
                 IVOL_DELTA_PUT_OR_CALL='IVOL_CALL')

print(df)

Output:

           TSLA US Equity                             
                  PX_LAST IVOL_Delta VOLUME_TOTAL_CALL
2021-08-02       709.6700    50.4803         1049494.0
2021-08-03       709.7400    50.3468          678750.0
2021-08-04       710.9200    50.0790          661374.0
2021-08-05       714.6300    49.7532          551532.0
2021-08-06       699.1000    47.7234          918211.0
2021-08-09       713.7600    47.6382          469777.0
2021-08-10       709.9900    46.8017          448640.0
2021-08-11       707.8200    46.3517          431084.0
2021-08-12       722.2500    46.7595          882608.0
2021-08-13       717.1700    47.3414         1028457.0
2021-08-16       686.1700    48.3680          639570.0
2021-08-17       665.7100    50.4111          716804.0
2021-08-18       688.9900    49.4700          732574.0
2021-08-19       680.7001        NaN               NaN

Which matches Excel (save for the live price on today's date).

EDIT: Adding support for Days and Sort

It is helpful to know that there is not a 1-1 mapping between the options on the Excel BDH call and the underlying Bloomberg API. This document describes the mapping: https://data.bloomberglp.com/professional/sites/10/2017/03/BLPAPI-Core-Developer-Guide.pdf Page 92.

So the BDH Days=A converts to Days='ALL_CALENDAR_DAYS'. But I would suggest that if you don't want weekends, using Days='NON_TRADING_WEEKDAYS'

As for Sort, the document says this: "Some parameters in the BDH() function, such as "Sort", are unavailable in the API schema, as they are unique to the Bloomberg Excel add-ins". ie the Excel addin does the sorting, not the underlying API. Fortunately it is simple to just reverse the received DataFrame if you want to.

Amended code:

df = blp.bdh('TSLA US Equity',['PX_LAST','IVOL_Delta','VOLUME_TOTAL_CALL'],datetime(2021,8,1),datetime(2021,8,19),
                 IVOL_DELTA_LEVEL='DELTA_LVL_25',
                 IVOL_MATURITY='MATURITY_90D',
                 IVOL_DELTA_PUT_OR_CALL='IVOL_CALL',
                 Days = 'NON_TRADING_WEEKDAYS').iloc[::-1]

With result:

           TSLA US Equity                             
                  PX_LAST IVOL_Delta VOLUME_TOTAL_CALL
2021-08-19         673.47    50.1950          461203.0
2021-08-18         688.99    49.4700          732574.0
2021-08-17         665.71    50.4111          716804.0
2021-08-16         686.17    48.3680          639570.0
2021-08-13         717.17    47.3414         1028457.0
2021-08-12         722.25    46.7595          882608.0
2021-08-11         707.82    46.3517          431084.0
2021-08-10         709.99    46.8017          448640.0
2021-08-09         713.76    47.6382          469777.0
2021-08-06         699.10    47.7234          918211.0
2021-08-05         714.63    49.7532          551532.0
2021-08-04         710.92    50.0790          661374.0
2021-08-03         709.74    50.3468          678750.0
2021-08-02         709.67    50.4803         1049494.0