处理数据--根据日期自动记录excel

将多个excel合成一个

有每日平值期权的相应数据，将各日数据格式统一并按时间顺序拼起来，得到该月的平值期权总数据。

import pandas as pd
import numpy as np
import time
import datetime as dt
from datetime import date, datetime, timedelta
from math import exp
import matplotlib.pyplot as plt
from scipy.stats import norm

读取期货数据excel并合并成一个

wind每周获得一次期货的tick数据。改好格式后，合并成一个all。

df1 = pd.read_excel("D:\ETF期权套利\IF2108_20210728.xlsx",index_label=False)
df2 = pd.read_excel("D:\ETF期权套利\IF2108_20210803.xlsx",index_label=False)
df2 = df2.rename(columns = {
   '10003226.SH':'Time'}) 
df3 = pd.read_excel("D:\ETF期权套利\IF2108_20210806.xlsx",index_label=False)
df3.columns = ['Time','bid1','ask1','last']
df = df1.append(df2)
df = df.append(df3)
df.to_excel("D:\ETF期权套利\IF2108_all.xlsx")

处理期权数据excel

#读取excel以及前期处理工作

df2108 = pd.read_excel("D:\ETF期权套利\IF2108_all.xlsx",index_label=False)
#期权每日数据的位置0726-0806
path1 = "D:\\ETF期权套利\\期权数据\\20210726 ({}).csv"
path2 = "D:\\ETF期权套利\\期权数据\\20210727 ({}).csv"
path3 = "D:\\ETF期权套利\\期权数据\\20210728 ({}).csv"
path4 = "D:\\ETF期权套利\\期权数据\\20210729 ({}).csv"
path5 = "D:\\ETF期权套利\\期权数据\\20210730 ({}).csv"
path6 = "D:\\ETF期权套利\\期权数据\\20210802 ({}).csv"
path7 = "D:\\ETF期权套利\\期权数据\\20210803 ({}).csv"
path8 = "D:\\ETF期权套利\\期权数据\\20210804 ({}).csv"
path9 = "D:\\ETF期权套利\\期权数据\\20210805 ({}).csv"
path10 = "D:\\ETF期权套利\\期权数据\\20210806 ({}).csv"

#每日预计分红（从ETF中扣除）
div_list0 = [12.58,12.33,12.18,12.18,11.78,11.17,11.11,11.11,11.09,10.52]
div_list1 = [68.36,68.59,69.14,70.45,72.14,72.17,72.17,72.42,72.58,73.50]
#div_list2 = [3.81,3.84,3.41,2.11,0.81,1.41,1.48,1.22,1.08,0.75]
path_list = [path1,path2,path3,path4,path5,path6,path7,path8,path9,path10]

K_list = [4.7,4.8,4.9,5.0,5.25,4.7,4.8,4.9,5.0,5.25]#各期权执行价格
Date_list = pd.bdate_range('2021-07-26', '2021-08-30')#获得工作日列表

Maturity = pd.to_datetime('2021-08-20 15:00:00')
df = pd.DataFrame()
df_p = pd.DataFrame()
data_IF = pd.DataFrame()
df4700 = pd.DataFrame()
df4800 = pd.DataFrame()
df4900 = pd.DataFrame()
df5000 = pd.DataFrame()
df5250 = pd.DataFrame()

#筛选出有报价的df部分
data1 = df2108[(df2108['bid1']>0)&(df2108['ask1']>0)&(df2108['last']>0)<