"""
Created on Fri March 29 2019

@author: PROT

The BIS securitisation framework d374 (11 December 2014 (rev. July 2016)) applies also to FINMA (Ord. 952.03 and circ. 17/7)

"""
__author__ = 'PROT'

import pandas as pd
import numpy as np


df_pd = pd.read_excel(r'C:\Users\prot\Desktop\SVN\trunk\CrKappa\Raw_Data_Test.xlsx', sheet_name='Raw_Data')


#Calculation of Attachment point A of the tranches
df_pd['cumsum'] = (df_pd
                           .sort_values(by=['Tranche_subordination_structure_securitisation_pool', 'Tranche'], ascending = False)
                           .groupby(['Securitisation_pool_number'])
                           ['Securitisation_tranche_total_current_balance_ pool'].apply(lambda x: x.cumsum()))


#To sort values when needed, in pool and tranches order
df_pd = df_pd.sort_values(by=['Securitisation_pool_number', 'Tranche_subordination_structure_securitisation_pool', 'Tranche'], ascending = True)

df_pd['AColexc'] = df_pd.groupby(['Securitisation_pool_number', 'Tranche_subordination_structure_securitisation_pool'])['cumsum'].transform('max') - df_pd.groupby(['Securitisation_pool_number', 'Tranche_subordination_structure_securitisation_pool'])['Securitisation_tranche_total_current_balance_ pool'].transform('sum')

SumBalance = df_pd.groupby(['Securitisation_pool_number'])['Securitisation_tranche_total_current_balance_ pool'].transform('sum')

df_pd['A'] = df_pd['AColexc']/SumBalance

#Calculation of Detachment point D of the tranches
SumBalanceTranche = df_pd.groupby(['Securitisation_pool_number', 'Tranche_subordination_structure_securitisation_pool'])['Securitisation_tranche_total_current_balance_ pool'].transform('sum')

df_pd['D'] = df_pd['A'] + SumBalanceTranche/SumBalance

#Calculation of parameters for KSSFA and RW calculation
p = 1 - 0.5 * np.array(df_pd['STC_compliant_securitisation'] == 'Yes')
df_pd['p'] = p

A = df_pd['A']
D = df_pd['D']

W = df_pd['Percentage_delinquent_underlying_exposure_securitisation_pool']
df_pd['W'] = W

RWavg = df_pd['RW_average_underlying_pool']
RWWavg = df_pd['RW_average_delinquent']
df_pd['RWavg'] = RWavg
df_pd['RWWavg'] = RWWavg

for i in range(0,len(df_pd)):
 if np.isnan(df_pd.loc[i,'RW_average_underlying_pool']):
  df_pd.loc[i,'RWavg'] = 1
 else:
  df_pd.loc[i,'RWavg'] = df_pd.loc[i,'RW_average_underlying_pool']
 if np.isnan(df_pd.loc[i,'RW_average_delinquent']):
  df_pd.loc[i,'RWWavg'] = 1.5
 else:
  df_pd.loc[i,'RWWavg'] = df_pd.loc[i,'RW_average_delinquent']

RWavg = df_pd['RWavg']
RWWavg = df_pd['RWWavg']

Ksa = ((1 - W)*RWavg*0.08 + W*RWWavg*0.08)
df_pd['Ksa'] = Ksa
KA = ((1-W)*Ksa + W*0.5)
df_pd['KA'] = KA

a = (-(1/(p*KA)))
df_pd['a'] = a
u = D - KA
df_pd['u'] = u
I = np.maximum((A - KA), 0)
df_pd['I'] = I
KSSFA = ((np.exp(a*u) - np.exp(a*I))/(a*(u-I)))
df_pd['KSSFA'] = KSSFA

for i in range(0,len(df_pd)):
 if df_pd.loc[i,'Ratio_delinquency_status_unknown'] <= 0.05:
  KA = (df_pd.loc[i,'EAD_delinquent_exposure_known']/df_pd.loc[i,'EAD_Total'])* df_pd.loc[i,'KA'] + (df_pd.loc[i,'EAD_delinquent_exposure_unknown']/df_pd.loc[i,'EAD_Total'])
  df_pd.loc[i,'KA'] = KA
  df_pd.loc[i,'a'] = (-(1/(df_pd.loc[i,'p']*df_pd.loc[i,'KA'])))
  a = df_pd.loc[i,'a']
  df_pd.loc[i,'u'] = df_pd.loc[i,'D'] - df_pd.loc[i,'KA']
  u = df_pd.loc[i,'u']
  df_pd.loc[i,'I'] = np.maximum(df_pd.loc[i,'A'] - df_pd.loc[i,'KA'],0)
  I = df_pd.loc[i,'I']
  df_pd.loc[i,'KSSFA'] = ((np.exp(a*u) - np.exp(a*I))/(a*(u-I)))

 elif df_pd.loc[i,'Ratio_delinquency_status_unknown'] > 0.05:
  KA = ((1-df_pd.loc[i,'W'])*df_pd.loc[i,'Ksa'] + df_pd.loc[i,'W']*0.5) * 12.5
  df_pd.loc[i,'KA'] = KA
  df_pd.loc[i,'a'] = (-(1/(df_pd.loc[i,'p']*df_pd.loc[i,'KA'])))
  a = df_pd.loc[i,'a']
  df_pd.loc[i,'u'] = df_pd.loc[i,'D'] - df_pd.loc[i,'KA']
  u = df_pd.loc[i,'u']
  df_pd.loc[i,'I'] = np.maximum(df_pd.loc[i,'A'] - df_pd.loc[i,'KA'],0)
  I = df_pd.loc[i,'I']
  df_pd.loc[i,'KSSFA'] = ((np.exp(a*u) - np.exp(a*I))/(a*(u-I)))

for i in range(0,len(df_pd)):
 if df_pd.loc[i,'D'] <= df_pd.loc[i,'KA']:
  RW = 12.50
  df_pd.loc[i,'RW'] = RW
 if df_pd.loc[i,'Ratio_delinquency_status_unknown'] > 0.05:
  RW = 12.50
  df_pd.loc[i,'RW'] = RW
 elif df_pd.loc[i,'A'] >= df_pd.loc[i,'KA']:
  RW = np.maximum(df_pd.loc[i,'KSSFA']*12.50, 0.15)
  df_pd.loc[i,'RW'] = RW
 elif df_pd.loc[i,'A'] < df_pd.loc[i,'KA'] and df_pd.loc[i,'D'] > df_pd.loc[i,'KA']:
  RW = np.maximum((((df_pd.loc[i,'KA']-df_pd.loc[i,'A'])/(df_pd.loc[i,'D']-df_pd.loc[i,'A']))*12.50) + (((df_pd.loc[i,'D']-df_pd.loc[i,'KA'])/(df_pd.loc[i,'D']-df_pd.loc[i,'A']))*12.50*df_pd.loc[i,'KSSFA']), 0.15)
  df_pd.loc[i,'RW'] = RW
 #else:
  #RW = np.maximum(df_pd.loc[i,'KSSFA']*12.50, 0.15)
  #df_pd.loc[i,'RW'] = RW

#Calculation of RWA and Capital Charge for the securitisation portfolio
RWA = df_pd['Asset'] * df_pd['RW']
df_pd['RWA'] = RWA
Capital_Charge = df_pd['Asset'] * df_pd['KSSFA']
df_pd['Capital_Charge'] = Capital_Charge

df_pd.to_excel('temp.xls')
df_pd.to_excel('output.xls')
exit(1)

#print(df_pd[["Securitisation_tranche_total_current_balance_ pool", "Tranche_subordination_structure_securitisation_pool", "no_cumulative"]].head(4))
#pandas.DataFrame.drop to remove unwanted columns at the end