Getting Python to output data to Excel Sheet

Question

I am trying to output prices given by an API for each stock symbol, into an excel sheet. The code works to output the data into the command line, but once I tried putting it into a sheet, the code gives back this error, File "C:\Users\sss\Documents\Python Programs\Bot\Td\td3.py", line 42, in get_ohlc for symbol, lastPrice in kwargs.get('symbol'): ValueError: too many values to unpack (expected 2)

I am not sure where to go from this point. The relevant code is below, and any help would be greatly appreciated !

data_sheet1 = pd.read_excel('C:\\Users\\sss\\Downloads\\companylist.xlsx', index_col=0)
data_impor = data_sheet1.head(2600)

workbook = xlsxwriter.Workbook('c:\\Users\\sss\\Downloads\\output.xlsx')
worksheet = workbook.add_worksheet()
worksheet.write('A1', 'Stock')
worksheet.write('B1', 'Price')

row = 1
col = 0

def get_ohlc(**kwargs):
    data = get_quotes(symbol=kwargs.get('symbol'))
    for symbol in kwargs.get('symbol'):
        print(symbol)
        print(data[symbol]['lastPrice'])
    for symbol, lastPrice in kwargs.get('symbol'): 
        worksheet.write(row, col, symbol) 
        worksheet.write(row, col + 1, lastPrice) 
        row += 1
    workbook.close()


for row in data_impor.index:
    get_ohlc(symbol=[row])

In one line you expect symbol to be stored in kwargs['symbol'] but in the second for loop within get_ohlc you expect 'symbol` and lastPrice to be stored in kwargs['symbol']. Which one is it? Did you mean to reference a different key of the kwargs dictionary? — David Wierichs
– David Wierichs, Commented Jun 12, 2020 at 23:35

David Erickson · Accepted Answer · 2020-06-13 00:07:52Z

1

You can use zip to iterate through multiple columns in parallel. I'm not sure of everything that your code does, but here is an example:

Try

for symbol, lastPrice in zip(kwargs.get('symbol'), kwargs.get('lastPrice')):

Or

for symbol, lastPrice in zip(data['symbol'],data['lastPrice']):

The existing code for symbol, lastPrice in kwargs.get('symbol'): is the source of this error:

ValueError: too many values to unpack (expected 2)

You have only provided one column but two iterators, so it can't iterate unless you zip another column in or use a different method. Zip is my favorite and easiest for this problem though in my opinion.

edited Jun 13, 2020 at 0:07

answered Jun 12, 2020 at 23:39

David Erickson

16.7k2 gold badges21 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

n2001177 Over a year ago

Thank you. It is giving me this error now, ` File "C:\Users\nhaup\Documents\Python Programs\Bot\Td\td3.py", line 42, in get_ohlc for symbol, lastPrice in zip(kwargs.get('symbol'), kwargs.get('lastPrice')): TypeError: 'NoneType' object is not iterable ` I believe my syntax somewhere else may be causing this error. But thank you for fixing the other one!

David Erickson Over a year ago

This is how I usually through two columns in parallel of a dataframe called data. The code is: for symbol, lastPrice in zip(data['symbol'],data['lastPrice']): I'm not sure what your code is doing with kwargs and .get(), but the code I just posted is fundamentally how you would do it.

David Erickson Over a year ago

geeksforgeeks.org/python-iterate-multiple-lists-simultaneously This article shows lists, but it is pretty much the exact same thing for dataframe columns

Collectives™ on Stack Overflow

Getting Python to output data to Excel Sheet

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related