It is easy to create a stock chart in excel. In this post I am going to describe how to insert buy and sell points.

A 50 day moving average will work as an indicator. If the average changes from negative to positive a buy signal is generated and a sell signal when the average moves from positive to negative trend.

S&P 500

How to quickly import stock prices to an excel sheet? I am using the user defined function from this post:Excel udf: Import historical stock prices from yahoo – added features
It let´s you choose start, end date and interval (daily, weekly, monthly prices).

Select cell range D1:J300 and enter YahooStockQuotes(2009, 9, 1, 2012, 5, 31, "d", "^GSPC") in formula bar. Press and hold Ctrl + Shift. Press Enter.

Calculating moving average

In cell K2:

Copy cell K2 and paste down as far as needed.

Find buy and sell dates

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 in our stock chart. Chandoo has a great post: Show Months & Years in Charts without Cluttering

In cell B2:

In cell B3:

Cell C2:

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

Create stock chart

1. Select cell range F1:H300
2. Go to tab "Insert"
3. Click "Other charts"
4. Click "High Low Close stock chart"

Adjust vertical axis min and max values

1. Select vertical axis
2. Right click on vertical axis
3. Click "Format axis..."
4. Click "Axis Options"
5. Click Minimum fixed and type 1050.
6. Click Maximum fixed and type 1450.

Change horizontal axis labels

1. Right click on chart
2. Click "Select data"
   
3. Click "Edit" button
4. Select cell range B2:C300
5. Click OK

Reverse horizontal (category) axis

1. Select horizontal axis
2. Right click on horizontal axis
3. Click "Format axis.."
4. Click "Axis Options"
5. Click "Categories in reverse order"
6. Select "Major tick mark type:" None
7. Click OK

Add moving average

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

 Add buy and sell points

1. Select cell range L2:L300
2. Copy
3. Select chart
4. Go to tab "Home"
5. Click "Paste"
6. Click "Paste special"
7. Click Ok

Select cell range M2:M300 and repeat above steps.

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

Repeat above steps with "Sell"

Change secondary vertical axis

1. Select and right click on the vertical axis to the left
2. Change minimum and maximum values. They must match the primary axis.
3. Select and right click on the vertical axis to the right
4. Click "Delete"

Add Data Labels

Buy series

1. Left click on chart
2. Go to tab "Layout"
3. Select "Buy" series
4. Click "Data Labels"
5. Click "More Data Label Options"
6. Click "Label options"
7. Deselect "Values"
8. Select "Series Name"
9. Label position: Below
10. Click Close

Sell series

1. Left click on chart
2. Go to tab "Layout"
3. Select "Sell" series
4. Click "Data Labels"
5. Click "More Data Label Options"
6. Click "Label options"
7. Deselect "Values"
8. Select "Series Name"
9. Label position: Above
10. Click Close

Download excel *.xlsm file

excel stock chart - moving average.xlsm

Related posts:

Plot buy and sell points in an excel chart using two moving averages

Learn how to create a stock chart in excel

Dynamic stock chart in excel – Add date ranges

Adjust stock chart axis automatically

Create a dynamic stock chart using a web query and a drop down list in excel