Use GOOGLEFINANCE Function to Import Crypto prices into Google Sheets

-

 Understanding "GOOGLEFINANCE"

The GOOGLEFINANCE function is a powerful and handy tool that is a Built-In Function in Google Sheets that allows you to fetch real-time financial data directly into your spreadsheet. It's particularly useful for tracking stock prices, exchange rates, and cryptocurrency prices as we'll explore.

The GOOGLEFINANCE function enables the creation of simple trackers or grandiose dashboards into Google Sheets without code, API, or anything else. Unlike most of the other solutions, importing real-time and historical prices using the GOOGLE FINANCE function is free. 

Basic Syntax:

=GOOGLEFINANCE(ticker, attribute, [start_date], [end_date], [interval])

ticker: The symbol for the asset you want to track (e.g., "BTC-USD" for Bitcoin in USD).

attribute: Optional. Specifies the type of data you want to retrieve (e.g., "price", "close", "high", "low", "open", "volume"). If omitted, the default is "price".

start_date: /Optional/. The start date for historical data.

end_date: /Optional/. The end date for historical data.

interval: /Optional/. The time interval for historical data (e.g., "DAILY", "WEEKLY", "MONTHLY").


The simplest version of the function is importing real-time price: =googlefinance("Currency:PAIR") that retrieves the current price of the coin. It’s mandatory to use both the exchange symbol and ticker symbol for accurate results and to avoid discrepancies. For Crypto has to be used "Currency:", not just a pair such as BTCUSD or any else. 

Importing Real-Time Crypto Prices

To get the current price of a cryptocurrency, you only need to specify the ticker: 

Example:

To get the current price of Bitcoin in USD: =googlefinance("Currency:BTCUSD"), or Ethereum in EUR: =googlefinance("Currency:ETHEUR")

Supported Cryptocurrencies:

While the list of supported cryptocurrencies can change, popular ones like Bitcoin (BTC), Ethereum (ETH), Binance (BNB), Litecoin (LTC), and many others are available. It's always best to check Google Finance directly for the most up-to-date list on Google Support page

Importing Historical Crypto Prices

To fetch historical data, add the start_date and end_date parameters. The other attributes are optional. 

Example:

To get the daily closing price of Bitcoin from July 01, 2024, to July 31, 2024:

=googlefinance("BTCUSD", "close", date(2024,7,1), date(2024,7,31), "daily")

The function supports connection to the cells also. If we have values into cells: A1: BTCUSD; A2: July 01, 2024; A3: July 31, 2024 we can write: 

=googlefinance(A1, "close", A2, A3, "daily") /interval „daily“ is optional and default/

Supported Attributes:

  • price: Current price
  • close: Closing price
  • high: Highest price for the period
  • low: Lowest price for the period
  • open: Opening price for the period
  • volume: Trading volume
  • all: list all data

Step-by-Step Guide to import historical prices

Identify the cryptocurrency ticker. You can find this on Google Finance or other cryptocurrency platforms.

Enter the GOOGLEFINANCE formula. Use the appropriate parameters based on whether you want real-time or historical data.


Adjust the formula. You can modify the formula to fetch different attributes or periods as needed.

Additional Tips

Auto Refresh: Google Sheets app allows auto refresh. Go to the FILE >> Settings >> Calculation. Under Recalculation button set auto refreshing. 
Charting and modify: You can create charts, error handling, complex dashboards, and many great visuals based on the imported data to visualize price trends.

By following these steps and understanding the GOOGLEFINANCE function, you can effectively import and analyze cryptocurrency price data in Google Sheets.

Note: While GOOGLEFINANCE is a convenient tool, it might not cover all cryptocurrencies or offer the most extensive historical data. For more comprehensive data, consider using specialized financial data APIs or platforms. The Google support page is here. 

I created a simple dashboard for the free download. It may be used for exercise or as a base for great dashboards and visualizations. 

Would you like to explore creating a basic crypto price tracker using GOOGLEFINANCE? You can do it alone or message me for the support. 

Comments

Post a Comment

Popular posts from this blog

Import Crypto live prices into Excel

-
Image
 Excel is a powerful tool that can be leveraged to track cryptocurrency prices. There are several methods to achieve this, but I shall explain a process in this article: Built-in Stock Data in this article.  Excel offers to fetch real-time prices for a limited number of cryptocurrencies without additional Add-ins, code, or API. This is free and the simplest method available to everyone, but the method restricts the available cryptocurrencies. At the moment of publishing this article available crypto are: -         Bitcoin (BTC) -         Ethereum (ETH) -         Litecoin (LTC) -         Bitcoin Cash (BCH) -         Ripple (XRP). To track a wider range of cryptocurrencies, including lesser-known or newly launched ones, you'll need to use third-party APIs or some of ...
Read more

Use Exponential Moving Average in Crypto Technical Analysis in Excel or Google Sheets

-
Image
  Understanding the Exponential Moving Average in Crypto Technical Analysis Cryptocurrency trading is a dynamic field that demands traders stay ahead of the curve with accurate and responsive tools. Among these tools, the Exponential Moving Average (EMA) stands out due to its ability to smooth out price data, making trends more apparent. This blog post delves into the EMA, its history, calculation, and application in cryptocurrency trading. A Brief History of the Exponential Moving Average The Exponential Moving Average is a type of weighted moving average that assigns greater importance to the most recent data points. Developed in the 1950s by technical analysts, the EMA was designed to address the lag problem inherent in Simple Moving Averages (SMA). This responsiveness makes the EMA particularly useful in the fast-paced world of cryptocurrency trading, where price volatility is a common occurrence. EMA vs. SMA: What's the Difference? The Simple Moving Average (SMA) calculates th...
Read more