How to create a Renko chart in Excel

How to create a Renko chart in Excel

AUTHOR:  JASON MORRELL 

Jason Morrell

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:


"Renko charts were developed by the Japanese to only show price movements, up or down, that are above a certain amount in contrast to standard charts that shows price on one axis and fixed time increments on the other. 

Renko (or 'Renga') means brick and the chart looks like a series of bricks. A new brick is created when the price moves a specified price amount. This interval is called the 'box size'. An up brick is typically colored white or green, while a down brick is typically colored black or red."

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

Finished Renko stock chart

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.

Step 1: Have your stock data ready

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.

Stock data for Renko chart

Step 2: Get your chart data ready

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.

Renko chart 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. 

Calculation columns for Renko chart

Column F - Identify the close prices that have moved (up or down) by at least the box size

=IF(ABS(E5-INDIRECT("F" & MAX(($F$4:INDIRECT("F" & ROW()-1) <> "") * ROW($F$4:INDIRECT("F" & ROW()-1))))) >= Box_Size, E5, "")

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:

  • INDIRECT retrieves the value from a given or constructed cell reference or range.
  • ROW retreives the row number of the given cell reference or the current row number  if the brackets are empty.
  • ABS removes the minus sign from a number. For example, -5 becomes 5.
  • MAX returns the largest number in a range.
  • IF tests a condition and shows one thing if it is TRUE and another if it is FALSE.
($F$4:INDIRECT("F" & ROW()-1) <> "")

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}

ROW($F$4:INDIRECT("F" & ROW()-1))

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}

MAX([Value in 1st array] * [Corresponding value in 2nd array])

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.

INDIRECT("F" & [Row number of last populated cell])

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

ABS(E5-[Value in the cell calculated in last 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] >= Box_Size, E5, "")

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

Column G - How big was the increase or decrease?

=IF(F5="", "", E5 - INDIRECT("F" & MAX(($F$4:INDIRECT("F" & ROW()-1) <> "") * ROW($F$4:INDIRECT("F" & ROW()-1)))))

Using the same logic as column F, show the amount of the increase or decrease but only if the difference is equal or greater than the box size.

Column H - Include the date on the chart if there was a sufficient increase or decrease

=IF(F5="", "", A5)

Column I - Increase or decrease the stock value by the box size. Ignore the actual increase or decrease value

=IF(G5="","",IF(G5>0,INDIRECT("I" & MAX(($I$4:INDIRECT("I" & ROW()-1) <> "") * ROW($I$4:INDIRECT("I" & ROW()-1)))) + Box_Size, INDIRECT("I" & MAX(($I$4:INDIRECT("I" & ROW()-1)<>"") * ROW($I$4:INDIRECT("I" & ROW()-1)))) - Box_Size))

If the figure in column G is positive and not blank then find the last populated closing price in column I and add the box price. For example, in cell I6 the last populated closing price is $74.77 in cell I4, therefore I6 is $75.77. For negative figures in column G, the box price is deducted from the last populated closing price.

Column J - If there was a sufficient change upwards, place the box-size value here as the increase value on the chart

=IF(AND(G5<>"", G5>0), Box_Size, "")

If the figure in column G is positive and not blank, put the box size in column I.

Column K - If there was a sufficient change downwards, place the box-size value here as the decrease value on the chart

=IF(AND(G5<>"", G5<0), Box_Size, "")

If the figure in column G is negative and not blank, put the box size in column J.

Step 3: Create a stacked column chart and set chart properties

Step 3a: Create the initial chart

1.  Select the chart data (cells H4:K45).

2.  Click the Insert tab, click the Column Chart icon, then the Stacked Column icon.

To create a Renko chart, start with a stacked column chart

3.  Right-click anywhere on the chart and choose Select Data.

Select Data

4.  In the Select Data Source dialog click on the default entry in the Legend Entries (Series) box, then click theRemove button. This provides a clean start.

Remove existing data range

5.  Click the Add button under Legend Entries to open the Edit Series dialog.

6.  Click in the Series Name box, then click cell I3 (Chart Close Value heading).

7.  Click in the Series Values box, delete the current entry then select range I4:I45 (Chart Close Value data).

Add Chart Close data range

8.  Click the Add button under Legend Entries to open the Edit Series dialog.

9.  Click in the Series Name box, then click cell J(Increase heading).

10.  Click in the Series Values box, delete the current entry then select range J4:J45 (Increase data).

Add the Increase brick data range

11.  Click the Add button under Legend Entries to open the Edit Series dialog.

12.  Click in the Series Name box, then click cell K(Decrease heading).

13.  Click in the Series Values box, delete the current entry then select range K4:K45 (Decrease data).

Add the Decrease brick data range

14.  Click the Add button under Horizontal (Category) Axis Labels to open the Axis Labels dialog.

16.  Click in the Axis Label Range box, delete the current entry then select range H4:H45 (Dates).

Set the dates (bottom axis) data range

The Select Data Source dialog should now look like this:

The final data should look like this

17.  Click OK.

The initial chart should look like this:

The initial Renko chart should look like this

Step 3b: Modify the chart properties

Change the axis scale

1.  Right-click on the chart axis and choose Format Axis from the context menu.

2.  In the sidebar, under Axis Options, set the Minimum and Maximum Bounds.

Change the vertical axis scale

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

3.  Left-click any of the (blue) bottom bars. All the bottom bars will be selected.

4.  Right-click any bottom bar and choose Format Data Series from the context menu.

5.  Click the Series Options icon (looks like column chart) then set the Gap Width to 0%.

6.  Click the Fill icon (looks like a paint bucket) then choose No Fill.

Set thecolumn spacing to zero and make the first set of data bars invisible

Set the up and down bar colours

7.  On the chart, left-click any UP bar to select them all.

8.  On the sidebar, which should still be showing the Series Options, set the Fill colour to green or white

9.  On the chart, left-click any DOWN bar to select them all.

10. Set the Fill colour to red or black.

Change the colour of the up bricks and down bricks

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

11. Right-click on any date on the bottom axis and choose Format Axis from the context menu.

12. Change the Axis Type to Text Axis.

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

Hide empty dates

13. On the table, click the filter button for the Date in column H and untick/uncheck the (Blanks) check box.

Hide empty dates

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

Finished Renko stock chart

Please feel free to post any questions you have into the comments below. And before you leave this page, I want to give you one quick little action item! 

ACTION ITEM: Comment below with ONE thing you picked up from this post and share how it has helped, or will help, you. It's good to share your successes because it encourages others and provides an instant boost.

Enjoy the rest of your day!

Jason Morrell blog signature
About Jason Morrell

About the author

Jason Morrell


Jason loves to simplify the hard stuff, cut the fluff and share what actually works. Things that make a difference. Things that slash hours from your daily work tasks. He runs a software training business in Queensland, Australia, lives on the Gold Coast with his wife and 4 kids and often talks about himself in the third person!

SHARE

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}
Office Mastery
>