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?
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})
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
}
}
}
}
}