Переделать процентный своп в валютный
Подскажите, у меня вот есть код, написанный общими силами, поэтому немного не понимаю его. Есть несколько xml файлов с данными валют. И считающий процентный своп, а мне нужно переделать его в обычный валютный. Может подскажете, как исправить этот код
import numpy as np
import pandas as pd
from scipy.interpolate import interp1d
import os
from datetime import datetime
import glob
value_date = datetime.strptime('09/12/2019', "%d/%m/%Y") # Дата оценки свопа
mosprime_shift = 0.01
dirr = os.path.abspath(os.curdir)
#автоматизировать выгрузку, когда будут данные в БД
mosprime_df = pd.read_excel(dirr + '\mosprime2.xlsx',sheet_name='Curve')
mosprime_df = pd.DataFrame(mosprime_df)
mosprime_df['+'] = mosprime_df['Mosprime']+mosprime_shift
mosprime_df['-'] = mosprime_df['Mosprime']-mosprime_shift
mosprime_df['Forward'] = 0
#здесь эксель файл со всеми сделками IRS
IRS_all_deals = pd.read_excel(dirr + '\swap_deals3.xlsx')
IRS_all_deals = pd.DataFrame(IRS_all_deals)
#Разбиваем на отдельные файлы для последовательного расчета
for i in IRS_all_deals.index:
w = (pd.DataFrame(IRS_all_deals.loc[i]))
w = w.transpose()
w.to_excel(dirr + '\\swaps\\deal_IRS_id_' +str(w['id'][i]) +'.xlsx')
#Берем каждую отдельную сделку в эксель и считаем потоки
path = dirr + '\swaps'
extension = 'xlsx'
os.chdir(path)
result = glob.glob('*.{}'.format(extension))
for files in result:
df = pd.read_excel(files)
total_rows_fixed = df['End years'] *df['Frequancy_fixed']
fixed = np.zeros((*total_rows_fixed, 13))
fixed = pd.DataFrame(fixed, columns=['Period', 'Days', 'Term', 'CF', 'DF', 'DF+', 'DF-', 'PV', 'PV+', 'PV-','PV * T','id','CF direction'])
curr_date = pd.to_datetime(*df['Start']) # Дата начала свопа
dates_fixed = []
for i in range(fixed.shape[0]):
if df['Frequancy_fixed'].values == 2:
curr_date = curr_date + pd.DateOffset(months = 6)
dates_fixed.append(curr_date)
elif df['Frequancy_fixed'].values == 1:
curr_date = curr_date + pd.DateOffset(months = 12)
dates_fixed.append(curr_date)
elif df['Frequancy_fixed'].values == 4:
curr_date = curr_date + pd.DateOffset(months = 3)
dates_fixed.append(curr_date)
fixed['Period'] = dates_fixed
fixed['Days'] = fixed['Period'] - value_date
fixed['Days'] = fixed['Days'].apply(lambda x: x.days)
fixed['Term'] = round(fixed['Days'] / 365, 2)
notional = df['Notional'].values[0]
fix_rate = df['Fix rate'].values[0]
float_rate = df['Float rate'].values[0] #последняя плавающая ставка. Нужно учесть что она меняться будет. Казна обновит?
spread = df['Spread'].values[0]
num_to_delete = []
check_rows = fixed['Days'].tolist()
for i in check_rows:
if i < 0:
num_to_delete.append(check_rows.index(i))
fixed = fixed.drop(num_to_delete)
fixed = fixed.reset_index()
fixed = fixed.drop('index', 1)
CFs = []
DFs = []
DFs_up = []
DFs_down = []
DFs_VaR = []
PVs = []
PVs_up = []
PVs_down = []
PVs_VaR = []
PVs_T = []
mosprime_df = pd.merge(mosprime_df, fixed['Term'], how='outer', on=['Term', 'Term'])
mosprime_df = mosprime_df.sort_values(by=['Term'],ascending=True)
mosprime_df = mosprime_df.reset_index()
mosprime_df = mosprime_df.drop('index', 1)
mosprime_df = mosprime_df.interpolate(method='linear', limit_direction='both', axis=0)
mosprime_df = mosprime_df.merge(fixed['Term'], on=['Term'], how='inner')
for i in fixed.index.values.tolist():
DFs.append((1 + mosprime_df['Mosprime'][mosprime_df['Term'].values.tolist().index(fixed['Term'][i])]) ** (-fixed['Days'][i] / 365))
DFs_up.append((1 + mosprime_df['+'][mosprime_df['Term'].values.tolist().index(fixed['Term'][i])]) ** (-fixed['Days'][i] / 365))
DFs_down.append((1 + mosprime_df['-'][mosprime_df['Term'].values.tolist().index(fixed['Term'][i])]) ** (-fixed['Days'][i] / 365))
# для 1-го потока
if i == 0:
CFs.append(notional * fix_rate * fixed['Days'][i] / 365)
PVs.append(CFs[i] * DFs[i])
PVs_up.append(CFs[i] * DFs_up[i])
PVs_down.append(CFs[i] * DFs_down[i])
# для последнего потока
elif i == fixed.index.values.tolist()[-1]:
CFs.append(notional + notional * fix_rate * (fixed['Days'][i] - fixed['Days'][i-1]) / 365)
PVs.append(CFs[i] * DFs[i])
PVs_up.append(CFs[i] * DFs_up[i])
PVs_down.append(CFs[i] * DFs_down[i])
# для промежуточных потоков
else:
CFs.append(notional * fix_rate * (fixed['Days'][i] - fixed['Days'][i-1]) / 365)
PVs.append(CFs[i] * DFs[i])
PVs_up.append(CFs[i] * DFs_up[i])
PVs_down.append(CFs[i] * DFs_down[i])
PVs_T.append(PVs[i] * fixed['Term'][i])
fixed['DF'] = DFs
fixed['DF+'] = DFs_up
fixed['DF-'] = DFs_down
fixed['CF'] = CFs
fixed['PV'] = PVs
fixed['PV+'] = PVs_up
fixed['PV-'] = PVs_down
fixed['PV * T'] = PVs_T
fixed['id'] = df['id'][0]
if df['Pay fixed'][0] == 1:
fixed['CF direction'] = -1
else:
fixed['CF direction'] = 1
FV_fixed = fixed['PV'].sum()
MD_fixed = fixed['PV * T'].sum() / fixed['PV'].sum() / (1 + fix_rate)
PV01_fixed = MD_fixed * FV_fixed / 10000
fixed['Fixed_PV01']= PV01_fixed
fixed['Fixed_FV'] = FV_fixed
fixed['Fixed_MD'] = MD_fixed
#FLOATING LEG
total_rows_floating = df['End years'] *df['Frequancy_floating']
floating = np.zeros((*total_rows_floating, 13))
floating = pd.DataFrame(floating, columns=['Period', 'Days', 'Term', 'CF', 'DF', 'DF+', 'DF-', 'PV', 'PV+', 'PV-', 'PV * T','id','CF direction'])
curr_date = pd.to_datetime(*df['Start']) # Дата начала свопа
dates_floating = []
for i in range(floating.shape[0]):
if df['Frequancy_floating'].values == 2:
curr_date = curr_date + pd.DateOffset(months = 6)
dates_floating.append(curr_date)
elif df['Frequancy_floating'].values == 1:
curr_date = curr_date + pd.DateOffset(months = 12)
dates_floating.append(curr_date)
elif df['Frequancy_floating'].values == 4:
curr_date = curr_date + pd.DateOffset(months = 3)
dates_floating.append(curr_date)
floating['Period'] = dates_floating
floating['Days'] = floating['Period'] - value_date
floating['Days'] = floating['Days'].apply(lambda x: x.days)
floating['Term'] = round(floating['Days'] / 365, 2)
num_to_delete = []
check_rows_floating = floating['Days'].tolist()
for i in check_rows_floating:
if i < 0:
num_to_delete.append(check_rows_floating.index(i))
floating = floating.drop(num_to_delete)
floating = floating.reset_index()
floating = floating.drop('index', 1)
CFs = []
DFs = []
DFs_up = []
DFs_down = []
DFs_VaR = []
PVs = []
PVs_up = []
PVs_down = []
PVs_VaR = []
PVs_T = []
#берем даты из кривой в соответствии с датами потоков
mosprime_df = pd.merge(mosprime_df, floating['Term'], how='outer', on=['Term', 'Term'])
mosprime_df = mosprime_df.sort_values(by=['Term'],ascending=True)
mosprime_df = mosprime_df.reset_index()
mosprime_df = mosprime_df.drop('index', 1)
mosprime_df = mosprime_df.interpolate(method='linear', limit_direction='both', axis=0)
mosprime_df = mosprime_df.merge(floating['Term'], on=['Term'], how='inner')
rate_1 = []
rate = []
term1 = []
term =[]
forward = []
for i in mosprime_df['Mosprime'][2:]:
rate_1.append(i)
for i in mosprime_df['Mosprime'][1:-1]:
rate.append(i)
for i in mosprime_df['Term'][2:]:
term1.append(i)
for i in mosprime_df['Term'][1:-1]:
term.append(i)
for i in range(0,len(term)):
forward.append((((1+rate_1[i])**term1[i])/((1+rate[i])**term[i])-1)*df['Frequancy_floating'][0])
mosprime_df['Forward'][1:-1] = forward
for i in floating.index.values.tolist():
DFs.append((1 + mosprime_df['Mosprime'][mosprime_df['Term'].values.tolist().index(floating['Term'][i])]) ** (-floating['Days'][i] / 365))
DFs_up.append((1 + mosprime_df['+'][mosprime_df['Term'].values.tolist().index(floating['Term'][i])]) ** (-floating['Days'][i] / 365))
DFs_down.append((1 + mosprime_df['-'][mosprime_df['Term'].values.tolist().index(floating['Term'][i])]) ** (-floating['Days'][i] / 365))
# для 1-го потока
if i == 0:
CFs.append(notional * (float_rate + spread) * floating['Days'][i] / 365)
PVs.append(CFs[i] * DFs[i])
PVs_up.append(CFs[i] * DFs_up[i])
PVs_down.append(CFs[i] * DFs_down[i])
# для последнего потока
elif i == floating.index.values.tolist()[-1]:
CFs.append(notional + notional * (spread + mosprime_df['Forward'][mosprime_df['Term'].values.tolist().index(floating['Term'][i-1])]) * (floating['Days'][i] - floating['Days'][i-1]) / 365)
PVs.append(CFs[i] * DFs[i])
PVs_up.append(CFs[i] * DFs_up[i])
PVs_down.append(CFs[i] * DFs_down[i])
# для промежуточных потоков
else:
CFs.append(notional * (spread + mosprime_df['Forward'][mosprime_df['Term'].values.tolist().index(floating['Term'][i])]) * (floating['Days'][i] - floating['Days'][i-1]) / 365)
PVs.append(CFs[i] * DFs[i])
PVs_up.append(CFs[i] * DFs_up[i])
PVs_down.append(CFs[i] * DFs_down[i])
PVs_T.append(PVs[i] * floating['Term'][i])
floating['DF'] = DFs
floating['DF+'] = DFs_up
floating['DF-'] = DFs_down
floating['CF'] = CFs
floating['PV'] = PVs
floating['PV+'] = PVs_up
floating['PV-'] = PVs_down
floating['PV * T'] = PVs_T
floating['id'] = df['id'][0]
if df['Pay fixed'][0] == 1:
floating['CF direction'] = 1
else:
floating['CF direction'] = -1
FV_float = floating['PV'].sum()
MD_float = floating['PV * T'][0] / floating['PV'][0] / (1 + float_rate + spread)
PV01_float = MD_float * FV_float / 10000
floating['Float_PV01']= PV01_float
floating['Float_FV'] = FV_float
floating['Float_MD'] = MD_float
print(floating)
fixed.to_excel(dirr + '\\output_CFs\\Fixed_leg_IRS_id_' +str(fixed['id'][0]) +'.xlsx')
floating.to_excel(dirr + '\\output_CFs\\Floating_leg_IRS_id_' +str(fixed['id'][0]) +'.xlsx')
#METRICS
MD_fixed_payer = MD_fixed - MD_float
MD_floating_payer = MD_float - MD_fixed
PV01_fixed_payer = PV01_fixed - PV01_float
PV01_float_payer = PV01_float - PV01_fixed
if fixed['CF direction'][0] == 1:
d = {'FV_IRS': [FV_fixed - FV_float],'MD_IRS': [MD_fixed_payer],
'PV01_IRS': [PV01_fixed_payer], 'id': [fixed['id'][0]],'Notional':[notional]}
else:
d = {'FV_IRS': [FV_float - FV_fixed],'MD_IRS': [MD_floating_payer],
'PV01_IRS': [PV01_float_payer], 'id': [fixed['id'][0]],'Notional':[notional]}
IRS = pd.DataFrame(data=d)
IRS.to_excel(dirr + '\\output_FV\\FV_IRS_id_' +str(IRS['id'][0]) +'.xlsx')
#Сохраняем в разные папки потоки которые мы заплатим и которые нам заплатят
path = dirr + '\output_CFs'
extension = 'xlsx'
os.chdir(path)
result = glob.glob('*.{}'.format(extension))
for files in result:
df = pd.read_excel(files)
if df['CF direction'][0] == -1:
df.to_excel(dirr + '\\pay_leg\\CF_IRS_id_' +str(df['id'][0]) +'.xlsx')
else:
df.to_excel(dirr + '\\receive_leg\\CF_IRS_id_' +str(df['id'][0]) +'.xlsx')
#объединяем все потоки которые заплатим
CF_pay = []
path = dirr + '\pay_leg'
extension = 'xlsx'
os.chdir(path)
result = glob.glob('*.{}'.format(extension))
for files in result:
df = pd.read_excel(files)
CF_pay.append(df)
df = pd.concat(CF_pay)
df = df.drop(['Fixed_FV','Fixed_MD', 'Fixed_PV01','Float_FV','Float_MD','Float_PV01','PV * T','Unnamed: 0','Unnamed: 0.1'],axis=1)
df = df.sort_values(by=['Period'])
df.to_excel(dirr + '\\total_pay_leg\\Pay_CF.xlsx')
#объединяем все потоки которые получим
CF_receive = []
path = dirr + '\\receive_leg'
extension = 'xlsx'
os.chdir(path)
result = glob.glob('*.{}'.format(extension))
for files in result:
df = pd.read_excel(files)
CF_receive.append(df)
df = pd.concat(CF_receive)
df = df.drop(['Fixed_FV','Fixed_MD', 'Fixed_PV01','Float_FV','Float_MD','Float_PV01','PV * T','Unnamed: 0','Unnamed: 0.1'],axis=1)
df = df.sort_values(by=['Period'])
df.to_excel(dirr + '\\total_receive_leg\\Receive_CF.xlsx')
#объединим все FV в один файл
FV_total = []
path = dirr + '\output_FV'
extension = 'xlsx'
os.chdir(path)
result = glob.glob('*.{}'.format(extension))
for files in result:
df = pd.read_excel(files)
FV_total.append(df)
df = pd.concat(FV_total)
df = df.drop(['Unnamed: 0'],axis=1)
df.to_excel(dirr + '\\total_output_FV\\FV_IRS.xlsx')