Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
using macros to chart data
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
using macros to chart data
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. " wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
using macros to chart data
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 Whe=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 Whe=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. On Jul 23, 11:02 am, Joel wrote: 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. " wrote: 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.- Hide quoted text - - Show quoted text - |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
using macros to chart data
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 Whe=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 Whe=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 " wrote: 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 Whe=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 Whe=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. On Jul 23, 11:02 am, Joel wrote: 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. " wrote: 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.- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
using macros to chart data
Thanks for your help on this. I really appreciate it.
On Jul 24, 6:50 am, Joel wrote: 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 Whe=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 Whe=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 "Joel" wrote: 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 Whe=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 Whe=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 " wrote: 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 Whe=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 Whe=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. On Jul 23, 11:02 am, Joel wrote: 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 ... read more »- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can not get Excel macros to save chart format -lost in space! | Charts and Charting in Excel | |||
2007 does not like 2003 chart macros | Charts and Charting in Excel | |||
chart series data corrupted when chart has no data | Excel Programming | |||
Looking for help with macros and chart making. | Excel Programming | |||
Macros to Create Pie Chart | Excel Programming |