Peter1961
03-07-2021, 06:02 AM
Dear All,
I wish to compare different stock instrument (Instrument1, Instrument2, Instrument3 and so on). Each Instrument has several types of data (Close, MA1, MA2, MA3 and so on).
I created specific names for the Instruments with formulas as:
Instrument1Close:
=OFFSET(Instrumet1Data!$M$103; Instrumet1Data!$I$6;0; Instrumet1Data!$I$5;1)
Instrument1MA1:
=OFFSET(Instrumet1Data!$N$103; Instrumet1Data!$I$6;0; Instrumet1Data!$I$5;1)
Instrument2Close:
=OFFSET(Instrumet1Data!$O$103; Instrumet1Data!$I$6;0; Instrumet1Data!$I$5;1)
Instrument2MA1:
=OFFSET(Instrumet1Data!$P$103; Instrumet1Data!$I$6;0; Instrumet1Data!$I$5;1)
And so on.
I want to see them on a chart. The code for the chart is the following:
The following DO NOT WORK:
.XValues = ThisWorkbook.Names("Date").RefersToRange
.Values = ThisWorkbook.Names(InstrumentName & "Close").RefersToRange
.XValues = ThisWorkbook.Names("Date").RefersToRange
.Values = ThisWorkbook.Names(InstrumentName & "MA1").RefersToRange
The following work:
.XValues = "='" & ThisWorkbook.Name & "'!Date"
.Values = "='" & ThisWorkbook.Name & "'!Instrument1Close"
ALSO, The following DO NOT WORK:
Y1AxisMax = Application.WorksheetFunction.Max(ThisWorkbook.Names(InstrumentName & "Close").RefersToRange)
Y1AxisMin = Application.WorksheetFunction.Min(ThisWorkbook.Names(InstrumentNamee & "Close").RefersToRange)
'nnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnn
' CREATE A CHART AND DELETE SERIE
'nnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnn
i = 1
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Range("'Compare'!$A$103:$B$150")
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveSheet.ChartObjects(i).Name = "Chart" & (i)
ActiveSheet.ChartObjects("Chart" & i).Activate
With ActiveChart
.SeriesCollection(1).Delete
End With
j = 1
'nnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnn
'ADD SERIES TO THE CHART
'nnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnn
InstrumentName= Cells(InstrumentNameRow, InstrumentNameColumn).Value
For i = 1 To InstrumentNumber
'ActiveSheet.ChartObjects("Chart" & i).Activate
With ActiveChart
'j = 1
.SeriesCollection.NewSeries
With .SeriesCollection(j)
.Name = InstrumentName & "Close"
‘.XValues = "='" & ThisWorkbook.Name & "'!Date" ‘This WORK
‘.Values = "='" & ThisWorkbook.Name & "'!Instrument1Close" ‘This WORK
.XValues = ThisWorkbook.Names("Date").RefersToRange ‘Don’t WORK
.Values = ThisWorkbook.Names(InstrumentName & "Close").RefersToRange ‘Don’t WORK
End With
j = j + 1
.SeriesCollection.NewSeries
With .SeriesCollection(j)
.Name = InstrumentumNeve & "CloseSzlkban"
.XValues = ThisWorkbook.Names("Date").RefersToRange
.Values = ThisWorkbook.Names(InstrumentName & "MA1").RefersToRange
End With
'Stop
End With
j = j + 1
k = k + 1
InstrumentNameRow = InstrumentNameRow + 1
InstrumentName= Cells(InstrumentNameRow, InstrumentNameColumn).Value
Next i
Thank you very much in advance for your help!
Peter
I wish to compare different stock instrument (Instrument1, Instrument2, Instrument3 and so on). Each Instrument has several types of data (Close, MA1, MA2, MA3 and so on).
I created specific names for the Instruments with formulas as:
Instrument1Close:
=OFFSET(Instrumet1Data!$M$103; Instrumet1Data!$I$6;0; Instrumet1Data!$I$5;1)
Instrument1MA1:
=OFFSET(Instrumet1Data!$N$103; Instrumet1Data!$I$6;0; Instrumet1Data!$I$5;1)
Instrument2Close:
=OFFSET(Instrumet1Data!$O$103; Instrumet1Data!$I$6;0; Instrumet1Data!$I$5;1)
Instrument2MA1:
=OFFSET(Instrumet1Data!$P$103; Instrumet1Data!$I$6;0; Instrumet1Data!$I$5;1)
And so on.
I want to see them on a chart. The code for the chart is the following:
The following DO NOT WORK:
.XValues = ThisWorkbook.Names("Date").RefersToRange
.Values = ThisWorkbook.Names(InstrumentName & "Close").RefersToRange
.XValues = ThisWorkbook.Names("Date").RefersToRange
.Values = ThisWorkbook.Names(InstrumentName & "MA1").RefersToRange
The following work:
.XValues = "='" & ThisWorkbook.Name & "'!Date"
.Values = "='" & ThisWorkbook.Name & "'!Instrument1Close"
ALSO, The following DO NOT WORK:
Y1AxisMax = Application.WorksheetFunction.Max(ThisWorkbook.Names(InstrumentName & "Close").RefersToRange)
Y1AxisMin = Application.WorksheetFunction.Min(ThisWorkbook.Names(InstrumentNamee & "Close").RefersToRange)
'nnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnn
' CREATE A CHART AND DELETE SERIE
'nnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnn
i = 1
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Range("'Compare'!$A$103:$B$150")
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveSheet.ChartObjects(i).Name = "Chart" & (i)
ActiveSheet.ChartObjects("Chart" & i).Activate
With ActiveChart
.SeriesCollection(1).Delete
End With
j = 1
'nnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnn
'ADD SERIES TO THE CHART
'nnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnn
InstrumentName= Cells(InstrumentNameRow, InstrumentNameColumn).Value
For i = 1 To InstrumentNumber
'ActiveSheet.ChartObjects("Chart" & i).Activate
With ActiveChart
'j = 1
.SeriesCollection.NewSeries
With .SeriesCollection(j)
.Name = InstrumentName & "Close"
‘.XValues = "='" & ThisWorkbook.Name & "'!Date" ‘This WORK
‘.Values = "='" & ThisWorkbook.Name & "'!Instrument1Close" ‘This WORK
.XValues = ThisWorkbook.Names("Date").RefersToRange ‘Don’t WORK
.Values = ThisWorkbook.Names(InstrumentName & "Close").RefersToRange ‘Don’t WORK
End With
j = j + 1
.SeriesCollection.NewSeries
With .SeriesCollection(j)
.Name = InstrumentumNeve & "CloseSzlkban"
.XValues = ThisWorkbook.Names("Date").RefersToRange
.Values = ThisWorkbook.Names(InstrumentName & "MA1").RefersToRange
End With
'Stop
End With
j = j + 1
k = k + 1
InstrumentNameRow = InstrumentNameRow + 1
InstrumentName= Cells(InstrumentNameRow, InstrumentNameColumn).Value
Next i
Thank you very much in advance for your help!
Peter