master.md

library(reshape)
setwd('D:/gdrive/FIM_MA/code')
library(gdata)

# Create urls
urls = c()
for (urlyear in 2012:2016){
 for (urlmonth in 1:12){
  for (urlday in 1:31){
   tryCatch({
     db_cdax(urlyear = urlyear, urlmonth = urlmonth, urlday = urlday, cache_url = T)
    }, error = function(e) {
     db_cdax(urlyear = urlyear, urlmonth = urlmonth, urlday = urlday, cache_url = F)
    }, finally = {
     next
    })
  }
 }
}

db_cdax = function(urlyear,urlmonth,urlday,cache_url = TRUE){
 # URL
 if (cache_url){
  url_edit = 'Cache'
 } else {
  url_edit = ''
 }

 base_url = paste0('http://dax-indices.com/MediaLibrary', url_edit, '/Document/WeightingFiles/')
 cdax_url = '/CDAX_ICR.'
 file_type_url = '.xls'

 url = paste0(base_url,sprintf("%02d", urlmonth),cdax_url,urlyear,sprintf("%02d", urlmonth),sprintf("%02d", urlday),file_type_url)
 print(url)

 dat <- read.xls(url, sheet = 'Data', pattern = 'Index ')
 # data cleaning
 dat[dat == 'n/a'] = NA
 dat$Date = as.Date(paste0(urlyear,'-',urlmonth,'-',urlday))
 dat2 <- data.frame(lapply(dat, as.character), stringsAsFactors=FALSE)
 table_melted <- na.omit(melt(dat2[,-c(1:2)],
                              id.vars = c('Date','ISIN','Index.ISIN','Trading.Symbol','Instrument','Transparency.Level','Instrument.Exchange')))
table_melted$value = gsub("\\,", "", table_melted$value)
 table_melted$value = sapply(table_melted$value, function(x) if (grepl('%',x)) {as.numeric(sub("%", "", x))*10^-2} else {as.numeric(x)})

 url_file_name = strsplit(url, '/')
 write.csv(table_melted,paste0('../data/cdax/',url_file_name[[1]][length(url_file_name[[1]])],'.csv'), row.names=FALSE)

}