Global market

library(zoo)
library(ggplot2)
library(tseries)
library(gridExtra)
library(Quandl)
Quandl.api_key("qZ3LsZXkitPz5KE9XZA5")
library("sqldf")
library("plyr")
library(quantmod)
library(plotrix)
library(grid)
library(rsdmx)
library(lubridate)
library(xtable)
library(RODBC)
library(devtools)
library(plotly)



rm(list=ls())

startdate<-as.Date("2009-01-04", "%Y-%m-%d")  #for Quandl data
startdate2<-as.Date("2009-01-04", "%Y-%m-%d") #for Quandl normalizing data
startdate3<-"20090101"  #for DB

#Stockdata

# KOSPI

KOSPI<-Quandl("YAHOO/INDEX_KS11", trim_start=startdate,order="desc", type="zoo")
KOSPIN<-Quandl("YAHOO/INDEX_KS11", trim_start=startdate2,order="desc", type="zoo", transform="normalize")
KOSPI<-round(KOSPI[,6],digits=2)
KOSPIN<-KOSPIN[,6]

# DOW

DJI <-Quandl("YAHOO/INDEX_DJI", trim_start=startdate,order="desc", type="zoo")
DJIN <-Quandl("YAHOO/INDEX_DJI", trim_start=startdate2,order="desc", type="zoo", transform="normalize")
DJI<-round(DJI[,6],digits=2)
DJIN<-DJIN[,6]

# NIKKEI

NIKKEI <-Quandl("NIKKEI/INDEX", trim_start=startdate,order="desc", type="zoo")
NIKKEIN <-Quandl("NIKKEI/INDEX", trim_start=startdate2,order="desc", type="zoo", transform="normalize")
NIKKEI<-round(NIKKEI[,4], digits=2)
NIKKEIN<-NIKKEIN[,4]

# DAX

DAX <-Quandl("YAHOO/INDEX_GDAXI", trim_start=startdate,order="desc", type="zoo")
DAXN <-Quandl("YAHOO/INDEX_GDAXI", trim_start=startdate2,order="desc", type="zoo", transform="normalize")
DAX<-round(DAX[,6], digits=2)
DAXN<-DAXN[,6]
# EURO

EURO50 <-Quandl("YAHOO/INDEX_STOXX50E", trim_start=startdate,order="desc", type="zoo")
EURO50N <-Quandl("YAHOO/INDEX_STOXX50E", trim_start=startdate2,order="desc", type="zoo", transform="normalize")
EURO50<-round(EURO50[,6], digits=2)
EURO50N<-EURO50N[,6]

#Shanghai

Shanghai <-Quandl("YAHOO/INDEX_SSEC", trim_start=startdate,order="desc", type="zoo")
ShanghaiN <-Quandl("YAHOO/INDEX_SSEC", trim_start=startdate2,order="desc", type="zoo",transform="normalize")
Shanghai<-round(Shanghai[,6], digits=2)
ShanghaiN<-ShanghaiN[,6]

#Hangseng

Hangseng <-Quandl("YAHOO/INDEX_HSI", trim_start=startdate,order="desc", type="zoo")
HangsengN <-Quandl("YAHOO/INDEX_HSI", trim_start=startdate2,order="desc", type="zoo",transform="normalize")
Hangseng<-round(Hangseng[,6], digits=2)
HangsengN<-HangsengN[,6]

#VIX

VIX <-Quandl("YAHOO/INDEX_VIX", trim_start=startdate,order="desc", type="zoo")
VIX<-VIX[,6]

Stock <- merge
Stock<- merge.zoo(KOSPI, DJI, DAX, NIKKEI, EURO50, Shanghai, Hangseng, VIX)
num<-ncol(Stock)

StockN <- merge
StockN<- merge.zoo(KOSPIN, DJIN, DAXN, NIKKEIN, EURO50N, ShanghaiN, HangsengN)

#Commodity

#

Copperp <- Quandl("LME/PR_CU", trim_start=startdate,order="desc", type="zoo")
Copper<-Copperp[,1]

#

Zincp <- Quandl("LME/PR_ZI", trim_start=startdate,order="desc", type="zoo")
Zinc<-Zincp[,1]

#

Goldp <- Quandl("LBMA/GOLD", trim_start=startdate,order="desc", type="zoo")
Gold<-Goldp[,1]

#

WTI<-Quandl("FRED/DCOILWTICO", trim_start=startdate,order="desc", type="zoo")

#

DXY<-Quandl("FRED/DTWEXM", trim_start=startdate,order="desc", type="zoo")

DXYIMTP<-Quandl("FRED/DTWEXO", trim_start=startdate,order="desc", type="zoo")
  
Commodity<-merge.zoo(Copper, Zinc, Gold, WTI, DXY, DXYIMTP)

#Currency data

# Connect to DB

channel <- odbcConnect("KBXUPDB", uid="STARM2SEL", pwd="starm2sel2*")

#Input data

input_data<-c("USD", "JPY", "EUR", "GBP", "CNY", "AUD", "CAD", "CHF", "INR", "BRL")

for(i in 1:length(input_data)){ 
  
  sql <- paste(" Select BASE_DT, TRADE_RT_LAST from KBFMSDB.DBO.BFZAR10 where BASE_DT >= '", startdate3, sep="")
  sql <- paste(sql,"'", sep="")
  sql <- paste(sql, "and CCY_cd in ('",input_data[i],sep="")
  sql <- paste(sql, "') order by BASE_DT", sep="")
  myresult <- (sqlQuery(channel, sql))
  
  if(i==1){
    crn <-myresult
    colnames(crn)[2]<-input_data[i]}
  
  else {
    colnames(myresult)[2]<-input_data[i]
    crn<-merge(crn, myresult, by.x="BASE_DT", by.y="BASE_DT", all.x=T)
  }
  
}

odbcClose(channel)

currency<-zoo(crn[,-1])
index(currency)<-as.Date(as.character(crn[,1]),"%Y%m%d")
currency2<-currency
colnames(currency)<-c("USDKRW", "USDJPY", "EURUSD", "GBPUSD", "USDCNY", "AUDUSD", "USDCAD", "USDCHF", "USDINR", "USDBRL")
colnames(currency2)<-c("USD", "JPY", "EUR", "GBP", "CNY", "AUD", "CAD", "CHF", "INR", "BRL")

currency$USDJPY <-round(crn$USD/crn$JPY, digits=2)
currency$EURUSD <-round(crn$EUR/crn$USD, digits=2)
currency$GBPUSD <-round(crn$GBP/crn$USD, digits=2)
currency$USDCNY <-round(crn$USD/crn$CNY, digits=2)
currency$AUDUSD <-round(crn$AUD/crn$USD, digits=2)
currency$USDCAD <-round(crn$USD/crn$CAD, digits=2)
currency$USDCHF <-round(crn$USD/crn$CHF, digits=2)
currency$USDINR <-round(crn$USD/crn$INR, digits=2)
currency$USDBRL <-round(crn$USD/crn$BRL, digits=2)



#Filling missing data 

for(i in 1:ncol(Stock)){
  
  ind<-which(is.na(Stock[,i]))
  if(length(ind)>0){
    if(ind[1]==1) ind<-ind[-1]
    for(j in 1:length(ind)){
      Stock[ind[j], i] <- Stock[ind[j]-1, i]
      
    }
  }
}

for(i in 1:ncol(StockN)){
  
  ind<-which(is.na(StockN[,i]))
  if(length(ind)>0){
    if(ind[1]==1) ind<-ind[-1]
    for(j in 1:length(ind)){
      StockN[ind[j], i] <- StockN[ind[j]-1, i]
          }
  }
}

for(i in 1:ncol(Commodity)){
  
  ind<-which(is.na(Commodity[,i]))
  if(length(ind)>0){
    if(ind[1]==1) ind<-ind[-1]
    for(j in 1:length(ind)){
      Commodity[ind[j], i] <- Commodity[ind[j]-1, i]
      
    }
  }
}

#Making stock return matrix

mcolnames<-colnames(Stock)
mrownames <-c("current", "1d", "1w", "1m", "3m","1y")
return<-matrix(NA, length(mrownames), length(mcolnames))
rownames(return)<-mrownames
colnames(return)<-mcolnames

  return[1,]<-round(Stock[nrow(Stock)], digits=0)
  return[2,]<-round(tail(diff(log(Stock), lag=1),1)*100, digits=2)
  return[3,]<-round(tail(diff(log(Stock), lag=7),1)*100, digits=2)
  return[4,]<-round(tail(diff(log(Stock), lag=30),1)*100, digits=2)
  return[5,]<-round(tail(diff(log(Stock), lag=90),1)*100, digits=2)
  return[6,]<-round(tail(diff(log(Stock), lag=365),1)*100, digits=2)

#Making commodity return matrix

mcolnames<-colnames(Commodity)
mrownames <-c("current", "1d", "1w", "1m", "3m","1y")
creturn<-matrix(NA, length(mrownames), length(mcolnames))
rownames(creturn)<-mrownames
colnames(creturn)<-mcolnames

  creturn[1,]<-round(Commodity[nrow(Commodity)], digits=0)
  creturn[2,]<-round(tail(diff(log(Commodity), lag=1),1)*100, digits=2)
  creturn[3,]<-round(tail(diff(log(Commodity), lag=7),1)*100, digits=2)
  creturn[4,]<-round(tail(diff(log(Commodity), lag=30),1)*100, digits=2)
  creturn[5,]<-round(tail(diff(log(Commodity), lag=90),1)*100, digits=2)
  creturn[6,]<-round(tail(diff(log(Commodity), lag=365),1)*100, digits=2)


#Making FX return matrix

mcolnames<-colnames(currency)
mrownames <-c("current", "1d", "1w", "1m", "3m","1y")
fxreturn<-matrix(NA, length(mrownames), length(mcolnames))
rownames(fxreturn)<-mrownames
colnames(fxreturn)<-mcolnames

  fxreturn[1,]<-round(currency[nrow(currency)], digits=2)
  fxreturn[2,]<-round(tail(diff(log(currency), lag=1),1)*100, digits=2)
  fxreturn[3,]<-round(tail(diff(log(currency), lag=7),1)*100, digits=2)
  fxreturn[4,]<-round(tail(diff(log(currency), lag=30),1)*100, digits=2)
  fxreturn[5,]<-round(tail(diff(log(currency), lag=90),1)*100, digits=2)
  fxreturn[6,]<-round(tail(diff(log(currency), lag=365),1)*100, digits=2)

#Making FX return matrix2
  
  mcolnames<-colnames(currency2)
mrownames <-c("current", "1d", "1w", "1m", "3m","1y")
fxreturn2<-matrix(NA, length(mrownames), length(mcolnames))
rownames(fxreturn2)<-mrownames
colnames(fxreturn2)<-mcolnames

  fxreturn2[1,]<-round(currency2[nrow(currency2)], digits=2)
  fxreturn2[2,]<-round(tail(diff(log(currency2), lag=1),1)*100, digits=2)
  fxreturn2[3,]<-round(tail(diff(log(currency2), lag=7),1)*100, digits=2)
  fxreturn2[4,]<-round(tail(diff(log(currency2), lag=30),1)*100, digits=2)
  fxreturn2[5,]<-round(tail(diff(log(currency2), lag=90),1)*100, digits=2)
  fxreturn2[6,]<-round(tail(diff(log(currency2), lag=365),1)*100, digits=2)

  
  
#Making stock plots

num<-ncol(Stock)
for(i in 1:(num/2)){
op <- par(mfrow=c(1,2))
chartSeries(Stock[,(2*i)-1], layout=NULL, TA='addBBands()', name=colnames(Stock)[2*i-1], theme=chartTheme("white"))

chartSeries(Stock[,(2*i)], layout=NULL, TA='addBBands()', name=colnames(Stock)[2*i], theme=chartTheme("white"))

}

StockNMelt<-fortify(StockN, melt=TRUE)

ggplot(data=StockNMelt, aes(x=Index, y=Value, colour=Series))+
 geom_line()+
  xlab("")+
  ylab("")

plot.new()
grid.table(return)


#Making Commomdity plots

num<-ncol(Commodity)
for(i in 1:(num/2)){
op <- par(mfrow=c(1,2))
chartSeries(Commodity[,(2*i)-1], layout=NULL, TA='addBBands()', name=colnames(Commodity)[2*i-1], theme=chartTheme("white"))

chartSeries(Commodity[,(2*i)], layout=NULL, TA='addBBands()', name=colnames(Commodity)[2*i], theme=chartTheme("white"))

}

plot.new()
grid.table(creturn)

#Making fx plots

i<-1
num<-ncol(currency)
for(i in 1:(num/2)){
op <- par(mfrow=c(1,2))
chartSeries(currency[,(2*i)-1], layout=NULL, TA='addBBands()', name=colnames(currency)[2*i-1], theme=chartTheme("white"))

chartSeries(currency[,(2*i)], layout=NULL, TA='addBBands()', name=colnames(currency)[2*i], theme=chartTheme("white"))

}

#for(i in 1:(num/2)){

#a<-ggplot(currency, aes(x=Index, y=currency[,(2*i)-1]))+ 
  #geom_line(aes(y=currency[,(2*i)-1]))+
  #geom_text(colour="blue", aes(x=Sys.Date(), y=fxreturn[1,(2*i)-1], label=fxreturn[1,(2*i)-1]))+
  #theme_bw()+
  #ggtitle(colnames(fxreturn)[(2*i)-1])+
  #xlab("")+
  #ylab("")
  
#b<-ggplot(currency, aes(x=Index, y=currency[,2*i]))+ 
  #geom_line(aes(y=currency[,2*i]))+
  #geom_text(colour="blue", aes(x=Sys.Date(), y=fxreturn[1,2*i], label=fxreturn[1,2*i]))+
  #theme_bw()+
  #ggtitle(colnames(fxreturn)[2*i])+
  #xlab("")+
  #ylab("")

#grid.arrange(a,b, ncol=2)
#}
plot.new()
grid.table(fxreturn)


#Makign fx2 plots

i<-1
num<-ncol(currency2)

for(i in 1:(num/2)){
op <- par(mfrow=c(1,2))
chartSeries(currency2[,(2*i)-1], layout=NULL, TA='addBBands()', name=colnames(currency2)[2*i-1], theme=chartTheme("white"))

chartSeries(currency2[,(2*i)], layout=NULL, TA='addBBands()', name=colnames(currency2)[2*i],theme=chartTheme("white"))

}

plot.new()
grid.table(fxreturn2)
plot.new()

par(mfrow=c(1,1))

plot(Stock)

plot(Commodity)

plot(currency)