One way to improve your abilities in Excel is to improve your understanding of the kinds of solutions Excel can generate when you use—or you don’t use—certain Excel features.
A current project illustrates this idea. This chart is a High Low Open Close (HLOC) chart that responds to the settings above it. But even if you care nothing about stock analysis, you’ll probably be interested in the techniques I used to create this chart.
Even if you don’t follow the stock market, you might find it interesting.
The Key Chart Elements
The chart contains at least five key elements. I’ll point them out, then describe the Excel features I used to create this chart.
First, the chart displays four sets of HLOC plots:
- The black bars show standard high-low prices for each trading day. The left tic mark shows the opening price and the right tick mark shows the closing price.
- The orange bars show “inside” performance. That is, they show when highs for the day are below the previous high and when lows are above the previous low.
- The green bars show “outside” performance. That is they show when highs for the day are above the previous high, and when lows are below the previous low.
- Hidden behind the black bars is a red bar that displays “true” highs and lows that are greater than the black bar. Several black bars show a hint of red peeking out above or below it.
Second, the date settings in the HLOC Bars section above the chart allow users to specify the beginning and ending dates to display. A star (“*”) setting indicates that all available data should be used.
Third, the chart displays three Channel Lines, which are trend lines through the bars for the specified periods. The setting for standard deviations allows the user to specify how many standard deviations to display on each side of the middle bar.
Fourth, the Vertical-Line section allows the user to specify as many as eight dates where dashed vertical lines should be drawn in the chart. The dates can be entered in any order in any of the eight postions shown.
Fifth, the Swing Line is the blue line that bounces from a low point in one bar to a high point in another bar to a low point…and so on. The Sensitivity setting allows users to specify how sensitive the formula is to nearby highs and lows. To illustrate, this chart…
…shows the most-sensitive setting, with 12 line segments. On the other hand, a sensitivity setting of 5 would show only two line segments with this data.
Key Excel Features and Techniques
This HLOC chart doesn’t look much like a normal Excel chart. So it’s easy to assume that it uses extensive VBA to control all that complexity. But, in fact, this chart uses no VBA at all. Instead, it uses formulas to determine where to draw the lines; which lines should be black, green, or orange; how much data to display; and so on.
When I’m done with the chart project, users will be able to choose the stock to display from a list of stocks with data stored in an Excel Table. And the chart will have additional indicators that users can display and control in the chart.
Here are the key Excel features and techniques I’ve used so far in this project…
1. The chart is an XY (Scatter) chart, mostly because XY charts offer the only way to display vertical lines in Excel. (I didn’t use Excel’s High-Low-Close chart type because it offers much less power than the XY chart type does.)
2. The chart currently uses 18 chart SERIES formulas. That is, it plots 18 lines, many of which consist of multiple line segments. To illustrate, I plotted all vertical black bars in the chart using only one chart SERIES formula.
3. Many of the settings use Excel’s Data Validation List feature, which allows users to select values from a dropdown list box.
4. The workbook uses about 140 range names, 36 of which are dynamic range names. This project would not have been possible without extensive use of range names.
5. The formulas that arrange data for display use INDEX-MATCH extensively.
6. The different bar colors are plotted using different SERIES formulas. So to display an orange bar on a particular date, formulas hide the data that the black bar expects on that date and reveal data that the orange bar expects.
7. If I had used default Excel chart scaling, Excel would have set up wide margins between the lines and the edges of the plot area. For example, the three vertical lines could not have touched the top and bottom borders of the plot area.
So this chart completely controls the horizontal and vertical scaling. To do so, I set the maximum value for both the X and Y axes to 1 and each minimum value to 0. Then my formulas converted all values and dates to their relative position within those max-to-min dimensions.
8. To determine the maximum and minimum values for the Y axis, along with its major unit value, I used Jon Peltier’s scaling technique, which he describes in Calculate Nice Axis Scales in Your Excel Worksheet. Then I used data labels to display those scale values as labels for the X and Y axes.
9. Because my formulas determine where to position the Y-axis labels, I had to draw my own gridlines. Therefore one SERIES formula generated the line—broken into seven segments—that produced the seven horizonal gridlines shown in the chart.
I’m thinking about offering several video training lessons about these and related spreadsheet techniques. If you might be interested in learning more about them when they’re ready, please send me a message with the subject, I’m Interested in the training. If I get enough interest, I’ll start to work on them soon.
Finally, I’m also planning to offer this workbook for people who are actively interested in the stock market. But first, I’d like to know what key features the chart is missing. So if you have experience with HLOC charts, please send me a message with the subject, HLOC chart suggestions. In your message, please explain how each suggestion would make stock analysis more useful.
