Due to popular demand, here's a little intro on how to get mtGox trade data (all trades) into a sql database and run some queries on the data:
Depending on your system you might need a lot of stuff I wont list here (like postgres database server, wget/curl, ...). You'll just run into it when you need it.
Firstly we need to wget or curl the csv from bitcoincharts.com ("#>" means you should enter this in a shell):
#> curl "http://api.bitcoincharts.com/v1/csv/mtgoxUSD.csv" > trades.csv
This will write a file "trades.csv" containing all mtgox trades.
now create a database ("postgres=#" means enter this in psql shell):
#> psql -U postgres
postgres=# create database mtgox;
CREATE DATABASE
postgres=# \connect mtgox;
You are now connected to database "mtgox" as user "postgres".
mtgox=# \q
#>
now you have created a database called "mtgox" and you can open a psql shell to it directly next time:
we'll create a table for the trades and import the data:
#> psql -U postgres mtgox
mtgox=# create table trades (id serial, unixtime int, t timestamp, price numeric(16,8), volume numeric(16,8));
NOTICE: CREATE TABLE will create implicit sequence "trades_id_seq" for serial column "trades.id"
CREATE TABLE
mtgox=# \copy trades(unixtime,price,volume) from 'trades.csv' delimiters ',' csv;
mtgox=# update trades set t = TIMESTAMP 'epoch' + unixtime * INTERVAL '1 second';
UPDATE 3563178
mtgox=#
done. now you can run queries:
mtgox=# select count(*) as cnt, sum(volume) as vol_btc, sum(volume*price) as vol_usd from trades;
cnt | vol_btc | vol_usd
---------+-------------------+------------------------------
3563178 | 39113649.25693204 | 293136750.814254195366000000 cnt | vol_btc | vol_usd
have fun and please, paste your queries here if you have cool ones.
