python 3.x - Pandas & requests code for financial analysis giving KeyError:0

Question

I've been looking for a few more tools to automate stock analysis, which is how i found this link to the code below. The author says he posted the whole code but I've not seen it so I am reconstructing it and can't quite get it running. Link below.

Requests, web scraping and pandas are areas where I'm not as proficent so I figure the code Jedi's on SO could help untangle or update this code. https://medium.com/swlh/automating-your-stock-portfolio-research-with-python-for-beginners-912dc02bf1c2

Long term I'm trying to learn python by updating or building more features into tools that others have created so this is also a learning experience. So I would love you to fix it but I would more prefer you give hints and lead me towards possible solutions.

# FILENAME financial_analysis.py
# SOURCE https://medium.com/swlh/automating-your-stock-portfolio-research-with-python-for-beginners-912dc02bf1c2

import requests
import pandas as pd


def getdata(stock):
    """Company Quote Group of Items"""
    company_quote = requests.get(f"https://financialmodelingprep.com/api/v3/quote/{stock}")
    company_quote = company_quote.json()
    share_price = float("{0:.2f}".format(company_quote[0]['price']))

    # Balance Sheet Group of Items
    BS = requests.get(f"https://financialmodelingprep.com/api/v3/financials/balance-sheet-statement/{stock}?period=quarter")
    BS = BS.json()

    # print_data = getdata(aapl)
    #Total Debt
    debt = float("{0:.2f}".format(float(BS['financials'][0]['Total debt'])/10**9))#Total Cash
    cash = float("{0:.2f}".format(float(BS['financials'][0]['Cash and short-term investments'])/10**9))

    # Income Statement Group of Items
    IS = requests.get(f"https://financialmodelingprep.com/api/v3/financials/income-statement/{stock}?period=quarter")
    IS = IS.json()

    # Most Recent Quarterly Revenue
    qRev = float("{0:.2f}".format(float(IS['financials'][0]['Revenue'])/10**9))

    # Company Profile Group of Items
    company_info = requests.get(f"https://financialmodelingprep.com/api/v3/company/profile/{stock}")
    company_info = company_info.json()# Chief Executive Officer
    ceo = company_info['profile']['ceo']

    return(share_price, cash, debt, qRev, ceo)


tickers = {'AAPL', 'MSFT', 'GOOG', 'T', 'CSCO', 'INTC', 'ORCL', 'AMZN', 'FB', 'TSLA', 'NVDA'}

data = map(getdata, tickers)

df = pd.DataFrame(data,
                  columns=['Total Cash', 'Total Debt', 'Q3 2019 Revenue', 'CEO'],
                  index=tickers), print(df)

generates this error

Traceback (most recent call last):
  File "/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/IPython/core/interactiveshell.py", line 3296, in run_code
    exec(code_obj, self.user_global_ns, self.user_ns)
  File "<ipython-input-2-d9759a746769>", line 1, in <module>
    runfile('/Users/owner/sbox/Jamesmk6_3/toolbox/financial_analysis.py', wdir='/Users/owner/sbox/Jamesmk6_3/toolbox')
  File "/Users/owner/Library/Application Support/JetBrains/Toolbox/apps/PyCharm-P/ch-1/193.7288.30/PyCharm.app/Contents/plugins/python/helpers/pydev/_pydev_bundle/pydev_umd.py", line 197, in runfile
    pydev_imports.execfile(filename, global_vars, local_vars)  # execute the script
  File "/Users/owner/Library/Application Support/JetBrains/Toolbox/apps/PyCharm-P/ch-1/193.7288.30/PyCharm.app/Contents/plugins/python/helpers/pydev/_pydev_imps/_pydev_execfile.py", line 18, in execfile
    exec(compile(contents+"
", file, 'exec'), glob, loc)
  File "/Users/owner/sbox/Jamesmk6_3/toolbox/financial_analysis.py", line 44, in <module>
    index=tickers), print(df)
  File "/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/pandas/core/frame.py", line 469, in __init__
    data = list(data)
  File "/Users/owner/sbox/Jamesmk6_3/toolbox/financial_analysis.py", line 12, in getdata
    share_price = float("{0:.2f}".format(company_quote[0]['price']))
KeyError: 0

I've dug deeper and found the dev pages but there seems to be a complication between what the author did and their docs show.

Answer 1

The API sometimes returns dict and sometimes list. Simpler approach is to always extract using json_normalize()

Obviously insert your API key to make this work. I've run out of allowed calls in 24hr period to further test, it did work well on run. Some of the tickers were returning multiple rows for some of the API call. i.e. final dataset was > 11 rows

import requests
import pandas as pd

tickers = {'AAPL', 'MSFT', 'GOOG', 'T', 'CSCO', 'INTC', 'ORCL', 'AMZN', 'FB', 'TSLA', 'NVDA'}

df = pd.DataFrame()

url = "https://financialmodelingprep.com/api/v3"
apikey="xxx"
payload = {"apikey":apikey}

for stock in tickers:
    print(stock)
    # use params rather than manually build request parameters
    quote = requests.get(f"{url}/quote/{stock}",params=payload)
    bs = requests.get(f"{url}/balance-sheet-statement/{stock}", params={"period":"quarter", "limit":1, **payload})
    IS = requests.get(f"{url}/income-statement/{stock}", params={"period":"quarter", "limit":1, **payload})
    company_info = requests.get(f"{url}/company/profile/{stock}", params=payload)

    if "Error Message" in quote.json():
        print(f"Error: {quote.text}")
        break

    else:
        # join all the results together using json_normalise() rather than hand coded extration from JSON
        df = pd.concat([df, (pd.json_normalize(quote.json())
         .merge(pd.json_normalize(bs.json()), on="symbol", suffixes=("","_BS"))
         .merge(pd.json_normalize(IS.json()), on="symbol", suffixes=("","_IS"))
         .merge(pd.json_normalize(company_info.json()), on="symbol", suffixes=("","_info"))
                    )])

# df.columns.tolist()
if len(df)>0:
    # the columns the question is interested in
    df.loc[:,["symbol","price","totalDebt","cashAndShortTermInvestments","revenue","profile.ceo"]]