How to value Balancer V2 Matic Smart Chain wallet in Google Sheets?

Eloise

Published in

Coinmonks

7 min readSep 4, 2021

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

TRY IT OUT: LIVE SAMPLE SHEET

What is POLYGON (MATIC) ?

“Polygon aims to bring that future closer to reality by offering a framework for creating scaling solutions compatible with Ethereum. Their Proof of Stake sidechain has been garnering some interest across the Bitcoin and cryptocurrency community.

Still, you may have heard about the Polygon Network, a Proof of Stake (PoS) sidechain and one of the first live products in the Polygon ecosystem. A sidechain is essentially a parallel chain that’s connected to another blockchain.

Sidechains can offer several benefits — most notably, increased transaction throughput and low fees. If you’ve used the Polygon Network, you’ll know that it’s incredibly fast and very low-cost compared to Ethereum. Even so, there are some trade-offs to be made for this performance.

But what can you do as a user on Polygon? Unsurprisingly, similar things as you can on Ethereum, but much cheaper and faster. Some of the most popular DeFi dapps have already been deployed on it, such as Aave, 1inch, Curve, and Sushi. But there are also some native applications on it that don’t exist elsewhere, such as QuickSwap and Slingshot.”

Getting USD balance on Matic SmartChain Wallet

I created the CRYPTOSUMATIC function to get directly the total USD amount on any matic smartchain 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: CRYPTOSUMATIC(address, [optional refresh attribute])

1st parameter: the public blockchain wallet where the crypto is stored. 2nd parameter an optional fixed cell for automatic refresh of the data, this parameter

EXAMPLE: BALANCER V2 VALUATION

We’ll use the Balancer V2 Wallet (0xba12222222228d8ba445958a75a0704d566bf2c8) as an example.

What is Balancer ?

“Balancer is a software running on Ethereum that seeks to incentivize a distributed network of computers to operate an exchange where users can buy and sell any cryptocurrency.

An emerging decentralized finance (DeFi) protocol, Balancer uses a combination of crypto assets to provide this service, enabling trading without a financial intermediary like an exchange.

It might help to think about Balancer as a kind of index fund, where users create funds based on the cryptocurrencies in their portfolios. These funds are known as Balancer pools, and any user wishing to provide liquidity to a pool can do so by simply depositing an asset in them.

Users who provide liquidity to a Balancer pool then earn a portion of the trading fee paid to the network for the use of their funds, and are rewarded with a custom cryptocurrency called BAL.”

Here is the Balancer V2 wallet on Polygonscan:

https://polygonscan.com/address/0xba12222222228d8ba445958a75a0704d566bf2c8

Calculating the total portfolio amount for Balancer V2 and verifying through Polygonscan.

Total ALTCOIN USD Amount + MATIC USD Amount = $212 MIO

The CRYPTOSUMATIC formula will get the USD networth of the specified wallet. In sheets, in order to retrieve this amount you will need to enter the following formula: =CRYPTOSUMATIC(“0xBA12222222228d8Ba445958a75a0704d566BF2C8”)

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/copy

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

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 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 matic smartchain address. If you had used the CRYPTOBALANCE function, you would have to add more than cryptocurrencies (MATIC, WETH, WBTC, AAVE, … etc..) in order to get the whole USD balance amount. It becomes off-putting and quite long without the CRYPTOSUMATIC method.

For the purpose of the example, if you wished to retrieve the MATIC balance from the Balancer V2 address, you would need to use the CRYPTOBALANCE function this way:

=CRYPTOBALANCE(‘MATIC’,’0xBA12222222228d8Ba445958a75a0704d566BF2C8’)

On the polygon smartchain except for MATIC, tickers can be called using the contract address directly instead of ticker. For example in the template sheet, in order to get the BAL amount, we call the function the following way:

=CRYPTOBALANCE(‘m0x9a71012b13ca4d3d0cdc72a177df3ef03b0e76a3’,’0xBA12222222228d8Ba445958a75a0704d566BF2C8’)

You must put a “m” in front of the contract address in order to signal to the function that you are looking for a balance on the Polygon SmartChain.

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 and Excel

For anyone looking to extract automatically data from CoinGecko.

medium.com

CONCLUSION:

This medium shows you how to retrieve total USD valuation in Google Sheet from a Polygon Smartchain wallet. The formula looks like:
=CRYPTOSUMATIC(POLYGON WALLET)

=CRYPTOSUMATIC(“0xBA12222222228d8Ba445958a75a0704d566BF2C8”)
GOOGLE SHEET EXAMPLE HERE

If you are looking for the same on ERC20 and BEP20, check out how to get the total Dollar amount of an ERC20 address here AND how to get the total Dollar amount of an BEP20 address here.

This is ongoing work. 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.

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 Channel and learn about crypto trading and investing