<http://lib.cnfolio.com/RiskAnalysisNotes04>
Introduction to Financial Risk Analysis

Value at risk of a single stock




Setup a spreadsheet to analyze data from Yahoo Finance. There should be tabs for worksheets to:

Spreadsheet setup




Use the historical pricing data from Yahoo Finance to select daily prices for a period of 1 year.

Historical prices at Yahoo Finance


Download the raw data:

Historical prices at Yahoo Finance


Import (or copy) into the source worksheet of the spreadsheet. The raw pricing data might look similar to this:

Historical prices import




Historical pricing data from Yahoo Finance has already been adjusted for dividend payments and other material changes to the stock.

Historical prices have been adjusted for dividend payments




Calculate the daily returns for the most recent 251 days (that will result in 250 values) on a separate worksheet by linking to values in the raw import worksheet.

Calculate daily returns




( current price – previous daily price ) ÷ previous daily price = daily rate of return




Sort the daily returns from lowest to highest in order to determine the 1%, 2% and 5% cumulative frequency ranges.

Sorting historical stock prices


The cumulative frequency is dependent on having 250 data values:

Sorting historical stock prices




Calculate the standard deviation for the daily and weekly rates of return at the 1%, 2% and 5% confidence levels.

Variance analysis of historical stock prices


Use the following formulas:

Variance analysis of historical stock prices




Combine the historical method and parametric methods to calculate the value at risk:

Value at risk using historical and parametric methods


Use the following formulas:

Value at risk using historical and parametric methods