※ Download: Cryptocurrency excel spreadsheet
While this is not an apples-to-apples comparison, cryptocurrency exchange consolidation is a natural market force that will happen eventually. Having spent the first 10 years of my career in corporate settings with both large and small companies, I learned a lot about the accounting process, managing accounting departments, financial reporting, external reporting to board of directors and the Securities and Exchange Commission, and working with external auditors. The abundance of choices in exchanges presents a multitude of problems, one of which is a large distribution of prices across all platforms.
Thanks Hi Luke, I put your post on my todo-list :! But what is a cryptocurrency? Hey Luke, I've been using your spreadsheet now for a few days and really love it.
- If my computer ever crashes, I can restore 1Password's password file from dropbox. In the time it takes to move a currency from one exchange to the next, the price could have rapidly moved against you.
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 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, which Pablo Yabo created just a few days ago. It's pretty awesome in that it pulls live rates for various cryptocurrencies. 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! Let me know if you have any suggestions to improve it! 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! 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; 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. For details of how to use these features checkout. Do you still utilize this spreadsheet? Any updates in the past crazy two months? 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. 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. 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. 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. 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. Also, check the updates I added today. You can now see your wallet balances. No luck yet on getting auto-refresh to work. 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. 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. 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. Cool, I'll check out those changes. 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. 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. 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. Don't just have it installed, but verify updates are happening regularly. 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. 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. 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. 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 Hope this helps! Really cool Luke, thanks for sharing! One way to add this new tab would be to use this JSON importer : and getting the historical data from Coincap for example: 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. 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. 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. 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. Thanks 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: It might be fun to power this file up with data feeds using API's just like you and others did too... 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... They are usually very responsive to questions and requests... 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. 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.
Since we already determined some of the link outreach strategy, part of the job would consist out of contacting a list of predetermined targets. Anja Helloreally appreciate you providing this for all to use. You can now see your wallet balances. I myself use an encrypted USB stick. Password manager all the things. Let me know if you have any suggestions to improve it. Maybe a bit late to the discussion, but I had been looking for the best way to do this for a while now. This VBA-powered spreadsheet give you live exchange rates for Bitcoin, Etherium, Monero, Dogecoin and 1500 other cryptocurrencies. 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. Thanks for your votes and encouraging words. Intra-day with 5 minute interval, daily, weekly and monthly. These transactions are then recorded on what is known as the blockchain, which documents all exchanges of currency, enabling it to keep record cryptocurrency excel spreadsheet what Bitcoin belongs to whom.
