Bitcoin Forum
June 18, 2021, 04:26:32 PM *
News: Latest Bitcoin Core release: 0.21.1 [Torrent]
 
   Home   Help Search Login Register More  
Pages: 1 2 [All]
  Print  
Author Topic: Use Google Spreadsheets to automatically keep track of your wallet balance  (Read 11181 times)
coblee
Donator
Legendary
*
Offline Offline

Activity: 1634
Merit: 1195


Creator of Litecoin. Cryptocurrency enthusiast.


View Profile
June 27, 2011, 10:25:04 PM
 #1

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.

1624033592
Hero Member
*
Offline Offline

Posts: 1624033592

View Profile Personal Message (Offline)

Ignore
1624033592
Reply with quote  #2

1624033592
Report to moderator
1624033592
Hero Member
*
Offline Offline

Posts: 1624033592

View Profile Personal Message (Offline)

Ignore
1624033592
Reply with quote  #2

1624033592
Report to moderator
Advertised sites are not endorsed by the Bitcoin Forum. They may be unsafe, untrustworthy, or illegal in your jurisdiction. Advertise here.
1624033592
Hero Member
*
Offline Offline

Posts: 1624033592

View Profile Personal Message (Offline)

Ignore
1624033592
Reply with quote  #2

1624033592
Report to moderator
1624033592
Hero Member
*
Offline Offline

Posts: 1624033592

View Profile Personal Message (Offline)

Ignore
1624033592
Reply with quote  #2

1624033592
Report to moderator
1624033592
Hero Member
*
Offline Offline

Posts: 1624033592

View Profile Personal Message (Offline)

Ignore
1624033592
Reply with quote  #2

1624033592
Report to moderator
BitCoinBarter
Newbie
*
Offline Offline

Activity: 56
Merit: 0



View Profile
June 28, 2011, 12:27:37 AM
 #2

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
Newbie
*
Offline Offline

Activity: 34
Merit: 0


View Profile
June 28, 2011, 12:54:02 AM
 #3

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

Activity: 84
Merit: 10


View Profile
June 28, 2011, 01:30:14 AM
 #4

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

Activity: 1634
Merit: 1195


Creator of Litecoin. Cryptocurrency enthusiast.


View Profile
June 28, 2011, 02:28:23 AM
 #5

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 Smiley

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

Activity: 80
Merit: 10


View Profile
June 28, 2011, 04:39:43 AM
 #6

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
Donator
Legendary
*
Offline Offline

Activity: 1634
Merit: 1195


Creator of Litecoin. Cryptocurrency enthusiast.


View Profile
June 28, 2011, 05:56:15 AM
 #7

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

Activity: 80
Merit: 10


View Profile
June 28, 2011, 06:21:09 AM
 #8

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
Administrator
Legendary
*
Offline Offline

Activity: 4144
Merit: 8490


View Profile
June 28, 2011, 06:46:04 AM
 #9

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

1NXYoJ5xU91Jp83XfVMHwwTUyZFK64BoAD
coblee
Donator
Legendary
*
Offline Offline

Activity: 1634
Merit: 1195


Creator of Litecoin. Cryptocurrency enthusiast.


View Profile
June 28, 2011, 07:17:49 AM
 #10


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

theymos
Administrator
Legendary
*
Offline Offline

Activity: 4144
Merit: 8490


View Profile
June 28, 2011, 07:33:17 AM
 #11

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

1NXYoJ5xU91Jp83XfVMHwwTUyZFK64BoAD
foo
Sr. Member
****
Offline Offline

Activity: 409
Merit: 250



View Profile
June 28, 2011, 07:34:53 AM
 #12

Very nice. Smiley Posting to follow this thread...

I know this because Tyler knows this.
coblee
Donator
Legendary
*
Offline Offline

Activity: 1634
Merit: 1195


Creator of Litecoin. Cryptocurrency enthusiast.


View Profile
June 28, 2011, 07:41:37 AM
 #13

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

Activity: 768
Merit: 500


View Profile
June 28, 2011, 09:03:05 AM
 #14

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

Will

Sukrim
Legendary
*
Offline Offline

Activity: 2590
Merit: 1003


View Profile
June 28, 2011, 02:00:58 PM
 #15

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

https://www.coinlend.org <-- automated lending at various exchanges.
https://www.bitfinex.com <-- Trade BTC for other currencies and vice versa.
Mail me at Bitmessage: BM-BbiHiVv5qh858ULsyRDtpRrG9WjXN3xf
k
Sr. Member
****
Offline Offline

Activity: 451
Merit: 250


View Profile
June 28, 2011, 10:18:42 PM
Last edit: June 29, 2011, 06:32:27 PM by k
 #16

thanks ChocoboLee,

learned a lot from this. Never knew Google docs were so powerful.
I copied (stole Wink) 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
Donator
Legendary
*
Offline Offline

Activity: 1634
Merit: 1195


Creator of Litecoin. Cryptocurrency enthusiast.


View Profile
June 29, 2011, 08:29:51 AM
 #17

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

UniverseMan
Newbie
*
Offline Offline

Activity: 26
Merit: 0


View Profile
July 04, 2011, 08:33:55 PM
 #18


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.  Smiley
coblee
Donator
Legendary
*
Offline Offline

Activity: 1634
Merit: 1195


Creator of Litecoin. Cryptocurrency enthusiast.


View Profile
July 04, 2011, 10:31:40 PM
 #19

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
Donator
Legendary
*
Offline Offline

Activity: 1634
Merit: 1195


Creator of Litecoin. Cryptocurrency enthusiast.


View Profile
July 04, 2011, 11:05:15 PM
 #20

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.

sunnankar
Legendary
*
Offline Offline

Activity: 1031
Merit: 1000



View Profile WWW
September 05, 2011, 04:33:35 PM
 #21

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

Will

It appears blockexplorer with a TradeHill deposit address does not yield an accurate balance.

Anyone have the code for getting your TradeHill balance via their API imported to Google Docs?

If it cannot be directly imported via formula line in Google Docs then the HTML code for a POST request on my own server would sure be a handy work around that I could scrap with the ImportHTML function.

Pretty please. Thanks.

Sukrim
Legendary
*
Offline Offline

Activity: 2590
Merit: 1003


View Profile
September 05, 2011, 10:50:47 PM
 #22

It appears blockexplorer with a TradeHill deposit address does not yield an accurate balance.
Got an example?

As far as I understand it, deposit adresses do not show higher amounts if you just do trades.
e.g.:
You deposit 10 BTC --> Balance on TH: 10 BTC; Balance on the address: 10 BTC
You trade the 10 BTC for 100 USD --> Balance on TH: 0 BTC; Balance on the address: maybe still 10 BTC (as it is on an adress owned by tradehill and another adress might get "billed" on payouts)
BTC crashes and you buy 100 BTC for 100 USD --> Balance on TH: 100 BTC; Balance on the address: maybe still 10 BTC, maybe less.

I don't really get what you want to achieve... you can track how much you paid to TH over time accurately though.

https://www.coinlend.org <-- automated lending at various exchanges.
https://www.bitfinex.com <-- Trade BTC for other currencies and vice versa.
Mail me at Bitmessage: BM-BbiHiVv5qh858ULsyRDtpRrG9WjXN3xf
piuk
Hero Member
*****
Offline Offline

Activity: 910
Merit: 1001



View Profile WWW
September 06, 2011, 01:16:08 AM
 #23

Nice idea. I made this for those who don't have google docs.

Rassah
Legendary
*
Offline Offline

Activity: 1680
Merit: 1033



View Profile WWW
September 06, 2011, 03:02:38 PM
 #24

Very nice. I'll have to set one up for my savings, too.
Btw, I decrypt my savings wallet file about once a month, because I don't trust data integrity of backups. So, I decrypt it, test it, and encrypt it, making another copy of the file with the file name including the date of the time it was tested. This way i have a rolling backup with multiple copies of the wallet file in my backup locations. If there is some corruption and I can't open the newest file, I'll at least have some older backups to fall back on. I'm paranoid like that.

sunnankar
Legendary
*
Offline Offline

Activity: 1031
Merit: 1000



View Profile WWW
September 06, 2011, 11:45:26 PM
 #25

It appears blockexplorer with a TradeHill deposit address does not yield an accurate balance.
Got an example?

I am using Tradehill like an online wallet and not trading in the account. If I use the Deposit Address to search the block explorer it does not return the available balance in Tradehill.

I would like to have an =importHTML() function for Google Docs which brings in the Available balance from Tradehill.

It would be nice to pull it directly from Tradehill via the API. But if needed I could pull from the API, create a webpage on my server that displays it and then reference that webpage with the function.

Problem is I do not understand the API or PHP well enough to either pull it directly into the Google Doc or create the HTML/PHP for a webpage that displays the Available balance.


sunnankar
Legendary
*
Offline Offline

Activity: 1031
Merit: 1000



View Profile WWW
September 17, 2011, 04:30:03 AM
 #26


That is pretty cool. Anyone figured out how to get Tradehill's API to echo a balance?

pazor
Legendary
*
Offline Offline

Activity: 966
Merit: 1000



View Profile
January 06, 2012, 04:52:40 PM
 #27

Hello coblee,

nice work... thanks for the inspiration!

greetings
pazor

treuhand-Dienst gewünscht? - frag per PM an
BTC 174X17nR7vEQBQo4GXKRGMGaTmB49Gf1yT
tvbcof
Legendary
*
Offline Offline

Activity: 3598
Merit: 1184


View Profile
October 29, 2013, 12:58:03 AM
 #28


I feel OK about necro-ing this thread because I've been using it off and on for the last several years and it's been pretty reliable.

The other day, my permutation gave me a start when it switched to Yuan from BTCChina.  Fixed that.

Now it barely works at all, though it seems to probably be an issue with blockexplorer producing odd results.

Anyone got a more modernized form and/or ideas about a more suitable data source than
'http://blockexplorer.com/q/getreceivedbyaddress/1blahblahblah...'

Also, I sort of ran out of capacity for Google docs to make remote queries at about the same time as I ran out of addresses that I wanted to query so I never bothered to look for other options.  A single source for balance on an address rather than several and some math would eek things out a bit longer.


sig spam anywhere and self-moderated threads on the pol&soc board are for losers.
coblee
Donator
Legendary
*
Offline Offline

Activity: 1634
Merit: 1195


Creator of Litecoin. Cryptocurrency enthusiast.


View Profile
October 29, 2013, 01:05:47 AM
 #29

You can use http://blockchain.info/q/addressbalance/1blahblah or http://blockchain.info/q/getreceivedbyaddress/1blahblah
But be careful as that returns a number in satoshis, so you have to divide it by 100000000

tvbcof
Legendary
*
Offline Offline

Activity: 3598
Merit: 1184


View Profile
October 29, 2013, 01:17:56 AM
 #30

You can use http://blockchain.info/q/addressbalance/1blahblah or http://blockchain.info/q/getreceivedbyaddress/1blahblah
But be careful as that returns a number in satoshis, so you have to divide it by 100000000

Thanks coblee.  I think that blockexplorer is probably rate-limiting some of Google's address space.  Manual queries I do work OK.  I saw some reference to Cloudflare in the output on the spreadsheet.  Dunno if blockexplorer has been using them forever or what (but I kind of doubt it, and was surprised to see it actually.)


sig spam anywhere and self-moderated threads on the pol&soc board are for losers.
willphase
Hero Member
*****
Offline Offline

Activity: 768
Merit: 500


View Profile
October 29, 2013, 10:44:29 AM
 #31

I just create a new wallet on blockchain and pull the public keys of the addresses I wish to monitor into that wallet, then get the balance using

=importData("https://blockchain.info/merchant/WALLET-GIUD/balance?password=PASSWORD")

then just regextract to pull the balance in satoshis

=regexextract(A1,"\d+")

and divide by 10,000,000

=A2/100000000

Since only the public keys are on there, I don't worry about putting a password in my Google doc.

Will

Pages: 1 2 [All]
  Print  
 
Jump to:  

Powered by MySQL Powered by PHP Powered by SMF 1.1.19 | SMF © 2006-2009, Simple Machines Valid XHTML 1.0! Valid CSS!