Spells

Introduction

Unlock the power of Balancer data with our meticulously crafted Dune Spells! Balancer Labs' data team is dedicated to the relentless pursuit of excellence, continuously refining and updating our Dune Spells to deliver the most accurate, insightful, and up-to-date analytics for the Balancer community.

Explore the Spellbook

Embark on your journey through the world of Balancer's data by exploring our Spellbook. Visit https://github.com/duneanalytics/spellbook/tree/main/models/balanceropen in new window to delve into the intricacies of our models, gaining access to the very spells that empower your understanding of Balancer's ecosystem.

Here are the updated dashboards:

SpellDescriptionUpstream SpellsChains
balancer_tradesopen in new windowAll trades on Balancer, with information on date, tx_hash, tx_from, tx_to, tokens, amounts, version of Balancer in which the trade happened, the pool and its respective swap feebalancer_v1_ethereum_trades, balancer_v2_[chain]trades, balancer_cowswap_amm[chain]_tradesArbitrum, Avalanche, Base, Ethereum, Gnosis, Optimism, Polygon PoS, Polygon ZkEVM
balancer_liquidityopen in new windowDaily liquidity information for balancer pools, by each token contained in a pool. It is also divided in pool_liquidity_usd, which also includes BPTs (Balancer Pool Tokens) balances and and protocol_liquidity_usd, which excludes BPTs.balancer_v1_ethereum_liquidity, balancer_v2_[chain]liquidity, balancer_cowswap_amm[chain]_liquidityArbitrum, Avalanche, Base, Ethereum, Gnosis, Optimism, Polygon PoS, Polygon ZkEVM
balancer_pools_feesopen in new windowBalancer v2 swap fees stored at the pool level, including information on the transaction where the fee was set.balancer_v2_[chain]_pools_feesArbitrum, Avalanche, Base, Ethereum, Gnosis, Optimism, Polygon PoS, Polygon ZkEVM
balancer_protocol_feesopen in new windowDaily Protocol Fee collected and Treasury Revenue by pool and token.balancer_v2_[chain]_protocol_feesArbitrum, Avalanche, Base, Ethereum, Gnosis, Optimism, Polygon PoS, Polygon ZkEVM
balancer_bpt_supplyopen in new windowBPT supply over time of ComposableStablePools versions 4+balancer_v2_[chain]_bpt_supplyArbitrum, Avalanche, Base, Ethereum, Gnosis, Optimism, Polygon PoS, Polygon ZkEVM
balancer_bpt_pricesopen in new windowBalancer Pool Token (BPT) hourly median price by pool.balancer_v2_[chain]_bpt_pricesArbitrum, Avalanche, Base, Ethereum, Gnosis, Optimism, Polygon PoS, Polygon ZkEVM
balancer_flashloansopen in new windowAll Balancer flashloansbalancer_v2_[chain]_flashloansArbitrum, Avalanche, Base, Ethereum, Gnosis, Optimism, Polygon PoS, Polygon ZkEVM
balancer_transfers_bptopen in new windowBalancer Pool Token (BPT) transfer logs on Balancer,balancer_v2_[chain]_transfers_bptArbitrum, Avalanche, Base, Ethereum, Gnosis, Optimism, Polygon PoS, Polygon ZkEVM
balancer_bpt_supply_changesopen in new windowAll BPTs mints and burnsbalancer_[chain]_token_balance_changesArbitrum, Avalanche, Base, Ethereum, Gnosis, Optimism, Polygon PoS, Polygon ZkEVM
balancer_bpt_supply_changes_dailyopen in new windowDaily Deltas in BPT supplybalancer_[chain]_token_balance_changes_dailyArbitrum, Avalanche, Base, Ethereum, Gnosis, Optimism, Polygon PoS, Polygon ZkEVM
balancer_pools_tokens_weightsopen in new windowToken weights in Balancer’s weighted poolsbalancer_[chain]_pools_tokens_weightsArbitrum, Avalanche, Base, Ethereum, Gnosis, Optimism, Polygon PoS, Polygon ZkEVM
balancer_token_balance_changesopen in new windowAll token adds and removes on Balancer poolsbalancer_[chain]_token_balance_changesArbitrum, Avalanche, Base, Ethereum, Gnosis, Optimism, Polygon PoS, Polygon ZkEVM
balancer_token_balance_changes_dailyopen in new windowDaily Deltas in token balances per poolbalancer_[chain]_token_balance_changes_dailyArbitrum, Avalanche, Base, Ethereum, Gnosis, Optimism, Polygon PoS, Polygon ZkEVM
balancer_ethereum_balancesopen in new windowDaily running cumulative balance for ERC20 tokens on balancer v1 poolsEthereum
balancer_ethereum_vebal_slopesopen in new windowSlope and bias of veBAL per wallet after each balance updateEthereum
balancer_ethereum_vebal_balances_dayopen in new windowDaily balances of veBAL per walletEthereum
balancer_ethereum_vebal_votesopen in new windowRecords of votes for Balancer gauges by provider at each voting roundEthereum
balancer_cowswap_amm_balancesopen in new windowDaily running cumulative balance for ERC20 tokens on Balancer CoWSwap AMM pools poolsArbitrum, Ethereum, Gnosis
labels_balancer_v1_poolsopen in new windowNames Balancer v1 pools, based on tokens and weightslabels_balancer_v1_pools_ethereumEthereum
labels_balancer_v2_poolsopen in new windowNames Balancer v2 pools, based on tokens and weights. Also returns pool type.labels_balancer_v2_pools_Arbitrum, Avalanche, Base, Ethereum, Gnosis, Optimism, Polygon PoS, Polygon ZkEVM
labels_balancer_cowswap_amm_poolsopen in new windowNames Balancer CoWSwap AMM pools, based on tokens and weights. Also returns pool type.labels_balancer_cowswap_amm_pools_Arbitrum, Ethereum, Gnosis
labels_balancer_v2_gaugesopen in new windowNames Balancer v2 gauges, based on their respective blockchain and poollabels_balancer_v2_gauges_

Contribute to the Magic

We invite you to not only explore but also contribute to the magic. As we strive for excellence, collaboration is at the heart of our mission. Your insights, feedback, and contributions are invaluable in shaping the future of Balancer's data analytics. To do so, you can create pull requests to Dune's spellbook or reach out to Balancer Labs' data team directly on Discordopen in new window.

You can leverage our spells with queries such as:

1, 7 and 30 day volume on Balancer

SELECT 
    SUM(amount_usd)/1e6 AS "Volume on Balancer"
    , 1 AS rn 
FROM balancer.trades 
WHERE block_time >= CAST(NOW() AS TIMESTAMP) - INTERVAL '1' DAY 

UNION ALL

SELECT 
    SUM(amount_usd)/1e6 AS "Volume on Balancer"
    , 2 AS rn 
FROM balancer.trades 
WHERE block_time >= CAST(NOW() AS TIMESTAMP) - INTERVAL '7' DAY

UNION ALL

SELECT 
    SUM(amount_usd)/1e6 AS "Volume on Balancer"
    , 3 AS rn 
FROM balancer.trades 
WHERE block_time >= CAST(NOW() AS TIMESTAMP) - INTERVAL '30' DAY
ORDER BY rn ASC

All swaps on the last 24 hours

SELECT 
    block_date
    , tx_hash
    , project_contract_address AS pool_address
    , token_bought_address
    , token_bought_amount
    , token_sold_address
    , token_sold_amount
    , tx_from
    , tx_to
    , amount_usd
    , swap_fee
FROM balancer.trades
WHERE block_time >= now() - interval '24' hour
ORDER BY 1 ASC

Daily TVL by Blockchain

SELECT 
    blockchain
    , CAST(day AS TIMESTAMP) AS day
    , sum(protocol_liquidity_usd) AS chain_tvl
FROM balancer.liquidity x
GROUP BY 1, 2
ORDER BY 2 DESC, 3 DESC

Current TVL by pool, from highest to lowest

SELECT 
    blockchain
    , pool_id
    , pool_symbol
    , sum(pool_liquidity_usd) AS pool_tvl
FROM balancer.liquidity x
WHERE day >= current_date
GROUP BY 1, 2, 3
ORDER BY 4 DESC

Daily Liquidity Utilization

WITH 
    swaps AS (
        SELECT
            date_trunc('day', d.block_time) AS day
            , SUM(amount_usd) AS volume
        FROM balancer.trades d
        )
        GROUP BY 1
    ),

    total_tvl AS (
        SELECT 
            CAST(day as timestamp) as day
            , SUM(protocol_liquidity_usd) AS tvl
        FROM balancer.liquidity
        )
        GROUP BY 1
    )
   
SELECT
    CAST(t.day as timestamp) as day,
    (s.volume)/(t.tvl) AS liquidity_utilization,
FROM total_tvl t
LEFT JOIN swaps s ON s.day = t.day
ORDER BY 1