How to create a Renko Chart in Excel

I was recently asked how to create a Renko chart in Excel.

My first question was "What the heck is a Renko chart?" which was quickly answered by the guys at Investopedia.  To paraphrase:

So I gave it a crack and this is the chart I came up with:

In this post I explain how I set up the data and how I created this Renko chart. For some reason, I keep calling it a Jenga chart, so if a 'Jenga' accidently slips in, let me know in the comments!

All right, let's get into it.

Here is some example stock data. Unlike a regular stock chart, the open, high and low figures are not required, so the only data used is the Date and the Close price.

The 'box size' is key. It defines the size of each up or down block. For flexibility, write the box size value into cell H1 and name the cell Box_Size.

Next, create all the calculation columns required for the Renko chart. These formulas are broken down below.

Notice how the values in column I increase or decrease by exactly 1 (the box size) whenever the close price in column E changes by at least the box size. 

What the formula does::

If the difference between the value in column E and the last populated value in column F is equal or greater than the box size then record the close price, otherwise make the cell blank.

How the formula works:

This part of the formula constructs an array of '1's and '0's. Each cell between F4 and the cell above the current one is checked. A '1' is added to the array if the cell is populated and a '0' is added to the array if the cell is empty.

For example, for cells F5:F10, the array is {0, 1, 1, 1, 0, 0}

This part of the formula constructs an array of row numbers for the range between F4 and the cell above the current cell.

For example, for cells F5:F10, the array is {5, 6, 7, 8, 9, 10}

This part of the formula multiplies the corresponding values of each array , then selects the largest.

For example, for cell F11, multiplying the corresponding values in the 2 arrays {0x5, 1x6, 1x7, 1x8, 0x9, 0x10} gives {0, 6, 7, 8, 0, 0}. Therefore 8 is the largest value which means that row 8 is the last populated cell to this point.

This part of the formula creates the cell reference for the last populated cell using the row number calculated in the previous step.

This part of the formula subtracts the value in the last populated cell (identified above) from the close price on the current row. If the result is negative, the sign is removed because it's the difference that is important.

If the difference is equal or greater than the box size, record the close price otherwise make the cell blank.

The Select Data Source dialog should now look like this:

The initial chart should look like this:

Change the axis scale

Remove the gaps between the columns and make the base bars invisible

Set the up and down bar colours

Change the dates on the bottom axis to text so empty dates can be hidden

Hide empty dates

Once these steps have been completed, your Renko chart will look like this.