Exporting Stock Fundamental Data to a CSV file with yahoo_fin

[DustinKlent]

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

[detlefschmitt]

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

[paulyan]

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
""