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,



.

.

.