How to Make Standard Deviation Volatility Chart

Standard deviation is a commonly used statistic in finance and economics to measure the volatility or risk of a security or portfolio. By plotting the standard deviation over time, it is possible to create a chart that shows how the volatility of a security or portfolio has changed over time. This type of chart is known as a standard deviation volatility chart.

The creation of a volatility chart is about knowing how the trade has change over a specific period of time.

Here is a step-by-step guide on how to make a standard deviation volatility chart in Microsoft Excel:

Data preparation

To create a standard deviation volatility chart, you will need to have a series of data points representing the returns of a security or portfolio. Make sure the data is organized chronologically, with the earliest data first and the latest data last.

data set to create a volatility chart in Excel

Stdev formula

Calculate Standard Deviation in Excel on a daily basis. Click on the third cell from return and write, for example =STDEV(D6:D7), and then enter.

This will calculate the standard deviation of the data. Repeat this process for each period of time you want to analyze.

Standard Deviation

Click on the small square under the calculated result in the previous step twice.

return

Mark whole Changes column.

mark column

Note: This was chosen because it was based on how the stock performed at the end of the day.

Inserting volatility chart

Click on insert, lines, and line chart with marker.

insert marker chart

Click on the chart (the design will show), and Click select data.

See also  How to Create a Timeline Chart in Excel

click select data

Click edit on the horizontal area.

edit horizontal data

Select all the dates, then press ok and ok.

select date

Right-click on the Dates and choose Format Axis.

Format Axis

Change label position to low.

low labels

Click on the series (blue line).

blue line

Click on Add chart element, Error Bars, and Standard Deviation.

error bars standard deviation

On the right side, scroll down to choose custom, and specify value.

specify value

Select the range in “Return”, for both Positive and negative error values, and press ok.

select return range

Our volatility chart looks like this:

Volatility Chart ready

Whether you are a business analyst, financial analyst, or data analyst, a standard deviation volatility chart can help you to quickly and easily analyze and communicate the volatility of your data.