Title: MTGOX Personal Trade History Aggregator - Calc your Account profitability etc! Post by: twa on February 20, 2012, 06:12:58 PM 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: http://dl.dropbox.com/u/12292739/btc.png USD_History.CSV has: http://dl.dropbox.com/u/12292739/usd.png Result after processing looks like: http://dl.dropbox.com/u/12292739/result.png Result with example calculations derived from the processed data: http://dl.dropbox.com/u/12292739/extended.png 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:
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.
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:
Thanks! Title: Re: MTGOX Personal Trade History Aggregator - Calc your Account profitability etc! Post by: marked on February 20, 2012, 11:11:21 PM 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. arghhh... just spent half an hour going over tarrant_01's could have done both and given you both pointers... :D Quote 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). excellent now I know that this can be done, tarrant_01, look in the initiate subroutine. Quote 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 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 Title: Re: MTGOX Personal Trade History Aggregator - Calc your Account profitability etc! Post by: tarrant_01 on February 20, 2012, 11:16:02 PM arghhh... just spent half an hour going over tarrant_01's could have done both and given you both pointers... :D ;D Title: Re: MTGOX Personal Trade History Aggregator - Calc your Account profitability etc! Post by: twa on February 21, 2012, 12:22:12 AM 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. arghhh... just spent half an hour going over tarrant_01's could have done both and given you both pointers... :D Quote 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). excellent now I know that this can be done, tarrant_01, look in the initiate subroutine. Quote 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 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 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. Title: Re: MTGOX Personal Trade History Aggregator - Calc your Account profitability etc! Post by: twa on February 21, 2012, 12:28:15 AM 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. arghhh... just spent half an hour going over tarrant_01's could have done both and given you both pointers... :D Quote 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). excellent now I know that this can be done, tarrant_01, look in the initiate subroutine. Quote 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 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 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. Title: Re: MTGOX Personal Trade History Aggregator - Calc your Account profitability etc! Post by: tarrant_01 on February 21, 2012, 10:38:23 PM 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?
Title: Re: MTGOX Personal Trade History Aggregator - Calc your Account profitability etc! Post by: tarrant_01 on February 21, 2012, 10:51:28 PM 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 ;D
Title: Re: MTGOX Personal Trade History Aggregator - Calc your Account profitability etc! Post by: twa on February 21, 2012, 10:52:58 PM 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? 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. Title: Re: MTGOX Personal Trade History Aggregator - Calc your Account profitability etc! Post by: statdude on April 24, 2013, 05:04:32 PM 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 Title: Re: MTGOX Personal Trade History Aggregator - Calc your Account profitability etc! Post by: statdude on April 24, 2013, 05:14:13 PM Is a newer version available/
Title: Re: MTGOX Personal Trade History Aggregator - Calc your Account profitability etc! Post by: jimmydorry on April 26, 2013, 12:49:30 AM I am guessing Gox changed their output syntax?
|