About Us

Jon Peltier[_7_]

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,

.

.

.

.

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Charting?	[email protected]	Charts and Charting in Excel	2	October 31st 08 03:00 PM
charting add in	[email protected]	Charts and Charting in Excel	3	February 14th 07 03:51 PM
Jon Peltier's code: can't delete initial series??	Ed	Charts and Charting in Excel	2	October 4th 06 03:11 AM
Charting help!	Dezos112	Charts and Charting in Excel	0	April 13th 06 03:54 PM
Custom charting - Stacked charting with a line	Randy Lefferts	Charts and Charting in Excel	3	March 3rd 05 03:10 AM

Menu

About Us