View difference between Paste ID: yQLpEiy8 and 81iQuBNE

 
 # MtGox trades history download ----------------------------------------
 
 #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 <- 1000000
+output_chunks <- 1000000 #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],'000000')
+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='')
 
 #join 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.integer(NA),as.integer(NA),as.numeric(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.integer(price_int),as.integer(amount_int),as.numeric(tid),price_currency,item,trade_type,primary,properties)]
 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 & NROW(merged_dt) > (i*output_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)
   }
   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'))
   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 & NROW(merged_dt) > (i*output_chunks)) dbWriteTable(conn = db_conn, name = market, value = merged_dt[(1+(i-1)*output_chunks):(i*output_chunks)], 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='')