TL;DR: see APR logs in fragola_apr_analysis - Google Sheets
Hello Isvikingers,
I saw a lot of drama around APR’s in the discord lately. I think people are accustomed to farming, so they are more used to monitor their farming rewards, rather than the evolution of their underlying LP position. Sorbetto Fragola is different because it earns rewards from UNI-V3 fees only, thus giving a true value to ICE (which is not wasted to attract liquidity providers).
In this regard, the net position in Fragola (position without the impact of the prices evolution) might decrease (after a rerange for example) or increase. It’s a long term game: there will be negative days (remember impermanent loss is amplified in UNI-V3) but the net positions should mostly increase over a longer timespan. Following the “don’t trust, verify!” moto I coded a small bash script that anyone can run on its own computer to verify that claim. Here it is:
#!/bin/bash
### Credits ###
# This bash scripts monitors the net position of Popsicle Sorbetto Fragola pools
# version: 1.00
# credits: zazka
#################
### Dependencies ###
# curl (see https://curl.se/)
# jq (see https://stedolan.github.io/jq/)
#################
### Constants ###
ARRAY_PLP_POOLS=(AXS-WETH_0.3pct DYDX-WETH_0.3pct FTM-WETH_1pct SHIB-WETH_0.3pct SHIB-WETH_1pct SPELL-WETH_0.3pct USDC-WETH_0.3pct USDC-WETH_0.05pct WBTC-WETH_0.3pct WBTC-WETH_0.05pct WETH-ICE_0.3pct WETH-USDT_0.3pct WETH-USDT_0.05pct USDT-USDC_0.01pct WBTC-USDT_0.3pct SAND-WETH_0.3pct ENS-WETH_0.3pct)
# I skip MIM-USDC_0.05pct, MIM-USDT_0.05pct, USDT-UST_0.05pct and USDC-UST_0.05pct because I don't have a user_address for these pools
declare -A MAP_CONSTANTS
# user addresses
MAP_CONSTANTS[user_address_AXS-WETH_0.3pct]="0xd72C0Bc5250c8F82E48BE46aD5f65bB5891483a0"
MAP_CONSTANTS[user_address_DYDX-WETH_0.3pct]="0x2A44FCB5aA315c6718a2759095Fa351B065D5971"
MAP_CONSTANTS[user_address_FTM-WETH_1pct]="0x973Be491406BDD307179D72d599C015d15e7d200"
MAP_CONSTANTS[user_address_SHIB-WETH_0.3pct]="0x415B67BEEF3B502367652DDED95DD02D273cb94c"
MAP_CONSTANTS[user_address_SHIB-WETH_1pct]="0x32844a824F458f24878515923F1FA120b6b711F2"
MAP_CONSTANTS[user_address_SPELL-WETH_0.3pct]="0xA37EC72f16b09664B633213dEfdc0fC077C225eD"
MAP_CONSTANTS[user_address_USDC-WETH_0.3pct]="0xF2cdD8b147802a07F862C9dc125190e0653795a2"
MAP_CONSTANTS[user_address_USDC-WETH_0.05pct]="0xEfce38f31Ebeb9637E85D3487595261FDf6ebeEb"
MAP_CONSTANTS[user_address_WBTC-WETH_0.3pct]="0x49bEa41f0B0Ed9B15CE0afad922C507B494200a3"
MAP_CONSTANTS[user_address_WBTC-WETH_0.05pct]="0x319d84CAe0F06F1f73B722623e08cDfc54Cfd30a"
MAP_CONSTANTS[user_address_WETH-ICE_0.3pct]="0xFd1916343d7D2Ec8C8d8ee18055bD551137E1a0e"
MAP_CONSTANTS[user_address_WETH-USDT_0.3pct]="0x1f92b5affD12981Ef0FA7Ba22a802379Fd36929E"
MAP_CONSTANTS[user_address_WETH-USDT_0.05pct]="0x5E49a561b35d6867d98D67B702C3b7946edeF044"
#MAP_CONSTANTS[user_address_MIM-USDC_0.05pct]="???"
#MAP_CONSTANTS[user_address_MIM-USDT_0.05pct]="???"
MAP_CONSTANTS[user_address_USDT-USDC_0.01pct]="0x0fE8583C45d181FDa20d49b05361cd41236485a9"
#MAP_CONSTANTS[user_address_USDT-UST_0.05pct]="???"
#MAP_CONSTANTS[user_address_USDC-UST_0.05pct]="???"
MAP_CONSTANTS[user_address_WBTC-USDT_0.3pct]="0x355240dad9a5c9A320d07e4A31446F4F4c832fba"
MAP_CONSTANTS[user_address_SAND-WETH_0.3pct]="0x3DEA70718E2d02e5d5367687b23a7E1d7e6B08da"
MAP_CONSTANTS[user_address_ENS-WETH_0.3pct]="0x537655Be6EB860f801666D11e01B79a402Fac673"
# fragola addresses
MAP_CONSTANTS[fragola_address_AXS-WETH_0.3pct]="0xa7053782dC3523D2C82B439Acf3f9344Fb47b97f"
MAP_CONSTANTS[fragola_address_DYDX-WETH_0.3pct]="0xd2C5A739ebfE3E00CFa88A51749d367d7c496CCf"
MAP_CONSTANTS[fragola_address_FTM-WETH_1pct]="0x949FDF28F437258E7564a35596b1A99b24F81e4e"
MAP_CONSTANTS[fragola_address_SHIB-WETH_0.3pct]="0xa0273C10b8A4BF0bDC57cb0bC974E3A9d89527b8"
MAP_CONSTANTS[fragola_address_SHIB-WETH_1pct]="0x495410B129A27bC771ce8fb316d804a5686B8Ea7"
MAP_CONSTANTS[fragola_address_SPELL-WETH_0.3pct]="0x5C08A6762CAF9ec8a42F249eBC23aAE66097218D"
MAP_CONSTANTS[fragola_address_USDC-WETH_0.3pct]="0xaE7b92C8B14E7bdB523408aE0A6fFbf3f589adD9"
MAP_CONSTANTS[fragola_address_USDC-WETH_0.05pct]="0x9683D433621A83aA7dd290106e1da85251317F55"
MAP_CONSTANTS[fragola_address_WBTC-WETH_0.3pct]="0x212Aa024E25A9C9bAF5b5397B558B7ccea81740B"
MAP_CONSTANTS[fragola_address_WBTC-WETH_0.05pct]="0xBE5d1d15617879B22C7b6a8e1e16aDD6d0bE3c61"
MAP_CONSTANTS[fragola_address_WETH-ICE_0.3pct]="0xFF338D347E59d6B61E5C69382915D863bb22Ef2f"
MAP_CONSTANTS[fragola_address_WETH-USDT_0.3pct]="0xa1BE64Bb138f2B6BCC2fBeCb14c3901b63943d0E"
MAP_CONSTANTS[fragola_address_WETH-USDT_0.05pct]="0x8d8B490fCe6Ca1A31752E7cFAFa954Bf30eB7EE2"
MAP_CONSTANTS[fragola_address_MIM-USDC_0.05pct]="0x298b7c5e0770D151e4C5CF6cCA4Dae3A3FFc8E27"
MAP_CONSTANTS[fragola_address_MIM-USDT_0.05pct]="0xe6E14be906c1F1b438DA2010B38bECa14b387231"
MAP_CONSTANTS[fragola_address_USDT-USDC_0.01pct]="0x989442D5cCB27E7931095B0f3165c75a6def9bc3"
MAP_CONSTANTS[fragola_address_USDT-UST_0.05pct]="0x92995D179a5528334356cB4Dc5c6cbb1c068696C"
MAP_CONSTANTS[fragola_address_USDC-UST_0.05pct]="0xbA38029806AbE4B45D5273098137DDb52dA8e62F"
MAP_CONSTANTS[fragola_address_WBTC-USDT_0.3pct]="0xd2EF15af2649CC46e3E23B96563a3d44ef5E5A06"
MAP_CONSTANTS[fragola_address_SAND-WETH_0.3pct]="0xF4f542E4b5E2345A1f2D0fEab9492357Ebc5c8f4"
MAP_CONSTANTS[fragola_address_ENS-WETH_0.3pct]="0x36e9B6e7FADC7b8Ee289c8A24Ad96573cda3D7D9"
################
# I retrieve the data from zapper.fi and log the data for every PLP pool
for plp_pool in "${ARRAY_PLP_POOLS[@]}"; do
printf "\nRetrieving data for the pool ${plp_pool}...\n"
timestamp=$(date +"%s")
json_popsicle=$(curl --silent "https://api.zapper.fi/v1/balances?addresses%5B0%5D=${MAP_CONSTANTS[user_address_${plp_pool}]}&nonNilOnly=true&networks%5B0%5D=ethereum&api_key=5d1237c2-3840-4733-8e92-c5a58fe81b88" -H 'Accept: text/event-stream' -H 'Accept-Language: en-US,en;q=0.5' --compressed -H 'Origin: https://zapper.fi' -H 'DNT: 1' -H 'Connection: keep-alive' -H 'Referer: https://zapper.fi/' -H 'Sec-Fetch-Dest: empty' -H 'Sec-Fetch-Mode: cors' -H 'Sec-Fetch-Site: same-site' -H 'Pragma: no-cache' -H 'Cache-Control: no-cache' -H 'TE: trailers' | grep -i "${MAP_CONSTANTS[fragola_address_${plp_pool}]}" | sed 's,data: {,{,g' | jq --arg fragola_address "$(awk '{print tolower($0)}' <<< ${MAP_CONSTANTS[fragola_address_${plp_pool}]})" '.balances[].products[].assets[].tokens[] | select(.address==$fragola_address)')
# I query the values of tokenA
price_tokenA=$(jq '.tokens[0].price' <<< ${json_popsicle})
balance_tokenA=$(jq '.tokens[0].balance' <<< ${json_popsicle})
balance_USD_tokenA=$(jq '.tokens[0].balanceUSD' <<< ${json_popsicle})
# I query the values of tokenB
price_tokenB=$(jq '.tokens[1].price' <<< ${json_popsicle})
balance_tokenB=$(jq '.tokens[1].balance' <<< ${json_popsicle})
balance_USD_tokenB=$(jq '.tokens[1].balanceUSD' <<< ${json_popsicle})
# I query the total balance
total_balance_USD=$(jq '.balanceUSD' <<< ${json_popsicle})
# I generate the columns headers
symbol_tokenA=$(jq --raw-output '.tokens[0].symbol' <<< ${json_popsicle})
symbol_tokenB=$(jq --raw-output '.tokens[1].symbol' <<< ${json_popsicle})
columns_headers="Unix time\tPrice ${symbol_tokenA} (USD)\tBalance ${symbol_tokenA}\tBalance ${symbol_tokenA} (USD)\tPrice ${symbol_tokenB} (USD)\tBalance ${symbol_tokenB}\tBalance ${symbol_tokenB} (USD)\tTotal Balance (USD)\n"
printf "${columns_headers}"
# I log the data
logfile="$( cd "$(dirname "$0")" >/dev/null 2>&1 ; pwd -P )/${plp_pool}.log"
# I write the user_address, fragola_address and the columns headers on top of to the logfile if it doesn't exist
if [ ! -e ${logfile} ]; then
printf "plp_pool\t${plp_pool}\nfragola_address\t${MAP_CONSTANTS[fragola_address_${plp_pool}]}\nuser_address\t${MAP_CONSTANTS[user_address_${plp_pool}]}\n${columns_headers}" > "${logfile}"
fi
# I append the logline to the logfile
printf "${timestamp}\t${price_tokenA}\t${balance_tokenA}\t${balance_USD_tokenA}\t${price_tokenB}\t${balance_tokenB}\t${balance_USD_tokenB}\t${total_balance_USD}\n" | tee -a "${logfile}"
# I pause for 5 seconds so I don't get banned by zapper.fi
sleep 5
done
A few notes:
-
As usual in crypto, don’t run this script (or any script you find on the internet) if you don’t know what you are doing
-
It is meant to run on a Linux machine where the classical grep, printf, tee, sleep, … binaries are installed by default. The only missing binaries could be jq and curl (see “dependencies” within the script)
-
I tried to spot an “hodler” address for each pool (an address that deposited a long time ago and didn’t touch the funds). Then, the script simply monitors the net position of the address in the pool (via the zapper.fi API) and logs it in a logfile
-
On my side, I have a cron job running this script every 10x minutes. For the moment, I am pasting the logs manually in this spreadsheet fragola_apr_analysis - Google Sheets once in a while. But it can probably be fed automatically via the google API if more people are interested. There isn’t much data yet but it will grow slowly and will eventually allow us to average the APR’s on different timespans (daily, weekly, monthly for example). Unfortunately, I have no control over the hodler addresses I chose for each pool, so any dataset could be interrupted if the holder decides to deposit more or withdraw. If the community likes this “dirty” monitoring solution, we might vote for having the treasury deposit $10 in every pool and never touch these positions, so we have an easy fix to this problem.
Cheers
Zazka
. Note I am now logging every 20x minutes (instead of every 10x minutes initially) because the script freezes the spreadsheet for a few seconds when publishing (so it disturbs the viewers less often). Also, I will limit the spreadsheet to 1,000x rows but my publishing script is ready to purge the logs when we surpass that threshold. I’ll be using a dirty trick: keep the oldest log (1x row), keep the last 24 hours of logs (72x rows) and purge every 10th log in-between until reaching a maximum of 1,000 rows.