import pandas as pd
from matplotlib import pyplot as plt
import math
from scipy.optimize import fsolve
from scipy.stats import norm
import numpy as np
import logging
import pickle
plt.style.use('ggplot')
def read(filepath):
"""
:param filepath: the data file
:return: processed 2d data containing five input var each row(B,r,T,sigmaE,E)
"""
df = pd.read_excel(filepath)
data = []
length = len(df.index)
df=df.loc[:,['r','B','E','sigma_E','T']]
for i in range(length):
row=[]
for j in range(5):
row.append(df.iat[i,j])
data.append(row)
return data
def cal(input):
"""
:param input: processed 2d data containing five input var each row(B,r,T,sigmaE,E)
:return:output 2d array containing five vars(A,sigmaA,PD,DD,P) each row calculated from five input var.
"""
result=[]
length=len(input)
for i in range(length):
result_row=[]
result_row_raw=fsolve(get_equation_set(input[i]),[1,1,1,1,1,1,1])
for j in range(2,7):
result_row.append(result_row_raw[j])
result.append(result_row)
return result
def plot(result):
dates = []
nums = []
for i in range(0, 8):
year = 2007 + i
dates.append("{year}Q1".format(year=year))
dates.append("{year}Q2".format(year=year))
dates.append("{year}Q3".format(year=year))
dates.append("{year}Q4".format(year=year))
exhibit = []
for i in range(0, 32):
nums.append(i)
exhibit.append(result[i][4])
plt.plot(nums, exhibit, label='P')
plt.xticks(nums, dates)
font1 = {'family': 'Times New Roman',
'weight': 'normal',
'size': 80,
}
plt.legend(prop=font1)
fig = plt.gcf()
fig.set_size_inches(28.5, 20.5)
fig.savefig('P.png', dpi=100)
plt.show()
def read_plot(filepath):
data = read(filepath)
dates = []
nums=[]
for i in range(0, 8):
year=2007+i
dates.append("{year}Q1".format(year=year))
dates.append("{year}Q2".format(year=year))
dates.append("{year}Q3".format(year=year))
dates.append("{year}Q4".format(year=year))
exhibit=[]
for i in range(0,32):
nums.append(i)
exhibit.append(data[i][3])
plt.plot(nums, exhibit, label='sigma_E')
plt.xticks(nums,dates)
font1 = {'family': 'Times New Roman',
'weight': 'normal',
'size':80,
}
plt.legend(prop=font1)
fig = plt.gcf()
fig.set_size_inches(28.5, 20.5)
fig.savefig('sigma_E.png', dpi=100)
plt.show()
return data
def savetoExcel(result):
A =[]
sigmaA = []
PD = []
DD = []
P =[]
for i in range(0,32):
A.append(result[i][0])
sigmaA.append(result[i][1])
PD.append(result[i][2])
DD.append(result[i][3])
P.append(result[i][4])
df=pd.DataFrame({"A":A,"sigma_A":sigmaA,"PD":PD,"DD":DD ,"P":P})
df.to_excel("result.xlsx")
def get_equation_set(input_row):
B = float( input_row[1])
r = float( input_row[0])
T = float( input_row[4])
sigmaE = float( input_row[3])
E = float( input_row[2])
def equation_set(x):
d1 = float(x[0])
d2 = float(x[1])
A = float(x[2])
sigmaA = float(x[3])
PD=float(x[4])
DD=float(x[5])
P=float(x[6])
return [
E - (A * norm.cdf(d1) - B * math.exp(-1 * r * T) * norm.cdf(d2)),
d1 - ((( math.log(A / B)) + (r + math.pow(sigmaA, 2)/2) * T) / (sigmaA * math.sqrt(T))),
d2 - (d1-sigmaA * math.sqrt(T)),
sigmaE - ((A / E) * norm.cdf(d1) * sigmaA),
PD-(norm.cdf(-1*d2)),
DD-(d2),
P-(B * math.exp(-1 * r * T) * norm.cdf(-1*d2)-A * norm.cdf(-1*d1))
]
return equation_set
if __name__=="__main__":
#a=read_plot("./data3.xlsx")
#print(a)
data=read("./data3.xlsx")
result=cal(data)
savetoExcel(result)
plot(result)
print(result)