What Idea about PE Band ?

The PE Band chart is a useful tool for visualizing a stock’s price-to-earnings (P/E) ratio over time. By illustrating valuation trends, it allows investors to determine if a stock is overvalued, undervalued, or fairly valued.

In this article, we will demonstrate how to create a PE Band chart using data for five banks: SCB, KBANK, BBL, BAY, and KTB. We will import the data from an Excel file and store it in a pandas DataFrame, then stack and sort the data for further analysis.

First, let’s import the necessary libraries:

import pandas as pd
import numpy as np

Next, we will read the data from an Excel file and store it in a dictionary:

data = pd.read_excel('Bank_Data.xlsx',sheet_name=['SCB','KBANK','BBL','BAY','KTB'])

To verify that the data has been imported correctly, we can print the keys of the dictionary:

data.keys()

dict_keys(['SCB', 'KBANK', 'BBL', 'BAY', 'KTB'])

For a quick preview of the data, we can use the items() method:

The data provided is a dictionary containing stock data for multiple banks (SCB, KBANK, BBL, BAY, and KTB) over a period from 01/2016 to 10/2021.

data.items()

dict_items([('SCB',        Date  Prior    Open   High     Low  Close    P/E   PEG  P/BV  \
    0   01/2016  119.5  118.50  130.0  112.00  130.0   9.28     -  1.52   
    1   02/2016  130.0  130.50  142.0  125.00  138.5   9.98     -  1.53   
    2   03/2016  138.5  138.50  146.5  135.00  141.0  10.16     -  1.56   
    3   04/2016  141.0  139.50  140.0  127.00  133.5   9.62     -  1.48   
    4   05/2016  133.5  133.00  134.0  123.00  134.0  10.22     -  1.42   
    ..      ...    ...     ...    ...     ...    ...    ...   ...   ...   
    65  06/2021  102.5  102.50  106.5   97.50   98.0  11.87 -0.39  0.79   
    66  07/2021   98.0   98.25   99.5   90.25   93.5  11.33 -0.37  0.76   
    67  08/2021   93.5   93.50  108.0   92.50  106.5  12.70 -0.51  0.86   
    68  09/2021  106.5  106.00  137.0  101.50  122.0  14.55 -0.59  0.98   
    69  10/2021  122.0  121.50  127.5  120.50  126.0  15.02 -0.61  1.02   
    
            MarketCap  
    0   441283.807510  
    1   470136.979540  
    2   478623.206607  
    3   453249.885038  
    4   454947.450150  
    ..            ...  
    65  332771.012644  
    66  317491.655393  
    67  361634.880207  
    68  414267.186716  
    69  427851.814950  
    
    [70 rows x 10 columns]), ('KBANK',        Date  Prior   Open   High    Low  Close    P/E   PEG  P/BV  \
    0   01/2016  150.5  150.0  169.5  144.0  169.5   9.23     -  1.44   
    1   02/2016  169.5  169.0  173.5  159.0  170.5  10.34     -  1.43   
    2   03/2016  170.5  171.5  185.0  164.0  173.0  10.49     -  1.45   
    3   04/2016  173.0  171.5  172.0  154.0  166.0  10.06     -  1.39   
    4   05/2016  166.0  166.5  174.5  157.5  174.0  11.34     -  1.44   
    ..      ...    ...    ...    ...    ...    ...    ...   ...   ...   
    65  06/2021  119.0  120.0  130.5  118.0  118.0   8.34  -1.7  0.62   
    66  07/2021  118.0  118.5  123.0  101.5  103.0   7.28 -1.48  0.54   
    67  08/2021  103.0  102.5  126.0  101.0  123.0   7.39  0.19  0.64   
    68  09/2021  123.0  122.5  140.0  117.0  133.5   8.02   0.2  0.69   
    69  10/2021  133.5  133.5  145.5  132.5  141.0   8.47  0.21  0.73   
    
            MarketCap  
    0   405657.602713  
    1   408050.862906  
    2   414034.013389  
    3   397281.192038  
    4   416427.273582  
    ..            ...  
    65  279580.655974  
    66  244040.742079  
    67  291427.293939  
    68  316305.233666  
    69  334075.190613  
    
    [70 rows x 10 columns]), ('BBL',        Date  Prior   Open   High    Low  Close    P/E   PEG  P/BV  \
    0   01/2016  152.5  151.5  154.5  142.5  152.5   8.26     -  0.82   
    1   02/2016  152.5  152.0  167.5  146.0  161.5   9.02     -  0.85   
    2   03/2016  161.5  161.5  182.0  161.0  180.0  10.05     -  0.95   
    3   04/2016  180.0  178.5  179.5  161.0  164.0   9.16     -  0.87   
    4   05/2016  164.0  165.0  167.0  151.5  163.5   9.43     -  0.84   
    ..      ...    ...    ...    ...    ...    ...    ...   ...   ...   
    65  06/2021  115.0  116.0  123.5  113.0  113.0  13.13 -0.25  0.47   
    66  07/2021  113.0  113.5  114.5  100.5  102.5  11.91 -0.23  0.43   
    67  08/2021  102.5  102.0  119.5  100.0  114.0  11.05 -0.37  0.47   
    68  09/2021  114.0  115.0  123.5  111.0  116.5  11.29 -0.37  0.48   
    69  10/2021  116.5  116.0  124.5  115.0  122.5  11.87 -0.39  0.50   
    
            MarketCap  
    0   291098.541335  
    1   308278.127381  
    2   343591.720920  
    3   313050.234616  
    4   312095.813169  
    ..            ...  
    65  215699.247022  
    66  195656.396635  
    67  217608.089916  
    68  222380.197151  
    69  233833.254515  
    
    [70 rows x 10 columns]), ('BAY',        Date  Prior   Open   High    Low  Close    P/E   PEG  P/BV  \
    0   01/2016  29.75  30.00  31.75  27.50  31.00  13.06     -  1.23   
    1   02/2016  31.00  31.50  32.50  29.75  30.75  12.14     -  1.19   
    2   03/2016  30.75  31.00  36.00  30.75  34.50  13.62     -  1.33   
    3   04/2016  34.50  34.50  35.50  31.75  34.75  13.72     -  1.34   
    4   05/2016  34.75  35.00  38.00  33.00  37.75  14.27     -  1.42   
    ..      ...    ...    ...    ...    ...    ...    ...   ...   ...   
    65  06/2021  32.50  32.75  34.75  30.75  31.25  10.21 -0.61  0.78   
    66  07/2021  31.25  31.25  31.75  28.00  28.25   9.23 -0.55  0.71   
    67  08/2021  28.25  28.25  32.50  27.25  32.00   7.71  0.51  0.77   
    68  09/2021  32.00  32.00  36.00  30.25  32.50   7.83  0.52  0.78   
    69  10/2021  32.50  32.50  33.50  32.00  32.50   7.83  0.52  0.78   
    
            MarketCap  
    0   228028.614963  
    1   226189.674520  
    2   253773.781168  
    3   255612.721612  
    4   277680.006931  
    ..            ...  
    65  229867.555406  
    66  207800.270087  
    67  235384.376736  
    68  239062.257623  
    69  239062.257623  
    
    [70 rows x 10 columns]), ('KTB',        Date  Prior  Open  High   Low  Close   P/E   PEG  P/BV      MarketCap
    0   01/2016   16.7  16.6  17.8  15.4   17.4  8.18     -  1.02  243183.465750
    1   02/2016   17.4  17.5  18.2  17.0   17.7  8.32     -  1.04  247376.284125
    2   03/2016   17.7  17.8  19.1  17.6   18.7  9.18     -  1.06  261352.345375
    3   04/2016   18.7  18.6  18.6  17.1   17.5  8.59     -  1.00  244581.071875
    4   05/2016   17.5  17.5  17.8  16.2   17.1  8.51     -  0.91  238990.647375
    ..      ...    ...   ...   ...   ...    ...   ...   ...   ...            ...
    65  06/2021   10.9  10.9  11.4  10.6   10.7  9.44 -0.21  0.44  149543.855375
    66  07/2021   10.7  10.6  10.9   9.9   10.1  8.91  -0.2  0.41  141158.218625
    67  08/2021   10.1  10.1  11.4  10.0   11.2  9.88 -0.22  0.46  156531.886000
    68  09/2021   11.2  11.2  11.7  10.8   11.0  8.50 -0.34  0.45  153736.673750
    69  10/2021   11.0  10.9  12.0  10.9   11.5  8.88 -0.36  0.47  160724.704375
    
    [70 rows x 10 columns])])

data['SCB']

bank’s data is in a Pandas DataFrame with 70 rows and 10 columns. The columns represent the following attributes: Date, Prior, Open, High, Low, Close, P/E, PEG, P/BV, and MarketCap.

df = pd.concat(data,axis=1)
df.swaplevel(axis=1)

Rename

df.swaplevel(axis=1)['P/E']

df.swaplevel(axis=1)['P/E'].mean()
    SCB      10.543714
    KBANK    10.909429
    BBL      10.136714
    BAY      10.640714
    KTB       8.742571
    dtype: float64

df.swaplevel(axis=1)['P/E'].iloc[-1]
    SCB      15.02
    KBANK     8.47
    BBL      11.87
    BAY       7.83
    KTB       8.88
    Name: 69, dtype: float64

import plotly.express as px

df = df.iloc[-36:].swaplevel(axis=1)['P/E']

df.iloc[-1]
    SCB      15.02
    KBANK     8.47
    BBL      11.87
    BAY       7.83
    KTB       8.88
    Name: 69, dtype: float64

# Calculate the mean of P/E for each bank
avg = df.mean()
# Get the last row of the P/E dataframe
last=df.iloc[-1]
# Find the maximum P/E value for each bank
max=df.max()
# Find the minimum P/E value for each bank
min=df.min()

avg-min
  SCB      4.596667
    KBANK    4.157500
    BBL      4.361667
    BAY      4.031944
    KTB      3.205278
    dtype: float64

# Calculate the upper range by subtracting the average value from the maximum value
upper_range  = max-avg
# Calculate the lower range by subtracting the minimum value from the average value
lower_range = avg-min
# Calculate the transparent box value by subtracting 0 from the minimum value
transparent_box = min-0

pd.DataFrame([transparent_box,lower_range,upper_range])

# Create a DataFrame with the transparent box, lower range, and upper range values
band = pd.DataFrame([transparent_box,lower_range,upper_range],index=['transparent','lower','upper'])
band

# Transpose the DataFrame so that the banks are in the index and the ranges are in the columns
band = band.T
# Print the band DataFrame
band

Visualization section

import plotly.graph_objs as go

# Create a bar chart using the band DataFrame
fig = px.bar(band,
            template='simple_white',
             color_discrete_map ={'transparent':'white',
                                 'lower':'skyblue',
                                 'upper':'blue'})

# Add a scatter plot layer to the bar chart to display the last P/E values
fig.add_traces(go.Scatter(x=band.index,
                         y=last,
                         mode='markers',
                         marker_symbol ='line-ew'))

# Update the marker style for the scatter plot
fig.update_traces(marker=dict(size=30,
                             line=dict(width=5,color='red')),
                 selector=dict(mode='markers'))

# Display the chart
fig.show()

# Calculate the historical P/E band by subtracting the minimum value from the maximum value
histband = max-min
histband
    SCB      9.40
    KBANK    8.11
    BBL      8.64
    BAY      8.02
    KTB      5.57
    dtype: float64

# Create a DataFrame with the minimum value and historical P/E band
band2 = pd.DataFrame([min,histband],index=['transparent','historical pe'])

# Transpose the DataFrame so that the banks are in the index and the ranges are in the columns
band2 = band2.T
band2

visualization section with pe band

# Create a bar chart using the band2 DataFrame
fig = px.bar(band2,
            template='simple_white',
             color_discrete_map ={'transparent':'white',
                                 'historical pe':'chocolate'})
# Add a scatter plot layer to the bar chart to display the last P/E values
fig.add_traces(go.Scatter(x=band2.index,
                         y=last,
                         mode='markers',
                         marker_symbol ='line-ew'))
# Update the marker style for the scatter plot
fig.update_traces(marker=dict(size=30,
                             line=dict(width=5,color='red')),
                 selector=dict(mode='markers'))
# Display the chart
fig.show()

These two visualization sections create bar charts using the band and band2 DataFrames. The first chart displays the P/E bands as transparent, lower, and upper ranges, while the second chart shows the historical P/E band. In both charts, a scatter plot layer is added to display the last P/E values with customized marker styles. Finally, the charts are displayed using the fig.show() function.

Conclusion

In conclusion, the PE Band chart is an invaluable tool for investors aiming to enhance their stock valuation analysis. By leveraging Python and pandas to import, store, and process data, you can efficiently create a PE Band chart to guide your investment decisions. As you continue to explore financial analysis techniques, remember that the PE Band chart is just one of many valuable resources available to support informed decision-making.