The PODA Blog

Introduction
There are many addin’s on the market to get the best out of Excel and its charting capabilities. But sometimes its fun to construct something yourself and prove a point in the process. Our little exercise is meant to visualize why some chart types look better in dealing with price series than others. For example we can use point and figure charts. One of the advantages of these types of charts is their tendency to smooth out noise in price behaviour. And this is what we are going to show in a simple exercise and create a small building block of our own visual trend analyser at the same time.

About point and figure charts
A point and figure chart is a chart that discards time and therefore only displays the price trend in columns of x’s and o’s. The price has to move a certain amount before a new x or o is added to the current column and this amount is called a box. It uses reversals to signal a change of trend and when that happens you start a new column of the opposite sign. The reversal is triggered when the price of the instrument goes in the opposite direction of the current trend by a certain number of boxes and this is called the reversal amount.

What we want

• one simple bar chart
• one point and figure chart

What we need

• 96 points of historical data on your favourite financial instrument
• each row contains date, open, high, low, close, volume

We start with a blank sheet called Input and place our data there as in the picture below

And we add a second sheet called PnFData as this will be our sheet to contain our point and figure data series in a later stage.

The third and last sheet we add is called ChartSheet. In the accompanying workbook the bar chart is already pre-configured and thus any data pasted on the input sheet will simple flow in to the first chart on the chart sheet. We only use a little sub to rescale the x-axis for time and the y-axis for price and the secondary y-axis for volume.

Next we come to the main item of this story, the code to loop through the data and determine all the details regarding a certain row of data. This code rebuilds the PnFData sheet, of which the outcome is linked to the PnF chart on the ChartSheet. This second chart on the chart sheet is a standard scatter chart with two series in it. We use one series for the down moves (o’s) and one series for the up moves (x’s). Since a point and figure chart works with columns we can display each move of x or o in its own column. The routine we need has to take into account the following parameters.

• data range
• are we going up or down
• trend or reversal
• box size
• reversal amount

For example if the trend is down (we are in the middle of a column of o’s) and the current price we examine is higher by the reversal amount of boxes than we need to reverse the trend. And start a new column of x’s. Of course this is the reverse if we are in a column of x’s (up trend) and we encounter a reversal amount of boxes to the downside we need to start a new column of o’s. After we created the PnF chart we only need to tidy it up with a correction of the y and x-axis.

By then we end up with the result looking like

There is an obvious advantage to use a different type of chart to display the same data and get a lot more information out of it.

Conclusion
If everything went as planned, you created a PnF chart made from a standard Excel chart, it may not be perfect but it gives an idea of what is possible. This post did not contain any code as i hope the flow of the story gives an idea and the implementation can be seen in the sample workbook. That same code for the determination of reversals can also be employed in other parts of an analyser type of workbook. For example to build a sheet with reversal signals from a coded strategy or to reveal trends within a long-term set of sales performance data. But that would be a topic for some other time. The possibilities are endless and you only need to experiment a bit. This post will not help in making you a better trader but it can help in expanding your bag of tricks in a widely used environment.

I want to thank Andy Pope for the original idea at this webaddress Point and Figure sample and his permission to use it for this post.

Download
The zip contains the workbook and code used for this sample; i used monthly data so the labels on the ChartSheet used in the headings of the charts reflect that. Any questions or suggestions are appreciated.

Sample workbook

Good Luck!