Accessing previous record from current record

[ajith]

Hello,
I wanted to calculate exponential moving averages and few other parameters for the value of stocks. there is no real need to store the values. Howeever, the calculation involves accessing theprevious record. The key field for the table is date. Howeveer, the previous record does not mean the previous date (as stock market may not have worked on that date). Which dates to skip is also not known before hand as there are some random holidays other than weekends.
I am currently managing by (1) adding a index column carrying a serial number and then (2) using a function built on QueryApplyMulti to calculate the values and then storing them in columns. Howeever, this will become costly in terms of computation time and storage space when the program actually runs with data of atleast a hundres stocks.
So, I want to know
1) Is there a way to access the previous record of the same table from a Rule for a field?
2) If so, would it be a better option to build a rule and leave it as a calculated column rather than storing it?
3) How do I find a record (while inside a QueryApply loop), say 10 up of my current record (when the query is sorted using the key field) ?
4) Is there a better way to do this (say for repeatedly calculating for each row, the sum of the 10 preceeding values of the column of interest ) other than using QueryApply?
Thanks,
ajith

[amckinlay]

1) You can get the previous record with something like:

prev = QueryLast("mytable where date < " $ Display(currrent_date) $ " sort date")

Note: "previous" only makes sense when you specify a sort, otherwise Suneido doesn't guarantee any particular order.

If you wanted to get multiple previous records you could use the same query and read Prev multiple times.

2) I would leave it as a calculated column unless speed becomes a problem.

3) see (1)

4) if the data is small enough (e.g. less than 10,000 records) then it may be easier to read the data into memory and work with it there. Then you do not need to do queries to get previous records.

[ajith]

Hello,

Thanks! I will try using calculated fields.
ajith

[Author]

Posts