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:

TSLAUSEquityPX_LASTIVOL_DeltaVOLUME_TOTAL_CALL2021-08-02       709.670050.48031049494.02021-08-03       709.740050.3468678750.02021-08-04       710.920050.0790661374.02021-08-05       714.630049.7532551532.02021-08-06       699.100047.7234918211.02021-08-09       713.760047.6382469777.02021-08-10       709.990046.8017448640.02021-08-11       707.820046.3517431084.02021-08-12       722.250046.7595882608.02021-08-13       717.170047.34141028457.02021-08-16       686.170048.3680639570.02021-08-17       665.710050.4111716804.02021-08-18       688.990049.4700732574.02021-08-19       680.7001NaNNaN

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:

TSLAUSEquityPX_LASTIVOL_DeltaVOLUME_TOTAL_CALL2021-08-19         673.4750.1950461203.02021-08-18         688.9949.4700732574.02021-08-17         665.7150.4111716804.02021-08-16         686.1748.3680639570.02021-08-13         717.1747.34141028457.02021-08-12         722.2546.7595882608.02021-08-11         707.8246.3517431084.02021-08-10         709.9946.8017448640.02021-08-09         713.7647.6382469777.02021-08-06         699.1047.7234918211.02021-08-05         714.6349.7532551532.02021-08-04         710.9250.0790661374.02021-08-03         709.7450.3468678750.02021-08-02         709.6750.48031049494.0