Jon's technique helps me a lot to plot a stock's HLC chart
manually. But if I record it as a macro based on his
recommended steps, it doesn't work anymore. I have a
standard Data sheet, the
stock's "Date", "Open", "High", "Low" and "Close" data
located at "A, B, C, D, E" columns respectively.

The following "HLC20 macro" works fine, but "OpenLine
macro" doesn't. Can anyone find out what's the problem
with "OpenLine macro"? Thanks.

Sub HLC20()
Range("A1:A20,C1:E20").Select
Range("C1").Activate
Charts.Add
ActiveChart.ChartType = xlStockHLC
ActiveChart.SetSourceData Source:=Sheets("Data
Sheet").Range("A1:A20,C1:E20") _
, PlotBy:=xlColumns
ActiveChart.Location Whe=xlLocationAsNewSheet,
Name:="Chart1"
With ActiveChart
.HasAxis(xlCategory, xlPrimary) = True
.HasAxis(xlValue, xlPrimary) = True
End With
ActiveChart.Axes(xlCategory, xlPrimary).CategoryType =
xlCategoryScale
End Sub

Sub OpenLine()
Sheets("Data Sheet").Select
Range("A1:B20").Select
Selection.Copy
Sheets("Chart1").Select
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection.Paste Rowcol:=xlColumns,
SeriesLabels:=True, _
CategoryLabels:=True, Replace:=False,
NewSeries:=True
ActiveChart.SeriesCollection(4).Select
Application.CutCopyMode = False
ActiveChart.SeriesCollection(4).ChartType =
xlXYScatterSmooth
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(4).XValues = ""
ActiveChart.SeriesCollection(4).Select
ActiveChart.SeriesCollection(4).AxisGroup = 1
End Sub

I have created a chart maker. see it at
http://au.geocities.com/excelmarksway

If you would like to send some dummy info to me, with
requestinfo, maybe I can create a chartmaker for you.
I will require a real return email address
regards
Mark
-----Original Message-----
Jon's technique helps me a lot to plot a stock's HLC
chart
manually. But if I record it as a macro based on his
recommended steps, it doesn't work anymore. I have a
standard Data sheet, the
stock's "Date", "Open", "High", "Low" and "Close" data
located at "A, B, C, D, E" columns respectively.

The following "HLC20 macro" works fine, but "OpenLine
macro" doesn't. Can anyone find out what's the problem
with "OpenLine macro"? Thanks.

Sub HLC20()
Range("A1:A20,C1:E20").Select
Range("C1").Activate
Charts.Add
ActiveChart.ChartType = xlStockHLC
ActiveChart.SetSourceData Source:=Sheets("Data
Sheet").Range("A1:A20,C1:E20") _
, PlotBy:=xlColumns
ActiveChart.Location Whe=xlLocationAsNewSheet,
Name:="Chart1"
With ActiveChart
.HasAxis(xlCategory, xlPrimary) = True
.HasAxis(xlValue, xlPrimary) = True
End With
ActiveChart.Axes(xlCategory, xlPrimary).CategoryType
=
xlCategoryScale
End Sub

Sub OpenLine()
Sheets("Data Sheet").Select
Range("A1:B20").Select
Selection.Copy
Sheets("Chart1").Select
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection.Paste Rowcol:=xlColumns,
SeriesLabels:=True, _
CategoryLabels:=True, Replace:=False,
NewSeries:=True
ActiveChart.SeriesCollection(4).Select
Application.CutCopyMode = False
ActiveChart.SeriesCollection(4).ChartType =
xlXYScatterSmooth
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(4).XValues = ""
ActiveChart.SeriesCollection(4).Select
ActiveChart.SeriesCollection(4).AxisGroup = 1
End Sub
.

Mark,

I just went to your site, I did see any of your examples
relevant to the problem what I described here.

Here is the original data you may use it to test the
Charting macro:

Date,Open,High,Low,Close
6-Jul-04,28.32,28.33,27.94,28.02
2-Jul-04,28.62,28.68,28.4,28.57
1-Jul-04,28.7,28.84,28.26,28.63
30-Jun-04,28.57,28.8,28.39,28.56
29-Jun-04,28.18,28.58,28.18,28.5
28-Jun-04,28.6,28.75,28.17,28.28
25-Jun-04,28.48,28.63,28.25,28.57
24-Jun-04,28.48,28.65,28.36,28.39
23-Jun-04,28.2,28.38,28,28.3
22-Jun-04,28.15,28.35,27.81,28.29
21-Jun-04,28.22,28.66,28.12,28.35
18-Jun-04,27.77,28.5,27.7,28.35
17-Jun-04,27.31,27.92,27.29,27.77
16-Jun-04,27.34,27.5,27.15,27.32
15-Jun-04,26.99,27.6,26.97,27.41
14-Jun-04,26.55,26.9,26.53,26.9
10-Jun-04,26.38,26.79,26.38,26.77
9-Jun-04,26.4,26.65,26.4,26.47
8-Jun-04,26.28,26.65,26.24,26.6


-----Original Message-----
I have created a chart maker. see it at
http://au.geocities.com/excelmarksway

If you would like to send some dummy info to me, with
requestinfo, maybe I can create a chartmaker for you.
I will require a real return email address
regards
Mark
-----Original Message-----
Jon's technique helps me a lot to plot a stock's HLC
chart
manually. But if I record it as a macro based on his
recommended steps, it doesn't work anymore. I have a
standard Data sheet, the
stock's "Date", "Open", "High", "Low" and "Close" data
located at "A, B, C, D, E" columns respectively.

The following "HLC20 macro" works fine, but "OpenLine
macro" doesn't. Can anyone find out what's the problem
with "OpenLine macro"? Thanks.

Sub HLC20()
Range("A1:A20,C1:E20").Select
Range("C1").Activate
Charts.Add
ActiveChart.ChartType = xlStockHLC
ActiveChart.SetSourceData Source:=Sheets("Data
Sheet").Range("A1:A20,C1:E20") _
, PlotBy:=xlColumns
ActiveChart.Location Whe=xlLocationAsNewSheet,
Name:="Chart1"
With ActiveChart
.HasAxis(xlCategory, xlPrimary) = True
.HasAxis(xlValue, xlPrimary) = True
End With
ActiveChart.Axes(xlCategory, xlPrimary).CategoryType
=
xlCategoryScale
End Sub

Sub OpenLine()
Sheets("Data Sheet").Select
Range("A1:B20").Select
Selection.Copy
Sheets("Chart1").Select
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection.Paste
Rowcol:=xlColumns,
SeriesLabels:=True, _
CategoryLabels:=True, Replace:=False,
NewSeries:=True
ActiveChart.SeriesCollection(4).Select
Application.CutCopyMode = False
ActiveChart.SeriesCollection(4).ChartType =
xlXYScatterSmooth
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(4).XValues = ""
ActiveChart.SeriesCollection(4).Select
ActiveChart.SeriesCollection(4).AxisGroup = 1
End Sub
.

.

Shi -

Sometimes the macro recorder gets confused. This will work if you put
Chart Type after Set Source Data:

Sub HLC20()
Range("A1:A20,C1:E20").Select
Range("C1").Activate
Charts.Add
ActiveChart.SetSourceData Source:=Sheets("Data
Sheet").Range("A1:A20,C1:E20") _
, PlotBy:=xlColumns
ActiveChart.ChartType = xlStockHLC
ActiveChart.Location Whe=xlLocationAsNewSheet,
Name:="Chart1"
[etc.]

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Shi wrote:

Jon's technique helps me a lot to plot a stock's HLC chart
manually. But if I record it as a macro based on his
recommended steps, it doesn't work anymore. I have a
standard Data sheet, the
stock's "Date", "Open", "High", "Low" and "Close" data
located at "A, B, C, D, E" columns respectively.

The following "HLC20 macro" works fine, but "OpenLine
macro" doesn't. Can anyone find out what's the problem
with "OpenLine macro"? Thanks.

Sub HLC20()
Range("A1:A20,C1:E20").Select
Range("C1").Activate
Charts.Add
ActiveChart.ChartType = xlStockHLC
ActiveChart.SetSourceData Source:=Sheets("Data
Sheet").Range("A1:A20,C1:E20") _
, PlotBy:=xlColumns
ActiveChart.Location Whe=xlLocationAsNewSheet,
Name:="Chart1"
With ActiveChart
.HasAxis(xlCategory, xlPrimary) = True
.HasAxis(xlValue, xlPrimary) = True
End With
ActiveChart.Axes(xlCategory, xlPrimary).CategoryType =
xlCategoryScale
End Sub

Sub OpenLine()
Sheets("Data Sheet").Select
Range("A1:B20").Select
Selection.Copy
Sheets("Chart1").Select
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection.Paste Rowcol:=xlColumns,
SeriesLabels:=True, _
CategoryLabels:=True, Replace:=False,
NewSeries:=True
ActiveChart.SeriesCollection(4).Select
Application.CutCopyMode = False
ActiveChart.SeriesCollection(4).ChartType =
xlXYScatterSmooth
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(4).XValues = ""
ActiveChart.SeriesCollection(4).Select
ActiveChart.SeriesCollection(4).AxisGroup = 1
End Sub

Jon,

You are the Master of Excel Charting. Thank you very much
for your help here.

I just tried your solution, I still couldn't get
my "OpenLine" or your "Index line" working with a macro.

Can you offer me more help? Thanks.



-----Original Message-----
Shi -

Sometimes the macro recorder gets confused. This will
work if you put
Chart Type after Set Source Data:

Sub HLC20()
Range("A1:A20,C1:E20").Select
Range("C1").Activate
Charts.Add
ActiveChart.SetSourceData Source:=Sheets("Data
Sheet").Range("A1:A20,C1:E20") _
, PlotBy:=xlColumns
ActiveChart.ChartType = xlStockHLC
ActiveChart.Location Whe=xlLocationAsNewSheet,
Name:="Chart1"
[etc.]

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Shi wrote:

Jon's technique helps me a lot to plot a stock's HLC
chart
manually. But if I record it as a macro based on his
recommended steps, it doesn't work anymore. I have a
standard Data sheet, the
stock's "Date", "Open", "High", "Low" and "Close" data
located at "A, B, C, D, E" columns respectively.

The following "HLC20 macro" works fine, but "OpenLine
macro" doesn't. Can anyone find out what's the problem
with "OpenLine macro"? Thanks.

Sub HLC20()
Range("A1:A20,C1:E20").Select
Range("C1").Activate
Charts.Add
ActiveChart.ChartType = xlStockHLC
ActiveChart.SetSourceData Source:=Sheets("Data
Sheet").Range("A1:A20,C1:E20") _
, PlotBy:=xlColumns
ActiveChart.Location Whe=xlLocationAsNewSheet,
Name:="Chart1"
With ActiveChart
.HasAxis(xlCategory, xlPrimary) = True
.HasAxis(xlValue, xlPrimary) = True
End With
ActiveChart.Axes(xlCategory,
xlPrimary).CategoryType =
xlCategoryScale
End Sub

Sub OpenLine()
Sheets("Data Sheet").Select
Range("A1:B20").Select
Selection.Copy
Sheets("Chart1").Select
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection.Paste
Rowcol:=xlColumns,
SeriesLabels:=True, _
CategoryLabels:=True, Replace:=False,
NewSeries:=True
ActiveChart.SeriesCollection(4).Select
Application.CutCopyMode = False
ActiveChart.SeriesCollection(4).ChartType =
xlXYScatterSmooth
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(4).XValues = ""
ActiveChart.SeriesCollection(4).Select
ActiveChart.SeriesCollection(4).AxisGroup = 1
End Sub

.

Jon,

The true problem is with these two lines:
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(4).XValues = ""

If I run both macros without these above two lines,
everthing seems fine.

If I add these two lines inside my macro, this macro just
doesn't know how to select "SeriesCollection(4)".

Would you please give me an advice?

Thanks for your help,


-----Original Message-----
Jon,

You are the Master of Excel Charting. Thank you very much
for your help here.

I just tried your solution, I still couldn't get
my "OpenLine" or your "Index line" working with a macro.

Can you offer me more help? Thanks.



-----Original Message-----
Shi -

Sometimes the macro recorder gets confused. This will
work if you put
Chart Type after Set Source Data:

Sub HLC20()
Range("A1:A20,C1:E20").Select
Range("C1").Activate
Charts.Add
ActiveChart.SetSourceData Source:=Sheets("Data
Sheet").Range("A1:A20,C1:E20") _
, PlotBy:=xlColumns
ActiveChart.ChartType = xlStockHLC
ActiveChart.Location Whe=xlLocationAsNewSheet,
Name:="Chart1"
[etc.]

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Shi wrote:

Jon's technique helps me a lot to plot a stock's HLC
chart
manually. But if I record it as a macro based on his
recommended steps, it doesn't work anymore. I have a
standard Data sheet, the
stock's "Date", "Open", "High", "Low" and "Close" data
located at "A, B, C, D, E" columns respectively.

The following "HLC20 macro" works fine, but "OpenLine
macro" doesn't. Can anyone find out what's the problem
with "OpenLine macro"? Thanks.

Sub HLC20()
Range("A1:A20,C1:E20").Select
Range("C1").Activate
Charts.Add
ActiveChart.ChartType = xlStockHLC
ActiveChart.SetSourceData Source:=Sheets("Data
Sheet").Range("A1:A20,C1:E20") _
, PlotBy:=xlColumns
ActiveChart.Location Whe=xlLocationAsNewSheet,
Name:="Chart1"
With ActiveChart
.HasAxis(xlCategory, xlPrimary) = True
.HasAxis(xlValue, xlPrimary) = True
End With
ActiveChart.Axes(xlCategory,
xlPrimary).CategoryType =
xlCategoryScale
End Sub

Sub OpenLine()
Sheets("Data Sheet").Select
Range("A1:B20").Select
Selection.Copy
Sheets("Chart1").Select
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection.Paste
Rowcol:=xlColumns,
SeriesLabels:=True, _
CategoryLabels:=True, Replace:=False,
NewSeries:=True
ActiveChart.SeriesCollection(4).Select
Application.CutCopyMode = False
ActiveChart.SeriesCollection(4).ChartType =
xlXYScatterSmooth
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(4).XValues = ""
ActiveChart.SeriesCollection(4).Select
ActiveChart.SeriesCollection(4).AxisGroup = 1
End Sub

.

.

Shi -

1. Do you need to select the plot area? Probably not.

2. It's dangerous to be setting the XValues to "". What purpose does
this line serve? The effect is to remove reference to the X Values range
in the series formula, which means the series will just use 1, 2, 3,
etc. Interestingly, if you do this to the Y values, it does not clear
that piece of the formula. Instead, it converts from a range containing
values, to an array of these values {1,2,3,4}. Still, I imagine it's a
way to create problems.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Shi wrote:

Jon,

The true problem is with these two lines:
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(4).XValues = ""

If I run both macros without these above two lines,
everthing seems fine.

If I add these two lines inside my macro, this macro just
doesn't know how to select "SeriesCollection(4)".

Would you please give me an advice?

Thanks for your help,



-----Original Message-----
Jon,

You are the Master of Excel Charting. Thank you very much
for your help here.

I just tried your solution, I still couldn't get
my "OpenLine" or your "Index line" working with a macro.

Can you offer me more help? Thanks.




-----Original Message-----
Shi -

Sometimes the macro recorder gets confused. This will

work if you put

Chart Type after Set Source Data:

Sub HLC20()
Range("A1:A20,C1:E20").Select
Range("C1").Activate
Charts.Add
ActiveChart.SetSourceData Source:=Sheets("Data
Sheet").Range("A1:A20,C1:E20") _
, PlotBy:=xlColumns
ActiveChart.ChartType = xlStockHLC
ActiveChart.Location Whe=xlLocationAsNewSheet,
Name:="Chart1"
[etc.]

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Shi wrote:


Jon's technique helps me a lot to plot a stock's HLC

chart

manually. But if I record it as a macro based on his
recommended steps, it doesn't work anymore. I have a
standard Data sheet, the
stock's "Date", "Open", "High", "Low" and "Close" data
located at "A, B, C, D, E" columns respectively.

The following "HLC20 macro" works fine, but "OpenLine
macro" doesn't. Can anyone find out what's the problem
with "OpenLine macro"? Thanks.

Sub HLC20()
Range("A1:A20,C1:E20").Select
Range("C1").Activate
Charts.Add
ActiveChart.ChartType = xlStockHLC
ActiveChart.SetSourceData Source:=Sheets("Data
Sheet").Range("A1:A20,C1:E20") _
, PlotBy:=xlColumns
ActiveChart.Location Whe=xlLocationAsNewSheet,
Name:="Chart1"
With ActiveChart
.HasAxis(xlCategory, xlPrimary) = True
.HasAxis(xlValue, xlPrimary) = True
End With
ActiveChart.Axes(xlCategory,

xlPrimary).CategoryType =

xlCategoryScale
End Sub

Sub OpenLine()
Sheets("Data Sheet").Select
Range("A1:B20").Select
Selection.Copy
Sheets("Chart1").Select
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection.Paste

Rowcol:=xlColumns,

SeriesLabels:=True, _
CategoryLabels:=True, Replace:=False,
NewSeries:=True
ActiveChart.SeriesCollection(4).Select
Application.CutCopyMode = False
ActiveChart.SeriesCollection(4).ChartType =
xlXYScatterSmooth
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(4).XValues = ""
ActiveChart.SeriesCollection(4).Select
ActiveChart.SeriesCollection(4).AxisGroup = 1
End Sub

.


.

Jon,

Thanks a lot for your time. All what I want to do is to
plot exactly the same HLC chart as you sampled on your web
site, but with a macro.

The only difference is I use the "Open" price line as my
Index line on HLC chart.

I can do this easily by following your suggested steps
manually, but I can't do it with a macro.

Can you help, please?
Thank you,



-----Original Message-----
Shi -

1. Do you need to select the plot area? Probably not.

2. It's dangerous to be setting the XValues to "". What
purpose does
this line serve? The effect is to remove reference to the
X Values range
in the series formula, which means the series will just
use 1, 2, 3,
etc. Interestingly, if you do this to the Y values, it
does not clear
that piece of the formula. Instead, it converts from a
range containing
values, to an array of these values {1,2,3,4}. Still, I
imagine it's a
way to create problems.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Shi wrote:

Jon,

The true problem is with these two lines:
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(4).XValues = ""

If I run both macros without these above two lines,
everthing seems fine.

If I add these two lines inside my macro, this macro
just
doesn't know how to select "SeriesCollection(4)".

Would you please give me an advice?

Thanks for your help,



-----Original Message-----
Jon,

You are the Master of Excel Charting. Thank you very
much
for your help here.

I just tried your solution, I still couldn't get
my "OpenLine" or your "Index line" working with a macro.

Can you offer me more help? Thanks.




-----Original Message-----
Shi -

Sometimes the macro recorder gets confused. This will

work if you put

Chart Type after Set Source Data:

Sub HLC20()
Range("A1:A20,C1:E20").Select
Range("C1").Activate
Charts.Add
ActiveChart.SetSourceData Source:=Sheets("Data
Sheet").Range("A1:A20,C1:E20") _
, PlotBy:=xlColumns
ActiveChart.ChartType = xlStockHLC
ActiveChart.Location Whe=xlLocationAsNewSheet,
Name:="Chart1"
[etc.]

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Shi wrote:


Jon's technique helps me a lot to plot a stock's HLC

chart

manually. But if I record it as a macro based on his
recommended steps, it doesn't work anymore. I have a
standard Data sheet, the
stock's "Date", "Open", "High", "Low" and "Close"
data
located at "A, B, C, D, E" columns respectively.

The following "HLC20 macro" works fine, but "OpenLine
macro" doesn't. Can anyone find out what's the
problem
with "OpenLine macro"? Thanks.

Sub HLC20()
Range("A1:A20,C1:E20").Select
Range("C1").Activate
Charts.Add
ActiveChart.ChartType = xlStockHLC
ActiveChart.SetSourceData Source:=Sheets("Data
Sheet").Range("A1:A20,C1:E20") _
, PlotBy:=xlColumns
ActiveChart.Location Whe=xlLocationAsNewSheet,
Name:="Chart1"
With ActiveChart
.HasAxis(xlCategory, xlPrimary) = True
.HasAxis(xlValue, xlPrimary) = True
End With
ActiveChart.Axes(xlCategory,

xlPrimary).CategoryType =

xlCategoryScale
End Sub

Sub OpenLine()
Sheets("Data Sheet").Select
Range("A1:B20").Select
Selection.Copy
Sheets("Chart1").Select
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection.Paste

Rowcol:=xlColumns,

SeriesLabels:=True, _
CategoryLabels:=True, Replace:=False,
NewSeries:=True
ActiveChart.SeriesCollection(4).Select
Application.CutCopyMode = False
ActiveChart.SeriesCollection(4).ChartType =
xlXYScatterSmooth
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(4).XValues = ""
ActiveChart.SeriesCollection(4).Select
ActiveChart.SeriesCollection(4).AxisGroup = 1
End Sub

.


.


.

Shi -

This example assumes
- Data in columns, L-R, of Date, Open, High, Low, Close
- Dates are real numerical dates

The only error checking is whether a range of cells is selected.

'' START CODE ------------------------------------------------
Sub StockHLC_OpenLine()
Dim rData As Range
Dim rHLC As Range
Dim rOpen As Range
Dim rXVals As Range
Dim cHLCO As Chart

If TypeName(Selection) < "Range" Then
MsgBox "Select a range of cells containing data," & vbCrLf _
& "in order: Date, Open, High. Low, Close", vbExclamation, _
"Invalid selection"
Exit Sub
End If

Set rData = Selection
If rData.Columns.Count < 5 Then Set rData = rData.CurrentRegion

Set rXVals = rData.Columns(1)
Set rHLC = Union(rXVals, rData.Columns(3).Resize(, 3))
Set rOpen = rData.Columns(2)

Set cHLCO = ActiveSheet.ChartObjects.Add(100, 100, 375, 225).Chart
With cHLCO
.SetSourceData Source:=rHLC, PlotBy:=xlColumns
.ChartType = xlStockHLC
With .SeriesCollection.NewSeries
.Values = rOpen
.ChartType = xlXYScatterLinesNoMarkers
.XValues = rXVals
.Name = "Open"
End With
End With
End Sub
'' END CODE --------------------------------------------------------

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Shi wrote:

Jon,

Thanks a lot for your time. All what I want to do is to
plot exactly the same HLC chart as you sampled on your web
site, but with a macro.

The only difference is I use the "Open" price line as my
Index line on HLC chart.

I can do this easily by following your suggested steps
manually, but I can't do it with a macro.

Can you help, please?
Thank you,




-----Original Message-----
Shi -

1. Do you need to select the plot area? Probably not.

2. It's dangerous to be setting the XValues to "". What

purpose does

this line serve? The effect is to remove reference to the

X Values range

in the series formula, which means the series will just

use 1, 2, 3,

etc. Interestingly, if you do this to the Y values, it

does not clear

that piece of the formula. Instead, it converts from a

range containing

values, to an array of these values {1,2,3,4}. Still, I

imagine it's a

way to create problems.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Shi wrote:


Jon,

The true problem is with these two lines:
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(4).XValues = ""

If I run both macros without these above two lines,
everthing seems fine.

If I add these two lines inside my macro, this macro

just

doesn't know how to select "SeriesCollection(4)".

Would you please give me an advice?

Thanks for your help,




-----Original Message-----
Jon,

You are the Master of Excel Charting. Thank you very

much

for your help here.

I just tried your solution, I still couldn't get
my "OpenLine" or your "Index line" working with a macro.

Can you offer me more help? Thanks.





-----Original Message-----
Shi -

Sometimes the macro recorder gets confused. This will

work if you put


Chart Type after Set Source Data:

Sub HLC20()
Range("A1:A20,C1:E20").Select
Range("C1").Activate
Charts.Add
ActiveChart.SetSourceData Source:=Sheets("Data
Sheet").Range("A1:A20,C1:E20") _
, PlotBy:=xlColumns
ActiveChart.ChartType = xlStockHLC
ActiveChart.Location Whe=xlLocationAsNewSheet,
Name:="Chart1"
[etc.]

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Shi wrote:



Jon's technique helps me a lot to plot a stock's HLC

chart


manually. But if I record it as a macro based on his
recommended steps, it doesn't work anymore. I have a
standard Data sheet, the
stock's "Date", "Open", "High", "Low" and "Close"

data

located at "A, B, C, D, E" columns respectively.

The following "HLC20 macro" works fine, but "OpenLine
macro" doesn't. Can anyone find out what's the

problem

with "OpenLine macro"? Thanks.

Sub HLC20()
Range("A1:A20,C1:E20").Select
Range("C1").Activate
Charts.Add
ActiveChart.ChartType = xlStockHLC
ActiveChart.SetSourceData Source:=Sheets("Data
Sheet").Range("A1:A20,C1:E20") _
, PlotBy:=xlColumns
ActiveChart.Location Whe=xlLocationAsNewSheet,
Name:="Chart1"
With ActiveChart
.HasAxis(xlCategory, xlPrimary) = True
.HasAxis(xlValue, xlPrimary) = True
End With
ActiveChart.Axes(xlCategory,

xlPrimary).CategoryType =


xlCategoryScale
End Sub

Sub OpenLine()
Sheets("Data Sheet").Select
Range("A1:B20").Select
Selection.Copy
Sheets("Chart1").Select
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection.Paste

Rowcol:=xlColumns,


SeriesLabels:=True, _
CategoryLabels:=True, Replace:=False,
NewSeries:=True
ActiveChart.SeriesCollection(4).Select
Application.CutCopyMode = False
ActiveChart.SeriesCollection(4).ChartType =
xlXYScatterSmooth
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(4).XValues = ""
ActiveChart.SeriesCollection(4).Select
ActiveChart.SeriesCollection(4).AxisGroup = 1
End Sub

.


.


.

Jon,

Thanks a lot for your great solution. I almost got the
final result by running your macro. (Maybe I missed one or
two steps somewhere???)

So far, I got HLC chart, but without OpenLine. I think
this was due to my chart was not in a CategoryType. How do
you add these lines inside your macro?

With ActiveChart
.HasAxis(xlCategory, xlPrimary) = True
.HasAxis(xlValue, xlPrimary) = True
End With
ActiveChart.Axes(xlCategory, xlPrimary).CategoryType =
xlCategoryScale


Thanks for your advice,


-----Original Message-----
Shi -

This example assumes
- Data in columns, L-R, of Date, Open, High, Low, Close
- Dates are real numerical dates

The only error checking is whether a range of cells is
selected.

'' START CODE --------------------------------------------
----
Sub StockHLC_OpenLine()
Dim rData As Range
Dim rHLC As Range
Dim rOpen As Range
Dim rXVals As Range
Dim cHLCO As Chart

If TypeName(Selection) < "Range" Then
MsgBox "Select a range of cells containing
data," & vbCrLf _
& "in order: Date, Open, High. Low, Close",
vbExclamation, _
"Invalid selection"
Exit Sub
End If

Set rData = Selection
If rData.Columns.Count < 5 Then Set rData =
rData.CurrentRegion

Set rXVals = rData.Columns(1)
Set rHLC = Union(rXVals, rData.Columns(3).Resize(,
3))
Set rOpen = rData.Columns(2)

Set cHLCO = ActiveSheet.ChartObjects.Add(100, 100,
375, 225).Chart
With cHLCO
.SetSourceData Source:=rHLC, PlotBy:=xlColumns
.ChartType = xlStockHLC
With .SeriesCollection.NewSeries
.Values = rOpen
.ChartType = xlXYScatterLinesNoMarkers
.XValues = rXVals
.Name = "Open"
End With
End With
End Sub
'' END CODE ----------------------------------------------
----------

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Shi wrote:

Jon,

Thanks a lot for your time. All what I want to do is to
plot exactly the same HLC chart as you sampled on your
web
site, but with a macro.

The only difference is I use the "Open" price line as
my
Index line on HLC chart.

I can do this easily by following your suggested steps
manually, but I can't do it with a macro.

Can you help, please?
Thank you,




-----Original Message-----
Shi -

1. Do you need to select the plot area? Probably not.

2. It's dangerous to be setting the XValues to "". What

purpose does

this line serve? The effect is to remove reference to
the

X Values range

in the series formula, which means the series will just

use 1, 2, 3,

etc. Interestingly, if you do this to the Y values, it

does not clear

that piece of the formula. Instead, it converts from a

range containing

values, to an array of these values {1,2,3,4}. Still, I

imagine it's a

way to create problems.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Shi wrote:


Jon,

The true problem is with these two lines:
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(4).XValues = ""

If I run both macros without these above two lines,
everthing seems fine.

If I add these two lines inside my macro, this macro

just

doesn't know how to select "SeriesCollection(4)".

Would you please give me an advice?

Thanks for your help,




-----Original Message-----
Jon,

You are the Master of Excel Charting. Thank you very

much

for your help here.

I just tried your solution, I still couldn't get
my "OpenLine" or your "Index line" working with a
macro.

Can you offer me more help? Thanks.





-----Original Message-----
Shi -

Sometimes the macro recorder gets confused. This
will

work if you put


Chart Type after Set Source Data:

Sub HLC20()
Range("A1:A20,C1:E20").Select
Range("C1").Activate
Charts.Add
ActiveChart.SetSourceData Source:=Sheets("Data
Sheet").Range("A1:A20,C1:E20") _
, PlotBy:=xlColumns
ActiveChart.ChartType = xlStockHLC
ActiveChart.Location Whe=xlLocationAsNewSheet,
Name:="Chart1"
[etc.]

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Shi wrote:



Jon's technique helps me a lot to plot a stock's
HLC

chart


manually. But if I record it as a macro based on
his
recommended steps, it doesn't work anymore. I have
a
standard Data sheet, the
stock's "Date", "Open", "High", "Low" and "Close"

data

located at "A, B, C, D, E" columns respectively.

The following "HLC20 macro" works fine,
but "OpenLine
macro" doesn't. Can anyone find out what's the

problem

with "OpenLine macro"? Thanks.

Sub HLC20()
Range("A1:A20,C1:E20").Select
Range("C1").Activate
Charts.Add
ActiveChart.ChartType = xlStockHLC
ActiveChart.SetSourceData Source:=Sheets("Data
Sheet").Range("A1:A20,C1:E20") _
, PlotBy:=xlColumns
ActiveChart.Location
Whe=xlLocationAsNewSheet,
Name:="Chart1"
With ActiveChart
.HasAxis(xlCategory, xlPrimary) = True
.HasAxis(xlValue, xlPrimary) = True
End With
ActiveChart.Axes(xlCategory,

xlPrimary).CategoryType =


xlCategoryScale
End Sub

Sub OpenLine()
Sheets("Data Sheet").Select
Range("A1:B20").Select
Selection.Copy
Sheets("Chart1").Select
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection.Paste

Rowcol:=xlColumns,


SeriesLabels:=True, _
CategoryLabels:=True, Replace:=False,
NewSeries:=True
ActiveChart.SeriesCollection(4).Select
Application.CutCopyMode = False
ActiveChart.SeriesCollection(4).ChartType =
xlXYScatterSmooth
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(4).XValues = ""
ActiveChart.SeriesCollection(4).Select
ActiveChart.SeriesCollection(4).AxisGroup = 1
End Sub

.


.


.


.

Shi -

If your dates are true numerical dates, and you make a stock chart,
Excel will make a time scale axis. This works fine for both the XY
series (Open Line) and the stock chart series.

If you make the X axis into a category type, the Open Line XY series
still uses the dates as input. Your categories for the stock data will
be numbers like 1, 2, 3, etc., while the X data for the Open Line series
will be numbers in the 39 thousands, which is what dates look like. The
two series will not want to line up in this case.

Follow the instructions about the data arrangement
(Date-Open-High-Low-Close), and the date configuration (real numerical
dates), then try again.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Shi wrote:

Jon,

Thanks a lot for your great solution. I almost got the
final result by running your macro. (Maybe I missed one or
two steps somewhere???)

So far, I got HLC chart, but without OpenLine. I think
this was due to my chart was not in a CategoryType. How do
you add these lines inside your macro?

With ActiveChart
.HasAxis(xlCategory, xlPrimary) = True
.HasAxis(xlValue, xlPrimary) = True
End With
ActiveChart.Axes(xlCategory, xlPrimary).CategoryType =
xlCategoryScale


Thanks for your advice,

Jon,

Thanks for your time. How do you configure the Date as the
Real Numerical Date?


-----Original Message-----
Shi -

If your dates are true numerical dates, and you make a
stock chart,
Excel will make a time scale axis. This works fine for
both the XY
series (Open Line) and the stock chart series.

If you make the X axis into a category type, the Open
Line XY series
still uses the dates as input. Your categories for the
stock data will
be numbers like 1, 2, 3, etc., while the X data for the
Open Line series
will be numbers in the 39 thousands, which is what dates
look like. The
two series will not want to line up in this case.

Follow the instructions about the data arrangement
(Date-Open-High-Low-Close), and the date configuration
(real numerical
dates), then try again.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Shi wrote:

Jon,

Thanks a lot for your great solution. I almost got the
final result by running your macro. (Maybe I missed one
or
two steps somewhere???)

So far, I got HLC chart, but without OpenLine. I think
this was due to my chart was not in a CategoryType. How
do
you add these lines inside your macro?

With ActiveChart
.HasAxis(xlCategory, xlPrimary) = True
.HasAxis(xlValue, xlPrimary) = True
End With
ActiveChart.Axes(xlCategory, xlPrimary).CategoryType =
xlCategoryScale


Thanks for your advice,



.

Jon,

Here were the real data I run your macro, I was still
unable to get HLC chart with an Open line.

Date,Open,High,Low,Close
38174,28.32,28.33,27.94,28.02
38170,28.62,28.68,28.40,28.57
38169,28.70,28.84,28.26,28.63
38168,28.57,28.80,28.39,28.56
38167,28.18,28.58,28.18,28.50
38166,28.60,28.75,28.17,28.28
38163,28.48,28.63,28.25,28.57
38162,28.48,28.65,28.36,28.39
38161,28.20,28.38,28.00,28.30
38160,28.15,28.35,27.81,28.29
38159,28.22,28.66,28.12,28.35
38156,27.77,28.50,27.70,28.35
38155,27.31,27.92,27.29,27.77
38154,27.34,27.50,27.15,27.32
38153,26.99,27.60,26.97,27.41
38152,26.55,26.90,26.53,26.90
38148,26.38,26.79,26.38,26.77
38147,26.40,26.65,26.40,26.47
38146,26.28,26.65,26.24,26.60

I had Date-Open-High-Low-Close at L-M-N-O-P columns.
What I did wrong? I have no idea at all.

Please help,




-----Original Message-----
Jon,

Thanks for your time. How do you configure the Date as
the
Real Numerical Date?


-----Original Message-----
Shi -

If your dates are true numerical dates, and you make a
stock chart,
Excel will make a time scale axis. This works fine for
both the XY
series (Open Line) and the stock chart series.

If you make the X axis into a category type, the Open
Line XY series
still uses the dates as input. Your categories for the
stock data will
be numbers like 1, 2, 3, etc., while the X data for the
Open Line series
will be numbers in the 39 thousands, which is what dates
look like. The
two series will not want to line up in this case.

Follow the instructions about the data arrangement
(Date-Open-High-Low-Close), and the date configuration
(real numerical
dates), then try again.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Shi wrote:

Jon,

Thanks a lot for your great solution. I almost got the
final result by running your macro. (Maybe I missed
one
or
two steps somewhere???)

So far, I got HLC chart, but without OpenLine. I think
this was due to my chart was not in a CategoryType.
How
do
you add these lines inside your macro?

With ActiveChart
.HasAxis(xlCategory, xlPrimary) = True
.HasAxis(xlValue, xlPrimary) = True
End With
ActiveChart.Axes(xlCategory, xlPrimary).CategoryType =
xlCategoryScale


Thanks for your advice,



.

.

By the way, I forgot to mention, after I changed the Date
format to a "General" or "Numeric" format, the original
macro was stopped at line: .ChartType = xlStockHLC

What should I do next?


-----Original Message-----
Jon,

Here were the real data I run your macro, I was still
unable to get HLC chart with an Open line.

Date,Open,High,Low,Close
38174,28.32,28.33,27.94,28.02
38170,28.62,28.68,28.40,28.57
38169,28.70,28.84,28.26,28.63
38168,28.57,28.80,28.39,28.56
38167,28.18,28.58,28.18,28.50
38166,28.60,28.75,28.17,28.28
38163,28.48,28.63,28.25,28.57
38162,28.48,28.65,28.36,28.39
38161,28.20,28.38,28.00,28.30
38160,28.15,28.35,27.81,28.29
38159,28.22,28.66,28.12,28.35
38156,27.77,28.50,27.70,28.35
38155,27.31,27.92,27.29,27.77
38154,27.34,27.50,27.15,27.32
38153,26.99,27.60,26.97,27.41
38152,26.55,26.90,26.53,26.90
38148,26.38,26.79,26.38,26.77
38147,26.40,26.65,26.40,26.47
38146,26.28,26.65,26.24,26.60

I had Date-Open-High-Low-Close at L-M-N-O-P columns.
What I did wrong? I have no idea at all.

Please help,




-----Original Message-----
Jon,

Thanks for your time. How do you configure the Date as
the
Real Numerical Date?


-----Original Message-----
Shi -

If your dates are true numerical dates, and you make a
stock chart,
Excel will make a time scale axis. This works fine for
both the XY
series (Open Line) and the stock chart series.

If you make the X axis into a category type, the Open
Line XY series
still uses the dates as input. Your categories for the
stock data will
be numbers like 1, 2, 3, etc., while the X data for the
Open Line series
will be numbers in the 39 thousands, which is what
dates
look like. The
two series will not want to line up in this case.

Follow the instructions about the data arrangement
(Date-Open-High-Low-Close), and the date configuration
(real numerical
dates), then try again.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Shi wrote:

Jon,

Thanks a lot for your great solution. I almost got
the
final result by running your macro. (Maybe I missed
one
or
two steps somewhere???)

So far, I got HLC chart, but without OpenLine. I
think
this was due to my chart was not in a CategoryType.
How
do
you add these lines inside your macro?

With ActiveChart
.HasAxis(xlCategory, xlPrimary) = True
.HasAxis(xlValue, xlPrimary) = True
End With
ActiveChart.Axes(xlCategory, xlPrimary).CategoryType
=
xlCategoryScale


Thanks for your advice,



.

.

.

In my sample table, I had no header labels. The code below adjusts for
this. You just need to format your dates as any date format.

Sub StockHLC_OpenLine()
Dim rData As Range
Dim rHLC As Range
Dim rOpen As Range
Dim rXVals As Range
Dim cHLCO As Chart
Dim iPts As Integer
Dim iRows As Integer

If TypeName(Selection) < "Range" Then
MsgBox "Select a range of cells containing data," & vbCrLf _
& "in order: Date, Open, High. Low, Close", vbExclamation, _
"Invalid selection"
Exit Sub
End If

Set rData = Selection
If rData.Columns.Count < 5 Then Set rData = rData.CurrentRegion

Set rXVals = rData.Columns(1)
Set rHLC = Union(rXVals, rData.Columns(3).Resize(, 3))
Set rOpen = rData.Columns(2)

Set cHLCO = ActiveSheet.ChartObjects.Add(100, 100, 375, 225).Chart
With cHLCO
.SetSourceData Source:=rHLC, PlotBy:=xlColumns
.ChartType = xlStockHLC
iRows = rOpen.Rows.Count
iPts = .SeriesCollection(1).Points.Count
If iRows iPts Then
Set rOpen = rOpen.Offset(1).Resize(iPts)
Set rXVals = rXVals.Offset(1).Resize(iPts)
End If
Set rOpen = rOpen.Resize(.SeriesCollection(1).Points.Count)
With .SeriesCollection.NewSeries
.Values = rOpen
.ChartType = xlXYScatterLinesNoMarkers
.XValues = rXVals
.Name = "Open"
End With
End With
End Sub

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______


Shi wrote:

By the way, I forgot to mention, after I changed the Date
format to a "General" or "Numeric" format, the original
macro was stopped at line: .ChartType = xlStockHLC

What should I do next?



-----Original Message-----
Jon,

Here were the real data I run your macro, I was still
unable to get HLC chart with an Open line.

Date,Open,High,Low,Close
38174,28.32,28.33,27.94,28.02
38170,28.62,28.68,28.40,28.57
38169,28.70,28.84,28.26,28.63
38168,28.57,28.80,28.39,28.56
38167,28.18,28.58,28.18,28.50
38166,28.60,28.75,28.17,28.28
38163,28.48,28.63,28.25,28.57
38162,28.48,28.65,28.36,28.39
38161,28.20,28.38,28.00,28.30
38160,28.15,28.35,27.81,28.29
38159,28.22,28.66,28.12,28.35
38156,27.77,28.50,27.70,28.35
38155,27.31,27.92,27.29,27.77
38154,27.34,27.50,27.15,27.32
38153,26.99,27.60,26.97,27.41
38152,26.55,26.90,26.53,26.90
38148,26.38,26.79,26.38,26.77
38147,26.40,26.65,26.40,26.47
38146,26.28,26.65,26.24,26.60

I had Date-Open-High-Low-Close at L-M-N-O-P columns.
What I did wrong? I have no idea at all.

Please help,





-----Original Message-----
Jon,

Thanks for your time. How do you configure the Date as

the

Real Numerical Date?



-----Original Message-----
Shi -

If your dates are true numerical dates, and you make a

stock chart,

Excel will make a time scale axis. This works fine for

both the XY

series (Open Line) and the stock chart series.

If you make the X axis into a category type, the Open

Line XY series

still uses the dates as input. Your categories for the

stock data will

be numbers like 1, 2, 3, etc., while the X data for the

Open Line series

will be numbers in the 39 thousands, which is what

dates

look like. The

two series will not want to line up in this case.

Follow the instructions about the data arrangement
(Date-Open-High-Low-Close), and the date configuration

(real numerical

dates), then try again.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Shi wrote:


Jon,

Thanks a lot for your great solution. I almost got

the

final result by running your macro. (Maybe I missed

one

or

two steps somewhere???)

So far, I got HLC chart, but without OpenLine. I

think

this was due to my chart was not in a CategoryType.

How

do

you add these lines inside your macro?

With ActiveChart
.HasAxis(xlCategory, xlPrimary) = True
.HasAxis(xlValue, xlPrimary) = True
End With
ActiveChart.Axes(xlCategory, xlPrimary).CategoryType

=

xlCategoryScale


Thanks for your advice,


.


.


.

Jon,

I just saw your new post, it works like wonder.
Thank you for being here to help me.

You are really a good man.



-----Original Message-----
In my sample table, I had no header labels. The code
below adjusts for
this. You just need to format your dates as any date
format.

Sub StockHLC_OpenLine()
Dim rData As Range
Dim rHLC As Range
Dim rOpen As Range
Dim rXVals As Range
Dim cHLCO As Chart
Dim iPts As Integer
Dim iRows As Integer

If TypeName(Selection) < "Range" Then
MsgBox "Select a range of cells containing
data," & vbCrLf _
& "in order: Date, Open, High. Low, Close",
vbExclamation, _
"Invalid selection"
Exit Sub
End If

Set rData = Selection
If rData.Columns.Count < 5 Then Set rData =
rData.CurrentRegion

Set rXVals = rData.Columns(1)
Set rHLC = Union(rXVals, rData.Columns(3).Resize(,
3))
Set rOpen = rData.Columns(2)

Set cHLCO = ActiveSheet.ChartObjects.Add(100, 100,
375, 225).Chart
With cHLCO
.SetSourceData Source:=rHLC, PlotBy:=xlColumns
.ChartType = xlStockHLC
iRows = rOpen.Rows.Count
iPts = .SeriesCollection(1).Points.Count
If iRows iPts Then
Set rOpen = rOpen.Offset(1).Resize(iPts)
Set rXVals = rXVals.Offset(1).Resize(iPts)
End If
Set rOpen = rOpen.Resize(.SeriesCollection
(1).Points.Count)
With .SeriesCollection.NewSeries
.Values = rOpen
.ChartType = xlXYScatterLinesNoMarkers
.XValues = rXVals
.Name = "Open"
End With
End With
End Sub

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______


Shi wrote:

By the way, I forgot to mention, after I changed the
Date
format to a "General" or "Numeric" format, the original
macro was stopped at line: .ChartType = xlStockHLC

What should I do next?



-----Original Message-----
Jon,

Here were the real data I run your macro, I was still
unable to get HLC chart with an Open line.

Date,Open,High,Low,Close
38174,28.32,28.33,27.94,28.02
38170,28.62,28.68,28.40,28.57
38169,28.70,28.84,28.26,28.63
38168,28.57,28.80,28.39,28.56
38167,28.18,28.58,28.18,28.50
38166,28.60,28.75,28.17,28.28
38163,28.48,28.63,28.25,28.57
38162,28.48,28.65,28.36,28.39
38161,28.20,28.38,28.00,28.30
38160,28.15,28.35,27.81,28.29
38159,28.22,28.66,28.12,28.35
38156,27.77,28.50,27.70,28.35
38155,27.31,27.92,27.29,27.77
38154,27.34,27.50,27.15,27.32
38153,26.99,27.60,26.97,27.41
38152,26.55,26.90,26.53,26.90
38148,26.38,26.79,26.38,26.77
38147,26.40,26.65,26.40,26.47
38146,26.28,26.65,26.24,26.60

I had Date-Open-High-Low-Close at L-M-N-O-P columns.
What I did wrong? I have no idea at all.

Please help,





-----Original Message-----
Jon,

Thanks for your time. How do you configure the Date as

the

Real Numerical Date?



-----Original Message-----
Shi -

If your dates are true numerical dates, and you make
a

stock chart,

Excel will make a time scale axis. This works fine
for

both the XY

series (Open Line) and the stock chart series.

If you make the X axis into a category type, the Open

Line XY series

still uses the dates as input. Your categories for
the

stock data will

be numbers like 1, 2, 3, etc., while the X data for
the

Open Line series

will be numbers in the 39 thousands, which is what

dates

look like. The

two series will not want to line up in this case.

Follow the instructions about the data arrangement
(Date-Open-High-Low-Close), and the date
configuration

(real numerical

dates), then try again.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Shi wrote:


Jon,

Thanks a lot for your great solution. I almost got

the

final result by running your macro. (Maybe I missed

one

or

two steps somewhere???)

So far, I got HLC chart, but without OpenLine. I

think

this was due to my chart was not in a CategoryType.

How

do

you add these lines inside your macro?

With ActiveChart
.HasAxis(xlCategory, xlPrimary) = True
.HasAxis(xlValue, xlPrimary) = True
End With
ActiveChart.Axes(xlCategory, xlPrimary).CategoryType

=

xlCategoryScale


Thanks for your advice,


.


.


.


.

Shi -

Glad to help.

- Jon

Shi wrote:
Jon,

I just saw your new post, it works like wonder.
Thank you for being here to help me.

You are really a good man.




-----Original Message-----
In my sample table, I had no header labels. The code

below adjusts for

this. You just need to format your dates as any date

format.

Sub StockHLC_OpenLine()
Dim rData As Range
Dim rHLC As Range
Dim rOpen As Range
Dim rXVals As Range
Dim cHLCO As Chart
Dim iPts As Integer
Dim iRows As Integer

If TypeName(Selection) < "Range" Then
MsgBox "Select a range of cells containing

data," & vbCrLf _

& "in order: Date, Open, High. Low, Close",

vbExclamation, _

"Invalid selection"
Exit Sub
End If

Set rData = Selection
If rData.Columns.Count < 5 Then Set rData =

rData.CurrentRegion

Set rXVals = rData.Columns(1)
Set rHLC = Union(rXVals, rData.Columns(3).Resize(,

3))

Set rOpen = rData.Columns(2)

Set cHLCO = ActiveSheet.ChartObjects.Add(100, 100,

375, 225).Chart

With cHLCO
.SetSourceData Source:=rHLC, PlotBy:=xlColumns
.ChartType = xlStockHLC
iRows = rOpen.Rows.Count
iPts = .SeriesCollection(1).Points.Count
If iRows iPts Then
Set rOpen = rOpen.Offset(1).Resize(iPts)
Set rXVals = rXVals.Offset(1).Resize(iPts)
End If
Set rOpen = rOpen.Resize(.SeriesCollection

(1).Points.Count)

With .SeriesCollection.NewSeries
.Values = rOpen
.ChartType = xlXYScatterLinesNoMarkers
.XValues = rXVals
.Name = "Open"
End With
End With
End Sub

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______


Shi wrote:


By the way, I forgot to mention, after I changed the

Date

format to a "General" or "Numeric" format, the original
macro was stopped at line: .ChartType = xlStockHLC

What should I do next?




-----Original Message-----
Jon,

Here were the real data I run your macro, I was still
unable to get HLC chart with an Open line.

Date,Open,High,Low,Close
38174,28.32,28.33,27.94,28.02
38170,28.62,28.68,28.40,28.57
38169,28.70,28.84,28.26,28.63
38168,28.57,28.80,28.39,28.56
38167,28.18,28.58,28.18,28.50
38166,28.60,28.75,28.17,28.28
38163,28.48,28.63,28.25,28.57
38162,28.48,28.65,28.36,28.39
38161,28.20,28.38,28.00,28.30
38160,28.15,28.35,27.81,28.29
38159,28.22,28.66,28.12,28.35
38156,27.77,28.50,27.70,28.35
38155,27.31,27.92,27.29,27.77
38154,27.34,27.50,27.15,27.32
38153,26.99,27.60,26.97,27.41
38152,26.55,26.90,26.53,26.90
38148,26.38,26.79,26.38,26.77
38147,26.40,26.65,26.40,26.47
38146,26.28,26.65,26.24,26.60

I had Date-Open-High-Low-Close at L-M-N-O-P columns.
What I did wrong? I have no idea at all.

Please help,






-----Original Message-----
Jon,

Thanks for your time. How do you configure the Date as

the


Real Numerical Date?




-----Original Message-----
Shi -

If your dates are true numerical dates, and you make

a

stock chart,


Excel will make a time scale axis. This works fine

for

both the XY


series (Open Line) and the stock chart series.

If you make the X axis into a category type, the Open

Line XY series


still uses the dates as input. Your categories for

the

stock data will


be numbers like 1, 2, 3, etc., while the X data for

the

Open Line series


will be numbers in the 39 thousands, which is what

dates


look like. The


two series will not want to line up in this case.

Follow the instructions about the data arrangement
(Date-Open-High-Low-Close), and the date

configuration

(real numerical


dates), then try again.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Shi wrote:



Jon,

Thanks a lot for your great solution. I almost got

the


final result by running your macro. (Maybe I missed

one


or


two steps somewhere???)

So far, I got HLC chart, but without OpenLine. I

think


this was due to my chart was not in a CategoryType.

How


do


you add these lines inside your macro?

With ActiveChart
.HasAxis(xlCategory, xlPrimary) = True
.HasAxis(xlValue, xlPrimary) = True
End With
ActiveChart.Axes(xlCategory, xlPrimary).CategoryType

=


xlCategoryScale


Thanks for your advice,



.


.


.


.