### Baltic Dry Index
### via quandl
### v0.1 share 

###    ----
# 
# 
#_____________________________________________________________________________

### Meta data    ----
name1 <- "bender is great"
stamp.date <- Sys.Date()
description <- "Baltic Dry Index. Source: DryShips Inc." 
# 
#______________________________________________________________________________

### set variables   ----
quandl.auth <- 'GetThisFromYourQuandlAccount' #https://www.quandl.com
quandl.symbol <- "OFDP/INDEX_BDI_1"
path.to.sierra.data <- "F:/Programme/aaaa Trading/SierraChart5/Data/r.BDI.dly"

#_____________________________________________________________________________

### Libraries     -------------------------------------------------------------
library("Quandl")
library("xts")
library("sqldf")
#______________________________________________________________________________

### Auth for Quandl   ----
Quandl.auth(quandl.auth)
# 
#_____________________________________________________________________________

### DL from quandl  ------------------------------------------------------------
d = Quandl(quandl.symbol, 
               collapse=NULL, # NULL for Daily
               start_date="1900-01-01", 
               type="xts")

#plot(qdata[,1])
#______________________________________________________________________________

### opti data for sierra   ----

# convert xts 2 df
d.df <- as.data.frame(d)
rn <- row.names(d.df)

# opti Date 
rn <- sapply(rn, 
               function(x) 
                       {as.character(gsub("-","/", as.character(x)))})

# write back opti row names
row.names(d.df) <- rn

# make pseudo OHLC
d.df[,2] <- d.df[,1]
d.df[,3] <- d.df[,1]
d.df[,4] <- d.df[,1]
d.df[,5] <- d.df[,1]
d.df[,1] <- rn

# rename col
names(d.df) <- c("Date","Open","High","Low","Close")

#_____________________________________________________________________________

### merge with data in (txt-) DB ----
# this preserves the data downloaded earlier - 
# in case there is more data in local DB, than in the web-source

# get downloaded quandl data
d_DL <- d.df
rm(d.df)

# get current data in DB 
if (file.exists(path.to.sierra.data)){
        
        d_DB <- read.table(path.to.sierra.data,
                           header=T,
                           sep=",",
                           colClasses=c("character","numeric","numeric","numeric",
                                        "numeric"))
        d_DB[,1] <- sapply(d_DB[,1], 
                           function(x) 
                           {as.character(gsub("-","/", as.character(x)))})
} else {
        
        d_DB <- d_DL  
}

# Only the rows which are NOT in both data frames:
d_update <- sqldf('SELECT * FROM d_DL EXCEPT SELECT * FROM d_DB')

# merge into updated DB
d_DBnew <- merge(d_update, d_DB, all.x=T, all=T) 
rm(d_DB)
rm(d_DL)
rm(d_update)

#_____________________________________________________________________________

### write to file   ----
write.table(d_DBnew,path.to.sierra.data,sep=",",row.names=FALSE, quote=F)
#_____________________________________________________________________________

### clean   ----
rm(list=ls())
#_____________________________________________________________________________