EDIT: ignore this post, see next post
tcatm has limited the trade api. max 20000 trades can be downloaded per request.
here's a bash script that updates local trades table
#!/bin/bash
db="mtgox"
user="postgres"
symbol="mtgoxUSD"
PSQL="psql -q -t -U ${user} ${db}"
DL="curl -s"
function sql() {
rc=$(echo "$1" | $PSQL)
}
function extend() {
sql "drop table if exists import;"
sql "create table import (id serial, unixtime int, price numeric(32,10), volume numeric(32,8), type smallint);"
len=$(( 4 * 60 * 60 ))
while true; do
sql "select max(unixtime) from trades;"
start=$rc
start_human=$(date -d "@$start")
echo "--- $start_human ----------------------------------------------------------"
end=$(( $start + $len ))
echo "start ($start) + len ($len) = end ($end)"
# download
$DL "http://bitcoincharts.com/t/trades.csv?symbol=$symbol&start=$start&end=$end" > trades.csv
end_file=$(head -n 1 trades.csv | cut -d , -f 1)
start_file=$(tail -n 1 trades.csv | cut -d , -f 1)
echo "start_file ($start_file) - start ($start) = $(( $start_file - $start ))"
echo "end_file ($end_file) - end ($end) = $(( $end_file - $end ))"
if [ $start_file -eq $start ]; then
echo start times match, updating trades table
# put into import table
sql "delete from import;"
sql "\copy import(unixtime,price,volume) from 'trades.csv' delimiters ',' csv;"
sql "delete from trades where unixtime >= $start;"
sql "insert into trades (unixtime, t, price, volume) select unixtime, TIMESTAMP 'epoch' + unixtime * INTERVAL '1 second', price, volume from import order by id desc;"
if [ $start_file -eq $end_file ]; then
echo "end detected, sleeping for 10 minutes,...."
sleep 10m;
fi
else
echo "start_file != start, exiting, check code"
exit 1
fi
done
}
extend
it's quite fresh (use accordingly), currently in process of syncing my table. It uses 4-hour blocks.
dont know if it quits nicely yet when done
hoping someone can use it.
EDIT: added sync-end-detection. will sleep 10 minutes, then continue to sync.
EDIT2:
hmm, it stops prematurely. I think something is weird with the bitcoincharts api:
neither this:
http://bitcoincharts.com/t/trades.csv?symbol=mtgoxUSD&start=1365709116&end=1365710916nor this:
http://bitcoincharts.com/t/trades.csv?symbol=mtgoxUSD&end=1365710916deliver any data (except one trade in the first case) while I think they should.
while this:
http://bitcoincharts.com/t/trades.csv?symbol=mtgoxUSD&start=1365709116delivers the most recent trade data (20000 most recent trades) ignoring "start" (at least that is consistent with the api docs).
the api docs:
http://bitcoincharts.com/about/markets-api/ say I should only use the "end" parameter. That doesn't work, though (
http://bitcoincharts.com/t/trades.csv?symbol=mtgoxUSD&end=1365710916 doesn't deliver)
ideas?
