Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Bert
 
Posts: n/a
Default Combine candle and line in a single chart

Hello,

I've this columns:
-Date
-Open
-High
-Low
-Close
-Avg14

Now I'm lookin for creatin a chart that:
* uses the Date-field on the X-axis (no problem)
* Creates a candle-graph based on the fields Open, High, Low, Close (also no
problem)
* AND: shows as line-graph based on the field Avg14

The last one is the problem. Does anyone have an idea?

tia,
Bert





  #2   Report Post  
Debra Dalgleish
 
Posts: n/a
Default

Jon Peltier has instructions on his web site:

http://www.peltiertech.com/Excel/Cha....html#OHLCplus

Bert wrote:
Hello,

I've this columns:
-Date
-Open
-High
-Low
-Close
-Avg14

Now I'm lookin for creatin a chart that:
* uses the Date-field on the X-axis (no problem)
* Creates a candle-graph based on the fields Open, High, Low, Close (also no
problem)
* AND: shows as line-graph based on the field Avg14

The last one is the problem. Does anyone have an idea?

tia,
Bert







--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

  #3   Report Post  
John Mansfield
 
Posts: n/a
Default

Bert,

One way to add the average line is to eliminate the use of the stock chart
and use a combination stacked bar and line chart. To explain, assume your
original data is as follows:

a b c d e
Date Open DHigh DLow Close Average
1/1/2005 55 58 53 56 55.67
1/2/2005 56 57 54 55 55.67
1/3/2005 55 57 53 56 55.67
1/4/2005 56 58 52 57 55.67
1/5/2005 55 59 53 57 55.67
1/6/2005 54 57 52 55 55.67
1/7/2005 53 56 51 54 55.67
1/8/2005 54 58 52 56 55.67
1/9/2005 56 57 54 55 55.67

Rearrange the data to look like this.

f g h i j
Date Low High Average DHigh DLow
1/1/2005 55 1 55.67 2.33 2.67
1/2/2005 55 1 55.67 1.33 1.67
1/3/2005 55 1 55.67 1.33 2.67
1/4/2005 56 1 55.67 2.33 3.67
1/5/2005 55 2 55.67 3.33 2.67
1/6/2005 54 1 55.67 1.33 3.67
1/7/2005 53 1 55.67 0.33 4.67
1/8/2005 54 2 55.67 2.33 3.67
1/9/2005 55 1 55.67 1.33 1.67

Column F is the minimum value of column A or D.
Column G is the absolute value of column A minus column D.
Column H is the average i.e. column E from above.
Column I is the daily high (column B) minus column E.
Column J is column E minus the daily low (column C).

The chart goes together in three steps. The first step is to create the
Open€“Close bars. Create a stacked column chart based on this data:

f g
Date Low High
1/1/2005 55 1
1/2/2005 55 1
1/3/2005 55 1
1/4/2005 56 1
1/5/2005 55 2
1/6/2005 54 1
1/7/2005 53 1
1/8/2005 54 2
1/9/2005 55 1

Double-click on the bottom set of columns and go to Format Data Series -
Patterns Tab - Border = None and Area = None. When complete, all you should
see are the bars associated with the smaller values.

The second step is to create the average. Click onside of the chart, go to
the standard toolbar, and click on Chart - Source Data - Add - Values.
Choose the average value range for this series (55.67s). Click OK and you
should have some more columns on the chart. Click on the new columns, go to
the standard toolbar, and go to chart - chart Type - line - OK. When
complete you should have the Open-Close bars with an average line in the
chart.

The third step is to add the High-Low lines. Double-click on the average
line and in the Format Data Series dialog box go to the Y-Error Bars Tab.
Click in the Custom option. For the positive error bars, choose the Daily
High range (column I). For the negative error bars, choose the Daily Low
range (column J).

All you have to do now is format the chart like you would like.

----
Regards,
John Mansfield
http://www.pdbook.com



"Bert" wrote:

Hello,

I've this columns:
-Date
-Open
-High
-Low
-Close
-Avg14

Now I'm lookin for creatin a chart that:
* uses the Date-field on the X-axis (no problem)
* Creates a candle-graph based on the fields Open, High, Low, Close (also no
problem)
* AND: shows as line-graph based on the field Avg14

The last one is the problem. Does anyone have an idea?

tia,
Bert






  #4   Report Post  
Bert
 
Posts: n/a
Default

Great, thank you. With the info on this site I could create what I want :)


"Debra Dalgleish" schreef in bericht
...
Jon Peltier has instructions on his web site:

http://www.peltiertech.com/Excel/Cha....html#OHLCplus

Bert wrote:
Hello,

I've this columns:
-Date
-Open
-High
-Low
-Close
-Avg14

Now I'm lookin for creatin a chart that:
* uses the Date-field on the X-axis (no problem)
* Creates a candle-graph based on the fields Open, High, Low, Close (also
no
problem)
* AND: shows as line-graph based on the field Avg14

The last one is the problem. Does anyone have an idea?

tia,
Bert







--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html



  #5   Report Post  
Bert
 
Posts: n/a
Default

Thank you John, I have what I want now thanks to the post of Debra.


"John Mansfield" schreef in
bericht ...
Bert,

One way to add the average line is to eliminate the use of the stock chart
and use a combination stacked bar and line chart. To explain, assume your
original data is as follows:

a b c d e
Date Open DHigh DLow Close Average
1/1/2005 55 58 53 56 55.67
1/2/2005 56 57 54 55 55.67
1/3/2005 55 57 53 56 55.67
1/4/2005 56 58 52 57 55.67
1/5/2005 55 59 53 57 55.67
1/6/2005 54 57 52 55 55.67
1/7/2005 53 56 51 54 55.67
1/8/2005 54 58 52 56 55.67
1/9/2005 56 57 54 55 55.67

Rearrange the data to look like this.

f g h i j
Date Low High Average DHigh DLow
1/1/2005 55 1 55.67 2.33 2.67
1/2/2005 55 1 55.67 1.33 1.67
1/3/2005 55 1 55.67 1.33 2.67
1/4/2005 56 1 55.67 2.33 3.67
1/5/2005 55 2 55.67 3.33 2.67
1/6/2005 54 1 55.67 1.33 3.67
1/7/2005 53 1 55.67 0.33 4.67
1/8/2005 54 2 55.67 2.33 3.67
1/9/2005 55 1 55.67 1.33 1.67

Column F is the minimum value of column A or D.
Column G is the absolute value of column A minus column D.
Column H is the average i.e. column E from above.
Column I is the daily high (column B) minus column E.
Column J is column E minus the daily low (column C).

The chart goes together in three steps. The first step is to create the
Open-Close bars. Create a stacked column chart based on this data:

f g
Date Low High
1/1/2005 55 1
1/2/2005 55 1
1/3/2005 55 1
1/4/2005 56 1
1/5/2005 55 2
1/6/2005 54 1
1/7/2005 53 1
1/8/2005 54 2
1/9/2005 55 1

Double-click on the bottom set of columns and go to Format Data Series -
Patterns Tab - Border = None and Area = None. When complete, all you
should
see are the bars associated with the smaller values.

The second step is to create the average. Click onside of the chart, go
to
the standard toolbar, and click on Chart - Source Data - Add - Values.
Choose the average value range for this series (55.67's). Click OK and
you
should have some more columns on the chart. Click on the new columns, go
to
the standard toolbar, and go to chart - chart Type - line - OK. When
complete you should have the Open-Close bars with an average line in the
chart.

The third step is to add the High-Low lines. Double-click on the average
line and in the Format Data Series dialog box go to the Y-Error Bars Tab.
Click in the Custom option. For the positive error bars, choose the Daily
High range (column I). For the negative error bars, choose the Daily Low
range (column J).

All you have to do now is format the chart like you would like.

----
Regards,
John Mansfield
http://www.pdbook.com



"Bert" wrote:

Hello,

I've this columns:
-Date
-Open
-High
-Low
-Close
-Avg14

Now I'm lookin for creatin a chart that:
* uses the Date-field on the X-axis (no problem)
* Creates a candle-graph based on the fields Open, High, Low, Close (also
no
problem)
* AND: shows as line-graph based on the field Avg14

The last one is the problem. Does anyone have an idea?

tia,
Bert








Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Combine candle and line in a single chart Bert Charts and Charting in Excel 1 January 20th 05 02:08 PM


All times are GMT +1. The time now is 10:32 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"