from index_data import get_index_quotes, index_returns
import pandas as pd
import math

## look at spreads
df = get_index_quotes("IG", [23, 24, 25, 26, 27], tenor=['3yr', '5yr', '7yr', '10yr'])
spreads = df.groupby(level=['date', 'tenor']).nth(-1)['closespread'].unstack(-1)
# remove 'yr'
spreads.columns = [int(col[:-2]) for col in spreads.columns]
spreads = spreads.sort_index(1)
spreads_diff = spreads.diff(axis=1)
spreads_diff = spreads_diff.filter([5, 7, 10])
spreads_diff.columns = ['3-5', '5-7', '7-10']
spreads_diff.plot()

## look at returns
df = index_returns(index='IG', series=[24, 25, 26, 27, 28], tenor=['3yr', '5yr', '7yr', '10yr'])
## on-the-run returns
returns = df.price_return.unstack(-1).dropna().groupby(level='date').nth(-1)
strategy510 = 1.78 * returns['5yr'] - returns['10yr']
strategy710 = 1.33 * returns['7yr'] - returns['10yr']
strategy3510 = -2 * returns['3yr']+ 3 * returns['5yr'] - 1 * returns['10yr']

monthly_returns510 = strategy510.groupby(pd.TimeGrouper(freq='M')).agg(lambda df:(1+df).prod()-1)
monthly_returns710 = strategy710.groupby(pd.TimeGrouper(freq='M')).agg(lambda df:(1+df).prod()-1)
monthly_returns3510 = strategy3510.groupby(pd.TimeGrouper(freq='M')).agg(lambda df:(1+df).prod()-1)

sharpe510 = strategy510.mean()/strategy510.std()*math.sqrt(252)
sharpe710 = strategy710.mean()/strategy710.std()*math.sqrt(252)
sharpe3510 = strategy3510.mean()/strategy3510.std()*math.sqrt(252)

monthly_sharpe510 = monthly_returns510.mean()/monthly_returns510.std()*math.sqrt(12)
monthly_sharpe710 = monthly_returns710.mean()/monthly_returns710.std()*math.sqrt(12)
monthly_sharpe3510 = monthly_returns3510.mean()/monthly_returns3510.std()*math.sqrt(12)

worst_drawdown510 = strategy510.nsmallest(10)
worst_drawdown710 = strategy710.nsmallest(10)
worst_drawdown3510 = strategy3510.nsmallest(10)

def forward_loss():
    from db import dbengine, dbconn
    serenitasdb = dbengine('serenitasdb')
    index = 'IG'
    start_date = (pd.Timestamp.now() - pd.DateOffset(years=3)).date()

    df = pd.read_sql_query("SELECT date, index, series, tenor, duration, closespread, closespread*duration/100 as indexel " \
                           "FROM index_quotes WHERE index=%s AND date >= %s " \
                           "ORDER BY date DESC, series ASC, duration ASC",
                           serenitasdb, parse_dates=['date'], params=[index, start_date])
    df1 = pd.read_sql_query("select index, series, tenor, maturity from index_maturity", serenitasdb, parse_dates=['maturity'])

    df = df.merge(df1, on=['index','series','tenor'])
    df = df.set_index(['date','index', 'maturity']).dropna()
    df = df.groupby(level=['date','index', 'maturity']).nth(-1)
    # annual change, to take out some noise
    df['fwd_loss_rate'] = df.indexel.diff(2)/df.duration.diff(2)