How to build stock charts in Google Sheets & Excel

1. Manual ways to build stock charts in Excel and Google Sheets

These are the classic, hands on methods every analyst starts with. They are perfect for learning the mechanics and for one off analysis.

‍

1.1 Basic stock chart in Excel (High Low Close)

1. Collect your data so each row is a date and columns include at least High, Low, Close. If you also have Open and Volume, keep them in separate columns.
2. Order your columns to match the stock chart type you want. For a High Low Close chart, the order must be: High, Low, Close. For Open High Low Close: Open, High, Low, Close. For Volume High Low Close: Volume, High, Low, Close.
3. Select the data range, including the date column and the value columns.
4. In Excel, go to Insert, then select the Waterfall, Funnel, Stock, Surface or Radar icon in the Charts group.
5. Under Stock, pick the desired chart type (for example High Low Close). Excel will insert a stock chart.
6. Use the Chart Elements plus icon to add axes titles, data labels, legends, or a chart title, then right click to format line colors, up down bars, and gridlines.

You can see the official Microsoft guide to creating charts here: https://support.microsoft.com/en-us/office/create-a-chart-from-start-to-finish-0baf399e-dd61-4e18-8a73-b3fd5d5680c2

‍

1.2 Dynamic stock price history chart in Excel

For ongoing analysis, Excel 365 includes the STOCKHISTORY function.

1. In a blank sheet, add Start Date and End Date input cells.
2. Enter a ticker symbol (for example MSFT) and convert the cell to the Stocks data type using the Data ribbon.
3. In a new table, use the STOCKHISTORY function, for example:
   =STOCKHISTORY(ticker_cell, start_date_cell, end_date_cell, 0, 0, 0, 1, 5)
   This pulls date, close, and volume data.
4. Select the resulting date and close columns and insert a Line chart via Insert, Line.
5. Use named ranges and the Select Data dialog so when STOCKHISTORY spills more rows, your chart extends automatically.

A full walkthrough is available in Microsofts article on stock price history charts: search for How to create a dynamic stock price history chart in Microsoft Excel.

‍

1.3 Basic stock chart in Google Sheets

Sheets does not have a dedicated stock chart type, but you can effectively replicate one using line charts and the GOOGLEFINANCE function.

1. In a sheet, type a ticker in a cell, for example GOOG.
2. Use GOOGLEFINANCE to pull historical data:
   =GOOGLEFINANCE("GOOG", "price", DATE(2024,1,1), TODAY())
3. This returns a two column table: Date and Close.
4. Highlight the data range and click Insert, then Chart.
5. In the Chart editor, choose Line chart or Candlestick (if available), set Date as the x axis and Close as the series.
6. Customize axes, colors, and labels to match your reporting style.

See Googles official chart help here: https://support.google.com/docs/answer/63859

‍

1.4 Multi ticker comparison in Sheets or Excel

1. Pull data for multiple tickers into separate columns (either by STOCKHISTORY in Excel or GOOGLEFINANCE in Sheets).
2. Make sure the date column is consistent and aligned across tickers.
3. Select the full table, then insert a Line chart.
4. Use filters or slicers so stakeholders can toggle which tickers are visible.

Pros of manual methods: full control, no dependencies, great for learning; Cons: repetitive for many tickers or frequent updates, error prone when copying formulas, and time intensive for agencies and busy teams.

‍

2. No code automation with Sheets and Excel

Once you know the manual steps, you can start automating parts of the workflow without writing code.

‍

2.1 Use named ranges and templates

1. Build a master template workbook with your preferred stock chart layout, colors, axis formats, and annotations.
2. Convert your data table to an Excel Table (Insert, Table) or a named range in Sheets.
3. In Excel, charts auto expand with the table; in Sheets, set Series ranges to entire columns.
4. Each time you paste new stock data into the table, your charts refresh instantly.

‍

2.2 Automate data refresh in Google Sheets

1. Use GOOGLEFINANCE to always pull live or near live prices, with formulas configured for each ticker you care about.
2. Add Data, Named ranges so components of charts refer to ranges that grow automatically.
3. Configure triggers with Google Apps Script or simple Add ons so a summary PDF or link is emailed on schedule.

Official reference for GOOGLEFINANCE: https://support.google.com/docs/answer/3093281

‍

2.3 Connect Excel to web data

1. In Excel, use Data, From Web to connect to a stock data source or your broker CSV URL.
2. Use Power Query to clean and reshape the data so you get Date, Open, High, Low, Close, Volume.
3. Load the query into a table. Because your chart references the table, refreshing the query keeps charts in sync.
4. Optionally record a macro that refreshes all, then re exports charts to PDF, and assign it to a button.

Learn about Importing data from the web in Excel: https://support.microsoft.com/en-us/office/import-data-from-the-web-b13eed81-33fe-410d-9247-1747269c28e4

Pros: data flows in automatically, middle ground between manual and full AI; Cons: still brittle when websites change, scripting in Sheets or macros in Excel need maintenance, and someone must monitor failures.

‍

3. Scaling with AI agents (Simular)

At some point, chart building and maintenance becomes pure busywork. Maybe your agency tracks 50 client tickers, or your sales team wants a daily slide of your companys stock versus peers. This is where a Simular AI agent shines.

‍

3.1 AI agent to rebuild daily Excel stock dashboards

A Simular Pro agent can:

1. Open your brokerage or data vendor in the browser, export the days CSV, and save it.
2. Launch Excel, open the portfolio dashboard file, and paste new data into the right table.
3. Refresh Power Query connections, recalc formulas, and verify charts updated.
4. Export key charts as images or a PDF report and upload to a shared drive or send via email.

Pros: end to end hands off workflow, production grade reliability over thousands of steps, transparent logs of every click; Cons: requires initial onboarding time, and you should still review strategic outputs.

‍

3.2 AI agent to maintain Google Sheets reporting

Your Simular agent can:

1. Log into Google Workspace, open a Sheets workbook, and update control cells such as Start Date, End Date, or ticker lists.
2. Confirm that GOOGLEFINANCE calls refreshed correctly, checking for errors or missing data.
3. Duplicate your stock chart sheet per client, update tickers and formatting, and share or protect each sheet as needed.

Pros: scales across many sheets and clients, consistent formatting and naming; Cons: depends on Sheets and GOOGLEFINANCE availability, and you must define clear guardrails for account access.

‍

3.3 Blending AI agents with no code tools

The most powerful setup is often hybrid. Power Query or GOOGLEFINANCE handle structured data, while the Simular AI agent orchestrates everything: opening apps, validating values, handling logins, and pushing outputs where your stakeholders live. You remain the portfolio storyteller; the agent becomes the reliable junior analyst who never gets tired of pulling and charting the numbers.