I am trying to do a calculation for a stochastic oscillator on a series of stock prices in a database (SQL Server 2014). The calculation I am trying to do is as follows:
- Work out the lowest low for the last 14 periods (including the current one)
- Calculate the raw value by: 100 * (CurrentClose-LowestLow) / (CurrentHigh-LowestLow)
- Return field k as the average of the raw value for the last 3 periods (including the current one)
- Return field d as the average of field k for the last 3 periods (including the current one)
Here is some test data:
High Lowest Close rawK K D
7.39 7.251 7.33 56.83453237
7.38 7.251 7.282 24.03100775
7.432 7.251 7.3 27.0718232 35.97912111
7.38 7.203 7.38 100 50.36761032
7.35 7.203 7.214 7.482993197 44.85160547 43.73277897
7.349 7.203 7.349 100 69.16099773 54.79340451
7.399 7.203 7.3 49.48979592 52.32426304 55.44562208
7.501 7.203 7.501 100 83.16326531 68.21617536
7.549 7.203 7.49 82.94797688 77.4792576 70.98892865
7.649 7.203 7.649 100 94.31599229 84.98617173
7.609 7.203 7.609 100 94.31599229 88.70374739
7.504 7.203 7.473 89.70099668 96.56699889 95.06632783
7.688 7.203 7.483 57.73195876 82.47765181 91.12021433
7.558 7.203 7.558 100 82.47765181 87.17410084
Because I am trying to calculate this on a database table it would be great if I could calculate the moving average k and the moving average of the moving average d with just one pass of the data.
I'm guessing I can calculate k as a cumulative average, but how do I calculate a cumulative average of a cumulative average with just the raw data?