How to use AI to fetch S&P500 P/Es by Sector

Leave a Comment / By /

Everyone is using AI nowadays for different purposes. It can also leverage your code building capabilities, specially for finance!

In this post, we will go over a project to fetch the price per earnings for all of the companies from the S&P 500. We will also divide them by sector and have interesting insights on it. Everything using AI. Don’t worry we will show the complete code in the end.

To start with, open your favorite AI tool. I will be using ChatGPT 4 in this demonstration. It is important to go step by step with it.

AI Prompt Order

I would like a code in Python to fetch P/E info from all companies from the S&P 500

This generated an interesting code at first, functional, using yfinance to fetch the PE data. AI was using the Wikipedia S&P 500 list, which is nice and updated. Then I added a new prompt to fetch the sector.

I would like to fetch their sector as well and add it as a column

That same Wikipedia page has a column saying the GICS Section, so it simply fetched this new column. The code was exporting the data as a CSV. Here is our next input:

Ok, now I want to read this CSV on a different piece of code and group these by sector. I will have a map where each key (being the sector), we will have the list of companies with their PEs

It created a good piece of code, but I had to tweak it for the common errors, like PE null or not a number, in case the data from yfinance is missing.

How can I check for pe_ratio NAN or zero ? I want a condition for that before
sector_map[sector].append((ticker, pe_ratio))

With that completed, I finally asked for some metrics from the grouped sector data.

now I want to group these metrics. I want to do a for
for sector in sector_map:
fetch min PE and company, max PE and company, average and median by sector

And here is the final code, each block representing a block of code in a Jupyter Notebook, for instance.

import yfinance as yf
import pandas as pd

# Get the list of S&P 500 companies
sp500_url = "https://en.wikipedia.org/wiki/List_of_S%26P_500_companies"
df = pd.read_html(sp500_url)[0]

# Extract ticker symbols and sectors
tickers = df['Symbol'].tolist()
sectors = dict(zip(df['Symbol'], df['GICS Sector']))

# Function to fetch P/E ratio
def get_pe_ratio(ticker):
    try:
        stock = yf.Ticker(ticker)
        pe_ratio = stock.info.get('trailingPE', None)
        return pe_ratio
    except Exception as e:
        return None

# Fetch P/E ratios
pe_ratios = {ticker: get_pe_ratio(ticker) for ticker in tickers}

# Convert to DataFrame
pe_df = pd.DataFrame(list(pe_ratios.items()), columns=['Ticker', 'P/E Ratio'])
pe_df['Sector'] = pe_df['Ticker'].map(sectors)

# Save to CSV
pe_df.to_csv("sp500_pe_ratios.csv", index=False)

print("P/E ratios with sectors saved to sp500_pe_ratios.csv")
if pe_df is None or pe_df.empty:
    pe_df = pd.read_csv("sp500_pe_ratios.csv")

# Group by sector
sector_map = {}
dropped_companies = []
for _, row in pe_df.iterrows():
    sector = row["Sector"]
    ticker = row["Ticker"]
    pe_ratio = row["P/E Ratio"]

    # Check if pe_ratio is NaN or zero
    if pd.isna(pe_ratio) or pe_ratio == 0:
        dropped_companies.append((ticker, pe_ratio, sector))
        continue

    if sector not in sector_map:
        sector_map[sector] = []

    sector_map[sector].append((ticker, pe_ratio))

# Print the grouped data
print('Companies without PE in place')
for ticker, pe_ratio, sector in dropped_companies:
    print(f"{ticker}, {sector}")
for sector, companies in sector_map.items():
    print(f"{sector}: {companies}")
import numpy as np

for sector in sector_map:
    companies = sector_map[sector]
    sorted_companies = sorted(companies, key=lambda x: x[1])  # Sort by P/E Ratio
    min_pe_company, min_pe = sorted_companies[0]
    max_pe_company, max_pe = sorted_companies[-1]
    pe_values = [pe for _, pe in sorted_companies]
    
    avg_pe = np.mean(pe_values)
    median_pe = np.median(pe_values)

    print(f"Sector: {sector}")
    print(f"  Min P/E: {min_pe} ({min_pe_company})")
    print(f"  Max P/E: {max_pe} ({max_pe_company})")
    print(f"  Average P/E: {avg_pe:.2f}")
    print(f"  Median P/E: {median_pe:.2f}")
    print()

You can notice that it is an interesting piece of code. I did some changes to also list the companies having null/NaN PEs.

Here is the code output from the day we ran it:

Sector: Industrials
  Min P/E: 8.346195 (UAL)
  Max P/E: 542.4545 (DAY)
  Average P/E: 36.16
  Median P/E: 26.88

Sector: Health Care
  Min P/E: 9.342592 (CNC)
  Max P/E: 753.5715 (CRL)
  Average P/E: 56.78
  Median P/E: 29.50

Sector: Information Technology
  Min P/E: 7.9554453 (HPE)
  Max P/E: 512.6666 (PLTR)
  Average P/E: 47.98
  Median P/E: 31.06

Sector: Utilities
  Min P/E: 5.337607 (AES)
  Max P/E: 33.91532 (ETR)
  Average P/E: 20.81
  Median P/E: 20.50

Sector: Financials
  Min P/E: 6.5325446 (SYF)
  Max P/E: 52.52113 (FIS)
  Average P/E: 21.41
  Median P/E: 15.25

Sector: Materials
  Min P/E: 11.139535 (CF)
  Max P/E: 80.895836 (IFF)
  Average P/E: 29.50
  Median P/E: 22.66

Sector: Consumer Discretionary
  Min P/E: 7.0547943 (F)
  Max P/E: 642.89996 (DASH)
  Average P/E: 36.27
  Median P/E: 21.28

Sector: Real Estate
  Min P/E: 12.4609375 (VICI)
  Max P/E: 765.7777 (BXP)
  Average P/E: 92.45
  Median P/E: 40.54

Sector: Communication Services
  Min P/E: 9.02657 (CMCSA)
  Max P/E: 7730.5 (TKO)
  Average P/E: 408.98
  Median P/E: 20.66

Sector: Consumer Staples
  Min P/E: 8.830276 (MO)
  Max P/E: 54.339565 (COST)
  Average P/E: 24.44
  Median P/E: 21.25

Sector: Energy
  Min P/E: 8.11039 (DVN)
  Max P/E: 120.27272 (EQT)
  Average P/E: 24.50
  Median P/E: 16.33

We can also… ask for some insights for the AI! We can paste the response and prompt: Could you give me some insights here?

And here is the AI answer:

————————————————————————————————————————————————

Looking at the response, here are some insights:

1. Highest P/E Ratio:

  • The Real Estate sector has the highest average P/E ratio at 92.45. This suggests that investors are willing to pay a premium for companies in this sector, possibly due to their perceived stability or growth potential.
  • The company with the highest P/E ratio is BXP with a value of 765.78. This could indicate speculative growth expectations or a market anomaly.

2. Lowest P/E Ratio:

  • The Utilities sector has the lowest average P/E ratio at 20.81. Utilities are typically stable, less volatile, and have lower growth expectations, which could explain their lower P/E ratios.
  • The company with the lowest P/E ratio is AES from the Utilities sector with 5.34, suggesting that it may be undervalued or facing challenges.

3. Volatility Between Sectors:

  • Some sectors like Communication Services and Real Estate show extreme P/E ratios, particularly with TKO in Communication Services at 7730.5. Such high values might suggest either a unique growth situation or an outlier company.
  • In contrast, Consumer Staples and Utilities sectors show much lower volatility in their P/E ratios.

4. General P/E Trends:

  • Health Care and Information Technology have high average P/E ratios of 56.78 and 47.98, respectively. This could reflect high growth expectations, especially in sectors like health tech or emerging technologies.
  • Financials and Materials have more moderate P/E ratios (average of 21.41 and 29.50), which might indicate a more stable market environment with predictable earnings.

5. Median vs Average:

  • The Real Estate sector has a significantly higher average P/E ratio (92.45) than its median (40.54), indicating the presence of extreme outliers that are skewing the average.
  • Financials and Materials have a smaller gap between average and median P/E, which implies a more balanced distribution of P/E ratios in these sectors.

6. General Trends in Stable vs High-Growth Sectors:

  • Sectors like Utilities and Consumer Staples tend to have more stable, lower P/E ratios, often reflecting steady, lower-risk investments.
  • Sectors such as Health Care, Information Technology, and Real Estate show higher P/E ratios, which might signal higher growth prospects but also higher risk.

Takeaway:

  • Growth Sectors: High P/E ratios in Health Care, Information Technology, and Real Estate suggest high investor expectations, but these sectors may be more volatile.
  • Stable Sectors: Lower P/E ratios in Utilities and Consumer Staples indicate stable, income-generating companies, often with less room for explosive growth but more reliable earnings.
  • Investment Decisions: If you’re looking for high-growth potential, sectors like Information Technology or Health Care may be appealing, but they come with higher risks. On the other hand, Utilities and Consumer Staples might be better choices for more conservative investments.

————————————————————————————————————————————————

From this AI analysis, we can see that it drew an interesting conclusion with the limited data we sent. You might want to continue asking for insights for the AI to improve your analysis and reduce the risk of the decisions!

That was it for this post, which aimed to present some of the ways we can use AI in helping with analysis and building code that is valuable for us.

See you in the next post!

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top