This was designed to make the USD/BTC personal history from MTGOX actually useful and in a useful format. It is a great starting point for analysis.
[ Originally posted in speculation board a while ago ]
Downloads:Blank spreadsheet with no data: http://dl.dropbox.com/u/12292739/MTGOX%20History%20Blank.xlsmSpreadsheet w/ my imported history: http://dl.dropbox.com/u/12292739/MTGOX%20History.xlsmFull Screenshot: http://dl.dropbox.com/u/12292739/screen.pngInstructions: Download your two history files from MTGOX and place them in the same directory as my workbook. Then open my workbook and go to the "First" tab and run it using that button (it's the "initiate" macro).
The spreadsheet with my processed history is an example of how my calculations work as a reference. Those example calculations are highlighted in yellow and the formulas are included in the blank one.
Note: This requires Office 2007/2010 on Windows (will not work in Office 2011)
What it is:This automatically combines both of
your MTGOX history CSV files into a single descending list of all transactions in Excel using VBA. I haven’t seen anything like this and I find it to be so much easier to look at. I spent an enormous amount of time on this so give it a shot. I am hoping someone will get some use out of it
Here is what it does visually. These represent the first 3 transactions on my account.
BTC_History.CSV has:
USD_History.CSV has:
Result after processing looks like:
Result with example calculations derived from the processed data:
How it works:1. Download both CSV files from your MTGOX account and place them in the same directory as this spreadsheet
2. It takes each CSV file and imports each line by line into an “array”. During this step:
- Each line is split up into a sub array based on the delimiter (comma).
- The elements of the line are parsed into the 8 or so components of each transaction.
- The trade ID, rate, and fee % are pulled from the “description” element
- The trade ID is compared to a running list of unique trade IDs and added if not present.
3. Once both of those are done (the BTC and USD history csv files) there are 3 arrays – BTC history, USD history, and unique transaction IDs.
4. Now the two history arrays are aggregated based on the unique transaction ids.
- I use the transaction ID because each order has a “balancing” transaction (in accounting terms) that needs to be excluded from the final listing. The USD history has “spent” as its balancing entry and the BTC history has “out”. They represent the effect a transaction has on the other side. Combining the two history files outright will include these “balancing” lines along with the actual transaction and I chose to exclude these and derive the effect on the opposite based on the transaction details.
- For instance, I would exclude the “out” from the BTC side but calculate the BTC effect based on the rate, and USD spent.
- The deposits and withdrawals do not have unique transaction ID's originally so I had to improvise and used the timestamp along with "deposit @" or "withdrawn @" as their unique ID.
5. So after this is finished, I have the list of all the orders and then I sort the list based on the date (descending was my preference).
6. Next, since the list is descending and therefore flows from bottom to top, I swap the transaction line with the transaction’s fee line.
7. Now it finally prints it out on the spreadsheet "all".
Note: Not all types of transactions are handled because I haven’t actually used them before so some may not show up correctly if I haven’t encountered them.
Special handling example: Dwolla deposits now have a hard return in the middle of the “description” for some reason (no other transaction has this) so results in a line split up into two in the CSV file so that is handled with the following:
- The first line of each CSV file contains headings and establishes the number of “columns”.
- Each line is imported and split into an array like normal but then if the number of elements of that line is less than the total established by the first line it assumes this to be a split line. The next line is combined with the current.
Thanks!
