The Cryptocurrency Bank Spreadsheet

in #crypto7 years ago (edited)

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!


Luke Stokes is a father, husband, business owner, programmer, voluntaryist, and blockchain enthusiast. He wants to help create a world we all want to live in.

Sort:  

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

  1. Automatic import of trades through APIs.
  2. Average purchase and sale price reports.
  3. Booked and unbooked profits.
  4. Ability to calculate your taxes.
  5. Set up price notifications.

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

Loading...

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:

  • An option to track tokens/coins by exchange, not by "aggregated" summary (fyi - I use cryptocompare but just this morning, I've been able to sell at an exchange (U.S.) at 15% higher than the cryptocompare stated value. I swing trade as well as hodl so I need "hot" tracking.
  • a column to ID cost of trades
  • a column that correlates the variable price of BTC on the site I choose to the "at the moment" price I buy/sell. (cryptocompare isn't accurate in assessing specific exchange values)

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 Only on 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 SnapshotRow being 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:

  • Always be on the latest security updates for your operating system
  • Always run an up-to-date antivirus software. Don't just have it installed, but verify updates are happening regularly.
  • Always use a secure password manager. I like 1Password because it stores the encrypted password database on dropbox so backups happen automatically. If my computer ever crashes, I can restore 1Password's password file from dropbox.

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.

Interesting that you didn't say anything regarding keyloggers.

But I did:

a key logger will pick them up when you use them.

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.

Coin Marketplace

STEEM 0.30
TRX 0.12
JST 0.036
BTC 66992.58
ETH 3460.53
USDT 1.00
SBD 4.68