Technology exploration project – M591
Value at risk of a single stock
Setup a spreadsheet to analyze data from Yahoo Finance. There should be tabs for worksheets to:
- Save raw data from Yahoo Finance
- Perform calculations
- Display analysis
Use the historical pricing data from
Yahoo Finance to select
daily prices for a period of 1 year.
Download the raw data:
Import (or copy) into the source worksheet of the spreadsheet. The raw pricing data might look similar to this:
Historical pricing data from Yahoo Finance has already been adjusted for dividend payments and other material changes to the stock.
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.
Sort the daily returns from lowest to highest in order to determine the
1%, 2% and 5% cumulative frequency ranges.
The cumulative frequency is dependent on having
250 data values:
Calculate the
standard deviation for the daily and weekly rates of return at the
1%, 2% and 5% confidence levels.
Use the following formulas:
Combine the historical method and parametric methods to calculate the
value at risk:
Use the following formulas:
