S&C Magazine Departments Traders' Tips MICROSOFT EXCEL: SWAMICHARTS
MICROSOFT EXCEL: SWAMICHARTS
- Details
- Parent Category: Departments
- Category: Traders' Tips
- Written by Ron McAllister
The heatmap is a concept with many interesting applications. Heatmaps are a component of John Ehlers’s MESA software (www.mesasoftware.com) and are presented in two of Ehlers’s past S&C articles, “Measuring Cycle Periods” (March 2008) and “Corona Charts” (November 2008), as well as in Ehlers & Ric Way’s article in this issue, “Introducing SwamiCharts.”
In their article in this issue, Ehlers & Way extend the heatmap concept to additional indicators and provide a detailed example of the stochastic indicator.
I have used a variant of Excel’s 3D surface charting capability — the contour chart — to map the stochastic indicator values into the color domain.
To simplify an otherwise tedious task of setting colors for a hundred or more value scale steps, I have provided a separate “Colors4” tab where the user can set up the value scale min and max along with the number of steps between. There is a place on this tab to code the RGB color component formulas that should be used to determine the color for each value. These correspond to the formulas that set colors 1, 2, and 3 in the EasyLanguage code given in the article’s sidebar.
The underlying macro that uses this tab to set the contour colors for the target chart is initiated by clicking a button on the Colors4 tab. Be patient: For a step count of 100, this macro can take a minute or more. If you increase the step count, you can reduce any visual color banding at the cost of run time for the macro as well as when the Excel graphics engine renders the target chart.
However, once the color setup is complete, you never need to consider the color map again unless you decide to alter the color component formulas or the value min, max, or interval step counts.
Please note: In past Traders’ Tips, to allow for the widest possible reader access, I presented Excel spreadsheets in the Office XP format (.xls), since any newer version of Excel (including Excel 2007 & 2010) can use spreadsheets built in Excel XP. However, I have built this Traders’ Tip spreadsheet using Excel 2010 (.xlsm) and it is not completely compatible with Excel XP, even if your Excel XP has the compatibility fixes installed. The contour chart will not display the proper colors, and the macro to set the colors will fail because of incompatible object properties. In testing with XP, I also found that on occasion, Excel XP would render the chart as a 3D side view rather than the expected top view. As of this writing, I have not been able to find a suitable workaround.
The spreadsheet file for this Traders’ Tip, “SwamiCharts.xlsm,” can be downloaded here.
A sample chart is shown in Figure 14.
FIGURE 14: EXCEL, SWAMICHART. This sample chart produced with Microsoft Excel 2010 spreadsheet software shows a SwamiChart of the stochastic oscillator on the S&P 500, modeled after John Ehlers & Ric Way’s technique presented in their article elsewhere in this issue.
