Search code examples
pythondataframexlwingsyfinance

Any reason why xlwings will not copy more than 8 lines from this dataframe?


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')])

Solution

  • 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