What is Stock Market Index?
Stock index is basically an index which measures a stock market or market performance by allocating the weighted selected stocks within an index. Serving as a powerful indicator for economies, stock market index enables investors to compare both current price level of stock with the past and stock indices with each other. Stock market index can be based on related stocks, stocks in the same industry, stocks in the same market and stocks of the similar market capitalization.
What do I mean by “weighted”?
There are different types of weighting methods exists for different stock market indices. We can weight by equals, weight by market cap or weight by free-floated market cap. For example, Dow Jones Industrial Average (DJIA), a well-known index, is a price-weighted index while S&P 500 is weighted by market capitalization. In this post, I will focus on one of this weighting methods which is Free-float Adjusted Market-Capitalization Weighting.
Free-Float Methodology
By following free-float methodology, market capitalization can be calculated by multiplying the number of shares available with the equity’s price. The difference between full-market capitalization method and free-float method is caused by the shares used in calculations. In full-market capitalization whether active or not all shares considering however, in free-float market method shares owned by insiders, governments etc. are not taken into account. So we might say that full-market capitalization is broader than free-float or float-adjusted capitalization.
Practical Example of Free-float index Weighting
Step by Step
1. Import the data by using Google Finance
2. Free-float Index Weighting
3. Index Prices
4. Visualize by Graphs
In this example, I used Google Sheets as a tool and FAANG stocks for stock market index. For the ones who are unfamiliar, FAANG Index includes five American tech companies, which are Facebook, Amazon, Apple, Netflix and Alphabet(Google).
1. Import the data by using Google Finance
In Google sheets I opened 5 different sheets for each company and named them accordingly (Page 1àFacebook (FB), Page 2 àAmazon (AMZN), Page 3 àApple (AAPL), Page 4 à Netflix(NFLX), Page 5 àGoogle(GOOG) )
For each sheet, I import the price data from Google Finance and choose the date interval by using this statement
=GOOGLEFINANCE(“ticker”;"attribute”;"start-date"; “end-date" ;"interval")
Note: You can either use , or ; it depends on your settings. You can easily see the necessary information for using this function when you write GOOGLEFINANCE()
So for First Sheet, I used the function below in A1 and press enter
=GOOGLEFINANCE("NASDAQ:FB";"price";"2016,1,1"; "2016,12,31" ;"DAILY")
For the Second One, I import the price information of Amazon by using
=GOOGLEFINANCE("NASDAQ:AMZN";"price";"2016,1,1"; "2016,12,31" ;"DAILY")
Note; You can use different intervals like “WEEKLY” or different start and end date for your own choice.
For the third one,
=GOOGLEFINANCE("NASDAQ:AAPL";"price";"2016,1,1"; "2016,12,31" ;"DAILY")
For the fourth one,
=GOOGLEFINANCE("NASDAQ:NFLX";"price";"2016,1,1"; "2016,12,31" ;"DAILY")
For the fifth one,
=GOOGLEFINANCE("NASDAQ:GOOG";"price";"2016,1,1"; "2016,12,31" ;"DAILY")
In the imported data you can name column A with “date” and column B with “close”.
2. Free-float Index Weighting
2.1 Finding Changes
After İmport the price data for each company, we should find the daily change in prices again for each company. To do that, we can use 3. Column ( C ) and name it as “Change”, however you can use any column since the formula for change will not depend on that.
Assuming you indicate C1 as “change” and your inputs started in the 2. Row (1. Row is for headers) You can easily calculate the daily change by writing =B3/B2-1 to C3
We can formulate this as Current Closing Price/Previous Closing Price -1
Change the result to percentage, then apply the same function to each row by double clicking to right-end of cell C3
Find the changes for each sheet, for each company
2.2 Shares
To find shares outstanding for each company, again we need to import data from Google Finance. For that purpose we prefer to use the attribute called“shares”. We determine a base to convert shares to smaller values, I will prefer to divide by 1 000 000 000.
Into a new cell, let’s call G3 I wrote shares and to G4 I applied this function;
=GOOGLEFINANCE("NASDAQ:FB";"shares")/1000000000
To G5, I wrote
=GOOGLEFINANCE("NASDAQ:AMZN";"shares")/1000000000 and write it for each company into same column, which is G.
2.3 Free Floated Share
Unfortunately we couldn’t import the free floated share data from Google Finance directly since it is highly subjective number. However we can easily find the necessary numbers by searching in the internet. For example, I searched “Facebook free floated share”
I choose the numbers in https://finviz.com/quote.ashx?t=FB but you can use other sites too, there may be differences its not that important since these differences are relatively small.
To find Free Floated Share, I previously mention that we need to exclude the shares owned by insiders, government etc. Regarding that we can calculate Facebook’s Free floated share by
100% – Insider Ownership (Insider Own) – Institutional Ownership (Inst Own) =
For Facebook
100%- 0.66% - 80.00%= 19.44%
We can do the same calculation for each company and write them in a new column, preferably H and name that column as Free Floated Shares %.
2.4 Free float, bin weighted USD
Into a new column, preferably I, we can write Free-floated bin shares and calculate it as
=shares,bin*free-floatedshare*GOOGLEFINANCE("ticker";"price")
For example, for facebook I wrote
=G4*H4*GOOGLEFINANCE("FB";"price") into I4
2.4 Finding Weights
Since we use free-floating as a weighting methodology, we need to find the weights by using the free-floated shares in USD, the amount of money that is available for trade.
So weight can be found by simply taking the ratio of selected company’s free-floated shares in USD to total
I choose column J for indicating weights and for each company I found weight by using
= free-floated price of company/ sum of free-floated prices of companies that are in index
For example, for Facebook I found the weight as
=I4/SUM(I4:I8)
For Amazon =I5/SUM(I4:I8) and so on.
3. Index Prices
To find Index Prices, I copy the dates that I imported fro Google finance to a new column, let’s say N. In my example the date starts with 04.01.2016 16:00:00. For this starting day, I assigned a price to see the changes based on that price. I assigned $1,000,00 and write it into a new cell, O3. Then for the next day, 05.01.2016 16:00:00, I want to see the change in my money and continue to that for the period that I have chosen at the beginning of the post.
=BasePrice*(1+$(weightofFacebook)(changeinFacebookPriceinSameDate)+Page2!(changeinAmazonPriceinSameDate)+*$(weightofAmazon)+Sayfa3!(changeinApplePriceinSameDate)*$(weightofApple)+$weightofNetflix*Sayfa4!(changeinNetflixPriceinSameDate)+Sayfa5!(changeinGooglePriceinSameDate)*$weightofGoogle)
For example, to find the price in 05.01.2016 16:00 I wrote
=O3*(1+$J$4*C3+AMZN!C3*$J$5+$J$6*AAPL!C3+$J$7*NFLX!C3+GOOG!C3*$J$8)
Then double click to apply the same function to other rows in the same column.
This step actually means this;
I have free-floated adjusted stock market index which consists of 5 big American Tech company (FAANG Index) and I invest $1,000,00 in 04.01.2016. How the money that I have invested is going to change within the time period that I am interested in?
Date Price
04.01.2016 16:00:00 $1.000,00
05.01.2016 16:00:00 $985,91
06.01.2016 16:00:00 $977,90
07.01.2016 16:00:00 $938,87
08.01.2016 16:00:00 $938,67
11.01.2016 16:00:00 $952,41
12.01.2016 00:00 $962,28
4.Visualize by graph
In the last step, we just select the dates and prices that we monitored the change of our based price and insert graph, preferably line graph. You can further customize your graph by assigning min value to price (around 700) or changing the colors and styles. You can also show R^2 and best line for better interpretation of the data.
Comments