Converting excel bloomberg function to python

Question

I m trying to convert this excel formula into python:

=@BDH("MZBZ4C 75.00 Comdty","Trade,Settle","2024-02-20 09:00:00","","Dir=V","IntrRw=true","Headers=Y","Dts=S","QRM=S","cols=4;rows=4") 

I tried with the blp library using this:

from blp import blp
bquery = blp.BlpQuery().start()
df = bquery.bdh("MZBZ4C 75.00 Comdty", ["Trade", "Settle"],
    start_date="20240219",
    end_date="",
    options={"adjustmentSplit": True})

But I get a field error saying the field is invalid. That library worked for my other types of data pulls.

Any idea how I could make this work please?

Answer 1

You need to call a different function for Intraday Tick data:

from blp import blp
import datetime

bquery = blp.BlpQuery().start()
df = bquery.bdit("RXH4 Comdty", ['TRADE', 'SETTLE'],
    start_datetime=datetime.datetime(2024,2,20,0,0),
    end_datetime=datetime.datetime(2024,2,21,23,59))

The Excel =BDH() function combines requests for static historic end-of-day data AND intraday tick data. The switch IntrRw=True is used to denote tick data. Under the covers in the low-level Bloomberg API these are different requests: HistoricalDataRequest and IntradayTickRequest respectively.

The blp.bdit function returns intraday data using start and end UTC datetime parameters. Here's the reference.

NB. Bloomberg only stores intraday tick data going back 6 months or so.

EDIT: Overrides and Options

The distinction between an Override and an Option is not always clear. In the Excel =BDH() function, there is a distinction between Field Overrides and Optional Parameters. Roughly speaking, an Override is a user-supplied value that changes how Bloomberg calculates each data item (eg: in some cases you can ask for the number to be converted to a different currency), while an Option changes which data items are returned.

The override and option names that Excel uses, are NOT always the same names that the underlying API will understand. Whether an API call accepts overrides or options is defined in the Request schema. The IntradayTickRequest request does NOT have any overrides. For the API overrides are usually Bloomberg fields (which can be viewed using the FLDS function in the Terminal).

The API definition is here, and the relevant information for Tick Data is on Page 95, where you can see the Excel option and the API equivalent.

In the OP's example, QRM is an option, but the API does not have the name QRM. Instead, reading the API docs, we see that the corresponding API option is the somewhat cryptic: includeNonPlottableEvents.

BDP Parameters	Description	API Programming Equivalent
Show QRM Equivalent(QRM)	Returns all ticks, including those with condition codes. Allows retrieving full QRM ticks if TRUE or standard API subset if FALSE (default value).	Element: includeNonPlottableEvents Element value: TRUE (show the data) or FALSE (hide the data)

The options parameter to the blp.bdit() function takes a dictionary of options, with their values.

As for the Dts flag, I think the API returns the dates/times whatever you specify, so this parameter is redundant. As is the case for some of the Excel =BDH() parameters they simple govern how the data is displayed in Excel and do not have an API equivalent. Another example is Sort: the API does not offer sorting, this flag is applied to the data by Excel.

So the OP's request becomes:

df = bquery.bdit("MZBZ4C 75.00 Comdty", ['TRADE', 'SETTLE'],
    start_datetime=datetime.datetime(2024,2,19,0,0),
    end_datetime=datetime.datetime(2024,2,22,23,59),
    options={"includeNonPlottableEvents":True})

The IntradayTickRequest schema

ELEMENT IntradayTickRequest {
    DESCRIPTION 
    MIN VALUES 1
    MAX VALUES 1
    TYPE IntradayTickRequest (SEQUENCE) {
        DESCRIPTION seqIntradayTickRequest
        ELEMENT security {
            DESCRIPTION 
            MIN VALUES 0
            MAX VALUES 1
            TYPE STRING
        }
        ELEMENT startDateTime {
            DESCRIPTION 
            MIN VALUES 0
            MAX VALUES 1
            TYPE DATETIME
        }
        ELEMENT endDateTime {
            DESCRIPTION 
            MIN VALUES 0
            MAX VALUES 1
            TYPE DATETIME
        }
        ELEMENT eventTypes {
            DESCRIPTION 
            MIN VALUES 0
            MAX VALUES 18446744073709551615
            TYPE EventType(ENUMERATION)  [] {
                DESCRIPTION 
                EventType(STRING) {
                    TRADE
                    BID
                    ASK
                    BID_BEST
                    ASK_BEST
                    BID_YIELD
                    ASK_YIELD
                    MID_PRICE
                    AT_TRADE
                    BEST_BID
                    BEST_ASK
                    SETTLE
                }
            }
        }
        ELEMENT includeConditionCodes {
            DESCRIPTION 
            MIN VALUES 0
            MAX VALUES 1
            TYPE BOOL
        }
        ELEMENT includeNonPlottableEvents {
            DESCRIPTION 
            MIN VALUES 0
            MAX VALUES 1
            TYPE BOOL
        }
        ELEMENT includeExchangeCodes {
            DESCRIPTION 
            MIN VALUES 0
            MAX VALUES 1
            TYPE BOOL
        }
        ELEMENT returnEids {
            DESCRIPTION 
            MIN VALUES 0
            MAX VALUES 1
            TYPE BOOL
        }
        ELEMENT includeBrokerCodes {
            DESCRIPTION 
            MIN VALUES 0
            MAX VALUES 1
            TYPE BOOL
        }
        ELEMENT includeRpsCodes {
            DESCRIPTION 
            MIN VALUES 0
            MAX VALUES 1
            TYPE BOOL
        }
        ELEMENT maxDataPoints {
            DESCRIPTION 
            MIN VALUES 0
            MAX VALUES 1
            TYPE INT32
        }
        ELEMENT includeBicMicCodes {
            DESCRIPTION 
            MIN VALUES 0
            MAX VALUES 1
            TYPE BOOL
        }
        ELEMENT forcedDelay {
            DESCRIPTION 
            MIN VALUES 0
            MAX VALUES 1
            TYPE BOOL
        }
        ELEMENT includeSpreadPrice {
            DESCRIPTION 
            MIN VALUES 0
            MAX VALUES 1
            TYPE BOOL
        }
        ELEMENT includeYield {
            DESCRIPTION 
            MIN VALUES 0
            MAX VALUES 1
            TYPE BOOL
        }
        ELEMENT includeActionCodes {
            DESCRIPTION 
            MIN VALUES 0
            MAX VALUES 1
            TYPE BOOL
        }
        ELEMENT includeIndicatorCodes {
            DESCRIPTION 
            MIN VALUES 0
            MAX VALUES 1
            TYPE BOOL
        }
        ELEMENT includeTradeTime {
            DESCRIPTION 
            MIN VALUES 0
            MAX VALUES 1
            TYPE BOOL
        }
        ELEMENT includeUpfrontPrice {
            DESCRIPTION 
            MIN VALUES 0
            MAX VALUES 1
            TYPE BOOL
        }
        ELEMENT includeEqRefPrice {
            DESCRIPTION 
            MIN VALUES 0
            MAX VALUES 1
            TYPE BOOL
        }
        ELEMENT adjustmentNormal {
            DESCRIPTION Adjust historical pricing to reflect: Regular Cash, Interim, 1st Interim, 2nd Interim, 3rd Interim, 4th
                        Interim, 5th Interim, Income, Estimated, Partnership Distribution, Final, Interest on Capital, 
                        Distribution, Prorated.
            
            MIN VALUES 0
            MAX VALUES 1
            TYPE BOOL
        }
        ELEMENT adjustmentAbnormal {
            DESCRIPTION Adjust historical pricing to reflect: Special Cash, Liquidation, Capital Gains, Long-Term Capital Gains, 
                         Short-Term Capital Gains, Memorial, Return of Capital, Rights Redemption, Miscellaneous, Return 
                         Premium, Preferred Rights Redemption, Proceeds/Rights, Proceeds/Shares, Proceeds/Warrants.
            MIN VALUES 0
            MAX VALUES 1
            TYPE BOOL
        }
        ELEMENT adjustmentSplit {
            DESCRIPTION Adjust historical pricing and/or volume to reflect: Spin-Offs, Stock Splits/Consolidations, Stock 
                         Dividend/Bonus, Rights Offerings/Entitlement.
            
            MIN VALUES 0
            MAX VALUES 1
            TYPE BOOL
        }
        ELEMENT adjustmentFollowDPDF {
            DESCRIPTION Adjust historical pricing and/or volume as per user's DPDF screen
            MIN VALUES 0
            MAX VALUES 1
            TYPE BOOL
        }
        ELEMENT includeClientSpecificFields {
            DESCRIPTION option to retrieve custom fields for new XDF source for currency trades: 
                   ClientDomicile
                   ClientSegment
                   ClientSubsegment
                   ClientIdentifier
                   Direction
                   TradeId
            
            MIN VALUES 0
            MAX VALUES 1
            TYPE BOOL
        }
        ELEMENT includeTradeId {
            DESCRIPTION option to retrieve unique identifier for a trade event.                
            
            MIN VALUES 0
            MAX VALUES 1
            TYPE BOOL
        }
        ELEMENT maxDataPointsOrigin {
            DESCRIPTION 
            MIN VALUES 0
            MAX VALUES 1
            TYPE DataPointsOrigin(ENUMERATION) {
                DESCRIPTION 
                DataPointsOrigin(STRING) {
                    AT_END_TIME
                    AT_START_TIME
                }
            }
        }
        ELEMENT filter {
            DESCRIPTION format example: "size>10 && cc=ob"
            MIN VALUES 0
            MAX VALUES 1
            TYPE STRING
        }
        ELEMENT includeTradeDate {
            DESCRIPTION option to retrieve the date of the trade.
            
            MIN VALUES 0
            MAX VALUES 1
            TYPE BOOL
        }
        ELEMENT includeDirtyTicks {
            DESCRIPTION option to retrieve ticks that had been subsequently cancelled or amended.  Return cancelledFlag, correctionFlag, and nativeTradeId.
            
            MIN VALUES 0
            MAX VALUES 1
            TYPE BOOL
        }
        ELEMENT includeSubSecondTimestamps {
            DESCRIPTION option to retrieve sub-second timestamps - where available 
            
            MIN VALUES 0
            MAX VALUES 1
            TYPE BOOL
        }
        ELEMENT includeBloombergStandardConditionCodes {
            DESCRIPTION option to retrieve the Bloomberg Standard Condition Codes. Return bloombergStandardConditionCodes.
            
            MIN VALUES 0
            MAX VALUES 1
            TYPE BOOL
        }
        ELEMENT includeMarketModelTypology {
            DESCRIPTION option to retrieve the Market Model Typology trade type, where available. Return marketModelTypology.
            
            MIN VALUES 0
            MAX VALUES 1
            TYPE BOOL
        }
        ELEMENT includeNativeTradeId {
            DESCRIPTION option to retrieve he Trade Aggressor. Return tradeAggressor.
            
            MIN VALUES 0
            MAX VALUES 1
            TYPE BOOL
        }
        ELEMENT includeAggressor {
            DESCRIPTION option to the Market Model Typology trade type, where available. Return marketModelTypology.
            
            MIN VALUES 0
            MAX VALUES 1
            TYPE BOOL
        }
        ELEMENT includeSecurityStatusEvents {
            DESCRIPTION option to retrieve trading phase, suspension status and auction status changes. Return tradingPhase, suspensionState, auctionState and simplifiedState.
            
            MIN VALUES 0
            MAX VALUES 1
            TYPE BOOL
        }
        ELEMENT includeESMATradeFlag {
            DESCRIPTION option to retrieve ESMA Trade flag. Return ESMATradeFlag
            
            MIN VALUES 0
            MAX VALUES 1
            TYPE BOOL
        }
        ELEMENT filters {
            DESCRIPTION define intraday tick request FDM filter.
            
            MIN VALUES 0
            MAX VALUES 18446744073709551615
            TYPE FDMFilterType (SEQUENCE)  [] {
                DESCRIPTION 
                ELEMENT filterName {
                    DESCRIPTION name of the filter: have to be unique, e.g. "filter_0"
                    MIN VALUES 0
                    MAX VALUES 1
                    TYPE STRING
                }
                ELEMENT fieldId {
                    DESCRIPTION ticker plant FDM field ID string:e.g. "EVENT_PRICE"
                    MIN VALUES 0
                    MAX VALUES 1
                    TYPE STRING
                }
                ELEMENT filterRule {
                    DESCRIPTION rule apply on this field: e.g. "[10.5, 11.5]"
                    MIN VALUES 0
                    MAX VALUES 1
                    TYPE STRING
                }
            }
        }
    }
}