Since 2013, I've been tracking my bitcoin transfers and balances in my own spreadsheet. This is important, because with cryptocurrency, you are your own bank. Recently I started playing around with Open Ledger and things started getting really complicated because of the various markets there. You can buy cryptocurrencies using various currencies like bitcoin, bitUSD, and BitShares. Keeping track of an asset's value on these different markets was getting really difficult.
Yesterday, I found this spreadsheet which Pablo Yabo created just a few days ago. It's pretty awesome in that it pulls live rates for various cryptocurrencies. I started working with it and ended up adding a transaction history tab as well so I could keep track of my trades and what the profit/loss is on those trades, as well as the value of my current holdings.
I included a calculator also so I could determine if buying an asset makes sense on a given market, given it's current BTC, USD, or BTS price.
You can have the spreadsheet for free! Just click here, then go to File -> Make a copy...
Here are some screenshots of it in action with some sample data filled in:
Prices & Totals
Transaction History
Calculator
I really hope you find this as useful as I do. Let me know if you have any suggestions to improve it!
Happy trading!
Updated: See my comment below for a couple new features I added today, the main one being a way to see the balances of your different wallets based on your transaction history. Thanks for your votes and encouraging words!
Great tool!
Maybe a bit late to the discussion, but I had been looking for the best way to do this for a while now. And even though this works perfectly I might found something easier which doesn't need you to do 'vlookup' etc.
This guy; https://jbuty.com/how-to-get-crypto-currencies-rates-and-more-in-google-sheet-1a57e571bc14 made an add-on which allows you to pull the rates directly from Coinmarketcap and different exchanges like Kraken. Made it a lot easier for me so I thought I should share..
I have never been a fan of Spreadsheets for tracking trades as it can become difficult over the time to enter them manually. You should check out Cointracking Portfolio Manager.
It has
For details of how to use these features checkout this post.
Very cool! The last time I looked around there wasn't one which had all the new currencies. I'll check it out.
Very cool! i upvote you both and followed! aweosme :)
YaY!
wow so nice both! very usefull!! just upvoting you ;)
Cool tool! Personally I use the portfolio tool on Cryptocompare
👉 https://www.cryptocompare.com/
You get the same features of this spreadsheet plus charts (candlestick too)
On my phone, my go to is the Blockfolio app
👉 http://blockfolio.com/
Doesn't it let you price things in BTS, USD, and BTC? I was a little hesitant to put all my crypto financials on the sites I found.
Yeah Cryptocompare does.
& as long as you don't post your private keys,
I think you will be good :D
Ah, yeah, this was the same site I was looking at before I created this spreadsheet. Seems nice, but the main thing I wanted to accomplish is to price things in BTS for when I'm using Open Ledger. I only see these currencies available:
Cool, I would like to see that too. We should both send feedback at the bottom and ask for BTS pricing. I already asked for another feature earlier today lol
I love being able to see a good candlestick chart, without having to log on to an exchange. Cryptocompare does that for me.
Heck yeah man! Regardless of the investment - a good spreadsheet makes analytics so much easier and efficient. I use one when analyzing real estate, will have to download this a track my crypto transactions. Thanks.
Cool! Let me know how it works out for you or if you have any questions or improvement suggestions.
Do you still utilize this spreadsheet?
Any updates in the past (crazy) two months?
Looking for a couple features:
Thanks for your work on this to date, btw.
Yes, I still use it, but the google spreadsheets API keeps getting rate limited, so it hasn't been working super well lately. I've been emailing with the developer of the API to see when they might get token-based requests working.
I don't have options to do exchange specific rating. Feel free to add it yourself in your own copy though.
"ID cost of trades"? Not sure what you mean about that. You mean the exchange fees? Yeah, that might be helpful, but it gets confusing to track with some exchanges, so I didn't want to bother with those details (though in previous spreadsheets, I have).
Again, not too interested in arbitrage opportunities via this spreadsheet. I think there are better tools out there for that like Cryptopia.
You are own bank - so true. This what I like about the altcoins you can start your own bank. Trade and lend. I use Poloniex for that. Thanks so much for sharing your spread sheet.
You're welcome!
Looks very useful! I'm not that much of an active trader, but I'm definitely bookmarking this for future reference.
Even if you're not an active trader, it's definitely good to keep track of what you own, where it is, when you bought it, and at what price. There's no monthly bank statement in crypto.
I've added a place to keep track of your current wallet balances:
And a place to keep track of the change in value over time:
(just copy the line above and use
paste -> Values Onlyon the line below)I'm including a change log also, so in the future, as I make changes, you should be able to just copy in the updated cells you want into your own copy of the spreadsheet.
Thanks for the download,would it be possible to add an average buy price on the wallet balances section & I'd love to see a EUR column,had a go at adding a euro column,but its above my skill level
The original spreadsheet had a mechanism for multiple currencies, including EUR. If you start with that, you might be able to get what you want. Building in average buy price is tricky as the spreadsheet doesn't currently take a snapshot of the price as the time the purchase was made but instead shows the price as it is right now. That would be tricky to keep track of snapshot data for all purchases.
Thanks,I'll check out the original & see what I can extract from it
Thanks for sharing, love the spreadsheet.
To get it to auto-update with fresh prices every minute, I went to File/Spreadsheet Settings, Calculation tab, set Recalculation to "On change and every minute."
Oh, I thought it wouldn't recalculate because the values were derived from a function call (with cached output) and the values to the function call have not changed. I thought that's why the original spreadsheet had the currency type as an input to the function to cause it to recalculate when the currency is changed.
You're right, I didn't test this well and it isn't working. Changing the value in L1 forces a refresh but I'm unable to use a now() or rand() function to trigger this because of restrictions on custom functions. If I have time later I'll look for another option for auto-refresh.
I found one earlier, but haven't implemented it yet. Essentially it talked about creating a function which returns rand() or now() and then using that output as a function input to the ImportJSON call. Then the trick is just triggering that function. Ideally a little "Refresh" button would do the trick, but I'm not sure if Google sheets supports anything like that.
Also, check the updates I added today. You can now see your wallet balances. :)
I got a refresh button working by adding this function to the code:
function increment() { SpreadsheetApp.getActiveSheet().getRange('L1').setValue(SpreadsheetApp.getActiveSheet().getRange('L1').getValue()+1); }Then drawing a button like this: http://stackoverflow.com/a/6966272
and attaching the increment script to it.
No luck yet on getting auto-refresh to work.
Made another tweak to my personal copy:
With this new function:
function takeSnapshot() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheets()[0]; var range = sheet.getRange("K2:L2"); var rowCount = SpreadsheetApp.getActiveSheet().getRange('SnapshotRow').getValue(); rowCount = rowCount+1; SpreadsheetApp.getActiveSheet().getRange('SnapshotRow').setValue(rowCount); range.copyValuesToRange(range.getGridId(), 11, 12, rowCount+2, rowCount+2); }with
SnapshotRowbeing M10. Every time the snapshot button is pressed, it copies the current values from K2:L2 and puts them in the portfolio value section.I'd love to add these to the main sheet, but if that triggers the script access stuff, then that's a non-starter, IMO.
Very cool! I made some changes on my personal copy with this:
function refreshJSON() { var d = new Date(); var currentTime = Utilities.formatDate(d, SpreadsheetApp.getActive().getSpreadsheetTimeZone(), "yyyy-MM-dd hh:mm a"); SpreadsheetApp.getActiveSheet().getRange('LastUpdated').setValue(currentTime); }(instead of L1, I'm using a named range "LastUpdated")
The button works great, but I learned you have to go to spreadsheet settings to set your timezone as you want it.
Here's the main problem with this approach:
I really don't like having to grant access to a script and I'm sure others won't either. :(
It won't let you use NOW() directly:
So... yeah. I'm thinking most people will probably just want to leave it as is instead of granting a script they didn't write access to their stuff.
Cool, I'll check out those changes.
I'm not sure if my last version required the script permissions or not because I'd already granted them when trying something else, but yeah, that was a "scary" permissions ask. I'd already read the code and wasn't worried about that, but I had to dig around Google's docs a bit to make sure I was only granting my copy of the script permissions and not the public copy.
I don't seem to be able to reply to you anymore - I guess there's a maximum reply depth on steemit? I'm new to the platform but really liking it so far.
Thanks for this man, I'm a total mess with my book-keeping lol, gonna look into it. In fact, I'm trying to find out the best solution to secure all my crypto stuff. Kinda scary if I wake up one day not remembering all the stuff that'll give me access to my own cryptos..
"Remember"? You shouldn't remember more than two passwords. Go get 1Password immediately. Password manager all the things. The only thing you should remember is your computer login and your 1Password login. Everything else should be auto-generated by 1Password, secured, and backed up.
Please, don't learn this lesson the hard way. Get a password manager. :)
Hmm I've always assumed that password managers can be exploited and end up being worse for me. So I never keep any passwords anywhere except on paper. The one time I actually saved a password was for Steemit last year and guess what, that was the first time someone gained keys to my account..
So I'm pretty paranoid about keeping stuff on the pc.
I'll look into 1Password!
I'd say on paper is much worse. If your computer is compromised, it doesn't matter where you store your passwords, a key logger will pick them up when you use them. Another benefit of a password manager is it avoids phishing attacks.
I wrote more about this here: https://steemit.com/steemit/@lukestokes/password-security-you-re-the-problem
Key security concepts to keep in mind:
Key loggers are the biggest risk on any platform. Despite the solid security model of Unix / Linux / Mac, if you manage to get infected with a key logger (typically you get infected through email or websites) NO schemes for password security will help, since the key logger will capture any passwords you use when you type them in.
I have been looking for reliable keylogger detector software for all 3 platforms I use (Windows, Mac & Linux) for the last 2 years and still have not found solutions for all 3 platforms yet.
I haven't looked at 1Password, I'm sure it's good if Luke endorses it, and there are many others to choose from. I myself use an encrypted USB stick. If my password to unlock the encryption of the USB key is captured by a keylogger, the attacker couldn't use that info if the usb key is not plugged in, and I don't leave it plugged in very long.
It's not a perfect system. If I fail to detect a keylogger, over time it would capture any passwords I used from the encrypted usb stick.
Response to Luke's comment below (6 level depth restriction):
Yes, I DO have 2 copies of the usb. A weakness of that is they are not always in sync with each other. Password managers like 1Password etc are very good, and I recommend you keep a backup in multiple locations. A cloud location is one solution, I prefer local myself, and although the chances of cracking a password vault left in a public location (dropbox, cloud storage etc) is extremely unlikely, I prefer to keep such sensitive data under my personal control / possession and thus eliminate any chances the vault can be accessed by anyone but me.
Many may not want to sacrifice the convenience of keeping such data available anywhere, I'm not one of them.
Interesting that you didn't say anything regarding keyloggers.
What happens if your USB stick fails? What if you lose it? Do you have multiple backups?
The reason I like 1Password is because the browser extensions also protect you from phishing attacks.
But I did:
LastPass is great for keeping track of passwords and private keys, at least :)
You can improve the security with 2FA, and you can also add your own layer of security on top of that.
Indeed! 2FA all the things with something like the Google Authenticator. Via text message used to be a thing, but now that's not recommended because spoofing mobile numbers is getting too easy.
What do you recommend instead?
Oh wait, you're saying 2FA with Google Authenticator (or similar) is good, but not the "2FA" that involves sending a text with code to mobile number. If I understand right, then yes, I agree.
Yep, sounds like you figured out my meaning.
I have been using CoinTracking for the past two years and all I can say are good things. One of the smartest customer support I've ever experienced. I've used about 4 other portfolio tracking, and none come close to what CoinTracking offers, usability and design is great.
The companion app is amazing as well. Now I can quickly see a snapshot of all my investments, regardless of where they are located, in one easy to use app.
Although the free version is good, import each CSV file from each exchange is time consuming, with the PRO version all your transactions are up to date with one time setup.
You can use my affiliate link which gives you 10% discount.
Hope this helps!
Awesome post! Thanks for sharing.
Greetings, @lukestokes! Engaging subject. Thanks!
Are you a bot? All your comments seem the same.
@lukestokes is there any way to represent sells in your spreadsheet? I tried setting the amount to a negative number on the transaction sheet, but it doesn't look quite right. thanks!
Yes, negative numbers are exactly how I do it. Example:
I then add a corresponding bitcoin entry:
(click on the images to see the full versions)
Hi Luke - this is awesome. I had built my own using google spreadsheets and the CRYPTOFINANCE plugin, but looking at yours I want to create a hybrid, starting with yours first. I've got live graphs to graphically show the exposure and a daily snapshot script running to capture the history and produce a graph of the portfolio. I'll happily share once I've finished it. However, one thing I can't figure out is how to change the three main currencies yours is based in: BTC USD and BTS. I want to change BTS to GBP as I buy crypto in BTC, GBP, USD and sometimes EUR. Specifically, when I change transaction history tab Columne F, to GBP I get this error 'Invalid: Input must be an item on specified list', but I can't figure out how to change the list. Many thanks.
Luke, don't worry I figured it out. I'm gonna try and make it so that anyone can change the three main anchor currencies you've created it with dynamic. I'll share it with you if you're interested. Thanks again for your great work!
Hi Luke - do you ever get a VALUE error in your spreadsheet? I keep getting this error every couple of days, but then if I go back in version history a day it's ok. But then it randomly reoccurs. I haven't changed anything in this part of the sheet you built. I'm wondering if just happens because it's making a call to look up the prices and maybe the API on cryptocompare.com is overwhelmed?
Thanks
Unfortunately the IP for Google spreadsheets gets rate limited by the API provider. I've been in dialogue with them hoping they'll implement a token-based solution so that each user's requests could be attributed to them directly instead of Google's IPs, but that hasn't happened yet.
My husband would be ALL OVER this... sharing!
Thanks!
Oh wow thanks for posting about this it was really useful.
Great! Glad to hear you got to try it out. Let me know if you have any questions or suggestions for improvement.
Will do.
Especially if you are self-employed personal-accounting of all transactions is perhaps one of the best advice to give regarding personal finance. Since I'm a coder I might put together my own personal system of doing this type of accounting. Thanks for the awesome advice!
~Np
Sounds like a plan! This one saved me a lot of time because of the spreadsheet I started with which already had scripts for pulling in the rates via JSON.
I upvoted your post with whale power! Thanks to @htooms and #takemyvotes!
https://steemit.com/takemyvotes/@htooms/i-m-not-going-to-upvote-you-do-it-yourself-2
Follow our blogs @digicrypt and @htooms
Keep up the great work and keep Steeming!
Very cool! I am trying to learn more about trading, I want to do it the right way.
Thank you! :)
Really cool Luke, thanks for sharing!
One improvement that could be great would be to see the historical prices of these currencies, that's useful for analysing when to buy & sell.
One way to add this new tab would be to use this JSON importer :
https://github.com/pipaman/google-docs/blob/master/scripts/ImportJSON/Code.gs
and getting the historical data from Coincap for example:
http://www.coincap.io/history/365day/BTC
It already has the JSON importer to get the current rates. It wouldn't be too difficult to take a snapshot and save that off. I've modified my personal copy to have a button to refresh the rates and another button to take a snapshot of my holdings and save them off so I can see the change over time. I don't want to add those to the main spreadsheet though because once I added the button it asked me give the spreadsheet permissions to my account which I'm guessing most people will not prefer.
Got it, thank you!
I'm horrible with spreadsheet formulas ... would you consider a consulting fee for helping me set mine up?
What is it you want set up?
Thanks a lot.
Perfect !
You're welcome!
So cool, thanks for sharing!
You're very welcome!
Hey Luke,
I've been using your spreadsheet now for a few days and really love it.
One question - is there a way to automatically update your balance when you making purchases. For instance I recently purchased some OMG with my BTC - but I can't figure out how to reduce my assest amount of BTC as per the new purchase.
Any help would be great.
Thanks!
Glad you find it useful! I simply add two entries for every transaction. One positive, one negative. Because exchange fees are involved as well, it's helpful to subtract the actual amount spent, not just the calculated amount based on the rate purchased. The negative entry for BTC I put in with a zero rate. I hope that helps.
I just found this now and love it! Thanks Luke !
Glad you found it, and you're welcome!
Hey Luke, you gave us a great tool and I am using it every day! Thank you so much!
I was wondering, is there a way to put a transaction in the transaction history that is made with a currency other of BTC,USD,BTS?
For example, I have ETH and I bought with that IOTA, I want to put that in my transaction but I can't at the moment.
Thank you again!
You're welcome! In my own copy, I ended up adding ETH later as another currency pair. It takes a bit of work, but if you look how the other currencies are done, you can add ETH without too much trouble. Maybe eventually I'll get around to adding it to the base copy as well.
That was what I was trying to do but I don't have such an experience with spreadsheets and I couldn't find where I have to click to review the code behind the other currencies. Is it easy to direct me on this?
I don't have time at the moment, but I'll ping you once I get around to it.
nice post man!! so usefull for the beginers :)
Hi @lukestokes, the Google Sheet plugin has suddenly stopped working and is showing the following error message, any idea what could be causing this and how to fix it? Thanks a lot.
That doesn't look like the spreadsheet I built. I don't have a function called CRYPTOFINANCE in my spreadsheet.
@lukestokes
Thank you so much for this. Can we pull live data from poloniex?
You're welcome. It pulls live data from cryptocompare.com
Your spreadsheet was the inspiration for creating my own. Check out this link if interested; happens to be my first post as well :) Created this spreadsheet with taxes in mind, so it currently calculates unrealized/realized gains/losses based on FIFO accounting rules.
sehr interessant, danke =
Anyone looking for a truly robust solution for importing cryptocurrencies, stocks, bonds, and essentially all other all source financial data into Excel should check out XLQ -- more at:
https://steemit.com/cryptocurrencies/@mckibbinusa/new-version-xlq-6-0-now-supports-cryptocurrencies
Thanks @lukestokes. I had tried both Altpocket.io and my own spreadsheet creation but I liked the direction yours went with it better and tweaked it to match my needs. Thanks for sharing!
Being a self admitted 'not the sharpest knife in the drawer' person, tried this spreadsheet out. Not my forte. Couldn't figure out what goes where, et al. Gave up. Going back to using the bits of paper I have all over my desk. :(
Would a video help? My thought on this is if it’s not obvious for spreadsheet users, it’s probably not the right tool for them.
I too am a beginner, a video would be awesome...I can't figure out anything on this...Thanks rhmgg
Hi Luke,
I put your post on my todo-list :) !
Could I also add a request, related to why your post ended up my todo-list?
Actually I just posted about an excel overview of which coins are traded on which exchanges:
https://steemit.com/cryptocurrency/@hasse72/drafting-a-missing-crypto-overview-which-coins-on-which-exchanges
It might be fun to power this file up with data feeds using API's just like you and others did too...if the API's limitations allow it... I'm afraid it won't be so easy as it requires more than the most common coin info requests,...
If you have any suggestions to add... It would be really appreciated :-) !
Further, I just did a feature proposal at Delta (which is about to support exchange API's too)...to let users choose their reference currency (from a list including besides BTC also BitShares, NEO, XLM,...)... hoping this will make the app even more useful for you and others too (!?)... They are usually very responsive to questions and requests...so...curious about what they'll say :-).
Thanks for this fantastic spreadsheet, and your prompt answer to my question on Twitter (while awaiting my SteemIt account to be approved).
You're welcome. :)
I guess, its a good starting POINT, but as usual, I am so grateful to be on STEEMIT, what a great TECH and site, always finding helpful tips here......GO Team STEEM
@LukeStokes can you confirm if the Google Sheets code still works? I notice it hasn't been updating for the last week or so. Before I bother troubleshooting, want to be sure it is expected to work. Thanks.