Closed tmlynowski closed 1 year ago
@tmlynowski could you spec out what this involves? Is this a feature of the underlying blpapi
library? The Excel Bloomberg functionality and their python API are not identical.
@matthewgilbert BQL is indeed a functionality that can only be accessed via Excel (or BQNT). However, there seems to be a workaround that involves accessing Excel via Python to make BQL requests (https://stackoverflow.com/a/69014472). Do you think it would be possible to create a new class to implement this workaround?
blp
is a wrapper for accessing blpapi
via a more pythonic interface. What you linked has nothing to do with blpapi
so I think this would be out of scope for this project.
@matthewgilbert Rather like the //blp/exrsvc
service for retrieving saved SRCH results (which is used in pdblp
), there is an undocumented API service for BQL queries, //blp/bqlsvc
. The sendQuery
request takes a BQL string (the expression
), and returns a single element response containing a JSON string, which can be unpacked into a DataFrame. It is not well advertised.
@matthewgilbert Rather like the
//blp/exrsvc
service for retrieving saved SRCH results (which is used inpdblp
), there is an undocumented API service for BQL queries,//blp/bqlsvc
. ThesendQuery
request takes a BQL string (theexpression
), and returns a single element response containing a JSON string, which can be unpacked into a DataFrame. It is not well advertised.
Is this feature available for the Desktop API or is it exclusive to BQNT?
@avantgardeam It is in the API, accessible via blpapi
.
Thanks for flagging @DS-London . In that case I’m happy to except a PR for this feature.
@DS-London, thank you for sharing this option!
@matthewgilbert, I have successfully made requests to //blp/bqlsvc
. However, I've noticed that the behavior of this endpoint differs from the others. Take a look at the sample request and the beginning of its response:
query = {'sendQuery': {'expression': "get( px_last() ) for( 'AAPL BZ Equity' )"}}
response = bquery.query(query, parse=False, collector=list)
Response snippet:
[{'eventType': 5,
'eventTypeName': 'blpapi.Event.RESPONSE',
'messageNumber': 0,
'message': {'fragmentType': 0,
'correlationIds': [66],
'messageType': 'result',
'timeReceived': Timestamp('2023-06-28 04:45:19.509121+0000', tz='UTC'),
'element': '{"results":{"px_last()":{"name":"px_last()","offsets":[0], ...'}]
The issue arises because BlpParser
relies on list(response["message"]["element"].keys())[0]
to represent the response type. However, in this case, response[0]["message"]["element"]
is a string that lacks any information about the response type.
In this case, how should I proceed? Should I set parse
to False
in self.query()
and utilize the collector for both parsing and collecting?
@avantgardeam The response is a JSON string. You can parse the whole string by using json.loads(response[0]["message"]["element"])
and then pick out the results
key. Something like this (untested with blp
):
from pandas import DataFrame,concat
from json import loads
...
jsonResults = loads(response[0]["message"]["element"])
rslts = jsonResults['results']
df = concat([ DataFrame(c['valuesColumn']['values'],
index=c['idColumn']['values'],
columns=[c['name']])
for c in [rslts[k] for k in rslts.keys()] ],
axis='columns')
is one way of converting the JSON to a DataFrame, though only tested on a sub-set of BQL queries.
Thanks, for sharing, @DS-London !
Sometimes, the secondaryColumns
are important too, especially when it comes to historical data. Since we don't have much info on this endpoint, I've gathered everything and melted the dataframes to make it easier to concatenate later. Any feedback would be great!
import json
bquery = BlpQuery().start()
query = create_query(
"sendQuery",
{"expression": "get( PX_LAST(start = -3D), CUR_MKT_CAP, PX_TO_BOOK_RATIO ) for( ['IBM US Equity', 'AAPL US Equity', 'AZUL4 BZ Equity'] )"}
)
res = bquery.query(query, parse=False, collector=list)
result = json.loads(res[0]['message']['element'])['results']
data = []
for field in result.values():
# ID column may be a security ticker
aux_dict = {
"field": field['name'],
"id": field['idColumn']['values'],
"value": field['valuesColumn']['values']
}
# Secondary columns may be DATE or CURRENCY, for example
for secondary_column in field['secondaryColumns']:
aux_dict[secondary_column['name']] = secondary_column['values']
df = pandas.DataFrame(aux_dict)
# Since we have multiple secondary columns, we need to melt the dataframe
id_vars = ['field', 'id', 'value']
df = df.melt(
id_vars=id_vars, value_vars=df.columns.difference(id_vars),
var_name="secondary_name", value_name="secondary_value"
).dropna(subset=["value"])
column_order = ['secondary_name', 'secondary_value', 'field', 'id', 'value']
df = df[column_order]
data.append(df)
final_df = pandas.concat(data)
Feature enhancement Request: Is it possible to implement BQL functionality same as Excel BQL Builder? Thanks