City University of Hong Kong Department of Economics and Finance
Course EF5213 Assignment #1 ( due February 5, 2023)
1. In the Exponentially weighted moving average model (EWMA), future variance is a weighted average of its immediate past estimation and the most recent observation of squared residual of price return. It follows an iteration equation given by
2 (1)(r )2 2 t1tt
with weight factor 1 0. The parameter can be estimated based on the historical mean of a given time series {r1, … , rn} as (1/n)(r1 … rn).
(a) Given, in file HSI.csv, historical daily closing prices for Hang Seng Index from 2001 to 2020 as {timestamp, open, high, low, close, volume}
use VBA to develop a procedure that captures the time series of price returns as
This return This close Last close Last close
(b) Determine the EWMA model for the extracted time series in (a). The parameter should be determined by considering the notion of minimizing root-mean-square error (RMSE) defined as
RMSE√1n ∑nt1[2t (rt )2 ]2
based on the historical time series of price returns { r1 , r2 , … , rn }. For this purpose, use the enclosed
Brent’s minimizer from netlib with your own modification.
(c) Use the same time series { r2 , … , rn } to backtest the 1- confidence level of the optimal EWMA model.
Sample user interface is given below:
(40 points)