Analyzing Market Caps of Any Listed Companies in Power BI

[augustindelaf]

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”

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“.

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

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.

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.

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.

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.

Expand the Stat column

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

Select the field “shares Outstanding”

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.

* 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.

Apply changes

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

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.

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

Original idea by Augustin de la Fouchardiere, MYPE