Kalman Filter

A Kalman filter is a linear model that is very popular among quantitative traders. The nice feature about Kalman filter is that there is very few free parameters: the model will adapt itself to the means and covariances of the input time series gradually.

Two things to keep in mind is that the filter uses a model to predict the system's next state. So you still have to choose between linear and non-linear modelling even after deciding to apply such a Kalman filter. Also be aware that the mathematical underpinnings of the Kalman filter assume continuous, normally distributed variables. Items which are strikingly hard to come by in trading.

Furthermore, it can do so one-step at a time (or in technical jargon, using an "online" algorithm) : i.e., there is no need to separate the data into "training" and "test" sets, and no need to define a "lookback" period unlike moving averages.

It makes use of "hidden states" much like Hidden Markov Models (HHM), but unlike HHM, Kalman filter is faithfully linear.

Implementing a Kalman filter in Excel requires creating a step-by-step matrix table that alternates between a prediction and an update step for every time period. While Excel is great for understanding the mechanics, Python or MATLAB are generally recommended for complex systems. 

The Kalman Filter Matrix Process A standard linear Kalman filter runs recursively using the following sequence: 

State Prediction:  Error Covariance Prediction:  Kalman Gain:  State Update:  Error Covariance Update:  Setting Up the Excel Table

To execute this in Excel, lay out your data across columns, tracking the variables over time. You will need matrix operations like MMULT (for matrix multiplication), MINVERSE (for inversion), and TRANSPOSE. 

Your columns should include: t: Time step : Predicted state (mean) : Predicted error covariance : The residual/innovation : Kalman Gain : Updated/filtered state estimate 

For a practical breakdown of these steps, you can review this academic paper on a Simplified Approach to Understanding the Kalman Filter, or download a pre-built reference file like the University of Auckland Kalman Example to study the exact formulas in use. 

See: Kalman Filter Videos - University of Auckland For a visual overview of how the two-step prediction and update process works.