Bitcoin Forum

I am working on backtesting for my trade bot Gekko (https://bitcointalk.org/index.php?topic=209149.0) and I created a simple script to calcuate candles on MtGox data. This script will output a csv file with all candles. Nitrous has created a great tool which can download all trade data and calculate candles based on this trade data. Check it out in the Mt. Gox data downloader thread (https://bitcointalk.org/index.php?topic=221055).

Downloadable candles

I've ran the script myself for a couple of candle durations on a couple of markets, here are the download links:

Hourly

• Hourly BTC/USD candles from Jun 26 2011 19:00:00 to May 23 2013 16:00:00 (https://www.dropbox.com/s/kcr2e1jq2ykh1il/mtgox-hourly-candles-USD.csv). The CSV file has 16699 candles (note that 27 candles/hours are missing, during these hours I could not find any trades).
  
• Hourly BTC/EUR candles from Aug 27 2011 10:00:00 to May 23 2013 16:00:00 (https://www.dropbox.com/s/m8rb4yhpon6ve07/mtgox-hourly-candles-EUR.csv). The CSV file has 12613 candles (note that a lot of candles/hours are missing, during these hours I could not find any trades).
  
• Hourly BTC/GBP candles from Sep 06 2011 08:00:00 to May 23 2013 16:00:00 (https://www.dropbox.com/s/52f054utnws3v5d/mtgox-hourly-candles-GBP.csv). The CSV file has 11423 candles (note that 3586 of candles/hours are missing, during these hours I could not find any trades).
  
• Hourly BTC/AUD candles from Sep 02 2011 12:00:00 to May 23 2013 16:00:00 (https://www.dropbox.com/s/9w0ddrxjthbf7o4/mtgox-hourly-candles-AUD.csv). The CSV file has 5830 candles (note that 9271 of candles/hours are missing, during these hours I could not find any trades).
  
• Hourly BTC/JPY candles from Aug 30 2011 11:00:00 to May 23 2013 15:00:00 (https://www.dropbox.com/s/etsaqi2bq5uhere/mtgox-hourly-candles-JPY.csv). The CSV file has 3101 candles (note that 15173 of candles/hours are missing, during these hours I could not find any trades).
  

4 Hourly

• 4 Hourly BTC/USD candles from Jun 26 2011 17:00:00 to May 23 2013 16:00:00 (https://www.dropbox.com/s/br8dxrasehskzpk/mtgox-4hourly-candles-USD.csv). The CSV file has 4182 candles (note that 1 candle/hour is missing, during these 4 hours I could not find any trades).
  
• 4 Hourly BTC/EUR candles from Aug 27 2011 09:00:00 to May 23 2013 13:00:00 (https://www.dropbox.com/s/g02dea7d5upmxjq/mtgox-4hourly-candles-EUR.csv). The CSV file has 3683 candles  (note that 129 of candles/hours are missing, during these hours I could not find any trades).
  

Daily

• Daily BTC/USD candles from Jun 26 2011 to May 23 2013 (https://www.dropbox.com/s/oayp3t5t18cag12/mtgox-daily-candles-USD.csv). The CSV file has 697 candles.
  
• Daily BTC/EUR candles from Aug 27 2011 to May 23 2013 (https://www.dropbox.com/s/i9ovqxtta6ownuw/mtgox-daily-candles-EUR.csv). The CSV file has 630 candles (note that 6 candles/days are missing).
  
• Daily BTC/GBP candles from Sep 06 2011 to May 23 2013 (https://www.dropbox.com/s/f2y3u2teoddqafd/mtgox-daily-candles-GBP.csv). The CSV file has 622 candles (note that 2 candles/days are missing).
  

Other timeframes (provided by Nitrous)

• 900 min USD (https://dl.dropboxusercontent.com/u/1760961/Candles/USD-900-Unix-novolume.csv)
• 1800 min USD (https://dl.dropboxusercontent.com/u/1760961/Candles/USD-1800-Unix-novolume.csv)

Calculate your own candles

EDIT2:

If you have a copy of Microsoft excel you can use the data from Bitcoincharts to create your own candles for a number of exchanges and makets. Read the whole method here (https://bitcointalk.org/index.php?topic=277704.new#new). (big thanks to TradesLikeAPotato (https://bitcointalk.org/index.php?action=profile;u=130290))

EDIT:

Nitrous has created a great tool which can download all trade data and calculate candles based on this trade data. Check it out in the Mt. Gox data downloader thread (https://bitcointalk.org/index.php?topic=221055). If you want to use my older script here is the howto:

You can also use the script to calculate your own candles (for example: weekly, daily, 4hourly, 15min, 5 min, 1min, 514 seconds, etc. candles):

The simple script requires a local copy of a database with MtGox trades. To download this database you should use Nitrous' excellent trade data downloader (https://bitcointalk.org/index.php?topic=221055.0).

Once you have this database:

• Install nodejs (http://nodejs.com)
• Install npm (http://npmjs.org) (you can skip this on Windows and OS X if you used the installers)
• Download the script (https://gist.github.com/askmike/5830803) and put it somewhere close to database dump (from the trade data downloader).
• Go to the directory in command line or terminal.
• Open candleCalculator.js in a texteditor and edit the candle vars to your needs (adjust the startTime, endTime and candleDuration)* also check to see if `mtgoxDump` points to the correct file (it is as long as it's called dump.sql and is in the same directory)
• type in: npm install moment underscore sqlite-wrapper
• type in: node candleCalculator.js
• watch it go! Once every 10 candles it will write them to the csv file (candles-[script start timestamp].csv).

Notes:

• It is advised to index the date column (Money_Trade__) of the database before you start calculating candles. This will extremely speed up the time it takes to calculate the candles: without it takes 40+ hours on my VPS (with SSD) and with it it takes less than 5 minutes to calculate all  hourly USD candles.
  
• As of now the script will report the following OHCL for a candle that did not have any trades: open: NaN, high: -Infinity, low: Infinity, close: NaN. If you don't want them strip them out after run or add a simple check at line 48.
  

*If you're not sure how to edit the dates and durations please see the docs of momentjs (http://momentjs.com/docs/).

I've added download links to all USD hourly candles (16k+) and all daily candles for USD and EUR (600+).

If you need the CSV files structured differently let me know.

Very cool. I will use this.

I'm wondering what is common practice for handling missing candles? For sure, the candlestick generator can simply ignore them, but wouldn't it cause errors when data is fed to technical analysis/charts algorithms?

That's a good question. I am building a list of candle files so they can be used to backtest with Gekko, not sure how Gekko will solve this yet. Also not sure how to do TA on candles with gaps. Luckely the USD market has a lot of liquidity, so I guess this won't make much of a difference.

I don't know how charting software normally deals with it but I now emitted the lines from the files. If someone runs into trouble running this and needs special syntax for gaps just let me know and I will re run the script.

Gekko uses these candles when you want to backtest. this is how I solved it for now:

I wondered if this script could be used to visualize trades in a phonegap app?

The script outputs plain numbers to a text file. I guess this data could be used to serve as input for some graphing library. I would suggest to rewrite it to output JSON instead of csv though, or you could convert it to something else (http://shancarter.github.io/mr-data-converter/) after running it.

I'm going to update the script soon to also include volume (and thus store as OHLCV candles).

I think that if during a time period there are no trades then the open,close,high,low is equal to the previous period close price.
This chart was during the time mtgox was down for a few days, and its just as I described.
http://bitcoincharts.com/charts/mtgoxUSD#rg360zigDailyzczsg2011-06-10zeg2011-07-05ztgSzm1g10zm2g25zv

Anyone taken the time to calculate 30-min and/or 15-min candle .CSV's (in the same format as whydifficult provided in his OP) for this dataset and care to share with the group? Thanks in advance.

Nice work :) Just a heads up - your JPY data is off by a factor of 100. See https://en.bitcoin.it/wiki/MtGox/API#Number_Formats (https://en.bitcoin.it/wiki/MtGox/API#Number_Formats) for the constants you need to divide each currency by.


Epoch, my GUI tool (https://bitcointalk.org/index.php?topic=221055.msg2669142#msg2669142 (https://bitcointalk.org/index.php?topic=221055.msg2669142#msg2669142)) has a port of WhyDifficult's script and you can then calculate any candles you want (export, set currency, set formatter to CSV Candle [Unix], set candle duration in seconds, don't include volume). If there are a few specific ones you're interested in though I could maybe do them for you :)


In my same tool, you can turn Don't include missing candle data off and get the data the way you want.

Nitrous, I did try your GUI tool (nice initiative, BTW), downloaded the MtGox data (638kB .sql file), but the tool crashes for me when I try to export to CSV.

I'll try out your next released version, but in the meantime if you happen to have 1800-second and 900-second CSVs (for gekko) that you could post it would keep me occupied until then.  ;)

Thanks for the information.

Oh yes, sorry I forgot you posted in the other thread :P I've released a new version, and I'm about to generate those CSVs for USD. I'll update this post when I've done that.

---

Edit:

USD/900 - https://dl.dropboxusercontent.com/u/1760961/Candles/USD-900-Unix-novolume.csv
USD/1800 - https://dl.dropboxusercontent.com/u/1760961/Candles/USD-1800-Unix-novolume.csv

The tool does now index the databases, so it didn't take 48 hours, but I guess because I didn't do it on a VPS it took 50 minutes instead of 5 for each.

Thanks! I changed the OP and the topic name to reflect that it hosts a list of downloadable candles (I also included yours, let me know if you mind). I've pointed to your tool for everyone looking for a calculator. I still need some time to check it out by the way.

The 5 minutes was on a VPS with an SSD, so that boost in IO probably did speed it up a lot.

I don't mind :) At the moment there are still some bugs with the tool, especially on windows 7, but hopefully I'll finally have a working cross-platform build sometime tomorrow, so there's no pressure to check it out yet ;)

Edit: btw, those are 900s and 1800s, not minutes!

Appreciate the links ... didn't see them the first time I read the post!

I just created a new topic with another technique to getting candles:

https://bitcointalk.org/index.php?topic=277704.new#new

That's awesome! I've added this link to the OP.

--

On a sidenote, I am working on my own platform which will offer candles for a lot of different exchanges / markets in a similar format as the candles above. If you want to get notified you can signup on our website:

wizbit (http://wizb.it)

I'm looking forward to it. In the meantime, the candles I'm downloading seem to have a slight variation from the ones you've calculated, leading to completely different backtesting results. Would you mind taking a look when you get a chance? I'm not sure where the variation stems from.

When there are no trades during a giving candle interval the candles are removed from the files. While instead they should have an OHLC of close of the previous candle, I guess this could be the cause.

EDIT: I will update the candles including empty candles ASAP.

Hi, I had a look at the difference between whydifficult's daily BTCUSD data and the data from bitcoincharts, and I noticed that whilst bitcoincharts data is aligned to midnight GMT for each datum, whydifficult's data is aligned to 17:00 GMT for each day, so that is likely the source of the differences wrt his data. This difference alignment shouldn't really cause a problem though. As for my data, I'll have a look at that now (remember I used 900s/1800s intervals, not 900m/1800m).

Ok, I looked at my data too and unfortunately I can't compare the two datasets. For example, my 1800s candles (due to the currently available bq data) only goes up to 23 May 2013, whilst the bitcoincharts data will only start at 22 Jun 2013, so unfortunately I can't really compare the two.

Make sure you select custom time, I have candles from your start date up to yesterday because I copied 5 month intervals into my Excel sheet.

Thanks. Ok I believe I see the reason now - bitcoincharts reuses the closing price of the last datum for the opening price of the next, whereas I only included trades within the range t and t+i (where t is the unix timestamp at the start of the candle and i is the candle interval). Both ways make sense - bitcoincharts gives the theoretical price for a trade if you were to do it at t, whereas whydifficult's and mine give the price for the first trade - however our way conforms to the official use of the term 'opening price' http://www.investopedia.com/terms/o/openingprice.asp (http://www.investopedia.com/terms/o/openingprice.asp). Except for the opening price, most of my data agrees with that of bitcoincharts', except for where the opening price is the same as the high, low or closing, in which case my values differ in about 5% of cases.

Make of this what you will. I don't think it makes too much of a difference anyway - backtesting is an approximation, and during real trading your actual trade prices are likely to be different from those given by the candles anyway. All of these datasets seem to be accurate, albeit inconsistent with each other, but these inconsistencies seem easily explained.

Nice, wizbit look's great! Will you support different formats, date ranges and intervals? I know a few people using forex tools would like ISO dates (ideally separate date/time fields).

Thanks for taking a look! I've got one more issue. When I test with the BTCe candles, I end up with a profit of 41664823177.120% on ema 1/5 hourly. I've got no idea what's causing that! Anyone have any ideas?

Here is the log: http://pastebin.com/M061Q03M (http://pastebin.com/M061Q03M)

The perfect trade strategy? xD

I don't know, sorry. I'm sure whydifficult can help you though.

Well, any ideas whydifficult?

Hmm that's very strange, I did heard some similar things before though.

Unfortunately I am not able to reproduce it, could you please send me the candle file you used + the EMA settings? That way I can debug the behaviour more easy without staring in the dark.

Of course, I should've done it from the start. EMA 5 short, 1 long, .25 threshold, .2 fee.

https://mega.co.nz/#!2Et0GLbT!YATk7xQsBKrVSSZmF9_8DQ0Mrcld9GDlhZMPOEogC9M (URL won't hyperlink for some reason ¯\_(ツ)_/¯)