Posts

How to create Stock Charts in Excel

avatar of @theexcelclub
25
@theexcelclub
·
0 views
·
5 min read

Stock charts are a specific chart used to track the changes in price of traded assets.  Assets such commodities, stocks and cryptocurrencies.  They allow you see high and low values over time, along with opening and closing values in the one chart.  Excel offers 4 stock charts and to use these, you must have the right sets of data available and you must select the columns in the right order.

The below set of data shows the daily trading information for Bitcoin.  We are going to use this data to create each of the 4 stock charts available in excel.  You can copy and paste this data into a spreadsheet and follow along with the article.

Watch related video on YouTube Now

Although the data we have is daily, traders often use minute or hourly charts, some would use weekly or monthly stock charts.

To insert a High Low Close stock chart in Excel first select the 3 correct series of data and the date column as shown.

A nifty trick selecting data like this is first to select larger set of data, then pressing CTRL and holding down the left mouse, select the date column.  Once you have selected the data, from the Insert Ribbon and the chart group of commands, select the Waterfall drop down.  The stock charts are the third set down.  The first of the stock charts is the High Low Close Chart.

When the chart is inserted to the worksheet, it will need a little formatting.  

To change the Chart title, we can select the text in the title box and just over type it with the title we want to give the chart

To improve the visibility of the Close price on the chart we will select these data points on the chart,  and select format data series.

From our Format Data Series options, select market and change the type and colour that will stand out a little  more

Other formatting options would include improving the formatting of the X and Y axis

First select the required columns, then from the Insert Ribbon and the chart group of commands, select the Waterfall drop down.  The stock charts are the third set down.  The second of the stock charts is the Open High Low Close Chart.

This chart is very like a box and whisker chart.  The box represents the opening and close prices, the box will be transparent if the price closed higher than the opening and it will be black if the price closed below the opening. The whiskers show the high and the low price for the day.

You will note the chart comes with a legend.  However, this legend does not make much sense using the default colour scheme and can easily be deleted.  To delete the legend, select it and press delete.

Both the up bars and the down bars can be formatted separately. To do this, select the up bars and select Format Data Series.

The colour and transparency can be changed from the Fill options.

Having the ability to change both the up and down bars means you can really customize how these bars appear

Trend lines can also be added to the chart.  As we have 4 data points, we can independently select any of these and create a trend line based on the data point values. 

To add a trend line, select the data points on the chart and right click.  Select Add Trend line.

.

In the chart shown above, we have used the Moving Average trend line based on 2 periods.  This is the 2 day moving average of the selected data point.

Select the required columns and follow the steps above to insert the stock chart.  This time select the third of the stock charts.  This is the Volume High Low Close chart.

The blue column bars represent the daily volume.  The left axis displays the volume scale.   Then the lines represent the High Low and Close. The right axis displays the price scale.

This chart could also do with some formatting.  The left axis takes ups a lot of space.  We can use formatting to display these values in Billions.  Select the left axis, right click and select Format Axis.  In the Axis options select Billions from the Display units’ option

By selecting the right axis, under Axis options at the bottom is the options to change the number format.  This can be used to display the values as currency

Other formatting options include the fill colour on the entire chart area and the ability to add trend lines.

The below chart is the fourth and final type of stock chart available in Excel.   Its shows the Volume, Open, High, low and close prices.  Can you recreate this chart?  Share with the community in the comments below 3 formatting changes you make to receive a similar look to the chart below.

SIGN UP