Stock Chart combined Dow Jones Stock Chart ?

Hi beautiful people,
1- Open enclosed file.
2- Run Macro1.
3- After running Macro1 you will see that there is a chart on the Sheet1. (This chart is a combined chart comparing YAHOO INC. and DOW JONES.)
4- Be aware that YAHOO.INC is a stock chart but DOW JONES is a line chart in this chart.

Question: How can I change DOW JONES chart from line chart to the stock chart ?
So, I want YAHOO INC. stock chart vs DOW JONES stock chart.

Thanks in advance.

Sub Test()
    
    With ActiveSheet.ChartObjects.Add(Left:=0, Width:=1200, Top:=45, Height:=500)
        With .Chart
            ' dummy data to allow chart construction
            .SetSourceData Source:=Range("XEZ1:XFD241")
            .ChartType = xlStockOHLC
            
            ' remove data
            Do While .SeriesCollection.Count > 0
                .SeriesCollection(1).Delete
            Loop
    
            With .SeriesCollection.NewSeries
                .Name = "=""OPEN"""
                .Values = Range("B3:IG3")
                .XValues = Range("B1:IG1")
            End With
            With .SeriesCollection.NewSeries
                .Name = "=""HIGH"""
                .Values = Range("IH3:RM3")
                .XValues = Range("IH1:RM1")
            End With
            With .SeriesCollection.NewSeries
                .Name = "=""LOW"""
                .Values = Range("RN3:AAS3")
                .XValues = Range("RN1:AAS1")
            End With
            With .SeriesCollection.NewSeries
                .Name = "=""CLOSE"""
                .Values = Range("AAT3:AJY3")
                .XValues = Range("AAT1:AJY1")
            End With
    
            With .SeriesCollection.NewSeries
                .Name = "=""DOWJONES-OPEN"""
                .Values = Range("B2:IG2")
                .XValues = Range("B1:IG1")
                .AxisGroup = 2
            End With
            With .SeriesCollection.NewSeries
                .Name = "=""DOWJONES-HIGH"""
                .Values = Range("IH2:RM2")
                .XValues = Range("IH1:RM1")
                .AxisGroup = 2
            End With
            With .SeriesCollection.NewSeries
                .Name = "=""DOWJONES-LOW"""
                .Values = Range("RN2:AAS2")
                .XValues = Range("RN1:AAS1")
                .AxisGroup = 2
            End With
            With .SeriesCollection.NewSeries
                .Name = "=""DOWJONES-CLOSE"""
                .Values = Range("AAT2:AJY2")
                .XValues = Range("AAT1:AJY1")
                .AxisGroup = 2
            End With
            
            ' make secondary axis series stock ochl
            With .ChartGroups(2)
                .HasDropLines = False
                .HasHiLoLines = True
                .HasUpDownBars = True
                .GapWidth = 150
                .VaryByCategories = False
            End With
            .Legend.Delete
            .Axes(xlCategory).Crosses = xlMaximum
        End With
    End With
End Sub

Hi Andy,
I know that issue was very simple for you
Lets make it more complicated.
I want same thing but this time xlStockVOHLC instead of xlStockOHLC. (That means volume integrated stock chart)

Please check enclosed file...

Note1: I want only YAHOO.INC volume. I dont want DOW JONES volume. Because of one on the top of the other issue.
Note2: I want to see volume values on the right axis of the chart.

Very thanks in advance.

Can you not just set the volume series for DOW Jones to an empty row?

        With .SeriesCollection.NewSeries
            .Name = "=""DOWJONES-VOLUME"""
            .Values = Range("B4:IG4")
            .XValues = Range("B1:IG1")
            '.Format.Fill.Visible = msoFalse
            .AxisGroup = 2
        End With

Hi Andy,
Nice to see you again.
You mean I should replace this;

        With .SeriesCollection.NewSeries
            .Name = "=""DOWJONES-VOLUME"""
            .Values = Range("B2:IG2")
            .XValues = Range("B1:IG1")
            '.Format.Fill.Visible = msoFalse
            .AxisGroup = 2
        End With

to this ?

        With .SeriesCollection.NewSeries
            .Name = "=""DOWJONES-VOLUME"""
            .Values = Range("B4:IG4")
            .XValues = Range("B1:IG1")
            '.Format.Fill.Visible = msoFalse
            .AxisGroup = 2
        End With

The only bit that need changes is

            .Values = Range("B4:IG4")

the other lines of code where to give the location within the total code.
Bascially you are using a blank row for volume data which would cause the volume column to be zero.

I changed the code.
Runned Macro named Test.
You can see the result in the enclosed file. (You will see that result not proper chart.)

Does not look like it is possible then.

Although the chart type is set to xlStockVOHLC it does not stay that type. Once you have 5 series of data it will not allow you to use the secondary axis correctly.

All right.
How about making 3-D chart?
http://msdn.microsoft.com/en-us/libr...ffice.15).aspx

How would a 3d chart help with secondary axis use when that page states, "3-D charts have only one axis group"?

Originally Posted by Andy Pope

How would a 3d chart help with secondary axis use when that page states, "3-D charts have only one axis group"?

I dont know...
That was such a bad idea

Andy,
I want to recall my request again in case of misunderstanding.
Yahoo Inc. stock chart will be xlStockVOHLC.
DowJones stock chart will be xlStockOHLC.
Just reminder.

Yes I know. If you use xlStockVOHLC then the secondary axis stuff does not work.
If you use xlStockOHLC then you don't get any volume data on either axis.

Andy,
Do you think I give up solving this issue and stop pushing you?
Of course not.

Here is an idea:
We have already used primary and secondary axis. But how about using tertiary axis?
Check this link: http://peltiertech.com/Excel/Charts/TertiaryAxis.html

That does not help either.
There is no builtin 3rd axis. You create the 3rd axis by rescaling your data and drawing the axis your self using xy-scatter series and data labels.

You can attempt to draw the components of the chart yourself using column, stacked column, error bars and a faked axis.

You mean there is a way to solve this, but that way is not macro way?

Forget macro for a moment.

It is possible to create combination charts which use different chart types. Such as column and xy-scatter.
I'm not sure if it's possible but you could try building a chart with series to hold
volume column chart
open/close stacked column chart, including stack to make it 'float'.
xy-scatter for high/low points with error bars to connect lines to open/close column

Even if you could get all those together on 1 chart you would need to rescale the data for at least 1 set of data to fit on alternative axis.

Then if the chart is creatable you can think about vba code to automate it.

I have just learned an English idiom:
Easier said than done
http://idioms.thefreedictionary.com/...said+than+done