Topic: MTGOX Personal Trade History Aggregator - Calc your Account profitability etc! (Read 1636 times)

I am guessing Gox changed their output syntax?

Is a newer version available/

Getting error on this line - Help:


"importData(index).RawLine = currentLine"





Private Function Import(ByVal fullPath As String) As Transaction()
    Dim index As Integer
    Dim importData() As New Transaction
    Dim detailCount As Integer
    Dim currentLine As String
    Dim nextLine As String
    Dim textStream As Object
    Dim textStreamFSO As Object
    Set textStreamFSO = CreateObject("Scripting.FileSystemObject")
    Set textStream = textStreamFSO.OpenTextFile(fullPath, 1)
    index = 0
    Do While Not textStream.atendofstream
        ReDim Preserve importData(index)
        currentLine = textStream.readline
        If index = 0 Then
            detailCount = UBound(Split(currentLine, ","))
            currentLine = textStream.readline
            importData(index).RawLine = currentLine
        ElseIf UBound(Split(currentLine, ",")) < detailCount Then
            nextLine = textStream.readline
            importData(index).RawLine = currentLine & nextLine
        Else
            importData(index).RawLine = currentLine

Hey Tarrant I was just trying to show how you could calculate account profitability based on what my code compiles.  The true objective was to compile the history into an easily manipulated format. Yeah if your data extends past the formulas in yellow you would need to just drag the formulas down so it covers everything.  

Well, after I copied and pasted the forumlas and fixed the remaining formulas that still weren't updating right it said i was -$67,000! I wish I even had that much money to put into Bitcoin 

Hmm...I'm a little confused with yours. It imports my history's but it seems you have pre-defined all the cells on the "all" sheet. My history goes well beyond the green area and the yellow area only covers a portion of it. I'm not sure what you mean by "dragging" the formulas down to get them to recalc.  Do you mean I need to copy and paste the yellow section to match the height of my history? You also mentioned in the other thread that the initial deposit has to be entered somewhere. Can't you find this from the data on import?

Can I refresh what more quickly? BTW I wrote a VB.NET program that connects to the mtgox socket and so it would be easy to put something together to get real time ticker data in excel.  It also has the usd Bitcoinica bid/ask that polls every 10 seconds which could go into excel just as easy.

Are asking why I didn't make it automatic after you give your credentials? I assumed that if you can avoid taking account details you should so since it wasn't necessary I decided to nix it.
 

 

arghhh... just spent half an hour going over tarrant_01's could have done both and given you both pointers...

excellent now I know that this can be done, tarrant_01, look in the initiate subroutine.

interested in why you import the csv file a line at a time into an array, rather than read it directly in via a data connection (text from source), and then manipulate it. That's what I do for loading ticker data into excel, though I'm limited to 1 refresh per minute minimum. Can you refresh more quickly than that, ie at 10 or 15 second intervals?


marked

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.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!