Developing an Interactive Dashboard for Value Investment with Python, Dash and Pandas (Version 2)
利用Python, Dash和Pandas 建立互動式價值投資工具(第二版)
This is an old version of the tool.
Please go to the updated version 3: https://medium.com/@lesterso1027/developing-an-interactive-dashboard-for-value-investment-with-python-dash-and-pandas-version-3-d39582a710d7
1. A glance at the final results
1.1 GIF
1.2 Link
App link: https://stockdashboardlester.herokuapp.com/
Please allow the dashboard to use some time for loading. I am only using the free tier service on Render so the speed may be limited.
Github link: https://github.com/lester1027/ticker_dashboard
1.3 Steps to use
I. Enter https://stockdashboardlester.herokuapp.com/, enter the account as ‘Lester’ and the password as ‘wildcard’.
II. Choose the tickers in the dropdown list (can filter the results with typing).
III. Select the date range and update the price (this step is not necessary for doing fundamental analysis).
IV. Enter the safety margin in terms of percentage (user can leave the value as default).
V. Click the ‘Acquire Data’ button.
VI. Click the ‘Calculate Intrinsic Value’ button to show the financial figures and the analysis results of the selected stocks. The column ‘Comparison’ shows whether the stock is over-valued or under-valued.
VII. If there are financial figures shown as ‘9999999999’ or ‘1e+10’, this means the required figure is not accessible from the data source and manual input is required. The cell in the ‘Comparison’ column will show ‘Error’.
VIII. The table is fully editable. In case there is an error like the above-mentioned one or the user wants to make some adjustment of the financial figures, columns showing financial figures 1 to 14 should be edited. After adjustment, the intrinsic value will be re-calculated automatically. The values of the column ‘3_Long_Term_Growth_Rate’ should be carefully adjusted.
IX. If the stock to be analysed cannot be found in the drop-down list, users can click the ‘Add an empty row’ button and input all the figures manually.
1.4 Changelog
24-May-2020
I. URLs of the API were updated.
II. The display of the table was enhanced.
5-March-2020
I. A URL construction private method is added in the class Stock.
II. Error handling is added for financial figure acquisition.
III. A column is added to tell if the stock is undervalued or over-valued.
IV. The table is now editable.
V. The whole tool is deployed on Heroku.
2. Motivation
In a society of capitalism, earning salary without extra income cannot sustain one’s daily life properly for a long time. The monetary need will be increasingly demanding after finishing college study. In order to have a long-term passive income, value investing can be a good choice. Value investment of stocks with fundamental analysis neglect the short- or medium-term fluctuation in the price of a stock. Instead, it focuses on the fundamental aspects of the enterprises and asks a core question: Is the company doing well and will it do well in the future? To answer this question, typically one has to collect a lot of financial figures from the companies’ balance sheets, cashflow statements and income statements. He/she also has to do a series of subsequent calculations to calculate the intrinsic value of the company. Collecting the figures and calculation the intrinsic value one-by-one for each target company can be time-consuming. Therefore, this small project aims at allowing people to do value investment in a convenient way.
3. Methods
3.1. Define the function for calculating the intrinsic value
A function is defined according to the discounted cash flow approach. The parameters are the financial figures introduced below and the output is the intrinsic value per share.
def calculate_intrinsic_value(ttmFCF,sharesOutstanding,longTermGrowthRate,currentSharePrice,stockBeta,riskFreeRate,riskPremium,taxRate,longTermIntRate,marketCap,mvDebt,totalLiab,cce,gdpGrowthRate):#a function for calculating the intrinsic value#this is used later for both after acquiring financial figures and#after changing values in the interactive tabler_e=riskFreeRate+stockBeta*riskPremiumr_d=longTermIntRate*(1-taxRate)wacc=(marketCap)/(marketCap+mvDebt)*r_e+(mvDebt)/(marketCap+mvDebt)*r_dprojectedFCF=np.array([ttmFCF*(1+longTermGrowthRate)**n for n in range(11)])discountFact=np.array([1/(1+wacc)**n for n in range(11)])discountedFCF=projectedFCF[1:]*discountFact[1:]pvDiscountedFCF=discountedFCF.sum()perpetuityValue=(projectedFCF[-1]*(1+gdpGrowthRate))/(wacc-gdpGrowthRate)terminalValue=perpetuityValue*discountFact[-1]intrinsicValuePerShare=(pvDiscountedFCF+terminalValue+cce-totalLiab)/sharesOutstandingreturn intrinsicValuePerShare
3.2. Get financial figures
3.2.1. Web-scraping vs. API
As mentioned above, quite a number of financial figures are involved in the fundamental analysis. The most common methods of getting real-time or constantly updated data are web-scraping and API. The former directly gets data from websites by analyzing the content of them in formats like html and json while the latter uses data source provided by other companies, authorities or some good Samaritans. A comparison of web-scraping and API should be done.
Web-scraping is mainly free-of-charge. It is quite attractive for people who want to extract information from the web directly. Another Medium article of value investment uses this method (Value Investing Dashboard with Python Beautiful Soup and Dash Python). I was enlightened by this article and found a book ‘Website Scraping with Python: Using BeautifulSoup and Scrapy’ by Gábor László Hajba. After reading the first three chapters and working with the code inside, I admitted that the learning curve was too steep for someone who wanted to make a tool on-the-fly, like me. Moreover, a typical trick in web-scraping, ‘inspect elements’ in Google Chrome does not always return desirable results. For example, Yahoo Finance does not wrap the data with <table> tags and the data in the cells do not have unique IDs, which both make the content difficult to be scraped.
One more downside of using web-scraping is, this method is vulnerable to any change in the website structure. If one is trying to download a dataset with web-scraping in a one-off way, this is OK. However, it is not a desirable solution for long-term use.
The API approach has other pros and cons. Using it does not require any troublesome scraping. All you need is to find a suitable API service. However, APIs may not be free. Many APIs are using the ‘freemium’ pricing approach. That is, usage below a certain amount of request times is free while the users need to pay for any request exceeding that amount. For example, Yahoo Finance API in RapidAPI has a pricing strategy as follows. Users can have 500 requests per month for free and have 10,000 requests per month at USD 10 per month. Apart from the price issue, APIs do not ensure users with a consistent data format. For example, in the Yahoo Finance API of RapidAPI, the quarterly balance sheets of Microsoft contain the figure or ‘intangible assets’ while those of Apple do not. This can hinder the automation of the investment tool.
Originally, my idea was to create a tool for global stock markets. However, I soon found out this was a too ambitious target for a prototype tool. This is because the inconsistency of data can be more serious if global markets are involved. For example, the Hong Kong and Shanghai markets, which are geologically closer to where I live, do not have well-developed APIs for value investment at a fair price. The United States at least has EDGAR data for the stock markets. Considering all the mentioned criteria, I decided to use Financial Modeling Prep service to get the data. This service is free and has a consistent data format for each company. Unfortunately, it only includes stocks of the United States. I guess I can only go with this for now.
Not all the data can be collected from the API. I still need to scrape some figures from the websites. For example, I got the GDP growth rate of countries from Wikipedia. This can be done easily because the websites I used only contain the data inside simple HTML <table> tags.
The financial figure list I used comes from a Udemy course The Complete Value Investing Course (Updated 2020). It is a good introduction to value investment. The figures are as follows.
1. Free Cash Flow
2. Number of Shares Outstanding
3. Long-term Growth Rate
4. Current Share Price
5. Stock Beta
6. Risk-Free Rate
7. Market Risk Premium
8. Business Tax Rate
9. Estimated Interest Rate
10. Market Value of Equity
11. Market Value of Debt
12. Total Liabilities
13. Cash & Cash Equivalents
14. GDP Growth Rate
First, a class ‘Stock’ is defined for all company stocks. Single company stock can be initiated as an object using the ticker symbol, the desired start date of the price trend, the desired end date of the price trend and the safety margin of the analysis (explained later).
class Stock:def __init__(self,symbol,startDate,endDate,margin):self.symbol=symbolself.startDate=startDateself.endDate=endDateself.safetyMargin=marginself.debtPremium=1.3#the number to indicate a financial figure that cannot be acquired successfullyself.epsilon=9999999999
The update_source() method is used to store the API links or web-scraping links to the object itself as data.
def update_source(self):#source of priceHistoryself.url_priceHistory =self._get_f_url('priceHistory',format=True)#source of figures 1self.url_cashflowQuarter=self._get_f_url('cashflowQuarter',format=True)self.url_cashflow=self._get_f_url('cashflow',format=True)
The update_price() method is used to get the historical price of the stock from the corresponding link and store it as data of the object.
def update_price(self):#price historyself.response_priceHistory = requests.request("GET",self.url_priceHistory).json()['historical']self.priceHistory=pd.DataFrame(self.response_priceHistory)self.selectedPriceHistory=self.priceHistory[(self.priceHistory['date']>self.startDate)&(self.priceHistory['date']<self.endDate)]
The update_data() method is used to get the required financial figures from the links and to calculate the required numbers and store all of them as data of the object. The calculation uses the function defined in section 3.1 above.
def update_data(self):#Figure 2: Total number of shares outstandingtry:self.sharesOutstanding=float(requests.request('GET',self.url_companyQuote).json()[0]['sharesOutstanding'])except:self.sharesOutstanding=self.epsilon
3.3. Make a list of dictionaries of stock symbols and company names
This tool involves the United States stock markets Nasdaq and NYSE. The pandas dataframes containing the ticker symbols and company names of the two market are combined together to form a list to be chosen from in the tool.
3.4. Create the layout of the tool
After taking the Udemy course Interactive Python Dashboards with Plotly and Dash, I found that Dash suits my need for an interactive tool for fundamental analysis. An additional library called ‘dashtable’ is needed for building the interactive table.
At the top left corner, there is the title of the dashboard ‘Stock Ticker Dashboard’. Under the title, there is a dropdown component for users to select the stock they would like to investigate. The selection can be multiple. Also, the user can type on the dropdown component to search for the company symbols and names they want. The options included in this dropdown component are defined by the list of dictionaries mentioned in section 3.2. The selected stock symbols are used for the price trend display and the fundamental analysis in the table below. At the right of the dropdown component, there is date range picker for users to determine the date range of the price displayed below. The ‘Update Price’ button next to it is for refreshing the graph below.
In the price trend graph, stock closing prices are displayed in different colours with legends shown at the right side. This graph is interactive. Users can enlarge a certain area, change the axis scales and toggle the display of certain trends etc.
Below the graph, there is the core of the dashboard tool, the fundamental analysis part. Before doing the analysis, users can enter a safety margin in terms of percentage. This safety margin is for deducting the calculated intrinsic value of the stock with a certain percentage such that the analysis has tolerance for some analysis error. The ‘Fundamental Analysis’ button next to the safety margin is for refreshing the interactive table below to display the important figures and analysis results. Users need to compare ‘Intrinsic Value per Share with Safety Margin’ with ‘4. Current Share Price’. If the current price is lower than the intrinsic value with a safety margin, ‘under’ is shown in the ‘Comparison’ column, meaning the stock is under-valued currently and vice versa.
The whole analysis table is editable. The rows and the columns can be deleted. The columns can be filtered or sorted. Users can change the acquired financial figures from the data source and modify them themselves. To add a completely empty row for manual figure input, users can press the ‘Add an empty row’ button below the table.
3.5. Create callback functions
After deciding the layout of the dashboard, callback functions have to be defined for realizing the features described above. These functions are set up for the dashboard to react to the changes made by users.
The update_graph() function can be triggered by the clicking of ‘Update Price’ button. After being triggered, it passes the stock symbols selected, the start and end dates selected and the safety margin to create new objects for the ‘stock’ class. Then the get_source() and get_price() functions are called to acquire the price trends. The returned figure format is used to replace the original figure in the layout.
#callback functions@app.callback(Output('my_graph', 'figure'),[Input('price-button', 'n_clicks')],[State('my_ticker_symbol', 'value'),State('my_date_picker', 'start_date'),State('my_date_picker', 'end_date'),State('safety_margin','value')])def update_graph(n_clicks, stock_ticker, start_date, end_date, margin):#when the 'price-button' is clicked, display the close price data in the graphtraces = []for tic in stock_ticker:equity=Stock(tic,start_date[:10],end_date[:10],margin)equity.update_source()equity.update_price()traces.append({'x':equity.selectedPriceHistory['date'],'y': equity.selectedPriceHistory['close'], 'name':tic})fig = {'data': traces,'layout': {'title':', '.join(stock_ticker)+' Closing Prices'}}return fig
The update_table() function can be triggered by three changes.
I. Clicking ‘Fundamental Analysis’ button.
II. Changing the values in the table
III. Clicking the ‘Add an empty row’ button
After being triggered, this callback function determines which of the above changes is done most recently.
If the timestamp shows that the last change is the ‘Fundamental Analysis’ button being clicked, it passes the stock symbols selected, the start and end dates selected and the safety margin to create new objects for the ‘stock’ class. Then the update_source() and update_data() functions are called to acquire the financial figures and to calculate the intrinsic values.
If the last change is the values in the table, this function recalculates the intrinsic value according to the same pipeline and refresh the result.
If the last change is the ‘Add an empty row’ button is clicked, a new empty row will be appended to the table. This row is also editable.
@app.callback(Output('table', 'data'),[Input('analysis-button', 'n_clicks_timestamp'),Input('table','data_timestamp'),Input('add-row-button','n_clicks_timestamp')],[State('my_ticker_symbol', 'value'),State('my_date_picker', 'start_date'),State('my_date_picker', 'end_date'),State('safety_margin','value'),State('table','data')])def update_table(analysis_timestamp, data_timestamp, add_row_timestamp, stock_ticker, start_date, end_date, margin ,rows):...
4. Conclusion
This is my first time trying to create an interactive dashboard and deploy it online. I have learned a lot from it. I hope the tool is helpful to myself and maybe some of you. Please feel free to leave me any comment. Thank you for reading.
