Exporting Stock Fundamental Data to a CSV file with yahoo_fin - Printable Version +- Python Forum (https://python-forum.io) +-- Forum: Python Coding (https://python-forum.io/forum-7.html) +--- Forum: General Coding Help (https://python-forum.io/forum-8.html) +--- Thread: Exporting Stock Fundamental Data to a CSV file with yahoo_fin (/thread-29618.html) |
Exporting Stock Fundamental Data to a CSV file with yahoo_fin - DustinKlent - Sep-13-2020 Good Day Everyone, I am attempting to write a python script which can take a list of stocks from a CSV file and pull fundamental stock data for each ticker (Stock price, Volume, P/E ratio, Book Value, Etc.) and export that to another CSV file. So far the code I have is the following code: import pandas as pd import pandas_datareader as pdr import datetime as dt from datetime import date from yahoo_fin import stock_info as si # Ticker list but I would like to pull this from a csv file instead stock_list = "MSFT", "TSLA", "AAPL" stock_stats = {} for ticker in stock_list: temp = si.get_stats_valuation(ticker) temp = temp.iloc[:,:2] temp.columns = ["Attribute", "Recent"] stock_stats[ticker] = temp stock_stats combined_stats = pd.concat(stock_stats) combined_stats = combined_stats.reset_index() combined_stats del combined_stats["level_1"] # update column names combined_stats.columns = ["Ticker", "Attribute", "Recent"] df = pd.DataFrame(combined_stats) df.to_csv(r'Stock_Data.csv')What this does is take the list of stocks and pulls the following data for each stock and puts it in the following format in a .CSV file: ,Ticker,Attribute,Recent 0,MSFT,Market Cap (intraday) 5,1.55T 1,MSFT,Enterprise Value 3,1.49T 2,MSFT,Trailing P/E,35.65 3,MSFT,Forward P/E 1,31.45 4,MSFT,PEG Ratio (5 yr expected) 1,2.44 5,MSFT,Price/Sales (ttm),11.03 6,MSFT,Price/Book (mrq),13.14 7,MSFT,Enterprise Value/Revenue 3,10.41 8,MSFT,Enterprise Value/EBITDA 6,21.76 9,TSLA,Market Cap (intraday) 5,346.66B 10,TSLA,Enterprise Value 3,353.52B 11,TSLA,Trailing P/E,896.96 12,TSLA,Forward P/E 1,105.26 13,TSLA,PEG Ratio (5 yr expected) 1,0.96 14,TSLA,Price/Sales (ttm),13.79 15,TSLA,Price/Book (mrq),35.18 16,TSLA,Enterprise Value/Revenue 3,13.75 17,TSLA,Enterprise Value/EBITDA 6,101.27 18,AAPL,Market Cap (intraday) 5,1.94T 19,AAPL,Enterprise Value 3,1.96T 20,AAPL,Trailing P/E,34.42 21,AAPL,Forward P/E 1,28.41 22,AAPL,PEG Ratio (5 yr expected) 1,2.70 23,AAPL,Price/Sales (ttm),7.35 24,AAPL,Price/Book (mrq),26.85 25,AAPL,Enterprise Value/Revenue 3,7.16 26,AAPL,Enterprise Value/EBITDA 6,23.65 I also have this code: stock_list = "MSFT", "TSLA", "AAPL" stock_table = {} for tickers in stock_list: ticker_data = si.get_quote_table(tickers) stock_table[tickers] = ticker_data print(stock_table)Which pulls additional stock data and puts it in the form of a nested dictionary. When I iterate through the nested dictionary it gives the following output: MSFT 1y Target Est, 228.71 52 Week Range, 132.52 - 232.86 Ask, 203.52 x 1400 Avg. Volume, 35380643.0 Beta (5Y Monthly), 0.89 Bid, 203.50 x 900 Day's Range, 201.24 - 208.63 EPS (TTM), 5.76 Earnings Date, Oct 21, 2020 - Oct 26, 2020 Ex-Dividend Date, Aug 19, 2020 Forward Dividend & Yield, 2.04 (0.99%) Market Cap, 1.544T Open, 207.2 PE Ratio (TTM), 35.42 Previous Close, 205.37 Quote Price, 204.02999877929688 Volume, 33620073.0 TSLA 1y Target Est, 292.77 52 Week Range, 43.67 - 502.49 Ask, 373.75 x 1100 Avg. Volume, 76389385.0 Beta (5Y Monthly), 1.64 Bid, 373.74 x 2900 Day's Range, 360.53 - 382.50 EPS (TTM), 0.39 Earnings Date, Oct 21, 2020 - Oct 26, 2020 Ex-Dividend Date, nan Forward Dividend & Yield, N/A (N/A) Market Cap, 347.304B Open, 381.94 PE Ratio (TTM), 965.6 Previous Close, 371.34 Quote Price, 372.7200012207031 Volume, 60717459.0 AAPL 1y Target Est, 116.14 52 Week Range, 53.15 - 137.98 Ask, 112.02 x 800 Avg. Volume, 170920981.0 Beta (5Y Monthly), 1.28 Bid, 111.96 x 4000 Day's Range, 110.03 - 115.23 EPS (TTM), 3.3 Earnings Date, Oct 28, 2020 - Nov 02, 2020 Ex-Dividend Date, Aug 07, 2020 Forward Dividend & Yield, 0.82 (0.72%) Market Cap, 1.915T Open, 114.57 PE Ratio (TTM), 33.98 Previous Close, 113.49 Quote Price, 112.0 Volume, 180860325.0 What I am having trouble doing is: 1. Getting it so that the "stock_list" can be pulled from a .CSV file. I'm not sure how to do that. I tried but it did not like the format the CSV file was in which is 1 column with a ticker symbol in each row. 2. Combining the output of the si.get_quote_table and the si.get_stats_valuation into 1 output and putting that into a .CSV file. Ideally I would like the output to be like this: Ticker, Open, PE Ratio, Etc. MSFT, 207.2, 965.6 AAPL, 114.57, 33.98 Etc. Etc. Where the first line is the header and each subsequent line in the .CSV is a new symbol with its respective stock data. I have made some updates to my code and it is the following: import pandas as pd from yahoo_fin import stock_info as si import glob stock_list = "MSFT", "AAPL" stats_val = {} for ticker in stock_list: data1 = si.get_stats_valuation(ticker) data1 = data1.iloc[:,:2] data1.columns = ["Attribute", "Recent"] stats_val[ticker] = data1 combined1 = pd.concat(stats_val) combined1 = combined1.reset_index() del combined1["level_1"] combined1.columns = ["Ticker", "Attribute", "Recent"] df = pd.DataFrame(combined1) df2 = df.drop_duplicates(subset=None, keep="first", inplace=False) df2.to_csv(r'stats_valuation.csv') stats = {} for ticker in stock_list: data2 = si.get_stats(ticker) data2 = data2.iloc[:,:2] data2.columns = ["Attribute", "Recent"] stats[ticker] = data2 combined2 = pd.concat(stats) combined2 = combined2.reset_index() del combined2["level_1"] combined2.columns = ["Ticker", "Attribute", "Recent"] df = pd.DataFrame(combined2) df2 = df.drop_duplicates(subset=None, keep="first", inplace=False) df2.to_csv(r'stats.csv') extension = 'csv' all_filenames = [i for i in glob.glob('*.{}'.format(extension))] #combine all files in the list combined_csv = pd.concat([pd.read_csv(f) for f in all_filenames ]) #export to csv combined_csv.to_csv( "Stock_Stats.csv", index=False, encoding='utf-8-sig') df = pd.read_csv('Stock_Stats.csv') # If you know the name of the column skip this first_column = df.columns[0] # Delete first df = df.drop([first_column], axis=1) df.to_csv('Stock_Stats.csv', index=False)What this does it it takes the two Yahoo_Fin methods (get_stats & get_stats_valuation) and makes two separate .CSV files. Then it removes duplicates and combines the 2 .CSV files into a .CSV file called "Stock_Stats.CSV" I was not able to figure out how to add them to the same .CSV at the same time. This seems like a bad way to do it but I'm not sure how else would make it simpler. What I need to do now is the following: 1. Figure out how to PULL the stock tickers from a .CSV file because I have a large number of them that I need to get the results back on. 2. Figure out how to convert the data I get back in my "Stock_Stats.CSV" file to the format described above: Ticker, Book Value, Price, Volume, Price to Earnings, Etc. StockA, $50, $ 85, 15 Million, 15 StockB, $36, $ 75, 24 Million, 6 StockC, $35, $ 78, 1 Million, 7 StockD, $54, $ 96, 5 Million, 2 RE: Exporting Stock Fundamental Data to a CSV file with yahoo_fin - detlefschmitt - Mar-02-2021 try this. The program in this example imports the csv file column with the header name "Ticker". How to create a csv file containing e.g. the ticker symbols from the NASDAQ can be found in this post. I added deleting unwanted columns, renaming columns and changing the columns order. import pandas as pd from yahoo_fin import stock_info as si ti = pd.read_csv('TickerList.csv') ti2 = ti["Ticker"].tolist() stock_list = ti2 stats = {} for ticker in stock_list: data = si.get_stats(ticker) stats[ticker] = data combined = pd.concat(stats) combined = combined.reset_index() del combined["level_1"] combined.columns = ["Ticker", "Attribute", "Recent"] df = pd.DataFrame(combined) df2 = df.pivot(index='Ticker', columns='Attribute', values='Recent') cols = [3,7,12,13,14,17,18,19,20,22,23,24,25,26,27,28,30,31,32,33,34,35,38,39,41,43,45,49,51,56,57,58] df2.drop(df2.columns[cols],axis=1,inplace=True) df2.rename(columns={ df2.columns[0]: "% Insider" }, inplace = True) df2.rename(columns={ df2.columns[1]: "% Institution" }, inplace = True) df2.rename(columns={ df2.columns[2]: "200-Day MA" }, inplace = True) df2.rename(columns={ df2.columns[3]: "50-Day MA" }, inplace = True) df2.rename(columns={ df2.columns[4]: "52-Week H" }, inplace = True) df2.rename(columns={ df2.columns[5]: "52-Week L" }, inplace = True) df2.rename(columns={ df2.columns[6]: "Avg Vol 10D" }, inplace = True) df2.rename(columns={ df2.columns[7]: "Avg Vol 3M" }, inplace = True) df2.rename(columns={ df2.columns[8]: "Beta" }, inplace = True) df2.rename(columns={ df2.columns[9]: "Book/Share" }, inplace = True) df2.rename(columns={ df2.columns[10]: "EBITDA" }, inplace = True) df2.rename(columns={ df2.columns[11]: "Enterprice V" }, inplace = True) df2.rename(columns={ df2.columns[12]: "Float" }, inplace = True) df2.rename(columns={ df2.columns[13]: "Market Cap" }, inplace = True) df2.rename(columns={ df2.columns[14]: "Price/Book" }, inplace = True) df2.rename(columns={ df2.columns[15]: "Price/Sales" }, inplace = True) df2.rename(columns={ df2.columns[16]: "Q Rev (YOY)" }, inplace = True) df2.rename(columns={ df2.columns[17]: "Return on Equity" }, inplace = True) df2.rename(columns={ df2.columns[18]: "Revenue/Share" }, inplace = True) df2.rename(columns={ df2.columns[19]: "Shares" }, inplace = True) df2.rename(columns={ df2.columns[20]: "Shares Short" }, inplace = True) df2.rename(columns={ df2.columns[21]: "Short prior" }, inplace = True) df2.rename(columns={ df2.columns[22]: "% Short" }, inplace = True) df2.rename(columns={ df2.columns[23]: "Cash" }, inplace = True) df2.rename(columns={ df2.columns[24]: "Cash/Share" }, inplace = True) df2.rename(columns={ df2.columns[25]: "Debt" }, inplace = True) df2.rename(columns={ df2.columns[26]: "Debt/Equity" }, inplace = True) order = [13,11,15,14,8,4,5,3,2,7,6,19,12,0,1,20,22,21,17,18,16,10,23,24,25,26,9] df3 = df2[[df2.columns[i] for i in order]] df3.to_csv(r'stats.csv') RE: Exporting Stock Fundamental Data to a CSV file with yahoo_fin - paulyan - Aug-01-2022 Hi DustinKlent, Try the following code. Best, Paul import glob import numpy as np import pandas as pd from yahoo_fin import stock_info as si # def getData2(tickers,type=1): stats_val = {} n=len(tickers) for i in range(0,n): ticker=tickers.ticker[i] if type==1: data= si.get_stats_valuation(ticker) else: data= si.get_stats(ticker) data = data.iloc[:,:2] data.columns = ["Attribute", "Recent"] stats_val[ticker] = data combined = pd.concat(stats_val) combined = combined.reset_index() del combined["level_1"] combined.columns = ["Ticker", "Attribute", "Recent"] df = pd.DataFrame(combined) df2 = df.drop_duplicates(subset=None, keep="first", inplace=False) return df2 # infile='http://datayyy.com/robo/tickers.txt' tickers=pd.read_table(infile) df=getData2(tickers) df2=getData2(tickers,2) np.shape(df) #Out[1194]: (36, 3) np.shape(df2) # Out[1195]: (204, 3) """ input file ticker aapl msft WMT IBM df.head() Out[1197]: Ticker Attribute Recent 0 aapl Market Cap (intraday) 2.61T 1 aapl Enterprise Value 2.68T 2 aapl Trailing P/E 26.86 3 aapl Forward P/E 25.45 4 aapl PEG Ratio (5 yr expected) 2.81 "" |