Bitcoin Forum
December 09, 2016, 09:20:23 PM *
News: To be able to use the next phase of the beta forum software, please ensure that your email address is correct/functional.
 
   Home   Help Search Donate Login Register  
Pages: [1]
  Print  
Author Topic: More excel macro's - day-trade accounting sheet & Mt. Gox history  (Read 2339 times)
tarrant_01
Hero Member
*****
Offline Offline

Activity: 546



View Profile
April 01, 2012, 04:02:14 PM
 #1

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?

1P95gCUCw3Tjb7yyoYtW3ARZZQyTpFgk6H
1481318423
Hero Member
*
Offline Offline

Posts: 1481318423

View Profile Personal Message (Offline)

Ignore
1481318423
Reply with quote  #2

1481318423
Report to moderator
1481318423
Hero Member
*
Offline Offline

Posts: 1481318423

View Profile Personal Message (Offline)

Ignore
1481318423
Reply with quote  #2

1481318423
Report to moderator
1481318423
Hero Member
*
Offline Offline

Posts: 1481318423

View Profile Personal Message (Offline)

Ignore
1481318423
Reply with quote  #2

1481318423
Report to moderator
"Bitcoin: mining our own business since 2009" -- Pieter Wuille
Advertised sites are not endorsed by the Bitcoin Forum. They may be unsafe, untrustworthy, or illegal in your jurisdiction. Advertise here.
1481318423
Hero Member
*
Offline Offline

Posts: 1481318423

View Profile Personal Message (Offline)

Ignore
1481318423
Reply with quote  #2

1481318423
Report to moderator
marked
Full Member
***
Offline Offline

Activity: 168



View Profile
April 01, 2012, 05:40:29 PM
 #2

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.

Quote
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
Hero Member
*****
Offline Offline

Activity: 546



View Profile
April 01, 2012, 06:25:06 PM
 #3

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.

Quote
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).

1P95gCUCw3Tjb7yyoYtW3ARZZQyTpFgk6H
tarrant_01
Hero Member
*****
Offline Offline

Activity: 546



View Profile
April 02, 2012, 06:25:45 PM
 #4

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.

1P95gCUCw3Tjb7yyoYtW3ARZZQyTpFgk6H
marked
Full Member
***
Offline Offline

Activity: 168



View Profile
April 02, 2012, 10:36:06 PM
 #5

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.

Quote
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?

Quote
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
Hero Member
*****
Offline Offline

Activity: 546



View Profile
April 03, 2012, 01:21:51 AM
 #6

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.

1P95gCUCw3Tjb7yyoYtW3ARZZQyTpFgk6H
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!