Play around with new windowing functions

Im not an developer but off courseI like to try new features and get knowledge in the transact language anyway. I have been interested in stocks for many years and was playing around with Excel to do some simple classic stock analysis. Then it come to my mind if these calculations is possible direct in SQL.

Here was a must try scenario to learn some more transact. In my example I have a simple table with date, high, low, close columns. The data is from OMX30 index beginning from year 2005 to now.

In the first case I would like to calculate Moving average for a period of 200 days. One of the most simple analysis in stock analysis are when the price pass over 200 days moving average there is a buy situation. When going down under this value you shall sell. If its tru or not I don’t know. But it’s a good indication if the market is positive or negative.

How can we use the new windowing function in transact then? Here is an example:

SELECT id, stock, date, close

,AVG(close) OVER (PARTITION BY stock ORDER BY date ROWS BETWEEN 199 PRECEDING AND CURRENT ROW) as  '200 SMA'

Really simple. Actually I don’t even know how to do when you don´t have this grate functions. Here is the result.

Stock         Date         Close        200 SMA

omx30     2013-01-28 1160.25 1057.748500

I will investigate those function more and try use them for more advanced calculations. That would be another post.