Beta, SCL with bulk NSE data

As a continuation of the previous post on calculating beta, we test the model with the closing share prices of 44 companies that are a part of the CNX Nifty index.

Instead of following the Yahoo Finance based data collection strategy shown in the sample program of the text book, we have written our own data extraction routine based on Quandl. Note that
niftStks04_* is a set of four lists that have names of NIFTY companies for which we have data from 2004
niftStks09 is a list of names of NIFTY companies for which we have data from 2009
niftStks13 is a list of names of NIFTY companies for whch we have data from 2013

For the purpose of our exercise we have used data from 2009 onwards so that we have excluded the last list.

As a sanity check, we have extracted the CNX Nifty data, twice, once along with the company share price data and once as a standalone data with the corresponding dates. By laying out this data side-by-side in the Close01 data.frame through a cbind, we can see the dates of the closing prices of the company data.

========================================
   setwd("C:/Users/admin/Desktop/Data Analytics/QuantitativeFinance/QFLabs")
getwd()
library(Quandl)

Quandl.auth("xxxxxxxxxxxxx")

Cstartdate = '2009-06-01'
Cenddate = '2014-06-01'
IIR00 <- Quandl('BCB/17901', trim_start = Cstartdate, trim_end = Cenddate)
IIR00$Value <- IIR00$Value/1200
NIFTY <- Quandl("NSE/CNX_NIFTY", trim_start = Cstartdate, trim_end = Cenddate, collapse="monthly")
NIFTY01 <- NIFTY[,c('Date','Close')]

nifStks04_1 <- c("ACC","ASIANPAINT","BANKBARODA","BHEL","CIPLA","DRREDDY","GAIL","GRASIM","HCLTECH","HDFCBANK","HDFC","ITC","ICICIBANK","IDFC","INDUSINDBK","JPASSOCIAT")
nifStks04_2 <- c("JINDALSTEL","KOTAKBANK","LT","LUPIN","MM","MARUTI","NTPC","ONGC","PNB","RANBAXY","RELIANCE")
nifStks04_3 <- c("SBIN","SUNPHARMA","TCS","TATAMOTORS","TATAPOWER","TATASTEEL","ULTRACEMCO","WIPRO")
nifStks09_1 <- c("AMBUJACEM","AXISBANK","BHARTIARTL","CAIRN","HINDALCO","HINDUNILVR","BAJAJ_AUTO","DLF","NMDC")
nifStks13_1 <- c("COALINDIA","HEROMOTOCO","SSLT","INFY")

#comps <- c("CNX_NIFTY","TATAELXSI", "TCS", "TATASTEEL","TATACOMM","TATAMOTORS","TATAMETALI","TATASPONGE","TATACHEM","TATAPOWER","TATACOFFEE")

comps <- c("CNX_NIFTY",nifStks04_1,nifStks04_2,nifStks04_3,nifStks09_1)

Close <- as.data.frame(sapply(comps, function(x) {
  NSEx <- paste("NSE", x, sep = "/")
  Quandl(NSEx, trim_start = Cstartdate, trim_end = Cenddate, collapse="monthly")$Close
}))

Close01 <- cbind(NIFTY01,Close)
write.csv(Close01,"NIFTY44Close.csv")
head(Close01)


IIR01 <- IIR00$Value

logreturn1 <- function(x) log(head(x,-1)/tail(x,-1))
##riskpremium <- function(x) logreturn1(x) - head(IIR01,-1)
riskpremium <- function(x) logreturn1(x) - IIR00$Value

Returns <- as.data.frame(apply(Close,2,riskpremium))

head(Returns)

r <-t(sapply(comps, function(comp)
  c(beta = lm(Returns[,comp] ~ riskpremium(NIFTY01$Close))$coefficients[[2]],
    mean = mean(Returns[,comp]))
  ))
r1 <- as.data.frame(r)
plot(r1$beta, r1$mean)
abline(lm(r1$mean ~ r1$beta), col = 'red')
========================================

Once the program is run, the following graph is generated

Calculating Beta for a specific stock

The Capital Asset Pricing Model, explained in Chapter 3 of our reference Resource 1 [ Introduction to R for Quantitative Finance ], leads us to the concept of Beta that is a measure of the risk of a particular security when compared to the risk of the market in general.

Beta is calculated on the basis of three input parameters, namely

• The returns on a given security, based on the logreturn formula, for example Tata Steel
• The returns of the market, which in our case will be returns on the CNX Nifty
• The risk free returns, represented in our case by bank interest in India.

Now we can run the the following program

========================================
setwd("C:/Users/admin/Desktop/Data Analytics/QuantitativeFinance/QFLabs")
getwd()
library(Quandl)
Quandl.auth("xxxxxxxxxxxxxxxxxxxx")

Cstartdate = '2007-06-01'
Cenddate = '2014-06-01'
##LIBOR <- Quandl('FED/RILSPDEPM01_N_B', start_date = Cstartdate, end_date = Cenddate)
IIR00 <- Quandl('BCB/17901', trim_start = Cstartdate, trim_end = Cenddate)
IIR00$Value <- IIR00$Value/1200
NIFTY <- Quandl("NSE/CNX_NIFTY", trim_start = Cstartdate, trim_end = Cenddate, collapse="monthly")
TISCO <- Quandl("NSE/TATASTEEL", trim_start = Cstartdate, trim_end = Cenddate, collapse="monthly")

cdates <- Reduce(intersect,list(IIR00$Date,NIFTY$Date,TISCO$Date))

IIR01 <- IIR00[IIR00$Date %in% cdates,'Value']
NIFTY01 <- NIFTY[NIFTY$Date %in% cdates,'Close']
TISCO01 <- TISCO[TISCO$Date %in% cdates,'Close']


## this is perhaps clear accurate
logreturn1 <- function(x) log(head(x,-1)/tail(x,-1))
riskpremium <- function(x) logreturn1(x) - head(IIR01,-1)

lrTIS1 <- logreturn1(TISCO01)

beta1_TISCO <- cov(logreturn1(TISCO01)-head(IIR01,-1),logreturn1(NIFTY01)-head(IIR01,-1))/var(logreturn1(NIFTY01)-head(IIR01,-1))
beta2_TISCO <- cov(riskpremium(TISCO01),riskpremium(NIFTY01))/var(riskpremium(NIFTY01))


(fit <- lm(riskpremium(TISCO01) ~ riskpremium(NIFTY01)))

plot(riskpremium(NIFTY01),riskpremium(TISCO01))
abline(fit,col = 'red')

(fit1 <- lm(riskpremium(TISCO01) ~ -1 +riskpremium(NIFTY01)))

summary(fit)
summary(fit1
========================================

Here what we are doing is as follows
We are getting the India Interest Rates, the NIFTY CNX closing prices and the Tata Steel Closing Prices for the same set of data. Note that for the 7 year data requested, we get 84 data points for the interest rates and 85 data points for the NSE data, which is good because in calculating returns from closing prices, we will be using data for two consecutive points and so we will end up with 84 data points

Also we note that unlike Google Data used in the book, the India data comes in descending order of dates [ later dates come earlier in the list ] and so the logreturn is calculated as
logreturn1 <- function(x) log(head(x,-1)/tail(x,-1) that is different from what is given in the book.

The program runs and we note that the beta calculated from ratio of covariances [ 1.7685] is the same as that obtained from the regression coeffient [1.768501] of the risk premium.

The Security Characteristic Line (SCL) is obtained as follows

---

---

The same analysis can be carried out using a spreadsheet and this is demonstrated in this Google Docs spreadsheet. The beta values calculated using the ratio of Covariance to Variance is very close to the value obtained using R (1.7691 vs 1.7685 ). However slope obtained in the spreadsheet (1.7905) is a little different.