Im getting the list of all S&P 500 stocks and then storing each stocks info which contains a lot of financial information about 170 columns per stock in a dataframe the code is very slow and I was wondering if it could be improved.
import pandas as pd
import yfinance as yf
import json
import datapackage
data_url = 'https://datahub.io/core/s-and-p-500-companies/datapackage.json'
# to load Data Package into storage
package = datapackage.Package(data_url)
# to load only tabular data
resources = package.resources
for resource in resources:
if resource.tabular:
data = pd.read_csv(resource.descriptor['path'])
spSymbols=data['Symbol']
sp500 =""
for i in spSymbols:
sp500= sp500 + i +" "
tickers= yf.Tickers(sp500)
dfStockInfo =[]
metricList=[]
#bottle neck ****
for i in spSymbols:
stock = tickers.tickers.get(i)
stockInfo= stock.info
metricList.append(stockInfo)
I would like to optimize the tickers.tickers.get(i) by trying to batch the calls.
For each stock it is making an API call.
I thought this line tickers= yf.Tickers(sp500) would have got me all the stock info at one time but seems not.
Thanks!
You cannot improve this because, for each ticker you are recieving 155 columns:
metricList = []
for t in small:
metricList.append(pd.DataFrame([yf.Ticker(t).info]))
df = pd.concat(metricList)
which is (for the first five tickers):
zip sector fullTimeEmployees \
0 55144-1000 Industrials 95000.0
0 53224-9508 Industrials 13700.0
0 60064-6400 Healthcare 113000.0
0 60064-6400 Healthcare 50000.0
0 01923 Healthcare NaN
longBusinessSummary city \
0 3M Company operates as a diversified technolog... Saint Paul
0 A. O. Smith Corporation manufactures and marke... Milwaukee
0 Abbott Laboratories, together with its subsidi... North Chicago
0 AbbVie Inc. discovers, develops, manufactures,... North Chicago
0 Abiomed, Inc. engages in the research, develop... Danvers
phone state country companyOfficers website \
0 651 733 1110 MN United States [] https://www.3m.com
0 414 359 4000 WI United States [] https://www.aosmith.com
0 224 667 6100 IL United States [] https://www.abbott.com
0 847 932 7900 IL United States [] https://www.abbvie.com
0 978 646 1400 MA United States [] https://www.abiomed.com
maxAge address1 industry \
0 1 3M Center Conglomerates
0 1 11270 West Park Place Specialty Industrial Machinery
0 1 100 Abbott Park Road Medical Devices
0 1 1 North Waukegan Road Drug Manufacturers—General
0 1 22 Cherry Hill Drive Medical Devices
ebitdaMargins profitMargins grossMargins operatingCashflow \
0 0.26768 0.15838 0.46331 6776999936
0 0.19407 0.13587 0.36394 553200000
0 0.30320 0.17354 0.58074 None
0 0.50160 0.21999 0.69647 None
0 0.27568 0.13230 0.81763 None
revenueGrowth operatingMargins ebitda targetLowPrice \
0 -0.002 0.21351 9457999872 118
0 0.271 0.17307 727299968 52
0 0.138 0.22483 13496500224 115
0 0.041 0.35288 28453249024 115
0 0.119 0.24881 284437984 275
recommendationKey grossProfits freeCashflow targetMedianPrice \
0 hold 16579000000 4292875008 157.0
0 hold 1310900000 351275008 75.0
0 buy 25009000000 None 141.0
0 buy 38883000000 None 167.5
0 buy 843595000 None 357.5
currentPrice earningsGrowth currentRatio returnOnAssets \
0 128.49 -0.184 1.579 0.10136
0 57.19 0.261 1.718 0.12393
0 110.22 0.370 None None
0 153.80 0.261 None None
0 256.66 0.049 7.053 0.1013
numberOfAnalystOpinions targetMeanPrice debtToEquity returnOnEquity \
0 19 158.36 117.329 0.3888
0 9 72.67 17.690 0.27841
0 20 141.77 53.439 None
0 23 164.73 502.701 None
0 6 354.17 0.894 0.09637
targetHighPrice totalCash totalDebt totalRevenue totalCashPerShare \
0 210.0 3359000064 17603999744 35333001216 5.903
0 85.0 579400000 320000000 3747599872 3.660
0 158.5 10248999936 19250999296 44514000896 5.812
0 200.0 9829999616 77596999680 56725000192 5.558
0 425.0 758606976 13439000 1031753024 16.656
financialCurrency revenuePerShare quickRatio recommendationMean exchange \
0 USD 61.241 0.893 3.3 NYQ
0 USD 23.601 1.185 2.6 NYQ
0 USD 25.139 None 1.8 NYQ
0 USD 32.034 None 2.1 NYQ
0 USD 22.703 6.133 2.5 NMS
shortName longName exchangeTimezoneName \
0 3M Company 3M Company America/New_York
0 A.O. Smith Corporation A. O. Smith Corporation America/New_York
0 Abbott Laboratories Abbott Laboratories America/New_York
0 AbbVie Inc. AbbVie Inc. America/New_York
0 ABIOMED, Inc. Abiomed, Inc. America/New_York
exchangeTimezoneShortName isEsgPopulated gmtOffSetMilliseconds \
0 EDT True -14400000
0 EDT True -14400000
0 EDT True -14400000
0 EDT True -14400000
0 EDT False -14400000
underlyingSymbol quoteType symbol underlyingExchangeSymbol headSymbol \
0 None EQUITY MMM None None
0 None EQUITY AOS None None
0 None EQUITY ABT None None
0 None EQUITY ABBV None None
0 None EQUITY ABMD None None
messageBoardId uuid market \
0 finmb_289194 375388b3-dab7-3763-90cd-457ad19388a2 us_market
0 finmb_997111 4cc854cc-0565-35cc-abef-bc91f30d07b0 us_market
0 finmb_247483 5f9c82b0-139d-3f8f-b30c-efd7ee9ccdcf us_market
0 finmb_141885706 73238d42-cdcc-3f92-8141-dd675addae10 us_market
0 finmb_247589 a393224e-40ae-3cd2-a2c8-12a0912a9351 us_market
annualHoldingsTurnover enterpriseToRevenue beta3Year enterpriseToEbitda \
0 None 2.728 None 10.191
0 None 2.399 None 12.362
0 None 4.704 None 15.514
0 None 5.775 None 11.513
0 None 11.928 None 43.268
52WeekChange morningStarRiskRating forwardEps revenueQuarterlyGrowth \
0 None None 10.37 None
0 None None 3.47 None
0 None None 4.76 None
0 None None 13.97 None
0 None None 5.01 None
sharesOutstanding fundInceptionDate annualReportExpenseRatio totalAssets \
0 576252992 None None None
0 133187000 None None None
0 1768290048 None None None
0 1767879936 None None None
0 45497500 None None None
bookValue sharesShort sharesPercentSharesOut fundFamily \
0 25.093 9500369 0.0167 None
0 11.916 4454569 0.0286 None
0 19.466 13461989 0.0077 None
0 7.665 13154753 0.0074 None
0 30.432 1122293 0.0246 None
lastFiscalYearEnd heldPercentInstitutions netIncomeToCommon trailingEps \
0 1640908800 0.67534 5596000256 10.111
0 1640908800 0.97911 509200000 2.890
0 1640908800 0.76305 7696000000 4.030
0 1640908800 0.70272 12405000192 4.196
0 1648684800 0.98808 136504992 3.257
lastDividendValue SandP52WeekChange priceToBook heldPercentInsiders \
0 None None 5.120552 0.00082
0 None None 4.799429 0.00719
0 None None 5.662180 0.00741
0 None None 20.065231 0.00106
0 None None 8.433886 0.02005
nextFiscalYearEnd yield mostRecentQuarter shortRatio \
0 1703980800 None 1648684800 3.52
0 1703980800 None 1648684800 3.35
0 1703980800 None 1648684800 2.65
0 1703980800 None 1648684800 1.52
0 1711843200 None 1648684800 4.10
sharesShortPreviousMonthDate floatShares beta enterpriseValue \
0 1648684800 568626364 0.969607 96388669440
0 1648684800 129763766 1.166666 8991120384
0 1648684800 1750132706 0.733823 209379229696
0 1648684800 1763801318 0.784803 327589560320
0 1648684800 44660516 1.325000 12307118080
priceHint threeYearAverageReturn lastSplitDate lastSplitFactor legalType \
0 2 None 1064880000 2:1 None
0 2 None 1475712000 2:1 None
0 2 None 1357084800 10000:4798 None
0 2 None None None None
0 2 None 970444800 2:1 None
morningStarOverallRating earningsQuarterlyGrowth \
0 None -0.200
0 None 0.226
0 None 0.365
0 None 0.264
0 None 0.061
priceToSalesTrailing12Months dateShortInterest pegRatio ytdReturn \
0 2.095569 1651190400 1.93 None
0 2.428881 1651190400 2.08 None
0 4.378419 1651190400 1.92 None
0 4.793300 1651190400 5.16 None
0 11.318007 1651190400 13.51 None
forwardPE lastCapGain shortPercentOfFloat sharesShortPriorMonth category \
0 12.390551 None 0.0167 9678460 None
0 16.481268 None 0.0391 5287955 None
0 23.155462 None 0.0079 12103872 None
0 11.009306 None 0.0075 13443845 None
0 51.229538 None 0.0282 1260350 None
fiveYearAverageReturn previousClose regularMarketOpen \
0 None 129.41 129.41
0 None 54.68 55.00
0 None 108.65 108.21
0 None 153.16 153.08
0 None 247.51 246.42
twoHundredDayAverage trailingAnnualDividendYield payoutRatio volume24Hr \
0 161.68895 0.045669 0.6171 None
0 69.35795 0.01902 0.3396 None
0 121.92410 0.016567 0.4223 None
0 136.87920 0.033951 0.7618 None
0 307.36900 None 0.0000 None
regularMarketDayHigh navPrice averageDailyVolume10Day \
0 129.830 None 3438870
0 57.455 None 1193330
0 110.470 None 6229710
0 153.860 None 7106760
0 256.740 None 343420
regularMarketPreviousClose fiftyDayAverage trailingAnnualDividendRate \
0 129.41 143.0196 5.91
0 54.68 58.7068 1.04
0 108.65 111.8648 1.8
0 153.16 149.8344 5.2
0 247.51 257.8366 None
open averageVolume10days expireDate algorithm dividendRate \
0 129.41 3438870 None None 5.96
0 55.00 1193330 None None 1.12
0 108.21 6229710 None None 1.88
0 153.08 7106760 None None 5.64
0 246.42 343420 None None None
exDividendDate circulatingSupply startDate regularMarketDayLow currency \
0 1652918400 None None 126.6100 USD
0 1651104000 None None 55.1700 USD
0 1649808000 None None 107.6500 USD
0 1649894400 None None 151.4590 USD
0 None None None 247.3035 USD
trailingPE regularMarketVolume lastMarket maxSupply openInterest \
0 12.707942 3720829 None None None
0 19.788927 1231198 None None None
0 27.349874 3291666 None None None
0 36.653957 4619958 None None None
0 78.802580 201938 None None None
marketCap volumeAllCurrencies strikePrice averageVolume dayLow \
0 74042753024 None None 2938364 126.6100
0 9102474240 None None 1096637 55.1700
0 194900934656 None None 5486408 107.6500
0 271899934720 None None 7314664 151.4590
0 11677388800 None None 319779 247.3035
ask askSize volume fiftyTwoWeekHigh fromCurrency \
0 129.48 900 3720829 203.21 None
0 71.04 1100 1231198 86.74 None
0 109.00 1300 3291666 142.60 None
0 0.00 1400 4619958 175.91 None
0 0.00 800 201938 379.30 None
fiveYearAvgDividendYield fiftyTwoWeekLow bid tradeable dividendYield \
0 3.05 126.61 128.1 False 0.0399
0 1.54 51.91 0.0 False 0.0187
0 1.57 101.24 108.0 False 0.0171
0 4.27 105.56 0.0 False 0.0367
0 None 219.85 0.0 False None
bidSize dayHigh regularMarketPrice preMarketPrice \
0 900 129.830 128.49 128.61
0 1000 57.455 57.19 None
0 900 110.470 110.22 108.92
0 800 153.860 153.80 153.34
0 800 256.740 256.66 None
logo_url address2 \
0 https://logo.clearbit.com/3m.com NaN
0 https://logo.clearbit.com/aosmith.com Suite 170 PO Box 245008
0 https://logo.clearbit.com/abbott.com Abbott Park
0 https://logo.clearbit.com/abbvie.com NaN
0 https://logo.clearbit.com/abiomed.com NaN
fax
0 NaN
0 NaN
0 NaN
0 NaN
0 978 777 8411
and the columns are
['zip',
'sector',
'fullTimeEmployees',
'longBusinessSummary',
'city',
'phone',
'state',
'country',
'companyOfficers',
'website',
'maxAge',
'address1',
'industry',
'ebitdaMargins',
'profitMargins',
'grossMargins',
'operatingCashflow',
'revenueGrowth',
'operatingMargins',
'ebitda',
'targetLowPrice',
'recommendationKey',
'grossProfits',
'freeCashflow',
'targetMedianPrice',
'currentPrice',
'earningsGrowth',
'currentRatio',
'returnOnAssets',
'numberOfAnalystOpinions',
'targetMeanPrice',
'debtToEquity',
'returnOnEquity',
'targetHighPrice',
'totalCash',
'totalDebt',
'totalRevenue',
'totalCashPerShare',
'financialCurrency',
'revenuePerShare',
'quickRatio',
'recommendationMean',
'exchange',
'shortName',
'longName',
'exchangeTimezoneName',
'exchangeTimezoneShortName',
'isEsgPopulated',
'gmtOffSetMilliseconds',
'underlyingSymbol',
'quoteType',
'symbol',
'underlyingExchangeSymbol',
'headSymbol',
'messageBoardId',
'uuid',
'market',
'annualHoldingsTurnover',
'enterpriseToRevenue',
'beta3Year',
'enterpriseToEbitda',
'52WeekChange',
'morningStarRiskRating',
'forwardEps',
'revenueQuarterlyGrowth',
'sharesOutstanding',
'fundInceptionDate',
'annualReportExpenseRatio',
'totalAssets',
'bookValue',
'sharesShort',
'sharesPercentSharesOut',
'fundFamily',
'lastFiscalYearEnd',
'heldPercentInstitutions',
'netIncomeToCommon',
'trailingEps',
'lastDividendValue',
'SandP52WeekChange',
'priceToBook',
'heldPercentInsiders',
'nextFiscalYearEnd',
'yield',
'mostRecentQuarter',
'shortRatio',
'sharesShortPreviousMonthDate',
'floatShares',
'beta',
'enterpriseValue',
'priceHint',
'threeYearAverageReturn',
'lastSplitDate',
'lastSplitFactor',
'legalType',
'morningStarOverallRating',
'earningsQuarterlyGrowth',
'priceToSalesTrailing12Months',
'dateShortInterest',
'pegRatio',
'ytdReturn',
'forwardPE',
'lastCapGain',
'shortPercentOfFloat',
'sharesShortPriorMonth',
'category',
'fiveYearAverageReturn',
'previousClose',
'regularMarketOpen',
'twoHundredDayAverage',
'trailingAnnualDividendYield',
'payoutRatio',
'volume24Hr',
'regularMarketDayHigh',
'navPrice',
'averageDailyVolume10Day',
'regularMarketPreviousClose',
'fiftyDayAverage',
'trailingAnnualDividendRate',
'open',
'averageVolume10days',
'expireDate',
'algorithm',
'dividendRate',
'exDividendDate',
'circulatingSupply',
'startDate',
'regularMarketDayLow',
'currency',
'trailingPE',
'regularMarketVolume',
'lastMarket',
'maxSupply',
'openInterest',
'marketCap',
'volumeAllCurrencies',
'strikePrice',
'averageVolume',
'dayLow',
'ask',
'askSize',
'volume',
'fiftyTwoWeekHigh',
'fromCurrency',
'fiveYearAvgDividendYield',
'fiftyTwoWeekLow',
'bid',
'tradeable',
'dividendYield',
'bidSize',
'dayHigh',
'regularMarketPrice',
'preMarketPrice',
'logo_url',
'address2',
'fax']
It doesn't matter waht you do, it will take time to retrieve that data unless you choose a reduced number of them.