Hi everyone, I’m Michael from Llama. As part of Llama’s treasury management grant, we just launched our Aave treasury analytics dashboard on Dune Analytics (you may have already seen an earlier version surface on Twitter). The goal of this dashboard is to provide the community with an easy-to-use resource for diving in to Aave’s treasury balances and growth, revenue and expenses, token holdings, and see where the funds are coming from/ going to.
We’re planning to continue to build analyses and create data sets for the community that are useful for governance proposals, treasury decisions, and better understanding how people are using Aave. If you have any feedback or questions, please leave a comment or send me a DM.
Thank you to the Aave Discord for helping answer my questions while building this!
Dashboard Overview
Current balances for the Reserve Factor Addresses for Ethereum (v1, v2), Polygon, and the Ecosystem Reserve on Ethereum.
Money In, Money Out, aToken Interest, Liquidity Mining Rewards Accrued, Price Change of Token Holdings, and Total Reserve Factor Balances growth over time.
Current Reserve Factor balances and distribution of holdings by token.
Revenue Sources (e.g., Flash Loans, Reserve Interest) and Revenue by Token.
Expense Transactions and USD Amount by Destinations.
Track the Reserve Factor’s liquidity incentive rewards earned by holding aTokens.
Quarterly Statements for the trailing four quarters from any date.
Ability to set time granularity (i.e. monthly, daily), trailing date range, and go “back in time” to a previous reference date. Default is “trailing 90 days.” (Note that re-running the queries will take ~3 minutes)
Note: aToken interest and liquidity incentive reward rates are approximated by average daily rates.
Early Insights
“Why Now?” for treasury management: Aave’s Ethereum Reserve Factor reached $9M USD on July 31st, up 3x from the previous quarter ended May 3rd ($2.96M), and 55x Year-to-Date ($163k on Jan 1st). Price change had minimal impact on the Ethereum Reserve, since > 90% is held in stables. But the Ecosystem Reserve, which held $800M+ USD in AAVE tokens, was more volatile with monthly swings due to price as high as +$628M USD and as low as -$350M USD in the past year.
Sustained Revenue on Ethereum, but Reduced Incentives Hurt Polygon: For the past four months (April - July), Aave’s Ethereum Reserve has earned > $1M USD in revenue, peaking at $3M revenue in May (based on token prices when earned). The Polygon Reserve crossed $1M revenue in May and June, but fell to $670k in July. Daily Polygon revenue fell 58% after MATIC token incentives were reduced on June 18th ($60.5k/day 7-day avg before, $25.1k after).
aTokens Make the Treasury Productive: V2 earns protocol revenue on reserve state updates in the form of Aave’s interest-bearing aTokens. In the trailing quarter May 4th - Aug 1st, Aave’s Ethereum Reserve earned $41k in interest (0.7% of total revenue), and the Polygon Reserve earned $11.8k (0.3%). Since liquidity incentives launched on April 26th, the Ethereum Reserve has earned ~$25k in AAVE tokens (Polygon liquidity incentive data WIP).
This is awesome work, thanks a lot for putting that together, super helpful
Would it be possible to have a bit more granularity for the Money Out Transactions, or at least a short explanation of what each category correspond to (e.g. Aave V2 Deployer, Kyber V2 Network, etc.)?
How do you calculate the interests? I’m usually unable to capture aToken accrual on Dune. I have looked at your code and saw you have created some special tables with the APRs.
Good points on the ‘Money Out.’ It’s probably worth going another step further on the Deployer to pull where that is sending fund to. The Kyber transactions were all for LEND burns in v1.
There’s definitely an open question/thought I have of what the best way to represent swaps would be.
Yeah, calculating interest and liquidity incentive rates was a whole challenge in itself. I was thinking about doing a video walkthrough, or at least write up documentation for it.
In short:
Calculating Interest Rates
I created a daily interest rate table, just for query simplicity: Dune Analytics
This uses aave_v2."LendingPool_evt_ReserveDataUpdated" in Dune to pull the "liquidityRate" for each aToken by time, which was the interest rate, per this: LendingPool - Developers
Approximated the interest rate by taking the average of each day. If we did this by block, I don’t think the query would ever fully run haha, but maybe eventually we could get to that granularity. I converted the yearly APY rate to daily APR with this ((1+interest_rate_ray)^(1.0/365.0)-1)
Compound Interest
We probably could’ve used a general compound interest formula to calculate the interest earned between two points, but for purposes of the dashboard and reporting, we wanted to calculate the interest earned each day.
If we were using scripting language we’d do something like loop, or in Excel pull the previous row’s balance + earned interest as our starting balance. We could use a recursive query in SQL to follow the Excel-type method.
In the recursive part of the query: daily_balances starting line 120 here Dune Analytics , we essentially select our first day of data, then union that with the next day’s data until we run out of days. But since it’s ‘recursive’, the unioned rows can self-join with the previous row’s data, in order to do something like:
yesterday’s balance (balance of the previous row) + today’s difference of tokens in and out = today’s pre-interest balance
For the next set: today’s ending balance = tomorrow’s ‘yesterday’s balance’
This feels kind of confusing in words, but I had no idea SQL could even do recursion until I did this. A few things online were helpful, I think this is the closest to it: Calculating compound interest in SQL | Teradata Downloads
