Follow stock market trends – Moving Average

In my previous post, I described how to build a dynamic stock chart that lets you easily adjust the date range and change index/company. Price data is quickly and automatically fetched from yahoo finance.

This post shows you how to add tools to the charts so you can quickly identify the trend for a stock or index. The stock market often trends for many months up or down and a moving average smooths out price data.

The examples shown in this post are based on S&P 500 index and larger trends. Charts show price data on a monthly scale and the date range is from 1995 to the present time.

 

This Excel stock chart has a 10-month moving average. It can be calculated using the AVERAGE function on an excel worksheet.

1. Calculate moving average

Formula in cell K23:

This function calculates the average from 10 latest closing prices. Copy cell K23 and paste to cells below as far as needed.

Back to top

2. How to add a moving average to an Excel stock chart

1. Press with right mouse button on on chart
2. Press with mouse on "Select Data..."
3. Press with left mouse button on "Add" button
4. Select cell range $K$23:$K$272
   
5. Press with left mouse button on OK button
6. Go to tab "Layout" on the ribbon.
7. Select "Series 4"
   
8. Press with left mouse button on "Format Selection" button, see picture above.
9. Select "Secondary Axis"
   
10. Go to "Line Color"
11. Select "Solid Line"
12. Pick a color
    

The chart now looks like this:

The data on the secondary axis has to be reversed.

1. Select line on chart
2. Go to tab "Layout" on the ribbon
3. Press with left mouse button on "Axis" button and then "Secondary horizontal axis" and finally press with left mouse button on "Show right to left axis"
   
4. Select and delete the horizontal axis above the chart
5. Go to tab "Layout" on the ribbon
6. Press with left mouse button on "Axis" button and "Secondary Vertical Axis" and finally "None"
   

The chart looks like this:

A moving average indicates if a market is about to go up or down in the long term.

Back to top

3. Plot moving average turning points

Let's start with calculating when the moving average changes from moving down to up.

Buy formula in cell L23:

If the value in cell K23 is larger than cell K24 AND cell K24 is smaller than K25 THEN return the closing price.  If not return nothing.
Read more about IF function.

The next formula calculates when the moving average changes from going up to going down.

Sell formula in cell M23:

Copy cell range L23:M23 and paste to cells below as far as needed. It is now time to plot these moving average turning points.

1. Press with right mouse button on on chart
2. Press with left mouse button on "Select Data..."
3. Press with left mouse button on "Add" button
4. Select cell range $L$23:$L$273
5. Press with left mouse button on OK button
6. Press with left mouse button on "Add" button again
7. Select cell range $M$23:$M$273
8. Press with left mouse button on OK button
9. Press with left mouse button on OK button

Here is how to remove lines shown above and use markers instead.

1. Go to tab "Layout"
2. Select Series 5
3. Press with left mouse button on "Format Selection"
4. Select "Marker Options"
5. Select "Built-in"
6. Pick a type
7. Go to "Line Color"
8. Select "No Line"
9. Select "Series 6" on tab "Layout" on the ribbon
10. Select "Marker Options"
11. Select "Built-in"
12. Pick a type
13. Go to "Line Color"
14. Select "No Line"

Back to top

4. How to extract the moving average turning dates using Excel array formulas

Array formula (Buy) in cell P23:

Formula (Buy Date) in cell O23:

Array formula (Sell) in cell S23:

Formula (Sell Date) in cell R23:

Back to top

4.1 How to enter an array formula

1. Select cell P23
2. Copy array formula
3. Paste array formula in the formula bar
4. Press and hold CTRL + SHIFT simultaneously
5. Press Enter once
6. Release all keys

If you did the above instructions correctly, the formula begins and ends with a curly bracket, like this {=formula}. Don´t enter these characters yourself.

Make sure you enter array formulas in cell P23 and S23. Then copy cell P23 and paste to cells below as far as needed. Repeat with cell S23, R23 and O23.

Back to top

4.2 Explaining formula in cell P23

Step 1 - Check if each value in $K$23:$K$262 is larger than the next cell value below

The less than sign checks if the numerical value in a cell is less than the number in the next cell below.

$K$23:$K$262<$K$24:$K$263

The less than sign is a logical operator and the result is a boolean value, TRUE or FALSE.

Step 2 - Check if each value in $K$24:$K$263 is larger than the next cell value below

The larger than sign checks if the numerical value in a cell is larger than the number in the next cell below.

$K$24:$K$263>$K$25:$K$264

The larger than sign is also a logical operator and the result is a boolean value, TRUE or FALSE.

Step 3 - Multiply arrays

When we multiply arrays using the asterisk sign we apply AND-logic meaning TRUE is returned only if TRUE is present in the same position in both arrays.

TRUE * TRUE = TRUE

TRUE * FALSE = FALSE

FALSE * TRUE = FALSE

FALSE * FALSE = FALSE

($K$23:$K$262>$K$24:$K$263)*($K$24:$K$263<$K$25:$K$264)

The numerical equivalent is returned when two boolean values are multiplied.

TRUE = 1

FALSE = 0 (zero)

Step 4 - Create sequence from 1 to n

The ROW function returns a number representing the row based on a cell reference, for example, ROW(A1) returns 1.

This works also if we use a cell range as a cell reference, however, the formula is now an array formula. It calculates an array of values.

MATCH(ROW($G$23:$G$262), ROW($G$23:$G$262))

The MATCH function returns the relative position of a given value.

MATCH({23; 24; 25; ... ; 262}, {23; 24; 25; ... ; 262})

and returns {1; 2; 3; ... }.

Step 5 - Returns row number if logical expression is TRUE

The IF function returns one value if the logical test is TRUE and another value if the logical test is FALSE.

IF(logical_test, [value_if_true], [value_if_false])

IF(($K$23:$K$262>$K$24:$K$263)*($K$24:$K$263<$K$25:$K$264), MATCH(ROW($G$23:$G$262), ROW($G$23:$G$262)), "")

Step 6 - Extract the k-th smallest row number

The SMALL function returns the k-th smallest value from a group of numbers.

SMALL(array, k)

The ROWS function returns the number of rows based on a cell reference.

ROWS(array)

SMALL(IF(($K$23:$K$262>$K$24:$K$263)*($K$24:$K$263<$K$25:$K$264), MATCH(ROW($G$23:$G$262), ROW($G$23:$G$262)), ""), ROWS($A$1:A1)))

Step 7 - Get corresponding value from column G

The INDEX function returns a value from a cell range, you specify which value based on a row and column number.

INDEX(array, [row_num], [column_num])

INDEX($G$23:$G$262, SMALL(IF(($K$23:$K$262<$K$24:$K$263)*($K$24:$K$263>$K$25:$K$264), MATCH(ROW($G$23:$G$262), ROW($G$23:$G$262)), ""), ROWS($A$1:A1)))

Step 8 - Handle errors

The IFERROR function lets you catch most errors in Excel formulas.

IFERROR(value, value_if_error)

IFERROR(INDEX($G$23:$G$262, SMALL(IF(($K$23:$K$262>$K$24:$K$263)*($K$24:$K$263<$K$25:$K$264), MATCH(ROW($G$23:$G$262), ROW($G$23:$G$262)), ""), ROWS($A$1:A1))), "")

Back to top

This template has dynamic named ranges for all chart series. This lets you change the date range and all chart data is adjusted automatically. I have not described how they work in this post, if you are curious

The template also contains a small custom function to fetch stock data from yahoo finance.

Tip! - Add data labels

Back to top

6. Plot buy and sell points in an Excel Chart based on two moving averages

This article demonstrates how to display buy and sell signals on an Excel chart based on two moving averages, the workbook lets you change how these moving averages are calculated. There is a get link below.

Above is an animated gif demonstrating a stock chart of S&P 500 with monthly prices. Two moving averages #1, #2, buy and sell points are plotted in this chart.

When the moving averages intersects a buy or sell point is created. If you use the value 7 in cell C1, moving average #1 uses the average of 7 months.

As you can see using a 15 month average (#1) returns better and fewer buy and sell points.

Extracting buy and sell points

Not only is the chart refreshed as you type new values in cell C1 and C2, the data below the chart is also instantly refreshed.

Array formula in cell B25:

Array formula in cell C25:

Array formula in cell D25:

How to create an array formula

1. Copy array formula above
2. Select cell B25
3. Paste formula to cell
4. Make sure you see the prompt in the cell, double press with left mouse button on the cell if you don't.
5. Press and hold Ctrl+ Shift simultaneously
6. Press Enter once
7. Release all keys

How to copy array formula

1. Select cell B25
2. Copy cell (not formula in formula bar)
3. Select cell range B26:B50
4. Paste

Explaining formula in cell B25

Step 1 - Remove errors

The IFERROR function removes errors and returns an array containing values or nothing, errors are filtered out.

IFERROR(Calculation!$M$2:$M$247, "")

Step 2 - Check if cell is empty

The less than and greater than sign combined means "not equal to", in this case the logical operators make sure that the value is not equal to nothing. It returns boolean values TRUE or FALSE.

IFERROR(Calculation!$M$2:$M$247, "")<>""

Step 3 - Check column N as well

Actions described in step 1 and 2 are also applied to column N.

(IFERROR(Calculation!$N$2:$N$247, "")<>"")

Step 4 - If cells are not empty return the corresponding relative row number

The + sign between the logical expressions performs an OR calculation meaning if the cell in column M is not equal to nothing OR if the cell in column N is not equal to nothing then return TRUE, both logical expressions must return FALSE in order to return a FALSE.

(IFERROR(Calculation!$M$2:$M$247, "")<>"")+(IFERROR(Calculation!$N$2:$N$247, "")<>"")

The IF function then returns the second argument if the logical expression returns TRUE.

IF((IFERROR(Calculation!$M$2:$M$247, "")<>"")+(IFERROR(Calculation!$N$2:$N$247, "")<>""), MATCH(ROW(Calculation!$M$2:$M$247), ROW(Calculation!$M$2:$M$247)), "")

The second argument is the corresponding row number and the third argument is nothing.

MATCH(ROW(Calculation!$M$2:$M$247), ROW(Calculation!$M$2:$M$247)

Step 5 - Extract the k-th largest row number

The LARGE function extracts the k-th largest value in the array. LARGE( array, k)

LARGE(IF((IFERROR(Calculation!$M$2:$M$247, "")<>"")+(IFERROR(Calculation!$N$2:$N$247, "")<>""), MATCH(ROW(Calculation!$M$2:$M$247), ROW(Calculation!$M$2:$M$247)), ""), ROW(A1))

Argument k is a relative cell reference that changes when the cell is copied, this makes it possible to extract different values.

Step 6 - Return value from column D based on row number

The INDEX function returns a value from a cell range based on a row and column number.

INDEX(Calculation!$D$2:$D$247, LARGE(IF((IFERROR(Calculation!$M$2:$M$247, "")<>"")+(IFERROR(Calculation!$N$2:$N$247, "")<>""), MATCH(ROW(Calculation!$M$2:$M$247), ROW(Calculation!$M$2:$M$247)), ""), ROW(A1)))

Calculating buy and sell points

Sheet: Calculation

Array formula in cell K2:

Array formula in cell L2:

Formula in cell M2:

Formula in cell N2:

Copy cells and paste down as far as needed.

The calculation sheet imports historical prices using this user defined function:
Excel udf: Import historical stock prices from yahoo – added features

Further reading

If you want to know how to plot buy and sell points in an excel stock chart, read this post:
Add buy and sell points to a stock chart

7. Follow stock market trends - trailing stop

Now I want to demonstrate an alternative way to identify a major trend in the stock market. The previous post showed you how to identify the trend using moving averages.

The picture above shows you a chart of S&P 500 with two lines, one is green and one is red. If the stock price moves above the green line a buy signal is returned, if the price moves below the red line a sell signal is generated.

7.1. How trailing stops work

In a bull market, the price moves above the green line again and again, and in a bear market the price moves repeatedly below the red line. The trailing stop method makes it easier to identify when a bull market becomes a bear market and vice versa. Let me explain how these colored lines are constructed.

The red line shows the smallest value out of the last eight months' price bars, see blue arrows on the picture above. In October 2013 the smallest value is found from the first price quote (February 2013). In November 2013 the smallest value is found in the price quote March 2013.

As time moves on the red line advances higher and higher until August 2015. The price moves below the red line and the first sell signal is generated.

This chart shows with blue markers each time a price bar moves above the green line and with red markers each time a price bar moves below the red line. As you can see this method is not perfect, sometimes it generates false signals, see markers in year 1998, 2010 and 2011. Who knows if sell signal in August 2015 is correct? Only time can tell.

Back to top

7.2. How to build this stock chart

The following formula allows you to change the number of calculated months, you can do that by changing the number in cell L20.

The red line formula in cell K23:

7.2.1 Explaining formula in cell K23

Step 1 - Calculate cell based on value in cell L20

The OFFSET function returns a reference to a range that is a given number of rows and columns from a given reference.

OFFSET(reference, rows, columns, [height], [width])

OFFSET(F24,$L$20-1,0)

becomes

OFFSET(F24,8-1,0)

becomes

OFFSET(F24, 7, 0)

and returns a reference to cell F31.

Step 2 - Create a reference to a cell range

The colon allows you to create a cell reference to a cell range.

F24:OFFSET(F24,$L$20-1,0)

returns F24:F31.

Step 3 -

MIN(F24:OFFSET(F24,$L$20-1,0))

The green line formula in cell M23:

Copy cell K23 and paste to cells below as far as needed. Repeat with cell M23.

Back to top

7.3. Add lines to the stock chart

1. Press with right mouse button on on the chart.
2. Press with mouse on "Select Data...".
3. Press with mouse on the "Add" button.
4. Select values in column K ($K$23:$K$271).
5. Press with left mouse button on OK.
6. Press with left mouse button on OK.
7. Select chart.
8. Go to tab "Layout" on the ribbon.
9. Select Series 4 for on the top left corner.
10. Press with left mouse button on the "Format Selection" button.
11. Make sure values are plotted on the "Secondary axis" (enabled).
12. Go to "Line Color".
13. Select "Solid Line" and pick a color.

Repeat above steps (add a new series with values from column M ($M$23:$M$271).

As you can see the two lines are in reverse order compared to the stock price bars.

1. Select one of the lines, it does not matter which one.
2. Go to tab "Layout" on the ribbon.
3. Press with left mouse button on the "Axes" button and then "Secondary horizontal axis" and finally "Show axis left to right".
4. Press with right mouse button on on the horizontal secondary axis (above the plot area).
5. Press with left mouse button on "Format Axis...".
6. Enable "Categories in reverse order".
7. Delete the horizontal secondary axis (above the plot area).
8. Select one of the lines.
9. Press with left mouse button on the "Axes" button and then "Secondary vertical axis" and finally press with left mouse button on "Show none".

Back to top

7.4. Add breakout signs to the chart

Sell formula in L23:

Buy formula in N23:

Copy cell L23 and paste to cells below as far as needed. Repeat with cell N23.

Now add two more markers series, see instructions above. Here is a small recap:

1. Add two series (values in columns L and N).
2. Plot series on the secondary axis.
3. Use markers instead of solid lines.

This post shows you in greater detail how to add markers.

Back to top

This template has dynamic named ranges for all chart series. This lets you change the date range and all chart data is adjusted automatically. I have not described how they work in this post.

The template also contains a small custom function to automatically fetch stock data from yahoo finance.

Back to top

8. Add buy and sell markers to a stock chart

The image above shows an Excel chart of the S&P 500 with buy and sell signals based on a 50 day average.

It is easy to create a stock chart in Excel. In this article, I am going to describe how to insert buy and sell points to an Excel chart.

To simplify things I will use a 50 day moving average as an indicator. If the 50day average changes from negative to positive a buy signal is generated and a sell signal when the average moves from positive to a negative trend.

I have copied stock data from the Yahoo Finance website, you can easily pick a stock or index and get historical data.

Calculating moving average

The AVERAGE function calculates the moving average, remember to use the last 50 days as an argument. I am using a relative cell reference that changes accordingly when the cell is copied to cells below.

In cell K2:

Copy cell K2 and paste down as far as needed.

Find buy and sell dates

The following formulas check if the 50-day average is higher or lower than the previous day and the day before that, this will return the close value only if there is a change in trend.

In other words multiple buy or sell signals won't be displayed, only the first one. The first IF function has two logical expressions (K2-K3>0) and (K3-K4<0), both these conditions must be true to return the value in K2.

In cell L2:

In cell M2:

Copy cell L2 and M2 and paste down as far as needed.

Extract year and month from date

Let's extract year and month from dates to avoid a mess on the x axis in our stock chart. The YEAR function returns the year based on an Excel date.

Chandoo has a great post: Show Months & Years in Charts without Cluttering

In cell B2:

In cell B3:

The TEXT function formats an Excel date in this particular example, and displays the month formatted with only first three visible characters.

The IF function makes sure that the formatted month name is shown only when the month changes, this to avoid repeated months.

Cell C2:

Copy cell B3 and C2 and paste down as far as needed.

Create stock chart

It is important that the data is arranged in this order: High, Low and Close.

1. Select cell range F1:H300
2. Go to tab "Insert"
3. Press with left mouse button on "Other charts"
4. Press with left mouse button on "High Low Close stock chart"

Adjust vertical axis min and max values

If your chart is zoomed out too far you can simply adjust the visible axis range by following these steps.

1. Select vertical axis
2. Press with right mouse button on on vertical axis
3. Press with left mouse button on "Format axis..."
4. Press with left mouse button on "Axis Options"
5. Press with left mouse button on Minimum fixed and type 1050.
6. Press with left mouse button on Maximum fixed and type 1450.

Change horizontal axis labels

These steps change the x-axis numbers to years and months based on the values in column B and C.

1. Press with right mouse button on on chart
2. Press with left mouse button on "Select data"
   
3. Press with left mouse button on "Edit" button
4. Select cell range B2:C300
5. Press with left mouse button on OK

Reverse horizontal (category) axis

If the dates are backward then these steps tell you how to change the order.

1. Select horizontal axis
2. Press with right mouse button on on horizontal axis
3. Press with left mouse button on "Format axis.."
4. Press with left mouse button on "Axis Options"
5. Press with left mouse button on "Categories in reverse order"
6. Select "Major tick mark type:" None
7. Press with left mouse button on OK

Add moving average

These steps explains how to display the moving average on an Excel chart.

1. Select cell range K1:K300
2. Copy
3. Select chart
4. Got to tab "Home"
5. Press with left mouse button on "Paste"
6. Press with left mouse button on "Paste Special.."
   
7. Press with left mouse button on "Ok"
8. Press with mouse on chart
9. Go to tab "Format"
10. Select "50 day m-avg"
    
11. Press with left mouse button on "Format selection"
12. Press with left mouse button on "Series Options"
13. Press with left mouse button on "Secondary axis"
14. Press with left mouse button on "Line color" and select: Solid Line
15. Press with left mouse button on Close button

 How to display buy and sell signals on an Excel chart?

1. Select cell range L2:L300
2. Copy
3. Select chart
4. Go to tab "Home"
5. Press with left mouse button on "Paste"
6. Press with left mouse button on "Paste special"
7. Press with left mouse button on Ok

Select cell range M2:M300 and repeat above steps.

1. Press with left mouse button on on chart to select it
2. Go to tab "Format"
3. Select "Buy"
4. Press with left mouse button on "Format Selection"
5. Press with left mouse button on "Marker Options"
6. Press with left mouse button on "Bulit-in"
7. Select a marker type and size
8. Select "Marker Fill"
9. Press with left mouse button on "Solid Fill"
10. Select "Line Color": No line
11. Press with left mouse button on Close

Repeat above steps with "Sell"

Change secondary vertical axis

1. Select and press with right mouse button on on the vertical axis to the left
2. Change minimum and maximum values. They must match the primary axis.
3. Select and press with right mouse button on on the vertical axis to the right
4. Press with left mouse button on "Delete"

Add Data Labels

Buy series

1. Press with left mouse button on on chart to select it
2. Go to tab "Layout"
3. Select "Buy" series
4. Press with left mouse button on "Data Labels"
5. Press with left mouse button on "More Data Label Options"
6. Press with left mouse button on "Label options"
7. Deselect "Values"
8. Select "Series Name"
9. Label position: Below
10. Press with left mouse button on Close

Sell series

1. Press with left mouse button on on chart to select it
2. Go to tab "Layout"
3. Select "Sell" series
4. Press with left mouse button on "Data Labels"
5. Press with left mouse button on "More Data Label Options"
6. Press with left mouse button on "Label options"
7. Deselect "Values"
8. Select "Series Name"
9. Label position: Above
10. Press with left mouse button on Close

9. How to calculate and plot pivots on an Excel chart

 

If you study a stock chart you will discover that sometimes significant trend reversals happen when a stock chart bar is higher than the previous bar and the following bar, especially in a monthly bar chart. Excel is a fantastic tool as you know, it can help us identify these major turning points or pivots.

The picture above shows the simple logic, a bar that is lower than the previous and next bar is interesting to analyze if the stock market is going up (bull market).

Likewise, if a bar is higher than the previous and the following one, keep an eye on these pivots if the market is going down.

Major turning points often happen when the price moves past a pivot. See the picture below.

 

The chart above shows the S&P500 during the house market crash 2007-2009, Each pivot excel identifies has a dotted line and if it is breached a buy or sell signal is generated. Subsequent signals of the same kind are not shown in the chart.

A blue dotted line shows "high" pivots and a red dotted line shows "low" pivots if a new pivot is found the old one's dotted line ends.

Back to top

9.2. Formulas

Here is the sheet that made it possible, you can press with left mouse button on to enlarge.

The user defined array formula in cell C23:I355:

Enter this udf array formula in cell range C23:I355. It fetches data from yahoo and a stock ticker you specify. You can read more about this udf here: Excel udf: Import historical stock prices from yahoo

 

Formula in cell A23:

This formula returns the YEAR from a date in cell C23.

Formula in cell B23:

This formula returns the first letter from a month.

Formula in cell K24:

This formula checks if the high is higher than the previous and next values. If TRUE change the value to the new high, if FALSE repeat the previous high value.

Formula in cell L24:

This formula checks if the low is lower than the previous and next values. If TRUE change the value to the new low, if FALSE repeat the previous low value.

Formula in cell M24:

This keeps track of whether a buy or sell was the latest signal.

Formula in cell N24:

Checks if the highest price point is above buy pivot. IF TRUE display buy pivot price.

Formula in cell O24:

Checks if the lowest price point is below sell pivot. IF TRUE display sell pivot price.

Formula in cell P24:

Returns the buy pivot price if the last signal was a sell signal. This avoids subsequent buy signals of the same kind.

Formula in cell Q24:

Returns the sell pivot price if the last signal was a buy signal. This avoids subsequent sell signals of the same kind.

Copy cells and paste below as far as needed.

Back to top

9.3. Named ranges

The purpose of these named ranges is that if you change the date range the chart will automatically use the new range. Basically, it looks for the last row not equal to "#N/A" in column C and returns a cell range we can use in the chart.

MClose

MHigh200

MLow200

BPivotlines

BuyPivots

Dates200

Sellm200

SellPivots

SPivotlines

Back to top

9.4. Setting up the chart

Insert a new stock chart

1. Go to tab "Insert" on the ribbon.
2. Select cell range E23:G46 on your worksheet.
3. Press with left mouse button on the "Other Charts" button.
4. Press with left mouse button on the "High-Low-Close" chart button.
5. Press with right mouse button on on the chart you just created.
6. Press with left mouse button on "Select Data..".
7. Select "Series1" and press with left mouse button on the "Edit" button.
8. Change Series values to: =Pivots!MHigh200
9. Press with left mouse button on OK.
10. Select "Series2" and press with left mouse button on the "Edit" button.
11. Change Series values to: =Pivots!MLow200
12. Select "Series3" and press with left mouse button on the "Edit" button.
13. Change Series values to: =Pivots!MClose
14. Press with left mouse button on OK.
15. Press with left mouse button on "Edit" button below Horizontal (Category) Axis Labels.
16. Change Axis Label range to: =Pivots!Dates200.
17. Press with left mouse button on OK.
18. Press with left mouse button on OK.

Back to top

9.5. Reverse horizontal axis

1. Press with right mouse button on on the horizontal axis
2. Press with left mouse button on "Format Axis..."
3. Enable "Categories in reverse order"
4. Press with left mouse button on OK

Add "High" pivots to chart

1. Press with right mouse button on on chart
2. Press with left mouse button on "Select Data..."
3. Press with left mouse button on "Add" button below Legend Entries (Series)
4. Type Pivots!BPivotlines in Series values
5. Press with left mouse button on OK button
6. Press with left mouse button on Ok
7. Go to tab "Layout" on the ribbon. If you can't find it make sure you have the chart selected
8. Select chart element "Series 4" in the drop down list
   
9. Press with left mouse button on "Format Selection" button
10. Press with left mouse button on "Secondary Axis"
    
11. Go to "Marker Options" on the menu to the left
    
12. Press with left mouse button on Built-in and select a type and size
13. Press with left mouse button on Close button

Reverse Series 4

1. Select Series 4 on the chart
2. Go to tab Layout on the ribbon
3. Press with left mouse button on "Axis button, press with left mouse button on "Secondary Horizontal Axis" and then press with left mouse button on "Show Left to Right"
4. Press with right mouse button on on the secondary horizontal axis
   
5. Press with left mouse button on "Format Axis"
6. Press with left mouse button on "Categories in reverse order"
7. Press with left mouse button on Close
8. Select secondary horizontal axis again and delete
9. Select the chart legend and delete
10. Select Series 4
11. Go to tab Layout on the ribbon
12. Press with left mouse button on button "Axis"
13. Go to secondary vertical axis
14. Press with left mouse button on None

Back to top

9.6. Add remaining series

1. Repeat above steps described in "Add High pivots to chart" and "Reverse Series 4" with Low Pivots, Buy and Sell signals
2. Here are the named ranges:
   Low Pivots - SPivotlines
   Buy - BuyPivots
   Sell - SellPivots

Back to top

9.7. Change the marker type

1. Go to tab "Layout" and Select Buy (Series 6)
2. Press with left mouse button on "Fomat Selection"
3. Go to "Marker Options" and choose a Built-in one
4.  Now select Sell (Series 7)
5. Press with left mouse button on "Format Selection" button
6. Go to "Marker Options" and choose a Built-in one

Back to top

9.8. Add Data Labels

1. Go to tab "Layout" and Select Buy (Series 6)
2. Press with left mouse button on "Data Labels" button
3. Press with left mouse button on "More Data Label Options.."
   
4. Make sure "Series Name" and "Above" is enabled, see picture above.
5. Press with left mouse button on Close
6. Repeat above steps with Sell (Series 7)

Back to top

9.9. Change minimum and maximum value on the vertical axis

1. Press with right mouse button on on vertical axis
2. Press with left mouse button on "Format Axis"
3. Change minimum value to 600
4. Change maximum value to 1600

Back to top

9.10. Change bar color

1. Go to tab "Layout" on the ribbon
2. Select "Series 3"
3. Press with left mouse button on "Format Selection"
4. Go to "Marker Line Color"
   
5. Press with left mouse button on "Solid Line" and pick a color, I chose black.
6. Press with left mouse button on Close

Back to top

9.11. Resize chart

Back to top

10. Dynamic stock chart - Excel 365

The image above demonstrates a dynamic stock chart, enter the stock quote in cell C2. The start and end date in cells C3 and C4, specify daily, weekly or monthly in cell C5.

The STOCKHISTORY function wants the corresponding number instead of daily, weekly, and monthly in order to work properly. We need a formula that converts the text to a number.

0 - daily
1 - weekly
2 - monthly

Formula in cell D5:

Explaining the formula in cell D5

Step 1 - Populate arguments

The MATCH function returns the relative position of an item in an array that matches a specified value in a specific order.

Function syntax: MATCH(lookup_value, lookup_array, [match_type])

becomes

MATCH(C5,{"Daily","weekly","monthly"},0)

Step 2 - Evaluate MATCH function

MATCH(C5,{"Daily","weekly","monthly"},0)

becomes

MATCH("weekly",{"Daily","weekly","monthly"},0)

and returns 2. "weekly" is the second value in the array.

Step 3 - Subtract with one

MATCH(C5,{"Daily","weekly","monthly"},0)-1

becomes

2-1

and returns 1.

0 - daily
1 - weekly
2 - monthly

Excel 365 dynamic array formula in cell B2:

Explaining the formula in cell B2

Step 1 - Populate arguments

The STOCKHISTORY function downloads stock prices based on a stock quote

Function syntax: STOCKHISTORY(stock, start_date, [end_date], [interval], [headers], [property0], [property1], [property2], [property3], [property4], [property5])

becomes

STOCKHISTORY(Sheet1!C2,Sheet1!C3,Sheet1!C4,Sheet1!D5,1,0,3,4,1)

Step 2 - Evaluate STOCKHISTORY function

STOCKHISTORY(Sheet1!C2,Sheet1!C3,Sheet1!C4,Sheet1!D5,1,0,3,4,1)

becomes

STOCKHISTORY("GOOGL",44197,44967,1,1,0,3,4,1)

and returns

{"Date","High","Low","Close";44193,89.4235,86.4,87.632;44200, ... ,95.01}

11. Dynamic stock chart - earlier Excel versions

Here is how to build this chart.

11.1. Create a date range

I entered "Start:" and "End:" in cell range B16:B17. The date values in cells C16 and C17 will determine when the date range begins and ends.

Back to top

11.2. Copy data

I found the stock price data on the yahoo finance website, here is a link to S&P 500 historical prices.

1. Press with left mouse button on the "Get to spreadsheet" link to the get file, you can find the link below historical data.
2. Open the geted file in Excel.
3. Copy data from the geted file.
4. Paste to cell A20 on your worksheet.

The data starts from the 1950s but I can't show all data, for obvious reasons.

Back to top

11.3. Build dynamic named ranges

We want the chart to change depending on the start and end date in cell range B16:B17. "Named ranges" is what we are looking for, it can return a cell range of variable size.

1. Go to "Formulas" on the ribbon.
2. Press with left mouse button on "Named Ranges".
3. Press with left mouse button on the "New" button.
4. Name it "Date".
5. Refers to:
   =INDEX(Sheet1!$A$21:$A$810, MATCH(Sheet1!$C$16,Sheet1!$A$21:$A$810,-1)):INDEX(Sheet1!$A$21:$A$810, MATCH(Sheet1!$C$17, Sheet1!$A$21:$A$810, -1))

   

6. Press with left mouse button on "OK".
7. Press with left mouse button on "New" button.
8. Name it "High".
9. Refers to:
   =INDEX(Sheet1!$C$21:$C$810, MATCH(Sheet1!$C$16,Sheet1!$A$21:$A$810, -1)):INDEX(Sheet1!$C$21:$C$810, MATCH(Sheet1!$C$17, Sheet1!$A$21:$A$810, -1))
10. Press with left mouse button on OK.
11. Press with left mouse button on "New".
12. Name it "Low".
13. Refers to:
    =INDEX(Sheet1!$D$21:$D$810, MATCH(Sheet1!$C$16, Sheet1!$A$21:$A$810, -1)):INDEX(Sheet1!$D$21:$D$810, MATCH(Sheet1!$C$17, Sheet1!$A$21:$A$810, -1))
14. Press with left mouse button on OK.
15. Press with left mouse button on "New".
16. Name it "Close".
17. Refers to:
    =INDEX(Sheet1!$E$21:$E$810, MATCH(Sheet1!$C$16, Sheet1!$A$21:$A$810, -1)):INDEX(Sheet1!$E$21:$E$810, MATCH(Sheet1!$C$17, Sheet1!$A$21:$A$810, -1))
18. Press with left mouse button on OK.

You have now created 4 different named ranges which are to be used in the stock chart. You can find a formula explanation later in this post. But first, let's insert a stock chart above the date range.

Back to top

11.4. Insert a stock chart

1. Select cell range C21:E29, column C:E contain high, low and close values.
2. Go to tab "Insert" on the ribbon
3. Press with left mouse button on "Stock chart" button

The values are in an incorrect order, they need to be reversed.

1. Press with right mouse button on on x axis
2. Select "Format Axis..."
3. Enable option "Categories in reverse order"

Back to top

11.5. Change series values

It is now time to use the dynamic named ranges we created earlier. They help us quickly change the cell range we want to be shown in the chart.

1. Press with right mouse button on on the chart.
2. Press with left mouse button on "Select Data...".
3. Select Series1.
4. Press with left mouse button on the "Edit" button.
5. Series values:
   =Sheet1!High

   (Don't forget to type the sheet name before the named range.)
   

6. Press with left mouse button on OK.
7. Select Series2.
8. Press with left mouse button on the "Edit" button.
9. Series values:
   =Sheet1!Low
10. Press with left mouse button on OK.
11. Select Series3.
12. Press with left mouse button on the "Edit" button.
13. Series values:
    =Sheet1!Close
14. Press with left mouse button on OK.
15. Press with left mouse button on the "Edit" button below Horizontal (Category) Axis Labels.
16. Axis label range:
    =Sheet1!Dates

Back to top

11.6. Explaining the named range (Date) formula

Step 1 - Find the position of value cell C16 in cell range A21:A810

The MATCH function returns a number representing the relative position of a given value in a cell range or array.

MATCH(lookup_value, lookup_array, [match_type])

MATCH(Sheet1!$C$16,Sheet1!$A$21:$A$810,-1)

becomes

MATCH(42248,Sheet1!$A$21:$A$810,-1)

and returns 2.

Step 2 - Calculate the first cell ref in the cell range

The INDEX function returns a value or a cell reference based on a row and column number.

INDEX(array, [row_num], [column_num])

INDEX(Sheet1!$A$21:$A$810, MATCH(Sheet1!$C$16,Sheet1!$A$21:$A$810,-1))

becomes

INDEX(Sheet1!$A$21:$A$810, 2)

and returns cell ref A22.

Step 3 - Find the position of value cell C17 in cell range A21:A810

The MATCH function returns a number representing the relative position of a given value in a cell range or array.

MATCH(lookup_value, lookup_array, [match_type])

MATCH(Sheet1!$C$17,Sheet1!$A$21:$A$810,-1)

becomes

MATCH(38353,Sheet1!$A$21:$A$810,-1)

and returns 130.

Step 4 - Calculate the second cell ref in the cell range

The INDEX function returns a value or a cell reference based on a row and column number.

INDEX(array, [row_num], [column_num])

INDEX(Sheet1!$A$21:$A$810, MATCH(Sheet1!$C$17, Sheet1!$A$21:$A$810, -1))

becomes

INDEX(Sheet1!$A$21:$A$810, 130)

and returns cell ref A151.

Step 5 - Combine cell refs to a cell range

You can concatenate two cell references using the colon character to create a cell reference to a cell range.

INDEX(Sheet1!$A$21:$A$810, MATCH(Sheet1!$C$16,Sheet1!$A$21:$A$810,-1)):INDEX(Sheet1!$A$21:$A$810, MATCH(Sheet1!$C$17, Sheet1!$A$21:$A$810, -1))

returns A22:A151.

Back to top

Back to top

11.7. How to make the chart even more dynamic

This user defined function allows you to fetch past data for any quote on yahoo finance. You don't have to go to the yahoo finance website and copy/paste the data, this udf does it for you automatically. Just type the stock quote in cell D17 and press enter.

If you are looking for a particular company, index or commodity go to yahoo finance and use their "Quote lookup" to find the quote you are looking for.

Yes, you still need to manually adjust the y axis values on the chart every time you change the date range or stock quote unless you check out this post.

Back to top

12. Build a stock chart with two series

This article demonstrates how to create a stock chart with two series.

The picture above shows a weekly stock bar chart of Microsoft and Caterpillar. One bar shows you the highest price and the lowest price during that specific week, it also shows the closing price which is the price of the last deal done that week.

Instructions

1. Rearrange columns like this: high, low and close
   
2. Select columns High, Low and close
3. Go to tab "Insert" on the ribbon
4. Press with left mouse button on "Other Charts" and then "High-Low-Close"
   
5. A chart is inserted on your active sheet
   
6. Press with right mouse button on on chart and press with left mouse button on "Select Data..."
7. Press with left mouse button on Edit button below Horizontal (Category) Axis Labels
8. Select your date range
   
9. Press with left mouse button on OK button twice
   
10. Press with right mouse button on on chart again
11. Press with mouse on "Select Data"
12. Press with mouse on "Add" button below Legend Entries (Series)
13. Select High column for the other stock you want plotted
    
    
14. Press with left mouse button on OK twice
15. Select the chart
16. Go to tab "Layout" on the ribbon
17. Select series 4
    
18. Press with left mouse button on "Format Selection" below Series 4
19. Change to "Secondary Axis"
    
20. Press with left mouse button on Close
21. Press with right mouse button on on chart again and press with left mouse button on "Select Data..."
22. Add Low and Close series
23. Go to tab "Layout" on the ribbon
24. Select "Series 4"
25. Press with mouse on "Lines" button and then "High-Low" Lines
    
    
26. Select "Series 6" (Close)
27. Press with left mouse button on "Format Selection"
28. Go to "Marker Options", choose built-in marker type and the sixth symbol from the top.
    
29. Select size 3
30. Go to "Marker Line Color", select "Solid Line" and finally pick a color. I chose dark blue.
    
    
31. Select "High Low Lines 2"
    
32. Select "Solid Line" and select a color
    
33. Press with left mouse button on Close
    

You now know how to change the color of high low lines and the closing line. Change the line color of all series (High, Low and Close) on the primary axis.

I chose black. Now delete the secondary axis and remove entries on the legend except "Close" and "Series 6". Change the name of series "Close" to Microsoft and "Series 6" to Caterpillar.

Get excel *.xlsx file

build-a-stock-chart-with-two-series.xlsx

Stock market trend category

More than 1300 Excel formulas

Excel categories

Comments (5)