Charts and Dashboards: The Point and Figure Chart – Part 4

Welcome back to our Charts and Dashboards blog series. This week, we continue to explain how to create a bespoke Point and Figure chart by looking at how we use all the data we have summary, prepare and transform to plot the chart.

Plotting Data

Next, we head to the ‘PnF-CSE’ sheet.  In the Lower Bin column, we will implement the following formula for the column:

Let’s recall that we have Max as the maximum value in the Adj Close column, Width is the distance between two [2] bins of number and Number_of_Bins is the number of number bins we have.  The ROW(INDIRECT("A1:A"&Number_of_Bins)) will generate an array of numbers from one [1] to the number of bins we enter in Number_of_Bins cells.  (You can also use the SEQUENCE function here to achieve the same thing.  What we are doing here is building this model in the Legacy Excel version for most of the users)

In Legacy Excel for the above formula to work we need to select a range that is equal to the Number_of_Bins (in our case here is 30) then enter the formula and press Ctrl + Shift + Enter.

If there is a curly bracket around the formula afterward then we are heading in the right direction:

We do a similar thing for the Upper Bin column with the following formula:

With that we have finished our vertical axis:

We will name the data in the Lower Bin column as Lower_Bin_Column and Upper Bin column as Upper_Bin_Column

Next, we will create the horizontal Axis.  In the sheet we prepare for you, we will select E9:EF10 and enter the following formula:

This formula will flip the row we have for the Unique Index column and Date column in the Transform_Data table to the right.  As this is a legacy array, we need to press Ctrl + Shift + Enter to complete the formula and with that our axes are completed.  We will name the numbering row as Index_Row and we will have the following visual:

The final step is to plot the chart.  We select the chart area from G11:EF40.  Then we enter the following formula:

Let’s break down the meaning of each component in this formula:

(Upper_Bin_Column>=TRANSPOSE(Transform_Data[Min])): The Upper_Bin_Column is an array that looks like a column vector and the output of this formula TRANSPOSE(Transform_Data[Min])) is an array that looks like a row vector.  The logical formula between these two arrays will generate a matrix of TRUE or FALSE with the width of the matrix is the maximum number of the Unique Index column in the Transform_Data Table and the height of the matrix is the Number_of_Bins.  This matrix will have TRUE on top and FALSE on the bottom like the following image:

(Lower_Bin_Column<=TRANSPOSE(Transform_Data[Max])): The Lower_Bin_Column is an array that looks like a column vector and the output of this formula TRANSPOSE(Transform_Data[Max])) is an array that looks like a row vector.  The logical formula between these two arrays will generate a matrix of TRUE or FALSE with the width of the matrix is the maximum number of the Unique Index column in the Transform_Data Table and the height of the matrix is the Number_of_Bins.  This matrix will have FALSE on top and TRUE on the bottom like the following image:

When we multiply these two matrices, we will generate a matrix of one [1] and zero [0] where the overlapping TRUE components are one [1] and the rest is zero [0] which will look like the following matrix:

We have overlapping points; we will need to determine which symbol goes into the cells.  That is when the latter components come in handy:

The first thing we check is if our Starting_Symbol is Up_Symbol, then every odd index number will possess the Up_Symbol and every even index number will possess the Down_Symbol.  This is why we use the MOD function like this MOD(Index_Row,2)=1 to detect the even and odd number here.  We repeat the same process if our Starting_Symbol is Down_Symbol.

"": the last component of the big IF function here is the blank space which will keep the chart clean.

Finally, after entering the formula and pressing Ctrl + Shift + Enter we will have the chart we want:

Now you might want to colour the ‘X’ symbol green and the ‘O’ symbol red.  We can employ Conditional Formatting to do our bidding.  We go to Home -> Styles -> Conditional Formatting -> New Rules (or Alt + O + D for short).  We add these rules for the colour scheme of the graph:

Congratulations we make the Point and Figure Chart.

You can download the complete file here, where it contains the chart make from CSE array, dynamic array and a dynamic array version that have Bollinger Bands.

Word to the Wise

This chart would be more useful if adding Bollinger Bands for our chart here and the chart will look like this with some tweaks in the formula and data transformation:

This can be in other Charts and Dashboards for some other day.

That’s it for this week, come back next week for more Charts and Dashboards tips.