Excel is a widely used tool for data analysis and visualization. It offers various chart types that can help to represent different types of data. One of the chart types that Excel offers is a stock chart. A stock chart is a chart that represents the price and volume of a stock over a period of time. In this article, we will discuss how to make a stock chart in Excel.
Excel has four different stock chart types. The type of chart that we can use depends on the columns and data that we have. For example, if we have three series of price values (high, low, and close), we can use the first type. If we have open, high, low, close, and volume, we can use the fourth type of chart.
To create a stock chart in Excel, we need to have the data of the stock for a specific period of time. In this example, we have all the data of a stock from August 26 to the end of October.
To create a stock chart, we need to highlight our data, except for volume. Then, we need to go to the Insert tab and choose the second chart. The first look of the chart may be a bit disappointing. However, we can make some changes to the chart to make it look better.
The first change that we need to make is to make the dates appear on the horizontal axis. We can do this by clicking on dates, going to the Home tab, and changing the font size to 6. This will make more dates visible with a smaller font size.
The second main change that we need to make is to insert different minimum and maximum bounds. It’s useless for the minimum price on the vertical axis to start from 0. It should be started from, let’s say, 30. To do this, we need to double-click on the axis and select the fourth tab from the dialog box. Then, we can change the minimum value to 30. The maximum bound is 60, so we can leave it as it is. After making these changes, the stock chart should look much better.
Until now, we haven’t used volume. Let’s make a second chart with volumes this time. We can highlight all the data and go to the Insert Tab. We see that we can’t select the first three charts because of the volume. We need to select the fourth chart and move it below the other chart.
When we look at the chart carefully, we see two vertical axes. The left axis shows the price of the stock, and the right axis shows the volume. Also, we see two columns, the blue column, which represents the open price, and the white column, which represents the volume.
To make the chart look better, we need to format the font and direction of dates. We can double-click on the horizontal axis, go to size and properties, and press the alignment tab. Then, we can change the text direction to vertical. After that, we can go to the Home tab and change the font size to 6.
If we need the high, low, or close of the stock, we can follow these steps: first, highlight the chart. Then, go to the Design tab and click on the change chart type button. Select the combo chart and choose the chart type and axis for data series carefully. We want open to be a clustered column, the same with high, low, close, and volume (but volume has to be on the secondary right-side axis). Press OK.
The chart is ready, but it may be too noisy. A final note is that stock charts can be used for a variety of other purposes, like temperatures. With these steps and some further formatting, we can create a stock chart in Excel that represents the price and volume of a stock over a period of time.