Crypto Portfolio Tracking with Excel and the CoinMarketCap API

Tracking your cryptocurrency investments doesn’t have to rely on third-party apps or platforms. With a bit of setup in Microsoft Excel and real-time data from the CoinMarketCap API, you can build a fully customizable, dynamic portfolio tracker that updates live—giving you full control over your data, privacy, and presentation.

This guide walks you through integrating Excel’s Power Query with the CoinMarketCap API to automatically pull live crypto prices, calculate your holdings, visualize performance, and even track profit/loss—all within a clean, professional dashboard.

Setting Up the CoinMarketCap API Connection

To begin, you’ll need access to real-time cryptocurrency pricing data. The CoinMarketCap API provides this through a simple integration with Excel.

👉 Generate live crypto data for your Excel tracker—start now with powerful tools.

1. Visit https://coinmarketcap.com/api/ and sign up for a free account.
2. Once registered, copy your unique API key—you’ll use this to authenticate requests from Excel.

Now open a new blank Excel workbook and rename the first sheet to CoinMarketCap Data. This will store all live price information pulled from the API.

Navigate to the Data tab, then under Get & Transform Data, select From Web. In the dialog box:

• Click Advanced.
• Enter the base URL:
  https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest?symbol=BTC,ETH
• Replace or extend the symbol list (e.g., add SOL, ADA, DOT) separated by commas—no spaces:
  Example: BTC,ETH,SOL,ADA

• Under HTTP request headers, add:

  • Name: X-CMC_PRO_API_KEY
  • Value: Paste your personal API key

Click OK.

Excel will now connect to the CoinMarketCap server and retrieve structured JSON data. When prompted, click Transform Data or Record to enter Power Query Editor.

Structuring Live Data in Excel

In Power Query, your raw JSON response appears as nested fields. We’ll flatten it to extract only what matters.

1. In the Value column, click the expand icon (➡️) in the top-right corner.

2. Deselect (Select All Columns) and choose only:

   • Name
   • Quote

Click OK.

You’ll now see a new column named Value.Quote. Expand it:

• Click the expand icon again.
• Select only USD (which contains price, market cap, change %, etc.).

Then expand Value.Quote.USD, selecting at minimum:

• price
• Optionally: percent_change_24h, market_cap

Once done, go to Home > Close & Load. Excel imports a clean table into your CoinMarketCap Data sheet with real-time crypto metrics.

Recording Your Trades: The Orders Sheet

Next, create a dedicated log of all your transactions—an essential foundation for accurate tracking.

Add a new sheet named Orders.

Set up the header row (A1:G1) with these columns:

• Date
• Symbol
• Name
• Buy/Sell
• Amount
• Price(USD)
• Amount(USD)

Enter each trade chronologically. For example:

• Buy 0.5 ETH at $3,000 → Amount = 0.5
• Sell 0.2 ETH → Amount = -0.2

This format allows Excel to calculate net holdings using formulas later.

Building the Portfolio Overview Dashboard

Create a third sheet called Portfolio Overview and move it to the front for easy access.

Set up headers in A1:G1:

• Symbol
• Name
• Price
• Amount
• Amount(USD)
• P/L
• Weight

Link live data:

• In cell A2: ='CoinMarketCap Data'!A2 (pulls first coin symbol)
• Copy across B2:C2 to pull Name and Price
• Drag fill handle right and down to apply formatting

Calculate current holdings:

• In D2 (Amount):

  =SUMIF(Orders!$B:$B,A2,Orders!$E:$E)

  This sums all amounts for the given symbol from your trade history.

Current value:

• In E2 (Amount in USD):

  =D2 * C2

Profit/Loss:

• In F2:

  =E2 / SUMIF(Orders!$B:$B,A2,Orders!$G:$G) - 1

  Compares current value against total cost basis (from Amount(USD) column in Orders).

Portfolio weight:

• In G2:

  =E2 / $E$[total_row]

  Where [total_row] is the cell containing total portfolio value (e.g., E4)

👉 Turn your transaction history into actionable insights—track growth like a pro.

Summarizing and Formatting Your Portfolio

At the bottom of your list (e.g., row 4), add a Total row:

• In A4: Type Total
• In E4: =SUM(E2:E3) → Total portfolio value

• In F4 (overall P/L):

  =SUMPRODUCT(F2:F3,G2:G3)

Format the entire table:

• Convert to an Excel Table (Insert > Table) with headers enabled
• Apply currency formatting ($) to Price and Amount(USD)
• Format P/L and Weight as percentages (%), with 2 decimal places
• Adjust column widths for clarity

Your dashboard now shows a professional summary of your crypto portfolio with live pricing and performance analytics.

Visualizing Performance with Charts

Visuals enhance understanding and make trends obvious at a glance.

Pie Chart: Portfolio Allocation

1. Hold Ctrl and select Symbol (A2:A3) and Weight (G2:G3)
2. Go to Insert > Charts > 2D Pie
3. Rename title to “Portfolio Breakdown”
4. Format labels to show percentages

Bar Chart: Profit & Loss Comparison

1. Select Symbol (A2:A3) and P/L (F2:F3)
2. Insert > Bar Chart > 2D Clustered Bar
3. Title it “Portfolio Performance”
4. Format axis as percentage; add data labels

These visuals auto-update when prices change—just refresh data via Data > Refresh All.

Adding New Cryptocurrencies

Want to add more coins? Easily expand your tracker:

1. Reopen Power Query by editing your original data connection.
2. Update the symbol list in the API URL (e.g., append ,AVAX)
3. Use Append Queries under Home > Combine to merge new data into existing table
4. Close & Load

Then:

• In Portfolio Overview, insert a new row above "Total"
• Link new symbol from CoinMarketCap Data
• Extend SUMIF ranges in D2:F2 and charts
• Drag formulas down

Everything updates dynamically—including charts and weights.

Frequently Asked Questions (FAQ)

Q: Do I need a paid CoinMarketCap API plan?
A: No. The free plan supports up to 333 calls per day, which is sufficient for daily tracking. Just avoid excessive refreshes.

Q: Can I track non-USDT or non-BTC pairs?
A: Yes—the API defaults to USD pricing via the .USD path, so all values are stablecoin/fiat-adjusted automatically.

Q: How often should I refresh data?
A: Manually refresh via Data > Refresh All, or set up scheduled refreshes if using Excel Online or Power BI backend.

Q: Will this work on Mac or mobile Excel?
A: Most features work on Mac, though some Power Query options may vary. Mobile apps support viewing but not editing queries.

Q: Is my API key safe in Excel?
A: Avoid sharing files containing your key. Store sensitive files locally or encrypted.

Q: Can I automate historical tracking?
A: Yes—combine this setup with timestamped logging or integrate with Google Sheets + Apps Script for long-term analysis.

Final Thoughts

By combining Excel’s powerful calculation engine with the CoinMarketCap API, you’ve built a robust, private, and scalable crypto portfolio tracker—without relying on external apps.

Whether you're monitoring a few assets or managing a diversified basket of digital currencies, this system grows with you. And with built-in visualizations and real-time updates, staying informed has never been easier.

👉 Take control of your crypto journey—start building smarter financial tools today.

Core Keywords:

• crypto portfolio tracking
• Excel cryptocurrency tracker
• CoinMarketCap API
• live crypto prices in Excel
• track crypto investments
• cryptocurrency profit calculator
• Power Query crypto
• build crypto dashboard