Open in app

Sign in

Write

Sign in

How to value your Ethereum portfolio using Google Sheets?

Eloise
Coinmonks

--

Simple Google Sheet Tricks to value your digital assets. Duplicate the sheet and make it your own.

TRY IT OUT: LIVE SAMPLE SHEET

Image : Portfolio Valuation Example using Google Sheets

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.

I already wrote a medium on how anyone can automatically retrieve stock & cryptocurrency prices, crypto balances from public blockchain addresses, lending and farming rates from different lending platforms, and stacking and rewards from Proof of Stake blockchains.

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.

Source: https://ethplorer.io/address/0xbcd5000f5c522856e710c5d274bb672b2f2eefbf
Source: https://etherscan.io/address/0xbcd5000f5c522856e710c5d274bb672b2f2eefbf

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”)

I used a second parameter in the formula which helps for automatic refresh. Each time the value in cell B1 is modified, it will automatically refresh the valuation. You can change the 6 into 5, it will then refresh.

TRY IT OUT: LIVE SAMPLE SHEET

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.

https://docs.google.com/spreadsheets/d/1DaR4yKrDuJ6LkgpwbA5aqpXmwtKUmHrQ_uVPdPA2-cE/edit?usp=sharing

2 — You need to copy/paste the following code in the app script of the Google Sheet you wish to use.

Tools -Script Editor

Code is available Here

More details can be find in the following medium

Google Sheet Open Source CryptoTools Set-Up

Objective: Show users how they can easily access the custom built-in CryptoTools formulas in Google Sheet:

medium.com

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)

Kick Token Contract Address : 0xc12d1c73ee7dc3615ba4e37e4abfdbddfa38907e

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”)

GOOGLE SHEET EXAMPLE HERE

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

--

--

Eloise
Coinmonks

Written by Eloise

675 Followers
Writer for

AI | Data | Blockchain Enthusiast | patreon.com/cryptotools