Stock chart with std dev plot

[novice198]

Hi,
I would like to plot a chart in excel as under:-
Stock price with Date ( I have this data and plotted the data)
Plot regression trend line ( have done this also)
Plot a line one std dev above and another std dev below the regression line ( dont know how to do this)
Plot a line 2 std dev above and another std dev below the regression line ( dont know how to do this)
Can someone pls help me with this.
Thanks in advance
Novice198

 

[novice198]

Hello,
Can someone help me with this please.

Cheers
novice198

 

[thisoldman]

Your Trend ± StdDev lines have to each be added to the chart as separate series. I'll assume you used a linear trend line in what you have graphed so far. Linear trend lines have formulas that are of the form y = mx + b, where m is the slope and b is the y-intercept. Your price is the y in the equation,the day is the x.

Did you check the option to 'Display equation on chart'? That would give you the equation you could use to create formulas for the other parallel series. Or you can use the Excel function LINEST to calculate the slope and the intercept. Note that LINEST is an array function, that must be entered using Ctrl+Shift+Enter, not simply enter. As I've used it in the example below, I selected the two cells, A2 and B2, and while they were both selected, typed the formula and then pressed Ctrl+Shift+Enter.

The six series to plot are in columns C through H.

Excel 2012
	A	B	C	D	E	F	G	H
1	Slope	Intercept
2	0.421429	18.94167
3
4	Date	Day	Price	LinTrend	LinT-2SD	LinT-1SD	LinT+1SD	LinT+2SD
5	31-May	1	19.13	19.36	17.19	18.27	20.45	21.54
6	1-Jun	2	19.50	19.78	17.61	18.70	20.87	21.96
7	2-Jun	3	20.00	20.21	18.03	19.12	21.29	22.38
8	3-Jun	4	22.00	20.63	18.45	19.54	21.72	22.80
9	6-Jun	5	21.25	21.05	18.87	19.96	22.14	23.23
10	7-Jun	6	20.63	21.47	19.29	20.38	22.56	23.65
Sheet1

Cell Formulas
Range	Formula
D5	=$A$2 * B5 + $B$2
E5	=D5 - 2 * STDEV.S($C$5:$C$10)
F5	=D5 - STDEV.S($C$5:$C$10)
G5	=D5 + STDEV.S($C$5:$C$10)
H5	=D5 + 2 * STDEV.S($C$5:$C$10)
A2:B2	{=LINEST($C$5:$C$10,$B$5:$B$10)}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[novice198]

Your Trend ± StdDev lines have to each be added to the chart as separate series. I'll assume you used a linear trend line in what you have graphed so far. Linear trend lines have formulas that are of the form y = mx + b, where m is the slope and b is the y-intercept. Your price is the y in the equation,the day is the x.

Thanks for the swift response. However, I have not been able to achieve what I wanted.
I am attaching the file with the raw data and the another file with the desired Chart. I would appreciate if you have a look at same and then guide me.

How can I attach a file here.


Cheers
Novice

 

[thisoldman]

You cannot attach files on this forum. See http://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html.

You can upload a file to a public folder on Dropbox or Box.com or a similar file sharing service and give a link to the file in your post. Many forum users will not download a file.

You also can't attach images directly. you have to use a public image posting service. You can us BBCode so that an image will appear in the post. Note that many companies filter these image links out and other users won't see anything. Here's the chart I made (I can't see this image when I'm at work):

Can you find a chart similar to what you want by Googling and then post a link to that image here?

 

[novice198]

You cannot attach files on this forum. See http://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html.

You can upload a file to a public folder on Dropbox or Box.com or a similar file sharing service and give a link to the file in your post. Many forum users will not download a file.

You also can't attach images directly. you have to use a public image posting service. You can us BBCode so that an image will appear in the post. Note that many companies filter these image links out and other users won't see anything. Here's the chart I made (I can't see this image when I'm at work):



Can you find a chart similar to what you want by Googling and then post a link to that image here?

Thanks for the swift response. Your chart looks like what should be the end result which I am looking for.

The above is what I have plotted.
The chart which I want is as per below

I hope you are able to see the charts

cheers
novice

 

[thisoldman]

OK, the graph you want is similar to what I posted previously. I downloaded the data from Yahoo!Finance, eliminated all columns except for the date and closing price column. Here's a tiny excerpt. There are two columns of 345 rows. I then sorted the data from oldest to newest.

	A	B
1	Date	Close
2	1987-12-28	823.2
3	1988-01-04	908.9
4	1988-02-01	888.8
5	1988-03-01	925.8

<tbody>
</tbody>

The first step is to select only B1:B345. Do not select the date column. Then insert a line chart. The x-axis labels should be a series of increasing numbers, not dates. Now insert a linear trendline and make sure the trendline equation is displayed. The equation should be very close to: y = 5.7971x + 1178. To be like the example chart format the trendline to be small red plus signs (dots?).

Use column C as a helper column. Fill the column from C2 downward with the numbers from 1 to 344. You can use either Home >> Fill >> Series from the menu bar or use a formula.

The lines parallel to the trendline appear to be set at ±1.0 and ±1.5 times the standard deviation. These are calculated in columns D through F as shown below. The formulas in row 2 are copied downward.

Excel 2012
	A	B	C	D	E	F	G
1	Date	Close	Helper	Tr-1.5*StDev	Tr-1*StDev	Tr+1*StDev	Tr+1.5*StDev
2	1987-12-28	823.2	1	104.4102	464.2058	1903.388	2263.184
3	1988-01-04	908.9	2	110.2073	470.0029	1909.185	2268.981
4	1988-02-01	888.8	3	116.0044	475.8	1914.983	2274.778
5	1988-03-01	925.8	4	121.8015	481.5971	1920.78	2280.575
monthly_sti_index

Cell Formulas
Range	Formula
D2	=5.7971 * C2 + 1178 - 1.5 * STDEV.S($B$2:$B$345)
E2	=5.7971 * C2 + 1178 - STDEV.S($B$2:$B$345)
F2	=5.7971 * C2 + 1178 + STDEV.S($B$2:$B$345)
G2	=5.7971 * C2 + 1178 + 1.5 * STDEV.S($B$2:$B$345)

Select D1:G345 and copy the cells. Then select the chart. With the chart selected, go to the Home tab on the ribbon. Under the Clipboard Paste icon is a small downward pointing triangle. Click the triangle, then select "Paste Special".

In the dialog box that pops up, select "New Series", "Columns", "Series Names in First Row" and then press "OK",

The standard deviation lines should appear in the chart.

Now we'll make the x-axis show dates instead of numbers. Select the chart and right-click to bring up the pop-up menu. Choose "Select data". In the dialog box that pops up, click on the right-hand "Edit" button. In the "Axis Labels" dialog box, enter "A2:A345". Press "OK" in that dialog box, then press "OK" in the "Select Data Source" dialog box and you should be returned to your chart, now showing dates on the x-axis.

The remainder is just formatting. The chart area is a light orange; the plot area is white; the y-axis is set to run from 200 to 4000 with 100 points as the interval; the x-axis has an 8-month interval; and so on. Too busy for my taste but it matches your example.

 

[novice198]

OK, the graph you want is similar to what I posted previously. I downloaded the data from Yahoo!Finance, eliminated all columns except for the date and closing price column. Here's a tiny excerpt. There are two columns of 345 rows. I then sorted the data from oldest to newest.

<tbody>
</tbody>

The first step is to select only B1:B345. The equation should be very close to: y = 5.7971x + 1178. To be like the example chart format the trendline to be small red plus signs (dots?).


The lines parallel to the trendline appear to be set at ±1.0 and ±1.5 times the standard deviation. These are calculated in columns D through F as shown below. The formulas in row 2 are copied downward.

Excel 2012

monthly_sti_index​

Worksheet Formulas Cell Formula D2 =5.7971 * C2 + 1178 - 1.5 * STDEV.S($B$2:$B$345) E2 =5.7971 * C2 + 1178 - STDEV.S($B$2:$B$345) F2 =5.7971 * C2 + 1178 + STDEV.S($B$2:$B$345) G2 =5.7971 * C2 + 1178 + 1.5 * STDEV.S($B$2:$B$345) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Too busy for my taste but it matches your example.

Thanks for the detailed explanantion. Though I have been able to prepare the chart that I wanted i could not get the equation as above. My equation had slope of 0.2774 and intercept as 1188.1. I used your previous example to calculate the LinTrend and then the STD points for the 4 lines. I am still interested to know how you managed to get the above numbers for the equation. Here is the final chart

[/IMG]

Thanks a ton for all the help. I really appreciate your efforts.
Cheers
novice

 

[thisoldman]

Thanks for the detailed explanantion. Though I have been able to prepare the chart that I wanted i could not get the equation as above. My equation had slope of 0.2774 and intercept as 1188.1. I used your previous example to calculate the LinTrend and then the STD points for the 4 lines. I am still interested to know how you managed to get the above numbers for the equation. Here is the final chart

The slope and intercept were from the trendline equation. I had the chart as first displayed, with just the line graph and the x-axis as numbers not dates. I added the trendline and then immediately checked the option to display the trendline equation on the chart. The equation I posted was a direct copy from the equation's textbox.

Your calculated slope is almost exactly one-twentieth of the slope I used. Here's a comparison of the trendlines: yours, yours with the slope multiplied by twenty, and mine.

What exactly was your LINEST equation? Was it entered as an array equation, two side-by-side cells selected when you pressed Ctrl+Shift+Enter? The same number of cells for the known_x's as the number of known_y's?

 

[novice198]

The slope and intercept were from the trendline equation. I had the chart as first displayed, with just the line graph and the x-axis as numbers not dates. I added the trendline and then immediately checked the option to display the trendline equation on the chart. The equation I posted was a direct copy from the equation's textbox.

Your calculated slope is almost exactly one-twentieth of the slope I used. Here's a comparison of the trendlines: yours, yours with the slope multiplied by twenty, and mine.

What exactly was your LINEST equation? Was it entered as an array equation, two side-by-side cells selected when you pressed Ctrl+Shift+Enter? The same number of cells for the known_x's as the number of known_y's?

I plotted the Close price first as under

[/IMG]
Then I added the linear Trendline and got the equation as under:-

[/IMG]

Then I followed the calculation from your first post
The formulas are as per below:-

[/IMG]

Finally I have got what I wanted but I am still clueless as o how you for the slope as 5.9.... which kindly explain.
Cheers
novice