How to Use Power BI for Stock Market Analysis

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.

How to Use Power BI for Stock Market Analysis
1. Copy Reuter’s MSFT Financial URL link
How to Use Power BI for Stock Market Analysis
2. Import data source by web link on Power BI
How to Use Power BI for Stock Market Analysis
3. Transform Data (Cleaning)

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

How to Use Power BI for Stock Market Analysis

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.

How to Use Power BI for Stock Market Analysis

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.

How to Use Power BI for Stock Market Analysis

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.

  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  

30 Comments

  1. Hi Chang,

    I also use Power BI to see our company CRM and latest / hot opportunities by segments.

    While acknowledging its powerful, it was not used as frequently in our company.

    • Hey Rolf! That’s interesting to know that you also use it. Yea, I think most companies only use Power BI for reporting or presentations purposes lol

    • Hmm if you are referring to a filter dropdown list of different stocks to analyse, it is a bit hard unless the data you import has ALL the fundamental data for each stock symbol. That’s why the workaround I can think of is to change the URL source link for now. If you figured out a better idea feel free to share it here! I would be interested to know as well.

      • Yes, you have to use .pbit files (template files). Then no changes to the PQ code will be required and only the stock you’re interested in will be loaded.

  2. Is it possible to do a dashboard in Power BI for personal finance in terms of monitoring the monthly amount of income and expenditure? Are there any useful guides for it?

    • Hey Wen Jie! I do my personal finance on simple excel sheet lol. But I link it to Power BI just for the visualisations. (Portfolio tab e.g.) Power BI and Excel are v similar and related so you can play it around see what works for u!

  3. Hi there. Thank you for sharing how you use Power bi. Can I ask, how did you manage to input the formula part? Can’t seem to figure it out.

  4. Hello,

    I recreated the dash, for the most part. I think there is an issue with the EPS growth, everything else matches yours. I double/triple checked my numbers but they just don’t match yours. Not sure… Great write up. I’m going through your balance sheet, cashflow, and income statement write ups also. Appreciate the time you took to put it together!

  5. This is an AWESOME tutorial! I had built a power bi dashboard but I’ve been combing through financials and inputting data onto a spreadsheet. I want to be able to add a filter so I can just select the ticker symbol and see the visuals change. Do you know how I can add a custom column in power query with the name of the data source? If I can do that, I’m thinking I can merge all of the data sources together and then use the custom Stock Ticker column to filter between them.

  6. Hi, very interesting tutorial. I am new to Power Bi. Once this is all set-up, how does the referesh of the dataset work out? I did not see it in the tutorial.
    Thanks

  7. Hi,
    I am having problem with yoy Growth rate calculation, I checked the dates for financial year closing are different for every year. I only see only 1 data point . Could you elaborate more YOY growth formula . Thanks

  8. This would have been so much better if you had not skipped steps on how to create the formulas in step 4. As you can see from the comments, most people are lost on this step including me. I saw you comment about selecting “New Measure” but that creates the formula in an existing query. How did you create the separate “formulas” group(query?) shown in step 5? BTW, Power BI is totally new to me.

    • Step 4 is creating a new measure. You can write any formula you want there. Step 5 is the original data source table. Those fields are already there when you import in the data. I thinking if u guys would be interested if I do a video on this lol, since there are many confusion surrounding the steps

  9. Hi ChangYueSin,

    Great guide for the next step in analysis of companies. You describe how to extract balance sheet info etc. but you do not elaborate on where/how you pull you stock price quotes. I see you have a table for it, so I was hoping you could elaborate that part. Thanks!

  10. Hi there. First, thank you for putting this all up for public view and thus helping others see the power of the Power BI platform and also promoting financial knowledge. That’s really something to praise. There are some not-quite-correct things I’ve noted as a hardcore DAX programmer, though. To perform (always) correct time-intelligence calculations in PBI you cannot use the time-intel functions on any date column in any table. You have to have a date table dedicated to this task (it must adhere to certain rules, see for instance http://dax.guide/sameperiodlastyear) and marked as such in the model. So, your formulas as they are right now hide some traps and will certainly return wrong results before you know it. Secondly, you don’t have to manually adjust the code in the Power Query editor. You can simply create a template file (.pbit) which will let the user that opens it select the stock ticker from a list you’ll define. Everything from this point onward will be done automatically, from the beginning to the very end. Hope this is helpful. Again, thanks for putting this all up.

Leave a Reply

Your email address will not be published.