Hello, Frequently when I optimize a trading system I want to chart the results (average return/median quarterly return, etc.). Charting 2 variable data is stupidly easy using excel, but how do I chart 3 variable data? I've done some creative printing on transparencies which overlap but how about some sharp vector graphics or something? Has anyone done this?
Are you wanting to create a surface chart? You cannot plot three variable data in Excel except as 'slices'. You can create a 3-d image of two variable data by creating a block of several columns of data and keep the upper left-hand cell of the block empty. The top row should contain your x values and the left-most column your y values. Remember upper-left corner cell is blank. Fill in the rest of the cells with the results of your x-y pairs. For 3 variable data the best you can do is follow the above procedure for each value of your z data. That is for each z variable make a matrix of the associated x and y values and create charts for each of them to see what your data looks like at each z variable data point.
you can animate something like that too using a little vba that dynamically changes the source data in a 2 series chart. i don't have it any more or i would attach it, but i made a sheet once when i first started playing with TA that animated a cross section of MAs of 50 different lengths, stepping through a series of random data. i don't see why you couldn't whip something up that does the same for a matrix of 3 variable data.
You can use a bubble chart (which is designed to plot 3 variables - the usual X and Y and the third value is used to size the bubble). A 3-D scatter chart would also work - unfortunately, I don't think Excel has a 3D scatter option.
I've tried the bubble chart in excel, but can't get it to work. Maybe I'm not feeding the data in properly. How would I do it given values belonging to unique x,y,z coordinates. Is there a way to use multiple standard sheets (each 2 variable) in a workbook to input data as the third variable. It seems that a 3d spreadsheet doesn't exist, and it's needed to chart 3 variables..
Now that I know what you want...here is something you can use, it's free and works with Excel. http://www.povray.org/
On a bubble chart, the bubble is plotted like a scatter diagram using X and Y and the size of the bubble is derived from the third variable. Use the Excel chart wizard to initially configure it. We used to even do 4D bubble charts using color to encode the 4th value. Bubbles work if you have two variables that can be representative of X/Y coordinates (as in a scatter) and a third parameter that is value oriented. If you actually have three values that are representative of XYZ spatial coordinates, then you'd need to use a 3D scatter diagram. If you told us the specific kind of data you're trying to chart instead of generalities, it would be easier to give you a more specific suggestion.
1st parameter: number of portfolio "slices" 2nd parameter: recommendation score 3rd parameter: hold time
..for every combination of those 3 parameters there is then an average quarterly return which >could< be the size of the bubble, if oriented in 3 dimensions and if I could get it to work