> ## Documentation Index > Fetch the complete documentation index at: https://docs.allium.so/llms.txt > Use this file to discover all available pages before exploring further. # PNL > Fetch block-level PNL across wallets. ## Blockchain Coverage We support PNL data for the following blockchains: | Blockchain | Balances Model | | -------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | EVM-Compatible Blockchains | The `crosschain.assets.fungible_pnl_evm` model offers a historical record of fungible asset pnls at block-level granularity. Chain-specific tables are available at `.assets.fungible_pnl`. | | Solana | The `solana.assets.pnl` model offers a historical record of SOL and spl tokens (no NFTs) pnls at transaction-level granularity. Refer to [here](/historical-data/supported-blockchains/solana/solana/assets/pnl) for more details. | # PNL calculation The PNL (Profit and Loss) calculation uses the **average-cost methodology** to determine realized and unrealized gains/losses for fungible assets. This approach provides a fair and consistent way to calculate performance across multiple transactions and time periods. ## Average-Cost Methodology The average-cost method calculates PNL by maintaining a running average of the cost basis for each asset position. The methodology tracks cumulative costs and quantities to compute accurate profit and loss metrics. ### Key Fields The PNL model provides several important fields for analysis: * **`average_cost`**: The current weighted average cost per token unit, calculated as `cumulative_costs / cumulative_quantities` * **`cumulative_costs`**: The total USD cost basis remaining after accounting for sales (reduces proportionally when tokens are sold) * **`cumulative_quantities`**: The remaining token quantity after accounting for sales * **`realized_pnl`**: The cumulative realized profit/loss from all sales transactions for this token * **`unrealized_pnl`**: The unrealized profit/loss based on the token price at the time of the transaction * **`unrealized_pnl_latest`**: The current unrealized profit/loss using the latest available token price ### Core Principles 1. **Cost Basis Averaging**: When acquiring additional tokens, the new average cost becomes the weighted average of previous holdings and the new acquisition. 2. **Proportional Cost Reduction**: When disposing of tokens, both `cumulative_costs` and `quantities` are reduced proportionally, keeping the `average_cost` unchanged. 3. **Transaction-Level Tracking**: The average cost basis and PNL metrics are updated with every transaction that changes the token balance. ## Calculation Steps ### 1. **Purchase Transactions** (transaction\_type: 'purchase' or 'first\_purchase') When tokens are acquired: ``` New Cumulative Costs = Previous Cumulative Costs + (Tokens Purchased × Token Price USD) New Quantities = Previous Quantities + Tokens Purchased New Average Cost = New Cumulative Costs / New Quantities ``` **Example:** * Existing: 100 ETH, cumulative\_costs = $200,000, average_cost = $2,000 * New purchase: 50 ETH at $2,400 = $120,000 * New cumulative\_costs: $200,000 + $120,000 = \$320,000 * New quantities: 100 + 50 = 150 ETH * New average\_cost: $320,000 / 150 = $2,133.33 per ETH ### 2. **Sale Transactions** (transaction\_type: 'sale') When tokens are sold: ``` Realized PNL This TX = Tokens Sold × (Sale Price - Average Cost Before Sale) New Cumulative Costs = Previous Cumulative Costs - (Tokens Sold × Average Cost Before Sale) New Quantities = Previous Quantities - Tokens Sold Average Cost = New Cumulative Costs / New Quantities (unchanged from before sale) ``` **Example:** * Before sale: 150 ETH, cumulative\_costs = $320,000, average_cost = $2,133.33 * Sell: 30 ETH at \$2,500 per ETH * Realized PNL: 30 × ($2,500 - $2,133.33) = \$11,000.10 gain * New cumulative\_costs: $320,000 - (30 × $2,133.33) = \$256,000.10 * New quantities: 150 - 30 = 120 ETH * New average\_cost: $256,000.10 / 120 = $2,133.33 per ETH (unchanged) ### 3. **Unrealized PNL Calculations** **At Transaction Time:** ``` Unrealized PNL = Current Balance × (Token Price USD - Average Cost) ``` **At Latest Price:** ``` Unrealized PNL Latest = Current Balance × (Latest Token Price - Average Cost) ``` **Example:** * Current holdings: 120 ETH at average\_cost of \$2,133.33 * Token price at transaction: \$2,600 * Latest token price: \$2,800 * Unrealized PNL (transaction time): 120 × ($2,600 - $2,133.33) = \$56,000.40 * Unrealized PNL Latest: 120 × ($2,800 - $2,133.33) = \$80,000.40 This query shows how to get daily PNL snapshots for a wallet ```sql theme={null} with pnl as ( select *, -- take last record per second granularity (asof join can only compare by time) row_number() over (partition by chain, address, token_address, block_timestamp order by block_number desc) as rank_in_timestamp_secs, row_number() over (partition by chain, address, token_address order by block_timestamp, block_number) as global_rank, from crosschain.assets.fungible_pnl_evm where address in ( lower('...') ) ), time_axis as ( select d.DATE_TIMESTAMP::varchar as description, d.DATE_TIMESTAMP as activity_date, chain, address, token_address from common.utility.dates d cross join (select distinct chain, address, token_address from pnl) where d.DATE_TIMESTAMP >= (Select date(min(block_timestamp)) from pnl) and d.DATE_TIMESTAMP <= current_date + 1 ), anchors as ( select time_axis.*, pnl.* exclude (chain, address, token_address) from time_axis asof join (select * from pnl where rank_in_timestamp_secs=1) pnl match_condition (time_axis.activity_date > pnl.block_timestamp) on pnl.address = time_axis.address and pnl.token_address = time_axis.token_address and pnl.chain = time_axis.chain ), unioned as ( select 'first' as description, block_timestamp as activity_date, chain, address, token_address, pnl.* exclude (chain, address, token_address) from pnl where global_rank = 1 union all select * from anchors ) select * from unioned where token_type is not null and balance is not null and usd_exchange_rate is not null order by activity_date, chain, address, token_address ``` This query shows how to get aggregated PNL across multiple address and/or tokens for portfolio-level analysis. ```sql theme={null} with latest_pnl as ( select * from crosschain.assets.fungible_pnl_evm where address in ( lower('...') ) qualify row_number() over (partition by chain, address, token_address order by block_timestamp desc, block_number desc) = 1 ), unioned as ( -- Aggregated PNL across all tokens select address, count(distinct token_address) as total_tokens_held, count(distinct chain) as chains_used, null as token_address, null as token_symbol, sum(realized_pnl) as total_realized_pnl, sum(unrealized_pnl_latest) as total_unrealized_pnl, sum(realized_pnl + unrealized_pnl_latest) as total_pnl, sum(usd_balance_latest) as total_portfolio_value, max(block_timestamp) as last_activity_timestamp from latest_pnl group by all union all -- Per-token PNL select address || ' - ' || coalesce(token_symbol, 'Unknown') as address, 1 as total_tokens_held, 1 as chains_used, token_address, token_symbol, sum(realized_pnl) as total_realized_pnl, sum(unrealized_pnl_latest) as total_unrealized_pnl, sum(realized_pnl + unrealized_pnl_latest) as total_pnl, sum(usd_balance_latest) as total_portfolio_value, max(block_timestamp) as last_activity_timestamp from latest_pnl group by all ) select * from unioned order by address, token_address ``` ## Table Columns Unique Key: `chain, address, token_address, unique_id` | Column Name | Description | | -------------------------- | ------------------------------------------------------------------------------------------------ | | `chain` | The EVM-compatible blockchain where the assets are held. | | `token_type` | The type of token (e.g., 'native', 'erc20'). | | `address` | The wallet address holding the token. | | `token_address` | The contract address of the token. Native tokens are represented with zero address. | | `token_name` | The name of the token. | | `token_symbol` | The symbol of the token. | | `balance` | The current token balance after this transaction. | | `prev_balance` | The token balance before this transaction. | | `balance_change` | The change in token balance from this transaction (balance - prev\_balance). Can be negative. | | `usd_exchange_rate` | The USD exchange rate for the token at the time of the transaction. | | `usd_balance` | The current token balance converted to USD at transaction time. | | `usd_balance_change` | The change in USD balance from this transaction. Can be negative. | | `usd_exchange_rate_latest` | The latest available USD exchange rate for the token. | | `usd_balance_latest` | The current token balance converted to USD using the latest exchange rate. | | `transaction_type` | The type of transaction: 'first\_purchase', 'purchase', 'sale', or 'no\_change'. | | `tokens_purchased` | The number of tokens purchased in this transaction (0 for sales). | | `tokens_sold` | The number of tokens sold in this transaction (0 for purchases). | | `average_cost` | The current weighted average cost per token unit after this transaction. | | `cumulative_costs` | The total USD cost basis remaining after accounting for proportional reductions from sales. | | `cumulative_quantities` | The total token quantity remaining after accounting for sales. | | `realized_pnl` | The cumulative realized profit/loss from all sales transactions for this token up to this point. | | `realized_pnl_this_tx` | The realized profit/loss from this specific transaction (null for purchases). | | `unrealized_pnl` | The unrealized profit/loss based on the token price at the time of this transaction. | | `unrealized_pnl_latest` | The unrealized profit/loss based on the latest available token price. | | `block_timestamp` | The timestamp of the block containing this transaction. | | `block_number` | The block number containing this transaction. | | `block_hash` | The hash of the block containing this transaction. | | `unique_id` | A unique identifier for this PNL record. |