- 1 Free Crypto Portfolio Tracker based on Google Sheets
- 2 Intro
- 3 How to use
- 4 Trades sheet
- 5 Coin settings sheet
- 6 Accounts sheet
- 7 Settings sheet
- 8 How to import trade data
- 9 Review and change source code
- 10 Upgrade to a new sheet version
- 11 Avoid rate limit issues
- 12 Known limitations
- 13 Contribution & support
- 14 Troubleshooting
- 15 Links
- 16 Changelog
Free Crypto Portfolio Tracker based on Google Sheets
Trades overview tl;dr… It retrieves decree values ( USD, EUR, … ) mechanically and generates reports, while giving you all tractability of sheets that you need. For free. download here. Please contribution this article if you like the tracker .
NEW VERSION 2.0 IN DEVELOPMENT.
Trades will be compatible with v1. It offers pivot tables, very fondness diagrams, marketplace and sentiment overview, tradingview consolidation and much more.
If you want to become examiner, write an e-mail to “ support at techupbusiness.com ”.
UPDATE : v2.0 Development is paused for the consequence. If person would like to continue the development, please contact me. Current version: 1.0.2 (released 12.03.2020)
Follow on Twitter for updates, tips and appreciation.
- 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.
- 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)
- 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.
- 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!
If you select some rows, you are able to create/insert multiple empty entries You need to enter/paste all your trades in this sheet. It is suggested to create newfangled trades on top, as you need to sort them “ newest first ” ( Date Z-A ), before generating the reports. To add modern entries on top, plainly right click on the quarrel total and choose “ Insert 1 above ”. If you select multiple rows, you can insert more than one raw row at the lapp prison term, which is important to paste values ( =new trades ). If you add columns this way, all format and data establishment ( =dropdowns ) will be inserted, besides. You entirely need to copy the convention for the column Exchange and Wallet manually. See the Binance import example ( chapter “ Best exercise with example Binance ” ) below for more information.
Just a technical note : Using an array convention to avoid copying the formula manually resulted in poor operation .Tip: Use format painter to keep your sheet in an easily readable layout (e.g. decimal precision and date format)
- 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 are colored automatically using conditional format. They can be maintained in the Settings tabloid. You can add your own but don ’ triiodothyronine erase or rename existing types, as some are used hard-coded in the reputation 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 early types .
Fees are always extra to the sell total. Which means e.g. if you want to buy BTC ( 1 BTC = 6,032.10 USD ) with 0.25 % tip while having 5,000.00 USD on your bill :
- 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 sell and buy total should be the lapp :
- Withdraw: Selling= 1 ETH, Fee = 0.0002 ETH (account balance is subtracted by 1.0002 ETH)
- Deposit: Buying = 1 ETH
You should use the independent coarse currentness 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 .
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 plane needs some attention if you wish to use your own crypto-currency symbols or like to add default disengagement exchange rates ( e.g. if your coin is not listed however and you can ’ thymine believe that it ’ mho measure has already gone zero ; – ) ) .Coin settings: default fiat exchange rate and alternative identifier for automated services
- 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).
This sheet will add newly accounts to your Trades sheet. The column Display can ’ t be edited, its a rule. Enter your Wallet-Name and choose Exchange/Technology ( maintained in Settings sheet ). To safe you from some headache, you may besides want to enter your wallet address for later research in your own transactions .Accounts sheet to manage your wallets and exchange accounts You should create one report quarrel per wallet, particularly on all unlike 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 quarrel per user per switch over ( the Wallet-Name will be your username if you own multiple accounts on that exchange ). “ For ICO ( Initial Coin Offering ) / STO ( Security Token Offering ) / and so forth ” please see the chapter ( with precisely this name ) above .
The most significant basic determine is your independent decree currentness in Display currency. thus far it ’ randomness tested with EUR and USD, but it should besides work with other currencies. If you have issues, please let me know .Basic settings For the Techup PHP-Proxy fields, please see the chapter “ Avoid rate terminus ad quem issues ” farther below. The Types are described in chapter “ Trades plane ” > “ Types ”. You can add modern 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 model Ethereum for all ERC-20 tokens out there. UPDATE : You need to add a ( release ) CryptoCompare key now, it is not possible to use the service any long without a release API key .
How to import trade data
If you want to import data from exchanges like Binance, KuCoin, Huobi, and so forth, I highly recommend to add a raw sheet Imports, which stores the death import date and time for each exchange/account ( entered manually after each manual of arms import process ). This way you have more see 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 exemplar string splittings ) and search & replace functionality. After you have created your consequence march sail ( see Binance exemplar below ), it ’ sulfur just a matter of 2 minutes to execute any extra import into the Trades sheet manually. Download an import assistant ( containing binance ) here. It includes the import overview sheet and the consequence march sheet for binance and bitstamp. For more details see following chapter “ Best commit with model Binance ”. Contributions of new import sheets are welcome in order to speed it up for everyone. If your exchange doesn ’ triiodothyronine offer a proper export format, you can merely copy & paste the HTML/trade data to impermanent 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
beginning choose your hope trades and export them to Excel ( xls ) format.
Export trades from binance 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 ) .Binance Excel export without modifications nowadays add column for the most crucial header fields of the Trades sail in the proper order, so we can copy & paste them by and by well :
- 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 fix value “ Trade ” as type for all entries ). The following formula are an example for row two :
- C “Buy”:
- D “Buy currency”:
- F “Sell”:
- G “Sell currency”:
- I “Fee”:
- J “Fee currency”:
We will leave the decree values empty, as they will be filled by our automated scripts, but we need the column to paste the data by rights into our Trades sheet. To copy this rule from row 2 to all others, select all cells ( B2 to J2 ) and drag it down to the last column. following step is to add new rows in the lapp ( or higher ) count as our new trades count into our Trades sheet. Afterwards plainly copy the trades into the created vacate rows by using Paste special – > merely values. once you have created an import plane for your exchange, you can reuse it. Then an import will just cost you around 2 minutes. If you want to plowshare your central spell sheet ( with recipe ), feel loose to do so and let me know. You can download an import benefactor ( containing binance and bitstamp ) here .Sheet with our added columns for import into trades table, the values are filled by our formulas
Review and change source code
You can review ( there is no back door ! ) and change the code in Tools > Script Editor, which does the charming ferment for you ( retrieving exchange rates and creating reports ) .
Upgrade to a new sheet version
After a newly acquittance you can copy & paste the subject of your Trades-sheet and your change values in Coin settings-, Accounts-, and Settings-sheet to the new released plane. The attempt to upgrade should be max. 5 minutes. UPDATE : The source code is now here on Github for easier versioning and collaboration .Since version 1.0.1 you can see your version number in sheet “Info”
Avoid rate limit issues
As the implant script is executed on Google servers, it is sharing its IP address with other Google Apps Scripts. therefore 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 humble ( protected ) PHP “ proxy ” script on Github here. You can run it on any web waiter that will route your request to your craved service and you don ’ t need to worry about any IP related problems .Settings-Sheet: Setup the “proxy” to avoid IP address limitations You can simply set it up by entering your ( hypertext transfer protocol ! ) URL ( including the script filename, by default option proxy.php ) and your keepsake .
- 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).
- 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”
- 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 identical welcome ( please see Github for the code part ) ! I will add them after a review to the sheet and note you with name and URL. If you have still issues after reading the wholly article and checking the trouble-shoot section, please write an electronic mail to support at techupbusiness.com . Donations are besides welcome :
- BTC: 3ECGVjb9GMbBf5UfKbSyriHSga851r7xMc
- ETH/ERC20: 0x7434B3Fcc0264F6EA173c6135fDeF63286Ee1f97
“Invested sum is wrong in profit overview”
All fiat deposits and decree withdraws are summarized and represent your current invested decree value. Please wear ’ triiodothyronine deposit/withdraw any currentness other than your decree currentness. If you wan ’ deoxythymidine monophosphate 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 decree amount “The coin prices and/or values are wrong”
You should go through your trades quarrel by row and check all rows related to your “ wrong ” asset symbol. Make certain 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 identify or the currency you are using is not known to CryptoCompare. Make certain you added a free CryptoCompare key ( you may need to register first for the dislodge plan ) in your Settings sheet. If the currentness 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 plane ( see the chapter Coin settings sheet in this article ) .Getting the ID from the search results (id is in brackets)Getting ID from URL of detail page ( /coins/
“Date sorting does not work properly”
You credibly have “ dates ” in these cells that are not parsed as dates by Google Sheets. If you can ’ deoxythymidine monophosphate sort them you probably need to fix them beginning .
“I want to use more than one fiat currency”
correctly nowadays the sheet alone allows one decree currency ( e.g. USD or EUR ) for the profit composition as “ base ”. Means, if you have an USD and an EUR bank report and send the money to exchanges, one of these currencies will not be recognized as decree deposit within the composition. Assuming your “ main ” display currency is EUR, a workaround would be to add two more trades to your USD deposit : First you deposit the measure 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 depository in EUR as investment in the main decree value but doesn ’ deoxythymidine monophosphate 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 hoard will be cleared .
“I want to have more realistic results for my portfolio values for my mining and airdrops”
first make certain that you upgraded to v1.0.1. then the follow will work…Example trades with automatic fiat valuesResulting portfolio values The resulting portfolio values are not truly 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 measure automatically. It would require deeper changes in the code. only if you vote for it in the comments, this may come one sidereal day.
But… there is a manual workaround already available : For Airdrops : Simply set the decree values of the “ free ” tokes to zero ( for all transactions that are related to these “ dislodge ” mined coins ). And you will get a net income of 100 % .Resulting portfolio values if you set the fiat values for all trades related to mining to zero For Mining : To put your electricity costs into calculation, set your actual costs manually for all transactions that are related to these mined coins .Resulting portfolio values if you set the fiat values for all trades related to mining of 1.0 BTC to 2.500 USD (per 1.0 BTC, would be 2.250 for 0.9 BTC, please refer to “Example trades with automatic fiat values”)
- Thanks to cryptobulls.info for the video review: https://www.youtube.com/watch?v=mXkG4fIuz8o
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