How to value your Ethereum portfolio using Google Sheets?
Simple Google Sheet Tricks to value your digital assets. Duplicate the sheet and make it your own.
At first, when I created CRYPTOTOOLS, I wanted to have a private flexible way to analyze whale moves on the blockchain, value trades I had made, find arbitrage opportunities and compute statistics in Google Sheets. Then friends got interested, so I made it more efficient and made the sheets public for everyone. I hope you can benefit from my work. And for developers who wish to directly connect to my API, you can do so here API documentation.
Lately my users have requested they get in 1 formula the total Dollar amount of an ERC20 address.
CRYPTOSUMETH function
I created the CRYPTOSUMETH function to get directly the total USD amount on any ERC20 address. When you hold many different coins on the same address, it is easier to use this function than looking one coin at a time.
Syntax: CRYPTOSUMETH(address, [optional refresh attribute])
1st parameter: the public blockchain address where the crypto is stored. 2nd parameter an optional fixed cell for automatic refresh of the data, this parameter
EXAMPLE: POLYCHAIN CAPITAL ERC20 VALUATION
We’ll use this Polychain Capital ERC20 (0xbcd5000f5c522856e710c5d274bb672b2f2eefbf) as an example.
Calculating the total portfolio amount for Polychain Capital and verifying through Ethersan and Ethplorer.
Total ERC20 USD Amount + ETH USD Amount = $54,4 mio
In Google Sheet, in order to retrieve this amount you will need to enter the following formula:
=CRYPTOSUMETH(“0xbcd5000f5c522856e710c5d274bb672b2f2eefbf”)
Here’s how you can get this formula to work in your private Google sheet
CODE SETUP:
You have 2 options to access the code:
1 — Easy, you make a copy of the template sheet. When you do, it will automatically save a new template sheet with the code.
2 — You need to copy/paste the following code in the app script of the Google Sheet you wish to use.
Code is available Here
More details can be find in the following medium
For those of you that are also interested in retrieving the individual balances from each of the cryptocurrencies on your ERC20 wallet, you can use the following function.
CRYPTOBALANCE function
In the medium How to value your Stocks & Crypto Portfolio on Google Sheets, I explained how to retrieve each of the coins on an ERC20. If you had used the CRYPTOBALANCE function, you would have to add all 86 cryptocurrencies (ETH, YFI, KICK, EBK etc..) from the address in order to get the whole USD balance amount. It becomes off-putting and quite long without this method.
For the purpose of the example, if you wished to retrieve the ETH balance from the Polychain Capital address, you would need to use the CRYPTOBALANCE function this way:
=CRYPTOBALANCE(‘ETH’,’0xbcd5000f5c522856e710c5d274bb672b2f2eefbf’,$A$1)
Some of the ERC20 that have duplicates in terms of tickers can be called using the contract address directly instead of ticker. For example in the template sheet, in order to get the KICK TOKEN amount, we call the function the following way:
=CRYPTOBALANCE(‘0xc12d1c73ee7dc3615ba4e37e4abfdbddfa38907e’,’0xbcd5000f5c522856e710c5d274bb672b2f2eefbf’,$A$1)
All details are written in the medium. Also if you wish to get the prices of cryptocurrencies in order to value all of your positions individually you can use the COINGECKO functions defined in CoinGecko Prices, Volumes, Market Caps in Google Sheets.
CONCLUSION:
This medium shows you how to retrieve total USD valuation in Google Sheet using an ERC20 address. The formula looks like:
=CRYPTOSUMETH(ERC20 ADDRESS)
=CRYPTOSUMETH(“0xbcd5000f5c522856e710c5d274bb672b2f2eefbf”)
Refreshing
Values are cached for 10min. My server updates prices every 2 minutes but for optimization on API calls on Google Sheets, data is refreshed on a 10min schedule. This is why you’ll notice a small lag on prices. It can be modified directly in the code.
This is ongoing work. If you find errors, please do not hesitate to let me know. Feedback is very welcomed. A telegram chat is also available for support. If this project adds any value to you and/or are looking for personalized coding on your Google Sheets, don’t hesitate to leave a message.
Thank you so much for investing your time in reading this article.
Take care of yourself & your family in these challenging times!
Join Coinmonks Telegram group and learn about crypto trading and investing
Also, Read
- What is Margin Trading
- The Best Crypto Trading Bot | Grid Trading
- 3Commas Review | Pionex Review | Coinrule review
- AAX Exchange Review | Deribit Review |FTX Exchange Review
- NGRAVE ZERO review | Phemex Review | PrimeXBT Review
- Bybit Exchange Review | Bityard Review | CoinSpot Review
- 3Commas vs Cryptohopper | Earn crypto interest
- The Best Bitcoin Hardware wallet | BitBox02 Review
- Ledger vs Ngrave | Ledger nano s vs x | Binance Review
- Crypto Copy Trading Platforms | Coinmama Review
- CoinLoan Review | YouHodler Review | BlockFi Review
- The Best Crypto Tax Software | CoinTracking Review
- Best Crypto Lending Platforms | Leveraged Token
- BlockFi vs Celsius | Hodlnaut Review
- Bitsgap review | Quadency Review | Bitbns Review
- Ellipal Titan Review | SecuX Stone Review
- LocalBitcoins Review | Cryptocurrency Savings Accounts
- Best Blockchain Analysis Tools | Earn Bitcoin
- Crypto arbitrage Guide | How to Short Bitcoin
- Best Crypto Charting Tool | Best Crypto Exchange
- How to buy Bitcoin in India? | WazirX Review
- Bitcoin exchange in India | Bitcoin Savings Account
- CoinDCX Review | Crypto Margin Trading Exchanges