CODEWITHSUNDEEP
pythonexcelpandasdataframeexport-to-excel
jamesishere
jamesishere

Reputation: 47

Python and Pandas Creating Multiple Dynamic Excel Sheets with Dataframes

Thanks in advance! I have been struggling for a few days so that means it is time for me to ask a question. I have a program that is pulling information for three stocks using the module "yfinance" It uses a ticker list in a txt file. I can get the intended information into a data frame for each ticker in the list using a for loop. I then want to save information for each separate ticker on its own sheet in an Excel book with the sheet name being the ticker. As of now I end up creating three distinct data frames but the Excel output only has one tab with the last requested ticker information (MSFT). I think I may need to use an append process to create a new tab with each data frame information, thanks for any suggestions.

Code

import platform
import yfinance as yf
import pandas as pd
import csv

# check versions
print('Python Version: ' + platform.python_version())
print('YFinance Version: ' + yf.__version__)

# load txt of tickers to list, contains three tickers
tickerlist = []
with open('tickers.txt') as inputfile:
    for row in csv.reader(inputfile):
        tickerlist.append(row)

# iterate through ticker txt file
for i in range(len(tickerlist)):
    tickersymbol = tickerlist[i]
    stringticker = str(tickersymbol)
    stringticker = stringticker.replace("[", "")
    stringticker = stringticker.replace("]", "")
    stringticker = stringticker.replace("'", "")

    # set data to retrievable variable
    tickerdata = yf.Ticker(stringticker)
    tickerinfo = tickerdata.info

    # data items requested
    investment = tickerinfo['shortName']
    country = tickerinfo['country']

    # create dataframes from lists
    dfoverview = pd.DataFrame({'Label': ['Company', 'Country'],
                          'Value': [investment, country]
                           })

    print(dfoverview)
    print('-----------------------------------------------------------------')

    #export data to each tab (PROBLEM AREA)
    dfoverview.to_excel('output.xlsx',
                        sheet_name=stringticker)

Output

Python Version: 3.7.7

YFinance Version: 0.1.54

Company Walmart Inc.

Country United States

Company Tesla, Inc.

Country United States

Company Microsoft Corporation

Country United States

Process finished with exit code 0

EDITS: Deleted original to try and post to correct forum/location

Upvotes: 1

Views: 1329

Answers (1)

Tyler Gallenbeck
Tyler Gallenbeck

Reputation: 588

If all of your ticker information is in a single data frame, Pandas groupby() method works well for you here (if I'm understanding your problem correctly). This is pseudo, but try something like this instead:

import pandas as pd

# df here represents your single data frame with all your ticker info
# column_value is the column you choose to group by
# this column_value will also be used to dynamically create your sheet names
ticker_group = df.groupby(['column_value'])

# create the writer obj
with pd.ExcelWriter('output.xlsx') as writer:
    # key=str obj of column_value, data=dataframe obj of data pertaining to key
    for key, data in ticker_group:
        ticker_group.get_group(key).to_excel(writer, sheet_name=key, index=False)

Upvotes: 0

Related Questions