Openpyxl Error raise ValueError("Cannot convert {0!r} to Excel".format(value))

Question

I wrote this code to display the series of contracts for Apple Inc. on Excel, but the data is not displayed on Excel

import yfinance as yf
from openpyxl import Workbook
wb = Workbook()
ws = wb.active
ws.title = "Gamma"
ticker = yf.Ticker("AAPL")
info = ticker.info
industry = print("industry:",info ["industry"])
Symbol = print("symbol:",info ["symbol"])
Last = print("currentprice:",info ["currentPrice"])
Option = ticker.option_chain('2023-06-23')
ks= print(Option.calls)

ws['A1']= info ["symbol"]
ws['A2']= info ["industry"]
ws['A3']= info ["currentPrice"]
ws['A5']= Option.calls
wb.save("gamm.xlsx")

The error that I resave related to this line ws['A5']= Option.calls. If I wrote the code with out it the program will create Excel file but only display A1 , A2 and A3

This is the error

raise ValueError("Cannot convert {0!r} to Excel".format(value))

ValueError: Cannot convert          contractSymbol             lastTradeDate  ...  contractSize  currency
0   AAPL230623C00070000 2023-05-30 14:09:28+00:00  ...       REGULAR       USD
1   AAPL230623C00100000 2023-06-05 19:53:22+00:00  ...       REGULAR       USD
2   AAPL230623C00110000 2023-06-16 17:45:43+00:00  ...       REGULAR       USD
3   AAPL230623C00115000 2023-06-16 17:34:41+00:00  ...       REGULAR       USD
4   AAPL230623C00120000 2023-06-16 17:38:58+00:00  ...       REGULAR       USD
5   AAPL230623C00130000 2023-06-14 14:46:51+00:00  ...       REGULAR       USD
6   AAPL230623C00135000 2023-06-16 16:11:04+00:00  ...       REGULAR       USD
7   AAPL230623C00140000 2023-06-16 16:27:40+00:00  ...       REGULAR       USD
8   AAPL230623C00145000 2023-06-16 15:50:56+00:00  ...       REGULAR       USD
9   AAPL230623C00148000 2023-06-12 16:40:01+00:00  ...       REGULAR       USD
10  AAPL230623C00149000 2023-06-12 17:29:03+00:00  ...       REGULAR       USD

[11 rows x 14 columns] to Excel

Answer 1

To display the data you want to use to_excel from Pandas to write the dataframe to the Excel sheet;

import yfinance as yf
import pandas as pd

ticker = yf.Ticker("AAPL")
info = ticker.info

print(f'Industry: {info["industry"]}')
print(f'Symbol: {info["symbol"]}')
print(f'Currentprice: {info["currentPrice"]}')

Option = ticker.option_chain('2023-06-23')

### Dataframe for Header; Header and Information 
### Formatted for two rows; row1 Header, row2 Info 
symbol_df = pd.DataFrame({
    'Symbol': [info["symbol"]],
    'Market': [info["industry"]],
    'Last Price': [info["currentPrice"]]
})

### Option.calls is a dataframe
options_df = Option.calls
### Need to fix the lasttradeDate as it contains TZ information
options_df['lastTradeDate'] = options_df['lastTradeDate'].dt.tz_localize(None)

### Write the two dataframes to Excel
### Symbol Info first from row 1, then Options df from row 3
with pd.ExcelWriter("gamm.xlsx") as writer:
    symbol_df.to_excel(writer,
                       sheet_name="Gamm",
                       startrow=0,
                       index=False)
    options_df.to_excel(writer,
                        sheet_name="Gamm",
                        startrow=3,
                        index=False)

Example Output

Note the default engine used is 'xlsxwriter' if you like you can switch use it's autofit() function to fit the columns to the data. Add the following to the ExcelWriter section to the below;

...
### Write the two dataframes to Excel
### Symbol Info first from row 1, then Options df from row 3
with pd.ExcelWriter("gamm.xlsx") as writer:
    symbol_df.to_excel(writer,
                       sheet_name="Gamm",
                       startrow=0,
                       index=False)
    options_df.to_excel(writer,
                        sheet_name="Gamm",
                        startrow=3,
                        index=False)

    ### xlsxwriter workbook and sheet objects
    workbook = writer.book
    worksheet = writer.sheets['Gamm']

    ### Use autofit() function on the worksheet 
    worksheet.autofit()

pandas