diff options
Diffstat (limited to 'python/bespoke_utils.py')
| -rw-r--r-- | python/bespoke_utils.py | 47 |
1 files changed, 28 insertions, 19 deletions
diff --git a/python/bespoke_utils.py b/python/bespoke_utils.py index 981fb483..a7aa60e8 100644 --- a/python/bespoke_utils.py +++ b/python/bespoke_utils.py @@ -1,5 +1,4 @@ from bbg_helpers import BBG_IP, init_bbg_session, retrieve_data -from db import serenitas_pool from psycopg2.extensions import connection from typing import List, Dict, Tuple import datetime @@ -11,7 +10,7 @@ def insert_bbg_tickers(conn: connection, tickers: List[str]) -> Dict[str, Tuple[ Parameters ---------- conn: psycopg2 connection - ticker: list of bloomberg ticker + ticker: list of bloomberg tickers Returns ------- @@ -44,7 +43,7 @@ def insert_bbg_tickers(conn: connection, tickers: List[str]) -> Dict[str, Tuple[ conn.commit() return d -def insert_bbg_markit_mapping(conn, d): +def insert_bbg_markit_mapping(conn: connection, d: Dict[str, Tuple[str, str]], df: pd.DataFrame): sql_str = ("INSERT INTO bbg_markit_mapping VALUES(%s, %s, %s, %s, %s, %s) " "ON CONFLICT DO NOTHING") with conn.cursor() as c: @@ -74,27 +73,37 @@ def backpopulate_short_codes(conn): for v in short_code.values() if 'restructuring_type_short_code' in v]) conn.commit() -def get_bbg_ids(conn: connection, date: datetime.date): - df = pd.read_sql_query("SELECT company_id, markit_ticker, markit_tier, " - "seniority FROM historical_cds_issuers(%s) " - "WHERE markit_ticker IS NOT NULL", - conn, params=(date,), index_col=["markit_ticker", "markit_tier"]) - df2 = pd.read_clipboard(header=None) - df2.columns = ["markit_ticker", "markit_tier"] - df2 = df2.set_index(["markit_ticker", "markit_tier"]) - return df2.join(df).groupby(level=["markit_ticker", "markit_tier"]).first() +def get_bbg_ids(conn: connection, df: pd.DataFrame = pd.read_clipboard(header=None), + date: datetime.date = datetime.date.today()): + df_mapping = pd.read_sql_query("SELECT company_id, markit_ticker, markit_tier, " + "seniority FROM historical_cds_issuers(%s) " + "WHERE markit_ticker IS NOT NULL", + conn, params=(date,), + index_col=["markit_ticker", "markit_tier"]) + df.columns = ["markit_ticker", "markit_tier"] + df = df.set_index(["markit_ticker", "markit_tier"]) + df = df.join(df_mapping).groupby(level=["markit_ticker", "markit_tier"]).first() + df.company_id = df.company_id.astype("Int64") + return df -def add_basketid(conn: connection, df: pd.DataFrame, basketid: int): - for company_id, seniority in df.itertuples(index=False): - with conn.cursor() as c: - c.execute("UPDATE bbg_issuers set index_list=index_list||%s" - "WHERE company_id=%s AND seniority=%s", - (basketid, company_id, seniority)) - conn.commit() +def add_basketid(conn: connection, basketid: int, + df: pd.DataFrame = pd.read_clipboard(header=None)): + + df_bbg = get_bbg_ids(conn, df.iloc[:, :2]) + df.columns = ["markit_ticker", "markit_tier", "weight"] + df = df.set_index(["markit_ticker", "markit_tier"]) + df = df.join(df_bbg) + with conn.cursor() as c: + c.executemany("INSERT INTO basket_constituents VALUES(%s, %s, %s, %s)", + [(t.company_id, t.seniority, basketid, t.weight) + for t in df.itertuples(index=False)]) + conn.commit() if __name__ == "__main__": df = pd.read_clipboard(header=None) bbg_tickers = df[1].to_list() + from db import serenitas_pool conn = serenitas_pool.getconn() d = insert_bbg_tickers(conn, bbg_tickers) insert_bbg_markit_mapping(conn, d) + serenitas_pool.putconn(conn) |