PDA

View Full Version : Z Axis of 3D Graph



aztariq
03-13-2013, 05:31 AM
I currently have many rows of data which refer to months of the year. In all, I have 27 lines which the graph builds on a 3D graph. My problem is it makes the 27 lines one right after the other. I'd like to limit the Z axis or depth of the graph to only 12 points (like 12 months in a year) on the Z axis. Is there a way to do this? I can't seem to find an answer anywhere.

I want the data to be on top of each other after a certain series is finished. In other words, if I have 9 rows on each of the 3 sheets that graph data is pulled from, I want the next sheet's 9 rows to be within the same Z axis. I hope this isn't confusing the way I'm saying it.

SamT
03-13-2013, 08:54 AM
aztariq,

Welcome to VBAX, the best VBA help on the web.

I think you will need to upload your workbook for us to see what the problem is. You may need to post a couple of replys to this message before you can do that.

Be sure to remove any data or sheets that are not germane to the problem.

Be sure to change or remove any personal, private, or proprietary information that may be in the workbook.

aztariq
03-15-2013, 09:55 AM
I cannot upload the workbook, there is proprietary information throughout. However, I can post you what I'm passing to the graph.

Sheets("Graph").Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Sheets("Cmd1Vals").UsedRange
ActiveChart.ChartType = xl3DLine
ActiveChart.PlotBy = xlRows
For i = 1 To numRows
ActiveChart.SeriesCollection(i).Interior.ColorIndex = 3
ActiveChart.SeriesCollection(i).Name = i
Next
With ActiveChart.Axes(xlCategory)
.HasTitle = True
.AxisTitle.Caption = "3D Surface Graph"
End With
With ActiveChart.Axes(xlValue)
.HasTitle = True
.AxisTitle.Caption = "Volatility"
End With
With ActiveChart.Axes(xlSeries)
.HasTitle = True
.AxisTitle.Caption = "Months"
End With
Dim serNum2
ActiveChart.SeriesCollection.Add _
Source:=Sheets("Cmd2Vals").UsedRange
serNum2 = ActiveChart.SeriesCollection.Count
For i = (numRows) To serNum2
ActiveChart.SeriesCollection(i).Interior.ColorIndex = 5
ActiveChart.SeriesCollection(i).Name = i
Next

ActiveChart.SeriesCollection.Add _
Source:=Sheets("Cmd3Vals").UsedRange
Dim serNum3
serNum3 = ActiveChart.SeriesCollection.Count
serNum2 = serNum2 + 1
For i = serNum2 To serNum3
ActiveChart.SeriesCollection(i).Interior.ColorIndex = 8
ActiveChart.SeriesCollection(i).Name = i
Next

SamT
03-15-2013, 05:27 PM
I am just putting VBA tags around your code to make it easier to understand. You can do the same by selecting the code in the edit box and clicking the VBA icon.
Sheets("Graph").Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Sheets("Cmd1Vals").UsedRange
ActiveChart.ChartType = xl3DLine
ActiveChart.PlotBy = xlRows
For i = 1 To numRows
ActiveChart.SeriesCollection(i).Interior.ColorIndex = 3
ActiveChart.SeriesCollection(i).Name = i
Next
With ActiveChart.Axes(xlCategory)
.HasTitle = True
.AxisTitle.Caption = "3D Surface Graph"
End With
With ActiveChart.Axes(xlValue)
.HasTitle = True
.AxisTitle.Caption = "Volatility"
End With
With ActiveChart.Axes(xlSeries)
.HasTitle = True
.AxisTitle.Caption = "Months"
End With
Dim serNum2
ActiveChart.SeriesCollection.Add _
Source:=Sheets("Cmd2Vals").UsedRange
serNum2 = ActiveChart.SeriesCollection.Count
For i = (numRows) To serNum2
ActiveChart.SeriesCollection(i).Interior.ColorIndex = 5
ActiveChart.SeriesCollection(i).Name = i
Next

ActiveChart.SeriesCollection.Add _
Source:=Sheets("Cmd3Vals").UsedRange
Dim serNum3
serNum3 = ActiveChart.SeriesCollection.Count
serNum2 = serNum2 + 1
For i = serNum2 To serNum3
ActiveChart.SeriesCollection(i).Interior.ColorIndex = 8
ActiveChart.SeriesCollection(i).Name = i
Next

aztariq
03-15-2013, 05:30 PM
Okay, thanks. Sorry for my unfamiliarity with the process of posting on the forum. This must be a good one because I'm getting replies back very quickly! I posted to Microsoft's and another and this was almost a week ago now, still no reply.

Thanks again!

SamT
03-15-2013, 06:16 PM
In order to help me understand your code, I cleaned it up and reformatted it. I think you may need to start by setting each Series object to xl3dLine. I am still looking at it though.

Sheets("Graph").Shapes.AddChart.Select
With ActiveChart 'Entire code uses ActiveChart
.SetSourceData Source:=Sheets("Cmd1Vals").UsedRange
.ChartType = xl3DLine
.PlotBy = xlRows


''''First set of Series
For i = 1 To numRows
With .SeriesCollection(i)
.Interior.ColorIndex = 3
.Name = i
End With
Next i

''''Set chart Axis Values
With .Axes(xlCategory)
.HasTitle = True
.AxisTitle.Caption = "3D Surface Graph"
End With

With .Axes(xlValue)
.HasTitle = True
.AxisTitle.Caption = "Volatility"
End With

With .Axes(xlSeries)
.HasTitle = True
.AxisTitle.Caption = "Months"
End With

''''Add Second set of Series
Dim serNum2 As Long
.SeriesCollection.Add Source:=Sheets("Cmd2Vals").UsedRange
serNum2 = .SeriesCollection.Count
For i = (numRows) To serNum2
With .SeriesCollection(i)
.Interior.ColorIndex = 5
.Name = i
End With
Next

''''Add Last Set Of Series
.SeriesCollection.Add Source:=Sheets("Cmd3Vals").UsedRange
Dim serNum3 As Long
serNum3 = .SeriesCollection.Count
serNum2 = serNum2 + 1
For i = serNum2 To serNum3
With .SeriesCollection(i)
.Interior.ColorIndex = 8
.Name = i
End With
Next

End With 'ActiveChart

SamT
03-15-2013, 08:49 PM
I have looked around and all I can figure out is that real 3d charts in excel are very difficult.:dunno

I am sorry, but I must bow out and let someone with charting experience help you.:friends:

sassora
03-16-2013, 06:26 AM
Hi aztariq, why don't you post a sample that includes no proprietary information?

SamT
03-16-2013, 01:32 PM
sassora,

Thanks. Can you take over, I'm out of my league.

SamT

aztariq
03-18-2013, 05:49 AM
What would you like me to post/show?

sassora
03-18-2013, 02:22 PM
How about the 3D graph with the sample data. This will show your current situation. It may help someone get some additional insight what needs to be done.

aztariq
03-18-2013, 08:57 PM
I just went ahead and posted the workbook

Jan Karel Pieterse
03-19-2013, 12:30 AM
Hi Aztariq,

I'm wondering, why do you want to display this data in a 3D chart?

3D charts are generally speaking quite hard to interpret. There are more useful alternatives which can show your data properly without distortion.

snb
03-19-2013, 05:30 AM
Are you looking for ?

aztariq
03-19-2013, 08:53 AM
snb: The months, yes, it needs to be 12, but I need more than 12 series of data plotted within the 12 month z-axis. I colored them differently to differentiate.

Jan Karel Pieterse: Yes, it is a duplication of a feature already used in another piece of software. However, in this other software, it does not give you the option to graph 3 different collections of data. So yes, it needs to be in 3D, just as the other one is. They need to see the surface of the graph.

Jan Karel Pieterse
03-21-2013, 12:52 AM
I'm stubborn :-)
I would still advise to look for a different way to display this data. Chart multiples are probably a good bet.
3D charting is highly overrated.

Jan Karel Pieterse
03-21-2013, 01:08 AM
Not sure why you insist on a 3D chart. They do hide a lot of information and are hard to interpret.

Consider something like the attached perhaps?

aztariq
03-21-2013, 05:32 AM
Jan Karel: I think maybe you're not understanding what I'm saying. Think mathematically for a second. In the file I sent, there are 27 series of data being graphed, 27 lines. The X Axis is defined by Excel, the Y Axis is kind of gauged to the values, and the Z Axis is defined by how many series there are. There are 27 series, so Z Axis is 27 points long.

Now here's what I want. I want the Z Axis to be limited to 12 points, instead of being lengthened to the amount of data series I have. Then, I want all 27 series to be graphed WITHIN the 12 points. Do you see what I'm getting at? If not, look at my original upload of the workbook. If you notice, there are 3 different colored sets. That's because those are 3 separate entities I want graphed. I want them graphed all within 12 months (Z Axis).

Does it make sense now?

Jan Karel Pieterse
03-21-2013, 06:42 AM
I do think I get what you need. I just find the 3D representation of the data rather pointless.
I'll just leave it at that :-)

aztariq
03-21-2013, 08:32 AM
Yeah, I should have clarified on that. So the reason they want it in 3D is because they need to see the surface of the data beside each other. With 2D, you can't really do that. They need to see the 3D Surface of it all and make decisions based on that.
They're using this in options trading on futures contracts. It's kind of like stock market trading, don't know if you're familiar with commodities markets.

Jan Karel Pieterse
03-21-2013, 08:39 AM
No, not really. Oh well, I hope someone can help you with this, I'm no charting wiz. :-)

sassora
03-24-2013, 11:16 AM
Hi

I agree with Jan that you should try to use a 2D chart; 3D charts can be misleading and they aren't easy to read. Also, showing less data on a single chart is often more helpful than trying to show everything.

I rewrote the code you had in your spreadsheet by following it closely, with the exception of two things 1) chart data is put into one sheet rather than in separate sheets 2) the chart is in 2D (I made a guess at what you were trying to show). There were things I brought forward like named ranges and arrays which I left in, in case your were going to use them for another reason other than chart creation.

In the attached I changed the sddev data as each sheet was the same, meaning that the chart lines would all overlap.

Hope you find this useful.


Sub CommoditiesChart()

Dim ws As Worksheet
Dim numColumns As Long
Dim numRows As Long

Dim shtCnt As Integer
Dim rowCnt As Long
Dim colCnt As Long

Dim stdDev_rngStr As String
Dim stdDev_rng As Range
Dim numWorkingcol As Long
Dim numWorkingrow As Long

Dim vol_rng As Range
Dim volValues() As Double
Dim chartValues() As Double


Call TidySheets

shtCnt = 0
For Each ws In ActiveWorkbook.Worksheets
If ws.Name = "Graph" Then Exit For

shtCnt = shtCnt + 1
numColumns = ws.Range("A1").CurrentRegion.Columns.Count
numRows = ws.Range("A1").CurrentRegion.Rows.Count

If ws.UsedRange.Address <> "$A$1" Then

stdDev_rngStr = FindNumericAddress(ws, numRows, numColumns)

Set stdDev_rng = ws.Range(stdDev_rngStr)
stdDev_rng.Name = "Range" & shtCnt

numWorkingrow = stdDev_rng.Rows.Count
numWorkingcol = stdDev_rng.Columns.Count

Set vol_rng = ws.Cells(stdDev_rng.Row, 3).Resize(numWorkingrow, 1)
vol_rng.Name = "VolSection" & shtCnt

'Calculate chart values and put in worksheet
ReDim Preserve chartValues(numWorkingrow, numWorkingcol), volValues(numWorkingrow)
For rowCnt = 1 To numWorkingrow

volValues(rowCnt) = vol_rng.Cells(rowCnt, 1)

For colCnt = 1 To numWorkingcol

chartValues(rowCnt, colCnt) = _
stdDev_rng.Cells(rowCnt, colCnt) + volValues(rowCnt)

'Populate CmdVals sheet with array values
With Worksheets("CmdVals")
.Cells(1, 2 + numWorkingcol * (shtCnt - 1)) = ws.Name
.Cells(1, colCnt).Offset(1, 1 + numWorkingcol * (shtCnt - 1)) = _
ws.Cells(1, stdDev_rng.Column - 1).Offset(0, colCnt)

.Cells(rowCnt, 1).Offset(2, 0) = rowCnt

.Cells(rowCnt, colCnt).Offset(2, 1 + numWorkingcol * (shtCnt - 1)) = _
chartValues(rowCnt, colCnt)
End With

Next colCnt
Next rowCnt
End If

Next ws

Call ReplaceNA(Worksheets("CmdVals"))
Call Create_chart(numWorkingcol)

End Sub


Sub TidySheets()

Dim ws As Worksheet

If Sheets("Graph").ChartObjects.Count > 0 Then
Sheets("Graph").ChartObjects.Delete
End If

For Each ws In ActiveWorkbook.Worksheets
If ws.Name = "CmdVals" Then
ws.Cells.Clear
End If
Next ws

End Sub

Function FindNumericAddress(ws As Worksheet, numRows As Long, numColumns As Long) As String

Dim colCnt As Long

Dim stdDev_ColStart As Integer
Dim stdDev_ColEnd As Integer

Dim stdDev_startCell As String
Dim stdDev_endCell As String

Dim stdDev_rngStr As String
Dim stdDev_rng As Range

'Find the standard deviation starting column
For colCnt = 1 To numColumns
If Val(ws.Cells(1, colCnt)) <> 0 And IsNumeric(Val(ws.Cells(1, colCnt))) Then
stdDev_ColStart = colCnt
Exit For
End If
Next colCnt

'Find the standard deviation ending column
For colCnt = numColumns To 1 Step -1
If Val(ws.Cells(1, colCnt)) <> 0 And IsNumeric(Val(ws.Cells(1, colCnt))) Then
stdDev_ColEnd = colCnt
Exit For
End If
Next colCnt

'Std dev range, start and end cells
stdDev_startCell = ws.Cells(2, stdDev_ColStart).Address
stdDev_endCell = ws.Cells(numRows, stdDev_ColEnd).Address

FindNumericAddress = ws.Name & "!" & stdDev_startCell & ":" & stdDev_endCell

End Function


Sub ReplaceNA(ByRef ws As Worksheet)

Application.DisplayAlerts = False
ws.Cells.Replace "#N/A", "", xlWhole
Application.DisplayAlerts = True

End Sub

Sub Create_chart(numWorkingcol As Long)

Dim colCnt As Long

Sheets("Graph").Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets("CmdVals").UsedRange
ActiveChart.PlotBy = xlColumns

ActiveChart.SetElement (msoElementChartTitleAboveChart)
ActiveChart.ChartTitle.Text = "Volatility over time"
ActiveChart.Axes(xlCategory).HasTitle = True
ActiveChart.Axes(xlCategory).AxisTitle.Caption = "Months"
ActiveChart.Axes(xlValue).HasTitle = True
ActiveChart.Axes(xlValue).AxisTitle.Caption = "Volatility"

'Resize
ActiveSheet.Shapes(ActiveChart.Parent.Name).ScaleWidth 3, msoFalse, msoScaleFromMiddle
ActiveSheet.Shapes(ActiveChart.Parent.Name).ScaleHeight 3, msoFalse, msoScaleFromMiddle


For colCnt = 1 To Sheets("CmdVals").UsedRange.Columns.Count - 1

With ActiveChart.SeriesCollection(colCnt)

Select Case (colCnt - 1) Mod 3 + 1
Case 1
.MarkerStyle = xlMarkerStyleTriangle
.MarkerSize = 6

Case 2
.MarkerStyle = xlMarkerStyleCircle
.MarkerSize = 5

Case 3
.MarkerStyle = xlMarkerStyleNone
End Select


Select Case (Application.Floor(colCnt - 1, 3) / 3) Mod 2 + 1
Case 1
.Format.Fill.ForeColor.ObjectThemeColor = _
Application.Floor(colCnt - 1, numWorkingcol) / numWorkingcol + 5
'.Format.Fill.Solid

.Format.Line.DashStyle = msoLineSquareDot
.Format.Line.ForeColor.ObjectThemeColor = _
Application.Floor(colCnt - 1, numWorkingcol) / numWorkingcol + 5

Case 2
.Format.Fill.ForeColor.ObjectThemeColor = _
Application.Floor(colCnt - 1, numWorkingcol) / numWorkingcol + 5
'.Format.Fill.Patterned msoPatternDarkDownwardDiagonal

'.Format.Line.DashStyle = msoLineDash
.Format.Line.ForeColor.ObjectThemeColor = _
Application.Floor(colCnt - 1, numWorkingcol) / numWorkingcol + 5
End Select

End With

Next colCnt

End Sub

aztariq
03-24-2013, 11:24 AM
First, I just want to thank you all for your help. This was the only forum to actually reply to the original posting. I will definitely come back if run into another problem in VBA.

Sassora: You do not understand the significance of the 3D graph in this situation. It is mandatory and not an option. For analysis, it is needed, no exceptions. The 2D graph will not suffice because for what they need to analyze, they must see the surface of the graph. I'm sorry for whatever misunderstanding there is, but that is what is needed and a 2D graph for the data is pretty much useless, as it is then only a volatility smile, which is a different and completely generic graph that is readily available to them.

I appreciate the help, and I hope I don't sound mean or arrogant in what I'm saying, it's just what they need and for analysis, the surface must be seen. The months (the different lines, which are diff futures contracts) must be all in one 3D graph.

Thanks again and will let you all know if I need more help. At this point, I think I'm going to see if I can get the data from Excel and stick it in another graphing software.

sassora
03-24-2013, 01:06 PM
I may not understand the significance of the 3D chart in this context other than this is what is being requested. And of course give the customer what they want.

Just as an aside:
Since the z-axis value doesn't vary in a given month, there's no surface to analyse. It's effectively a widened 2D line. With a good choice of line colours and markers it should be possible to view the information in 2D. My attachment showed how that might look: having a different colour for each set of 9 data points, using line markers and line styles to make the information clear. Some work would need to be done on it but in theory, it seems like a justified way forward. Not totally practical? Fine, but it has its merits too.

Good Luck

aztariq
03-24-2013, 01:20 PM
Yes, I see what you're saying. However, the numbers that it's using for those lines will differ greatly when actually used. I used those same values over and over because I didn't feel I needed it to complete the project. When real data for 3 differing commodities are put in, it will have a surface. In fact, each commodity will have its own surface when the left and right slope are put in later.

SamT
03-24-2013, 11:50 PM
aztariq,

I have played around with POV-Ray. It can read text files and Excel can generate txt files.

Anyway POV-Ray is a mathematician's tool. From a set of data points and or formulas, it can generate any shape or shapes you can describe.

I haven't used it in a decade, but I downloaded the latest version a few months ago and read the manual again. I seem to remember a couple of functions that will accomplish what you need.

SamT
03-25-2013, 12:43 AM
Back to VBA and Excel.

Algorythm for 12 datapoint = weighted average of 27 data points.

It may not be the most accurate, but the human eye is the best extrapolater of visual data ever invented.

DpArray(27) = your datapoints
With Column "A"
For n = 1 to 27
Range(An:An+12) = DP(n)
Next n
End With

NewDPArray(12 * 27) = Range(A1:A(12*27))
With Col "B"
For x = 1 to 12
For n = 1 to 27
Range(Bx) = Average(Sum(NewDPArray(x)
Next n
Next x
End With