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))