1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
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("""SELECT dealname, sum(curr_balance) AS bal, updatedate AS date
FROM cusip_universe GROUP BY dealname, updatedate""", engine, 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(data12.query('bal_x-bal_y>1|bal_x-bal_y<-1').sortlevel(level=1))
|
