Suppose that you have a nice dataset of trades for a given time period and you wanted to create a stock-chart from a PivotTable data source (a la PivotChart). Sounds simple enough; go through the quick little wizard and selection screen, and BAM!…error message:
You cannot use an XY (Scatter), Bubble, or Stock chart type with a chart that has been created from PivotTable data. Please select a different chart type.
Now, the error message tells us that we can’t explicitly create a scatter or stock chart; so let’s change our chart type to a line graph. Next, we’re going to edit each series and remove the lines from their formatting, and (temporarily) assign them a marker (so we can easily validate our design).
Next, we have to divide our data between the axes. Since we’re going to be creating Hi-Low Lines and Up/Down bars, we need to have our data partitioned off, so that the Hi/Low lines are working on the axis containing MSFT – High and MSFT – Low; and the Up/Down bars are working on the axis containing MSFT – Open and MSFT – Close. For my example, I’m going to move MSFT – High/Low to the second axis. Then, I’m going to click on the ‘Layout’ tab, under ‘PivotChart’ tools, and ensure that the second vertical axis is NOT displayed. By doing this, I ensure that both datasets are using the same scale automatically established by the first series.
Now, I’m going to make sure that I have selected the MSFT – High series, and I’m going to add in Lines –> High-Low Lines. Then, I can remove the markers from the MSFT – High/Low series.
Finally, I’m going to make sure that I have selected the MSFT – Open series, and I’m going to add in Up/Down Bars [Located: PivotChart Tools –> Layout –> Up/Down Bars]. Once the Up/Down bars have been added, simply remove the markers and you’re left with a PivotChart stock chart!
Since this is a PivotChart, you can do all of the usual cool pivot things, like filtering and different math calculations on the series. For example, the following two screenshots show daily trades from March 2010, and then last quarter for Microsoft; all I did to change the date was click on the Date filter at the bottom of the screen.
For those that are interested, I've included the sample spreadsheet used for this post here: MSFT Sample Stock Chart.xlsx. IE Users: Please select the 'Save' option rather than the 'Open' option for this file; if you do select 'Open' the PivotTable won't be able to 'refresh' it's datasource.