[Python] Attempting to use a financial API to scrape data into an excel document, It is using...

My friend and I are attempting to write a program that scrapes an API to gather earnings report information and then writes it to an excel document. Our intention ultimately is to have it only show us companies in real time that beat earnings by X percentage. But to limit the amount of variables and for proof of concept, for now we have just asked it to write to an excel document each companies ticker in the S&P 500, their expected EPS and their Actual EPS. We've gotten the program to the point where it does scrape the API (my daily uses get used) and it writes the excel document. However, the CMD Prompt returns an error for each ticker that no data was found and the Excel document it writes is completely blank. Any help would be greatly appreciated. Thanks.

Here is the code we are using:

import pandas as pd
import requests

# Constants
API_KEY = 'API Key' # Updated API key
OUTPUT_FILE = r'C:\Users\MyName\Downloads\s_and_p_500_earnings.xlsx' # Adjust path as needed

def get_sp500_tickers():
# Read the CSV file to get S&P 500 tickers
df = pd.read_csv(r'C:\Users\MyName\Downloads\constituents.csv')
return df['Symbol'].tolist()[:250] # Fetch the first 250 tickers

def fetch_earnings_data(tickers):
earnings_data = []

for ticker in tickers:
# Fetch earnings data for Q3 2024
url = f'https://financialmodelingprep.com/api/v3/earnings/{ticker}?apikey={API_KEY}'
response = requests.get(url)

# Check the response status
if response.status_code == 200:
data = response.json()
# Log the response for debugging
print(f"Response for {ticker}: {data}")

# If the response is valid and contains data
if isinstance(data, list) and data:
# Filter for Q3 2024
for earning in data:
report_date = earning.get('date')
if report_date and '2024-09-' in report_date: # Assuming Q3 ends in September 2024
company_name = earning.get('name') # Company name from the response
actual = earning.get('actualEPS')
estimated = earning.get('expectedEPS')

earnings_data.append({
'Company Name': company_name,
'Ticker': ticker,
'Estimated Earnings': estimated,
'Actual Earnings': actual
})
break # Stop checking once we find the relevant quarter
else:
print(f"No earnings data found for {ticker} or response format is invalid.")
else:
print(f"Error fetching data for {ticker}: {response.status_code} - {response.text}")

return earnings_data

def save_to_excel(earnings_data):
df = pd.DataFrame(earnings_data)
df.to_excel(OUTPUT_FILE, index=False, sheet_name='S&P 500 Earnings')
print(f"Earnings data saved to {OUTPUT_FILE}")

if __name__ == "__main__":
tickers = get_sp500_tickers()
earnings_data = fetch_earnings_data(tickers)
save_to_excel(earnings_data


API Key and file name are correct, I just removed them here.

Continue reading...