Nikhil Adithyan
- Jun 23, 2021
- 11 min read

Finding the Underlying Value of a Stock with a Simple Approach in Python

An easy-to-follow guide on implementing the CCA approach in Python to evaluate a stock

All stocks listed on the equity market are not traded on their intrinsic value but instead either greater or lesser to it. But, with some basic knowledge of financial concepts and fundamentals analysis, we could easily calculate the underlying or the actual price of a company’s stock and make our investments accordingly. There are a lot of approaches to make this job done but in this article, we are going with a simple yet efficient one which is the Comparable Company Analysis, shortly known as CCA. We will first explore what the CCA approach is all about, and then, we will move on to the programming part where we will use Python to program the complete approach and find the intrinsic value of Apple’s stock. Without further ado, let’s dive into the article!

Before moving on to exploring the article, make sure that you’re familiar with the following financial concepts: Revenue, Market Capitalization, Enterprise Value (EV), P/E Ratio, EBITDA, EBIT, along with some basics of Python programming.

Comparable Company Analysis (CCA)

Comparable Company Analysis is the process of evaluating a stock’s underlying value by comparing the financial metrics of other similar companies’ stocks of the same industry. The underlying idea of this approach is that similar companies have similar metrics. To make this approach a successful one, we have to do two things in the right way:

Pooling similar companies: This is the first and foremost task we have to undergo while following the CCA approach. CCA is all about comparing one stock to a bunch of others and it essential that we choose the right similar stocks. Or else, we might end up overvaluing or undervaluing the stock. For example, if we are trying to calculate the intrinsic value of Tesla, we must take into consideration the stocks that belong only to the automotive sector but not other sector’s stocks like Apple or Intel.

Choosing the right valuation multiples: This is nothing but the process of picking the metrics that are used to compare between stocks. Like the first process of choosing stocks, picking wrong multiples might lead to either undervaluing or overvaluing the stock. The most common multiples used are the P/E Ratio, EV/EBITDA, so on and so forth.

In this article, we will determine the intrinsic value of Apple stock by first pooling the right similar stocks that fall under the technology sector, and considering the valuation multiples P/E Ratio, EV/EBITDA, EV/EBIT, and EV/Revenue. That’s all about the CCA approach. Now, let’s move on to the programming where we will use Python to code the entire approach. Before moving on, a note on disclaimer: This article’s sole purpose is to educate people and must be considered as an information piece but not as investment advice or so.

Implementation in Python

The coding part can be classified into various steps as follows:


1. Importing Packages
2. Extracting the Financial Metrics with IEX Cloud
3. Formatting the Extracted Data
4. Calculating the Valuation Multiples
5. Calculating the Average and Difference of Multiples
6. Determining the Underlying Value

We will be following the order mentioned in the above list and buckle up your seat belts to follow every upcoming coding part.

Step-1: Importing Packages

Importing the required packages into the python environment is a non-skippable step. The primary packages are going to be Pandas to work with dataframes, data manipulation, and so on, Requests to make API calls, NumPy to work with arrays and for scientific functions. The secondary package is going to be Termcolor for font customization (optional but a great one to use). Let’s import all these packages into our Python environment.

Python Implementation:


# IMPORTING PACKAGES

import pandas as pd
import requests
import numpy as np
from termcolor import colored as cl

Now that we have imported all the required packages into our python. Let’s pull some financial metrics of a group of tech stocks along with Apple with IEX Cloud’s powerful API endpoints.

Step-2: Extracting the Financial Metrics with IEX Cloud

In this step, we will pull financial metrics (stock price, market capitalization, P/E ratio, EBITDA, EBIT, revenue, and enterprise value) of ten tech stocks (Microsoft, Amazon, Google, Facebook, Alibaba, Nvidia, PayPal, Intel, Netflix, and Apple) with the help of several API endpoints provided by IEX Cloud.

Here’s a note on IEX Cloud: IEX Cloud provides an extensive amount of API endpoints which include market data of stocks, commodities, forex, options, and cryptocurrency. The data is reliable and it is easy to use their API in any programming languages. The API comes in with highly customizable parameters which can help attain the desired information and one thing I truly appreciate about IEX Cloud is the amount of work and time they invested in creating wonderful documentation. Also, ensure that you have a developer account on IEX Cloud, only then, you will be able to access your API key (vital element to extract data with APIs). With that being, said, let’s pull some data.

Python Implementation:


# EXTRACTING THE FINANCIAL METRICS

ticker = ['MSFT', 'AMZN', 'GOOGL', 'FB', 'BABA', 'NVDA', 'PYPL', 'INTC', 'NFLX', 'AAPL']

def get_metrics(stock):
    iex_api_key = 'YOUR SANDBOX API KEY'
    fundamentals = []
    
    # 1. PRICE
    
    price_url = f'https://sandbox.iexapis.com/stable/stock/{stock}/price?token={iex_api_key}'
    raw_price = requests.get(price_url)
    price = raw_price.json()    
    fundamentals.append(price)
    
    # 2. MARKET CAP
    
    marketcap_url = f'https://sandbox.iexapis.com/stable/stock/{stock}/stats?token={iex_api_key}'
    raw_marketcap = requests.get(marketcap_url)
    marketcap = raw_marketcap.json()['marketcap']
    fundamentals.append(marketcap)
    
    # 3. PE RATIO
    
    peRatio_url = f'https://sandbox.iexapis.com/stable/stock/{stock}/stats?token={iex_api_key}'
    raw_peRatio = requests.get(peRatio_url)
    peRatio = raw_peRatio.json()['peRatio']
    fundamentals.append(peRatio)
    
    # 4. EBITDA
    
    ebitda_url = f'https://sandbox.iexapis.com/stable/time-series/fundamentals/{stock}/quarterly?token={iex_api_key}'
    raw_ebitda = requests.get(ebitda_url)
    ebitda = raw_ebitda.json()[0]['ebitdaReported']
    fundamentals.append(ebitda)
    
    # 5. EBIT
    
    ebit_url = f'https://sandbox.iexapis.com/stable/time-series/fundamentals/{stock}/quarterly?token={iex_api_key}'
    raw_ebit = requests.get(ebit_url)
    ebit = raw_ebit.json()[0]['ebitReported']
    fundamentals.append(ebit)
    
    # 6. REVENUE
    
    revenue_url = f'https://sandbox.iexapis.com/stable/time-series/fundamentals/{stock}/quarterly?token={iex_api_key}'
    raw_revenue = requests.get(revenue_url)
    revenue = raw_revenue.json()[0]['revenue']
    fundamentals.append(revenue)
    
    # 7. ENTERPRISE VALUE
    
    entvalue_url = f'https://sandbox.iexapis.com/stable/stock/{stock}/advanced-stats?token={iex_api_key}'
    raw_entvalue = requests.get(entvalue_url)
    entvalue = raw_entvalue.json()['enterpriseValue']
    fundamentals.append(entvalue)
    
    print(cl(f'Extracted {stock} Fundamentals', attrs = ['bold']))
    
    return fundamentals

msft_fundamentals = get_metrics(ticker[0])
amzn_fundamentals = get_metrics(ticker[1])
googl_fundamentals = get_metrics(ticker[2])
fb_fundamentals = get_metrics(ticker[3])
baba_fundamentals = get_metrics(ticker[4])
nvda_fundamentals = get_metrics(ticker[5])
pypl_fundamentals = get_metrics(ticker[6])
intc_fundamentals = get_metrics(ticker[7])
nflx_fundamentals = get_metrics(ticker[8])
aapl_fundamentals = get_metrics(ticker[9]))

Output:


Extracted MSFT Fundamentals
Extracted AMZN Fundamentals
Extracted GOOGL Fundamentals
Extracted FB Fundamentals
Extracted BABA Fundamentals
Extracted NVDA Fundamentals
Extracted PYPL Fundamentals
Extracted INTC Fundamentals
Extracted NFLX Fundamentals
Extracted AAPL Fundamentals

Code Explanation: We are first defining a function named ‘get_metrics’ that takes a stock’s symbol as the parameter. Inside the function, we are first defining a variable named ‘iex_api_key’ where we are storing the API key. You could notice that I have mentioned SANDBOX API KEY which is nothing but the Sandbox API key provided to us by IEX Cloud. To be more clear, while we create a developer account of IEX Cloud, the team will provide us with two types of API keys: one is the Cloud API key that helps us interact with the cloud environment, and the other is the Sandbox API key that helps us pull data from the sandbox environment.

We are here using the sandbox API because the data which is the financial metrics we are trying to pull is not supported or available in the cloud environment but can be extracted only via accessing the sandbox environment.

After storing the API key, we are creating another variable named ‘fundamentals’ in which an empty list is stored where the values of each financial metric will be appended. Next comes the process of pulling each financial metric using its respective API endpoint provided by IEX Cloud. I’m not here going to explain the code to pull each one of’em but only the first one which is extracting the stock price metric since the code structure is similar. In the code for extracting the stock price metric, we are first storing the API URL into the ‘price_url’ variable. Using the ‘get_function’ provided by the Requests, we are calling the API, and then, stored the data into the ‘price’ variable in JSON format.

Finally, we are appending the extracted value into the ‘fundamentals’ list we created earlier. The same applies to the remaining metrics too but only the API URL changes. At last, we are calling the created function, to store the financial metrics of each of the ten tech stocks.

Step-3: Formatting the Data

In this step, we are going to clean and format the extracted data. This step is an essential one since the data pulled using an API endpoint will be clumsy and hard to directly work with.

Python Implementation:


# FORMATTING THE DATA

raw_data = [msft_fundamentals, amzn_fundamentals, googl_fundamentals, fb_fundamentals, baba_fundamentals, 
            nvda_fundamentals, pypl_fundamentals, intc_fundamentals, nflx_fundamentals, aapl_fundamentals]

fundamentals = pd.DataFrame(columns = ['price', 'marketcap', 'pe', 'ebitda', 'ebit', 'revenue', 'ev'])
fundamentals.iloc[:,0] = range(0, 10)

for i in range(len(fundamentals)):
    fundamentals.iloc[i] = raw_data[i]

fundamentals['symbol'] = ticker
fundamentals = fundamentals.set_index('symbol')
fundamentals

Output:

Code Explanation: Firstly, we are creating a variable named ‘raw_data’ where we are storing all the extracted financial metrics of ten stocks. Next, we are creating a new dataframe ‘fundamentals’ to store all the raw data. The next line of code is not a big deal but just fills numbers ranging from 0 to 10 and fits its length to that of the ‘raw_data’ variable. In the next step, we are creating a for-loop that fills the ‘fundamentals’ data with the actual financial metrics of each stock and produces a result as seen in the above output. This whole process of cleaning and formatting the data makes it easier for us to work with and helps us make sense out of it easily.

Step-4: Calculating the Valuation Multiples

In this step, we are going to calculate the valuation multiples which are the P/E ratio, EV/EBITDA, EV/EBIT, and EV/Revenue with the help of the dataframe we cleaned and formatted before. These valuation multiples will then be used as the core components in further steps.

Python Implementation:


# VALUATION MULTIPLES CALCULATION

valuation_multiples = fundamentals.copy().iloc[:, 2:].drop('ev', axis = 1)
valuation_multiples = valuation_multiples.rename(columns = {'ebitda':'ev/ebitda', 'ebit':'ev/ebit', 'revenue':'ev/revenue'})

valuation_multiples.iloc[:, 1] = fundamentals['ev'] / fundamentals['ebitda']
valuation_multiples.iloc[:, 2] = fundamentals['ev'] / fundamentals['ebit']
valuation_multiples.iloc[:, 3] = fundamentals['ev'] / fundamentals['revenue']

valuation_multiples

Output:

Code Explanation: The first step we did is to duplicate the ‘fundamentals’ dataframe we created before using the ‘copy’ function provided by the Pandas package and stored it into the ‘valuation_multiples’. We also dropped three financial metrics the stock price, market capitalization, and enterprise value since are those are not needed. Again, the main idea of doing this is to just match the length so that it would be easy for us to calculate and append the results to the dataframe.

Next, we are renaming the columns to our needs, and then comes the calculation of each valuation multiple where we are dividing the EBITDA, EBIT, and the Revenue respectively by the Enterprise value and stored them into their concerning column in the dataframe. The final result can be seen from the above output where we have all the four desired valuation multiples of the ten tech stocks.

Step-5: Calculating the Average and Difference of Multiples

In this step, we are going to take into consideration all the valuation multiples of stocks except Apple and find the average of each of the multiple. Then, we will find the difference which is nothing but the ratio between the valuation multiples of Apple and the average of all nine stocks’ valuation multiples.

Python Implementation:


# AVERAGE AND DIFFERENCE OF MULTIPLES

index = ['avg', 'diff']
avg_diff = pd.DataFrame(columns = ['pe', 'ev/ebitda', 'ev/ebit', 'ev/revenue'])
avg_diff.iloc[:, 0] = np.arange(0,2)

avg_diff.iloc[0] = valuation_multiples[:9].sum() / 10
avg_diff.iloc[1] = valuation_multiples.iloc[9] / avg_diff.iloc[0]
avg_diff['avg/diff'] = index
avg_diff = avg_diff.set_index('avg/diff')
avg_diff

Output:

Code Explanation: There is nothing much going on in the first three lines where we are just creating a new dataframe named ‘avg_diff’ where the calculated readings will be stored and we are filling the dataframe temporarily with numbers ranging from 0 to 2 just to match the length instead of having an empty dataframe so that it will be helpful for us to append the values we will be calculating.

The remaining lines are the calculations where we are first calculating the average of each valuation multiples of stocks except Apple and appended those values into the ‘avg_diff’ dataframe, then, we are calculating the ratio between the valuation multiples of Apple and the average which we just calculated and stored into its respective row in the ‘avg_diff’ dataframe. It can be observed that there a lot of ‘iloc’ function usages in the code, so for those who don’t what this function is, the ‘iloc’ function is a slicing function used primarily to select specific rows or columns in a dataframe.

Step-6: Determining the Underlying Value

This is the final and the most interesting step where are going to make use of the previously determined readings to calculate the intrinsic or the underlying value of Apple’s stock. Also, this step is one of the easiest too.

Python Implementation:


# CALCULATING THE INTRINSIC VALUE

price_diff = raw_data[9][0] / avg_diff.iloc[1]
intrinsic_price = round((sum(price_diff) / 4), 2)
percentage_difference = round(((raw_data[9][0] / intrinsic_price) * 100), 2)

print(cl(f'The listed price of Apple : {raw_data[9][0]}', attrs = ['bold']))
print(cl(f'The intrinsic value of Apple : {intrinsic_price}', attrs = ['bold']))
if intrinsic_price > raw_data[9][0]:
    print(cl(f'The Underlying Value of Apple stock is {percentage_difference}% Higher than the Listed price', attrs = ['bold']))         
else:
    print(cl(f'The Underlying Value of Apple stock is {percentage_difference}% Lower than the Listed price', attrs = ['bold']))

Output:


The listed price of Apple : 133.8
The intrinsic value of Apple : 181.21
The Underlying Value of Apple stock is 73.84% Higher than the Listed price

Code Explanation: The code that matters here is just the first three lines and all the others are optional (but recommended). In the first line, we are defining a variable named ‘price_diff’ to store the ratio between the current listed price of Apple and the readings of the previously calculated difference (ratio between Apple’s valuation multiples and the average of all valuation multiples). We are then calculating the intrinsic or the underlying value of Apple’s stock by just taking an average of the ‘price_diff’ which we just calculated. In the next line of code, we are determining the percentage difference between the actual listed price and the underlying value of Apple’s stock and stored it into the ‘percentage_difference’.

From the output, it can be seen that the current or the last traded price of Apple’s stock is 133.8 USD, and the underlying value is 181.21 USD which is 73.84% higher than that of the current traded price. That’s some fascinating figures, isn't it? By observing the readings we got from our approach, we could go for a long (buy) since Apple’s stock is currently undervalued and has the potential to show substantial growth in the mere future (not a piece of investment advice but just my thoughts on the results).

Final Thoughts!

After an overwhelming process, we have successfully learned what the Comparable Companies Analysis (CCA) is all about, and its practical implication on the real-world market. When compared to other approaches that exist in the realm of financial analysis, what we did is not even equivalent to a pinch. So, the approach we did in this article is just the beginning or an introduction to a broader view in the field of finance and this article can also be taken as an example that Finance is no longer a boring or dry field but geeking out in the forthcoming days.

With that being said, you’ve reached the end of the article. If you forgot to follow any of the coding parts, don’t worry. I’ve provided the full source code at the end. That’s it! Hope you learned something new and useful from this article.

Full code:


# IMPORTING PACKAGES

import pandas as pd
import requests
import numpy as np
from termcolor import colored as cl

# EXTRACTING THE FINANCIAL METRICS

ticker = ['MSFT', 'AMZN', 'GOOGL', 'FB', 'BABA', 'NVDA', 'PYPL', 'INTC', 'NFLX', 'AAPL']

def get_metrics(stock):
    iex_api_key = 'YOUR SANDBOX API KEY'
    fundamentals = []
    
    # 1. PRICE
    
    price_url = f'https://sandbox.iexapis.com/stable/stock/{stock}/price?token={iex_api_key}'
    raw_price = requests.get(price_url)
    price = raw_price.json()    
    fundamentals.append(price)
    
    # 2. MARKET CAP
    
    marketcap_url = f'https://sandbox.iexapis.com/stable/stock/{stock}/stats?token={iex_api_key}'
    raw_marketcap = requests.get(marketcap_url)
    marketcap = raw_marketcap.json()['marketcap']
    fundamentals.append(marketcap)
    
    # 3. PE RATIO
    
    peRatio_url = f'https://sandbox.iexapis.com/stable/stock/{stock}/stats?token={iex_api_key}'
    raw_peRatio = requests.get(peRatio_url)
    peRatio = raw_peRatio.json()['peRatio']
    fundamentals.append(peRatio)
    
    # 4. EBITDA
    
    ebitda_url = f'https://sandbox.iexapis.com/stable/time-series/fundamentals/{stock}/quarterly?token={iex_api_key}'
    raw_ebitda = requests.get(ebitda_url)
    ebitda = raw_ebitda.json()[0]['ebitdaReported']
    fundamentals.append(ebitda)
    
    # 5. EBIT
    
    ebit_url = f'https://sandbox.iexapis.com/stable/time-series/fundamentals/{stock}/quarterly?token={iex_api_key}'
    raw_ebit = requests.get(ebit_url)
    ebit = raw_ebit.json()[0]['ebitReported']
    fundamentals.append(ebit)
    
    # 6. REVENUE
    
    revenue_url = f'https://sandbox.iexapis.com/stable/time-series/fundamentals/{stock}/quarterly?token={iex_api_key}'
    raw_revenue = requests.get(revenue_url)
    revenue = raw_revenue.json()[0]['revenue']
    fundamentals.append(revenue)
    
    # 7. ENTERPRISE VALUE
    
    entvalue_url = f'https://sandbox.iexapis.com/stable/stock/{stock}/advanced-stats?token={iex_api_key}'
    raw_entvalue = requests.get(entvalue_url)
    entvalue = raw_entvalue.json()['enterpriseValue']
    fundamentals.append(entvalue)
    
    print(cl(f'Extracted {stock} Fundamentals', attrs = ['bold']))
    
    return fundamentals

msft_fundamentals = get_metrics(ticker[0])
amzn_fundamentals = get_metrics(ticker[1])
googl_fundamentals = get_metrics(ticker[2])
fb_fundamentals = get_metrics(ticker[3])
baba_fundamentals = get_metrics(ticker[4])
nvda_fundamentals = get_metrics(ticker[5])
pypl_fundamentals = get_metrics(ticker[6])
intc_fundamentals = get_metrics(ticker[7])
nflx_fundamentals = get_metrics(ticker[8])
aapl_fundamentals = get_metrics(ticker[9])

# FORMATTING THE DATA

raw_data = [msft_fundamentals, amzn_fundamentals, googl_fundamentals, fb_fundamentals, baba_fundamentals, 
            nvda_fundamentals, pypl_fundamentals, intc_fundamentals, nflx_fundamentals, aapl_fundamentals]

fundamentals = pd.DataFrame(columns = ['price', 'marketcap', 'pe', 'ebitda', 'ebit', 'revenue', 'ev'])
fundamentals.iloc[:,0] = range(0, 10)

for i in range(len(fundamentals)):
    fundamentals.iloc[i] = raw_data[i]

fundamentals['symbol'] = ticker
fundamentals = fundamentals.set_index('symbol')
fundamentals

# VALUATION MULTIPLES CALCULATION

valuation_multiples = fundamentals.copy().iloc[:, 2:].drop('ev', axis = 1)
valuation_multiples = valuation_multiples.rename(columns = {'ebitda':'ev/ebitda', 'ebit':'ev/ebit', 'revenue':'ev/revenue'})

valuation_multiples.iloc[:, 1] = fundamentals['ev'] / fundamentals['ebitda']
valuation_multiples.iloc[:, 2] = fundamentals['ev'] / fundamentals['ebit']
valuation_multiples.iloc[:, 3] = fundamentals['ev'] / fundamentals['revenue']

valuation_multiples

# AVERAGE AND DIFFERENCE OF MULTIPLES

index = ['avg', 'diff']
avg_diff = pd.DataFrame(columns = ['pe', 'ev/ebitda', 'ev/ebit', 'ev/revenue'])
avg_diff.iloc[:, 0] = np.arange(0,2)

avg_diff.iloc[0] = valuation_multiples[:9].sum() / 10
avg_diff.iloc[1] = valuation_multiples.iloc[9] / avg_diff.iloc[0]
avg_diff['avg/diff'] = index
avg_diff = avg_diff.set_index('avg/diff')
avg_diff

# CALCULATING THE INTRINSIC VALUE

price_diff = raw_data[9][0] / avg_diff.iloc[1]
intrinsic_price = round((sum(price_diff) / 4), 2)
percentage_difference = round(((raw_data[9][0] / intrinsic_price) * 100), 2)

print(cl(f'The listed price of Apple : {raw_data[9][0]}', attrs = ['bold']))
print(cl(f'The intrinsic value of Apple : {intrinsic_price}', attrs = ['bold']))
if intrinsic_price > raw_data[9][0]:
    print(cl(f'The Underlying Value of Apple stock is {percentage_difference}% Higher than the Listed price', attrs = ['bold']))         
else:
    print(cl(f'The Underlying Value of Apple stock is {percentage_difference}% Lower than the Listed price', attrs = ['bold']))

tech. finance. ai

Finding the Underlying Value of a Stock with a Simple Approach in Python

Comparable Company Analysis (CCA)

Implementation in Python

Step-1: Importing Packages

Step-2: Extracting the Financial Metrics with IEX Cloud

Step-3: Formatting the Data

Step-4: Calculating the Valuation Multiples

Step-5: Calculating the Average and Difference of Multiples

Step-6: Determining the Underlying Value

Final Thoughts!

Full code:

Related Posts

insightbig