diff options
| -rw-r--r-- | python/position.py | 13 | ||||
| -rw-r--r-- | sql/dawn.sql | 27 |
2 files changed, 40 insertions, 0 deletions
diff --git a/python/position.py b/python/position.py index da2fa4b8..5f2d3da0 100644 --- a/python/position.py +++ b/python/position.py @@ -22,6 +22,19 @@ def get_list(engine, workdate=None, asset_class=None, include_unsettled=True): positions.set_index('bbg_id', inplace=True) return positions +def get_list_range(engine, begin, end, asset_class=None): + begin = pd.Timestamp(begin).date() + end = pd.Timestamp(end).date() + positions = pd.read_sql_query("select identifier, bbg_type from list_positions_range(%s, %s, %s)", + engine, + params=(begin, end, asset_class)) + positions.loc[positions.identifier.str.len() <= 11, 'cusip'] = positions.identifier.str.slice(stop=9) + positions.loc[positions.identifier.str.len() == 12, 'isin'] = positions.identifier + positions['bbg_id'] = positions.cusip.where(positions.cusip.notnull(), positions['isin']) + \ + ' ' + positions.bbg_type + positions.set_index('bbg_id', inplace=True) + return positions + def backpopulate_marks(begin_str='2015-01-15', end_str='2015-07-15'): pattern = re.compile("\d{4}-\d{2}-\d{2}") list_of_daily_folder = (fullpath for (fullpath, _, _) in os.walk('/home/share/Daily') diff --git a/sql/dawn.sql b/sql/dawn.sql index e57ea3cc..fe0feeb0 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -241,6 +241,33 @@ BEGIN END; $$ LANGUAGE plpgsql; +CREATE OR REPLACE function list_positions_range(start_date date, + end_date date, + p_class asset_class DEFAULT NULL) +RETURNS TABLE(identifier varchar(12), description varchar(32), notional float, strategy bond_strat, + curr_cpn float, start_accrued_date date, last_settle_date date, + principal_payment float, accrued_payment float, currency currency, daycount day_count, + bbg_type bbg_type) AS $$ +DECLARE sqlquery text; +DECLARE asset_opt text; +DECLARE unsettled_opt text; +BEGIN + + sqlquery := 'WITH temp AS (SELECT bonds.identifier, asset_class, settle_date, folder, + principal_payment, accrued_payment, sum(faceamount*(2*buysell::int-1) ) + OVER (PARTITION BY bonds.identifier) notional FROM bonds + WHERE trade_date <=$2) + SELECT DISTINCT ON (temp.identifier) temp.identifier, securities.description, notional, folder, + securities.coupon, start_accrued_date, settle_date, temp.principal_payment, + temp.accrued_payment, securities.currency, securities.day_count, securities.bbg_type + FROM temp LEFT JOIN securities USING (identifier) + WHERE (temp.notional>0 or (temp.notional==0 and settle_date>=$1)) AND paid_down>$1 '||asset_opt + ||' ORDER BY identifier, settle_date desc'; + RETURN QUERY EXECUTE sqlquery USING start_date, end_date, p_class; +END; +$$ LANGUAGE plpgsql; + + CREATE OR REPLACE function risk_positions(p_date date, p_assetclass asset_class) RETURNS TABLE (description varchar(32), identifier varchar(12), notional float, price float, strategy bond_strat, factor float, local_market_value float, usd_market_value float, |