Stock Chart in Google Sheets

What Is Stock Chart in Google Sheets?

A stock chart in Google Sheets is a type of chart used to visually represent the price movements of a stock over a specified period. It displays several key data points of the stock such as opening price, closing price, high, and low. This helps analysts track trends and make informed decisions. The stock charts are particularly useful for investors, analysts, and those in the financial field.

For example, let us suppose we have a dataset showing the daily stock prices of a company like Apple. These include details such as date, high, low, open, and close values. You can use Google Sheets to insert a stock chart. By selecting the relevant data and choosing the “Candlestick chart” type, Google Sheets will display a clear visual summary of how the stock performed each day.

How To Create A Stock Chart in Google Sheets?

Let us look at the step in detail to create a Stock Chart Google Sheets.

#1 – Utilizing GOOGLEFINANCE Function for Live Data

Step 1: Open a new Google Sheets document and select a cell to start entering data.

The GOOGLEFINANCE function helps us fetch real-time stock prices and historical market data directly into your spreadsheet. It is useful as there is no need for manual data entry and ensures that the stock data is always up to date.

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

• ticker: The ticker symbol for the stock to track. Don’t forget to enter the exchange symbol and ticker symbol. For example, “NASDAQ:GOOG” for Google stock.
• attribute (optional): The specific data point to fetch like “price,” “volume,” or “marketcap.” The default is “price.”
• start_date (optional): The beginning date for fetching the historical data. If it is given without an end_date or num_days, the data for that single day will be returned.
• end_date|num_days (optional): The end date for fetching historical data or the number of days from the start_date for which to return data.
• interval (optional): The frequency of the returned data, which can be either “DAILY” or “WEEKLY.” You can also use 1 for daily or 7 for weekly.

Step 2: Use the GOOGLEFINANCE function

=GOOGLEFINANCE(“NASDAQ:AAPL”, “price”, DATE(2025,1,1), TODAY()) to fetch live or historical stock data.

The function will populate the spreadsheet with columns such as Date and Closing Price automatically.

Step 3: Highlight the data that you get and go to Insert -> Chart, then select “Line chart” or “Candlestick chart” under Chart type. Here, we select a Line chart in Google sheets.

Step 5: Customize the chart using the Chart Editor to adjust titles, labels, and axes for better readability.

The customized chart is shown below.

#2 – Importing and Displaying Stock Charts in Google Sheets

As seen above, once we import stock data using the GOOGLEFINANCE function, we can make dynamic charts to visualize the information as required.

Google Sheets offers various charts for displaying stock data. These include bar charts, line charts, and candlestick charts.

Ensure the data obtained is in the correct format and in order to match Google Sheets’ requirements for stock charts. Select the entire data range and go to Insert -> Chart.

Step 4: In the Chart Editor, choose “Candlestick chart” to generate a financial-style chart.

Adjust visual elements like colors, axis titles, and gridlines to enhance the chart’s presentation.

When creating candlestick charts, the data is formatted correctly. The first column should contain dates. It is followed by columns for the high, low, open, and close prices. It is useful for analyzing price movements and patterns over a specific period.

Examples

Here are a few short introductory examples of a stock chart in Google Sheets, written in clear and professional English:

Example #1 – Visualize daily stock price movements (open, high, low, close) for Apple Inc. over the past month

To visualize daily stock price movements (open, high, low, close) for Apple Inc. (AAPL) over the past month in Google Sheets, we use the GOOGLEFINANCE function and then create a bar chart. It will allow us to see the daily price changes, including the highs, lows, and closing prices, in a visually appealing way.

Step 1: Set up the spreadsheet.

In the first Column A, we use the GOOGLEFINANCE function to retrieve the daily stock data. For example, in cell B1, we enter

=GOOGLEFINANCE(“AAPL”, “all”, DATE(2025,5,1), DATE(2025,5,7), “DAILY”)

to get the opening price for May 1, 2025, to May 7th, 2025.

The GOOGLEFINANCE function can handle a range of dates. You can specify a start date and end date, or several days to retrieve. For example, to get data for the last month, you can use the current date as the end date and subtract 30 days from the start date.

Step 2: Select the data to make as a chart. Go to “Insert” and then “Chart.”

Google Sheets will suggest a default chart type. Click on the chart to open the Chart Editor and select “Bar Chart.”

You can customize the chart further by adjusting the color, labels, and other visual aspects.

You can also add a volume chart to show the trading volume for each day. Use the GOOGLEFINANCE function to retrieve the volume data and then create a column chart to visualize it.

Example #2 – Analyze Bitcoin’s daily trading range using historical OHLC data

A stock chart in Google Sheets can be used to visualize Bitcoin’s price movements using historical data. It helps in identifying trends, volatility in the markets, and price shifts over time. The candlestick format is especially effective for spotting patterns in crypto trading.

We can enter the daily OHLC data for Bitcoin manually or through a GOOGLEFINANCE or API import in Google Sheets. Here, we extracted the data manually from investing.com.

One can create a candlestick chart that shows how Bitcoin’s price varied in May for each day.

Step 1: Enter the data as shown below for May 1^st to 27^th, 2025.

Step 2: Here, we have formatted the first Date column to text under Format -> Number-> Plain text. It is for the Candlestick chart to work. Next, go to Insert -> Chart.

Step 3: Next, under the Chart Editor, choose “Candlestick Chart.” You get the candlestick chart for displaying Bitcoin’s daily trading range in the month of May 2025.

Example #3 – Create a stock chart to evaluate weekly trends in Tesla stock using open and close prices

Let us create a stock chart in Google Sheets to visualize Tesla’s (TSLA) weekly trends using open and close prices, follow these steps.

Step 1: For this, let us first gather the data. We can use the GOOGLEFINANCE function. In a Google Sheet, enter

=GOOGLEFINANCE(“NASDAQ:TSLA”,”all”,TODAY()-365,TODAY(),”WEEKLY”) in a cell.

It will help get Tesla’s open, high, low, close, and volume data for the past year at a weekly interval.  As we could be using it on any date, adjust the TODAY()-365 part to control the data timeframe.

The GOOGLEFINANCE function will return columns for Date, Open, High, Low, Close, and Volume.

Step 2:  If necessary, one can add a “Week” column to extract the week number using a formula like =WEEKNUM(A2,1)

Step 3: Highlight the columns with Date, Open, and Close.

Go to Insert > Chart.

Choose Line chart.

Step 4: Adjust the labels on the x-axis, if required. You can also add a chart titled “Tesla (TSLA) Stock – Weekly Open/Close Prices”.

Important Things to Note

1. Using this feature, one can analyze cryptocurrencies like Bitcoin using historical data as GOOGLEFINANCE doesn’t support most crypto assets. One must import the data manually or via a plugin/API.
2. We should not clutter our chart with too much data. It is best to focus on the most important metrics that address your primary requirements.

Frequently Asked Questions (FAQs)

Download Template

This article must help understand Stock Chart in Google Sheets with its formulas and examples. You can download the template here to use it instantly.

Recommended Articles

Guide to What Is Stock Chart in Google Sheets. We learn how to create a stock chart in google sheets with its examples. You can learn more from the following articles. –

What-If Analysis in Google Sheets

CHISQ.DIST in Google Sheets

COUPDAYS In Google Sheets