I’m on my research project to find the last price for a security at IPO and 20 trading days after for 5 years. I use =BDP($A$1, "EQY_INIT_PO_DT") to get the IPO trading date and its fine. The issue happened when I use =BADDPERIODS(A2, "NumberOfPeriods", "20", "CDR", "ID JA", "BusDayAdj", "1") it still include the non trading days. So when I use =BDH($A$1, "PX_LAST", A6, A6) it shows #N/A N/A. I guess =baddperiods() function outputted the non trading days and the =bdh() unable to get the price. How can I obtain the correct 20 trading days (exclude weekends and any public / non-trading days) after a certain date?
Reference: A1= Company Ticker A2= IPO Date A3= =BADDPERIODS(A2, "NumberOfPeriods", "20", "CDR", "ID JA", "BusDayAdj", "1") ... A6= =BADDPERIODS(A5, "NumberOfPeriods", "20", "CDR", "ID JA", "BusDayAdj", "1")
The BDH() worksheet function has optional parameters that specify how the timeseries data is returned. These come after the start and end date parameters. One of these allows a calendar to be specified. Specifying a calendar prompts Bloomberg to look back and find the closest (but earlier) ‘good’ value for the date requested.
The short, but inefficient answer is to use this formula in cell B6:
It is inefficient because each cell is making a relatively expensive call to Bloomberg for one day’s history. It is better to pull back all the history from the start date to today in a single call, and put it in some spare columns. Then look your date up (using INDEX/MATCH) to find the price value.