I'm running a predictive model. Over 60 months, every month I get a median value from a data set. To avoid look-ahead bias, I can only use the current and prior median values found up until this current month. I need to standardise the current median value every month to have a mean = 0 and std.dev = 1. The formula I use in excel is: =IFERROR((A1-AVERAGE(A$1:A1))/STDEV.P(A$1:N1),0) Since the average and stdev values used in the formula are not based on the entire sample of 60 months' data, the standardisation is not perfect. But how else can this be achieved if look-ahead bias is to be avoided?
2 Answers
$\begingroup$
$\endgroup$
6
I'm not an expert in Excel but it seems that if you establish a window of time across a fixed number of past periods -- e.g., one quarter or one year (3 and 12 periods, respectively) -- and roll that forward as a function of the current period, then you would have something that does not use the full 60 months of information. In initializing this "time window," you can choose to lose the first x months of your data or, if you choose to retain these "initialization" periods, recognize that those first months will likely have higher volatility.
Another suggestion, since you are analyzing median values, why use mean standard deviation? This paper on outlier detection proposes using the median absolute deviation (MAD) instead: http://www.sciencedirect.com/science/article/pii/S0022103113000668
As a moderator to this site noted on an earlier thread, it's a nonstandard approach. But at least it's consistent with the median values you're working with whereas mean standardizing is not.
Sorry @sjoerd, your answer was posted as I was writing mine...
answered Oct 25, 2015 at 10:57
-
$\begingroup$ Thanks for suggesting MAD. I will implement and take note of the improvement. As mentioned below, I do not use a fixed window, but grow the window as more data becomes available. Should I use a fixed window and roll it forward instead? $\endgroup$– NelusCommented Oct 25, 2015 at 11:57
-
$\begingroup$ If I understand you correctly by growing the window, you mean that with each successive period, you simply add another period to your window. So, if you're predicting time t, your window would be from 0 to t-1, and at time t+1, your window would be 0 to t and so on? Yeah, I wouldn't do that and would recommend including a fixed number of time periods in your window for each new t. $\endgroup$ Commented Oct 25, 2015 at 12:51
-
$\begingroup$ Correct, I did a few test runs - fixed period works much better. That clears it up. Thanks! $\endgroup$– NelusCommented Oct 25, 2015 at 13:26
-
$\begingroup$ @nelus Thanks for the "check" for a correct answer. How about a +1 too? (I'm new to CV and trying to amass points.) $\endgroup$ Commented Oct 28, 2015 at 9:48
-
$\begingroup$ Sure. I hope it worked. Doesn't show publicly yet. $\endgroup$– NelusCommented Oct 28, 2015 at 14:10
$\begingroup$
$\endgroup$
1
An alternative to your approach is to keep a rolling mean and standard deviation in memory which you use to update your mean and standard deviation over time as more information becomes available.
The advantage of this is that it may reduce fluctuations in the mean and standard deviation as more information becomes available, and therefore may better allow your model to account for this aspect.
Have a look at http://jonisalonen.com/2014/efficient-and-accurate-rolling-standard-deviation/
-
$\begingroup$ thanks for suggestion. I currently do update mean and std.dev as more data becomes available. I do not however use a rolling approach with fixed N. My N is incremented as more data becomes available. i.e.(x0,…,xN−1) to (x0,…,xN). and not (x0,…,xN−1) to (x1,…,xN). Is there any benefit to using rolling approach with fixed N as apposed to an ever growing sample set? $\endgroup$– NelusCommented Oct 25, 2015 at 11:53