diff options
| -rw-r--r-- | python/position.py | 4 | ||||
| -rw-r--r-- | python/risk/bonds.py | 6 | ||||
| -rw-r--r-- | sql/dawn.sql | 8 |
3 files changed, 7 insertions, 11 deletions
diff --git a/python/position.py b/python/position.py index 3bb6b67b..83bff28f 100644 --- a/python/position.py +++ b/python/position.py @@ -18,7 +18,7 @@ def get_list( ): if workdate: positions = pd.read_sql_query( - "SELECT identifier, bbg_type FROM list_positions(%s, %s, %s, %s)", + "SELECT identifier, figi, bbg_type FROM list_positions(%s, %s, %s, %s)", engine, params=(workdate, asset_class, include_unsettled, fund), ) @@ -299,7 +299,7 @@ def populate_cashflow_history(engine, session, workdate=None, funds=("SERCGMAST" secs = get_list(engine, workdate, fund=fund) for sec in secs.itertuples(): if sec.Index not in securities: - securities[sec.Index] = sec.identifier + securities[sec.Index] = sec.figi data = retrieve_data( session, securities, diff --git a/python/risk/bonds.py b/python/risk/bonds.py index 56069b6a..7e0de99a 100644 --- a/python/risk/bonds.py +++ b/python/risk/bonds.py @@ -221,14 +221,10 @@ def get_portfolio(date, conn, asset_class: AssetClass, fund="SERCGMAST"): conn, params=(date, asset_class.name, fund), ) - with conn.cursor() as c: - c.execute("SELECT identifier, figi FROM securities") - figi_map = {identifier: figi for identifier, figi in c.fetchall()} - df["figi"] = df["identifier"].replace(figi_map) if asset_class == AssetClass.CLO: return df.set_index("figi") else: - return df.set_index("cusip") + return df.set_index("identifier") def crt_risk(date, dawn_conn, crt_engine, fund="SERCGMAST"): diff --git a/sql/dawn.sql b/sql/dawn.sql index 1c373cb6..486bbad6 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -939,7 +939,7 @@ CREATE OR REPLACE function risk_positions(p_date date, (description varchar(32), identifier varchar(12), notional float, price float, strategy bond_strat, factor float, local_market_value float, usd_market_value float, curr_cpn float, int_acc float, last_pay_date date, principal_payment float, -accrued_payment float, last_settle_date date, cusip varchar(9)) AS $$ +accrued_payment float, last_settle_date date, figi varchar(12)) AS $$ BEGIN RETURN QUERY SELECT a.description, a.identifier, a.notional, c.price, a.strategy, coalesce(b.factor, 1), @@ -948,10 +948,10 @@ BEGIN b.coupon, a.notional * coalesce(b.factor,1) * fxrate * yearfrac(case WHEN start_accrued_date>=p_date+1 THEN b.prev_cpn_date ELSE start_accrued_date END, p_date+1, daycount) * b.coupon/100., - b.last_pay_date, a.principal_payment, a.accrued_payment, a.last_settle_date, a.cusip + b.last_pay_date, a.principal_payment, a.accrued_payment, a.last_settle_date, a.figi FROM list_positions(p_date, p_assetclass, true, p_fund) a - LEFT JOIN factors_history(p_date) b USING (identifier) - LEFT JOIN list_marks(p_date) c USING (identifier) + LEFT JOIN factors_history(p_date) b ON a.figi = b.identifier + LEFT JOIN list_marks(p_date) c ON a.figi = c.identifier LEFT JOIN fx_rate(p_date) USING (currency) ORDER by identifier asc; END; |