Introduction to Regularizing 3D Data – Part 2 of 2

An Entire 3D Surface from a Few Measured Points

• An investment banker needs to create an entire volatility surface from a handful of derivatives trades that took place today.
• A chemist needs to know the temperature near the center of a reaction chamber based on only a few temperature measurements scattered around its perimeter.
• An engineer needs to model something with a smooth function of two variables, but he doesn’t know the function and all he has are a few dozen noisy measurement points.

How do they do it? By regularizing their 3D data of course!

The first part of this article showed how we use bilinear interpolation to relate arbitrary input points to locations on an output grid. Now we will look at the fidelity equations, set up second-derivative equations for the 3D data, and find the least-squares solution. The result is a regularized surface in 3D.

For those who like to see the details, the example spreadsheet is available for download.

RegularizeData2D, the Excel Spreadsheet Function for Regularizing 2D Data – Part 2 of 2

In Part 1 of RegularizeData2D, the Excel Spreadsheet Function for Regularizing 2D Data we saw how the program is organized, reviewed some of the differences between theory and implementation, and listed the important programming shortcuts that become available when we depart from rigorous mathematical theory in favor of practical implementation. In this part we will see the inner workings of the Excel spreadsheet function by looking at an example in detail. In the example we will take five “input points” in x and y, and we will regularize them to ten “output points.” The spreadsheet and source code are available for download.

RegularizeData2D, the Excel Spreadsheet Function for Regularizing 2D Data – Part 1 of 2

Custom Excel Spreadsheet Function for Regularizing 2D Data

This is a custom Excel spreadsheet function that regularizes 2D data. It is an Excel array function that duplicates the results of the spreadsheet calculations we saw in an earlier article about regularizing. This article shows the development of the VBA function and how, as a computer program, its design is different from the mathematical formulation. We arrive at the same result as the spreadsheet example, but we avoid slow operations like multiplying matrices, storing unnecessary data, and solving a matrix equation inefficiently.

Syntax for the Excel Regularizing Function

A Numerical Second Derivative from Three Points

What is the second derivative of three unevenly spaced points?  Is there a formula for the numerical second derivative?

In practice, these points could represent measured data that we want to analyze like weather patterns or financial data. We will see that the second derivative is a linear combination of the three points.