Is there a limit to the number of times BBG will allow you to connect before it refuses the connection?

tmiller198 commented 1 year ago

Code Sample, a copy-pastable example if possible

from owscipy.context import DbContext
from owscipy.util import load_config
import pandas as pd
from blp import blp
import logging

def main():
    #set connection profile
    context = DbContext()

    #get bond list
    def get_bond_data():
        df = context.frame("""SELECT TOP 3000 (YEAR([date]) * 10000 + MONTH([date]) * 100 + DAY([date])) AS 'bbg_date',
        isin + ' Corp' AS 'bond_lookup',
        (bidprice + offerprice) / 2 AS 'midprice'
        FROM bond_hist
        WHERE [date] >= '2021-07-01'
        AND [date] < '2021-07-02'""","risk")
        return df

    #pull bond data
    df = get_bond_data()
    logging.info('bond data Downloaded') 

    #define BBG pull functions
    def get_dv01(a,b,c):
        bquery = blp.BlpQuery().start()
        try: df1 = bquery.bdp(a, ['YAS_RISK'],overrides=[('YAS_BOND_PX', float(b)),('USER_LOCAL_TRADE_DATE', int(c))])
        except ValueError:
            pass
        try:
            df1 = df1['YAS_RISK'].loc[df1.index[0]]
        finally: blp.BlpQuery().stop()
        return df1

    #get BBG data
    blp.BlpQuery().start()  
    df['dv01'] = df.apply(lambda row : get_dv01(row['bond_lookup'],row['midprice'], row['bbg_date']), axis = 1)
    blp.BlpQuery().stop()       
    df['dv01'] = df['dv01'] * 100
    logging.info('DV01 Calculated') 

if __name__ == '__main__':
    main()

Problem description

I have a python script that pulls a bond ISIN, date, and prices in order to calculate the DV01 for the bond using BBG. The script runs fine when I only pull the top 100 rows, but if I increase to the top 3,000 rows for example, I get the below error:

2023-01-18 16:25:29 OWSCNYWKS105 root[39332] INFO bond data Downloaded

18JAN2023_16:27:08.808 39332:38476 WARN apicm_apiconnector.cpp:573 ApiConnector::negotiatorCallback localhost: Session negotiation failed for 127.0.0.1:8194 result = 1

18JAN2023_16:27:08.809 39332:38476 WARN blpapi_apicmadapter.cpp:336 blpapi.session.transporttcp.apicmadapter.{476} Failed BBCOMM session negotiation localhost:8194 <127.0.0.1:8194>

18JAN2023_16:27:08.809 39332:38476 WARN blpapi_platformtransporttcp.cpp:151 blpapi.session.transporttcp.{476}. { platformId=0 } 127.0.0.1, session pool state=Failed

18JAN2023_16:27:08.810 39332:38476 WARN blpapi_platformcontroller.cpp:634 blpapi.session.platformcontroller.{476} Platform failed 1 consecutive connect attempts, stopped trying to reconnect. { PlatformId=0 }

18JAN2023_16:27:08.811 39332:6728 ERROR blpapi_sessionimpl.cpp:2445 blpapi.session.{476} Failed to start session: Failed to connect 2023-01-18 16:27:08 OWSCNYWKS105 blp.blp[39332] INFO Failed to connect to Bloomberg: SessionStartupFailure = { reason = { source = "Session" category = "IO_ERROR" errorCode = 3 description = "Failed to connect" } }

Traceback (most recent call last): File "s:\tmiller\Script\Python\test.py", line 45, in main() File "s:\tmiller\Script\Python\test.py", line 39, in main df['dv01'] = df.apply(lambda row : get_dv01(row['bond_lookup'],row['midprice'], row['bbg_date']), axis = 1) File "C:\Users\timmiller\Anaconda3\envs\singlesdash\lib\site-packages\pandas\core\frame.py", line 8740, in apply return op.apply() File "C:\Users\timmiller\Anaconda3\envs\singlesdash\lib\site-packages\pandas\core\apply.py", line 688, in apply return self.apply_standard() File "C:\Users\timmiller\Anaconda3\envs\singlesdash\lib\site-packages\pandas\core\apply.py", line 812, in apply_standard results, res_index = self.apply_series_generator() File "C:\Users\timmiller\Anaconda3\envs\singlesdash\lib\site-packages\pandas\core\apply.py", line 828, in apply_series_generator results[i] = self.f(v) File "s:\tmiller\Script\Python\test.py", line 39, in df['dv01'] = df.apply(lambda row : get_dv01(row['bond_lookup'],row['midprice'], row['bbg_date']), axis = 1) File "s:\tmiller\Script\Python\test.py", line 27, in get_dv01 bquery = blp.BlpQuery().start() File "C:\Users\timmiller\Anaconda3\envs\singlesdash\lib\site-packages\blp\blp.py", line 546, in start raise ConnectionError(f"Failed to start {self!r}") ConnectionError: Failed to start <class 'blp.blp.BlpQuery'> with

For reference owscipy is a self developed module to pull database data via SQL into a dataframe, of which frame is the function used to pull it and context is the connection profile. To make it replicable, I attached the exports of these dataframes. I'm not worried about the SQL part, as it works when there's only 100 entries.

Expected Output

top_100_examples.xlsx top_3k_examples.xlsx

matthewgilbert commented 1 year ago

This looks like a Bloomberg specific question related to blpapi not the blp wrapper. I would contact Bloomberg help for this.

gandhis1 commented 1 year ago

Only open the BLPQuery once, and make multiple bdp calls. The way you have it now, you are unnecessarily re-initializing it every single time.

matthewgilbert / blp