The Cryptocurrency Bank Spreadsheet

nickg (35) 8 years ago

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

$21.98

30 votes

cointracking (49) 7 years ago

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

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

For details of how to use these features checkout this post.

$0.00

lukestokes (75) 8 years ago

Very cool! The last time I looked around there wasn't one which had all the new currencies. I'll check it out.

$0.00

5 votes

andreolf (53) 7 years ago

Very cool! i upvote you both and followed! aweosme :)

$0.00

1 vote

zackcovell (43) 7 years ago

YaY!

$0.00

cryptoblock (25) 7 years ago

wow so nice both! very usefull!! just upvoting you ;)

$0.00

1 vote

slickwilly (66) 8 years ago

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/

$0.42

lukestokes (75) 8 years ago

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.

$0.00

slickwilly (66) 8 years ago

Yeah Cryptocompare does.
& as long as you don't post your private keys,
I think you will be good :D

$0.00

lukestokes (75) 8 years ago

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:

$0.00

slickwilly (66) 8 years ago

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.

$0.47

scaredycatguide (73) 8 years ago

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.

$0.35

lukestokes (75) 8 years ago

Cool! Let me know how it works out for you or if you have any questions or improvement suggestions.

$0.00

wiredless (25) 7 years ago

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.

$0.00

lukestokes (75) 7 years ago

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.

$0.00

steempowerwhale (59) 8 years ago

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.

6 votes

lukestokes (75) 8 years ago

You're welcome!

$0.00

denmarkguy (76) 8 years ago

Looks very useful! I'm not that much of an active trader, but I'm definitely bookmarking this for future reference.

4 votes

lukestokes (75) 8 years ago

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.

$0.00

lukestokes (75) 8 years ago

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.

6 votes

lup (25) 8 years ago

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

$0.00

lukestokes (75) 8 years ago

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.

$0.00

lup (25) 8 years ago

Thanks,I'll check out the original & see what I can extract from it

$0.00

slomo (40) 8 years ago

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

3 votes

lukestokes (75) 8 years ago

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.

$0.00

slomo (40) 8 years ago

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.

4 votes

lukestokes (75) 8 years ago

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

$0.00

slomo (40) 8 years ago

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.

$0.09

4 votes

lukestokes (75) 8 years ago

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.

$0.00

lukestokes (75) 8 years ago (edited)

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.

$0.00

slomo (40) 8 years ago

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.

$0.00

kevinwong (76) 8 years ago

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

$0.25

lukestokes (75) 8 years ago

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

$0.65

3 votes

kevinwong (76) 8 years ago

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!

$0.00

lukestokes (75) 8 years ago

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.

$2.96

9 votes

full-steem-ahead (65) 8 years ago (edited)

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.

$0.00

lukestokes (75) 8 years ago

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.

$0.00

lukestokes (75) 8 years ago

Interesting that you didn't say anything regarding keyloggers.

But I did:

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

$0.00

wiser (58) 8 years ago

LastPass is great for keeping track of passwords and private keys, at least :)

$0.00

wiser (58) 8 years ago

You can improve the security with 2FA, and you can also add your own layer of security on top of that.

$0.44

lukestokes (75) 8 years ago

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.

$0.00

wiser (58) 8 years ago

What do you recommend instead?

$0.00

wiser (58) 8 years ago

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.

$0.00

lukestokes (75) 8 years ago

Yep, sounds like you figured out my meaning.

$0.00

manuelcompres (25) 7 years ago

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!

$0.00