KaotiXL - a simple investment tool for MS Excel                                             

 |    Home    |   Download    |    Screenshot    |    Buy Now    |    Contact Us    |    Awards    |    User Guide    |

With KaotiXL, you can have the best of both world and examine the stock prices in details with

A) Value At Risk – which assume the stock prices to be normally distributed

B) Rescaled Range Analysis – which assume the stock prices is not normally distributed

A) Value At Risk

Value At Risk or VaR has been called the "new science of risk management". It is most commonly used by security firms or investment banks to measure the market risk of their asset portfolios (market value at risk). VaR is widely applied in finance for quantitative risk management for many types of risk.

Our main concern here is using KaotiXL to help us as private investors to manage our risk in stock market investment.

For investors, risk is about the odds of losing money, and VaR is based on this common-sense fact. By assuming investors care about the odds of a really big loss, VaR answers the question, "What is my worst-case scenario?" or "How much could I lose in a really bad month?"

VaR help investors to calculate the risk of a particular investment and the odds of losing money.

Consider a trading position. Its market value in US dollars today is known, but its market value tomorrow is not known. The investor holding that position might report that its position has a 1-day VaR of $1000 at the 95% confidence level.

This implies that under normal trading conditions the investor can be 95% confident that a change in the value of its stock would not result in a decrease of more than $1000 during 1 day. A 95% confidence interval does not imply a 95% chance of the event happening, the actual probability of the event cannot be determined.

The key point to note is that the target confidence level (95% in the above example) is the given parameter here; the output from the calculation ($1000 in the above example) is the maximum loss (the value at risk) at that confidence level.

From the above example, you can see how the "VaR question" has three components:

1. a relatively high level of confidence (typically either 95% or 99%),

2. a time period (a day, a month or a year) and

3. an estimate of investment loss (expressed either in dollar or percentage terms).

Keeping these three parts in mind, VaR will help you to answer

• What is the most I can - with a 95% or 99% level of confidence -  expect to lose in dollars over the next day/month?

• What is the maximum percentage I can - with 95% or 99% confidence - expect to lose over the next year?

How VaR is calculated ?

1. by using the historical method - assuming that asset returns in the future will have the same distribution as they had in  the past (historical market data)

2. by using the variance-covariance method - assuming that risk factor returns are always (jointly) normally distributed and that the change in portfolio value is linearly dependent on all risk factor returns

3. by using the Monte Carlo Simulation - where future asset returns are more or less randomly simulated

      (see details of VaR calculation here http://www.investopedia.com/articles/04/092904.asp and http://en.wikipedia.org/wiki/Value_at_risk )

KaotiXL includes all the three method of calculation and present them in a result table for easy interpretation and decision making. (see below)

With KaotiXL, you don't need to know how VaR is derived. All you need to do is to enter the historical closing price of a stock or index, and you will get the detail result in a separate worksheet. Simple...(see User Guide for details explanation)

B) Rescaled Range Analysis (Hurst Exponent)

Rescaled Range analysis is a statistical methodology used to detect the presence or absence of trend in time series by finding the Hurst exponent. For example, it is generally known that time series like stock prices, indexes of stock market, sunspot etc does exhibit the persistence of trends. R/S analysis is also highly data intensive.

Basically, this method is used to  identify when a stock price is persistence i.e. the tendency of the price to continue its current direction and also antipersistence  i.e. the tendency of the price to reverse itself rather than to continue its current direction. Or it is random and unpredictable.

The Hurst exponent (H) is use to determine the underlying distribution of a particular time series. As a rule of thumb...

    a) 0.50 < H < 1.0 implies a persistence time series. The larger the H indicates a stronger trend. (strong position on long)

    b) 0 < H < 0.5 implies antipersistence. (trade on reversal)

    c) H more or less equal to 0.5 indicates random time series. (No position taken)

(A complete explanation on how the Hurst exponent is calculated is available at this link)

R/S Analysis is also use to find the primary cycle length of stock prices and market indexes. The V Statistic shown below is very efficient in doing this. Not only the primary cycle but the underlying cycle as well, as long as the subcycles is a small, finite number.

  When you run KaotiXL to calculate the Hurst exponent, two charts will be generated in the Report sheet :

1.  R/S Chart - Hurst exponent can be approximated by plotting the Log (R/S) against the Log (N) by solving the slope ( y,x ).

2. V Statistic Chart - the break point on the V statistic chart can be used the estimate the cycle length - find the antilog of  the break point.

                            (NOTE: If the time series is antipersistence, i.e., H < 0.5 then it will be a upward slope. More on the User Guide)

User Guide For Value At Risk :(back to top)

1. From the Excel menu bar, select KaotiXL -> Start VaR. This will launch the dialog box (Fig 1.1 below). (Demo version only allow you to enter 100 rows of closing prices)

2. Enter the price range in the Price Range edit box. In Fig 1.1, we have enter, E2:E958, the daily closing price of Google from 19 August 2004 to 6 June 2008. (For demo version, you need to enter E2:E101)

Figure 1.1

3. In the No. of trials edit box, the default value is 500. The trials here is meant for number of iterations you want to simulate for the Monte Carlo method when calculating the VaR. Here, you can enter a value up to a maximum of 10000.

4. To begin calculation, click on the Run button.

5. A new worksheet, "VAR Output" will be created  when calculation completes.

6. In this worksheet a complete analysis of the result will be presented. (Fig 1.2 below)

7. You will see at the top of the worksheet, the decision table is displayed. Let's discuss the result in Fig 1.2 one by one.

Figure 1.2

1. On row 4 , we use the Historical method to calculate the VaR. Along the row, we can see that with 95% confidence (on cell D4), we expect that our worst daily loss will not exceed 3.52% (on cell G4). In other words, if we invest $100, we are 95%  confident that our worst daily loss will not exceed $3.52 ($100 x -3.52%).

2. One row 5, it is derived that with 99% confidence (on cell D5), we expect that the worst daily loss will not exceed 7.02%(on cell G5) or if we invest $100, we are 99% confident that our worst daily loss will not exceed $7.02

3. The Variance-CoVariance calculation method is shown on row 7. Along the row, we can see that with 95% confidence (on cell D7), we expect that our worst daily loss will not exceed 3.75% (on cell G7). In other words, if we invest $100, we are 95%  confident that our worst daily loss will not exceed $3.75 ($100 x -3.75%).

4. One row 8, it is derived that with 99% confidence (on cell D8), we expect that the worst daily loss will not exceed 5.3% (on cell G8) or if we invest $100, we are 99% confident that our worst daily loss will not exceed $5.30

5. The third method, where the VaR is derive by Monte Carlo simulation is presented on row 10. Here we can see that with 95% confidence (on cell D10), we expect that our worst daily loss will not exceed 3.62% (on cell G10). In other words, if we invest $100, we are 95%  confident that our worst daily loss will not exceed $3.62 ($100 x -3.62%).

6. One row 11, it is derived that with 99% confidence (on cell D11), we expect that the worst daily loss will not exceed 5.32% (on cell G11).  or if we invest $100, we are 99% confident that our worst daily loss will not exceed $5.32

If you scroll down on the worksheet you will see 2  tables and 2 charts. (see Fig 1.3)

Figure 1.3

The first table on the left, display the full result of the historical calculation of the VaR. 

The second table show the full result of the Monte Carlo simulation. This Monte Carlo simulation amounts to a "black box" generator of random outcomes. KaotiXL ran this  simulation based on the stock/index  historical trading pattern. You will get a different result each time you run this simulation.

And lastly, the two histograms (Historical and Monte Carlo) display the underlying distribution of stock returns.

NOTE: Only 10 result worksheets can be created in a workbook. Run KaotiXL on a new workbook each time it has reach “VaR Output 10”

User Guide For Rescaled Range Analysis :(back to top)

1. From the Excel menu bar, select KaotiXL -> Start Hurst. This will launch the dialog box (Fig 2.1 below).

Figure 2.1

2. Enter the time series in the Data Range box.

3. By default, KaotiXL use period 5 (N) to start calculation. User can enter different period (N).

4. Select what type of data to process i.e Original data or the Return

5. If user select the Return data to process, KaotiXL will automatically process the original data using this equation ( N+1- N ); N = period

6. Click on the Run button to start calculation

7. User can stop the program prematurely by clicking the Abort button. No report will be generated if this option is chosen.

8. End program by clicking the Cancel button.

9. Report shown below (see screenshot) will be generated. (NOTE: Only 10 result worksheets can be created in a workbook. Run KaotiXL on a new workbook each time it has reach “Result 10”)

  Two charts  will be generated in the Report sheet. The H is display in cell B23 : (see screenshot)

1. R/S Chart - Hurst exponent can be approximated by plotting the Log (R/S) against the Log (N) by solving the slope ( y,x ).             

2. V Statistic Chart - the break point on the V statistic chart can be used the estimate the cycle length - find the antilog of  the break point.           

                            ( NOTE: If the time series is antipersistence, i.e., H < 0.5 then it will be a upward slope.)