Demo entry 6646957

qs<fqsf

   

Submitted by anonymous on Oct 18, 2017 at 15:25
Language: Python 3. Code size: 7.9 kB.

# coding: utf-8

# In[1]:

import pandas as pd
import multiprocessing as mp
import numpy as np
import os as os
import datetime as dt


# In[3]:

# parameters
situation_Date = dt.datetime.strptime('01/06/2017', '%d/%m/%Y')




# In[275]:
#j'ai rajouté un paramètre a csv_read pour skipper les lignes après la 10ème
with open('C:/UserTemp/acco_input_may17.csv',newline = '') as f:
    df = pd.read_csv(f,sep=",",parse_dates=[2,24,25,29],dayfirst=True,thousands=' ',decimal='.',skiprows=range(10, 10000))

with open('C:/UserTemp/scenarios_12M_may17.csv',newline = '') as f1:
    scen = pd.read_csv(f1,sep=",",decimal='.')


#with open('scenarios_3M.csv',newline = '') as f2:
#    scen_3M = pd.read_csv(f2,delimiter=";",sep=";",decimal=',')

#with open('scenarios_DF.csv',newline = '') as f3:
#    scen_DF = pd.read_csv(f3,delimiter=";",sep=";",decimal=',')


# In[94]:


df.fillna(0,inplace=True)


# In[76]:

nrMonths = scen.shape[1]-1
nrPool = len(df)


# In[77]:

dates = []
for i in range(0,nrMonths):
    dates.append(dt.datetime.strptime(scen.columns[i+1],'%d/%m/%Y'))

size = [nrPool,nrMonths]    
print(size)
outstanding = np.zeros(size)
capped_client_rate = np.zeros(size)
fixings_done = np.zeros(size)
max_remaining_payments = np.zeros(size)
maturity_cap_euribor = np.zeros(size)
applicable_cap_euribor = np.zeros(size)
client_rate_floored = np.zeros(size)
payoff_cap = np.zeros(size)
linear_part_flag = np.zeros([nrPool,12])


# In[202]:

## Setting arrays that do not depend on rate scenario
df['max maturity'] = 18
df.loc[df['Initial maturity in years'] == 20,['max maturity']] = 25
df.loc[df['Initial maturity in years'] == 25,['max maturity']] = 30

annuity = df['Current annuity'].values

max_remaining_payments[:,0] = df['max maturity']*12 - df['Number of payments made'].astype(int)
for j in range(1,nrMonths):    
    max_remaining_payments[:,j] = max_remaining_payments[:,j-1]-1
    
max_remaining_payments[np.where(max_remaining_payments<0)] = 0

fixings_done[:,0] = pd.DatetimeIndex(df['Last fixing date']).year - pd.DatetimeIndex(df['Start Date']).year
                 
for j in range(1,nrMonths):
    fixings_done[:,j] = np.where(pd.DatetimeIndex(df['Start Date']).month == dates[j].month,fixings_done[:,j-1]+1,fixings_done[:,j-1])


# In[ ]:

margin = df['spread all-in'] + df['commercial margin']

writer = pd.ExcelWriter('C:/UserTemp/summary.xlsx', engine='xlsxwriter')
listofscen = [506]
for s in listofscen[0:]:
    print (s)
    rate_scen = scen.values[s-1,1:]
    #si on veut tourner le scen +10Bp
    #rate_scen = scen.values[s-1,1:]+0.001
    
    # j'ai mis les 2 lignes suivante en comment car je je ne tounre pas l'imput de ca en haut et c'est pas utilisé je crois si je laissais ca bloquait le code
    # rate_scen_3M = scen_3M.values[s-1,1:]
    # rate_DF = scen_DF['0'].values
    
    monthly_payoff = np.zeros([nrMonths])
    wav_capped_client_rate = np.zeros([nrMonths])
    wav_client_rate_floored = np.zeros([nrMonths])
    total_outstanding = np.zeros([nrMonths])
    sum_linear_amount = np.zeros([nrMonths])
    monthly_pnl = np.zeros([nrMonths])
    matu_cap_eurib_count_nan_per_pool = np.zeros([nrPool,1])
    
    for j in range(0,nrMonths): #nrMonths
        print('Month ' + str(j))
        if j==0:
            outstanding[:,j] = df['Avg Outstanding at the situation month']
            for i in range(0,nrPool):
                maturity_cap_euribor[i,j] = np.power(1+np.rate(max_remaining_payments[i,j],-annuity[i],-outstanding[i,j],0,tol=5e-6,maxiter=50),12)-1-margin[i]
            
        else:
            m_rates = np.power(1+capped_client_rate[:,j-1],1/12) - 1
            outstanding[:,j] = np.maximum(outstanding[:,j-1]*(1+m_rates) + annuity,0)
            for i in range(0,nrPool):
                if ((fixings_done[i,j]>fixings_done[i,j-1]) and (~np.isnan(maturity_cap_euribor[i,j-1]))):
                    maturity_cap_euribor[i,j] = np.power(1+np.rate(max_remaining_payments[i,j],-annuity[i],-outstanding[i,j],0,guess=maturity_cap_euribor[i,j-1],tol=5e-6,maxiter=15),12)-1-margin[i]
                
                    #maturity_cap_euribor[i,j] = 0.01
                else:  
                    maturity_cap_euribor[i,j] = maturity_cap_euribor[i,j-1]
                    
  
        
        maturity_cap_euribor[:,j] = np.where(np.isnan(maturity_cap_euribor[:,j]),1.0,maturity_cap_euribor[:,j])
        maturity_cap_euribor[:,j] = np.where(maturity_cap_euribor[:,j]<0.0,1.0,maturity_cap_euribor[:,j])
        
        rate_caps_1 = np.where(fixings_done[:,j] == 1,df['Cap on euribor year 1'],df['Cap on euribor year 3 & after'])
        rate_caps_2 = np.where(fixings_done[:,j] == 2,df['Cap on euribor year 2'],rate_caps_1)
        applicable_cap_euribor[:,j] = np.min(np.column_stack((rate_caps_2,maturity_cap_euribor[:,j])),axis=1)#,df['']+df['']) # 
         
    
    
     
    
        if j==0:
            capped_client_rate[:,j] = df['Annual Rate of the Current Coupon']
            client_rate_floored[:,j] = df['Annual Rate of the Current Coupon']
            payoff_cap[:,j] = np.zeros([nrPool])
        else:
            capped_client_rate[:,j] = np.where(pd.DatetimeIndex(df['Start Date']).month == dates[j].month, margin+np.minimum(applicable_cap_euribor[:,j],rate_scen[j]),capped_client_rate[:,j-1])
            client_rate_floored[:,j] = np.where(pd.DatetimeIndex(df['Start Date']).month == dates[j].month, margin+rate_scen[j],client_rate_floored[:,j-1])
            payoff_cap[:,j] = np.where(pd.DatetimeIndex(df['Start Date']).month == dates[j].month, np.maximum(rate_scen[j]-applicable_cap_euribor[:,j],0.0),payoff_cap[:,j-1])
        
        monthly_payoff[j] = np.dot(payoff_cap[:,j],outstanding[:,j])/12
        total_outst = np.sum(outstanding[:,j])
        wav_capped_client_rate[j] = np.dot(capped_client_rate[:,j],outstanding[:,j])/total_outst
        wav_client_rate_floored[j] = np.dot(client_rate_floored[:,j],outstanding[:,j])/total_outst
        total_outstanding[j] = np.sum(outstanding[:,j])
    
    av_payoff_cap = np.divide(monthly_payoff,total_outstanding) 
       
    linear_part_flag[:,0] = np.ones([nrPool])
    for j in range(1,12):
        linear_part_flag[:,j] = np.where(pd.DatetimeIndex(df['Start Date']).month == dates[j].month,0,linear_part_flag[:,j-1])
        sum_linear_amount[j] = np.dot(linear_part_flag[:,j],outstanding[:,j]) 
    
    
    p1 = np.multiply(total_outstanding,np.power(1+wav_client_rate_floored,1/12)-1.)
    p2 = np.multiply(total_outstanding - sum_linear_amount, np.power(1+wav_capped_client_rate,1/12)-1.)
    monthly_pnl = p1 - p2 + monthly_payoff
    monthly_pnl_accordeon = monthly_pnl - monthly_payoff

    #Export des diféretns indicateurs qui sortent
    nrMonths = scen.shape[1]-1
    nrPool = len(df)
    size = [nrMonths,11] 
    summary = np.zeros(size)
    summary= pd.DataFrame(summary) 
    summary.columns = ["Total Outstandingt scen" +str(s)  , "Avg payoff cap scen" +str(s), "monthly payoff scen" +str(s), "monthly pnl scen" +str(s)
                , "monthly pnl accordeon scen" +str(s), "linear amount" +str(s), "wav_capped_client_rat" +str(s), "wav_FLOOREDd_client_rat" +str(s), "rate_scen" +str(s), "p1", "p2"]
    summary.values[:,0] = total_outstanding[:]
    summary.values[:,1] = av_payoff_cap[:]
    summary.values[:,2] = monthly_payoff[:]
    summary.values[:,3] = monthly_pnl[:]
    summary.values[:,4] = monthly_pnl_accordeon[:]
    summary.values[:,5] = sum_linear_amount[:]
    summary.values[:,6] = wav_capped_client_rate[:]
    summary.values[:,7] = wav_client_rate_floored[:]
    summary.values[:,8] = rate_scen[:]
    summary.values[:,9] = p1[:]
    summary.values[:,10] = p2[:]
    
   
    summary.to_excel(writer, sheet_name= str(s))


writer.save()

This snippet took 0.01 seconds to highlight.

Back to the Entry List or Home.

Delete this entry (admin only).