MtGox trades history download

# MtGox trades history download ----------------------------------------
# v1.3

#official topic of the script: https://bitcointalk.org/index.php?topic=286755
#caused by mtgox issue related to server overload
#the result set will contain join dataset, bitcoincharts data has fewer attribiutes
#it should handle other currencies

#install.packages - RUN ONLY FIRST TIME
#install.packages(c('data.table','RCurl','RJSONIO','RSQLite'))

#loading packages
require(data.table) #http://cran.r-project.org/web/packages/data.table/index.html
require(RCurl) #http://cran.r-project.org/web/packages/RCurl/index.html
require(RJSONIO) #http://cran.r-project.org/web/packages/RJSONIO/index.html
require(RSQLite) #http://cran.r-project.org/web/packages/RSQLite/index.html

#params
market <- 'mtgoxUSD'
output_chunks <- 100000 #to do not write millions of rows at once, if you have low memory you can adjust this to smaller chunks
output_format <- c('csv','db') #possible values: 'csv', 'db', c('csv','db') #market is also sqlite table name
csv.sep <- ';'
csv.dec <- ','
ssl.verify <- FALSE #before changing to TRUE put certificate file 'ca-bundle.crt' in the working directory, by default directory of the script: http://curl.haxx.se/docs/caextract.html

cat('\n',as.character(Sys.time()),': processing started',sep='')
init_time <- proc.time()[[3]]
#download full history from bitcoincharts
#http://bitcoincharts.com/about/markets-api/
cat('\n',as.character(Sys.time()),': downloading trades csv from bitcoincharts',sep='')
bitcoincharts_dt <- data.table(read.csv(paste0('http://api.bitcoincharts.com/v1/csv/',market,'.csv'), header = FALSE, col.names = c('date','price','amount')))
cat('\n',as.character(Sys.time()),': bitcoincharts trades downloaded',sep='')

#download most recent trades until reached time from bitcoincharts
#https://bitbucket.org/nitrous/mtgox-api/#markdown-header-moneytradesfetch
cat('\n',as.character(Sys.time()),': downloading recent trades from MtGox',sep='')
since <- paste0(bitcoincharts_dt[NROW(bitcoincharts_dt),date]-1,'999999')
mtgox_dt <- data.table()
repeat{
  mtgox_dt.batch <- fromJSON(rawToChar(getURLContent(curl = getCurlHandle(),
                                                     url = paste0('https://data.mtgox.com/api/2/BTC',substring(market,6,9),'/money/trades/fetch?since=',since),
                                                     useragent = R.version.string,
                                                     cainfo = 'ca-bundle.crt',
                                                     ssl.verifypeer = ssl.verify,
                                                     ssl.verifyhost = ssl.verify,
                                                     verbose = FALSE,
                                                     binary = TRUE)))
  if(mtgox_dt.batch[['result']] != 'success'){
    cat('\n',as.character(Sys.time()),': mtgox api returns error, retrying',sep='')
    next
  }
  mtgox_dt.batch <- rbindlist(mtgox_dt.batch$data)
  mtgox_dt <- rbindlist(list(mtgox_dt, mtgox_dt.batch))
  if(NROW(mtgox_dt.batch) < 1000) break
  since <- mtgox_dt.batch[NROW(mtgox_dt.batch),tid]
}
cat('\n',as.character(Sys.time()),': MtGox recent trades downloaded',sep='')

#transform datasets
bitcoincharts_dt <- bitcoincharts_dt[,c('date','price','amount','price_int','amount_int','tid','price_currency','item','trade_type','primary','properties') := list(date,price,amount,as.numeric(NA),as.numeric(NA),as.character(NA),as.character(NA),as.character(NA),as.character(NA),as.character(NA),as.character(NA))]
mtgox_dt <- mtgox_dt[,c('date','price','amount','price_int','amount_int','tid','price_currency','item','trade_type','primary','properties') := list(as.integer(date),as.numeric(price),as.numeric(amount),as.numeric(price_int),as.numeric(amount_int),tid,price_currency,item,trade_type,primary,properties)]
#merge datasets
merged_dt <- rbindlist(list(bitcoincharts_dt[date < last(date),],mtgox_dt))
cat('\n',as.character(Sys.time()),': merged ',NROW(bitcoincharts_dt),' bitcoincharts rows and ',NROW(mtgox_dt),' MtGox rows, current total is ',NROW(merged_dt),' rows',sep='')

#output to csv
if('csv' %in% output_format){
  cat('\n',as.character(Sys.time()),': exporting to csv',sep='')
  chunks <- ceiling(NROW(merged_dt)/output_chunks)
  for(i in 1:chunks){
    if(i == 1) write.table(merged_dt[(1+(i-1)*output_chunks):min((i*output_chunks),NROW(merged_dt))], file = paste0('trades.',market,'.csv'), row.names = FALSE, sep = csv.sep, dec = csv.dec, col.names = TRUE)
    else if(i != chunks) write.table(merged_dt[(1+(i-1)*output_chunks):(i*output_chunks)], file = paste0('trades.',market,'.csv'), append = TRUE, row.names = FALSE, sep = csv.sep, dec = csv.dec, col.names = FALSE)
    else if(i == chunks) write.table(merged_dt[(1+(i-1)*output_chunks):NROW(merged_dt)], file = paste0('trades.',market,'.csv'), append = TRUE, row.names = FALSE, sep = csv.sep, dec = csv.dec, col.names = FALSE)
  }
  cat('\n',as.character(Sys.time()),': exported csv: ',paste0('trades.',market,'.csv'),sep='')
}

#output to sqlite db
if('db' %in% output_format){
  db_conn <- dbConnect(SQLite(), dbname = paste0('trades.db'))
  #if table with name equal to market param value already exists (was previously processed) in trades.db drop it manually before running the script
  cat('\n',as.character(Sys.time()),': exporting to db',sep='')
  chunks <- ceiling(NROW(merged_dt)/output_chunks)
  for(i in 1:chunks){
    if(i == 1) dbWriteTable(conn = db_conn, name = market, value = merged_dt[(1+(i-1)*output_chunks):min((i*output_chunks),NROW(merged_dt))], row.names = FALSE)
    else if(i != chunks) dbWriteTable(conn = db_conn, name = market, value = merged_dt[(1+(i-1)*output_chunks):(i*output_chunks)], append = TRUE, row.names = FALSE)
    else if(i == chunks) dbWriteTable(conn = db_conn, name = market, value = merged_dt[(1+(i-1)*output_chunks):NROW(merged_dt)], append = TRUE, row.names = FALSE)
  }
  dbDisconnect(db_conn)
  cat('\n',as.character(Sys.time()),': exported sqlite: ',paste0('trades.db'),sep='')
}
cat('\n',as.character(Sys.time()),': processing finished, total time ',proc.time()[[3]] - init_time,'s., bitcoin donation: 1BNBQw2k8u3L9DLnfLFAeHjs5vcEbGnarT',sep='')