Free Crypto Portfolio Tracker based on Google Sheets
tl;dr… It retrieves fiat values (USD, EUR, …) automatically and generates reports, while giving you all flexibility of sheets that you need. For free. Check out a great fork which is maintained here! Or Download my old discontinued version here. Please share this article if you like the tracker.
DEVELOPMENT DISCONTINUED! Version 2 is cancelled.
See this fork instead.Current version: 1.0.2 (released 12.03.2020)
Follow on Twitter for updates, tips and appreciation.
Intro
There are endless (crypto) portfolio tracker to manage trades and portfolio. Some of them are only available as mobile app, others as Web application. Some require to enter trades manually, others can import them ,often requiring an API key and paying a good amount of money.
When you start using the automatic import functionality of these portfolio tracker, you can track your trades easily as long as they aren’t complex trades. However if you participate in ICOs, have your own wallets or use smart contracts, the automatic import doesn’t work any longer. So far, existing portfolio trackers can’t import or calculate these transactions automatically, a lot of the imported transactions need to be corrected or otherwise lead to false data.
I personally prefer the flexibility that data analysis provides. The manual data check, which happens during the import/export procedure, is especially useful in spotting false data. In order to avoid adding a lot of trades manually or giving my API keys to random services, I decided to create my own crypto tracker based on Google Sheets. This allows to modify and analyse the data with pivots, graphs, formulas and Javascript to suit the traders needs.
Features
- Dashboard-View
- Stores unlimited transactions (trades, withdrawals, …)
- Retrieves automatically the fiat value for crypto currencies (historical and current)
- Creates a coin performance report (including the average cost and the current value of all assets)
- Creates a coin location report of your accounts and wallets
- Creates a profit / loss report (based on the invested fiat); so you know how much is left of your hard earned money
- Stores most fixed values in adjustable settings (e.g. main fiat currency, proxy url)
- Allows to define aliases which maps your currency code to the one used by the market-data service (to retrieve the correct price e.g. XBT for BTC)
- Allows to define default fallback exchange rates for coins that are not listed yet (for example of your ICOs). After listing on the market-data service, it will retrieve the current price instead.
Advantages
- Additional import helper available (Bitstamp, Coinbase Pro/GDAX, Binance). Download here.
- Use of all Excel-like functionality within your portfolio and reports (e.g. filter, visualizations, pivot tables, …)
- Data is stored on your drive only, nobody else has access to it
- Open source (you can dive in the source-code and change it)
- Faster to enter manually than using entry masks (also easy to copy&paste)
- Take advantage of all other existing extensions for Google Sheets
- Import of any data for transforming (e.g. CSV import, intelligent paste functionality of Google Docs, …)
- Freedom to change anything according your needs (add new columns, change the code, use search & replace, …)
- Create snapshots/backups by simply duplicate the Google Sheets file
- Copy&paste compatible with lots of applications
- Possibility to change the fiat value for each transaction/trade individually
How to use
- Save the Free Crypto Portfolio Tracker to your drive (you need a Google account to download the Google Sheet to your personal Google Drive)
UPDATE: Better download this fork (actively maintained) - Open it (you will see some demo data that should be deleted before starting with your own data)
- API Key: Get a free CryptoCompare API key (see here) and store it in your sheet in Settings > API KEY: CryptoCompare (same column as “USD”!).
- Authorize the script component “CryptoHelper”. Simply click on Cryptocurrency > Add fiat rates in the menu, which triggers the script functionality, then follow the instructions to allow the execution of the embedded script (you can review and edit the source code any time).
Note: Don’t panic if you receive a notification email by Google, letting you know that CryptoHelper got access to your account. Google is taking security notifications very serious, nobody except yourself has access to your data! - Retrieve fiat values: Click in the menu on Cryptocurrency > Add fiat rates and confirm the dialog to fill the gray columns for fiat values in your Trades sheet.
- Generate standard reports: After retrieving the fiat values of all your trades (Add fiat rates) and setting the sort order to “Date Z-A”, click on Cryptocurrency > Update portfolio value.
- Follow on Twitter for updates and tips (and show your appreciation) and feel free to share your experience.
Important
- Don’t delete the default sheets or change their names, but you can add your own ones
- Don’t modify the reports (sheets with a lock symbol), they will be overwritten each time you generate them
- Always sort the Trades sheet by “Date Z-A” before generating reports!
Trades sheet
You need to enter/paste all your trades in this sheet. It is suggested to create new trades on top, as you need to sort them “newest first” (Date Z-A), before generating the reports. To add new entries on top, simply right click on the row number and select “Insert 1 above”. If you select multiple rows, you can insert more than one new row at the same time, which is important to paste values (=new trades). If you add columns this way, all formatting and data validation (=dropdowns) will be inserted, too. You only need to copy the formulas for the columns Exchange and Wallet manually. See the Binance import example (chapter “Best practice with example Binance”) below for more information.
Just a technical note: Using an array formula to avoid copying the formula manually resulted in poor performance.
Columns
- Date: Google Sheets recognizes lot’s of different date formats, you may want to use the format painter to display all dates in the same format.
- Type: The type of your transaction. The existing types are described further down in this article.
- Buy: Amount that you bought
- Currency (buy): Currency that you bought (USD, BTC, …)
- Fiat value (buy): Fiat value of the bought amount at the transaction day. Filled automatically.
- Sell: Amount that you sold
- Currency (sell): Currency that you sold (USD, BTC, …)
- Fiat value (sell): Fiat value of the sold amount at the transaction day. Filled automatically.
- Fee: Amount of fee
- Currency (fee): Currency of fee (USD, BTC, …)
- Fiat value (fee): Fiat value of the fee amount at the day of transaction. Filled automatically.
- Exchange: “Read-Only” field (contains a formula to show the exchange/technology of the account that you select)
- Wallet: “Read-Only” field (contains a formula to show the wallet of the account you select)
- Account: The account which the transaction belongs to (needs to be specified in Account Settings sheet). Will cause filling Exchange and Wallet-column automatically (if you copy the formula).
- Transfer code: Here you can store the transaction hash, if available. So you can later easily check/confirm the trades.
- Comment: Here you can enter any information. It’s good practice to comment some special trades or “workaround-trades”.
Types
Types are colored automatically using conditional formatting. They can be maintained in the Settings sheet. You can add your own but don’t delete or rename existing types, as some are used hard-coded in the report generation.
- Withdraw (Sell): If you send assets to another wallet belonging to yourself. There should be always a Deposit transaction following this type if it’s a transfer (and not going back to your non-tracked bank account).
- Deposit (Buy): If you receive assets on your account. There must be a previous withdraw transaction, if it’s not a fiat transfer from your bank account. All non-transfer and initial deposits without prior withdraw must be done in your fiat currency to ensure proper reporting.
- Trade (Buy+Sell): For all trades on Exchanges. You can also use it if you participate in ICO’s for the token exchange (e.g. ETH for XYZ).
- Loss (Sell): Loss of your assets. For example if you sent it to a wrong address, someone scammed you or you simply can’t remember where it went to.
- Mining (Buy): If you got coins because of mining or staking activities.
- Airdrop (Buy): Free coins. Who doesn’t like it?
- Dividend (Buy): If you got assets for simply holding something.
- Expense (Sell): If you used assets to buy something.
- Correction (Buy or Sell): To fix wrong values in the report. Should be only very small amounts less than 10 USD. Otherwise you need to go over your books and find out what is causing the wrong values.
- Gift (Sell): If you give someone something (for example you send coins as a gift to a friend).
Important: Fill always Buy and Sell amount+symbol for the type Trade !! But don’t fill both for all other types.
Fees
Fees are always additional to the sell amount. Which means e.g. if you want to buy BTC (1 BTC = 6,032.10 USD) with 0.25% fee while having 5,000.00 USD on your account:
- Buy in BTC: 0.8268264
- Sell in USD: 4,987.50
- Fee in USD: 12.50
Same principle applies for sending transactions with transaction fees (like for Ethereum and ERC-20 tokens). In case of transactions (Withdraw and Deposit), the selling and buying amount should be the same:
- Withdraw: Selling= 1 ETH, Fee = 0.0002 ETH (account balance is subtracted by 1.0002 ETH)
- Deposit: Buying = 1 ETH
Currencies
You should use the main common currency codes (Bitcoin: BTC, Ethereum: ETH, Litecoin: LTC, Stellar: XLM, EOS: EOS, etc.), otherwise you need to add aliases for all the coins in the sheet Coin settings.
Best practices
For ICO (Initial Coin Offering) / STO (Security Token Offering) / etc.:
If you participate in any of these offerings, you can track them in two ways, depending on the distribution of coins:
- Advanced (5 rows in your Trade sheet): You create a new account (in Accounts sheet) for this coin/token offering (like you would own a wallet there). Then you track it in the Trades sheet as you would send (=withdraw+deposit) the tokens from your existing wallet to this new wallet. Afterwards you create a trade for the amount of tokens that you get for your coins. At the end you send them back to your own wallet (=withdraw+deposit).
- Simple (1 row in your Trade sheet): You simply create a new trade (Trades sheet) for your sending wallet and put the transaction costs in the fee column.. But this will have the disadvantage that it’s not a correct presentation of the reality and the date/time will not fit with to the wallets transaction logs on the distributed ledger.
Coin settings sheet
This sheet needs some attention if you wish to use your own crypto-currency symbols or like to add default fallback exchange rates (e.g. if your coin is not listed yet and you can’t believe that it’s value has already gone zero ;-) ).
- Currency: Your currency symbol/identifier in your Trades sheet and reports
- Name: Just for your information (to identify the currency)
- FallbackRateFiat: The fiat value of this coin, in case it’s not found on the exchange rate service(s) like CryptoCompare.
- CryptoCompare: Add here the identifier of the coin that is used on CryptoCompare.com, if it differs to your currency symbol (otherwise you won’t receive correct prices).
Accounts sheet
This sheet will add new accounts to your Trades sheet. The column Display can’t be edited, its a formula. Enter your Wallet-Name and choose Exchange/Technology (maintained in Settings sheet). To safe you from some headache, you may also want to enter your wallet address for later research in your own transactions.
You should create one account row per wallet, especially on all different kinds of Distributed Ledger Technologies. Regardless how many different assets you store in this wallet (e.g. in Ethereum wallets).
Best practice for exchanges would be to create one account row per user per exchange (the Wallet-Name will be your username if you own multiple accounts on that exchange). “For ICO (Initial Coin Offering) / STO (Security Token Offering) / etc.” please see the chapter (with exactly this name) above.
Settings sheet
The most important basic setting is your main fiat currency in Display currency. So far it’s tested with EUR and USD, but it should also work with other currencies. If you have issues, please let me know.
For the Techup PHP-Proxy fields, please see the chapter “Avoid rate limit issues” further below. The Types are described in chapter “Trades sheet” > “Types”. You can add new types but please do not delete or rename them. Exchanges/Technology is used for the accounts and you should add all exchanges and DLT (ETH, BTC, XLM, NEO, DOGE, EOS, …) you are using. If a DLT allows multiple kinds of tokens/coins you just need to add the technology, for example Ethereum for all ERC-20 tokens out there.
UPDATE: You need to add a (free) CryptoCompare key now, it is not possible to use the service any longer without a free API key.
How to import trade data
If you want to import data from exchanges like Binance, KuCoin, Huobi, etc., I highly recommend to add a new sheet Imports, which stores the last import date and time for each exchange/account (entered manually after each manual import processing). This way you have more control about the actuality of your trades and avoid double entries (on import).
In general you can import most data easily by using Excel/Sheet formulas (for example string splittings) and search&replace functionality. After you have created your import processing sheet (see Binance example below), it’s just a matter of 2 minutes to execute any additional import into the Trades sheet manually.
Download an import helper (containing binance) here. It includes the import overview sheet and the import processing sheet for binance and bitstamp. For more details see next chapter “Best practice with example Binance” . Contributions of new import sheets are welcome in order to speed it up for everyone.
If your exchange doesn’t offer a proper export format, you can simply copy&paste the HTML/trade data to temporary or pre-made import sheets and process the data within these sheets. Important: Never paste them directly into your Trades sheet!
Best practice with example Binance
First choose your desired trades and export them to Excel (xls) format.
Download the file and either open it on Google Drive or in your local Excel (or any Open Source Office that can read this format).
Now add columns for the most important header fields of the Trades sheet in the right order, so we can copy&paste them later easily:
- Date and time (in a format Google Sheets can understand)
- Type (containing only a fixed value “Trade”)
- Buy amount
- Buy currency
- Placeholder column (fiat value of buy amount)
- Sell amount
- Sell currency
- Placeholder column (fiat value of sell amount)
- Fee amount
- Fee currency
Then we need to fill these columns with formulas (except of the fixed value “Trade” as type for all entries). The following formulas are an example for row two:
- C “Buy”:
=IF(L2="BUY";N2;O2) - D “Buy currency”:
=IF(L2="BUY";LEFT(K2;3);RIGHT(K2;3)) - F “Sell”:
=IF(L2="BUY";O2;N2) - G “Sell currency”:
=IF(L2="BUY";RIGHT(K2;3);LEFT(K2;3)) - I “Fee”:
=P2 - J “Fee currency”:
=Q2
We will leave the fiat values empty, as they will be filled by our automated scripts, but we need the columns to paste the data properly into our Trades sheet.
To copy this formula from row 2 to all others, select all cells (B2 to J2) and drag it down to the last column. Next step is to add new rows in the same (or higher) number as our new trades count into our Trades sheet. Afterwards simply copy the trades into the created empty rows by using Paste special -> only values. Once you have created an import sheet for your exchange, you can reuse it. Then an import will just cost you around 2 minutes. If you want to share your exchange import sheet (with formulas), feel free to do so and let me know. You can download an import helper (containing binance and bitstamp) here.
Review and change source code
You can review (there is no backdoor!) and change the code in Tools > Script Editor, which does the magical work for you (retrieving exchange rates and creating reports).
Upgrade to a new sheet version
After a new release you can copy&paste the content of your Trades-sheet and your modified values in Coin settings-, Accounts-, and Settings-sheet to the new released sheet. The effort to upgrade should be max. 5 minutes.
UPDATE: The source code is now here on Github for easier versioning and collaboration.
Avoid rate limit issues
As the embedded script is executed on Google servers, it is sharing its IP address with other Google Apps Scripts. So if you want to access an API which has limits for the IP, this could lead to accessibility issues (or even bans for the IP address). Therefore you will find a small (protected) PHP “proxy” script on Github here. You can run it on any web server that will route your request to your desired service and you don’t need to worry about any IP related problems.
You can simply set it up by entering your (https!) URL (including the script filename, by default proxy.php) and your token.
Notes
- Indeed, the “proxy” could be more secure, but for normal use it is secure enough (don’t publish your proxy URL and token anywhere!).
- If changes are not applied when running the menu actions, please wait 10 minutes (expiration time of cache for settings).
Known limitations
- Prices are taken only from cryptocompare.com. Coinpaprika.com, a new free very interesting API, may be implemented soon.
- Historical prices from CryptoCompare are aggregated only per hour (not per minute). This means that the fiat values may differ from your expected “real” price of the trade-moment.
- The value of AverageCoinPrice in the report Portfolio values may be misleading as the fiat value of the coin/token could have changed during trades because of price changes.
Example: You buy 1 coin XYZ for USD 200 (you payed USD 200). After that the price of XYZ rises and you sell it for Bitcoin (worth 210 USD). Now Bitcoin rises (but also XYZ) and your Bitcoin purchase is worth 300 USD. Next you use it to buy again 1 coin XYZ. The report will pick up your invested 200 USD first, then your sell for 210 USD (USD 200/1 coin minus USD 210/1 coin = USD -10). Next your buy worth 300 USD is added to the calculation (USD -10 + USD 300/1 coin = USD 290). The average coin price for 1 coin is then 290 USD for this report (even though you invested only USD 200 from your bank account).
UPDATE: Please see also Troubleshooting → “I want to have more realistic results for my portfolio values for my mining and airdrops” - Calculation made with double-precision 64-bit format IEEE 754 values (means not 100% accurate, see https://developer.mozilla.org/en-US/docs/Web/JavaScript/A_re-introduction_to_JavaScript#Numbers)
- Reports are fixed calculated values, formulas were not used to make it more dynamic (e.g. if you change your coin count in the report, the fiat value would not increase).
- No input masks/wizards to simplify to enter data (e.g. you need to add two rows for each wallet transfer manually) or to validate your input data.
- If you change an asset value that has already a fiat value, you need to delete the existing fiat value. Otherwise the script will calculate with the wrong old fiat value.
Contribution & support
Please let me know if you need more features or some help. Code/sheet improvements are very welcome (please see Github for the code part)! I will add them after a review to the sheet and mention you with name and URL.
If you have still issues after reading the whole article and checking the troubleshooting section, please write an email to support at techupbusiness.com .
Donations are also welcome:
- BTC: 3ECGVjb9GMbBf5UfKbSyriHSga851r7xMc
- ETH/ERC20: 0x7434B3Fcc0264F6EA173c6135fDeF63286Ee1f97
Troubleshooting
“Invested sum is wrong in profit overview”
All fiat deposits and fiat withdraws are summarized and represent your current invested fiat value. Please don’t deposit/withdraw any currency other than your fiat currency. If you wan’t to add them, you need two virtual trades as a workaround to represent your asset deposit/withdraw:
1) Deposit/withdraw fiat value of your asset
2) Trade your asset for the fiat amount
“The coin prices and/or values are wrong”
You should go through your trades row by row and check all rows related to your “wrong” asset symbol. Make sure to always fill Buy and Sell side for all rows of type “Trade”.
“Fiat rates does not appear when I click on Add fiat rates“
Probably you forgot to add the CryptoCompare API key or the currency you are using is not known to CryptoCompare. Make sure you added a free CryptoCompare key (you may need to register first for the free plan) in your Settings sheet. If the currency is not know, please check the Coin settings sheet and add a default exchange rate or alternative CryptoCompare name.
“A specific fiat rate is not loaded”
Please check on CryptoCompare for the id of your asset. You can find the ID by using the normal search in the results list or extract it from the URL of the assets detail page. If you want to use your own asset id in your trades sheet, add it to the Coin settings sheet (see the chapter Coin settings sheet in this article).
“Date sorting does not work properly”
You probably have “dates” in these cells that are not parsed as dates by Google Sheets. If you can’t sort them you probably need to fix them first.
“I want to use more than one fiat currency”
Right now the sheet only allows one fiat currency (e.g. USD or EUR) for the profit report as “base”. Means, if you have an USD and an EUR bank account and send the money to exchanges, one of these currencies will not be recognized as fiat deposit within the report. Assuming your “main” display currency is EUR, a workaround would be to add two more trades to your USD deposit: First you deposit the value of the already tracked USD in EUR to the same wallet. Next you exchange it against zero, using the Correction type. This way the report tracks your deposit in EUR as investment in the main fiat value but doesn’t consider it later.
“Changing the proxy URL in the settings have no effect”
This can happen, because settings are cached after they are read. Please wait 10 minutes, then the cache will be cleared.
“I want to have more realistic results for my portfolio values for my mining and airdrops”
First make sure that you upgraded to v1.0.1. Then the following will work…
The resulting portfolio values are not really correct, because the costs for 1 BTC may be zero (Airdrop) or something else (Mining = electricity costs). This portfolio tracker is not considering the pricing history of each asset amount automatically. It would require deeper changes in the code. Only if you vote for it in the comments, this may come one day.
But… there is a manual workaround already available:
For Airdrops: Simply set the fiat values of the “free” tokes to zero (for all transactions that are related to these “free” mined coins). And you will get a profit of 100%.
For Mining: To put your electricity costs into calculation, set your actual costs manually for all transactions that are related to these mined coins.
Links
- Thanks to cryptobulls.info for the video review: https://www.youtube.com/watch?v=mXkG4fIuz8o
Changelog
1.0.0 Initial version (Github)
- Contains already most functionality described as described in this article :-)
1.0.1 Small bug-fix / behavior change (Github, Changes)
- Removed hard-coded “Eur” from “Portfolio Values” sheet (use fiat currency from settings instead for the column name)
- Changed the calculation of PayedCoinPriceTotalFiat (includes now also Mining, Dividend, etc.) — more information
- Fixed a potential show-stopper bug for future releases
