Overview
A Point and Figure (P&F) chart is a technical analysis tool that filters out time and focuses purely on price movement. Columns of X's represent rising prices; columns of O's represent falling prices. This workbook builds the entire P&F chart from MSFT (Microsoft) stock data using Excel formulas, then overlays Bollinger Bands to identify overbought/oversold conditions. The chart rendering uses conditional formatting and VBA.
Download the Excel file (.xlsm)
How It Works
- Data Sheet: Contains adjusted close prices from MSFT stock (sourced from Yahoo Finance).
- P&F Logic: Formulas calculate bin width, switch locations, up/down indices, and upper/lower bin boundaries. Each column switches from X to O (or vice versa) only when the price reverses by at least the bin width.
- Symbols: Rising columns display
X, falling columns displayO, and Bollinger Band boundaries use$and+++. - Bollinger Bands: Standard deviation calculations are overlaid on the P&F grid to show upper and lower bands, helping identify when prices are extended beyond normal volatility.
- Summary Data: Tracks max/min stock prices, max/min of upper and lower bands for scaling.
When to Use This
Point and Figure charts are used by technical analysts to identify support/resistance levels and trend reversals without the noise of time-based charts. Adding Bollinger Bands provides a statistical framework for identifying when P&F columns are reaching extreme levels. This workbook is a learning tool for understanding both techniques and how to implement them with pure Excel formulas.