Bitcoin Forum
July 16, 2019, 11:14:58 AM *
News: Latest Bitcoin Core release: 0.18.0 [Torrent] (New!)
 
   Home   Help Search Login Register More  
Pages: [1]
  Print  
Author Topic: Spreadsheet for tracking your current BTC/USD value  (Read 4610 times)
lukestokes
Full Member
***
Offline Offline

Activity: 165
Merit: 102


Live life on purpose


View Profile WWW
March 06, 2013, 05:26:20 AM
 #1

I'm new to Bitcoin and quickly realized how helpful a spreadsheet is for keeping track of the few buys I've made at different levels and what my overall USD value is. I figured others may find this useful as well, so I made a generic one. Feel free to make a copy and enjoy: https://docs.google.com/spreadsheet/ccc?key=0AkOD_dGHOm6IdE5ZbTdRYmhxLUNBSkFvb2dqS0RKTHc#gid=0

If you already have something like this in place, what are you using? I assume there has to be something more sophisticated out there.

http://www.foxycart.com: Helping developers create flexible, powerful, custom ecommerce in less time, while equipping merchants with the fastest checkout flow available to their customers. 60+ payment gateways, including Bitpay. Bitrated user: lukestokes.
Advertised sites are not endorsed by the Bitcoin Forum. They may be unsafe, untrustworthy, or illegal in your jurisdiction. Advertise here.
lukestokes
Full Member
***
Offline Offline

Activity: 165
Merit: 102


Live life on purpose


View Profile WWW
March 06, 2013, 05:50:25 PM
 #2

Apologies for posting this in general if it's trading related. I didn't think of keeping track of my BTC via a spreadsheet as trading, but I guess that makes sense.

My original question still stands. What is everyone using to keep track of this stuff? If you have multiple online and offline wallets, surely you need a tool to keep track of them all, right?

http://www.foxycart.com: Helping developers create flexible, powerful, custom ecommerce in less time, while equipping merchants with the fastest checkout flow available to their customers. 60+ payment gateways, including Bitpay. Bitrated user: lukestokes.
mokahless
Sr. Member
****
Offline Offline

Activity: 470
Merit: 250



View Profile
March 06, 2013, 06:01:24 PM
Last edit: March 06, 2013, 06:12:25 PM by mokahless
 #3

bitcoinexchangerate.org/price is new to me and I like it. The importXML command I was also unaware of. I'll be adding those to my spreadsheet.

I've been using a couple methods to get the price myself.

1. The first thing I used was =ImportData("http://blockchain.info/q/24hrprice") to get a 24-hour average price. The webpage is just the price text. But I found at least once a week, the price would be totally messed. Once it was $14 when Bitcoin had been $20-$30 in the past week.

2. I found this command that I can't quite figure out:
=Index(ImportHTML("http://bitcoincharts.com/"&"?workaround="&INT(NOW()*1E3)&REPT(GoogleFinance("GOOG");0),"table",4),2,2)
But he seems to be importing the latest price from bitcoincharts and somehow forcing it to update faster than the standard ~1 hour updates of docs.

3. I wanted the latest mtgox price from mtgox so I whipped up a quick bash script on my centos webserver:

Code:
stop=$(<stop.txt)
while [ "$stop" = 0 ];
do
stop=$(<stop.txt)
wget -q https://mtgox.com/code/data/ticker.php
cat ticker.php | cut -d',' -f8 | cut -d':' -f2 > last.htm
cat ticker.php | cut -d',' -f5 | cut -d':' -f2 > vol.htm
cat ticker.php | cut -d',' -f3 | cut -d':' -f2 > avg.htm
cat ticker.php | cut -d',' -f2 | cut -d':' -f2 > low.htm
cat ticker.php | cut -d',' -f1 | cut -d':' -f3 > high.htm
date > lastupdated.htm
rm -rf ticker.php
sleep 5
done

then I just ran it:

./update.sh&
disown [job#]

Then I just point docs to the webpage with =ImportData("http://mysite.com/last.htm")
I know I could have done parsing in some other web language or something and made a script there but I'm way more familiar with bash shell scripting so this was much quicker for me.

The wallet contents are easy to fetch from blockexplorer.com/q/
I make a chart like this:
A                  | B
wallet address | =ImportData("http://blockexplorer.com/q/addressbalance/"&A1)

...[for however many addresses are in your wallet]

total              | =sum(B2:B12)

I created a public spreadsheet that I planned to put all kinds of data in but there was no interest so I abandoned it and just keep updating my personal one.

lukestokes
Full Member
***
Offline Offline

Activity: 165
Merit: 102


Live life on purpose


View Profile WWW
March 06, 2013, 06:21:30 PM
 #4

bitcoinexchangerate.org/price is new to me and I like it. The importXML command I was also unaware of. I'll be adding those to my spreadsheet.

Sweet! I'm so glad you found something useful here.

The wallet contents are easy to fetch from blockexplorer.com/q/
I make a chart like this:
A                  | B
wallet address | =ImportData("http://blockexplorer.com/q/addressbalance/"&A1)

...[for however many addresses are in your wallet]

total              | =sum(B2:B12)

I created a public spreadsheet that I planned to put all kinds of data in but there was no interest so I abandoned it and just keep updating my personal one.


That's a great idea, I should add that to mine as well. I'm surprised there's no interest in this sort of thing. For me, it was day 1 stuff. I had to know what was going on to figure out if this was worth further interest. Watching it the last 30 days, it's been pretty clear to me, it is worth my time (that is, unless the bottom falls out tomorrow).

Thanks again for your comments.

http://www.foxycart.com: Helping developers create flexible, powerful, custom ecommerce in less time, while equipping merchants with the fastest checkout flow available to their customers. 60+ payment gateways, including Bitpay. Bitrated user: lukestokes.
mokahless
Sr. Member
****
Offline Offline

Activity: 470
Merit: 250



View Profile
March 08, 2013, 08:45:21 PM
 #5

I forgot to mention and was reminded when blockchain.info went down. In my case, I use a lot of data from them. I don't do scripting or if statements in my spreadsheets so if blockchain.info sends me their "we're down" page, it completely screws up my spreadsheet because for some reason, the import will go into multiple cells, overwriting your other data. (html tags all over the spreadsheet)

So essentially, when blockchain.info is down, my spreadsheet is down.

To fix this, once blockchain.info comes back online, simply roll back the spreadsheet changes to the last time they were correct. In other words, to before you tried to view your spreadsheet while blockchain.info was down.

This is a simple fix that can be done by clicking on the "all changes saved" or "there were saved changes" etc. That small underlined grey text that appears at the top. Then, in the sidebar, simply select the version before the screw-ups.

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!