Sep-13-2020, 12:01 AM
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:
,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"
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:
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
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