Importing Datetimes into Excel

Question

Error in option.py below, at line (near the end):

ws.cell(row=i,column=j).value=c

The error is:

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

Here is the file 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')

Answer 1

I have not used nsepy.derivatives nor openpyxl, so, I cannot validate or confirm. But the error points you most of the way there:

Understanding your error

The proper Python datetime object that you're likely getting from get_expiry_date in the line expiry=get_expiry_date(year=xyear,month=xmonth) cannot fully/properly be understood within Excel.

You can look deeper within the openpyxl code for how dates are managed, but barring that, simply convert your datetime object to a string in the format you want before pushing it out to Excel.

Solution

Right after for c in data:, you can add:

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

This will convert d into a string in a format something like "2018-06-30" (year, month, day). You can change it to whatever format you like... I prefer the Asian style, as given here, since it's fully unambiguous.

PS. I severely edited your question to make it more manageable. The easier the question is to read/understand, the more/better the responses you'll get.