DeFi crypto portfolio in Google Sheets -Vaults, NFTs, Tokens, Interest
Simple Google Sheet Tools to value your DEFI crypto portfolio using APIs from Zapper, Etherscan, Bscscan and more. Duplicate the sheet and make it your own.
Intro
This tutorial is built to help non-technical Google sheet users get the list of combined assets on their Ethereum, Binance Smart Chain, Polygon, Fantom, etc.. chains. I’ve been working on gathering crypto data in Google Sheets for a couple of years now and I found that sheets is a useful interface when gathering data from decentralized protocols. In this tutorial you’ll learn how to integrate javascript code, and retrieve portfolio assets in the Sheet with a user-defined formula.
Prerequisites
For beginners no need for coding skills but basic knowledge is a plus. This training assumes that you have a Gmail/Google account as we will be building upon Google Sheets.
What is DEFI ?
Decentralized Finance (commonly referred to as DeFi) is a blockchain-based form of finance that does not rely on central financial intermediaries such as brokerages, exchanges, or banks to offer traditional financial instruments, and instead utilizes smart contracts on blockchains, the most common being Ethereum.DeFi platforms allow people to lend or borrow funds from others, speculate on price movements on a range of assets using derivatives, trade cryptocurrencies, insure against risks, and earn interest in savings-like accounts
Where does the data come from ?
Zapper
Zapper is a simple dashboard for DeFi where you can easily track and visualize all your DeFi assets and liabilities in one simple interface.
In order to build the DEFI portfolio interface in Google Sheet, I have integrated the Zapper API which is publicly available. The documentation can be found here.
Zapper API endpoints overview,
The main advantage of using their API compared to others is that they have direct endpoints for claimable tokens, interest bearing, NFTs, vaults amounts.
Nevertheless it doesn’t give the full extensive list of assets, especially assets that sometimes don’t have a clear valorization. For that reason, I integrated other APIs:
Etherscan - BscScan — PolygonScan APIs
- Etherscan is a Block Explorer and Analytics Platform for Ethereum. You can find the API endpoints in the following documentation.
- Bscscan allows you to explore and search the Binance blockchain for transactions, addresses, tokens, prices and other activities taking place on Binance Smart Chain. (API documentation)
- PolygonScan allows you to explore and search the Polygon blockchain for transactions, addresses, tokens, prices and other activities taking place on Polygon Smart Chain. (API documentation)
With these APIs, you can retrieve all tokens from an address. This API list isn’t exhaustive, more smart chains will be added as more are available.
What does it look like in Google Sheet ?
The below GIF shows how to load the portfolio valuation.
DIY:
- Add up to 3 ERC20, BEP20, MATIC addresses
- Add specific protocols if needed
- Press on Refresh
The Google Script takes as parameters, the list of addresses (3 limit) and protocols (5 limit), then calls my servers where it computes all holdings using the API endpoints mentioned earlier and then transforms the data into a range with the following characteristics:
- NETWORK
- PROTOCOL
- ADDRESS
- TYPE
- SYMBOL
- BALANCE
- PRICE
- BALANCE_USD
It will also summarize holdings in 3 piecharts, where holdings are computed by network, assets, and type (interest bearing, tokens etc…)
In the Sheet tab DEFI_NETWORTH, what you need to do is enter the defi address in the cells where its mentioned DEFI ADDRESS.
Then you need to click on Refresh and wait for the results to load. It can take up to 1min to load if addresses have a lot of protocols. If you wish to specify protocols, you can do so by selecting the names in the cells next to DEFI protocols. If those optional cells are empty, the script will get the general list of protocols.
Don’t change the name of the TAB or the script won’t work. It’s dependent on DEFI_NETWORTH as being the name of the TAB.
The drawback of this method is that if you have many protocols, it won’t get all of the data because I had to optimize the computational load on my servers. As a result I created the following customizable formulas where you can request directly by protocols.
CRYPTODEFI
This formula gets the list of all assets (pool, tokens, claimable …) by DEFI protocol.
CRYPTODEFI_BALANCE
This formula gets the staked/lended quantity per symbol/ticker and DEFI protocol.
CRYPTODEFI_BALANCEUSD
This formula gets the staked/lended USD amount per symbol/ticker and DEFI protocol.
GETTING THE LIST OF PROTOCOL NAMES
If you are wondering how to get the names of protocols, I created a quick script for all available protocols on Zapper. The following javascript code can be used to list and select specific protocols you want assets/pools from. Right now it’s a fixed list but soon it will be completely automatic.
You can display the list of names using this formula in the sheet:
If you have assets on a protocol that is not in the list, you can find the name directly on Zapper’s website when looking at your portfolio.
The side picture shows where you can find the name of the protocol to call. In this example, you would call:
‘binance-smart-chain autofarm’
NFT EXAMPLE
If you are interesting in valuing your NFTs in sheets, you can do so by using the CRYPTODEFI formula and use :
-> ETHEREUM NFT as the protocol
TOKENS EXAMPLE
If you are interesting in getting the list of tokens you hold on Ethereum for example, you can do so by using the CRYPTODEFI formula and entering :
-> ETHEREUM TOKENS as the protocol
JAVASCRIPT CODE SETUP:
- Easy Way
The easiest way to get access to the code is to make a copy of the read-only sheet which will automatically add the code to the newly created sheet.
2. Insert Manually the Code
a) Go to Tools › Script editor
b) Copy paste and Add the following Script
- From the Github, copy the content of defiasset.gs and paste it in the script editor (replace any existing content), name it DEFI.
c) Save the script with File › Save and refresh the sheet
d) Make sure you Authorize Google Services. Apps Script requires user authorization to access private data from built-in Google services or advanced Google services.
For more details, click here.
CONCLUSION:
This medium shows you how to retrieve a portfolio valuation of DEFI assets from a public blockchain address.
- App Script Runtime directly embedded in the Google app script loads the portfolio valuation directly within the sheet.
- CRYPTODEFI gets the list of all assets (pool, tokens, claimable …) by DEFI protocol.
- CRYPTODEFI_BALANCE gets the staked/lended quantity per symbol/ticker and DEFI protocol
- CRYPTODEFI_BALANCEUSD gets the staked/lended USD amount per symbol/ticker and DEFI protocol.
Code References
This is ongoing work. 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 am interested on getting feedback on what doesn’t work, what’s missing, what needs to be improved etc.. Feedback is very welcomed. A telegram chat is also available for support, don’t hesitate to leave a message. If this project adds any value, CryptoTools is on patreon.
Thank you so much for investing your time in reading this article.
Join Coinmonks Telegram Channel and Youtube Channel learn about crypto trading and investing
