Portfolio & Risk Automation. Crypto portfolio tracker in Excel: my 6-month experiment

A workbook can calculate allocation drift, realized P&L, drawdown, and exposure by asset. It cannot do so reliably if its price feed is incomplete, its refresh schedule exceeds API limits, or its transaction ledger is manually corrupted. The spreadsheet is not the portfolio system. It is the reporting layer built on top of a data pipeline.

Over six months, the practical result was clear. Excel remains adequate for low-frequency portfolio monitoring: spot holdings, staking positions, DeFi allocations recorded at daily or hourly intervals, and periodic rebalancing. It is structurally unsuitable for tick-level tracking, latency-sensitive hedge management, or liquidation protection. Those require a database, event-driven logic, and exchange or wallet connectivity beyond native Excel.

The useful question is therefore narrower: how far can an Excel crypto portfolio template be pushed before its operational risk exceeds its analytical value?

Native Excel data is insufficient for a real crypto portfolio

Excel’s built-in STOCKS data type is convenient for equities and some major cryptocurrencies. It is not a complete crypto market-data service. Coverage is selective. Altcoins, wrapped assets, LP tokens, lending receipt tokens, and most DeFi positions will not resolve consistently through the native feed.

That creates an immediate portfolio distortion.

If a tracker includes BTC, ETH, and SOL through Excel’s native data type but leaves a lending position, stablecoin farm, or governance token manually priced, the allocation model is not observing the portfolio at one timestamp. It is combining asynchronous prices. During low volatility this may be tolerable. During a market-wide drawdown, it produces false diversification and delayed exposure estimates.

A usable crypto portfolio Excel sheet therefore needs an external data source for at least four categories:

• Spot market prices for liquid exchange-traded assets.
• Token identifiers that remain stable when symbols collide. BTC is unambiguous. Many smaller tickers are not.
• Historical timestamps for transaction valuation and realized P&L.
• Manual valuation fields for assets without a reliable public spot price: LP positions, vesting allocations, locked tokens, or thinly traded assets.

The last category is often ignored. It should not be. A portfolio tracker that pretends every asset has a clean, executable mark is overstating precision.

The workbook becomes useful only when every position has a stated pricing method, a timestamp, and a confidence level.

For a simple spot portfolio, that pricing method can be an API quote. For a Uniswap-style liquidity position, it may be a calculated value from underlying reserves. For a locked allocation, it may be a conservative mark with an explicit liquidity haircut. These are different data types. Treating them as equivalent is a modeling error.

The workbook architecture that survives repeated updates

Most failed crypto tracking spreadsheets place everything on one tab: transactions, prices, formulas, charts, and manually typed corrections. This works until the first schema change, asset rename, or failed refresh. Then the model becomes non-auditable.

A more stable structure separates raw inputs from calculations.

The distinction between Transactions and Positions is critical.

A transaction ledger records events. A positions table calculates inventory. These are not interchangeable. Transfers between wallets should not be recognized as sales. Gas fees should not be mistaken for zero-cost disposals. A staking reward should enter with both a quantity and a valuation timestamp, even if the tax treatment is handled elsewhere.

The minimum transaction schema should include:

1. Timestamp in UTC. Local time causes errors when trades occur near daily boundaries or during daylight-saving changes.

2. Transaction type. Buy, sell, swap, transfer in, transfer out, reward, fee, or adjustment.

3. Asset ID. Use an API-compatible identifier where possible, not only a ticker.

4. Quantity. Signed or unsigned, but consistent throughout the workbook.

5. Quote currency value. Usually USD, USDT, USDC, or a chosen base currency.

6. Fee asset and fee quantity. Network fees paid in ETH, SOL, or another token affect inventory.

7. Venue or wallet. Exchange accounts, cold wallets, and DeFi wallets should remain distinguishable.

8. Reference ID. Exchange order ID, transaction hash, or imported CSV row reference.

This is less elegant than an all-in-one excel crypto portfolio template. It is more robust. The purpose is not visual simplicity. The purpose is to make a wrong number traceable.

CoinGecko and CoinMarketCap: rate limits determine the design

Free API access is sufficient for a low-frequency automated crypto Excel tracker. It is not sufficient for continuous monitoring across many assets, wallets, and derived metrics without aggregation.

CoinGecko’s free Demo API provides 10,000 monthly call credits and a rate limit of 100 calls per minute. Its /simple/price endpoint can query more than 500 coins in a single request. That endpoint structure matters more than the nominal monthly allowance.

A poorly designed workbook makes one API request per asset. A portfolio with 40 assets refreshed every 10 minutes would generate:

• 40 requests per refresh
• 144 refreshes per day
• 5,760 requests per day
• More than 170,000 requests in a 30-day month

That is not a spreadsheet. It is a rate-limit violation with cells attached to it.

The correct design batches assets into a single request where the provider supports it. If the tracker holds 40 assets and one endpoint can return all 40 prices in one call, the same 10-minute schedule becomes approximately 4,320 requests over 30 days. This is still material, but it is within a free-tier architecture if the query count remains controlled and no duplicate refresh chains exist.

CoinMarketCap’s free Basic tier uses a different constraint model: 15,000 monthly call credits, a 50-requests-per-minute limit, and one call credit per 100 data points returned. This can be efficient for broad portfolio snapshots, provided the workbook requests only required fields.

The selection is operational rather than ideological.

The spreadsheet should include a refresh budget. Not as a note. As a calculated control.

For example, define:

• Assets per request
• Requests per refresh
• Refreshes per day
• Expected monthly requests
• API monthly limit
• Remaining request capacity

Once this is visible, refresh frequency can be selected rationally. A long-only portfolio that is rebalanced monthly does not gain material risk control from ten-minute polling. It gains data noise, API consumption, and additional failure points.

A reasonable default for a crypto tracking spreadsheet is:

• Daily refresh for tax-lot review, allocation reporting, and long-horizon risk.
• Hourly refresh for active multi-asset portfolios with predefined rebalancing thresholds.
• Ten-minute refresh only when a portfolio has a genuine operational need for it and the API budget supports it.
• No attempt at tick-level tracking through free REST APIs and Excel.

A refresh interval is not a feature. It is a sampling decision with a cost function.

Power Query is the practical ingestion layer

Excel’s WEBSERVICE function appears attractive because it retrieves API responses directly into cells. The limitation is immediate: most crypto APIs return JSON. Excel’s legacy FILTERXML function does not parse raw JSON natively.

This is where many spreadsheet guides become misleading. They show a URL, a formula, and a price cell. They do not show a durable transformation layer.

Power Query is the appropriate default for a crypto portfolio tracker in Excel because it can:

• Send REST API requests.
• Store headers and parameters outside visible worksheet cells.
• Parse JSON into tables.
• Expand nested fields.
• Merge raw prices with an asset mapping table.
• Preserve a repeatable transformation sequence.
• Load cleaned results into a dedicated worksheet or data model.

The workflow is straightforward in principle:

1. Maintain a controlled Assets table with the provider’s token IDs.

2. Use Power Query to read that table.

3. Concatenate IDs into a batched API request.

4. Retrieve the JSON response.

5. Transform the response into rows containing asset ID, price, market-cap field if needed, and retrieval timestamp.

6. Load the result into Raw_Prices.

7. Use lookup formulas or Power Pivot relationships to join prices to current positions.

The complexity is not Power Query syntax. The complexity is identifier discipline.

An asset may trade under one ticker on an exchange and another identifier in a pricing API. Tokens can migrate contracts. Wrapped assets can have materially different liquidity from their native equivalents. A workbook that maps by ticker alone eventually assigns the wrong price to the wrong asset.

The asset mapping table should therefore contain at least:

A manual override field is also necessary. Not because the API is unreliable in general, but because portfolio valuation can encounter exceptions: a depegged stablecoin, a token with a broken market feed, or an asset suspended from reliable trading.

The override must never silently replace the raw price. It should be displayed beside it, with an override timestamp and reason. Otherwise, the workbook becomes vulnerable to untracked discretionary changes.

Timestamps, P&L, and the false precision problem

Price data without a retrieval time is incomplete. A portfolio total calculated from BTC at 14:00 UTC, ETH at 14:10 UTC, and a manually entered DeFi position from yesterday is not a single valuation event.

Each row in Raw_Prices should carry:

• Asset ID
• Quote currency
• Price
• Provider timestamp, if available
• Workbook retrieval timestamp
• Source name
• Refresh status

Crypto APIs frequently use UNIX timestamps: seconds elapsed since 1 January 1970. Excel uses serial dates. The conversion is:

(UNIX_timestamp / 86400) + 25569

The arithmetic is simple. The operational detail is not. Confirm whether the source timestamp is in seconds or milliseconds. Milliseconds must first be divided by 1,000. A timestamp interpreted at the wrong scale can produce dates centuries away from the actual trade or refresh event.

P&L is similarly vulnerable to casual spreadsheet logic.

For a spot long position, the economic form is simple:

Unrealized P&L = Current Value − Remaining Cost Basis

For a short position, the sign reverses:

Unrealized P&L = Entry Proceeds − Current Liability Value

The issue is not the formula. The issue is inventory accounting after partial exits, token swaps, transfers, and fee payments.

A basic tracker can use weighted-average cost. It is compact and usually sufficient for portfolio-risk reporting. A transaction-level system may require FIFO, specific identification, or jurisdiction-specific tax-lot treatment. Excel can model these methods, but formula complexity rises rapidly after multiple partial fills.

The minimum risk report should separate:

• Unrealized P&L: mark-to-market change on open inventory.
• Realized P&L: result from closed lots.
• Net deposits: capital added or removed by the account owner.
• Fees: exchange, network, borrowing, and vault fees.
• Yield or rewards: tokens received through staking, lending, or liquidity provision.
• Currency effect: performance in USD may differ materially from performance in BTC or ETH terms.

A portfolio that rises 15% in USD while underperforming BTC by 20% has two valid measurements. The base currency determines the reported result. The tracker should not mix them.

Risk automation is possible, but not through a single total-value cell

The strongest use of an Excel tracker is allocation surveillance. Not execution.

A current portfolio value is an output. Risk is the relationship between component weights, volatility, correlation, liquidity, and rebalance rules. Excel can model part of this relationship if the data interval is appropriate.

A functional risk tab can calculate:

• Asset weight: position market value divided by total portfolio value.
• Concentration: largest asset weight and top-five asset share.
• Stablecoin exposure: aggregate weight across recognized stablecoin assets.
• Target drift: actual weight minus target weight.
• Rebalance trigger: drift beyond a defined threshold.
• Rolling volatility: standard deviation of periodic returns.
• Maximum drawdown: peak-to-trough decline of portfolio NAV.
• Return-to-risk ratio: simplified Sharpe ratio using periodic portfolio returns and a stated risk-free assumption.

The rebalancing logic should distinguish between target allocation and trade instruction.

Suppose a portfolio target is 50% BTC, 30% ETH, and 20% stablecoins. A 5-percentage-point drift band may trigger a review, not an automatic order. If BTC rises to 56%, the tracker can calculate the notional amount required to return to target. It cannot assume that selling this amount is optimal after spread, slippage, tax consequences, and on-chain costs.

For less liquid assets, a percentage-based rebalancing rule is particularly dangerous. A calculated sell order may exceed executable liquidity or create price impact larger than the expected variance reduction.

The spreadsheet can flag conditions. It should not impersonate an execution engine.

Windows and Mac are not equivalent environments

Excel for Windows supports scheduled background refresh behavior through Power Query configurations, including refresh intervals such as every 10 minutes. This makes it possible to run a workbook as a periodic portfolio monitor while the application remains active.

Excel for Mac does not provide equivalent timed background refresh behavior in the same way. Refreshes are generally manual unless external automation, such as AppleScript or a separate process, is introduced.

This distinction changes the viability of an automated crypto Excel tracker.

On Windows, a workbook can operate as a low-frequency monitoring dashboard if:

• Queries are batched.
• API credentials are protected.
• Refresh intervals fit within the provider’s allowance.
• Errors are logged rather than hidden.
• The workbook remains open in a stable environment.

On Mac, the same workbook should be treated as an on-demand valuation tool unless external automation is deliberately engineered. Manual refresh is not inherently inferior for a long-horizon portfolio. It is inferior only if the model assumes continuous monitoring.

A further limitation applies to both platforms: Excel is not a reliable always-on service. Sleep mode, expired credentials, network interruptions, provider schema changes, and file-locking issues can all create stale data. The dashboard should expose the last successful refresh time in a prominent cell. If the time is old, every risk number downstream is old.

The six-month verdict

A crypto portfolio tracker in Excel is viable when the portfolio is sampled, not streamed.

It performs well for asset allocation, periodic rebalancing analysis, exposure reporting, cost-basis organization, and basic drawdown monitoring. Power Query plus a batched API request is the correct technical core. A structured transaction ledger is more valuable than a polished dashboard. Identifier mapping is more important than chart design.

It fails when asked to behave like institutional portfolio infrastructure. Free API tiers have hard rate limits. Native Excel does not provide comprehensive token coverage. Mac automation is constrained. JSON parsing requires a transformation layer. Manual entries remain necessary for many DeFi positions. None of these are minor implementation details.

The risk-adjusted conclusion is strict: use Excel as a transparent portfolio control surface for hourly, daily, or manual valuation. Do not use it as a real-time risk engine, a liquidation-protection system, or an execution layer. The moment the strategy depends on sub-minute pricing, automated hedging, or uninterrupted wallet synchronization, the spreadsheet has exceeded its design envelope.