How to Use Power BI for Stock Market Analysis

This post would be a tutorial on how you can also get started with Power BI for stock market analysis.

In the past, I used to analyse the fundamentals of a company through the S&P Capital IQ platform. (*The school terminated my account after realising I am not a student anymore) Everything was done in Excel spreadsheets and the process is repetitive.

Since I am always charting the same financial ratios & metrics, I was thinking if there is a way to automate this. Tinkered around with Python, but it didn’t really go well. So I tried playing around with Power BI and realised how easy this can be. It is probably the best tool to do this.

1. What is Power BI?

I am using Power BI on a day-to-day basis for my job. The way I would explain Power BI is just like Tableau, it is a data visualisation tool to generate beautiful dashboards from messy data. Both of them are competitors that fall under the category of Business Intelligence in Data Science.

Microsoft Power BI is recognised as a leader in Gartner’s 2020 Magic Quadrant for Analytics and Business Intelligence Platforms. So it is a good skill to learn & acquire if you want to go down the analytics path in your career.

Power BI is created by Microsoft and it is sort of like an extension of Microsoft Excel’s Power Query. The software is free to download for all and it is simple to use. As long as you know some basic Excel formulas, you are good to go. Do download it here if you are also interested to get started.

2. Pulling Stock Financial Data

Extracting data source and cleaning up data is often the most time-consuming part. But once you have got these settled, the rest is easy.

Since I don’t have S&P Capital IQ anymore, I have to resort to other methods of retrieving financial data information. After experimenting with Morningstar and Yahoo Finance, I found Reuters to be the best data source platform. So this tutorial would be using Reuters as an example, but you can use any website as long as the financial data are in a tabular format.

In this article, I used Microsoft (MSFT) as a case study to create the Power BI dashboard. To import MSFT’s income statement over the past 5 years, you just need to follow 3 simple steps.

1. Copy the URL link of MSFT’s Financials on Reuters.
2. Go to “Get Data > Web” on Power BI desktop and paste the URL link
3. Click on the HTML Tables[1] and select “Transform Data”

That’s the power of Power BI. It can automatically detect tabular data without having to write lengthy web-scraping codes. Importing financial data takes less than a minute which would otherwise have taken me an hour if I were to do it in Python.

3. Data Cleaning

Now that the data is inside Power BI, we have to do a bit of cleaning.

The original raw source of data probably looks like this. Here are 4 steps to clean the data in Power BI.

1. Remove Column 7
2. Select on Column 1 and click “Transpose” under the Transform tab
3. Click on “Use First Row as Headers” under the Transform tab
4. Rename the first column to “Date”

Done! Data cleaning in less than a minute. That’s the format and structure we want it to be. Now just repeat the same steps above and do it for MSFT’s Balance Sheet and Cashflow Statement.

4. Financial Ratios using Power BI DAX

After importing in the 3 financial statements of MSFT, the final step is to write some formulas for certain financial ratios.

Power BI has its own programming language known as Data Analysis Expression or DAX. The syntax for DAX is quite similar to Excel functions, so it should be easy to follow since we are not writing complicated functions.

Here is a list of measures that I have created. For example, let’s say I want to write a formula to calculate the total debt to total capital ratio, it would look something like this.

Debt-to-Capital = 
DIVIDE(SUM('Balance Sheet'[Total Debt]), 
SUM('Balance Sheet'[Total Equity]) + SUM('Balance Sheet'[Total Debt]))

Or let’s say I want to find out the gross profit margin for each year.

Gross Profit % = 
DIVIDE(SUM('Income Statement'[Gross Profit]), 
SUM('Income Statement'[Total Revenue]))

Almost all of the formulas I created just use the simple DIVIDE function which takes in numerator and denominator as a parameter.

However, there are some other ratios which are slightly more complicated such as calculating the growth rate or Return on Asset (ROA).

Let’s say I want to calculate the YOY revenue growth rate %. Here is how my DAX formlua looks like.

Revenue Growth % =
VAR x = SUM('Income Statement'[Revenue])
VAR y = CALCULATE(SUM('Income Statement'[Revenue]), DATEADD('Income Statement'[Date],-1,YEAR))
VAR z = x - y

RETURN 
  DIVIDE(z, y)

The CALCULATE function takes in an expression as the first parameter and a filter as the second parameter. So variable y can be interpreted as give me the revenue of MSFT for the previous year. DATEADD is the filter which I used to push back the year by one.

Similarly, the formula for ROA % is net profits divided by the average of total assets for current year and previous year. So we would have to do something like the above formula.

ROA % = 
VAR x = SUM('Income Statement'[Net Income])
VAR y = SUM('Balance Sheet'[Total Assets])
VAR z = CALCULATE(SUM('Balance Sheet'[Total Assets]), DATEADD('Balance Sheet'[Date],-1,YEAR))

RETURN 
  DIVIDE(x, (y + z)/2)

You can come up with your own formulas and see what financial ratios or metrics you want to tweak or analyse. My list of formulas are definitely not exhaustive but it is those that I want to analyse in a company.

5. Creating a Stock Summary Dashboard

The final & easiest step is data visualisation. Just drag and drop the visuals and whichever fields you want to analyse.

To keep it clean, I have selectively filtered out just a few key important ratios and financial figures to look at. Most of my previous blog posts on stock analysis would have covered them.

The 7 segements are:

1. Liqudity/solvency
2. Earnings
3. Margins
4. Cash Flow
5. Growth
6. Profitability
7. Market

Here is how the dashboard looks like for MSFT.

Right on the top is the financial position strength of the company. I like to look at things like how much debt and cash the company has in its balance sheet.

I also pick conservative ratios like quick ratio and total debt to free cash flow to see if the company can survive under adverse conditions. Any red flags should be immediately apparent on the top of the dashboard.

Earnings analysis just look at things like revenue, gross profit, operating income and net income. It should be all trending upwards for a healthy company.

Margins analysis look at how strong the company is. The criteria should be a high margin business that is either trending up or held steady. Declining margins usually mean the company is losing its relevance and market dominance.

Cash flow & free cash flow should be positive and increasing. Same goes for earnings growth and profitability metrics (ROE %, ROA %). Generally speaking, as long as these figures are all sloping up, it is most likely a solid fundamental company.

Finally, the market analysis gives us an idea of how expensive or cheap the stock is relative to historical prices. Though, 5 years is a relatively short period of time. But that is as best as free platform data gives.

5.1 Creating New Table for Stock Prices

Importing in the share price is somewhat a manual process. I have to create a new table, find out the stock price as of that date and establish a relationship with the income statement date.

I chose to do it this way since there are only 5 entries. It would probably be more efficient rather than trying to figure out how to import a new data source of stock prices elsewhere.

6. Changing Stock Quote Symbol

What if instead of MSFT we want to see the stock analysis dashboard of Facebook (FB)? How do we change it?

To do this, you can change the original URL source link when we import in the financial statements. This can be found under the Power Query Editor of Power BI.

Click on the Settings button located beside the “Source” on the right panel to change the stock quote symbol. Right now we are looking at MSFT. I did a test by changing it to FB. If the number of columns matches, then nothing needs to be changed. It will update the new financial figures of FB.

6.1 Fixing Bugs & Error Messages

However, more often then not, the financial line items of each company won’t match exactly. For example, when I tried to change the URL link of the balance sheet from MSFT to FB. It throws up this error message.

Power BI is trying to say that the previous table (MSFT) has a column called “Total Inventory” but now that I changed it to (FB), this column is missing. That is because FB doesn’t have an inventory line item on its balance sheet.

Fixing this is quick. The error message occurs at the step where Power BI attempts to change the data type of all the columns. To remove the error, we just click the “X” mark on the left-hand side to undo that step.

Next, we have to ask Power BI to change the data types of all columns again, this time with FB new column names. To do that, select all columns and click “Detect Data Type”. This would automatically change all the columns into their respective data types.

To summarise, if you want to change a stock quote symbol, go to the edit query page, click on source settings, change the URL link, undo the step, select all columns and ask Power BI to detect data type again.

Repeat the steps above for all 3 financial statements. It should take about 1-2 minutes to change a stock quote symbol and change the data type.

Some other kinds of errors could occur in a column or your DAX formulas. If the column is not used in any formulas or analysis, the simplest way would be to just remove the entire column.

If the column doesn’t exist, for example, “inventory” on Facebook, then quick ratio won’t work. So you have to tweak your formula to remove inventory from it.

7. Summary

Here is how Facebook dashboard looks like.

Surprising to know they have almost 0% debt.

Nevertheless, here is a glimpse of how Power BI can be used for stock market analysis. If you want a more granular view, you can just change the URL link to a quarterly format on Reuters.

Though it is not completely automated, it saves up a lot of time as you only have to design the dashboard and measures ONCE. But the dashboard allows you to see any stock symbol of your preference. The only manual actions are to change the quote in the URL link, fix minor errors and update the stock price.