SpearianCAPM

 

Syntax: SpearianCAPM(StockTickers, IndexTickers, ReferenceFromDate, ReferenceToDate, Fields, ShowLabels, AutoFormat)

 

Equivalent Function Names: SPCAPM

 

Purpose: Function used to calculate the CAPM model

 

Parameters: 
 

1.StockTickers and IndexTickers are the standard Spearian Tickers. Review the Tickers guidelines here.

The function works in 3 modes:

1. Single StockTickers and missing or single IndexTickers - the output can be the full calculation result if the Fields are missing or AlphaBeta or All

2. Multiple StockTickers and missing IndexTickers - the output is a vector of a specified Field - the Fields cannot be AlphaBeta or All

3. Single or Multiple StockTickers and Single or Multiple IndexTickers where at least one of them is Multiple - the output is a matrix of a specified Field - the Fields cannot be AlphaBeta or All


Automatic IndexTicker: IndexTicker may be optional - if IndexTicker is not specified, and if the StockTicker is given and if it is a Yahoo! Ticker, the IndexTicker is inferred from the below mapping table:

StockTicker Suffix

Country

Exchange

Inferred IndexTicker

 

United States of America

 

Y::^GSPC

.AS

Netherlands

Amsterdam Stock Exchange

Y::^AEX

.AX

Australia

Australian Stock Exchange

Y::^AORD

.BA

Argentina

Buenos Aires Stock Exchange

Y::^MERV

.BC

Spain

Barcelona Stock Exchange

Y::^IBEX

.BE

Germany

Berlin Stock Exchange

Y::^GDAXI

.BI

Spain

Bilbao Stock Exchange

Y::^IBEX

.BM

Germany

Bremen Stock Exchange

Y::^GDAXI

.BO

India

Bombay Stock Exchange

Y::^BSESN

.CBT

United States of America

Chicago Board of Trade

Y::^GSPC

.CME

United States of America

Chicago Mercantile Exchange

Y::^GSPC

.CMX

United States of America

New York Commodities Exchange

Y::^GSPC

.CO

Denmark

Copenhagen Stock Exchange

Q::NASOMXNORDIC/DX0000001376

.DE

Germany

XETRA Stock Exchange

Y::^GDAXI

.DU

Germany

Dusseldorf Stock Exchange

Y::^GDAXI

.F

Germany

Frankfurt Stock Exchange

Y::^GDAXI

.HA

Germany

Hanover Stock Exchange

Y::^GDAXI

.HK

Hong Kong

Hong Kong Stock Exchange

Y::^HSI

.HM

Germany

Hamburg Stock Exchange

Y::^GDAXI

.JK

Indonesia

Jakarta Stock Exchange

Y::^JKSE

.KQ

South Korea

KOSDAQ

Y::^KS11

.KS

South Korea

Korea Stock Exchange

Y::^KS11

.L

United Kingdom

London Stock Exchange

Y::^FTSE

.MA

Spain

Madrid Stock Exchange

Y::^IBEX

.MC

Spain

Madrid SE C.A.T.S.

Y::^IBEX

.MF

Spain

Madrid Fixed Income Market

Y::^IBEX

.MI

Italy

Milan Stock Exchange

Y::FTSEMIB.MI

.MU

Germany

Munich Stock Exchange

Y::^GDAXI

.MX

Mexico

Mexico Stock Exchange

Y::^MXX

.NS

India

National Stock Exchange of India

Y::^BSESN

.NX

France

Euronext

Y::^FCHI

.NYB

United States of America

New York Board of Trade

Y::^GSPC

.NYM

United States of America

New York Mercantile Exchange

Y::^GSPC

.NZ

New Zealand

New Zealand Stock Exchange

Y::^NZ50

.OB

United States of America

OTC Bulletin Board Market

Y::^GSPC

.OL

Norway

Oslo Stock Exchange

Q::NASOMXNORDIC/SE0002947515

.PA

France

Paris Stock Exchange

Y::^FCHI

.PK

United States of America

Pink Sheets

Y::^GSPC

.SA

Brazil

BOVESPA – Sao Paolo Stock Exchange

Q::BCB/7

.SG

Germany

Stuttgart Stock Exchange

Y::^GDAXI

.SI

Singapore

Singapore Stock Exchange

Y::^STI

.SS

China

Shanghai Stock Exchange

Y::^SSEC

.ST

Sweden

Stockholm Stock Exchange

Q::NASOMXNORDIC/SE0000337842

.SW

Switzerland

Swiss Exchange

Y::^SSMI

.SZ

China

Shenzhen Stock Exchange

Y::^SSEC

.TA

Israel

Tel Aviv Stock Exchange

Y::^TA100

.TO

Canada

Toronto Stock Exchange

Y::^GSPTSE

.TW

Taiwan

Taiwan Stock Exchange

Y::^TWII

.TWO

Taiwan

Taiwan OTC Exchange

Y::^TWII

.V

Canada

TSX Venture Exchange

Y::^GSPTSE

.VI

Austria

Vienna Stock Exchange

Y::^ATX


 

2.Parameter ReferenceFromDate and ReferenceToDate are optional.

If provided, it can be specified either as an Excel date or in the text format "yyyy-MM-dd", e.g. "2013-02-25". The time range can be entered using relative time periods, see here.

If not provided, the default of the last 2 years is applied.
 

3.Fields- If not specified or specified as "All", returns the full result of the model:


Other possible values of the Fields are:
a) "A" or "Alpha" returning only Alpha


b) "AB" or "AdjBeta" or "AdjustedBeta" returning only Adjusted Beta



c) "B" or "Beta" returning only Beta


d) "BA" or "BetaAlpha" returning only Beta and Alpha

 

4.ShowLabels - an optional flag specifying if the result should contain the labels
 

5.AutoFormat - an optional flag signifying if the result should be automatically converted to a range and if the result should be automatically formatted as Excel Data Types

 

Calculation Methodology:

1.The matrix of all price data of the default columns is retrieved

2.All rows of the matrix where at least one number is NaN are removed

3.The matrix is transformed into a log returns matrix

4.The log returns from the stock are regressed against the the log returns from the index

5.Adjusted beta (long term beta is converging to 1) is defined as 2/3 * Estimated Beta + 1/3 * 1

 

Note: Risk-free rate is ignored.
 

Note: Recommended Set-Up is weekly returns for 2 years.

 

Examples:

 

1. Calculate the CAPM results for Microsoft on weekly data from the last 2 years using S&P 500 as the index

=SPCAPM("MSFT::W")

 

2. Calculate the CAPM results for Microsoft on weekly data from the last 2 years using the IBM as the index

=SPCAPM("MSFT::W", "IBM::W")

 

3. Calculate the beta for Microsoft and IBM on weekly data from the last 2 years using S&P 500 as the index

=SPCAPM("MSFT::W,IBM::W")

 

 

 

4. Calculate the adjusted beta for Microsoft and IBM on weekly data from the last 2 years using S&P 500 as the index

=SPCAPM("MSFT::W,IBM::W",,,,"adjbeta")

 

 

 

 

5. Calculate the betas for Microsoft and IBM on weekly data from the last 2 years using JPMorgan and Apple as indices

=SPCAPM("MSFT::W,IBM::W","JPM::W,AAPL::W")

 

 

 

 

Copyright © 2013-2017 Jiri Pik

Document Version: Sunday, May 7, 2017