How to Make a Stock Chart in Google Sheets

Want to track your stock portfolio without leaving your spreadsheet? Google Sheets has a powerful, built-in way to pull live financial data and turn it into a professional stock chart. This article will show you exactly how to do it in just a few steps. We'll cover pulling historical data for any stock and then visualizing that data with a classic candlestick chart.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

First Things First: Getting Your Stock Data into Google Sheets

The magic behind pulling stock data into your sheet is a single function: GOOGLEFINANCE. This versatile function can fetch current or historical securities data directly from Google Finance, giving you access to real-time prices, historical trends, and key financial metrics without ever needing to copy and paste.

The GOOGLEFINANCE Function Explained

Before building a chart, you need data. The GOOGLEFINANCE function formula looks a bit intimidating at first, but its core components are quite simple. The full syntax is:

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

Let's break down the essential parts:

• ticker: The stock symbol for the security you want to track. It's crucial to prefix this with its exchange to avoid ambiguity. For example, use "NASDAQ:GOOGL" for Alphabet, "NYSE:F" for Ford, or "TSE:SHOP" for Shopify on the Toronto Stock Exchange.
• attribute: This tells Google Sheets what data you want. For a single piece of current data, you could use "price". For building a historical chart, we'll want to pull a full dataset by using "all".
• start_date & end_date: These define the historical timeframe you're interested in. You can type dates directly (like "1/1/2023") or use functions like TODAY() for dynamic date ranges.
• interval: This can be set to "DAILY" or "WEEKLY". The default is "DAILY".

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step-by-Step: Pulling Historical Stock Data for Your Chart

To create a proper stock chart, you need historical daily performance. Specifically, you need the opening price, the high and low for the day, and the closing price. The GOOGLEFINANCE function makes this nearly automatic.

1. Set Up Your Sheet and a Ticker Cell:

Open a new Google Sheet. To make our formula flexible, we’re going to put our stock ticker in its own cell. In cell C1, type your company’s stock ticker. For this example, we’ll use Apple, so type in NASDAQ:AAPL. We'll pull in an end date and start date to easily change date ranges.

1. Label Date Range Cells and Enter Dates:

Beside C1, in C2, enter ‘Start date’, and in C3, enter ‘End date.’ Let’s use a one-year period for now, so in D2, enter any start date, for example, 1/1/2023. And for the end date in D3, enter 1/1/2024.

1. Label Your Stock Data Columns:

Now label five columns: Date, Open, Close, High, and Low.

1. Enter the GOOGLEFINANCE Formula:

With our reference cell (C1), our date range (D2 and D3), and column headings set, you can now enter your stock data formula in A2. Using our cell references, our formula will have three parameters and looks like this:

=GOOGLEFINANCE(C1, "all", C2, C3)

When you press enter, Google Sheets will automatically fill in the daily Date, Open, High, Low, Close, and Volume data for the date range you specified.

Pro Tip: You can make the date range dynamic by using the TODAY() function. For example, to always show the last 365 days of data, use this formula instead:

=GOOGLEFINANCE(C1, "all", TODAY()-365, TODAY())

Now your data will always be current!

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Preparing Your Data for the Chart (An Essential Step)

This is where many people get stuck. We now have a table full of perfectly good financial data, but there's a small discrepancy between how GOOGLEFINANCE outputs data and how Google Sheets' Candlestick chart expects to receive it.

Take a look at the data your formula just pulled in. The column order is:

Date | Open | High | Low | Close | Volume

However, for a Candlestick chart to render correctly in Google Sheets, it requires the underlying data to be in this very specific order:

Date | Low | Open | Close | High

If you don't reorder your columns, the chart won't make sense. Trying to do this by copying and pasting a newly sorted section is cumbersome, but luckily, we can use another simple formula: QUERY to sort this out on the fly.

The Fix: Reorder Data in Seconds

1. In G2 enter "Date". Subsequently in G3-K: "Low", "Open", "Close", and "High".
2. Now fill in your query in G2 as below:

=QUERY(A2:F, "SELECT A, C, D, E, B")

This form will now create a perfectly ordered table for a candlestick chart!

From Data to Visuals: Building Your Stock Chart

With your data reordered, you can visualize your stock data.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Creating a Candlestick Chart

1. Select Your Chart-Ready Data:

Click and drag to select your newly reordered data from columns G to K.

1. Insert a Chart:

Go to Insert > Chart.

1. Choose Chart Type:

To let the chart wizard auto-generate your default graph, look on the sidebar for a dropdown menu. Scroll down to find the Candlestick chart and select it. Now, your chart will display in the spreadsheet. You can customize your chart to suit your needs.

Making It Yours: Customize Your Chart

Since you've now generated a basic, useful chart, we can make it even better with some simple adjustments. In the Chart Editor sidebar, click the Customize tab to start.

• Chart Title: Give your chart a clear headline that describes it, such as ‘AAPL Stock Performance’.
• Vertical Axis: Make sure this is labeled to reflect the price accurately.
• Colors: Adjust the up and down colors, which enhance the readability of your chart.
• You can change the appearance of gridlines or background colors to suit your preferences.
• Use moving averages (e.g., a 7-day or 30-day moving average) to analyze price trends. You can achieve this in Sheets by creating an average line using the column. For example, to locate a moving average for your closing prices in column E, you can write a formula in F2 such as: =AVERAGE(E2:E365). Drag your formula down to apply it to the rest of the row.
• To add a moving average line in your chart, add it as a separate series in your chart type ‘line’. It will enhance your candlestick chart by adding an additional layer of information.

Final Thoughts

Creating stock charts in Google Sheets is a powerful way to track your investments. With the GOOGLEFINANCE function, you can pull live data and visualize it with a candlestick chart. You've turned dynamic data into a clear visual reference of your figures without any manual calculations.

While building these charts, remember that they serve as an insightful framework for analyzing data from personal finances to CRMs, providing a full picture at a glance. Using tools like Graphed can further streamline the process, focusing on automating intricate formulas and allowing you to concentrate on strategy rather than manual spreadsheets.