How to Get Crypto DeFi Data Into Your Spreadsheet: Part 1
While DeFi continues gaining in popularity, DeFi data remains difficult to access for non-developers. Learn how to get different types of common DeFi data, metrics & analytics into your Excel or Google spreadsheet.
The purpose of this article is to demonstrate how to easily search and get data for the crypto DeFi markets in a spreadsheet using Cryptosheets. This is intended for all users levels and highlights basic concepts
OVERVIEW
In this tutorial we’re going to highlight several examples demonstrating how to get different types of DeFi data, metrics and analytics into Excel & Googlesheets using Cryptosheets. For this article (Part 1) we’ll focus on three basic DeFi data concepts for statistics, historical liquidity & yield farming.
(QUICK) DEFI BACKGROUND
DeFi is short for “decentralized finance” which is a broadly generalized term for a variety of financial applications across the cryptocurrency and blockchain industry. The defining and unifying attribute is that they are all commonly designed to disrupt legacy financial intermediaries and optimize traditional market inefficiencies.
The category is constantly evolving with current major applications including: stablecoins, decentralized exchanges (DEXs), crypto debt servicing (borrowing/lending), prediction markets, yield farming, liquidity mining, full stack autonomous financial transaction management and much more.
HOW TO
Follow the simple steps for each example to find and get different types of DeFi data.
EXAMPLES COVERED
- DeFi Global Stats
- UniSwap V2 Historical Liquidity
- Yearn Yield Farming
EXAMPLE 1: DeFi Global Stats
Arguably the easiest to remember formula to get DeFi data in Cryptosheets is CoinGecko’s API endpoint fittingly named “defi”.
- Load your Google or Excel spreadsheet and login to Cryptosheets
- In cell A1 type “Coingecko”
- In cell A2 type “defi”
- In cell A4 type =CSQUERYA(A1,A2)
5. To clean up the formatting we’ll use the global parameter called _path and add it to our existing formula like this
6. Finally we’ll use the built in TRANSPOSE formula to pivot the data and format the values
Here are the exact formulas if you want to simply copy + paste to try them yourself…
Excel: =CS.QUERYA("Coingecko","defi","_path","data")Googlesheets: =CSQUERYA("Coingecko","defi","_path","data")
EXAMPLE 2: Uniswap V2 Historical Liquidity
Using data from The Graph & UniSwap we’ll pull in historical liquidity data and create a simple chart.
- From the Cryptosheets side panel, type “uniswap historical liquidity” into the global search bar and click on the endpoint
2. From the API console query builder, select the _formatDates option from the global parameters section and change it to MM-DD-YYYY so we have clean dates that will work well with Excel or Google Sheets charts
3. Press the “Generate Spreadsheet Template” button which will automagically send all of your parameters and formula(s) to cells in a new sheet tab 👉 #SHAZAM!
4. Create a simple chart and add a basic trendline to show the 10day moving average — that’s it!
Here are the exact formulas if you want to simply copy + paste to try them yourself…
Googlesheets:
=CSQUERYA(“The Graph”,”Uniswap V2 Historical Total Liquidity”, “query”,”total_liq”,”_formatDates”,”MM-DD-YYYY”)
EXAMPLE 3: Yearn Yield Farming Screener
Using data from Yearn API & Yearns Finance we’ll pull in yields for different vaults on the Yearn network and compare the APY (annualized percentage yield) for different periods. Vaults employ dynamic strategies to automate the best yield farming opportunities available but analyzing multiple period returns in tandem helps provide better overall perspective on the potential opportunities.
- We’re going to use the examples on the dedicated Cryptosheets docs page for the Yearn API — Vaults APY endpoint and the same formatting for our formula from example #2.
- In cell B3 type “Yearn API”
- In cell B4 type “Vaults APY”
- In cell B5 type
=CSQUERYA(B3,B4)
5. Now we’re going to shape the response using the global parameter _fields so we only get the fields back that we want
6. Then we’ll simply replicate our layout and change the specific fields to return for each one giving us 3x yields over different periods to compare
7. Last we’ll add some quick sorting for each formula using the _orderBy global parameter then apply some simple formatting
Here are some of the exact formulas if you want to simply copy + paste to try them yourself…
Googlesheets:
=CSQUERYA(“Yearn API”,”Vaults APY”,”_fields”,”symbol,description,apyOneDaySample”,”_orderBy”,”-apyOneDaySample”)
SUMMARY
In this article we covered three different DeFi data categories and demonstrated how to:
- Get DeFi data
- Transform our data
- Visualize our data for analysis
👉 Click here to try the template in Excel or Googlesheets for free!
In future articles we will explore increasingly advanced uses cases including DeFi data archetypes, derivatives and sophisticated risk management strategies. Follow us on Twitter and subscribe to our YouTube channel for more DeFi content, tutorials and examples.
Cryptosheets is free to sign up for and start using now….
What are you waiting for? 👉 Get crypto data in 30 seconds or less
Get the Googlesheets add-in now
________________________________________________________________
Troubleshooting
- See FAQ and troubleshooting help topic in the help center here
- Explore troubleshooting tips & tricks in the quick docs section here
Tutorials
- Explore tutorials here
ADDITIONAL RESOURCES
Website | WebApp | Excel | Googlesheets | Help Center | Blog | Twitter | YouTube
________________________________________________________________
Tags: console, api, functions, function builder, formulas, make request, get data, tutorial, guide, excel, googlesheets, crypto data, query builder, excel crypto api, defi, decentralized, yield farming, yearn, coingecko, the graph, YFI, UNI, uniswap, uniswaprotocol
