import pandas as pd
from serenitas.utils.db import dbconn

dawndb = dbconn("dawndb")


# Bonds

df = pd.read_excel("/home/serenitas/flint/rec.xlsx")

security_balance = df[df["Asset Type"] == "FIXED INCOME SECURITIES"][
    "Base Market Value"
].sum()
print(f"The current security balance is {security_balance}")

bowd_bond_trades = df[df["CUSIP"].notnull()]
db_bond_trades = pd.read_sql_query(
    "select * from risk_positions('2021-02-25', null, 'BOWDST')", dawndb
)

bowd_bond_trades.merge(
    db_bond_trades, left_on="Mellon Security ID", right_on="identifier", how="outer"
)[
    [
        "description",
        "identifier",
        "notional",
        "factor",
        "Shares/Par",
        "Base Market Value",
        "usd_market_value",
    ]
]

# market value differences