How to apply kalman filter on rotation matrix?

Kalman filter implementation

• Background ========== I am a finance professional asked to give a presentation on smoothing of economic timeseries. I would like to include optimal smoothing, but do not have the mathematical ability to implement a Kalman filter. Ability Level ============= I use Excel and VBA regularly and I have a fair amount of practical experience with timeseries analysis, analog signal processing, forecasting and statistical concepts. My mathematical ability is stuck where I stopped studying it at age 16 and has degraded somewhat; I have trouble with mathematical notation and medium-advanced mathematical techniques, including, unfortunately, matrix algebra. I have done some background reading on Kalman filters, from John Elhers' "Rocket science for traders" which presents only the most basic concepts, to Eli Brookner's "Tracking and Kalman Filtering Made Easy" which, for a lay-person such as myself, is interesting, yet did not help me build one for myself. Question ======== Help me implement a Kalman filter in Excel. A simple example of a Kalman filter would do. I would prefer a spreadsheet solution, possibly using Excel's matrix formulae, but I'll accept a VBA solution if necessary. I suggest historical monthly closing S&P500 for a dataset, unless you have an alternative. Deliverables ============ A file, VBA listing, detailed and simple instructions for entering formulae, or whatever it takes for me to have a working, modifiable, Excel implementation. Professional Fees ================= I assume this question will be answered by a professional familiar with Kalman filters. Therefore I will pay my standard Google Answers rate of $20 per hour, on the basis of two chargeable hours' work. Hence, I have priced the question at $53, including Google Answers' commission. I realise I may have misjudged the requirements and, notwithstanding Google Answers' researcher guidelines, am willing to negotiate a different price. I'll discuss lowering the fee if you cannot deliver exactly what I need, but can point me to an existing third party implementation on the Web, or raising the fee (through tipping) if you feel it needs more work, but can still deliver value for money. Rating ====== A five star rating will be timely and complete, and leave me able to implement Kalman filters and deduce/explain the concepts to others. A four star rating, would be for an answer, that following clarification remains acceptable, but not completely satisfies my requirements. Other answers will be ungraded.

• Answer:

  Hi, asiatechnicals: Your spreadsheet is PK'zipped and ready for download at: http://www.lucidmatrix.com/uploads/MonthlyTS.zip The Excel workbook includes three worksheets. The first sheet contains two time series, in reverse time order, for the S&P 500 monthly closing prices and for gold prices (per ounce), over the period Jan. 1985 to Dec. 1999. The second sheet is the desired implementation of the "g-h" Kalman filter on the S&P closing prices and their month-to-month changes. The third sheet has a chart that plots the row data (in blue) vs. the filtered data (in pink). I've tested the spreadsheet by changing the system parameters and initial values around some. I'd be glad to provide any clarifications you want, in particular perhaps a discussion of how I chose these parameters. I'm including by reference the comments I wrote below concerning the notation and forumulas involved for the discrete Kalman filter algorithm. regards, mathtalk