In a data set with thousands of data points, I am testing different short-term and longer term data outputs based on 5 rolling data points all the way to 100 rolling data points (which each value being a separate column in excel: 5, 6,..., 100).
The test I developed (with rudimentary knowledge of the whole thing) is to check which are the better "fits" of these rolling data outputs (kinda like moving averages, but not quite), in other words, if column 5, 17 or 98. In theory, the better fits should produce lower standard deviations, in the conventional calculation.
An analogy for better understanding is something like this: if I do a 5 point moving average on a five period sin wave, the output should be zero or flat, the same for a 6 point moving average on a six period sin wave, and so on all the way to 100. This is the same as saying that the standard deviation in each case should be zero.
So the test really is choosing the lowest standard deviation in each column from 5 all the way to 100, which should then provide the best "fit" (to go back to the analogy, fit with the "sin wave").
However, as the statistical inclined will quickly note (am afraid am not one of them for sure), this method presents two major difficulties:
- The longer data sets, ie closer to 100 rolling data points, have larger absolute deviations, thus producing higher standard deviations. So a lower standard deviation in the shorter data series may not be indicative of better "fitting", just lower absolute deviations;
- Each column has a different number of rolling data points, so once again the numbers and statistical fit may not be comparable between the columns.
So my question is therefore: is there a statistical measure better than the standard deviation I used above, such that it is not sensitive to absolute deviations and number of data points?