Use Google Spreadsheets to automatically keep track of your wallet balance

[coblee]

I'd like to share this google spreadsheet I created:
https://spreadsheets.google.com/spreadsheet/ccc?key=0Amu2Hoiel5SYdFJMVV95cG5pbFppSHc4YnVwUzZwanc&hl=en_US&authkey=CIa_g-AM

I created this spreadsheet because I wanted to keep a secure savings account. For this savings account, I have 12 bitcoin addresses that I use for sending money to. And since I rarely need to take money out of my savings account, I would prefer to keep my wallet.dat file encrypted and not point the bitcoin client to that wallet much. But I still want an easy way to check how much money I have in that account. I figured out that I can use blockexplorer.com to check the balance of each of my 12 bitcoin addresses. So I created this spreadsheet to do the work for me. Now, I will never need to decrypt my savings account wallet file unless I need to send money out of it. I can just use this spreadsheet to make sure that my money is still safe and sound.

This may be helpful for others too, so I made this example spreadsheet. To use it, make a copy of it to your own account. If you want the total balance to be accurate, be sure to copy all your receiving bitcoin addresses into the spreadsheet and create more rows if you need to.

[BitCoinBarter]

Admin,

Could you move this thread to Newbies?

Why? When I was sandboxed into the Newbies section, I did not go out and read the other sections (why tease myself).

There my be 1 (or 2) people who feel the same way and would not get this information.

Thank you.

[ghost]

You'll want to change the formula for column E to: =Value(Substitute(Substitute(Index(ImportHTML(C3,"list",1),3), "Received BTC: ", ""), " ", ""))

Blockexplorer uses a space instead of a comma so balances greater then 1000 will show up like 1 000. The space needs removed for the Value function to work.

[eturnerx]

OP: Great work. Using the ideas from your spreadsheet I can properly calc my savings wallet into my spreadsheets using live balances.

[coblee]

ghost: thanks, I will make the change. I have not run cross this issue since I don't play with accounts with more than 1000 bitcoins

eternex: thanks. If you really want the account value to be real time, check out the workaround code in the mtgox price cell. I didn't do that for the other cells because in most cases, you don't need it.

[CryptoCommodity]

I like this a lot.  Thanks!

One question.  Anyone know why blockexplorer would be off for a particular address?

I received a total of 1.95, sent 1.00 plus a .0005 for a total of -1.0005.  My bitcoin client shows a total balance of .9495 but blockexplorer show .91.

The address I am referring to can be seen here.

http://blockexplorer.com/address/1QGhZ2EJmvfH2wWAzdo6rKHPjkQeRuQ2EU

[coblee]

I like this a lot.  Thanks!

One question.  Anyone know why blockexplorer would be off for a particular address?

I received a total of 1.95, sent 1.00 plus a .0005 for a total of -1.0005.  My bitcoin client shows a total balance of .9495 but blockexplorer show .91.

The address I am referring to can be seen here.

http://blockexplorer.com/address/1QGhZ2EJmvfH2wWAzdo6rKHPjkQeRuQ2EU

Check this transaction out:
http://blockexplorer.com/tx/15571140b463eb09c05da9b69e317cfa6c4ca4e4838fe61bc602982336d88efc#i1425815

You basically sent the full 1.04 out and .0395 got returned to you at another address.

[CryptoCommodity]

Thanks. 

I knew that happened in theory but it wasn't until I put the addresses I had used into the OPs spreadsheet that I noticed a discrepancy.

[theymos]

Don't scrape the HTML pages. There are better API pages:
http://blockexplorer.com/q/getreceivedbyaddress
http://blockexplorer.com/q/mytransactions

[coblee]

Don't scrape the HTML pages. There are better API pages:
http://blockexplorer.com/q/getreceivedbyaddress
http://blockexplorer.com/q/mytransactions

But I need both getreceivedbyaddress and getsendbyaddress. Do you plan to implement the latter? Or better yet, how about getbalancebyaddress?

[theymos]

The balance is totally useless for anything like this, as you've seen.

[foo]

Very nice. Posting to follow this thread...

[coblee]

The balance is totally useless for anything like this, as you've seen.

This is useful to me since for my savings account, I will rarely send money out. And when I do and there is a new return address, I will just add that address to my spreadsheet. I wish the client will let me specify a return address and not just create a new one, but that's another topic.

If you would provide the getsentbyaddress or getbalancebyaddress, I can make the spreadsheet use those APIs. Otherwise, html scraping is the best way.

[willphase]

Nice sheet.  You will need to export all your public keys including the hidden ones if you want the balance to be accurate.  And as theymos says, you really need to be using the mytransactions API to avoid hammering his server, I am querying 50 addresses at a time so only perform 3 GET requests for my entire wallet.

My android wallet balance viewer (link in my sig) does the same thing on Android. Shameless plug.

Will

[Sukrim]

Instead of depending on blockexplorer.com you could also try to have these functions in the OpenSource Alternative Block Explorer and host a small instance yourself (on DynDNS for example).

[k]

thanks ChocoboLee,

learned a lot from this. Never knew Google docs were so powerful.
I copied (stole ) many of your ideas and made this live arbitrage chart with USD/BTC prices of the largest exchanges.

https://spreadsheets.google.com/spreadsheet/pub?key=0Arbegqx_2nKadElRaFlBYWljTURCMGl0R3prTkZsU2c&single=true&gid=1&output=html

@Sukrim - must check out the OpenSource Alternative Block Explorer, haven't come across it yet.

[coblee]

Feel free to copy (or steal!) my spreadsheet formulas. Yeah, Google docs is pretty powerful.

[UniverseMan]

Don't scrape the HTML pages. There are better API pages:
http://blockexplorer.com/q/getreceivedbyaddress
http://blockexplorer.com/q/mytransactions

But I need both getreceivedbyaddress and getsendbyaddress. Do you plan to implement the latter? Or better yet, how about getbalancebyaddress?

Not sure if it was put up in response to your request, but there is a getsentbyaddress value accessible on blockexplorer right now. Note the past tense: getsentbyaddress, not getsendbyaddress as you asked for. (Though this does conform to the usage of getreceivedbyaddress.)

I was able to import the same data you got by scraping HTML with the lines:

Code:

=ImportData("http://blockexplorer.com/q/getreceivedbyaddress/"&B3)
=ImportData("http://blockexplorer.com/q/getsentbyaddress/"&B3)

And thanks for the Doc. It's great. Once I see my balance going up some more I'll send you a donation.  

[coblee]

Thanks! I didn't realize theymos added it. I made the change to the spreadsheet. Should be much better now... no more html scraping.

[coblee]

BTW, this system will only work if you are careful when you take money out of this "savings" account. Since if you create a transaction that has change, the change will go to a random wallet address. You will need to add that wallet address to this spreadsheet in order for it to stay accurate.

So the way I plan to use it is for each address I have in this account, I plan to put the same number of bitcoins in them. So for example, if I have 10 BTC in each of the 12 addresses, I will have 120 BTC total. If I need to transfer money out, I do it in a multiple of 10s. That way, there won't be any change. For anything less than 10 BTC, I just keep in my checking account.

I hope in the future, the client will let me choose which account I want the change in.