如何将Datetime导入Excel?

内容来源于 Stack Overflow,并遵循CC BY-SA 3.0许可协议进行翻译与使用

  • 回答 (1)
  • 关注 (0)
  • 查看 (1393)

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')
提问于
用户回答回答于

for c in data:之后,可以添加:

if isinstance(c, datetime.datetime):
    d = d.strftime("%Y-%m-%d")

这将转换d为类似"2018-06-30"(年,月,日)格式的字符串。你可以将其更改为你喜欢的任何格式...

扫码关注云+社区

领取腾讯云代金券

年度创作总结 领取年终奖励