Track Crypto Prices in Google Sheets: 3 Live Formulas

KEY TAKEAWAYS

The GOOGLEFINANCE function supports cryptocurrency prices but returned data for only 17 of the top 30 coins in a test conducted by CoinTable on 15 October 2026, leaving significant coverage gaps.
The IMPORTDATA function retrieves cryptocurrency prices from the free cryptoprices.cc endpoint using a simple formula that requires no API key or programming knowledge, although its data can be up to one hour old.
Google Sheets add-ons like CoinTable provide cryptocurrency pricing with 300 free monthly updates. Its free tier can return prices up to 30 minutes old, while automatic refresh on spreadsheet open is a paid Plus feature.
The IMPORTXML function can scrape prices from static web pages, though it may fail on JavaScript-loaded pages such as Coinbase’s, and XPath selectors need updating whenever the source site changes layout.
Portfolio tracking formulas calculate holdings value, profit and loss, percentage returns, and allocation percentages using basic multiplication and division across linked cells.

Google Sheets offers three built-in methods for pulling live cryptocurrency prices into a spreadsheet without any external software. The GOOGLEFINANCE function handles basic crypto pairs, while IMPORTDATA and IMPORTXML extend coverage to coins that GOOGLEFINANCE misses entirely.

This article walks through each method with working formulas, explains which approach covers the most assets, and outlines how to structure a complete portfolio tracker. It covers the limitations of each function, the add-on alternatives that fill coverage gaps, and the specific spreadsheet formulas needed to calculate real-time portfolio performance metrics.

What GOOGLEFINANCE Covers and Where It Fails for Crypto Prices

The GOOGLEFINANCE function uses a simple syntax to pull cryptocurrency prices into any cell. The formula =GOOGLEFINANCE(“CURRENCY:BTCUSD”) returns the current Bitcoin price in United States dollars. Replacing BTC with other ticker symbols retrieves prices for supported alternative cryptocurrencies using the same formula structure across the entire spreadsheet.

In a test conducted by CoinTable on 15 October 2026, GOOGLEFINANCE returned valid price data for only 17 of the top 30 cryptocurrencies by market capitalization. Coins returning errors included DOGE, LINK, AVAX, and USDT, making the function unreliable as a sole data source for diversified portfolios that hold smaller or newer tokens.

Google states that quotes from GOOGLEFINANCE may be delayed up to 20 minutes, according to its GOOGLEFINANCE documentation. That delay makes the function suitable for portfolio tracking and daily monitoring but inadequate for active trading decisions that depend on real-time price movements across volatile crypto markets.

Cell references work within the function, allowing dynamic tracking. The formula =GOOGLEFINANCE(“CURRENCY:”&B2&”USD”) pulls the price for the cryptocurrency ticker entered in cell B2, enabling users to build a coin list in one column and populate prices automatically in an adjacent column without editing individual formulas for each cryptocurrency holding.

How IMPORTDATA and IMPORTXML Fill the Coverage Gaps

The IMPORTDATA function offers broader cryptocurrency coverage by pulling price data from external endpoints. The formula =IMPORTDATA(“https://cryptoprices.cc/BTC/“) retrieves the Bitcoin price from the free cryptoprices.cc endpoint without requiring an API key, account registration, or any technical configuration steps beyond entering the formula itself.

However, cryptoprices.cc states that its data can be up to one hour old, so the endpoint does not guarantee real-time prices.

Replacing “BTC” in the URL with any supported ticker retrieves prices for thousands of cryptocurrencies that GOOGLEFINANCE cannot access. The full list of supported tokens is available in the service’s plain-text token list, giving users a reference for supported ticker symbols.

IMPORTXML takes a different approach by scraping price data directly from websites. The function can extract price elements from static pages when their content is accessible in the page’s HTML, using a formula such as =IMPORTXML(“page URL”, “XPath”). Users find the correct XPath by right-clicking the relevant element on the website, selecting Inspect, and copying its XPath from the developer panel.

However, this approach may not work on dynamic pages such as Coinbase’s price pages, which load price information using JavaScript.

The main limitation of IMPORTXML is fragility. Website layout changes break the XPath selector without warning, causing the formula to return errors until the user manually updates the path. The price may also be split across multiple cells, requiring a secondary formula to combine the dollar sign, whole number, and decimal components into a single usable value.

Building a Complete Portfolio Tracker With Calculated Performance Metrics

A functional portfolio tracker requires six core columns: coin name, ticker symbol, quantity held, average purchase cost, current price, and total current value. The price column uses one of the three methods described above, while the value column multiplies quantity by price using a basic formula such as =C2*E2 in each row.

Profit and loss calculations add three more columns to the tracker layout. The profit column subtracts total cost from current value using =F2-(C2*D2). The percentage return column divides profit by total cost, wrapped in an IF statement to prevent division errors when cost is zero.

Allocation percentage divides each position’s value by the portfolio total using =F2/SUM($F$2:$F$20). The absolute references keep the portfolio total range fixed when the formula is dragged down the column.

Add-ons extend functionality beyond what built-in formulas can provide. CoinTable offers formulas such as =COINTABLE(B2) for current prices, =COINTABLE(B2, “changepct”) for 24-hour percentage change, and =COINTABLE(B2, “marketcap”) for market capitalization data.

The free tier provides 300 monthly updates, while premium plans offer unlimited updates for normal use. Automatic refresh on spreadsheet open is available with the paid Plus plan, not the free tier.

Conditional formatting enhances readability by color-coding gains and losses automatically. Applying green formatting to positive profit cells and red to negative values creates an instant visual overview of portfolio health. Google Sheets allows custom formatting rules that update dynamically as prices change throughout each trading session.

Choosing the Right Method for Different Portfolio Sizes and Tracking Needs

Small portfolios holding fewer than ten major cryptocurrencies can rely entirely on GOOGLEFINANCE for daily tracking. The function covers Bitcoin, Ethereum, and most large-cap tokens with sufficient accuracy for long-term position monitoring and periodic rebalancing without any external dependencies or configuration requirements.

Diversified portfolios that include mid-cap and small-cap tokens require IMPORTDATA or a dedicated add-on to achieve broader coverage.

The free cryptoprices.cc endpoint through IMPORTDATA supports thousands of tokens without requiring an API key, making it a practical free option for portfolios spanning 20 or more separate cryptocurrency positions. Its prices can be up to one hour old, however, and it is separate from the CoinGecko for Sheets add-on.

Professional or institutional tracking demands add-on solutions that provide automated refresh cycles, historical price data, and multiple metric fields beyond simple spot prices.

CoinGecko for Sheets and CoinTable both install from the Google Workspace Marketplace in under 30 seconds and require no programming knowledge to configure. CoinGecko for Sheets requires a CoinGecko API key, although users can start with a free Demo key.

Combining methods creates the most robust tracker. GOOGLEFINANCE handles major pairs with minimal setup, IMPORTDATA fills the coverage gaps for smaller tokens, and a conditional formatting layer provides visual performance signals. That layered approach balances reliability, coverage breadth, and maintenance effort across all portfolio sizes.

FAQs

Does the GOOGLEFINANCE function work for all cryptocurrency prices in Google Sheets?
In a 15 October 2026 test by CoinTable, GOOGLEFINANCE returned prices for only 17 of the top 30 cryptocurrencies, leaving significant gaps for tokens like DOGE, LINK, AVAX, and USDT.

What is the simplest formula to get Bitcoin price in Google Sheets?
The formula =GOOGLEFINANCE(“CURRENCY:BTCUSD”) returns the current Bitcoin price in United States dollars with a delay of up to 20 minutes from live.

How does IMPORTDATA pull cryptocurrency prices without needing an API key?
The formula =IMPORTDATA(“https://cryptoprices.cc/BTC/“) retrieves prices from the free cryptoprices.cc endpoint, which requires no registration, API key, or authentication. Its data can be up to one hour old, so it does not guarantee real-time pricing.

What are the limitations of using IMPORTXML for live crypto price tracking?
IMPORTXML scrapes website elements using XPath selectors that break whenever the source site changes its layout, requiring manual updates to restore data.

Which Google Sheets crypto add-on provides the most comprehensive price coverage?
CoinTable and CoinGecko for Sheets both cover thousands of cryptocurrencies, with CoinTable offering 300 free monthly updates and paid plans providing unlimited updates for normal use. CoinGecko for Sheets requires a CoinGecko API key, including the free Demo key.

Can Google Sheets track cryptocurrency portfolio profit and loss automatically?
Basic formulas calculate profit, percentage return, and allocation by referencing quantity, cost, and live price cells, updating automatically when prices refresh.

How often do cryptocurrency prices refresh when using Google Sheets functions?
GOOGLEFINANCE quotes may lag up to 20 minutes. CoinTable’s free-tier prices can be up to 30 minutes old. Its Plus plan supports refresh on spreadsheet open at most once every five minutes, alongside hourly automatic refreshes.