cancel
Showing results for 
Search instead for 
Did you mean: 

Analyzing Market Caps of Any Listed Companies in Power BI

The purpose of this tutorial is to analyze and compare the market value of companies over time. To do this, we will see step by step how to download data from the Yahoo Finance site and then how to import this data into Power BI and process it in order to create a report.

 

Step I: Downloading data from the Yahoo Finance website 

In this step you should:

  1. Go to the Yahoo Finance website (https://fr.finance.yahoo.com)
  2. Type the company of your choice (in our case Facebook, which has FB symbol) in the Yahoo search bar
  3. In the bottom menu, click on “historical data”

 

1.gif

 

4. Indicate the maximum duration (you should select "Max" in order to have all historical data)

5. Select "Daily" for frequency

6. Click on “Apply”

7. Finally, click on “download“.

2.gif

2.gif


NB: Repeat the same steps for all the desired companies.

In this tutorial we will take a second company, Google (GOOG symbol).

 

Step II: Data processing on Power BI Desktop
 

 

In the previous step we downloaded the Facebook and Google data from the Yahoo Finance site. Now we will import and process this data. It should be noted that for this tutorial, we used the version: 2.75.5649.961 64-bit (November 2019) of Microsoft Power BI Desktop.

 

  1. In Power BI Desktop, Click on the "File" tab then "New"
  2. In the window that appears click on "Get data"
  3. Click on "Folder"
  4. Fill in the filepath of your folder (the folder in which your files are previously downloaded). Then click "ok"
  5. In the window that opens click on "combine" then on "combine and transform"
  6. The combined files window is displayed, click on "OK".
  7. Apply the changes

 

3.gif

 

Editing Queries

 

On the Home tab, in the External data group, click on "Edit queries".

 

  1. Right under “APPLIED STEPS” in the pane “Query Parameters” click on “Source”.
  2. Click the two arrows to the right of the "Content" column to expand the columns of the tables.
  3. Rename the first column “source.name” to “Company”
  4. We will then select the csv files relating to Facebook and Google
  5. In the table obtained click the launcher to the right of the “Company” column, check only the boxes relating to Facebook and Google, click ok.

 

4.gif

Taking into account the number of shares.
 

Here, we have to go to the Yahoo Finance site to get a hold of a statistical value called "Shares Outstanding". To do this, we must:

  1. Go to the Yahoo Finance website (https://fr.finance.yahoo.com)
  2. Type in the Yahoo search bar, the company of your choice (in our case Facebook)
  3. In the bottom menu, click on “Statistics”
  4. Note the number corresponding to "Pending Actions" under "Action Statistics"

 

NB: Do the same for Google.

 

5.gif
This value will be used in the Stat table which we will create later.

Creating the Stat table

 

  1. In Home click on “Enter data”
  2. Add columns and rows by clicking on “*”
  3. In the column "Shares Outstanding" we will put the statistical values, relating to Facebook and Google, noted in the previous step.

 

 

Conversion of Columns (Low, high) to decimal

 

  1. Right click on Low, click on Replace values and replace the “.“ with “,”
  2. Then, right click on  the icon “abc” to convert to decimal.

NB: Do the same for The High column and Adj Close.

 

 

6.gif


Merging the table (Facts and Stat)

 

It is time to merge our two tables. As a prerequisite, we must make sure that the Company columns have the same content:

1. Right click on the “Company” column, click on “Replace values”.

we will replace “. Csv” by a vacuum.

 

We can do the merger

 

2. In Home, click "Merge Queries" in the Combine group.

3. Click on the rectangle to add the Stat table

4. Click on the common columns (in this case Company), from which the merger will take place

5. Click ok.

 

8.gif


Expand the Stat column

 

1. Right click on the symbol to the right of the Stat column

Select the field “shares Outstanding”

8.gif

8.gif

 

Addition of columns (Events and Market Cap)

 

* Events

 

In this pane we will add the column “Events”:

  1. In Power BI Desktop, click on the tab "Add a column", then click on "Custom column"
  2. Rename it and insert a personalized formula.

 

9.gif

 

* Market Cap

The approach is the same as in “Events”, with the creation of a Custom Column.

The difference is at the level of the formula and the name of the column.

 

10.gif

 

Apply changes

1. To complete part 2, right click on the file and click on Apply

11.gif

11.gif


Step III: Visualization
 

At the end of step 2, we clicked on apply so that the modifications made in

the Query Editor are taken into account. In this last step, we should come back

to the first window to display visual elements.

 

To do this you must:

  1. Double click on the visual element of your choice
  2. Select a field, drag and drop the different values.

 

12.gif

 

 

Tutorial by Roméo Brunège Kouata, MYPE.

Original idea by Augustin de la Fouchardiere, MYPE

Comments

Nice tutorial. Fun to follow along, but can you add the formula you used for the Event column to the text so we can copy/paste? It's hard to keep up with the animations. I had to screenshot the final formula. 😅 The Market Cap is easy enough, but the Events calculation would be nice to have if someone isn't terribly advanced with writing their own (me). 

hello @lbudack 

 

Many thanks for your feedback

 

Please find here the code of the Events Column. (in my example, it contains values for Google, Apple, Facebook, Microsoft and Amazon: 

 

Please also give me your email adress, I'll provide you access to the power bi file.

BR,

Augustin de la Fouchardiere

MYPE

---

 

if[Company]="AAPL" and [Date] = #date(2018, 8, 1) then "Apple reaches 1 Trillion market cap"

else if [Company]="AAPL" and[Date] = #date(2019, 1, 3) then "Tim Cook issues the first Apple profit warning of the iPhone era"

else if [Company]="AAPL" and[Date] = #date(2011, 10, 5) then "Apple's Steve Jobs dies at 56"

else if[Company]="AAPL" and [Date] = #date(2007, 1, 9) then "Apple officially launches the iPhone"

else if [Company]="AAPL" and[Date] = #date(1980, 12, 12) then "Apple's IPO"

else if [Company]="AAPL" and[Date] = #date(1997, 8, 6) then "Apple rescued by Microsoft"

else if [Company]="GOOG" and[Date] = #date(2004, 4, 01) then "Google Launches Gmail"

else if [Company]="GOOG" and[Date] = #date(2004, 8, 19) then "Google's IPO"

else if [Company]="GOOG" and[Date] = #date(1998, 9, 27) then "Launch of Google search engine"

else if [Company]="GOOG" and[Date] = #date(2008, 9, 23) then "Initial release of Google's Android"

else if [Company]="GOOG" and[Date] = #date(2008, 9, 02) then "Google launches Chrome web browser"

else if [Company]="GOOG" and[Date] = #date(2012, 12, 21) then "Gangnam Style is the first YouTube video to reach 1 billion views"

else if [Company]="GOOG" and[Date] = #date(2017, 10, 29) then "Google joins Apple in $700 billion club in a fraction of the time"

else if [Company]="GOOG" and[Date] = #date(2013, 07, 03) then "Android gets 70% market share"

else if [Company]="GOOG" and[Date] = #date(2015, 10, 23) then "Google’s market capitalization surpasses the half trillion mark as Alphabet’s stock soars"


else if [Company]="MSFT" and[Date] = #date(2001, 11, 15) then "Microsoft launches Xbox"

else if [Company]="MSFT" and[Date] = #date(2014, 2, 4) then "Satya Nadella named Microsoft CEO as Bill Gates steps down as chairman"

else if [Company]="MSFT" and[Date] = #date(1986, 3, 13) then "Microsoft's IPO"

else if [Company]="MSFT" and[Date] = #date(1983, 1, 1) then "In 1983, Microsoft did $55 million in sales, making it the biggest company in the computer business"

else if [Company]="MSFT" and[Date] = #date(1995, 8, 24) then "Microsoft Windows 95 Launch"

else if [Company]="MSFT" and[Date] = #date(1985, 9, 30) then "1st version of Microsoft Excel"

else if[Company]="MSFT" and [Date] = #date(2018, 11, 29) then "Microsoft's stock market value pulls ahead of Apple's"

else if[Company]="MSFT" and [Date] = #date(1997, 06, 13) then "LinkedIn bought by Microsoft for $26.2bn in cash"

else if[Company]="MSFT" and [Date] = #date(2018, 12, 31) then "Microsoft closes out 2018 as the top public company"

else if[Company]="MSFT" and [Date] = #date(2016, 06, 13) then "LinkedIn bought by Microsoft for $26.2bn in cash"

else if [Company]="MSFT" and[Date] = #date(1995, 1, 1) then "hanks to his shares in Microsoft, Gates was named as the world's richest person for the first time in 1995"

else if [Company]="MSFT" and[Date] = #date(2013, 9, 2) then "Microsoft buying Nokia's phone business in a $7.2 billion bid for its mobile future"

else if [Company]="MSFT" and[Date] = #date(1985, 11, 20) then "launch of Microsoft Windows 1.0"

else if [Company]="MSFT" and[Date] = #date(1989, 8, 😎 then "Launch of Microsoft Office 1st version"

else if [Company]="MSFT" and[Date] = #date(2000, 3, 10) then "End of the internet 'Dot-com' bubble, Microsoft is the world's largest cap"

else if [Company]="MSFT" and[Date] = #date(2018, 5, 29) then "Microsoft is now more valuable than Alphabet - by about $10 billion"

else if [Company]="AMZN" and[Company]="FB" and [Date] = #date(2017, 07, 27) then "Facebook and Amazon hit $500 billion milestone"

else if[Company]="AMZN" and [Date] = #date(2018, 9, 4) then "Amazon reaches $1 trillion market cap for the first time (on trade)"

else if [Company]="AMZN" and[Date] = #date(2018, 3, 6) then "Amazon's founder becomes richest man on earth, ahead of Bill Gates"

else if [Company]="AMZN" and[Date] = #date(1997, 5, 15) then "Amazon's IPO"

else if [Company]="AMZN" and[Date] = #date(2006, 3, 14) then "Amazon Web Services was officially re-launched on March 14, 2006"

else if [Company]="AMZN" and[Date] = #date(2019, 01, 07) then "Amazon just became the world's most valuable company, ending Microsoft's spot at the top after 5 weeks"

else if [Company]="AAPL" and[Date] = #date(2010, 1, 27) then "Apple Launches iPad"

else if [Company]="AAPL" and[Date] = #date(2008, 7, 10) then "Apple's Steve Jobs introduces the App Store"

else if [Company]="AAPL" and[Date] = #date(2012, 8, 20) then "Apple Becomes Biggest Stock Ever"

else if [Company]="AAPL" and[Date] = #date(1983, 1, 1) then "launch of the first Apple Macintosh"

else if [Company]="AAPL" and[Date] = #date(1985, 9, 16) then "Steve Jobs leaves Apple Computer"

else if [Company]="AAPL" and[Date] = #date(1996, 12, 20) then "Apple brings back Steve Jobs with NeXT buyout"

else if [Company]="AAPL" and[Date] = #date(2001, 10, 23) then "Apple Presents iPod"

else if [Company]="AAPL" and[Date] = #date(1983, 10, 23) then "Apple releases Lisa 1"

else if [Company]="FB" and[Date] = #date(2018, 3, 17) then "Facebook & Cambridge Analytica data scandal"

else if [Company]="FB" and[Date] = #date(2014, 09, 03) then "Facebook Market Cap Tops $200 Billion"

else if [Company]="FB" and[Date] = #date(2014, 10, 06) then "Facebook Closes $19 Billion WhatsApp Deal"

else if [Company]="FB" and[Date] = #date(2012, 10, 04) then "Facebook hits 1 billion users a month"

else if [Company]="FB" and[Date] = #date(2017, 06, 27) then "Facebook hits 2 billion monthly users"

else if [Company]="FB" and[Date] = #date(2018, 07, 26) then "Facebook stock drops roughly 20%, loses $120 billion in value after warning that revenue growth will take a hit"

else if [Company]="FB" and[Date] = #date(2012, 5, 18) then "Facebook's IPO"

else null