I am just starting to learn xlwings and copying info from python to excel. However, this does not copy all the info over. Any experts in xlwings haves any idea why and can resolve this issue? There are 154 rows in the company info but only 8 get copied to excel and then it stops. When run in jupyter or IDEL or VS the info is visible but wont copy to excel. The historical price data (sheet2) works just fine. Thanks
import time
import pandas as pd
import numpy as np
import xlwings as xw
import yfinance as yf
stock = yf.Ticker("JPM")
wb = xw.Book()
info = stock.info.items()
info = pd.DataFrame(info, columns = ['keys', 'values'])
sheet1 = wb.sheets.add('Co. Info')
sheet1.range('A1').value = info
hist = pd.DataFrame(stock.history(period="max"))
sheet2 = wb.sheets.add('Px Hist')
sheet2.range('A1').value = hist
The output of the dictionary "info" in sheet1 or "Co. Info" looks as follows when run in jupyter or IDLE:
dict_items([('zip', '95014'), ('sector', 'Technology'),.......
('preMarketPrice', 175.84), ('logo_url', 'https://logo.clearbit.com/apple.com')])
Take a closer look at row 8 of the pandas dataframe info
. The cell of column values
row 8
contains an empty list []
. It seems that xlwings can't handle to copy a cell with an empty list of a pandas dataframe to an excel sheet. I would say this is a bug.
If you remove the empty list before you copy info
to the excel sheet, it works fine:
import time
import pandas as pd
import numpy as np
import xlwings as xw
import yfinance as yf
stock = yf.Ticker("JPM")
wb = xw.Book()
info = stock.info.items()
info = pd.DataFrame(info, columns = ['keys', 'values'])
info.drop(8, axis=0, inplace=True) # Remove row 8.
sheet1 = wb.sheets.add('Co. Info')
sheet1.range('A1').value = info
hist = pd.DataFrame(stock.history(period="max"))
sheet2 = wb.sheets.add('Px Hist')
sheet2.range('A1').value = hist