More excel macro's - day-trade accounting sheet & Mt. Gox history

[tarrant_01]

I'm building off of my other macro here: https://bitcointalk.org/index.php?topic=62856.0

Would anyone have interest in a macro that goes through your Mt. Gox transactions and creates a sheet like this?



I have it mostly completed. It currently just matches buy and sell orders and splits the excess for varying amount/sell prices.

I am not planning on doing the daily profit section at the bottom, I could but I am not sure it is really worth it these days with such a lack in volatility.

What do you really want to see data wise?

[marked]

I'm building off of my other macro here: https://bitcointalk.org/index.php?topic=62856.0

I knew I had forgotten to do something with spreadsheets a while ago... I had downloaded and tested that dataset you sent me, but my machine crashed out on me, and I ended up doing something else. Sorry.

I am not planning on doing the daily profit section at the bottom, I could but I am not sure it is really worth it these days with such a lack in volatility.

There was a 2% drop change today (about 3600BTC) from 4.83- 4.75 on mtgox, does that count as volatile?

What about making it possible to import from multiple exchanges, CryptoX, intersango, btc-e, vircurex etc.? or at least enable multiple sheets so that it is possible to import ourselves. Some of these don't have easy csv downloads so I maybe suggesting something that isn't currently possible from them.

I think it might be worth having an averaging function in there somewhere, so for example the 10BTC bought at 7.5 is offset by the 30 bought at 4.3, giving me an overall view of BTC/USD or BTC/GBP.


marked

[tarrant_01]

Don't worry about it marked.  I've changed that file over and over since then.  Also, it looks like the hosting site, fyels.com,  was bought by another company and is now re-directing.

If you send me a csv file sample from their site with data that I can use for reference(deposits, withdraws, buys, sells, etc), then yes I can make it possible to import it all.  I currently do not trade through other exchanges since it has been difficult to get money to them.

I expect things to be a bit more volatile with the discounts at Mt. Gox this week, other than that though we are usually looking at a semi-straight line.

I think it might be worth having an averaging function in there somewhere, so for example the 10BTC bought at 7.5 is offset by the 30 bought at 4.3, giving me an overall view of BTC/USD or BTC/GBP.

It's very easy to throw in an average function for a range of cells.  I'm not sure how I am going to be able to do multi-currency exchanges unless I create a seperate sheet entirely, which would be very easy to do (I think).

[tarrant_01]

Ok, I've added a few stats that get added at the end of all of the data.

General: USD Deposited, USD Withdrawn, BTC Deposited, BTC Withdrawn

More revealing information: Gains/Losses, Average G/L, Average G/L %, Avg Buy Price, Avg Sell Price. Formulas in this section change with the filters so seeing monthly/daily gains is possible.

Good sale prices are marked green and bad ones marked red.

Marked, you seem to only ever be the one interested in this.  If you want to, perhaps we could work together to get this to be useful.  I wouldn't mind seeing it go multi-currency multi-exchange.  I would be even nicer to see it grab the data itself rather than having to get the csv files itself.

I will upload it when I get home.  I have a few things to sort out with it before it's ready for distribution.

[marked]

Ok, I've added a few stats that get added at the end of all of the data.

General: USD Deposited, USD Withdrawn, BTC Deposited, BTC Withdrawn

More revealing information: Gains/Losses, Average G/L, Average G/L %, Avg Buy Price, Avg Sell Price. Formulas in this section change with the filters so seeing monthly/daily gains is possible.

Good sale prices are marked green and bad ones marked red.

sounds good.

Marked, you seem to only ever be the one interested in this.  If you want to, perhaps we could work together to get this to be useful.  I wouldn't mind seeing it go multi-currency multi-exchange.  I would be even nicer to see it grab the data itself rather than having to get the csv files itself.

I have some scripts that pull and reformat the json data to csv, but they are a bit brutal, and not really json compliant, as I rely on an order in the ticker. 

I have perl/python in both active* and cygwin versions, so should be able to rewrite them, and match if you have or use external processing using another language.

Not sure if there is a powershell json library that can interact with excel?

I will upload it when I get home.  I have a few things to sort out with it before it's ready for distribution.

I use btc-e, mtgox, bitcoinica (as an exchange), vircurex, intersango, cryptoxchange in that order of use. I have started to look at bitfloor, and probably have one or two others registered (oh, yes, the german btcde one can't think of the domain offhand, it is a market matcher?).

btc-e is an absolute nightmare txn history, and there is no current csv download.
bitcoinica pull from ledger, no download, c&p
vircurex should be a simple cut and paste of the history, but I'll need to check the multi-currency status
intersango from api?
cryptoxchange



marked

[tarrant_01]

Here's the file: http://www.fileden.com/files/2012/4/2/3286762/MtGox_Merge_v2.xlsm

Added: BTC and USD balances to the general information.

Running my history is a little weird.  Even though the BTC I have on hand right now isn't sold, it's being shown as sold on the sheet.  This is because of BTC deposits and having a greater number of sales of BTC vs buys.  The GainsLosses sheet only shows you what you've made based on the BTC you've bought through MtGox.  When "new" BTC is sold, it just gets added to the next free BTC buy entry and rated as a good/bad sale. 

I hope that makes sense.

I haven't added other currencies yet.  I do need some test data for that although it's all set up to easily add new ones.