I just completed a few Data Science courses on Coursera https://www.coursera.org/specialization/jhudatascience/1. So I wanted to apply them on some financial metrics, before they became rusty!

The Indian banking sector is impacted by non performing loans. It’s predominantly visible in public banks which have a very high proportion of NPA’s on their books.The resulting visualization demonstrates how NPAs have varied with the return on asset and net profits of the banks. The data on the quarterly financial performance of 38 Indian banks(24 in the public sector & 14 in the private) was scrapped into two excel sheets.

The data was then read using the xlsx package

require(xlsx)
for(i in 1:14){
  sheets[[i]]<-read.xlsx("Private_Banks_Quarterly.xlsx",i)
  
  # Bank name, whether the bank is private or public & the quarter for which
  # the information was available
  bankName<-rep(names(sheets[[i]])[1],18)
  privatePublic<-rep("Private",18)
  quarters<-t(rev(sheets[[i]][6,(2:19)]))
  
  # Copy the required metrics
  bank_data<-t(rev(sheets[[i]][c(26,43:45,47),(2:19)]))
  
  #Append all data
  output<-rbind(output,cbind(bankName,privatePublic,quarters,bank_data))
  
}

A similar for loop was run to read data for public sector banks

>>>>Note to self
>>>>This approach is too clunky and I want to optimise the code

Then the collated data were written into an external file. This needed to be done so that I could avoid reading the 38 sheets all over again. Writing the data into an output file, also let me to do some string manipulations in excel.

colnames(output)<-c("Bank.Name","Private.Public","Quarter","Net.Profit","Gross.NPA","Net.NPA","RoA","No.Of.Shares")
write.xlsx(output,"datafile.xlsx",row.names=FALSE)

Reading the data back into R, I used the magic of google motion charts…….

Using a single command - gvisMotionChart() I can generate some awesome graphics!


#Motionchart the data
M=gvisMotionChart(data=bank_data,idvar="Bank.Name",timevar="Quarter.End",
                  xvar="Net.NPA",yvar="RoA",colorvar="Private.Public",
                  sizevar="Net.Profit",
                  options=list(vAxes="[{viewWindowMode:'explicit',
      viewWindow:{min:-0.01, max:0.025}}]",title="NPA Visualization of Indian Banks"),
                  chartid="NPAViz",)
plot(M)

I simply love this package!

The chart is displayed in a separate browser window. If I want to store it, as an html file, we can simply use the cat() command -

cat(unlist(M$html),file="NPAViz.html")

I was unable to include the visualization in RPubs, so the following link will take you to the Blog page on my website http://www.theshoeshineboy.in/2015/02/npa-visualization-for-indian-banks.html

The code has been shared on Github https://github.com/skyprince999/NPA_Visualization

What next???
* Optimize the code for cleaning data;
* Insert market capitalization, as another metric for size;
* Understand how to use the options parameter, to customize the chart.

Citation

This legendary TED talk by Hans Rosling http://www.ted.com/talks/hans_rosling_shows_the_best_stats_you_ve_ever_seen?language=en

Thanks to Josh http://rpubs.com/josh whose RPubs FAQ was very helpful in getting this file published!

Markus Gesmann and Diego de Castillo. Using the Google Visualisation API with R. The R Journal, 3(2):40-44, December 2011.

R Core Team (2014). R: A language and environment for statistical computing. R Foundation for Statistical Computing, Vienna, Austria. URL http://www.R-project.org/.

This is an R Markdown document. Markdown is a simple formatting syntax for authoring HTML, PDF, and MS Word documents. For more details on using R Markdown see http://rmarkdown.rstudio.com.

and others…