using macros to chart data

E

eyelynch

I have a sheet containing over 200 series of data and I want to chart
each one separately while comparing them to a benchmark data series
within another sheet. Is there a way I can run a macro instead of
doing this manually? I tried to construct a macro myself and was only
able to duplicate what was recorded. I know there is a way. Please
help.
 
J

Joel

Post the macro. It can be modified. The recorded macros only refer to the
cells you recorded, but it is easy to xpand the macro for a more general
case. Record a macro for two charts so we can see the diferences and the we
can expand the macro properly to work for all 200 series. Explain how the
200 series are different.
 
E

eyelynch

Here's the Macro

Sub Macro3()
'
' Macro3 Macro
' Macro recorded 7/23/2007 by Tudor
'
' Keyboard Shortcut: Ctrl+u
'
Charts.Add
ActiveChart.ChartType = xlLine
ActiveChart.SetSourceData
Source:=Sheets("Sheet1").Range("B111:B145"), _
PlotBy:=xlColumns
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = "='Japan LongShort
Index'!R3C2:R92C2"
ActiveChart.SeriesCollection(1).Values = "=Sheet1!R110C2:R145C2"
ActiveChart.SeriesCollection(1).Name = "=Sheet1!R1C2"
ActiveChart.SeriesCollection(2).XValues = "='Japan LongShort
Index'!R3C2:R92C2"
ActiveChart.SeriesCollection(2).Values = "='Japan LongShort Index'!
R3C3:R92C3"
ActiveChart.SeriesCollection(2).Name = "=""Japan L/S Index"""
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet2"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "788 Japan Ltd"
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Monthly
Return"
End With
ActiveSheet.Shapes("Chart 1").IncrementLeft -117#
ActiveSheet.Shapes("Chart 1").IncrementTop -93#
ActiveWindow.Visible = False
Windows("Performance data.xls").Activate
Range("C20").Select
Charts.Add
ActiveChart.ChartType = xlLine
ActiveChart.SetSourceData
Source:=Sheets("Sheet1").Range("C110:C145"), _
PlotBy:=xlColumns
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = "='Japan LongShort
Index'!R3C2:R92C2"
ActiveChart.SeriesCollection(1).Values = "=Sheet1!R110C3:R145C3"
ActiveChart.SeriesCollection(1).Name = "=Sheet1!R1C3"
ActiveChart.SeriesCollection(2).XValues = "='Japan LongShort
Index'!R3C2:R92C2"
ActiveChart.SeriesCollection(2).Values = "='Japan LongShort Index'!
R3C3:R92C3"
ActiveChart.SeriesCollection(2).Name = "=""Japan L/S Index"""
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet2"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "A.I. Equity Hedge Fund"
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Monthly
Return"
End With
End Sub



The start date of the data series vary. It's performance numbers. So
they all tend to end around the same date but start at different
dates. The first two in the example above started in January 2005 and
I have others that start in 1996. I have the data arranged in columns
in ascending order from earliest date to latest. The very top column
has the name of the series and below that (depending on the start date
of the series) are the data series.
 
J

Joel

I got the first part owrking minus the .NAME which I commented out. You need
to set your range when you create the chart (I changed the source line).
Then when you add seriescollection you start at an index of 2. One was
already added.

I will try to work on this a little bit more when I have time
Sub Macro3()
'
' Macro3 Macro
' Macro recorded 7/23/2007 by Tudor
'
' Keyboard Shortcut: Ctrl+u
'
Charts.Add
ActiveChart.ChartType = xlLine
ActiveChart.SetSourceData _
Source:=Sheets("Sheet1").Range("B110:C145"), _
PlotBy:=xlColumns
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(2).XValues = _
"='Japan LongShort Index'!R3C2:R92C2"
ActiveChart.SeriesCollection(2).Values = "=Sheet1!R110C2:R145C2"
'ActiveChart.SeriesCollection(2).Name = "=Sheet1!R1C2"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(3).XValues = _
"='Japan LongShort Index'!R3C2:R92C2"
ActiveChart.SeriesCollection(3).Values = _
"='Japan LongShort Index'!R3C3:R92C3"
'ActiveChart.SeriesCollection(3).Name = "=""Japan L/S Index"""
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet2"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "788 Japan Ltd"
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = _
"Monthly Return"
End With
ActiveSheet.Shapes("Chart 1").IncrementLeft -117#
ActiveSheet.Shapes("Chart 1").IncrementTop -93#
'did not get working yet
'---------------------------------------------------------------------------------------------------
ActiveWindow.Visible = False
Windows("Performance data.xls").Activate
Range("C20").Select
Charts.Add
ActiveChart.ChartType = xlLine
ActiveChart.SetSourceData _
Source:=Sheets("Sheet1").Range("C110:C145"), _
PlotBy:=xlColumns
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = _
"='Japan LongShort Index '!R3C2:R92C2"
ActiveChart.SeriesCollection(1).Values = "=Sheet1!R110C3:R145C3"
ActiveChart.SeriesCollection(1).Name = "=Sheet1!R1C3"
ActiveChart.SeriesCollection(2).XValues = _
"='Japan LongShort Index'!R3C2:R92C2"
ActiveChart.SeriesCollection(2).Values = _
"='Japan LongShort Index'!R3C3: R92C3 "
ActiveChart.SeriesCollection(2).Name = "=""Japan L/S Index"""
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet2"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "A.I. Equity Hedge Fund"
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = _
"Monthly Return"
End With
End Sub
 
J

Joel

I don't have tinme to work on this any more. I will advise if you need
additional help. I got the first chart to work with all series colections.
Here are some tricks

1) You can't write the name of the series collection unless there is data in
the chart.
2) You also have to set the series collection to write the name. Not sure
why.
3) You can't add the first series collection. You must set it as part of
the source name. I made the souce name contain two column where you had only
one.

You should be able to turn this code into a loop so it will work with all
200 charts. I have these same problems evvery time I try to automate my
charts. I always takes me hours to get right.



Sub Macro3()
'
' Macro3 Macro
' Macro recorded 7/23/2007 by Tudor
'
' Keyboard Shortcut: Ctrl+u
'
Worksheets("Sheet1").Activate
myseriesname = Worksheets("Sheet1").Cells(1, "B")
Charts.Add
ActiveChart.ChartType = xlLine
ActiveChart.SetSourceData _
Source:=Sheets("Sheet1").Range("B110:C145"), _
PlotBy:=xlColumns

ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(2).XValues = _
"='Japan LongShort Index'!R3C2:R92C2"
ActiveChart.SeriesCollection(2).Values = "=Sheet1!R110C2:R145C2"

Set MySeries = ActiveChart.SeriesCollection(2)
MySeries.Name = myseriesname


ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(3).XValues = _
"='Japan LongShort Index'!R3C2:R92C2"
ActiveChart.SeriesCollection(3).Values = _
"='Japan LongShort Index'!R3C3:R92C3"
Set MySeries = ActiveChart.SeriesCollection(3)
MySeries.Name = "Japan L/S Index"



ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet2"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "788 Japan Ltd"
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = _
"Monthly Return"
End With
ActiveSheet.Shapes("Chart 1").IncrementLeft -117#
ActiveSheet.Shapes("Chart 1").IncrementTop -93#
'did not get working yet
'---------------------------------------------------------------------------------------------------
ActiveWindow.Visible = False
Windows("Performance data.xls").Activate
Range("C20").Select
Charts.Add
ActiveChart.ChartType = xlLine
ActiveChart.SetSourceData _
Source:=Sheets("Sheet1").Range("C110:C145"), _
PlotBy:=xlColumns
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = _
"='Japan LongShort Index '!R3C2:R92C2"
ActiveChart.SeriesCollection(1).Values = "=Sheet1!R110C3:R145C3"
ActiveChart.SeriesCollection(1).Name = "=Sheet1!R1C3"
ActiveChart.SeriesCollection(2).XValues = _
"='Japan LongShort Index'!R3C2:R92C2"
ActiveChart.SeriesCollection(2).Values = _
"='Japan LongShort Index'!R3C3: R92C3 "
ActiveChart.SeriesCollection(2).Name = "=""Japan L/S Index"""
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet2"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "A.I. Equity Hedge Fund"
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = _
"Monthly Return"
End With
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Creating Network Diagram using a Chart SW integration 0
Creating a Chart on form from selected data in query. 0
Setting column width in a table with a macro is not working 0
Automatically selecting active chart 4
Macro to grab data from a chart 0
Help: Chart & data 1
Gantt chart duration not showing 0
Menu Macro Not Executing - Need Help 1

Top