1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
import pandas as pd
import datetime
from sqlalchemy import create_engine
if __name__=="__main__":
engine = create_engine('postgresql://et_user@debian/ET')
data1 = pd.read_sql("""SELECT dealname, "Curr Collat Bal" AS bal, "Latest Update" AS date
FROM clo_universe""", engine, parse_dates=["date"], index_col=["dealname", "date"])
data1bis = pd.read_sql("""SELECT dealname, "Curr Deal Bal" AS bal, "Latest Update" AS date
FROM clo_universe""", engine, parse_dates=["date"], index_col=["dealname", "date"])
data2 = pd.read_sql("""SELECT dealname, sum(currentbalance) AS bal, updatedate
AS date FROM et_collateral GROUP BY dealname, updatedate""", engine,
parse_dates=["date"], index_col=["dealname", "date"])
data3 = pd.read_sql("""with cte AS (SELECT distinct on (dealname, updatedate, tranche)
dealname, updatedate, curr_balance FROM cusip_universe
WHERE type not in ('MODELING', 'Combination', 'Unspecified', 'JUN_FIX_IO')
ORDER BY dealname, updatedate, tranche)
SELECT dealname, updatedate AS date, SUM(curr_balance) AS bal FROM cte
GROUP BY dealname, updatedate ORDER BY dealname, updatedate""", engine,
parse_dates=["date"], index_col=["dealname", "date"])
data12 = pd.merge(data1, data2, left_index=True, right_index=True)
data13 = pd.merge(data1bis, data3, left_index=True, right_index=True)
print(data13.query('bal_x-bal_y>1|bal_x-bal_y<-1').sortlevel(level=1))
|
