How to translate Excel BDH formula into Python API code

by

I need help translating the below BDH formula from excel into python code using the BBG API. Specifically, I need to query only the weekly data for the particular index but my current python code is only returning daily. Is there a way to set specific criteria in python similar to how you query in Excel?


=@BDH(AAIIBEAR Index,PX_LAST,Start Date,End DAte,"Dir=V","fill=#N/A","Per=W","Days=A","Dts=H","cols=1;rows=1783")

Currently, I have built this Python code which queries the data in daily increments but need it to return weekly.

import os 
from blp import blp 
import pdblp 
import blpapi
import datetime 
import xlwings 
import pandas as pd

with open(os.path.join(os.getenv("TEMP"), "Bloomberg", "log", "bbcomm.log"),"r") as f: 
    try:
        port = f.read().split("BLOOMBERG COMMUNICATION SERVER READY on Port: ")[-1].split("\n")[0]
    except:
        port = 8194

con = pdblp.BCon(debug = False, port = 8194, timeout = 100000)
con.start()
today = datetime.datetime.today()

#assign tickers
bbg = con.bdh(['AAIIBEAR Index','PX_LAST', start_date="20240210",end_date= "20240220")
bbg = bbg.resample('D').ffill()
print (bbg)

#print data to excel 
with pd.ExcelWriter(r'X:\Public\Employee folders\fvan\Market.xlsx') as writer:
    bbg.to_excel(writer, sheet_name="data", startcol=1)

I am expecting to return bloomberg data in weekly increments rather than daily. Is there a way to set this kind of criteria when querying in Python?

  • Your question needs a minimal reproducible example consisting of sample input, expected output, actual output, and only the relevant code necessary to reproduce the problem. See How to make good reproducible pandas examples for best practices related to Pandas questions.

    – 

  • I don’t know the Python API, but maybe resample('W')?

    – 

The OP has serendipitously chosen a security that is only updated weekly (on a Thursday), hence there is no adjustment needed. The line bbg = bbg.resample('D').ffill() is then coercing this weekly data into daily data, which is not what is wanted here.

If a security does actually have daily data, then the options are passed to the bdh() call by the elms parameter. The Excel =BDH() call uses different option names compared to pdblp (which uses the underlying Bloomberg API).

The mapping can be found on Page 92 of the Bloomberg API Guide, but I’ve extracted the relevant ones here (the doc also gives the alternative values for these parameters for different manipulations).

Excel BDH() parameter API option Option Value
fill=#N/A nonTradingDayFillMethod NIL_VALUE
Per=W periodicitySelection WEEKLY
Days=A nonTradingDayFillOption ALL_CALENDAR_DAYS

Putting this together:

import pdblp

con = pdblp.BCon() 
con.start()

dtStart="20240202"
dtEnd = '20240220'

df = con.bdh('INDU Index','PX_LAST',dtStart,dtEnd,
             elms=[('periodicitySelection','WEEKLY'),
                   ('nonTradingDayFillOption','ALL_CALENDAR_DAYS'),
                   ('nonTradingDayFillMethod','NIL_VALUE'),
                   ('periodicityAdjustment','ACTUAL')])

Note: The final parameter I have added is periodicityAdjustment. This governs whether the start or the end date is used for the weekly reference.

Using periodicityAdjustment set to ACTUAL, the last day of the period is the reference, and the weekly days are counted back from that:

ticker  INDU Index
field   PX_LAST
date    
2024-02-06  38xxx.xx
2024-02-13  38xxx.xx
2024-02-20  38xxx.xx

Using periodicityAdjustment set to CALENDAR, the starting date is the reference and the weeks are counted forward from that:

ticker  INDU Index
field   PX_LAST
date    
2024-02-02  38xxx.xx
2024-02-09  38xxx.xx
2024-02-16  38xxx.xx

Leave a Comment