Locked lesson.
About this lesson
Create a Stock Chart in Excel, also known as a financial chart, to visualize the price movements of a stock over a certain period.
Exercise files
Download this lesson’s related exercise files.
4.11 stock-charts - Exercise.docx131.1 KB 4.11 stock-charts - Exercise solution.docx
5.8 MB Exercise - Stock Charts.xlsx
10.4 KB Exercise - Stock Charts - Solution.xlsx
15.9 KB Workbook - Stock Charts.xlsx
10.5 KB Workbook - Stock Charts - Complete.xlsx
21.4 KB
Quick reference
Stock Charts
Visualize the price movements of a stock with Stock Charts.
When to use
We use a Stock Chart whenever we are interested in tracking stock prices over a period of time.
Instructions
A stock chart in Excel, also known as a financial chart, is specifically designed to visualize the price movements of a stock over a certain period. These charts are useful for financial analysis and tracking stock performance. They can represent various data points such as the opening, closing, high, and low prices of a stock, and are particularly valuable for identifying trends and patterns in financial markets.
Set up the Stock Chart
In this example, we are going to track stock prices for two stocks: Amazon (AMZN) and Apple (AAPL). We are going to set the chart up so it's dynamic and we can toggle between both stocks.
Create a Data Validation List
We want our users to be able to select a stock from a list. For this, we can create a data validation drop-down list.
- Click in cell C6.
- From the Data tab, in the Data Tools group, click Data Validation.
- From the Settings tab, in the Allow list choose List.
- Click in Source and select the cells that contain the company names.
- Click OK.
Create a Lookup Formula
Let's create a formula to look up the ticker code based on the stock name.
- Click in cell C4.
- Type =XLOOKUP(C6,U4:U5,T4:T5)
This formula looks up the stock name in cell C6 in the cell range U4:U5 (Stock column) and returns the ticker code from cell range T4:T5.
Return Stock Prices with STOCKHISTORY
- Click in cell C9.
- Type the formula: =STOCKHISTORY(C4,E4,G4,0,0,0,2,3,4,1)
This formula uses the ticker code in cell C4, the start date in cell E4 and the end date in cell G4 to return the relevant stock prices. We can also tell the formula if we want daily (0), weekly (1), or monthly (2) stock prices.
If we don't have the headers in the worksheet, we can also tell the formula which headers we want in the properties argument.
Insert a Stock Chart
- Click in the data.
- From the Insert tab, in the Charts group, click the diagonal arrow.
- From All Charts, choose Stock.
There are 4 versions of the stock chart but not all will be suitable for your data. This is determined by the order of our columns.
For example, our columns are Date, Open, High, Low, and Close but the chart currently selected is a High, Low, Close chart so this is not suitable for our data.
- Select the second chart.
- Click OK.
The chart will change depending on which stock we have selected.
Format the Chart
Change the Axis
We need to ensure we can see all the data from both companies on the chart.
- Click on the vertical axis and press CTRL+1.
- From Axis Options, change the minimum bound to 170 and the maximum bound to 230.
We can change the color or the boxes and add a chart title in the usual way.
Login to downloadSorry, we don’t have a transcript for this lesson yet.
Lesson notes are only available for subscribers.