diff options
Diffstat (limited to 'python/bespoke_utils.py')
| -rw-r--r-- | python/bespoke_utils.py | 24 |
1 files changed, 18 insertions, 6 deletions
diff --git a/python/bespoke_utils.py b/python/bespoke_utils.py index 19ac4919..2ff93e59 100644 --- a/python/bespoke_utils.py +++ b/python/bespoke_utils.py @@ -54,7 +54,7 @@ def insert_bbg_markit_mapping(conn: connection, d: Dict[str, Tuple[str, str]], d mkit_tier, 100, seniority)) conn.commit() -def backpopulate_short_codes(conn): +def backpopulate_short_codes(conn: connection): with conn.cursor() as c: c.execute("SELECT cds_curve[6] from bbg_issuers") securities = [f"{t} Corp" for t, in c] @@ -74,7 +74,7 @@ def backpopulate_short_codes(conn): conn.commit() def get_bbg_ids(conn: connection, df: pd.DataFrame, - date: datetime.date = datetime.date.today()): + date: datetime.date = datetime.date.today()) -> pd.DataFrame: 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", @@ -86,8 +86,19 @@ def get_bbg_ids(conn: connection, df: pd.DataFrame, df.company_id = df.company_id.astype("Int64") return df -def add_basketid(conn: connection, basketid: int, - df: pd.DataFrame = pd.read_clipboard(header=None)): +def get_basketid(conn: connection, name: str) -> Tuple[int, int]: + with conn.cursor() as c: + c.execute("SELECT max(series)+1 FROM index_version where index='BS'") + serie, = c.fetchone() + c.execute("INSERT INTO index_version(index, series, version, redindexcode) " + "VALUES('BS', %s, 1, %s) RETURNING basketid", + (serie, name)) + basket_id, = c.fetchone() + conn.commit() + return serie, basket_id + +def add_basket_constituents(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"] @@ -106,7 +117,8 @@ if __name__ == "__main__": df = get_bbg_ids(conn, pd.read_clipboard(header=None)) #missing tickers missing_tickers = df[df.company_id.isnull()] - bbg_tickers = df[1].to_list() + df_mapping = pd.read_clipboard(header=None) + bbg_tickers = df_mapping[1].to_list() d = insert_bbg_tickers(conn, bbg_tickers) - insert_bbg_markit_mapping(conn, d) + insert_bbg_markit_mapping(conn, d, df_mapping) serenitas_pool.putconn(conn) |