Use Python to Value a Stock Automatically
Is Apple Stock Overvalued? Just Enter the Ticker and Let Python decide Automatically!
In a previous article I described how to manually obtain data from the web and perform calculations for the intrinsic value of Apple stock, as well as explained the discounted cash flow model in full detail.
This article uses the same model except that everything (pulling data from multiple sources, performing calculations) is now done automatically in Python. I will not explain the rationale behind the model or for making certain estimates here as they are already explained in the previous article. Please refer to it if you need further elaboration.
I have also changed the order of the steps a bit as it makes sense to programmatically obtain all relevant data needed first before making estimates and performing calculations.
Disclaimer: I am not a financial advisor and this article is not meant to represent any form of financial advice. Any investments you make using these calculations will carry risk so do remember to do your due diligence and research before doing so.
Step 1. Import Packages and Sign up for Financial Modelling Prep API
Import Packages to Extract and Present Data
First, we import the packages needed to parse data from Finviz and from Financial Modelling Prep API. The data from Finviz is in a html table which while the data from Financial Modelling Prep API is in JSON format, both of which we will need packages to parse.
This data is required to calculate the intrinsic value of the stock. We also use Matplotlib to do some plotting for data visualization later.
In the last line above, the base_url for the Financial Modelling Prep API is given, for any data request we make(e.g. cash flow statement of AAPL), we add it after the base_url in the form of queries (more on this later).
Enter Financial Modelling Prep API Key and Ticker
Enter the Financial Modelling Prep API Key and the ticker of the stock that you are interested in (“AAPL” for Apple stock in this case).
The API key “demo” below is a demo key which only works for obtaining data for “AAPL”. For other tickers, you need to sign up for an account at https://financialmodelingprep.com/developer to get an API key for free (for the first 250 requests, however you can actually just sign up for another account when your free requests are used up).
Go to the Dashboard to obtain your API key after you’ve signed up.
Step 2. Obtain Financial Statements from Financial Modeling Prep API
The Financial Modeling Prep API gives us everything we need to obtain financial statements data from different companies, without having to pour through them manually. The data returned is in the form of JSON (example below) which we can parse using the json Python package. Click on this link to view it for yourself.
Quarterly Cash Flow Statement (Most Recent 4 Quarters)
Why are we looking at the quarterly cash flow statements instead of the annual cash flow statements?
In the previous article, we used the free cash flow for the trailing twelve months (TTM) period to project future cash flows. Unfortunately, under Financial Modelling Prep’s annual cash flow statements data, there is no TTM data and the most recent data is the cash flow over the last year (2019). Hence, the most recent cash flows of the company (particular the first 2 quarters of 2020) are not accounted for.
Hence we would need to sum up the cash flows from the most recent 4 quarters ourselves for the TTM data. In the code below, we add the ‘cash-flow-statement/’ to the base_url of Financial Modelling Prep API as well as the ‘ticker’ and the parameters ‘period=quarter’ and ‘apikey=demo’. For more details on how to query data please refer to the documentation.
We parse the json data into a DataFrame and obtain the most recent 4 rows (hence the iloc[:4] in the code). Check the dates of the result below to see they are indeed the recent quarters. The Free Cash Flow is one of the columns in the DataFrame below (though not shown in the screenshot).
Annual Cash Flow Statement
We repeat the above but leave out the ‘period=quarter’ to obtain the annual cash flow statement data. This step is optional as we do not need anything here to calculate the intrinsic value. We obtain this data so as to later plot out the cash flows over the years to check if the cash flows are stable and increasing.
Verify the output below to see that the dates are indeed for annual data.
Cash Flow Statement (Annual + TTM)
Now we sum up data of the most recent 4 quarters, rename the row as ‘TTM’ and append it to the annual cash flow statement to combine both the annual and TTM data.
Notice that in the result, we now have a row which contains TTM data.
Check Stability of Free Cash Flows
Let’s plot out the free cash flows from the above DataFrame for the last 15 years (hence the iloc[-15:] in the code).
The cash flows are pretty stable and increasing, hence predictable. Note that a couple of anomalies are fine. An interested fact is that the TTM free cash flow is higher than the annual free cash flow last year (2019). This is because Apple managed to grew earnings in the previous quarter, despite the COVID pandemic. Pretty solid company eh?
Note also that if you get a company with unstable and erratic free cash flows, the discounted cash flow model probably will not be a good estimate for its intrinsic value.
Balance Sheet Statement (Quarterly)
The balance sheet shows a snapshot of the company’s financial health (all the assets and liabilities that it has) at a moment in time (not summed up over a period of time as with cash flow statement). To get the relevant data of a company to calculate the intrinsic value, we simply need the balance sheet statement for the most recent quarter.
Free Cash Flow, Total Debt, Cash and Short Term Investments
Let us extract the Free Cash Flow from the TTM row of our cash flow statement that we created earlier. We also obtain the Total Debt and Cash and Short Term Investments from the top row (most recent quarter) of the balance sheet statement. Let’s check them by printing them out as well.
Step 3. Extract and Parse Data from Finviz
Next, you need to obtain more data from Finviz. Finviz presents all the company data in a html table in its website as shown below.
Hence, we need to use the requests.get method in Python and the Beautiful Soup package to parse whatever data we need.
Price, EPS next 5Y, Beta, Number of Shares Outstanding
The following code shows how I obtain the ‘Price’ (current share price), ‘EPS next 5Y’ (estimated growth in Earnings per Share for next 5 years), ‘Beta’ (more on this later) and ‘Shs Outstand’ (number of shares outstanding) from Finviz and store them into a Python dictionary so I can extract this data later.
The results in the Python dictionary are shown below.
To learn more about the details of how data is extracted from Finviz, feel free to read my article below.
Estimate Discount Rate from Beta
Again, in previous article when explaining the discounted cash flow model, I wrote in detail what Beta is and how it shows the risk level of a stock. Feel free to head back to the article if you need more elaboration. I also wrote that for stocks with higher Beta, we demand a risk premium (i.e. higher reward for taking higher risk) hence a higher discount rate. I’ve written code below to estimate the discount rate based on the stock’s Beta.
So we got a discount rate of 8% as the Beta is 1.32.
Print All Data Needed for Intrinsic Value Calculation
For clarity, let’s print out all the data obtained from Financial Modelling Prep API and Finviz so far.
All the data we have obtained are shown below, if you compare this to my previous article with manually grabbed data for Apple, you will see that they are the same. (Well except for the slight difference in Total Debt, which is probably due to slightly different definitions in what constitutes the Total Debt in the balance sheet for Yahoo Finance and Financial Modelling Prep, this is fine and we will see that it does not affect the calculated intrinsic value much.)
Also in my previous article I elaborated on how I decide on the growth rate for years 6 to 10 and years 11 to 20. The same is done here. Here we assume that Apple will survive for the next 20 years, which is quite reasonable.
Step 4. Project Future Cash flows and Calculate Intrinsic Value!
We are now ready to calculate the intrinsic value of Apple stock! I’ve written a function below to do all of this!
The first part of the code (the for loop) projects the free cash flows of the company for the next 5 years using the ‘EPS Next 5Y’ growth rate. We have years 6 to 10 and years 11 to 20 in the next two for loops. We just need to multiply the previous cash flow by (1 + growth rate) for each year and loop it.
For the projected cash flow in each year, we also divide it by (1 + discount rate) to get the discounted cash flow from that year. We store all the projected cash flows into Python lists for summing up and plotting to visualize later later.
Then we add up all up all the discounted cash flows in the cash_flow_discounted_list using the .sum() method, add the Cash and Short Term Investments Short Term Investments and subtract the Total Debt.
Finally, we divide the above by the Total Number of Shares Outstanding and we are done!
Let’s look at the plot of the projected Cash Flow as well as the Discounted Cash Flow for the next 20 years. while the projected cash flow is always growing, the discounted projected cash flow starts to decrease after year 5. This is because while the growth rate decreases over time, the discount rate does not and eventually becomes higher than the growth rate. Hence even if Apple survives for an eternity, the discounted cash flow eventually becomes 0 and the intrinsic value is not infinite.
Print Intrinsic Value, Current Share Price, Margin of Safety
The code below outputs the intrinsic value, current share price and margin of safety. The margin of safety shows how many % the current share price is lower than its intrinsic (the higher the safer we are investing in it).
Here we obtain an intrinsic value that is similar to what we calculated in the previous article. We also see that the intrinsic value is lower than the current price of Apple. A negative margin of safety in the case of Apple below means that the share price is higher than the intrinsic value by a certain percentage). Once again, feel free to read the last part of my previous article to find out what I think of this.
In this article we used Python to pull data from multiple sources before using them to perform calculations to find the intrinsic value of a stock. Simply change the ticker symbol to perform the same calculations on any other company you want!
I hope this article was useful. If you like this article, feel free to check out the article below for an application example.
Feel free to check out my other articles below too!
