Garch Modeling in Excel and MATLAB

* Update – Trade and investing models that were designed earlier using Yahoo! Finance API have been rewritten to retrieve quotes from Bloomberg Markets or an Open Source API. At this time only US stocks and ETF quotes can be retrieved from the UI model. See setup instructions.

Introduction to GARCH Modeling

GARCH is a time series modeling approach that uses past variances to predict future variances. This post shows you the way to do a simple GARCH modeling in Excel. You can construct the same model in MatLab in a totally concise manner. GARCH stands for Generalized Autoregressive Conditional Heteroskedasticity. Investopedia provides a good introduction to GARCH. The series is heteroskedastic when the time series is said to have GARCH effects – when variances vary with time. The series is homoskedastic when variances remain constant with time. Put  another way, GARCH includes past variances that explains future variances. Consequently, you can apply this models to diverse fields such as option pricing, foreign exchange, asset allocation, risk management, and portfolio management just to name a few.

GARCH(1,1) is defined as:

where,

σ is the variance, ε is the residual, t is the time period, ω, α, and β are estimation parameters determined by the log likelihood function.

ε²_t-1 is the natural log of the ratio of closing asset prices for two consecutive trading periods or ln(P_t/P_t-1) and P stands for asset closing price.

GARCH Modeling Excel Matlab

The Excel Spreadsheet in this case has been automated in every way possible. To start, just enter a major stock index or an ETF symbol, the start and end dates. This example uses daily returns of S&P 500 from Feb-2010 to Feb-2015.

Figure 1: GARCH input parameters and results

The excel code uses the Solver library to maximize the Log Likelihood. You can manually regulate the constraints or the objective function by bringing up the Solver dialogue box, assuming you have Solver addin is installed.

Note: Before using Solver commands in VBA, you must reference the Solver library in the VBE. To do this, go to Tools > References and choose Solver from the list. If you do not see Solver as an option, use the Browse button and go to C :/> Program Files > Microsoft Office > Office (or Office10) > Library > Solver and look for the “Solver32.dll” file. Double-click this file; now you should find “SOLVER” in the list and check the box next to it. If this does not work, copy the “Solver32.dll” file to the C > WINDOWS > SYSTEM32 directory. You may also try to open the file “Solver.xla” directly and then open the Excel file. If you do not find the “Solver32.dll” file, you may need to reinstall Office. The code has been tested against Excel 2010 and 2013.

Figure 2: Volatility of GSPC as estimated by GARCH(1,1) model.

You can see from Figure 2 that GARCH volatility spikes upward and decays until there is another spike. There are usually two type of spikes – Shocks and Announcements. Volatility increases as the announcement (financial result) season approaches, and then fades way when the results of the announcement are known to the public. The GARCH(1,1) model is employed primarily to estimate how fast the fading (decay) is. Lots of data points would indicate tens or thousands of daily observations.

In this example, daily returns of S&P 500 Index was fed into the GARCH(1,1) model, totaling 1260 observations. If you have fewer than 1000 observations, GARCH estimation cannot do a good job in estimating omega, alpha, and beta variables. GARCH models are estimated through maximum likelihood. The optimization results can go awry at times, especially when Excel Solver is used.

Returns almost always do not have a pure normal distribution. They rather have long tails. GARCH effects can be a contributing factor for long tails. A t- distribution can be used when longer tailed distribution turns out to give a better fit. There will be no auto-correlation in the squared residuals, if the volatility clustering is well explained by the model. In such situations, Ljung-Box test can be used to detect auto-correlation.

Optimization demands caution. It is always a good idea to test the outcome of one algorithm in Excel against alternative robust tools such as Matlab. Below is the Matlab Snippet for GARCH(1,1) model estimation. The same set of daily returns was used against Matlab. See the attached csv file.

GARCH Modeling in Matlab

Comparing the results:

The results came very close with the exception of α parameter. The GRG Non Linear optimization method in Solver yielded in subtle differences on output parameters.

Excel Solver for [ω, α, β] = [0.0000, 0.1559, 0.8400]

Matlab results for [ω, α, β] = [0.0000, 0.1559, 0.8408]

Limitations:

Although GARCH models are usually applied to return series, investment decisions are rarely based on expected returns and volatilities. GARCH models often fail to capture high periods of volatility, including severe market downturns (e.g., crashes and subsequent rebounds), and other highly unanticipated events that can lead to significant structural change.

It often fails to fully capture the fat tails observed in asset return series. Ljung box tests can help you in identifying auto-correlation that remains in the residuals. Once you get a feel of GARCH(1,1), you can adapt the code to EGARCH or NGARCH.

[sociallocker]Download Garch Modeling in Excel and Matlab[/sociallocker]

The UI spreadsheet retrieves quote information from Bloomberg OpenMarkets through a lightweight data-interchange format called JSON or JavaScript Object Notification. If this is your first time programming JSON in Excel, you must add certain COM objects to retrieve Bloomberg Markets Web Service through VBA.

Under Developer->Visual Basic->Tools->References, add the highlighted objects.