Fundamental Analysis in Stock Market with Tableau
Status Quo
Covid-19 that is appeared in 2019 brings impact in several sectors especially finance that forces every company to survive. Not many companies use their saves to operate and keep their employees, some companies lay off the employee which make everyone shocked to survive without any preparation. Therefore, everyone tries to find an alternative way to get income outside the company such as the stock market.
In the covid-19 situation, the stock market gets famous by everyone which many influencers who have invested so long appear and make a video how they get a lot of capital gain through social media such as Youtube, Twitter, Facebook, and many more. In the last two or three months, the good sentiments come in the commodity sector in Indonesia especially Nickel makes the price in several stocks increase significantly 190% for ANTM and 68,25% for INCO.
Unfortunately, the good sentiment brings two things: good news and bad news. Based on Kontan.co.id, the number of investors increases by 1.5 million or grows 36.13% in 2020 compared to 2019 which is good news that everyone realizes to invest their money to fight inflation [1]. The bad news, the new investors do not use strategy or analysis on when and where they should put and invest their money. As a result, many new investors get loss gain instead of capital gain because they do not use fundamental analysis as the baseline for their strategy.
Why investors have to learn Fundamental Analysis?
Fundamental analysis (FA) is a method of measuring intrinsic values such as equity, prospect, and many more that are related to economic and financial sectors. The simple meaning of FA is to analyze whether the company grows well or not, and always give a positive return every year. What kind of metrics that investor use in fundamental analysis
- revenue
- earning
- future growth
- return of equity
- future margin
- and other metrics that determine a company’s underlying value and potential for future growth
New investors usually learn this fundamental analysis as their fundamental to think first on why they finally choose this stock A instead of other stocks. By analyzing the company, we will not get into a trap in terms of loss gain.
In fundamental analysis, you just see the annual report of the company and the metrics above to learn whether the company grows well or worse. Here I present you a piece of the annual financial report.
The report has a thousand-page but we have not read all of them instead of several pages and also several metrics. For instance, I have highlighted the total assets and total equity, these two metrics increase every year which means good for the company. I am not saying it will be easy for the first time to read but I finally can so I believe you can read too.
Selecting stocks whose prices are below fair value
The above metrics are useful to know that the company operates well or not, but when exactly the good time to buy the stocks with the discount price (prices are below the fair price). In this section, I will guide you on how to calculate the price in a simple way so you are aware of when you invest your money. I will use stocks from Indonesia as an example.
The variables that are used from the above picture BBRI in 2019:
- Total Assets (1,416,758,840) in million
- Liability (1,183,155,670) in million
- Listed share (122,112,351,900 — you may find the listed share in this link)
Here I send you the formula to calculate the fair value/price:
The result of the calculation shows that the fair value/price is Rp. 1,913 but today when I write this article is Rp. 4,300 which means that today the price is more expensive around 2,24 times than the fair value/price. According to my analysis, the company is good to invest in the long-term but you are aware that the price is not good to invest in right now. Therefore, I recommend you to wait until the price touch around 1,5 times more expensive.
I wish you to do this calculation to the stocks that you think you want to invest in and compiled all of them into one spreadsheet/excel and then you can see your watchlist of stocks and invest whenever the price touches the fair price. Here I provide you the real-time data in BI tools especially Tableau to see your watchlist.
Watchlist of your stocks in Tableau
Tableau is one of the business intelligence (BI) tools that companies use to find insight for the business decision. I have compiled the data of stocks in a spreadsheet and connect it to the tableau. Here is the view of my spreadsheet as a data source:
1. Summary of stocks
2. Compiled open, close, high, and low prices for all stocks
Let me explain what is the difference between summary data and compiled data. Summary data shows that the main point that I need to know such as volume and volume average which I want to understand if there is an unusual transaction I will use technical analysis (this analysis is an advance, not recommended for a new investor), and discount which I want to see which stocks that has a discount price (today price is cheaper than the fair price). On the other, compiled data shows the open, close, high, and low prices that will be used to create candlestick in Tableau.
As you see in the list of sheets from the above picture, you need to compile several sheets into one sheet *compiled*. I used script editor to compiled, I will discuss how to compile several sheets into one sheet in the different article, I hope I can finish it next week.
Here is the waiting part of this article. The watchlist of your stocks is no longer static and monotone in your spreadsheet. Therefore, I provide you the dashboard of your watchlist.
From this picture, you can see that MNCN's price is discounted around 45,58% and the second place is ITMG around 23.25%. This dashboard is useful for me to control my money to invest the right time so I can maximize my capital gain and whenever the price down as an investor I won’t feel panic because we have learned the fundamental analysis from the second section.
I hope this article about the stocks market and how powerful Tableau provide your analysis in the detail helps you so much. I will upload the next article related to compile the several sheets into one sheet and guide you step-by-step to create the above visualization. Please follow me on LinkedIn and Medium, I am so happy to share with you. Thank you.
