Nov 23, 2011

Volume by Price Charts using R

R-Bloggers is a wonderful site which offers some great ideas for analysis.

While I have been busy of late, hence could not do much with R, I was inspired by this post by Eric Nguyen on Volume by Price chart. 

This chart can be used with a great effect in ones trading strategies. It helps in identifying the support and resistance zones, and a breakout from these levels used as entry/exit points

Extending the work done by Eric, and making few changes in his original code, here is what I have achieved.

Reliance EOD Volume by Price chart

It can now be visually observed the role of volume in providing support/resistance zones.

Future Work: Make animation-like effect to show case the strength offered by volumes at various price levels over a period of time.

Not the best way to code, here is the workable version for more experimentation and extension

It can also be downloaded from here

library(quantmod)
###############################################################
 
#Original Code
#http://blog.datapunks.com/2011/10/volume-by-price-charts-with-r-first-attempt/
 
#Change the ticker to get chart of any "yahoo" symbol
ticker = "RELIANCE.NS" 
symbol <- getSymbols(ticker)
stock <- xts(get(symbol))
 
#remove stock name
names(stock)[names(stock)==paste(symbol,'.Open',sep="")] <- 'Open' 
names(stock)[names(stock)==paste(symbol,'.Close',sep="")] <- 'Close' 
names(stock)[names(stock)==paste(symbol,'.Volume',sep="")] <- 'Volume' 
names(stock)[names(stock)==paste(symbol,'.Adjusted',sep="")] <- 'Adjusted' 
names(stock)[names(stock)==paste(symbol,'.High',sep="")] <- 'High' 
names(stock)[names(stock)==paste(symbol,'.Low',sep="")] <- 'Low'
 
#Add Positive and Negative Volumes
stock$posVbP <- Vo(stock[which(Lag(Cl(stock)) <= Cl(stock))])
stock$negVbP <- Vo(stock[which(Lag(Cl(stock)) > Cl(stock))])
 
 
 
#Since NAs got generated, replace NAs with 0.
stock[is.na(stock)] <- 0
 
#Subset for data since May 2011
myQ <- stock['2011-06::']
 
 
#Define function to add positive and negative volumes by prices
pVolBlock <- function(x) sum(myQ$posVbP[myQ$t==x])
nVolBlock <- function(x) sum(myQ$negVbP[myQ$t==x])
 
funcPriceByVol <- function(x){
 myDiv <- 50         #Divisor for stock
 myQHi <- as.integer(ceiling(max(Cl(myQ))/myDiv)*myDiv)  #Identify High of Series
 myQLo <- as.integer(floor(min(Cl(myQ))/myDiv)*myDiv) #Identify Low of Series
 
 myBreaks <- as.integer(seq(myQLo, myQHi, by=myDiv))  #Create Breaks of interval divisor
 
 # Identify and assign price intervals
 myQ$t <<- myBreaks[findInterval(myQ$Close,myBreaks,all.inside=T)]
 
 myVolsP <- unlist(lapply(myBreaks,pVolBlock)) #Add Positive Volumes to block
 myVolsN <- unlist(lapply(myBreaks,nVolBlock)) #Add Negative Volumes to block
 myVols <- rbind(myVolsP,myVolsN)   #Bind the Positive and Negative Volumes
 colnames(myVols) <- myBreaks    #Define Column Naes
 x=myVols
}
 
myPBV <- funcPriceByVol()
 
 
#lets Plot the graph now
 
  plot(Cl(myQ),yaxt="n", ylab="",xlab="Time",
  ylim=c(min(as.integer(colnames(myPBV))),max(as.integer(colnames(myPBV)))),
  main=paste(ticker, "Close:- Volume by Price"),sub="Market Analyzer http://mypapertrades.blogspot.com/")
 axis(side=2,las=1)
 par(new=T)
 barplot(height=myPBV, 
  beside=F,horiz=T, col=c(rgb(0,1,0,alpha=.3),rgb(1,0,0,alpha=.3)),
  xlim=c(0,max(myPBV[1,]+myPBV[2,])*1.1), 
  space=10, width=3,xaxt="n",yaxt="n",
  las=2)


Aug 11, 2011

Plotting Cumulative FII & DII Inflow against Nifty spot index

Now that I have the FII and DII Inflow along with Nifty Index, I tried my hands on charting!

The objective is to plot the Cumulative FII, DII and Net Inflow and Nifty Index for current year (2011) on a single graph. This post takes inspiration from Deepak Shenoy's post, wherein he has plotted some nice graphs.

The approach is to read the file created earlier for FII & DII Stats, convert the object as zoo (package used is quantmod), filter the data to contain only current years figure values, and then calculate cumulative sum using cumsum() fuction.

The extra effort done here is the use of right axis to display Nifty Index values using axis() function.

The graph contains 4 lines on the same plot, with the legends. If you observe, there is a high co-relation between Nifty spot and Net FII inflow. 68% is what the following line tell. Does this mean our markets trends are dictated by FIIs?

cor(as.numeric(z$Close),as.numeric(z$cumFIINetValue))

Cumulative Institutional inflow in 2011

The code to generate this plot is available here

The code is as below:

library(quantmod)
 
Dir = "D:\\FII Stats"
filename = "FII-DII-Nifty.csv"
 
#Read the csv file created earlier
temp <- read.csv(file.path(Dir, filename), header=TRUE, sep=",") 
temp$Date <- as.Date(temp$Date, format="%d-%m-%Y")
temp  <- temp[order(temp$Date, decreasing=FALSE),]
 
#Define Date as row name
rownames(temp) <- temp$Date
 
temp$ChangePct <- temp$Change*100 / Lag(temp$Close)#[2:length(temp$Close)]
#temp$ChangePct[is.na(temp$ChangePct)] <- 0 
 
z <- as.zoo(temp)
#Filter for 2011
z <- z[z$Date > "2011-01-01"]
 
z$cumFIINetValue <- cumsum(z[,"FIINetValue"])
z$cumDIINetValue <- cumsum(z[,"DIINetValue"])
z$cumEffectiveInflow <- cumsum(z[,"EffectiveInflow"])
 
# Remove NAs
z[is.na(z)] <- 0
 
plot(cbind(z$cumDIINetValue,z$cumFIINetValue,z$cumEffectiveInflow), screens=c(1,1,1), type="l", 
	col=c("blue","red", "green"), xlab="Time",
	ylab="Rs (In Cr)",
	ylim = list(20100:-100,-20100:100), lwd=2,
	main="Cumulative Institutional inflow in 2011", sub="http://mypapertrades.blogspot.com")
 
par(new=TRUE)
plot(z$Close, screens=1,type="l", ann=FALSE, yaxt="n", ylim = 4500:6500, lwd=3, ylab="Nifty")
 
axis(4,labels=TRUE)
legend(x="topleft", bty="n", lty=c(1,1,1,1), col=c("blue","red","green","black"),
       legend=c("DII Inflow", "FII Inflow", "Effective Inflow","Nifty"), lwd=2)
 
 
#cor(as.numeric(z$Close),as.numeric(z$cumFIINetValue))
Created by Pretty R at inside-R.org

Aug 4, 2011

FII and DII turnover with effect on Nifty Downloader

My thirst for statistics has been increasing. IV had another requirement, which would eventually be useful to me as well. He currently downloads FII and DII buy and sell values and its impact on Nifty manually in Excel. He suggested me to try and automate this process in R. Wow! Some more learning of R, which would eventually help me in building my strategies!

Unlike in NSE EOD Bhavcopy and BSE EOD Bhavcopy downloaders, I tried a different approach to download FII + DII stats from NSEIndia website (as if I had a choice), along with the index Nifty values and change over previous day. The National Stock Exchange of India has different structures for different pages. To download index values, you have to refer to the link http://www.nseindia.com/content/indices/ind_histvalues.htm whereas for FII and DII stats, you need to visit http://www.nseindia.com/content/equities/eq_fiidii_archives.htm.

Both of these pages are HTML forms, you have to enter parameters like index name, start and end dates. Another challenge is that each of these pages displays a maximum of 100 rows in table and if you need all rows (more than 100), you have to download a csv (dynamically generated in temp location). I faced problem in accessing the csv, as for older dates, the link generated was not valid. Hence, I decided to read the page itself, parse the table to be consumed as data frame. For instances, where the parameters generate more than 100 rows, I decided to use while loop.

OK, now here is the solution for it.

I am using the packages RHTMLForms, RCurl, and XML. While RCurl and XML packages are available at R repositories, RHTMLForms package is maintained by omegahat, and can be installed by using the following command

install.packages('RHTMLForms', repos = "http://www.omegahat.org/R")

Another peculiar problem faced is that NSE India website has enforced check for useragent, which if not specified explicitly in R, would not allow access to the desired data.

This can be achieved by defining RCurl settings before loading the library. The command is

options(RCurlOptions = list(useragent = "R"))
library(RCurl)

NOTE: 9-Aug-2011, I have modified the code a bit to enable this script with optional input parameters, like Start Date as 16-April-2007, from which both FII and DII stats are available, along with End Date as current date. Another change that has been made is to check, if file already exist; if it does not exist, the script creates the file, if it exists, it reads the last entry in the file, and starts downloading records beyond that to current date.


outputDir = "D:\\FII Stats"
filename = "FII-DII-Nifty.csv"


startDate = as.Date("2007-04-16", order="ymd")
endDate =Sys.Date()


The code can be downloaded from here.

#install.packages('RHTMLForms', repos = "http://www.omegahat.org/R")
 
library(RHTMLForms)
options(RCurlOptions = list(useragent = "R"))
library(RCurl) 
library(XML)  
library(timeDate)
 
blnfileExist=FALSE
 
######################################################################
# User Input
outputDir = "D:\\FII Stats"
filename = "FII-DII-Nifty.csv"
######################################################################
 
 
######################################################################
# Optional User Input
startDate = as.Date("2007-04-16", order="ymd")
endDate =Sys.Date()
 
######################################################################
 
 
 
# If file exists, read the file to retrieve dates for 
# which information already exists
 
	if (file.exists(file.path(outputDir, filename)))
	{
		existingStats <- read.csv(file.path(outputDir, filename), header=TRUE, sep=",")
 
		# Read Last date in the csv and add 1 to begin with next date range
		startDate = end(as.timeDate(existingStats$Date))
		startDate = as.Date(startDate)+1
		blnfileExist = TRUE
	}else
	{
		# User defined startDate 
		existingStats <- NULL
		blnfileExist = FALSE
	}
 
 
 # Read the HTML page since we cannot use htmlParse() directly
 # as it does not specify the user agent or an
 # Accept:*.*
 
urlNifty <- "http://www.nseindia.com/content/indices/ind_histvalues.htm";
urlFIIDIIEq <- "http://www.nseindia.com/content/equities/eq_fiidii_archives.htm"
 
contentNifty = getURLContent(urlNifty)
contentFIIDIIEq = getURLContent(urlFIIDIIEq)
 
 # Now that we have the page, parse it and use the RHTMLForms
 # package to create an R function that will act as an interface
 # to the form.
 
docNifty = htmlParse(contentNifty, asText = TRUE)
docFIIDIIEq = htmlParse(contentFIIDIIEq, asText = TRUE)
 
  # need to set the URL for this document since we read it from
  # text, rather than from the URL directly
 
docName(docNifty) = urlNifty
docName(docFIIDIIEq) = urlFIIDIIEq
 
  # Create the form description and generate the R
  # function "call" the
 
formNifty = getHTMLFormDescription(docNifty)[[1]]
funNifty = createFunction(formNifty)
 
formFIIDIIEq = getHTMLFormDescription(docFIIDIIEq)[[1]]
funFIIDIIEq = createFunction(formFIIDIIEq)
 
  # now we can invoke the form from R. We only need 2
  # inputs  - FromDate and ToDate
 
#Since the NSE URLs only display a maximum of 100 records, 
#	we shall attempt the range in loop 
 
myStDt = startDate
 
while (myStDt <= endDate){
 
	if (endDate<= myStDt + 50){ 
		myEnDt = endDate
	}else
	{
		myEnDt = myStDt + 50
	}
 
	print(paste("Downloading from",myStDt,"to", myEnDt ))
 
	Nifty = funNifty(fromDate = as.character(myStDt-5, "%d-%m-%Y"), 
		toDate = as.character(myEnDt, "%d-%m-%Y"), indexType="S&P CNX NIFTY")
	FIIDIIEq = funFIIDIIEq (fromDate = as.character(myStDt, "%d-%m-%Y"), 
		toDate = as.character(myEnDt, "%d-%m-%Y"), category="all")
 
  # Having looked at the tables, I think we want the the 4th one.
	tableNifty = readHTMLTable(htmlParse(Nifty, asText = TRUE),
                        which = 4,
				skip.rows = 3, 
				trim=TRUE,
				as.data.frame = TRUE,
                        header = TRUE,
				stringsAsFactors = FALSE)
 
  # Having looked at the tables, I think we want the the 4th one.
	tableFIIDIIEq = readHTMLTable(htmlParse(FIIDIIEq, asText = TRUE),
                        which = 4,
				trim=TRUE,
				as.data.frame = TRUE,
                        header = TRUE,
				stringsAsFactors = FALSE)
 
	#Select only FII stats
 
	#Format Date Column from string to Date type
	tableFIIDIIEq$Date <- as.Date(tableFIIDIIEq$Date, format="%d-%b-%Y")
 
	#Order by Date in Ascending Order
	tableFIIDIIEq<-tableFIIDIIEq[order(tableFIIDIIEq$Date,decreasing = TRUE),]
 
	dfFIIEq <-subset(tableFIIDIIEq, Category=="FII")
	colnames(dfFIIEq)[3] <- "FIIBuyValue"
	colnames(dfFIIEq)[4] <- "FIISellValue"
	colnames(dfFIIEq)[5] <- "FIINetValue"
 
	#Select only DII stats
	dfDIIEq <-subset(tableFIIDIIEq, Category=="DII")
	colnames(dfDIIEq)[3] <- "DIIBuyValue"
	colnames(dfDIIEq)[4] <- "DIISellValue"
	colnames(dfDIIEq)[5] <- "DIINetValue"
 
	#Merge FII and DII Stats
	dfFIIDIIEq<-merge(dfFIIEq,dfDIIEq, by.x="Date", by.y="Date")
 
	#Convert into numeric for addition
	dfFIIDIIEq$DIINetValue <- as.numeric(dfFIIDIIEq$DIINetValue)
	dfFIIDIIEq$FIINetValue <- as.numeric(dfFIIDIIEq$FIINetValue)
 
	# Find Effective Inflow
	dfFIIDIIEq$EffectiveInflow <- dfFIIDIIEq$FIINetValue + dfFIIDIIEq$DIINetValue
 
	#Process Nifty
	dfNifty <- tableNifty 
	colnames(dfNifty)[6] <- "Volume"
	colnames(dfNifty)[7] <- "TurnoverInCr"
 
	#Format Date Column from string to Date type
	dfNifty$Date <- as.Date(dfNifty$Date, format="%d-%b-%Y")
 
	#Format CLose Column from string to Numeric
	dfNifty$Close <- as.numeric(dfNifty$Close)
 
	#Order by Date in Ascending Order
	dfNifty <-dfNifty[order(dfNifty$Date,decreasing = TRUE),]
 
	#Create a function to find out Change in Nifty Closing over previous day
	FUN=function(a) c(diff(a), NA)
 
	dfNifty$Change<- FUN(dfNifty$Close)*-1
 
	#Store the merged dataframe in temp and then join it with earlier results
	temp <- merge(dfNifty,dfFIIDIIEq, by.x="Date", by.y="Date")
 
	if (startDate == myStDt){ 
		dfNiftyFIIDIIEq = temp
	}else
	{
		dfNiftyFIIDIIEq <- rbind(dfNiftyFIIDIIEq,temp)
	}
 
	myStDt = myStDt + 51
}
 
closeAllConnections() 
 
######################################################################
#Get Rid of unwanted columns
 
	dfNiftyFIIDIIEq$Category.x <- NULL
	dfNiftyFIIDIIEq$Category.y <- NULL
#	dfNiftyFIIDIIEq$Open <- NULL
#	dfNiftyFIIDIIEq$High <- NULL
#	dfNiftyFIIDIIEq$Low <- NULL
#	dfNiftyFIIDIIEq$Volume <- NULL
#	dfNiftyFIIDIIEq$TurnoverInCr<- NULL
 
# If file exists, merge the retrieved records
 
	if (blnfileExist)
	{
		print("Appending to existing file")
		#existingStats$Date <- as.Date(existingStats$Date, format = "%d-%m-%Y")
		dfNiftyFIIDIIEq$Date <- as.Date(dfNiftyFIIDIIEq$Date, format = "%d-%m-%Y")
		dfNiftyFIIDIIEq <- rbind(dfNiftyFIIDIIEq,existingStats)
		dfNiftyFIIDIIEq <- dfNiftyFIIDIIEq[order(dfNiftyFIIDIIEq$Date, decreasing=TRUE),]
	}else
	{
		print("Writing to a new file")
		dfNiftyFIIDIIEq <- dfNiftyFIIDIIEq[order(dfNiftyFIIDIIEq$Date, decreasing=TRUE),]
	} 
#Finally write the csv file
	write.csv(dfNiftyFIIDIIEq ,file=file.path(outputDir, filename),row.names = FALSE)
Created by Pretty R at inside-R.org
Next steps, add more indices in this list and try to figure out any co-relation.

Jul 24, 2011

BSE Bhavcopy with Delivery Quantity

One of my TI forum members IV had a requirement for BSE Quotes along with Delivery Quantity. This made me implement "merge" function of R (thanks to the great work done by people behind various packages and guidance available on R Mailing lists).

The task involves downloading files from two separate links and merging it on the basis of Symbol on any given date.


The output is a single file for one trading day. With a slight tweak in the code, single file can be generated for a week, a month, a year , or from beginning to end. If anyone is interested, let me know, and will offer the solution for the same

Without much write-up here is the code for the same.

For those, who want to take a shortcut, here are historical quotes

You can download R Code here


#This code merges BHAVCOPY and Total Deliverable Quantity from BSE
#The output file contains "DATE","SC_CODE","SC_NAME","OPEN","HIGH",
#"LOW","CLOSE","NO_OF_SHRS","DLV_QTY"
 
 
# Start ---------------- User Defined Variables
 
#Define Working Directory, where files would be saved
setwd('D:/BSE Equity')
 
#Define start and end dates, and convert them into date format
startDate = as.Date("2010-01-01", order="ymd")
endDate = as.Date("2011-07-31", order="ymd")
 
#Define Download folder, use \\ instead of windows standard \
filepath="D:\\BSE Equity\\New\\"
 
# End ---------------- User Defined Variables
 
 
junkBHAV <- dir(pattern="EQ")
junkDELV <- dir(pattern="SCBSEALL")
 
 
#work with date, month, year for which data has to be extracted
myDate = startDate
 
#Define temporary files
zippedBhav <- tempfile()
zippedDelv <- tempfile()
 
while (myDate <= endDate){
  filenameDate = paste(as.character(myDate, "%Y%m%d"), sep = "")
  fileBHAV=paste("eq", as.character(myDate, "%d%m%y"), "_csv.zip", sep = "")
  fileDELV=paste("SCBSEALL", as.character(myDate, "%d%m"), ".zip", sep = "")
 
  #Generate URL in following format
 #http://www.bseindia.com/Hisbhav/eq161210_csv.zip
 #http://www.bseindia.com/BSEDATA/gross/2010/SCBSEALL1612.TXT
  URLBhav = paste("http://www.bseindia.com/Hisbhav/", fileBHAV, sep = "")
 URLDelv = paste("http://www.bseindia.com/BSEDATA/gross/", as.character(myDate, "%Y"),"/",fileDELV, sep = "")
 
 
  #retrieve Zipped file
  tryCatch({
  #Download Zipped File
  download.file(URLBhav,zippedBhav, quiet=TRUE, mode="wb")
  download.file(URLDelv,zippedDelv, quiet=TRUE, mode="wb")
 
  bhav <- read.csv(unzip(zippedBhav), header=TRUE, sep=",") 
 
  #Add Date Column
  bhav <- cbind(DATE=myDate,bhav)
 
  #Select only SY_Type="Q", or Equity Quotes
  bhav <- subset(bhav, SC_TYPE=="Q")
 
  delv <- read.csv(unzip(zippedDelv), header=TRUE, sep="|")
  colnames(delv)[2] <- "SC_CODE"
  colnames(delv)[3] <- "DLV_QTY"
  colnames(delv)[4] <- "DLV_VAL"
  colnames(delv)[5] <- "VOLUME"
  colnames(delv)[6] <- "TURNOVER"
  colnames(delv)[7] <- "DLV_PCT"
 
  temp<-merge(bhav,delv, by.x="SC_CODE", by.y="SC_CODE")
  colnames(temp)[2] <- "DATE"
  temp$DATE.y<-NULL
  temp.IV<-subset(temp,select=c("DATE","SC_CODE","SC_NAME","OPEN","HIGH","LOW","CLOSE","NO_OF_SHRS","DLV_QTY"))
 
  write.csv(temp.IV,file=paste(filepath,filenameDate, ".csv",sep=""),row.names = FALSE)
 
  #Print Progress
  print(paste (myDate, "-Done!", endDate-myDate, " days left"))
 }, error=function(err){
  print(paste(myDate, "-No Record", endDate-myDate, " days left"))
 }
 )
  myDate <- myDate+1
}
 
 #Delete temp file
 file.remove(junkBHAV)
 file.remove(junkDELV)
Created by Pretty R at inside-R.org