def getDailySpline(): data = pd.read_excel( r"S:\Francesco\PRICING\Copy of BootstrappingSwapCurve_francesco.xlsx", sheet_name='Sheet4') data['x'] = pd.to_datetime(data['x']) tableOfParameters = getTablesOfParameters(data) #evaluationDate=pd.to_datetime('15/11/2018',format='%d/%m/%Y') #interpolatedValue=getSplineValue(evaluationDate,tableOfParameters) #plot the spline datelist = pd.date_range(start=data.loc[0, 'x'], end=data.loc[len(data) - 1, 'x']).tolist() Interpolation = [] for i in range(len(datelist)): interpolatedValue = getSplineValue(datelist[i], tableOfParameters) Interpolation.append(interpolatedValue) A = np.vstack(Interpolation) data = pd.read_excel( r"S:\Francesco\PRICING\Copy of BootstrappingSwapCurve_francesco.xlsx", sheet_name='Sheet4') data['x'] = pd.to_datetime(data['x']) data1 = data.set_index('x') dailySpline = pd.DataFrame(datelist) dailySpline['x'] = A dailySpline = dailySpline.set_index(0) ax = data1.plot(legend=False, figsize=(8, 5), x_compat=True, linestyle='', marker='o', color='r') ax = dailySpline.plot(legend=False, ax=ax) return dailySpline
from CubicSpline import getTablesOfParameters,getSplineValue,remove_duplicates data=pd.read_excel(r"\\data01\primissu\ERQA\Analytical Tasks\Syndication\Pricing templates\BootstrappingSwapCurve_francesco.xlsx",sheet_name='libor') tableOfParameters=getTablesOfParameters(data) ######################################################################################################### #plot the spline ######################################################################################################### datelist = pd.date_range(start = data.loc[0,'date'], end = data.loc[len(data)-1,'date'] ).tolist() Interpolation=[] for i in range(len(datelist)): interpolatedValue=getSplineValue(datelist[i],tableOfParameters) Interpolation.append(interpolatedValue) A=np.vstack(Interpolation) data=pd.read_excel(r"\\data01\primissu\ERQA\Analytical Tasks\Syndication\Pricing templates\BootstrappingSwapCurve_francesco.xlsx",sheet_name='libor') data1=data.set_index('date') G1=pd.DataFrame(datelist) G1['date']=A G1=G1.set_index(0) #ax = data1.plot(legend=False, figsize=(8, 5), x_compat=True,linestyle='',marker='o',color='r') #ax = G.plot(legend=False,ax=ax) ############################################################################################################# #setting coupon dates given cobdate
def getZCandDFCurve(AA, yearFrac, startingSet): YF = pd.DataFrame(yearFrac) AAA = list(AA.columns) YF = YF.set_index([AAA]) zc = list(startingSet.iloc[0, :]) df = list(startingSet.iloc[1, :]) A = list(pd.to_datetime(list(startingSet.columns), format='%d/%m/%Y')) splineDataFrame = pd.DataFrame() row1 = AA.iloc[0, :] row1 = row1[row1 != 0] nextdf = df[-1] #BondPV=100 A.append(row1.index[-1]) splineDataFrame['x'] = A numberMissing = list(row1[1:-1].index) #while BondPV!=0: for l in range(100): zc = list(startingSet.iloc[0, :]) df = list(startingSet.iloc[1, :]) dfGuess = nextdf zcGuess = -m.log(dfGuess) / float( YF[YF.index == row1.index[-1]].values) zc.insert(len(zc), zcGuess) splineDataFrame['y'] = zc tableOfParameters = getTablesOfParameters(splineDataFrame) Interp = [] newdf = [] #newzc=[] for j in range(len(numberMissing)): evaluationDate = row1.index[-2 - j] if evaluationDate < pd.to_datetime(startingSet.columns[0], format='%d/%m/%Y'): interpolatedValue = startingSet.iloc[0, 0] Interp.append(pd.Series(interpolatedValue)) else: interpolatedValue = getSplineValue(evaluationDate, tableOfParameters) Interp.append(interpolatedValue) newdf.append( m.exp(-Interp[j].iloc[0] * float(YF[YF.index == evaluationDate].values))) newdf.reverse() #newdf=m.exp(-Interp[0].iloc[0]*yearFrac[3]) Interp.reverse() newzc = [Interp[i].iloc[0] for i in range(len(Interp))] + [zcGuess] df = [1] + newdf + [dfGuess] BondPV = (sum([row1.iloc[i] * df[i] for i in range(len(df))])) nextdf = df[-1] + BondPV / 2 * -1 df = list(startingSet.iloc[1, :]) + df[1:] zc = list(startingSet.iloc[0, :]) + newzc A = list(pd.to_datetime(list(startingSet.columns), format='%d/%m/%Y')) B = list(row1.index[1:]) A = A + B splineDataFrame = pd.DataFrame() if len(AA) == 1: pass else: for i in range(1, len(AA)): # row1 = AA.iloc[i, :] row1 = row1[row1 != 0] nextdf = df[-1] #BondPV=100 previuoszc = zc previuosdf = df A.append(row1.index[-1]) splineDataFrame['x'] = A numberMissing = int( np.where(AA.loc[i, :] > 1)[0] - np.where(AA.loc[i - 1, :] > 1)[0] - 1) if numberMissing == 0: row1 = AA.iloc[i, :] row1 = row1[row1 != 0] nextdf = df[-1] previuoszc = zc previuosdf = df numberMissing = int( np.where(AA.loc[i, :] > 1)[0] - np.where(AA.loc[i - 1, :] > 1)[0] - 1) for l in range(100): newdf = [1] + df[3:] + [nextdf] BondPV = (sum( [row1.iloc[i] * newdf[i] for i in range(len(newdf))])) nextdf = newdf[-1] + BondPV / 2 * -1 df = list(startingSet.iloc[1, :]) + newdf[1:] zc = zc + [ -m.log(newdf[-1]) / float(YF[YF.index == row1.index[-1]].values) ] A = list( pd.to_datetime(list(startingSet.columns), format='%d/%m/%Y')) B = list(row1.index[1:]) A = A + B splineDataFrame = pd.DataFrame() else: #while BondPV!=0: for l in range(100): zc = copy.copy(previuoszc) df = copy.copy(previuosdf) dfGuess = nextdf zcGuess = -m.log(dfGuess) / float( YF[YF.index == row1.index[-1]].values) zc.insert(len(zc), zcGuess) splineDataFrame['y'] = zc tableOfParameters = getTablesOfParameters(splineDataFrame) Interp = [] newdf = [] #newzc=[] for j in range(numberMissing): evaluationDate = row1.index[-2 - j] interpolatedValue = getSplineValue( evaluationDate, tableOfParameters) Interp.append(interpolatedValue) newdf.append( m.exp( -Interp[j].iloc[0] * float(YF[YF.index == evaluationDate].values))) newdf.reverse() #newdf=m.exp(-Interp[0].iloc[0]*yearFrac[3]) Interp.reverse() newzc = [Interp[i].iloc[0] for i in range(len(Interp))] + [zcGuess] #newzc=[Interp[0].iloc[0],zcGuess] df = [1] + previuosdf[3:] + newdf + [dfGuess] BondPV = (sum( [row1.iloc[i] * df[i] for i in range(len(df))])) nextdf = df[-1] + BondPV / 2 * -1 df = list(startingSet.iloc[1, :]) + df[1:] zc = previuoszc + newzc A = list( pd.to_datetime(list(startingSet.columns), format='%d/%m/%Y')) B = list(row1.index[1:]) A = A + B splineDataFrame = pd.DataFrame() dataFrameDF = pd.DataFrame() dataFrameDF['df'] = df dataFrameDF = dataFrameDF.set_index([A]) dataFrameZC = pd.DataFrame() dataFrameZC['ZC'] = zc dataFrameZC = dataFrameZC.set_index([A]) return dataFrameDF, dataFrameZC
from CubicSpline import getTablesOfParameters, getSplineValue, remove_duplicates data = pd.read_excel( r"S:\Francesco\PRICING\presentation\BootstrappingSwapCurve_francesco_cobdate15feb2019.xlsx", sheet_name='Sheet4') tableOfParameters = getTablesOfParameters(data) ######################################################################################################### #plot the spline ######################################################################################################### datelist = pd.date_range(start=data.loc[0, 'x'], end=data.loc[len(data) - 1, 'x']).tolist() Interpolation = [] for i in range(len(datelist)): interpolatedValue = getSplineValue(datelist[i], tableOfParameters) Interpolation.append(interpolatedValue) A = np.vstack(Interpolation) data = pd.read_excel( r"S:\Francesco\PRICING\presentation\BootstrappingSwapCurve_francesco_cobdate15feb2019.xlsx", sheet_name='Sheet4') data1 = data.set_index('x') G = pd.DataFrame(datelist) G['x'] = A G = G.set_index(0) #ax = data1.plot(legend=False, figsize=(8, 5), x_compat=True,linestyle='',marker='o',color='r') #ax = G.plot(legend=False,ax=ax) import matplotlib.pyplot as plt