Build a Real-time Cryptocurrency Watchlist in Google Sheets

·

Creating a comprehensive cryptocurrency watchlist can be challenging, especially when you need real-time data across multiple blockchain solutions and data providers. Whether you're tracking major tokens like Bitcoin (BTC) and Ethereum (ETH) or lesser-known altcoins, consolidating your portfolios from various wallets and exchanges into one place simplifies wealth tracking.

This guide will walk you through building a dynamic cryptocurrency watchlist in Google Sheets using SheetsFinance, enabling fast data loading with batch functions and visual enhancements like conditional formatting and sparklines.

Getting Started

  1. Install SheetsFinance: Ensure the add-on is properly linked to your Google account. Installation guide here.
  2. Use the Free Template: Access the cryptocurrency watchlist template and create a copy.
  3. Video Tutorial: Prefer visual learning? Check the SheetsFinance YouTube Channel.

Crypto Price and Price Change Tracking

Step 1: Add Symbols

Input tokens like BTCUSD, ETHUSD, and BNBUSD in cells B1, C1, and D1 respectively. Extend this for additional symbols.

👉 Optimize your watchlist with real-time data

Tip: Always include the base currency (e.g., BTCUSD outputs Bitcoin’s price in USD).

Step 2: Fetch Real-Time Prices

Use this batch function to retrieve prices for all symbols at once:

=TRANSPOSE(SF(B$1:Z$1))

This leverages Real-time Batch to minimize API calls. TRANSPOSE() converts row data into a column format.

Step 3: Calculate Percentage Changes

Add 1-day, 7-day, 30-day, and YTD performance metrics:

=ARRAYFORMULA(TRANSPOSE(SF(B$1:Z$1, "change","1D&5D&1M&ytd","","NH"))/100)

Conditional Formatting: Apply color scales (e.g., green/red for positive/negative changes) for quick visual analysis.

Sparklines for Price Trends

Visualize historical price movements with compact sparklines:

=SPARKLINE(SF_SPARK(B$1, 365, "price"), {"charttype", "line"; "color", IF(B6 < 0, "red", IF(B6 > 0, "green", "blue"))})

👉 Enhance your analysis with sparklines

Relative Token Market Cap

Compare token market caps to Bitcoin (BTCUSD):

=TRANSPOSE(ARRAYFORMULA(SF(B$1:Z$1,"realTime","marketCap")/SF("BTCUSD","realTime","marketCap")))

Repeat for ETHUSD and USDTUSD. Use conditional formatting to highlight values >100% (e.g., black fill for BTC/BTC comparisons).

Market Sentiment Analysis

Volume Trends

Track monthly volume changes for BTCUSD:

=SPARKLINE(SF_SPARK(B$1,30,"volume"),{"charttype","column"})

Key Metrics

Fetch average volume, real-time volume, and yearly lows/highs:

=TRANSPOSE(SF(B$1:Z$1,"realTime","avgVolume&volume&yearLow&yearHigh","","NH"))

FAQs

How do I add more tokens to the watchlist?

Simply extend the symbol range (e.g., B1:Z1) and replicate the formulas.

Can I customize the timeframes for sparklines?

Yes! Adjust the SF_SPARK() parameter (e.g., 30 for 30-day data).

What if I don’t know a token’s ticker?

Use the Symbol Search tool (Extensions > Sheets Finance > Symbol Search) or visit the SF Symbol Search page.

Additional Resources