下面行(接近末尾)处的option.py
出错:
ws.cell(row=i,column=j).value=c
错误是:
raise ValueError("Cannot convert {0!r} to Excel".format(value))
ValueError: Cannot convert [Date
2014-12-01 8555.9
Name: Close, dtype: float64] to Excel
这是文件option.py
#importing the necessary packages
import numpy
from nsepy import get_history
import datetime
from nsepy.derivatives import get_expiry_date
import openpyxl
#opening the excel sheet
wb=openpyxl.load_workbook('/home/arvind/summer_sweta/financial_math/computation/option.xlsx')
ws=wb.active
#to get the expiry date of the particular year and month when the option will expire
xyear = int(input('Enter the expiry year(e.g. 2013)'))
xmonth = int(input('Enter the expiry month(e.g. 6,12)'))
expiry=get_expiry_date(year=xyear,month=xmonth)
#we want the index option price data where days of maturity will be between 40 and 60
sdate =expiry-(datetime.timedelta(days=60))
edate =expiry-(datetime.timedelta(days=40))
#index for the rows of the excell sheet
i=1
#loop where we find data for all the possible days
while(sdate!=edate):
#underlying index price data
nifty_price = get_history(symbol="NIFTY 50",
start=sdate,
end=sdate,
index=True)
#condition to check if data is empty
if (nifty_price.empty):
sdate += datetime.timedelta(days=1)
continue
#to get index option price data
nifty_opn = get_history(symbol="NIFTY",
start=sdate,
end=sdate,
index=True,
option_type='CE',
strike_price=int(numpy.round(nifty_price.get('Close'),-2)), #to round off the strike price to the nearest hundred
expiry_date=expiry)
if (nifty_opn.empty):
sdate += datetime.timedelta(days=1)
continue
if (int(nifty_opn.get('Number of Contracts'))): #we want the data only of days when the option was traded
#we are only collecting the relevant information we need
symbol=nifty_opn.get('Symbol')
date=nifty_price.get('Date')
close=nifty_price.get('Close')
expiry=nifty_opn.get('Expiry')
strike_price=nifty_opn.get('Strike Price')
settle_price=nifty_opn.get('Settle Price')
contracts_no=nifty_opn.get('Number of Contracts')
data= [symbol,date,close,expiry, strike_price,settle_price,
contracts_no]
j=1
for c in data:
ws.cell(row=i,column=j).value=c
j +=1
i +=1
sdate += datetime.timedelta(days=1)
#saving the information to the excel
wb.save('option.xlsx')
发布于 2018-07-01 08:51:34
我没有使用过nsepy.derivatives
或openpyxl
,因此,我无法验证或确认。但这个错误在很大程度上指出了这一点:
理解你的错误
您可能会从get_expiry_date
的expiry=get_expiry_date(year=xyear,month=xmonth)
行中获得正确的Python datetime
对象,但在Excel中无法完全/正确地理解该对象。
您可以在openpyxl
代码中更深入地了解如何管理日期,但除此之外,只需将datetime
对象转换为所需格式的字符串,然后再将其推送到Excel。
解决方案
在for c in data:
之后,您可以添加:
if isinstance(c, datetime.datetime):
d = d.strftime("%Y-%m-%d")
这会将d
转换为类似于"2018-06-30"
(年、月、日)格式的字符串。您可以将其更改为您喜欢的任何格式...我更喜欢这里给出的亚洲风格,因为它是完全明确的。
PS。我对你的问题进行了严格的编辑,使其更易于管理。问题越容易阅读/理解,你得到的回答就越多/越好。
https://stackoverflow.com/questions/51116262
复制相似问题