Build a market-cap weighted index

Manipulating Time Series Data in Python

Stefan Jansen

Founder & Lead Data Scientist at Applied Artificial Intelligence

Build your value-weighted index

Key inputs:
- number of shares
- stock price series

Build your value-weighted index

Key inputs:
- number of shares
- stock price series
- Normalize index to start at 100

ch4_2_v2 - Build Market-Cap Weighted Index.006.png

Stock index components

components

                                Company Name  Market Capitalization  Last Sale
Stock Symbol                                                                  
PG            Procter & Gamble Company (The)             230,159.64      90.03
TM                 Toyota Motor Corp Ltd Ord             155,660.25     104.18
ABB                                  ABB Ltd              48,398.94      22.63
KO                   Coca-Cola Company (The)             183,655.31      42.79
WMT                    Wal-Mart Stores, Inc.             221,864.61      73.15
XOM                  Exxon Mobil Corporation             338,728.71      81.69
JPM                    J P Morgan Chase & Co             300,283.25      84.40
JNJ                        Johnson & Johnson             338,834.39     124.99
BABA           Alibaba Group Holding Limited             275,525.00     110.21
T                                  AT&T Inc.             247,339.52      40.28
ORCL                      Oracle Corporation             181,046.10      44.00
UPS              United Parcel Service, Inc.              90,180.89     103.74

Number of shares outstanding

shares = components['Market Capitalization'].div(components['Last Sale'])

Stock Symbol
PG     2,556.48 # Outstanding shares in million
TM     1,494.15
ABB    2,138.71
KO     4,292.01
WMT    3,033.01
XOM    4,146.51
JPM    3,557.86
JNJ    2,710.89
BABA   2,500.00
T      6,140.50
ORCL   4,114.68
UPS      869.30
dtype: float64

Market Capitalization = Number of Shares x Share Price

Historical stock prices

data = pd.read_csv('stocks.csv', parse_dates=['Date'],         
                            index_col='Date').loc[:, tickers.tolist()]
market_cap_series = data.mul(no_shares)
market_series.info()

DatetimeIndex: 252 entries, 2016-01-04 to 2016-12-30
Data columns (total 12 columns):
ABB     252 non-null float64
BABA    252 non-null float64
JNJ     252 non-null float64
JPM     252 non-null float64
...
TM      252 non-null float64
UPS     252 non-null float64
WMT     252 non-null float64
XOM     252 non-null float64
dtypes: float64(12)

From stock prices to market value

market_cap_series.first('D').append(market_cap_series.last('D'))

                 ABB       BABA        JNJ        JPM         KO       ORCL  \\
Date                                                                          
2016-01-04 37,470.14 191,725.00 272,390.43 226,350.95 181,981.42 147,099.95   
2016-12-30 45,062.55 219,525.00 312,321.87 307,007.60 177,946.93 158,209.60   
                   PG          T         TM       UPS        WMT        XOM  
Date                                                                         
2016-01-04 200,351.12 210,926.33 181,479.12 82,444.14 186,408.74 321,188.96  
2016-12-30 214,948.60 261,155.65 175,114.05 99,656.23 209,641.59 374,264.34

Aggregate market value per period

agg_mcap = market_cap_series.sum(axis=1) # Total market cap

agg_mcap(title='Aggregate Market Cap')

ch4_2_v2 - Build Market-Cap Weighted Index.015.png

Value-based index

index = agg_mcap.div(agg_mcap.iloc[0]).mul(100) # Divide by 1st value

index.plot(title='Market-Cap Weighted Index')

ch4_2_v2 - Build Market-Cap Weighted Index.020.png

Let's practice!

Manipulating Time Series Data in Python