Staking Balances & Rewards on PoS tokens TEZOS— COSMOS — EOS — NANO — DUNE
UPDATE TRY IT OUT: LIVE SAMPLE SHEET
I wrote this medium in complement to Pull Crypto Wallet Balance directly on Google Sheets where you can retrieve almost any cryptocurrency (150+ with biggest market cap) balance from a wallet address and the article Pull realtime lending rates from DeFi platforms where I show you how to benefit from arbitrage opportunities on lending rates.
This article will be useful for you, if you want to get your staking balances as well as your rewards in real-time which could be of use for accounting and portfolio valuations. I’ll be using Google Sheets, it is a free, web-based program for creating and editing spreadsheets.
At the end of this medium, you will be able to automatically retrieve staking and reward balances from the following cryptocurrencies:
TEZOS— COSMOS— EOS — NANO — DUNE
The CRYPTOSTAKING & CRYPTOREWARDS Formula
This is what your Google Sheet will look like once you finish the setup:
The CRYPTOSTAKING function returns the amount staked for the wallet address you provided. Available for TEZOS , COSMOS, EOS, NANO and DUNE. You will be able to retrieve the amount through the following formula:
=CRYPTOSTAKING(“CRYPTOCURRENCY TICKER”,” PUBLIC WALLET ADDRESS”, optional refresh_cell)
2 Required Parameters + 1 Optional
- {“CRYPTOCURRENCY TICKER”} The cryptocurrency TICKER/SYMBOL data to fetch, for example the tikcker of Tezos is XTZ.
- {“PUBLIC WALLET ADDRESS”} The wallet address associated with the cryptocurrency you want the balance from. Please pay attention, DO NOT ENTER your private wallet address.
- Optional {EMPTY CELL REFERENCE} ONLY on 3rd argument. Reference an empty cell and change its content to force refresh of the balances.
The CRYPTOREWARDS function returns the amount of rewards you get from staking. Available for TEZOS and COSMOS. You will be able to retrieve the amount through the following formula:
=CRYPTOREWARDS(“TICKER”,”WALLET ADDRESS”, optional refresh_cell)
I open sourced the code for anyone that wishes to get the staking and reward amounts. The staking and reward amounts are directly taken from the block explorers (TZStats, Blocks.io, CosmoStation, Nano Crawler, DunScan.io,…).
Below I displayed a couple of examples on how you can use these 2 functions.
EXAMPLE 1: Tezos from Tezos Capital Legacy
Coin: Tezos (XTZ)
Wallet Address: Tezos Capital Legacy fund
For this first example, I’m interested in getting the staking and reward values for the Tezos Capital Legacy Fund that has the following wallet address: tz1TDSmoZXwVevLTEvKCTHWpomG76oC9S2fJ
Below, I’ve made a screenshot of the balances on the Tezos Block Explorer (TZStats).
The yellow frame box corresponds to Evaluated Balance which is the sum of the Balance and the Frozen balance. In the Frozen balance you have the Rewards, Fees and Deposits. Once you have Copied/Pasted the Github code in your sheets, you’ll be able to get the Evaluated Balance through this formula:
=CRYPTOBALANCE(“XTZ”,”tz1TDSmoZXwVevLTEvKCTHWpomG76oC9S2fJ”)
The blue frame box corresponds to the Staking Balance which you’ll get through this formula:
=CRYPTOSTAKING(“XTZ”,”tz1TDSmoZXwVevLTEvKCTHWpomG76oC9S2fJ”)
The green frame box corresponds to the Rewards from staking which you’ll get through this formula:
=CRYPTOREWARDS(“XTZ”,”tz1TDSmoZXwVevLTEvKCTHWpomG76oC9S2fJ”)
Screenshot 3 shows the view from your sheet with the exact amounts that you would have on TZStats block explorer.
EXAMPLE 2: EOS from Block One
Coin: EOS (EOS)
Wallet Address: b1 Block One
In this example, I’m interested in getting the staking amounts for the Block One fund that has the following wallet address: b1
Below, I’ve made a screenshot of the balances on the EOS block explorer Blocks.io. On EOS, the amount of CPU Staked and the amount of NET Staked correspond to your EOS staking amount. The CRYPTOREWARD formula is unavailable for EOS as the rewards from staking are distributed into the staking amount.
The blue frame box corresponds to the Staking Balance which you’ll get through this formula:
=CRYPTOSTAKING(“EOS”,”b1”)
Staking Amount = CPU Staked + NET Staked =40'199'995.005+49'999'995.005 =90'199'990.01
Screenshot 5 shows the view from your sheet with the exact EOS amounts that you would have on Block.io explorer.
EXAMPLE 3: COSMOS (ATOM) from COSMOSTATION Validator account
Coin: COSMOS (ATOM)
Wallet Address: COMOSTATION Validator
In this example, I’m interested in getting the Cosmos staking and reward amounts for the Cosmostation Validator account that has the following wallet address: cosmos1clpqr4nrk4khgkxj78fcwwh6dl3uw4ep4tgu9q
Below, I’ve made a screenshot of the balances on the Cosmos block explorer MINTSCAN.
Similarly to the other above examples, screenshot 7 shows that the CRYPTOSTAKING and CRYPTOREWARDS formula give the same ATOM amounts as the MINTSCAN explorer.
EXAMPLE 4: NANO from NANO’s Developer Fund
In this last example, I’m interested in getting the staking amounts for NANO’s developer fund that has the following wallet address: nano_1ipx847tk8o46pwxt5qjdbncjqcbwcc1rrmqnkztrfjy5k7z4imsrata9est
Below, I’ve made a screenshot of the balances on the NANO block explorer NANO CRAWLER. On NANO, you do not get rewards for staking. You will find the staking amount on the delegators tab names as NANO weight.
Screenshot 9 shows that the staking amount (NANO weight) matches NANO CRAWLER’s explorer.
EXAMPLE 5: DUNE(DUN) from Dune Foundation 1
Coin: DUNE (DUN) Tezos’ hard fork
Wallet Address: Dune Foundation 1
In this example, I’m interested in getting the Dune staking and reward amounts for the Dune Foundation 1 account that has the following wallet address: dn1as9EAbZeaYxu4HSpXSoVKJrJRTQ5CWXaH
Below, I’ve made a screenshot of the balances on the Dune block explorer DuneScan.
Similarly to the other above examples, screenshot 7 shows that the CRYPTOSTAKING and CRYPTOREWARDS formula give the same DUN amounts as the DunScan explorer.
Open-Source Setup (5min)
You will need:
- a Google Account for Google Sheets
- to Copy Paste code from Github
- 5 minutes of your time
How to :
- Open a Google sheet where you wish to use CRYPTOSTAKING() and CRYPTOREWARDS() function
2. Go to Tools › Script editor
3. From the Github, copy the content of CRYPTOTOOLS_V2 and paste it in the script editor (replace any existing content)
4. Save the script with File › Save, name it CRYPTOTOOLS, then close the script editor
5. Back to your Google sheet, refresh the page, a CRYPTOSTAKING and CRYPTOREWARDS menu will appear with parameters, an example and contact info.
Now the function is available for use! Try it out!
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.
Thank you.
Annex: remarks Google sheet’s formula syntax :
In some countries like Italy and Russia, Google sheet’s formula syntax is done with semicolon ‘ ; ’ instead of the comma ‘ , ’
If you get an error message from the formula, it might be the reason. In this case, the formula then becomes :
=CRYPTOSTAKING(“CRYPTOCURRENCY TICKER”;” PUBLIC WALLET ADDRESS”; optional refresh_cell)
