Bitcoin Forum
December 08, 2016, 12:09:39 PM *
News: Latest stable version of Bitcoin Core: 0.13.1  [Torrent].
 
   Home   Help Search Donate Login Register  
Pages: [1]
  Print  
Author Topic: MTGOX Personal Trade History Aggregator - Calc your Account profitability etc!  (Read 4274 times)
twa
Full Member
***
Offline Offline

Activity: 140

Tipsters Championship www.DirectBet.eu/Competition


View Profile
January 20, 2012, 12:29:11 AM
 #1

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.

Downloads:

Blank spreadsheet with no data: http://dl.dropbox.com/u/12292739/MTGOX%20History%20Blank.xlsm
Spreadsheet w/ my imported history: http://dl.dropbox.com/u/12292739/MTGOX%20History.xlsm
Full Screenshot: http://dl.dropbox.com/u/12292739/screen.png

Instructions: 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!
1481198979
Hero Member
*
Offline Offline

Posts: 1481198979

View Profile Personal Message (Offline)

Ignore
1481198979
Reply with quote  #2

1481198979
Report to moderator
1481198979
Hero Member
*
Offline Offline

Posts: 1481198979

View Profile Personal Message (Offline)

Ignore
1481198979
Reply with quote  #2

1481198979
Report to moderator
1481198979
Hero Member
*
Offline Offline

Posts: 1481198979

View Profile Personal Message (Offline)

Ignore
1481198979
Reply with quote  #2

1481198979
Report to moderator
Advertised sites are not endorsed by the Bitcoin Forum. They may be unsafe, untrustworthy, or illegal in your jurisdiction. Advertise here.
1481198979
Hero Member
*
Offline Offline

Posts: 1481198979

View Profile Personal Message (Offline)

Ignore
1481198979
Reply with quote  #2

1481198979
Report to moderator
1481198979
Hero Member
*
Offline Offline

Posts: 1481198979

View Profile Personal Message (Offline)

Ignore
1481198979
Reply with quote  #2

1481198979
Report to moderator
adamstgBit
Legendary
*
Offline Offline

Activity: 1904


Trusted Bitcoiner


View Profile WWW
January 20, 2012, 12:44:09 AM
 #2

i dont want to sound mean or anything...

but how is raw data better then a graphical representation as we see on bitcoincharts?

twa
Full Member
***
Offline Offline

Activity: 140

Tipsters Championship www.DirectBet.eu/Competition


View Profile
January 20, 2012, 01:33:54 AM
 #3

This is for your personal order history from MTGOX.  Sorry I was pretty ambiguous with the title considering the post is a wall of text. 
jojo69
Hero Member
*****
Offline Offline

Activity: 728



View Profile
January 20, 2012, 02:19:14 AM
 #4

oh...that could be pretty cool

This is not some pseudoeconomic post-modern Libertarian cult, it's an un-led, crowd-sourced mega startup organized around mutual self-interest where problems, whether of the theoretical or purely practical variety, are treated as temporary and, ultimately, solvable.
Censorship of e-gold was easy. Censorship of Bitcoin will be… entertaining.
elux
Legendary
*
Offline Offline

Activity: 1454



View Profile
January 20, 2012, 02:19:46 AM
 #5

Hey guys this is my first board post and I appreciate any feedback or suggestions! (...) 
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.


Awesome! Thanks. Will this work in LibreOffice? Oh, and welcome to bitcointalk!  Cheesy
twa
Full Member
***
Offline Offline

Activity: 140

Tipsters Championship www.DirectBet.eu/Competition


View Profile
January 20, 2012, 02:45:53 AM
 #6

I dont think it will work in libre office.

I posted a screenshot of what my history looks like up at the top of the post FYI for anyone curious.

You could tell how profitable your trading has been or how much you have paid in fees TOTAL.  I was always curious how much my fees added up to at least Smiley

StewartJ
Sr. Member
****
Offline Offline

Activity: 392



View Profile
January 20, 2012, 03:18:17 AM
 #7

I dont think it will work in libre office.

I posted a screenshot of what my history looks like up at the top of the post FYI for anyone curious.

You could tell how profitable your trading has been or how much you have paid in fees TOTAL.  I was always curious how much my fees added up to at least Smiley



Could you post a screen shot or two of your excel file on this thread, just an example, so we can get a visual idea?

Always looking for helpful trading tools, and happy to donate for something I will use everyday.

twa
Full Member
***
Offline Offline

Activity: 140

Tipsters Championship www.DirectBet.eu/Competition


View Profile
January 20, 2012, 03:32:25 AM
 #8

Sure! I think this is what you mean. 

http://db.tt/t11hVltx

That was from the post so if that doesn't help I will be more than happy to take more screen shots. 
StewartJ
Sr. Member
****
Offline Offline

Activity: 392



View Profile
January 20, 2012, 05:14:00 AM
 #9

Sure! I think this is what you mean. 

http://db.tt/t11hVltx

That was from the post so if that doesn't help I will be more than happy to take more screen shots. 

Thanks, will review.
Valalvax
Sr. Member
****
Offline Offline

Activity: 439


View Profile
January 20, 2012, 05:22:56 AM
 #10

This...

This is something I do not want.



Because it'd show me just how much money I'd lost tooling around in the market :/

Seriously though, very cool
payb.tc
Hero Member
*****
Offline Offline

Activity: 812



View Profile
January 20, 2012, 05:25:03 AM
 #11

Code:
echo ($val == 0) ? ' ' : $val;

it would be much easier on the eyes if you could replace all the occurrences of 0.00000 with empty cells.
twa
Full Member
***
Offline Offline

Activity: 140

Tipsters Championship www.DirectBet.eu/Competition


View Profile
January 20, 2012, 02:40:51 PM
 #12

Code:
echo ($val == 0) ? ' ' : $val;

it would be much easier on the eyes if you could replace all the occurrences of 0.00000 with empty cells.


Yeah I had done that originally but decided to insert values because I like eyestrain.
twa
Full Member
***
Offline Offline

Activity: 140

Tipsters Championship www.DirectBet.eu/Competition


View Profile
January 20, 2012, 04:00:49 PM
 #13

I added a new screenshot that should be more clear.  It also shows the account profitability calculation based on transactions with nifty up/down arrows to boot.  

Current Account Value - Total Invested = Current Profitability

Current Account Value = USD in Account + (BTC in Account * Rate from Last Transaction)

Total Invested = "deposits" excluding "deposits" that are redeeming anything (this is because I have only used Dwolla to deposit USD so anything I have redeemed was withdrawn prior)

twa
Full Member
***
Offline Offline

Activity: 140

Tipsters Championship www.DirectBet.eu/Competition


View Profile
January 20, 2012, 04:57:49 PM
 #14

Added new screens in post to more clearly represent what this does as my explanations aren't that great.
cypherdoc
Legendary
*
Offline Offline

Activity: 1764



View Profile
January 21, 2012, 01:11:29 AM
 #15

too lazy to look closely but will this tell me my total USD invested in BTC?
twa
Full Member
***
Offline Offline

Activity: 140

Tipsters Championship www.DirectBet.eu/Competition


View Profile
January 21, 2012, 01:49:43 AM
 #16

too lazy to look closely but will this tell me my total USD invested in BTC?

yes
gtabmx
Jr. Member
*
Offline Offline

Activity: 52


View Profile
January 21, 2012, 02:41:25 AM
 #17

I did as you instructed, but all I get is your history, not mine from my CSV files. I went to the macros and ran Initiate, but your history didn't get cleared. Could you provide us with a blank template and perhaps a button to Initiate or import?

Thanks,

Mike
twa
Full Member
***
Offline Offline

Activity: 140

Tipsters Championship www.DirectBet.eu/Competition


View Profile
January 21, 2012, 04:35:16 AM
 #18

I did as you instructed, but all I get is your history, not mine from my CSV files. I went to the macros and ran Initiate, but your history didn't get cleared. Could you provide us with a blank template and perhaps a button to Initiate or import?

Thanks,

Mike

Sure Mike - http://dl.dropbox.com/u/12292739/MTGOX%20History%20Blank.xlsm

Let me know if that helps you out.

gtabmx
Jr. Member
*
Offline Offline

Activity: 52


View Profile
January 21, 2012, 04:52:51 AM
 #19

Perfect, thanks. I tried it out, and the data imported beautifully, however, the calculations on the side quickly returned #value cells. I think there is a bug with the calcvalue() method since the first non-dependent cell to be #value is a simple calcvalue(str,1) call with str being

"BTC sold: [tid:1325141427287997] 5.75000000 BTC at $4.26000"

without quotes.

-Mike
twa
Full Member
***
Offline Offline

Activity: 140

Tipsters Championship www.DirectBet.eu/Competition


View Profile
January 21, 2012, 05:00:45 AM
 #20

Perfect, thanks. I tried it out, and the data imported beautifully, however, the calculations on the side quickly returned #value cells. I think there is a bug with the calcvalue() method since the first non-dependent cell to be #value is a simple calcvalue(str,1) call with str being

"BTC sold: [tid:1325141427287997] 5.75000000Â BTC at $4.26000"

without quotes.

-Mike

All you need to do is drag the formulas down and refresh them.  They won't automatically do it.  Also keep in mind that the running total columns for USD and BTC need the first value SET at the bottom.  

They need to start adding to something so the formula copied down won't calculate without that first row at the VERY bottom being set to a value.  So in my case my first deposit at the bottom was $30 so I could just type in 30 in the USD running total column at the bottom.

Edit: Those calculations are definitely not 100% bug free because I wanted to release this sooner than later so I put those in to give more of a starting place for whatever someone wanted to do.  I am working on making them 100% solid though and will update it probably tomorrow.  My first goal was assuring that the import was 100% airtight for the known transactions.

Pages: [1]
  Print  
 
Jump to:  

Sponsored by , a Bitcoin-accepting VPN.
Powered by MySQL Powered by PHP Powered by SMF 1.1.19 | SMF © 2006-2009, Simple Machines Valid XHTML 1.0! Valid CSS!