1. Candlestick charts
Next, we are going to look at Candlestick charts. Candlesticks are financial charts that are used to show price movements for securities, derivatives, currencies, stocks, bonds, and commodities. Each candlestick shows one day of behavior.
They were originally developed in the 16th century by a Japanese rice trader as an ancient method of technical analysis to trade rice contracts. They have been around ever since.
2. Creating a candlestick
Candlesticks display multiple bits of price information. In fact, the minimum number of columns you can use to chart is 4. They are great for showing trends, predicting future behavior, and displaying day to day market movement.
Each candlestick symbol shows the compressed trading activity for a time period. In your case, you are going to chart the open, close, highest and lowest price for 10 dates.
As always, optimize your dataset first and create named ranges, validations, and VLOOKUPs, as needed.
3. Formatting dates as plain text
One of the drawbacks with a candlestick chart is that if you are using dates, they need to be formatted as text instead of as dates. This can be easily done by highlighting your dates and selecting Format, Number, and Plain text.
Unfortunately, if you do this on your main dataset, it may affect the other charts that were already created. To avoid this, you will need another copy of the dataset so you can format the dates as plain text.
4. Plain text using a formula
There is an alternative. You could, instead, insert a column and use a formula to feed off the dates you have in your dashboard. To use the formula, write equals text followed by parentheses that contain the cell you want to format, and the format you want to use in quotation marks. In the example shown, the date we want to format is in cell A3 and the format is month month day day year year.
5. Viewing the detail
Select the range you want to chart and be sure that you use the column with the plain dates. As with the other charts, change the chart type that was automatically selected to a candlestick chart.
Once the chart is created, hide the plain dates column. You can see the figures for a particular date by clicking on your chart to activate it and hovering your mouse over the candlestick for that date.
6. Formatting your candlestick chart
Let's discuss candlestick chart formatting. Use the Chart editor to modify the font size or change the slant. Be sure the font size is consistent on the vertical axis, and don't forget a suitable title.
This is one chart type where gridlines could be useful. Start by changing the minor gridline count from None to 2 and see what your chart looks like.
A big drawback with the candlestick chart is that unless you use code to create it, which we won't cover in this course, you can't change the series color as you can with other charts.
You will also have noticed that there is no legend. This is because you are showing multiple bits of information. However, as mentioned, you can see all the price information by hovering your mouse over the symbols.
7. Let's do it!
Although there are some drawbacks with candlestick charts, they are a very useful tool. It is important that if you are working with financial instruments you understand how valuable they are. Let's practice!