How to value your crypto on a Binance Smart Chain — BEP20 wallet in 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.
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. And I also wrote one on how to get the total Dollar amount of an ERC20 address here.
Lately my users have requested they get in 1 formula the total Dollar amount of a BEP20 address (Binance Smart Chain Address).
CRYPTOSUMBSC function
I created the CRYPTOSUMBSC function to get directly the total USD amount on any BEP20 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: CRYPTOSUMBSC(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: BINANCE HOT WALLET 6 BEP20 VALUATION
We’ll use the Binance Hot Wallet 6 BEP20 address (0x8894e0a0c962cb723c1976a4421c95949be2d4e3) as an example.
Calculating the total portfolio amount for Binance Hot Wallet 6 and verifying through Ethersan.
Total ALTCOIN USD Amount + BNB USD Amount = $2,335,968,714
In Google Sheet, in order to retrieve this amount you will need to enter the following formula:
=CRYPTOSUMBSC(“0x8894e0a0c962cb723c1976a4421c95949be2d4e3”)
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 BEP20 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 a bep20 public address. If you had used the CRYPTOBALANCE function, you would have to add more than cryptocurrencies (BNB, BUSD, USDC,BTCB, … etc..) in order to get the whole USD balance amount. It becomes off-putting and quite long without the CRYPTOSUMBSC method.
For the purpose of the example, if you wished to retrieve the BNB balance from the Binance Hot Wallet 6 address, you would need to use the CRYPTOBALANCE function this way:
=CRYPTOBALANCE(‘BNB’,’0x8894e0a0c962cb723c1976a4421c95949be2d4e3’)
For BEP20 except for BNB, tickers must be called using the contract address directly instead of ticker. For example in the template sheet, in order to get the BUSD amount, we call the function the following way:
=CRYPTOBALANCE(‘b0xe9e7cea3dedca5984780bafc599bd69add087d56’,’0x8894e0a0c962cb723c1976a4421c95949be2d4e3’)
You must put a “b” in front of the contract address in order to signal to the function that you are looking for a balance on the Binance Smart Chain
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
CONCLUSION:
This medium shows you how to retrieve total USD valuation in Google Sheet using an BEP20 address. The formula looks like:
=CRYPTOSUMBSC(BEP20 ADDRESS)
=CRYPTOSUMBSC(“0x8894e0a0c962cb723c1976a4421c95949be2d4e3”)
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!
