Tutorial: How to create the Leadership Board chart
Tutorial: How to create the Leadership Board chart
I’ve been getting lots of requests for a tutorial on how I make my ‘leadership board’ chart. I use this handy tool to identify market leadership both at the sector and individual stock level:
This is all done in Excel and updated with a click of a button. The initial setup, however, does requires some time. Here’s how to do it:
Part 1: Create the spreadsheet
In Excel, type a list of tickers. Then select the cells:
Go into the Data tab (see blue box below), and click the ‘Stocks’ button (see orange box below).
Your tickers will be converted to a Stocks data type and should look like this:
Aside: Sometimes, Excel will convert your ticker into something different from what you intended (especially for ETF’s). If that happens:
Right-click on the cell you want to change, select “Data Type” and then “Change” from the dropdown menu.
The “Data Selector” window will open on the right. In its search box, type the ticker you want. A list of options will show. Select the correct ticker you want.
OK. Right beside your selected cells, an “Add Column” button will appear (see blue box above). Click on it and select “Ticker Symbol” from the menu. You will see the ticker symbols in a new column.
Keep repeating the above step until you’ve added all of these fields:
52 week high
52 week low
Price
Market cap (optional)
Name the column headers. Your spreadsheet should look like this:
In a new column, we will calculate the gain factor that each ticker is currently above its 52-week low. The formula I use is shown below:
Finally, we’ll add one more new column in which we calculate the percent each ticker is currently below it’s 52-week high. The formula I use is shown below:
Awesome, your spreadsheet is all setup!
Part 2: Create the chart
Select the data in columns I & J. Then go to the Insert tab. Under the charts section, click on the Scatter button, and select the first option.
A new chart will appear on your spreadsheet. Move this to a new tab by right-clicking on the chart, and selecting “Move Chart.” A new window will pop up. Select “New sheet” and click OK.
The chart should look like this:
Right-click on the y-axis, and select Format Axis. A new window will open on the right. In Axis Options, under ‘Horizontal axis crosses,’ select ‘Axis value.’ Type -1. This will make the x-axis drop to the bottom of the chart.
Now right-click on the x-axis, and select Format Axis. In Axis Options, under ‘Bounds,’ set the Minimum to 1.0 and Maximum to 4.0. Select the ‘Logarithmic scale’ checkbox and set the Base to 2.
The chart should now look like this:
Formatting I like to do: turn off gridlines, add tick marks, add a plot-area border, change y-axis to percent, label the axis, and change font sizes. My chart now looks like this:
Next, we’ll add the ticker symbols on the chart. Right-click on one of the data points, and select “Add Data Labels.” Values will appear next to the scatter dots.
Double click on one of the values. On the right-hand window, in Label Options, uncheck ‘Y Value’ and check the ‘Value From Cells’ box. A window will open asking for the Data Label Range. Click the up-arrow, go to tab containing your tickers, and select the tickers in column D. The chart should look like this:
Finally, I like to add green & red regions to show the leaders and laggards. This is done by clicking on the chart, going into the Format tab, and under ‘Insert Shapes’ clicking on the ‘Right triangle’ shape.
Draw the triangles on the chart. Then, right click on each, select ‘Format Object.’ In the right-hand window, choose the fill color, set fill transparency to 80%, and choose ‘No line.’ The chart should look like this:
You’re done!
You can update this chart simply by going into the sheet containing your data, right-clicking on one of the values in Column C, and selecting ‘Data Type’ then ‘Refresh.’ You may need to adjust the axis ranges on your chart so that they fit your data.
See also:
Important Disclaimer: This blog is for educational purposes only. I am not a financial advisor and nothing I post is investment advice. The securities I discuss are considered highly risky so do your own due diligence.