Coins: 15,953
Exchanges: 1,183
Market Cap: $3.878T 0.4%
24h Vol: $415.593B
Gas: 27.88 GWEI
Go Ad-free
Trading
TABLE OF CONTENTS

Bitcoin Technical Analysis on Google Sheets (Beginner's Guide)

5.0
| by
Jackson Henning
|
Edited by
Julia Ng
-

Technical analysis is a method used in financial markets to predict future price movements of assets by analyzing historical market data. It focuses on price and volume patterns, and operates on the assumption that the price reflects all available information, moves in trends and tends to repeat historical patterns.

In today’s guide, we’ll cover:

  • How to import historical Bitcoin price data into Google Sheets with an API
  • How to import historical Bitcoin price data into Google Sheets with a CSV
  • How to perform a technical analysis on Bitcoin, to develop a Bitcoin price prediction for the upcoming halving

With much ground to cover, let’s jump right in!


How to Import Bitcoin Historical Prices into Google Sheets (with an API)

Follow these steps to import historical Bitcoin price into Google Sheets:

  1. Install an API connector on Google Sheets.
  2. Generate your CoinGecko API key.
  3. Input your request URL and API key into the API connector.
  4. Run the query and organize the data.
  5. Set up a refresh schedule or manually refresh to update the data.

Note: You will require a paid CoinGecko API plan to import more than one year of historical price data.

How to do a bitcoin technical analysis on google sheets guide

For this tutorial, we’ll be using the API connector by Mixed Analytics. After installing the API connector add-on on Google Workspace Marketplace, run the extension from Google Sheets > Extensions.

Create a request to import data accordingly.

Bitcoin price API - import real-time and historical price data into gsheets | CoinGecko

Navigate to the Coins Historical Chart Data by ID endpoint on CoinGecko API's documentation. Fill in the parameters accordingly to derive the request URL.

  • id: bitcoin
  • vs_currency: usd
  • days: max
  • interval: daily
  • precision: full

pull crypto price into google sheets - market cap, trading volume, crypto price

Enter your Pro API key under the key Header authorization section and copy the Request URL. It should be:

https://pro-api.coingecko.com/api/v3/coins/bitcoin/market_chart?vs_currency=usd&days=max&interval=daily&precision=full

Feed this request URL back into the API connector. Under ‘Headers’, fill in the following:

  • Key: x-cg-pro-api-key
  • Value: Your API key (i.e. CG-XXXXXXXXXXXXXXXXXXXXXXXX)

Get historical BTC price in Gsheets using an API Connector

Next, select ‘Output options’ to expand the settings, and update the report style to ‘compact’. 

Compact report style under Output mode

Run the query and you’ll see Bitcoin (BTC) historical price data imported into Google Sheets, in the following format:

Bitcoin price historical data since 2013 in gsheets

Each cell under prices, market_caps and total_volumes contain nested data within the ‘[ ]’ brackets. The first value within the bracket displays the Unix time for that corresponding price, market cap and trading volume. The second value within the bracket shows the Bitcoin price, market cap and trading volume for that corresponding Unix time.

To convert Unix to a human-readable date, we’ll first split the cell values for row A (prices) by applying the following formula in cell F2.

=SPLIT(SUBSTITUTE(SUBSTITUTE($A2,"[",""),"]",""), ",")

Repeat the following for rows B (market_caps) and C (total_volumes), applying the formula for all cells in the column.

To convert Unix time to a human-readable date, we’ll apply the formula =EPOCHTODATE().

From the screenshot below, we can see that the date for the first Bitcoin price and market data is captured on April 28, 2013 at 0:00:00 UTC.

Use the EPOCHTODATE formula to convert Unix time to a human readable format

Similarly, apply the EPOCHTODATE() formula on all cells in column E, to convert Unix time for the imported data.

Since the corresponding Unix time is the same across Price, Market Cap and Volume, we can hide the subsequent repeated columns.

Finally, we have an organized data set for historical Bitcoin (BTC) price in Google Sheets.

💡Pro-tip: Having this integration in place allows us to append new historical price data for Bitcoin as days pass, by simply running the query. Additionally, you may set up a refresh schedule on the paid API Connector subscription or manually refresh the query.

CoinGecko users can enjoy a 30% off lifetime MixedAnalytics subscription by applying the code "COINGECKO" when installing the integration.

You may also look to duplicate this integration for other cryptocurrencies for technical analysis.

We’ll now look at another way to import historical Bitcoin price data into Google Sheets. 


How to Import Bitcoin Historical Prices Data into Google Sheets (with a CSV)

We can retrieve Bitcoin historical price data in Google Sheets through a CSV exported from CoinGecko’s Bitcoin page.

We can access the Bitcoin price page on CoinGecko by clicking on  ‘Cryptocurrencies’ from the CoinGecko home page. From there, Bitcoin (BTC) will appear as the top line item when the list of cryptocurrencies are sorted by market cap.

Coingecko homepage - cryptocurrency price tracker

Once you’re on the Bitcoin page, select “Historical data” to view all historical data points for Bitcoin.

Historical btc price on CoinGecko.com

From the Bitcoin historical data page, select the calendar icon at the top right and input your preferred time coverage. Once the data refreshes, tap on the ‘download’ icon next to it to export the historical BTC price data in either the .xls or .csv format.

CSV export historical price data based on your preferred timeframe
Create a new Google Sheet, select ‘File’, ‘Import’, and find your .csv file. With that, we have BTC historical data accessible in Google Sheets.


Conducting a Technical Analysis with Historical Crypto Price Data

When conducting technical analysis, specifically chart analysis, the first thing we need to consider is the time frame. We need to zoom out in order to comprehend, for example, what occurs in daily time frames to make predictions in hourly time frames, or monthly time frames for weekly time frames, etc. Off this understanding, we can create an investment thesis that 'invalidates' our perception.

For example, if we think a breakout is going to occur, where the price of a cryptocurrency is going to surpass a former level of resistance, ‘invalidating’ our thesis would be when the price closes below a level X, as opposed to consecutively higher closes. Alternatively, we can form time-based invalidations, suggesting that the price should consecutively close above X to suggest a breakout.

To substantiate these perceptions, we need to identify levels of support and resistance. Resistance levels are prices that the asset struggles to surpass, and support are price levels the asset struggles to drop below. Identifiers of significant price levels that can help us isolate support/resistance include the bottom of a dump, the peak of a rally, an area of recurring tests, and consolidations (sideways movements) between impulsive moves (this Elliott Wave article deep dives into graph terminology).

In the process of identifying levels of support and resistance, we’ll again review data at a higher time frame (i.e. monthly/weekly/daily) to make inferences for lower time frames (daily/intraday). From this, we are looking for confluence, which are significant price points that occur on a multi-timeframe level.


How to Perform a Technical Analysis of Bitcoin

We can perform a technical analysis of Bitcoin by identifying support and resistance levels, using historical price data on Google Sheets.

To conduct a Bitcoin price prediction for April 2024, let’s first go back in time to assess trends during the first Bitcoin halving, which occurred on July 9, 2016.

We’ll start by creating three separate graphs with the data imported earlier:

  1. Monthly price data for 2016
  2. Weekly price data for 2016
  3. Daily price data for 2016

We will need to splice our imputed 'date' column to a numeric form, for us to calculate the monthly and weekly prices. As we are only concerned with 2016, we will start at cell A978, which is 01/01/2016.

With the formula:

 =DATE(VALUE(LEFT(A978,4)), VALUE(MID(A978,6,2)), VALUE(MID(A978,9,2)))

We can separate the time values into excel-readable formatting. Then we can calculate the weekly and monthly price data with the following formula, starting in cells G978 and H978 as shown above:

  • =IFERROR(AVERAGEIFS($B$971:$B$1344, $F$971:$F$1344, "<="&F978, $F$971:$F$1344, ">"&F978-7), "No Data")
  • =IFERROR(AVERAGEIFS($B$971:$B$1344, $F$971:$F$1344, "<="&F978, $F$971:$F$1344, ">"&F978-31), "No Data")

For this, ensure you have created proper 'date' values for the 31 days prior to January 1, 2016 (01/01/2016).

The IFERROR statement is for pre-emptive error handling, in case we had insufficient data to calculate weekly/monthly averages. The AVERAGEIFS statement allows us to calculate moving averages, conditional to the size of our intervals (i.e. weekly and monthly). From there, we can simply input a line graph for both sets of data. 

The graphs look progressively more “smoothed” with each higher frequency, which is expected. Nevertheless, on a higher time frame we can note the Bitcoin Halving on July 9, 2016 (07/09/16) and a sustained period of consolidation around this date. If we mark this period, which is equally as prevalent on the weekly time frame, we can notice a period of resistance on the yearly and daily time frame, as shown in the daily time frame below:

The level 675 is tested on three occasions both before and after the Bitcoin Halving date, as depicted above. As we have marked this on higher time frames as well, we can consult this as a level of resistance. Returning to our Google Sheet, we can notice this price level is not tested again after July 19, 2016 (19/07/2016) until October 26, 2016 (26/10/2016), marking a three and a half-month gap. On a weekly price level it is first tested on July 6, 2016 (06/07/2016) and then again on October 30, 2016 (30/10/2016). Coincidingly, it is tested at a monthly level on July 12, 2016 (12/07/2016) and then not until November 11, 2016 (11/11/2016).

The recurring tests make the level weaker as the supply and demand at that price becomes weaker with each test. Additionally, as this level is availed by a higher time frame as well, we can assume a bigger move was going to ensue. This signals the integral importance of time frames for chart analysis. 

We can employ various technical indicators for our technical analysis. For this guide, we will be utilizing the Relative Strength Index (RSI). To fractionise its components, the RSI is calculated as follows:

RSI = 100- [100/(1+RS)]

Where RS = average gain over 14-periods/ average loss over 14-periods

When creating RSI for our data it may look scattered and disorderly, but once we break down its intricacies, it's not as difficult as it may seem. To graph this technical indicator, we are going to make 7 new columns: Price change, Gain, Loss, Average Gain, Average Loss, RS, RSI. The formulas for each are:

  • Price Change =B978-B977
  • Gain =IF(K978>0, K978, 0)
  • Loss =IF(K978<0, -K978, 0)
  • Average Gain =AVERAGE(L965:L978)
  • Average Loss =AVERAGE(M965:M978)
  • RS = Average Gain/ Average Loss = N978/O978
  • RSI = 100 - ((100 / (1 + P978)))

After these are created, we can plot our RSI indicator. Although appearing untidy and erratic, we can simplify the indicator by plotting three lines, one each at 50, 70 and 30. This is due to the consideration of an asset being overbought when the RSI is greater than 70, oversold when it is less than 30, and the significance of midpoint value crosses.

A midpoint value cross can indicate whether there is a bullish or bearish bias in a trend. While judged mostly by perception, the amount of time the RSI spends above the 50 level can be an indication of a bullish bias, and the opposite applies if the bears are in control. This level also serves to indicate if a trend reversal will occur. If we treat the 50 line like a dynamic support/resistance level that switches from one to another when the line is crossed, a trend reversal can occur when the line fails to act as a support/resistance (i.e. crosses).

Similarly, to our earlier analysis (wherein we highlighted a period of consolidation) we can also indicate periods of consolidation when the RSI is chopping through the midpoint continuously, which suggests an expansion is to follow. . This aligns with the graph above, as during the Bitcoin Halving, which we identified in our daily, weekly, and yearly price charts, there is also a period of consolidation in the RSI chart, suggesting an expansion would follow – which it did.


Bitcoin Price Prediction in 2024

Let’s create a price prediction using the previously outlined methods for the Bitcoin Halving slated in April 2024. First, we construct a graph of the BTC price for the past year, using our data on Google Sheets. From the graph, we can observe the two noticeable periods of consolidation that were tested on multiple occasions, before the breakout that occurred (orange arrow), leading to another period of consolidation. To properly formulate an opinion let’s now “zoom in” to the recent period of consolidation.

Zooming in on the past 2 months, which includes the period highlighted in the above graph, we can notice 3 tests of the Bitcoin price resistance level at $44,000. Following the last test we have had a sustained period of sideways price movements, as shown below. The recurring tests suggest a weakening resistance level. This bodes well for a breakout after the upcoming halving, but it will also depend on the demand for Bitcoin at the time. 

Lastly, we can coincide our price analysis with a check of the RSI for the past 2 months. The recent discernible downwards trend in the RSI from October 12, 2023 (10/12/23) to December 19, 2023 (19/12/23) that carried it below the midpoint value suggests sellers were waking up and long positions were closing. However, the persistence of the RSI above the midpoint for the past 3 months indicates a bearish bias, which may coincide with many being hopeful of a price rally following the upcoming halving. This would align with our previous analysis, spotting a recurring test of $44,000.


Conclusion

Leveraging a crypto data API like CoinGecko API to import historical price data into Google Sheets proves to be valuable and convenient, especially once the initial setup is complete. This method offers a seamless approach to accessing and analyzing market information, allowing for automated data refreshes instead of having to manually export and import crypto data into Google Sheets. This can help to streamline the technical analysis process.

Comprehensive and reliable crypto data API with wide coin and exchange coverage

When conducting technical analysis, zoom out! You can always view an obscure technical indicator or chart pattern, assuming it will give you new insight, but the value of establishing confluence on multiple time frames is just as useful, if not more. After creating an overall thesis and understanding what a market is doing then you can zoom in and create a more specialized opinion. 

By trimming the original data set to identify trends around past Bitcoin halvings, we are able to construct an investment thesis for the upcoming Bitcoin halving in April 2024, demonstrating a tangible application of the discussed technical analysis techniques. By incorporating these insights and understanding the levels of support and resistance, traders and investors can enhance their ability to make well-informed decisions in the dynamic world of cryptocurrency trading.

Disclaimer: The information provided in this article is for educational and informational purposes only. It does not constitute financial advice, investment recommendations, or endorsement of any specific trading strategy. Readers are encouraged to conduct their own research before making any financial decisions.


Looking for more resources? Learn how to leverage other CoinGecko API endpoints to import live crypto prices into Google Sheets.

CoinGecko's Content Editorial Guidelines
CoinGecko’s content aims to demystify the crypto industry. While certain posts you see may be sponsored, we strive to uphold the highest standards of editorial quality and integrity, and do not publish any content that has not been vetted by our editors.
Learn more
Want to be the first to know about upcoming airdrops?
Subscribe to the CoinGecko Daily Newsletter!
Join 600,000+ crypto enthusiasts, traders, and degens in getting the latest crypto news, articles, videos, and reports by subscribing to our FREE newsletter.
Tell us how much you like this article!
Vote count: 7
Jackson Henning
Jackson Henning
Jackson Henning has a background in economics and has spent 3 years in crypto, primarily ensconced in NFTs and DeFi. He is especially intrigued by the perpetual ingenuity common to crypto trading, along with the employment of technical analysis and the ability of DeFi to push the bounds of traditional finance. Follow the author on Twitter @Henninng

Related Articles

Select Currency
Suggested Currencies
USD
US Dollar
IDR
Indonesian Rupiah
TWD
New Taiwan Dollar
EUR
Euro
KRW
South Korean Won
JPY
Japanese Yen
RUB
Russian Ruble
CNY
Chinese Yuan
Fiat Currencies
AED
United Arab Emirates Dirham
ARS
Argentine Peso
AUD
Australian Dollar
BDT
Bangladeshi Taka
BHD
Bahraini Dinar
BMD
Bermudian Dollar
BRL
Brazil Real
CAD
Canadian Dollar
CHF
Swiss Franc
CLP
Chilean Peso
CZK
Czech Koruna
DKK
Danish Krone
GBP
British Pound Sterling
GEL
Georgian Lari
HKD
Hong Kong Dollar
HUF
Hungarian Forint
ILS
Israeli New Shekel
INR
Indian Rupee
KWD
Kuwaiti Dinar
LKR
Sri Lankan Rupee
MMK
Burmese Kyat
MXN
Mexican Peso
MYR
Malaysian Ringgit
NGN
Nigerian Naira
NOK
Norwegian Krone
NZD
New Zealand Dollar
PHP
Philippine Peso
PKR
Pakistani Rupee
PLN
Polish Zloty
SAR
Saudi Riyal
SEK
Swedish Krona
SGD
Singapore Dollar
THB
Thai Baht
TRY
Turkish Lira
UAH
Ukrainian hryvnia
VEF
Venezuelan bolívar fuerte
VND
Vietnamese đồng
ZAR
South African Rand
XDR
IMF Special Drawing Rights
Cryptocurrencies
BTC
Bitcoin
ETH
Ether
LTC
Litecoin
BCH
Bitcoin Cash
BNB
Binance Coin
EOS
EOS
XRP
XRP
XLM
Lumens
LINK
Chainlink
DOT
Polkadot
YFI
Yearn.finance
Bitcoin Units
BITS
Bits
SATS
Satoshi
Commodities
XAG
Silver - Troy Ounce
XAU
Gold - Troy Ounce
Select Language
Popular Languages
EN
English
RU
Русский
DE
Deutsch
PL
język polski
ES
Español
VI
Tiếng việt
FR
Français
PT
Português
All Languages
AR
العربية
BG
български
CS
čeština
DA
dansk
EL
Ελληνικά
FI
suomen kieli
HE
עִבְרִית
HI
हिंदी
HR
hrvatski
HU
Magyar nyelv
ID
Bahasa Indonesia
IT
Italiano
JA
日本語
KO
한국어
LT
lietuvių kalba
NL
Nederlands
NO
norsk
RO
Limba română
SK
slovenský jazyk
SL
slovenski jezik
SV
Svenska
TH
ภาษาไทย
TR
Türkçe
UK
украї́нська мо́ва
ZH
简体中文
ZH-TW
繁體中文
Log in
By continuing, you agree to CoinGecko Terms of Service and acknowledge you’ve read our Privacy Policy
or
Forgot your password?
Didn't receive confirmation instructions?
Resend confirmation instructions
Sign up
By continuing, you agree to CoinGecko Terms of Service and acknowledge you’ve read our Privacy Policy
or
Password must contain at least 8 characters including 1 uppercase letter, 1 lowercase letter, 1 number, and 1 special character
Didn't receive confirmation instructions?
Resend confirmation instructions
Forgot your password?
You will receive an email with instructions on how to reset your password in a few minutes.
Resend confirmation instructions
You will receive an email with instructions for how to confirm your email address in a few minutes.
Get the CoinGecko app.
Scan this QR code to download the app now App QR Code Or check it out in the app stores
coingecko
Continue in app
Track prices in real-time
Open App