Indzara

How to Create a Dynamic Stock Price History Chart in Microsoft Excel?

Welcome to the Data to Decisions series, where we empower you with the skills to turn raw data into insightful visuals.

In this blog, we’ll walk you through the process of building a stock price history chart in Microsoft Excel, one that is interactive and dynamic in nature. To analyze markets, mastering the art of charting stock prices is a crucial skill, with Excel and this tutorial, all that you need is a few minutes to build this amazing, stock chart!

Create a Dynamic Stock Price History Chart sample

By the end of this post, you’ll be able to create a detailed and dynamic stock price history chart like the one featured above displaying Min/Max points, with slicers of dynamic intervals and historic periods, and the red and green volume columns.

Let’s get started!!

Before we get into the steps involved in creating this stock price chart, please note that the chart pulls stock data from Microsoft Servers, and to be able to achieve this, you’ll need a Microsoft 365 subscription.

Step 01:

Begin by entering “START DATE” and “END DATE” in cells G2 and G3 of a blank Excel workbook.

Create a Dynamic Stock Price History Chart Enter Start Date and End Date

The end date will be today since we’ll use this chart to pull historical data until the current date. So, the formula in cell H3 will be

For the start date, let us take the same as the 1st of January, 2024. With these, the cells H2 and H3 will be:

Create a Dynamic Stock Price History Chart Enter Start Date and End Date

If the date is not displayed in this format for you, please select the necessary cells, press CTRL+1 to open format pane from where you can modify the format to a “dd-mmm-yyyy” format.

Step 02:

The next input needed is the stock symbol for which the prices will be fetched. Enter “SYMBOL” in cell G5 and for this example, consider extracting the prices of Microsoft Corp., the stock ticker symbol for this is “MSFT” which goes in cell H5, as shown below.

Create a Dynamic Stock Price History Chart Enter Symbol

Once entering the data, we need to change the data type of MSFT to a stock data type for Excel to recognize this as a stock ticker. To do this, click on MSFT, and in the Data ribbon, choose the Stocks data type.

Create a Dynamic Stock Price History Chart choose stock type

This prompts Excel to ask which “MSFT” as a stock are you referring to in a Data Selector Pane that opens to the right. Choose the stock that corresponds to the ticker, in this case Microsoft Corp as shown below.

Create a Dynamic Stock Price History Chart choose the stock

After selecting the stock, the inputs cell should appear something like this:

Create a Dynamic Stock Price History Chart after selecting the stock

Check our free Excel template, the Stock Lookup in Excel to

Step 03:

Before we get into the next steps, click on the cell with the stock ticker symbol, i.e.H5, and assign a name for the same as “I_SYM”. Do this by using the name box at the top left corner, as shown:

Create a Dynamic Stock Price History Chart assign name to the stock ticker

To get the stock history prices, we’d need additional columns as shown below:

Create a Dynamic Stock Price History Chart add additional columns

To get these historical attributes of stock, we use the STOCKHISTORY function in Excel.

The syntax for this function is:

Create a Dynamic Stock Price History Chart use STOCKHISTORY function

Ticker symbol: the stock symbol we need to retrieve the prices for (here, I_SYM)

Start and end dates: in cells H2 and H3 respectively.

Interval: 0 – daily; 1 – weekly; 2 – monthly

Headers: 0 – no headers; 1 – basic headers

Properties are the attributes that need to be retrieved,

0 – Date

1 – Close

2 – Open

3 – High

4 – Low

5 – Volume

With this, our formula to extract the date, closing price and volume for a daily interval would be:

With this, the retrieved information would be:

Create a Dynamic Stock Price History Chart retrieved information

For ease of use and understanding the dates, select all the column K, press CTRL+1 to open the formatting dialog box. Here, change the date format to “dd-mmm-yyyy”.

Create a Dynamic Stock Price History Chart change date format

With this, the date format gets changed as below:

Create a Dynamic Stock Price History Chart date format changed

Try changing the start date in the input cell, H2. Two key points are to be noted here:

  1. Even though the intervals are daily, the dates do not show all consecutive days. Meaning, that the days when the stock market is closed will be skipped.
  2. With every change in dates, the rows of data that gets displayed are dynamic.

To make the final chart adapt to the dynamic nature of the data, create named ranges for each of the Date, Closing Price, and Volume.

The names can be assigned by:

  1. Go to the Formulas tab, and click on Define Name which opens a dialog box.
  2. Here, use an INDEX formula to refer to the first cell of Dates column i.e. K3 and use “#” to ensure this is dynamic.
  1. Assign a name, say “R_DATES”.
Create a Dynamic Stock Price History Chart use INDEX function

Repeat the same steps for the Closing Price column. Assign a name as “R_PRICE” and here, point the INDEX function to the second column in the third argument.

Create a Dynamic Stock Price History Chart point INdex function to secoond column

Step 04:

With these preparatory steps, its time to create the stock price chart. Click on a blank cell, go to the Insert tab and choose a Line chart.

Create a Dynamic Stock Price History Chart choose line chart

This creates an empty chart. Time to add our data: right-click on the chart and choose Select data.

Create a Dynamic Stock Price History Chart select data

This opens a pop-up to add our series.

Create a Dynamic Stock Price History Chart add the series

Firstly, we’ll add the Closing Price data, the series name is “Closing Price” or yo can choose the cell L2 which has this header.

For the series values, since we need the chart to be dynamic, we’ll use the R_PRICE named range created. Make sure to add the sheet name followed by an ! before the named range, as shown in the image below:

Create a Dynamic Stock Price History Chart edit series name and values

For the X-axis, the label range values are that of the Dates column, hence we’ll use the named range R_DATES, as shown here:

Create a Dynamic Stock Price History Chart edit axis labels

After this input, the populated chart looks as shown below:

Dynamic Stock Price History Chart populated chart after input

Try modifying the starting date to 1-Jan-23 and any other ticker symbol, the chart should fetch and populate accordingly.

Dynamic Stock Price History Chart modify start date and ticker symbol

Step 05:

Now, let’s format this chart before we proceed further.

  1. Right-click on the gridlines, go to format gridlines and choose a line color to make this less apparent.
Dynamic Stock Price History Chart format grid lines
  1. Change the chart title to suit our analysis, and format it as well.
  2. Click on chart, modify the chart border.
Dynamic Stock Price History Chart modify chart border

Step 06:

Let’s now format the axis. To do this, right-click on the Y-axis, choose Format axis.

Dynamic Stock Price History Chart  format axis

Reset the minimum to 0. Change the decimal point to 0

Dynamic Stock Price History Chart reset  minimum bound
Dynamic Stock Price History Chart change decimal point

Now, right-click on the X-axis, choose the format axis as above, and modify this to a text axis.

Dynamic Stock Price History Chart modify to text axis

In the Size & Properties option, align the x-axis text as shown below:

Dynamic Stock Price History Chart align x axis

With these changes, at the end of this step, our chart looks as below:

Default Stock Price History Chart


Step 07:

Let’s now shift our focus to creating interactive slicers of intervals and periods for our stock price chart. To do this, open a new sheet in the same Excel workbook.

It is advisable to keep a helper sheet to contain information that need not be present on the sheet where you have the chart but are useful in your chart creation process.

Add the three intervals: Daily, Weekly, and Monthly and select all press CTRL + T to make them as a table as shown here below:

Default Stock Price History Chart add three intervals

Now, with any cell inside the table selected, go to the insert tab and insert a Pivot Table.

Default Stock Price History Chart insert pivot table

This opens a pop-up, insert the pivot table within the same sheet in another location.

Default Stock Price History Chart insert pivot tablewithin the same sheet

Drag the Intervals as Pivot Rows.

Default Stock Price History Chart intervals as pivot rows

After this, select anywhere inside the pivot table, and from Insert ribbon, add a slicer as shown:

Default Stock Price History Chart add slicer
Default Stock Price History Chart insert slicer

With this, an interval slicer gets added.

Default Stock Price History Chart interval slicer gets added

When any interval is clicked, the pivot table automatically displays only the chosen value in cell B12.

Default Stock Price History Chart displays only chosen value

Step 08:

The value from B12 now should correspond to one of the numbers:  0 – daily; 1 – weekly; 2 – monthly. To get this, we’ll use a SWITCH function, the syntax for which is:

Default Stock Price History Chart use SWITCH function

In our case, depending on the value of B12, the formula in cell B9 would be:

Default Stock Price History Chart use SWITCH function

Similar to Step 03, let’s name this cell B9 which has the interval value “I_INTERVAL”.

Default Stock Price History Chart name the cell

Step 09:

It’s time to move our slicer to the sheet where we have our chart. Click on the slicer and do a simple cut (CTRL+X) and paste (CTRL+V).

Here, click on the slicer, and in the “Slicer” ribbon, change to 3 columns and resize to be on top of your chart.

Default Stock Price History Chart change number of columns

Now, click on the slicer settings from top left

and from the pop-up that opens up, un-check the Display Header:

Default Stock Price History Chart uncheck display header

After this step, your chart and slicer should look like this:

Default Stock Price History Chart chart and slicer

Step 10:

It’s time to connect the slicer to the chart, to make this interactive. In our previous step, step 08, we’ve created a named range I_INTERVAL to denote the interval being chosen. This, we’ll replace in our original STOCKHISTORY formula (from step 03).

Once this change is made, check the slicer to see the chart change based on the interval chosen.

To ensure that the slicer has an order of appearance like, Daily, Weekly and then Monthly, there a few quick steps that needs to be followed.

  1. Go to the sheet where the Interval table is present (refer step 07), select all the values and go to File -> Options
  2. In the dialog box that opens up, go to Advanced and Edit Custom List, as shown:
Default Stock Price History Chart edit custom list
  1. This opens a pop-up from where you can import the list from the table as shown:
Default Stock Price History Chart import list from table
  1. Click OK in the Excel options dialog box, then go to chart to check if the order has changed.
  2. If not, then click on the chart and open slicer settings from the top-left (refer to step 09) and change the order to descending and then back again to ascending.

Descending:

Default Stock Price History Chart descending slicer settings

Ascending:

Default Stock Price History Chart ascending  slicer settings

With this, our ordered slicer looks as below, which when changed, automatically changes the chart based on the chosen interval.

Default Stock Price History Chart order slicer

Step 11:

In this and the following steps, our focus in on displaying the Min/Max price points on the line chart.

To do this, we need to add additional columns to the data.
The MIN, ideally should be the minimum value of the price from our price column (R_PRICE). The formula for the same will be:

In the same way, the MAX will be the maximum value of the price from our price column (R_PRICE). The formula for the same will be:

With these additional columns, our new data would be:

Default Stock Price History Chart new data

Step 12:

Since with the remaining columns, the MIN/MAX are dynamic, we’ll assign named ranges for these as well. Do so by clicking on the first cell of Min value, N3 and go to Formulas -> Define Name.

Here, to make the rows dynamic, include a # at the end of the cell reference and assign a name, say R_MIN.

Default Stock Price History Chart assign name to the series

Similarly, assign a name to the Max series, R_MAX as shown below:

Default Stock Price History Chart assign name to the series

Step 13:

Now,let’s add these newly created series to the chart. Right-click on the chart and select data. From the dialog box that appears, add a new series as shown below:

Default Stock Price History Chart add newly created series

Edit the horizontal axis as well:

Default Stock Price History Chart edit horizontal axis

Here, since the horizontal axis is dates, use the R_DATES named range created for the same.

Default Stock Price History Chart use the named range created

Once this is done, the data select dialog box should look like this:

Default Stock Price History Chart data select dialog box

In the same way, add the MAX series as well and edit the horizontal axis too.

Default Stock Price History Chart add the series

Note: Here, in my Excel, Demo is the sheet where my chart and its data are present. Use your sheet name followed by a “!” accordingly. To correctly add this, click on any blank cell and delete till the “!” and then add your named range.

Step 14:

As a next step, right-click on the chart and go to Change chart type.

In combo, modify the chart type as shown below:

Default Stock Price History Chart modify chart type

With this change, our chart with min/max points takes shape and looks as below:

chart with min max series

Step 15:

To make this into a desired format, click on the chart and press CTRL+1 to open the format pane.

  1. In the drop-down, choose the MIN series.
Default Stock Price History Chart choose MIN series
  1. In fill & line, choose No Line option.
Default Stock Price History Chart  choose no line
  1. In the Marker options, choose a built-in circle style marker and increase the size, assign a suitable color to depict a min value.
Default Stock Price History Chart  choose built in circle marker and color

Repeat the same steps for the “Max” series by choosing it from the format pane drop-down. Edit the markers accordingly.

Default Stock Price History Chart  choose built in circle marker and color

Step 16:

After adding and formatting the min/max points, let us now add labels to this. From the drop-down choose the Min series and from the “+” icon on chart add data labels and go to More options:

Default Stock Price History Chart  add data labels

Here, include the categories as well to be displayed in a new line and since this is a Min point, position the label below.

Default Stock Price History Chart  include category name

Now, choose the Max series, add the data labels and position them above with the category name.

Default Stock Price History Chart  add data labels

Try changing the dates, and the intervals and see the chart dynamically update with min/max points.

Default Stock Price History Chart  change dates and intervals

Step 17:

Till now, we have created the price history chart, and added the interactive, dynamic interval slicer and the min/max points.

In the next few steps, let us create the interactive time period slicer to choose the look-back period for your chart.

Let’s get to the helper sheet (refer step 07) where we’ve created a pivot table. Here, add the necessary time period data and covert the same into a table (CTRL+T).

Default Stock Price History Chart  add necessary time period

Here the periods are: five days (5D), month-to-date (MTD), one month (1M), three months (3M), six months (6M), year-to-date (YTD), 1 year (1Y) and 5 years (5Y).

Step 18:

Similar to Step 07, create a pivot table for this period data in the same sheet.

Default Stock Price History Chart  create pivot table

Click inside the pivot table, from Insert -> add Slicer.

Default Stock Price History Chart  add slicers

Based on the Slicer selection cell B30 records the same. Assign a name to the same as “I_PERIOD”

Default Stock Price History Chart  assign name to the slicer

Based on the period selection, the starting date needs to be changed accordingly for our chart to use.

For each of the 5D, MTD, 1M etc let us see how to use some Excel functions to achieve it.

First, enter the current date (=TODAY()) and assign a name to this as “TD”.

Default Stock Price History Chart  enter current date

The start date for each of the period can be achieved as follows:

  1. 5D: This will be today minus four days, in cell D18 the formula would be
  1. MTD: Use EOMONTH to get the end of previous month and add 1 to get to the next date i.e. the beginning of the current month, in cell D19 the formula would be
  1. 1M: Use EDATE to go back exactly 1 month from today i.e TD, in cell D20 the formula would be
  1. 3M: Similar to 1M, use EDATE and go back three months, in cell D21 the formula would be
  1. 6M: Same as 1M and 3M, use EDATE, in cell D22 the formula would be
  1. YTD: Use a combination of DATE and YEAR functions to get the first date of the current year, in cell D23 the formula would be
  1. 1Y: Here, we’ll use EDATE to go back 12 months, in cell D24 the formula would be
  1. 5Y: Similar to above, go back 60 months using EDATE to represent 5 years

With these formulas, the corresponding start dates for the various periods are as shown:

Default Stock Price History Chart formulas are shown

Step 19:

With the list of potential start dates, let us use the SWITCH function and the I_PERIOD assigned name to get the date corresponding to the period slicer selection.

That is based on the selection from I_PERIOD, the SWITCH function needs to return the corresponding start date value. (Refer to the syntax for SWITCH in step 08).

In cell F17, the formula would be:

Let’s assign this date a name, say “C_START_DATE” before we proceed to tie this to our chart.

Default Stock Price History Chart  assign date a name

Step 20:

Go back to the sheet where we have the chart, and change the Start date (which we have manually entered so far) to =C_START_DATE.

Default Stock Price History Chart  change start date

Cut the period slicer (CTRL+X, from the helper sheet) and paste it (CTRL+V) into the sheet with the chart. Similar to step 09, let us format the slicer to be more visually appealing.

Default Stock Price History Chart  format the slicer

Also, remove the slicer header (refer step 09) and position it on top of the chart as needed.

Remove slicer header

Now, similar to changing the order of the appearance of slicer items in the interval slicer in step 10, we’ll do the same and get a custom list for the period slicer. Follow the steps in the step 10 for the Period table data and include a custom list as shown:

Default Stock Price History Chart  get custom list

If the slicer is not changed to the right order, go to slicer settings, and change the sorting order to descending and back to ascending order.

Default Stock Price History Chart  change sorting order

Test with a new stock symbol and apply the interval and the period slicer to get the chart updated accordingly. By the end of this step, your chart should look something like this:

Test with a new stock

Step 21:

In the final leg of building our interactive and dynamic Stock Price History chart, the next few steps will focus on building the volume bars in the chart. We’ll have additional columns for the green and red volume columns.

Since we’ll use the Volume column here, let us assign a name for this (as we did earlier for dynamic arrays, refer step 03). Please note that the Date, Closing price, and Volume data come from a single formula from cell K3. So, click on K3, and in the Formula ribbon, go to Define Name and assign R_VOL to the volume column.

Default Stock Price History Chart  assign a name

Now the formula for the GREEN VOL column should check the current row’s price and if it is greater than that of the previous row’s then this comes under green volume otherwise, we’ll return an NA.

Similarly, if the price is less than the previous price, we get the RED VOL.

With these addition, our data looks like this:

Default Stock Price History Chart  new data

Step 22:

Before adding these new columns to our chart, let us assign them names. Similar to the previous steps, go the Formulas and add a new name. For Green vol, assign “R_GREEN” and

Default Stock Price History Chart  add new columns to the chart

For red vol column assign the name as “R_RED”.

Default Stock Price History Chart  add new columns to the chart

Ensure that the “#” follows the cell to make this dynamic.

Step 23:

Time to add these to our chart, right-click on the chart and go to select data. Add the green vol series and ensure the series value refers to the named range created. (refer step 14)

Default Stock Price History Chart  add named range created

Similarly, add the red vol column.

Default Stock Price History Chart  add named range created

Adding these series would have altered the appearance of the chart. To get the desired chart style, right-click and go to Change chart type.

In combo, ensure the chart types are as shown below:

Default Stock Price History Chart  add chart types

Step 24:

Click on chart and press CTRL+1 to open format pane. From drop-down choose Green Vol column (refer step 15) and modify the column color.

Default Stock Price History Chart  add chart column color

Similarly, do the same for the red vol column.

Default Stock Price History Chart  add chart column color

To ensure the columns are not very tall, for our final chart, we’ll add a DUMMY series to make them shorter. The value in this is, say four times the volume (to get a larger value).

Default Stock Price History Chart  add dummy series

Similar to all other columns, assign a name:

Default Stock Price History Chart  assign name

Similar to our previous steps, add the DUMMY series to the chart (right-click on chart , go to select data and add the series).

Right-click on chart and choose change chart type and while other series remain the same, ensure DUMMY series is a clustered column in primary axis.

Default Stock Price History Chart  choose chart type

In format pane, remove fill and line for this dummy series.

Default Stock Price History Chart  remove fill

Step 25:

As some final formatting, right-click on Y-axis and choose Format Axis to change the category to number and the display units in millions for a cleaner chart.

Default Stock Price History Chart   final formatting

With this, our final dynamic and interactive Stock History Price chart is ready for your use!

Stock Price History Final chart

Check Indzara’s free, downloadable Stock Price chart template that has all these steps already created for you, with a ready-to-use chart!

Also, check our collection of Stock Market templates in Microsoft Excel from simple stock charts, screeners, to market analysis & watchers and technical indicators.

If you have any feedback or suggestions, please post them in the comments below.

Leave a Reply

Your email address will not be published. Required fields are marked *