Google Sheets is a versatile tool for managing financial data, and its googlefinance() function is a powerful feature for tracking asset prices. However, when it comes to cryptocurrencies, this function has limitations, especially for less popular coins. This guide explores effective methods to fetch a wide range of cryptocurrency prices directly into your spreadsheets.
Using the Standard googlefinance() Function
The googlefinance() function supports major cryptocurrencies such as Bitcoin (BTC), Ethereum (ETH), and Cardano (ADA). To use it, simply input the cryptocurrency ticker followed by your desired fiat currency code.
Examples:
=googlefinance("BTCUSD")— Fetches the Bitcoin price in US Dollars.=googlefinance("ETHBRL")— Retrieves the Ethereum price in Brazilian Real.
This method is straightforward and reliable for supported assets. However, it does not work for many altcoins, such as Polygon (MATIC) or Chainlink (LINK), limiting its usefulness for diversified crypto portfolios.
Advanced Methods for Altcoin Data
For cryptocurrencies not supported by the native googlefinance() function, you need alternative data sources. While some methods involve complex API integrations that require authentication, simpler solutions are available.
The importdata() Workaround
A practical solution is to use the importdata() function with a dedicated cryptocurrency price service. This approach avoids the complexities of parsing HTML with importxml(), which often fails on modern JavaScript-rendered websites.
The service provides a straightforward URL structure where you append a coin's ticker to its base URL. Here’s how to use it:
=importdata("https://cryptoprices.cc/MATIC/")
=importdata("https://cryptoprices.cc/LINK/")These formulas will return the current price of the specified cryptocurrency in US Dollars (USD).
Handling Different Locales and Currencies
The data from the service is formatted for the en_US locale. If your spreadsheet uses a different number format (e.g., using a comma as a decimal separator), you may receive a misinterpreted value. To ensure correct parsing, explicitly set the locale parameter in the function.
=importdata("https://cryptoprices.cc/MATIC/";;"en_US")
=importdata("https://cryptoprices.cc/SOL/";;"en_US")By specifying en_US, you instruct Google Sheets to correctly interpret the numerical format of the imported price.
To convert the USD price into another currency, combine the imported data with the googlefinance() currency conversion function. Multiply the crypto price by the current forex rate.
Example: Fetching Chainlink (LINK) in Brazilian Real (BRL)
=importdata("https://cryptoprices.cc/LINK/";;"en_US") * googlefinance("USDBRL")This formula first gets the LINK price in USD and then converts it to BRL using the current USDBRL exchange rate.
For those managing diverse portfolios, keeping track of these conversions is crucial. 👉 Explore more strategies for efficient portfolio management.
Frequently Asked Questions
Why doesn't the googlefinance() function work for all cryptocurrencies?
Google's native function only supports a limited number of the largest and most established cryptocurrencies by market capitalization. Many altcoins and newer tokens are not included in its database, necessitating alternative methods for price tracking.
How often is the price data updated?
The price data fetched through the importdata() method depends on the external service provider, which typically sources information from major exchanges. Data is usually updated in near real-time, but there can be a slight delay of a few minutes, similar to most free financial data services.
Can I get historical price data for cryptocurrencies?
The importdata() method described here is designed for fetching current spot prices. For historical cryptocurrency data, you would need to use a dedicated API service that offers historical endpoints, which often requires a more complex setup involving API keys and custom scripts.
What should I do if the importdata() function returns an error?
First, check your internet connection and the correctness of the cryptocurrency ticker. If the error persists, the external service might be temporarily unavailable. You can try refreshing the sheet or, for a more permanent solution, consider using a different data provider or a paid API with higher reliability.
Is this method reliable for automated trading?
While useful for tracking and analysis, this method is not recommended for automated trading systems. The data might have slight delays, and the import functions can occasionally timeout or be rate-limited by Google Sheets itself, which could lead to outdated or missing information for critical trading decisions.
Can I fetch other data like market cap or 24h volume?
The simple URL method focused on here is primarily for price. To fetch other metrics like market capitalization or trading volume, you would need to use a full-featured crypto API. These services can provide a wider array of data but require more advanced integration techniques into Google Sheets.