Fragola APR logs + google sheet analysis

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

7 Likes

Nice work! I’ve been running this against some positions and it’s working as you described. I agree that people are used to the farming rewards concept to watch their positions grow as the “rewards pile” next to their LP position gets bigger. It’s easier to understand and keeps investors interest and attention on their position. It’s less opaque or at least easier to understand.

That said my understanding is that the number of PLPs that you have invested represent your share of the pool. You won’t get rewards to the like farming but your individual PLPs themselves will become more valuable.

This new value of your PLP (which could be used to calculate profit/growth) is seen when you go to “withdraw”. You aren’t given more PLPs when you withdraw but they shoudl be worth more. This calculation is obfuscated because the underlying asset prices themselves will have changed. When calculating APR we need to account for that I would imagine. Your assets could drop 50% each but your APR could technically be positive. (It’s not popsicle’s problem if the assets tank outside the pool)

The script you wrote pulls the underlying position (number of each asset point in time owned and their prices) but it doesn’t give you an up-to-date value of the SLP itself. Right? Please correct me if I’m wrong here.

How can we get the new PLP value itself while factoring out the price change of the individual assets?

1 Like

Thanks!

Correct. In fact, this is exactly what I used to do manually. But I got tired so I decided to code this little script to automate this process.

It’s easy:

  • step1: calculate the new balance based on the original prices (apply the prices of timestamp1 to the token quantities of timestamp2)
  • step2: Net gain = result_of_step1 - balance_of_timestamp1

I added an “example_APR” sheet at the end of fragola_apr_analysis - Google Sheets so you can check the formulas.

1 Like

I realized after I replied you probably were using row one as “inception” and basing it off that. I should have noticed that! Thanks for the detailed explanation. This is great - I will continue to run this (cron job every 6 hours) and see how this works out. If I add anything I will let you know.

I think something like this would be a very useful addition to the UI on Popsicle. Adding to your position with an additional investment into the same PLP would throw off the calculation but this type of clarity is important.

Much appreciated!

I think the devs are working on it, and they probably are doing it the proper way: by querying the blockchain, factoring out withdraws/deposits, and factoring out the price changes via the historical data from Coingecko. This is harder to implement but it allows you to calculate any passed gain between 2x timestamps at any time.

On the other hand, my “dirty” solution is only able to calculate gains as from the date it started logging. And you understood it correctly: the dataset of a pool can be invalidated at anytime if the monitored address move funds (yet this can be fixed with $10 deposits as stated in my first post). Anyway, this is only a temporary solution, and my main goal was to oppose real data to APR critiques (and hopefully calm down the haters in Discord).

By the way, the spreadsheet is now fed automatically via gspread (Python API for Google Sheets), so the data is in real time now :sunglasses:. 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.

I’m clearly playing catch-up and missed your $10 deposit point in your first post. Good idea. Hey, sometimes simple solutions like that can be powerful and useful.

Great work - I will check out the google sheets you have up there to keep an eye on things!

TL;DR: I added Polygon to the script + I added some features to the spreadsheet + comparing the methods to calculate the APR

Hello Isvikingers,

here is a quick update.

I updated the script to monitor Polygon pools too

Here is the new version:

#!/bin/bash

### Credits ###
# This bash script monitors the net position of Popsicle Sorbetto Fragola pools
# version: 1.01
# credits: zazka
#################

### Dependencies ###
# curl (see https://curl.se/)
# jq (see https://stedolan.github.io/jq/)
#################

### Constants ###
SCRIPT_PATH="$( cd "$(dirname "$0")" >/dev/null 2>&1 ; pwd -P )"
ARRAY_PLP_POOLS=(ethereum_AXS-WETH_0.3pct ethereum_DYDX-WETH_0.3pct ethereum_ELON-WETH_1pct ethereum_ENS-WETH_0.3pct ethereum_FTM-WETH_1pct ethereum_MIM-USDC_0.05pct ethereum_MIM-UST_0.05pct ethereum_SAND-WETH_0.3pct ethereum_SHIB-WETH_0.3pct ethereum_SHIB-WETH_1pct ethereum_SPELL-WETH_0.3pct ethereum_USDC-UST_0.05pct ethereum_USDC-WETH_0.05pct ethereum_USDC-WETH_0.3pct ethereum_USDT-USDC_0.01pct ethereum_WBTC-USDT_0.3pct ethereum_WBTC-WETH_0.05pct ethereum_WBTC-WETH_0.3pct ethereum_WETH-ICE_0.3pct ethereum_WETH-USDT_0.05pct ethereum_WETH-USDT_0.3pct polygon_MATIC-USDC_0.3pct polygon_MATIC-USDT_0.3pct polygon_MATIC-WETH_0.05pct polygon_MATIC-WETH_0.3pct polygon_USDC-USDT_0.05pct polygon_USDC-WETH_0.05pct polygon_USDC-WETH_0.3pct)
# I skip ethereum_MIM-USDT_0.05pct because I don't have a user_address for this pool

declare -A MAP_CONSTANTS
## user addresses ##
# ethereum
MAP_CONSTANTS[user_address_ethereum_AXS-WETH_0.3pct]="0x91FC92251B79402309415Ceb7ACa12cd4951E227"
MAP_CONSTANTS[user_address_ethereum_DYDX-WETH_0.3pct]="0x2A44FCB5aA315c6718a2759095Fa351B065D5971"
MAP_CONSTANTS[user_address_ethereum_ELON-WETH_1pct]="0x3389E1eF5241f36b4355fE60c1628f7b63744896"
MAP_CONSTANTS[user_address_ethereum_ENS-WETH_0.3pct]="0x537655Be6EB860f801666D11e01B79a402Fac673"
MAP_CONSTANTS[user_address_ethereum_FTM-WETH_1pct]="0x973Be491406BDD307179D72d599C015d15e7d200"
MAP_CONSTANTS[user_address_ethereum_MIM-USDC_0.05pct]="0xdf938d253a6fb2845eb4b96f27cf8b3d61216b93"
MAP_CONSTANTS[user_address_ethereum_MIM-USDT_0.05pct]="???"
MAP_CONSTANTS[user_address_ethereum_MIM-UST_0.05pct]="0x4f96f50edb37a19216d87693e5db241e31bd3735"
MAP_CONSTANTS[user_address_ethereum_SAND-WETH_0.3pct]="0x7670af152BF001D098BA7F7d58aa156B03644486"
MAP_CONSTANTS[user_address_ethereum_SHIB-WETH_0.3pct]="0x415B67BEEF3B502367652DDED95DD02D273cb94c"
MAP_CONSTANTS[user_address_ethereum_SHIB-WETH_1pct]="0x32844a824F458f24878515923F1FA120b6b711F2"
MAP_CONSTANTS[user_address_ethereum_SPELL-WETH_0.3pct]="0xA37EC72f16b09664B633213dEfdc0fC077C225eD"
MAP_CONSTANTS[user_address_ethereum_USDC-UST_0.05pct]="0xad8F72A7612Bb91B2dfaB09E54464aaA5150914E"
MAP_CONSTANTS[user_address_ethereum_USDC-WETH_0.05pct]="0xf24f55a787cc5d512293760335c5e98414b4f74e"
MAP_CONSTANTS[user_address_ethereum_USDC-WETH_0.3pct]="0xF2cdD8b147802a07F862C9dc125190e0653795a2"
MAP_CONSTANTS[user_address_ethereum_USDT-USDC_0.01pct]="0xC2F1D1cde6CAec9b7bDa98B00E8e3d63a05d1870"
MAP_CONSTANTS[user_address_ethereum_WBTC-USDT_0.3pct]="0x355240dad9a5c9A320d07e4A31446F4F4c832fba"
MAP_CONSTANTS[user_address_ethereum_WBTC-WETH_0.05pct]="0x319d84CAe0F06F1f73B722623e08cDfc54Cfd30a"
MAP_CONSTANTS[user_address_ethereum_WBTC-WETH_0.3pct]="0xcf09abdbf62a6905f2a8a38c893f1718e9a93160"
MAP_CONSTANTS[user_address_ethereum_WETH-ICE_0.3pct]="0xFd1916343d7D2Ec8C8d8ee18055bD551137E1a0e"
MAP_CONSTANTS[user_address_ethereum_WETH-USDT_0.05pct]="0xbd1f7d88c76a86c60d41bddd4819fae404e7151e"
MAP_CONSTANTS[user_address_ethereum_WETH-USDT_0.3pct]="0x60be6061804402be92fbef1fc3027481b58a4dd9"
# polygon
MAP_CONSTANTS[user_address_polygon_MATIC-USDC_0.3pct]="0xaea2119432b4401f106379cdaab0f728ab7efd17"
MAP_CONSTANTS[user_address_polygon_MATIC-USDT_0.3pct]="0xabd4c34cc6bb50f49b633ed82ea9a3bb44cbbcec"
MAP_CONSTANTS[user_address_polygon_MATIC-WETH_0.05pct]="0x027e54dcc0c3e34b8c4f0a1932aa81a0fe22645e"
MAP_CONSTANTS[user_address_polygon_MATIC-WETH_0.3pct]="0x70c9666b338795ddab8f4bd67f580b1d9234b8dc"
MAP_CONSTANTS[user_address_polygon_USDC-USDT_0.05pct]="0x027e54dcc0c3e34b8c4f0a1932aa81a0fe22645e"
MAP_CONSTANTS[user_address_polygon_USDC-WETH_0.05pct]="0xdd557872b1289fbc19f28cae6eea6e93a032a4aa"
MAP_CONSTANTS[user_address_polygon_USDC-WETH_0.3pct]="0x427197b1fb076c110f5d2bae24fb05fed97c0456"

## fragola addresses ##
# ethereum
MAP_CONSTANTS[fragola_address_ethereum_AXS-WETH_0.3pct]="0xa7053782dC3523D2C82B439Acf3f9344Fb47b97f"
MAP_CONSTANTS[fragola_address_ethereum_DYDX-WETH_0.3pct]="0xd2C5A739ebfE3E00CFa88A51749d367d7c496CCf"
MAP_CONSTANTS[fragola_address_ethereum_ELON-WETH_1pct]="0xCeBAB89EaBE767f637E36EA6a54e46F0c16862B3"
MAP_CONSTANTS[fragola_address_ethereum_ENS-WETH_0.3pct]="0x36e9B6e7FADC7b8Ee289c8A24Ad96573cda3D7D9"
MAP_CONSTANTS[fragola_address_ethereum_FTM-WETH_1pct]="0x949FDF28F437258E7564a35596b1A99b24F81e4e"
MAP_CONSTANTS[fragola_address_ethereum_MIM-USDC_0.05pct]="0x4b5E4e508B268A795988354d3689a0d983eBaD7B"
MAP_CONSTANTS[fragola_address_ethereum_MIM-USDT_0.05pct]="0xe2F04B543d9Fe57D2333d9827fdf188424b20623"
MAP_CONSTANTS[fragola_address_ethereum_MIM-UST_0.05pct]="0x74450a224359Fe6a8C8b6cA5038F22c5C7f69F3C"
MAP_CONSTANTS[fragola_address_ethereum_SAND-WETH_0.3pct]="0xF4f542E4b5E2345A1f2D0fEab9492357Ebc5c8f4"
MAP_CONSTANTS[fragola_address_ethereum_SHIB-WETH_0.3pct]="0xa0273C10b8A4BF0bDC57cb0bC974E3A9d89527b8"
MAP_CONSTANTS[fragola_address_ethereum_SHIB-WETH_1pct]="0x495410B129A27bC771ce8fb316d804a5686B8Ea7"
MAP_CONSTANTS[fragola_address_ethereum_SPELL-WETH_0.3pct]="0x5C08A6762CAF9ec8a42F249eBC23aAE66097218D"
MAP_CONSTANTS[fragola_address_ethereum_USDC-UST_0.05pct]="0x71fd405e9C2f55522A73911b4A2F39CD80E06051"
MAP_CONSTANTS[fragola_address_ethereum_USDC-WETH_0.05pct]="0x9683D433621A83aA7dd290106e1da85251317F55"
MAP_CONSTANTS[fragola_address_ethereum_USDC-WETH_0.3pct]="0xaE7b92C8B14E7bdB523408aE0A6fFbf3f589adD9"
MAP_CONSTANTS[fragola_address_ethereum_USDT-USDC_0.01pct]="0x989442D5cCB27E7931095B0f3165c75a6def9bc3"
MAP_CONSTANTS[fragola_address_ethereum_WBTC-USDT_0.3pct]="0xd2EF15af2649CC46e3E23B96563a3d44ef5E5A06"
MAP_CONSTANTS[fragola_address_ethereum_WBTC-WETH_0.05pct]="0xBE5d1d15617879B22C7b6a8e1e16aDD6d0bE3c61"
MAP_CONSTANTS[fragola_address_ethereum_WBTC-WETH_0.3pct]="0x212Aa024E25A9C9bAF5b5397B558B7ccea81740B"
MAP_CONSTANTS[fragola_address_ethereum_WETH-ICE_0.3pct]="0xFF338D347E59d6B61E5C69382915D863bb22Ef2f"
MAP_CONSTANTS[fragola_address_ethereum_WETH-USDT_0.05pct]="0x8d8B490fCe6Ca1A31752E7cFAFa954Bf30eB7EE2"
MAP_CONSTANTS[fragola_address_ethereum_WETH-USDT_0.3pct]="0xa1BE64Bb138f2B6BCC2fBeCb14c3901b63943d0E"
# polygon
MAP_CONSTANTS[fragola_address_polygon_MATIC-USDC_0.3pct]="0x5C08A6762CAF9ec8a42F249eBC23aAE66097218D"
MAP_CONSTANTS[fragola_address_polygon_MATIC-USDT_0.3pct]="0xaE7b92C8B14E7bdB523408aE0A6fFbf3f589adD9"
MAP_CONSTANTS[fragola_address_polygon_MATIC-WETH_0.05pct]="0xa0273C10b8A4BF0bDC57cb0bC974E3A9d89527b8"
MAP_CONSTANTS[fragola_address_polygon_MATIC-WETH_0.3pct]="0x949FDF28F437258E7564a35596b1A99b24F81e4e"
MAP_CONSTANTS[fragola_address_polygon_USDC-USDT_0.05pct]="0x9683D433621A83aA7dd290106e1da85251317F55"
MAP_CONSTANTS[fragola_address_polygon_USDC-WETH_0.05pct]="0xa7053782dC3523D2C82B439Acf3f9344Fb47b97f"
MAP_CONSTANTS[fragola_address_polygon_USDC-WETH_0.3pct]="0xd2C5A739ebfE3E00CFa88A51749d367d7c496CCf"
################

# 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")
network="${plp_pool%%_*}"
json_popsicle=$(curl --silent "https://api.zapper.fi/v1/balances?addresses%5B0%5D=${MAP_CONSTANTS[user_address_${plp_pool}]}&nonNilOnly=true&networks%5B0%5D=${network}&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="${SCRIPT_PATH}/${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
if [ $(echo "${total_balance_USD} > 0" | bc -l) ]; then
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}"
fi

# I pause for 5 seconds so I don't get banned by zapper.fi
sleep 5

done

I added a few more features to the spreadsheet

  • You can now visually check the evolution of the positions in the pools via charts. Each pool has its own chart. The blue line reflects the balance in USD of the monitored address in the pool, while the grey line reflects the balance in USD “if hodled in wallet” (eg if the liquidity provider had kept the tokens in his wallet instead of providing liquidity in Fragola). If the blue line is over the grey line: the return is positive. If the blue line is under the grey line: the return is negative.

  • I added direct links to etherscan for the monitored addresses in the pools. So in case a result looks crazy, you can now quickly check if the monitored address moved funds (thus invalidating the calculations). Note you would notice that from the chart too. From my experience during the last 40 days of monitoring, a few of the addresses I chose to monitor moved funds, but they withdraw 100%. So the script just stopped publishing for these and I had to chose another address to monitor to start over.

  • I updated the “example_APR” sheet with clear explanations of the method. Take a look, they are very simple maths

Different methods to calculate the APR

Let me use an analogy: imagine you launched a Playmobil pirate ship in the high sees and you want to know where it went (see The Adventure So Far — TRACK OUR ADVENTURE)

Solution1

Solution1: you track your Playmobil pirate ship by reading the weather reports everyday and calculate where it should have gone based on the wind direction and speed.

I understand this is the basic approach of “feeGrowthInside0LastX128” and “feeGrowthInside1LastX128” in the Fragola contracts which return the generated fees for token0 and token1. Then you annualize the quotient of generated_fees/TVL (Total Value Locked) to get the APR.

This is the approach used by UNI-V2 farms (using farming rewards instead of generated fees). It is not 100% accurate as it doesn’t include the IL (Impermanent Loss). This is why uniswaproi.com / liquidityfolio.com emerged so that LPers (Liquidity Providers) could compare how much they really earned (" how much do I have if I exit now " versus " how much would I have now if I just hodled the tokens in my wallet instead of providing liquidity "). On UNI-V2 farms, the farming rewards usually exceed the IL so people don’t really pay attention. For example, an LPer might earn a “real” 30% instead of the 40% advertised return but he probably won’t notice. Also, the IL will be cancelled if the price goes back to the level when the LPer entered in the pool (thus the adjective “impermanent”).

On UNI-V3, the LPer provides liquidity in a specific range in order to concentrate the liquidity. If the price goes off range, the LPer can chose to wait for the price to go back in range, or to “rerange” to start earning fees again. But a rerange means swapping enough tokens to get back in range. This is effectively converting IL to Permanent Loss (PL). It also implies absorbing the price impact / slippage of the swap. An “always rerange” strategy (instead of waiting for the price to go back in range) also means you are always buying high and selling low, so the rerange thresholds must be chosen carefully. This is the whole business case of Fragola: having a good strategist who decides the ranges, when to rerange, and pooling together to reduce the rerange fees for every participant, while sharing all the trading fees.

The generated_fees/TVL method might be OK for UNI-V2, but it is probably not fair for UNI-V3, as explained by the community member who posted this proposal

Solution2

Solution2 = you track your Playmobil pirate ship with Solution1 + you include all external elements you can think about (ocean currents + tides + you check marinetraffic.com to add the probability it was dragged by a container ship + you check this polar bear tracker to add the probability it was attacked by a polar bear, etc…)

This is the courageous approach used by 0xDanger, another one of our great community members. From his latest work and my conversations with him, I understand his method vastly improves the basic generated_fees/TVL system by including the overall impact of reranges in USD terms at the moment they occurred. You might understand how herculean this task is by analyzing the methodology used in this controverted report from November summarized on this cointelegraph article.

Solution3

Solution3 = you track your Playmobil pirate ship with a GPS tracker

It sounds like an obvious option, but the big caveat is it would work only when the GPS tracker is turned on… This is the solution I used with my little bash script (to log) and the google spreadsheet (to analyze the data) because I felt like I was not capable of coding solution2. By logging the data continuously, I am now able to average the APR over different periods (7 days, 72 Hours and 24 Hours currently). But you can use the same very simple maths on many passed transactions before the script started logging:

– For an address who deposited in a Fragola pool and didn’t move, add or withdraw funds afterwards:

  • Step1: Find the deposit transaction on the explorer (https://etherscan.io / https://polygonscan.com) and note the token quantities deposited

  • Step2: go to zapper.fi and note the value in USD of the position

  • Step3: calculate the “hodling value” (what the address would have now if it didn’t deposit in Fragola) by multiplying the tokens quantities of step1 by their price in USD of today

=> You earned money if your position on zapper.fi is higher than your hodling value. Else, you lost money

– For an address who deposited and withdrawn in a Fragola pool in the past (and didn’t move, add or partially withdrawn funds in-between):

  • Step1: Find the deposit transaction on the explorer and note the token quantities deposited

  • Step2: Find the withdraw transaction on the explorer and note the token quantities withdrawn

  • Step3: Multiply the token quantities of step1 and step2 by their price in USD at the time of withdrawn

=> compare the 2 values to understand if this address earned or lose by providing liquidity in Fragola

Where this method can’t help is if you ask me: what was the APR in a pool between, for example, November 1st and November 4th because I was not logging back then (eg the GPS tracker was turned off). I would have to find an address who deposited on November 1st and withdrawn on November 4th to answer this question. Another problem is the fact that a dataset is invalidated and reset when a monitored address move funds (though this can be fixed with a “community” deposit of $10 in each pool as explained in my first post).

Conclusion

The method I use (solution3) isn’t perfect but it gives reliable data to calculate the APR’s. At the end of the day, LPers just want to know if they earned money by providing liquidity in Fragola or if they would have been more profitable simply holding their tokens in their wallet.

I believe the maths are so simple that it makes the data opposable to other methods that try to retrocalculate returns from any date to any other date in the past. I think such a method should be tested against the return of an address that deposited and withdrawn in the past, by applying the model from the exact time of deposit to the exact time of withdraw. And it should get the exact same “LPing vs hodling” return if everything (permanent loss, price impact of swaps during reranges, JIT attacks, etc…) was taken into account. Hopefully 0xDanger, or another very motivated community member manages it and we would have a wonderful solution

Cheers,
Zazka

2 Likes

@zazka thank you, as always great analysis. I agree that the best benchmark is comparison with just holding two assets, another good one is comparison with uniV2 liquidity providing (or sushi, or whatever can give you sustainable gains like auto compounded uniV2 LP with third party service), because these benchmarks excludes volatility of USD value of assets.

Why are some of the rows missing? Here’s what I’m seeing:

It looks like he stopped his program from running. I would wager he gave up, is quite frustrating here.