import numpy as np
import pandas as pd
from math import sqrt, exp, log
from scipy.stats import norm
import time
def main():
global S, T, R, K, Price
ave = []
std = []
Date = []
year = "2020_1"
file = "Path_教學_0523.xlsx"
path = "C:/Users/Administrator/Desktop/finance/"
DD = pd.read_excel(path + file, sheet_name=year)
DD_len = len(DD)
#print(DD_len)
# 列印欄位名稱，確認正確的日期欄位名稱
print(DD.columns)
print(DD['Path'][0])
print(DD['File'][0])
print(DD['Path'][0]+DD['File'][0])
#path1 = "C:\\\\Users\\\\tcm73\\\\Downloads\\\\"
for i in range(DD_len):
D = pd.read_csv(DD['Path'][0]+DD['File'][i], encoding = "ANSI", low_memory=False)
D.columns
D = D.dropna()
D = D.drop(' 開盤集合競價 ', axis=1)
mapping = {
' 成交日期': 'Date',
' 商品代號': 'ID',
' 履約價格': 'E',
' 到期月份(週別)': 'Maturity_contract',
' 買賣權別': 'Call_Put',
' 成交時間': 'Time',
' 成交價格': 'Pr',
' 成交數量(B or S)': 'Num',
}
D = D.rename(mapping,axis = 1)
S = DD['S0'][i]
T = DD['Maturity'][i]/365
R = DD['Rf'][i]/100
Contract = 'TXO'
Call_Put = 'C'
Volume = 30
m = 0
IV = []
for j in range(1,len(D)+1):
if D['ID'][j].strip() == Contract and \\
D['Call_Put'][j].strip() == Call_Put and \\
((str(D['Maturity_contract'][j]).strip() == str(DD['Contract'][i])) or \\
(D['Maturity_contract'][j] == int(DD['Contract'][i]))) and \\
D['Num'][j] >= Volume:
K = D["E"][j]
Price = D["Pr"][j]
sol = solve(D['Call_Put'][j].strip())
if sol < 0.03:
continue
IV.append(sol)
print("%3d \\t %7.2f \\t %.2f \\t %d \\t %f %% \\t row: %s" % \\
(m, Price,K,D['Num'][j], IV[m]*100, j))
m += 1
ave.append(np.average(IV))
std.append(np.std(IV, ddof = 1))
Date.append(DD['Date'][i])
print("%s \\t ave = %.4f%% \\t std.s = %f" % (str(Date[i]),ave[i]*100,std[i]))
print('**************************************************************')
fp = open("Finalresult_"+ year + "_" + Call_Put + ".txt", "w")
for i in range(DD_len):
print("%s \\t %f \\t %f" % (str(Date[i]),ave[i],std[i]),file=fp)
print("%s \\t %f \\t %f" % (str(Date[i]),ave[i],std[i]))
fp.close()
def BS(X,Sigma):
d1 = (log(S/K)+(R+Sigma*Sigma/2)*T)/(Sigma*sqrt(T))
d2 = d1-Sigma*sqrt(T)
if X == "C":
return S*norm.cdf(d1)-K*exp(-R*T)*norm.cdf(d2)-Price
if X == "P":
return K*exp(-R*T)*norm.cdf(-d2)-S*norm.cdf(-d1)-Price
def solve(X):
a = 0.00001
b = 1.00001
for i in range(4096):
c = (b+a)/2
if b-c < pow(10,-6):
break
else:
if BS(X,b)*BS(X,c)<0:
a = c
else:
b = c
return c
main()
ImplyVolatility練習_20240523.xlsx
