STUB: result_vector = INTERPOLATE( source_vector)

Function INTERPOLATE replaces missing values in a vector with meaningful replacements. Although a number of interpolation methods exist in statistics, StockWiz uses the method of the "nearest neighbor". It essentially takes the last known value and uses it to fill in the missing values.

For example, consider the vector

Dec-03-2007 10.0
Dec-04-2007 10.5
Dec-05-2007 NA
Dec-06-2007 NA
Dec-07-2007 11.0

The returned vector with the interpolated values will look like the following:

Dec-03-2007 10.0
Dec-04-2007 10.5
Dec-05-2007 10.5
Dec-06-2007 10.5
Dec-07-2007 11.0

How about weekends

StockWiz does not consider weekends to be missing days.
StockWiz treats weekends as if they never existed.

How about holidays

Currently values for stock market holidays are marked as missing values in the StockWiz database. The INTERPOLATE function does not distinguish between holiday missing values and other missing values. This may not be the ideal scenario for all situations. For example, when the calculation calls for the creation of a report with the average volume over the last 5 days and a holiday falls into those five days, you may want to skip over the holiday and bring in data from the 6th day prior to the last trading day (i.e., consider the data from the actual five last trading days). We intent to offer a way to deal with this scenario in some automated fashion but the functionality is not in the current release of StockWiz 5.

How StockWiz finds the "nearest neighbor"

First, StockWiz looks for the nearest value to the "right" side of the missing value (the one that chronologically came  afterwards). If none exist, then StockWiz looks to the "left" side (the one that chronologically came before the missing value).

Obviously if all values are missing in a vector nothing can be done. For that case you should use the STOPIF function to stop the execution of the formula for that company, if there are fewer actual values that you think is appropriate.

How about "naturally" occuring missing values

When one calculates certain moving functions such as a 60 day moving average, the first 59 values will be missing because there is not enough data to calculate those values. StockWiz 5 provides a nice way to deal with this problem. First, you should note that StockWiz 5 formula functions are like spreadsheet formulas but instead of performing calculations on individual cells, they calculate whole columns. Each column is called a vector. The collection of vectors is called a matrix. The number of rows in that matrix is controlled by the function RANGE. Another way of looking at this is that all vectors have the same size, and that size is defined by the RANGE function. The RANGE function can be used multiple times within the same formula. So, what you can do is to first use the RANGE function with a large size, perform the moving average calculations and then change the size of all vectors to a smaller size, chopping values from the "back" (removing older values). This will take into consideration older values when calculating the moving average, but it will not leave you with missing values. Here is a short example:

// Example of using RANGE function twice




// Interpolate for missing values


// Calculate moving average


// Change size of matrix to get rid of missing values in MA50


// Display on graph


Interpolation vs extrapolation vs forecasting

For the sake of completeness, statisticians use the term interpolation when calculating appropriate values for missing values that have actual values to their left and right sides. When the missing values are at the beginning or end of a vector, the process is called extrapolation. The special case of extrapolation to the "right" side past the most recent values (into the future, that is) is called forecasting.

Why is interpolation necessary

Interpolation is necessary in all cases where some purely mathematical calculations need to take place. Otherwise, the mathematical function would produce the wrong results.  StockWiz uses the value 1E9 (a 1 followed by 9 0s) to specify missing values. This is an extremely unlikely value for any stock market trading company or calculation. If such a value is passed to a mathematical calculation you will get very strange results. This is not a weakness of StockWiz 5 but rather a fact of life when dealing with stock market data (and this is what keeps a lot of Wall Street programmers employed!)

How to view interpolated values

You can visually inspect the values of the vector returned by INTERPOLATE by first running a formula then clicking on  the 'Formulas' tab and then on the 'Formula Data' tab.

Why we have missing values

Although the heavily traded companies should not have any missing values (except for stock market holidays or due to other closures such as the Sept-11-2001 attack on the US which kept the US stock  markets closed from Tuesday Sept-11-2001 to Friday Sept-14-2001), most companies do not trade every day, or they do not keep the same ticker symbol every day. Yes, you read correctly. The wise folks at NASDAQ came up with the "ingenious" idea to change the ticker symbol of any NASDAQ company, if that company is late for their quarterly report. In general NASDAQ traded companies, have 4-letter ticker symbols. If the company is late, then the ticker symbol gets the letter E added to its ticker symbol. Also, if the company is in bankruptcy proceedings it gets the letter Q added as a fifth letter. StockWiz does not keep track of these changes. StockWiz stores the data as they were reported on the date of the trade. This is a neutral position, since an investor may have his or her views on how to deal with such companies.