Error while updating

[ajith]

Hello,

After a long time I am using Suneido for a new project. I wanted to calculate the exponential moving average of the price of a stock. The code and test table are atatched.
When I run CalculateEMA(‘BHARTIARTL’, “01/Nov/2010”, period:12, output:’calc1′ ),
the code is supposed to calculate EMA using certain values from previous record and soem from current records and then update the current record. The code works fine for first few records, but then starts giving wrong values. Using the Print() commands, it is seen that calculation is done correctly, but after updating the record in the QueryApplyMulti loop, the value stored is a different one. I am unable to discern how this happens. Hope you can help.

If the test table is used with the parameters above, the value calculated from the previous and current records would be 329.88 for date: #20101110. However, after updating, the value in the calc1 column becomes 329.83. Can you help confirm if the same result is displayed in other computers?
Thanks,
ajith

[amckinlay]

Hi Ajith,

Good to hear from you after so long! I hope you are well.

I ran it here. I think this is the part of the output you are looking for:

329.88 prev_EMA: 330.39 difference: -3.34 rec_price: 327.05 rec_date: #20101110

[macdhisto: -329.53, aroonup: 4, ema: 329.94, open: 335, aroondown: 60, high: 345.6, sma: 329.455, turnover: 34002.51, low: 325.55, date: #20101110, ttq: 10217605, ltp: 327, calc2: 329.47, serialno: 217, calc1: 329.83, macd: -.06, vol_sign: "neg", close: 327.05]

327.82 prev_EMA: 329.83 difference: -13.08 rec_price: 316.75 rec_date: #20101111

[macdhisto: -327.99, aroonup: 4, ema: 328.96, open: 325.45, aroondown: 100, high: 326.75, sma: 328.065, turnover: 22661.5, low: 316, date: #20101111, ttq: 7070523, ltp: 317.2, calc2: 326.93, serialno: 218, calc1: 327.86, macd: -1.06, vol_sign: "neg", close: 316.75]

It seems the same as what you are talking about.

I do not understand what it is doing exactly, but I can suggest some possible issues:

Doing a Query1 to get the previous record will use a separate transaction from the QueryApply, so it will get the record before the update (since the QueryApply transaction does not get committed till the very end). If you want it to get the new updated record then you need to do transaction.Query1 using the same transaction as the QueryApply. To do this you can wrap the loop in a Transaction block and use t.QueryApply and t.Query1. Or you can get the QueryApply transaction using rec.Transaction() e.g.

t = rec.Transaction()

prevrec = t.Query1(stockname, serialno: (rec.serialno -1))

If I do this, I get:

329.88 prev_EMA: 330.39 difference: -3.34 rec_price: 327.05 rec_date: #20101110

[macdhisto: -329.53, aroonup: 4, ema: 329.94, open: 335, aroondown: 60, high: 345.6, sma: 329.455, turnover: 34002.51, low: 325.55, date: #20101110, ttq: 10217605, ltp: 327, calc2: 329.47, serialno: 217, calc1: 329.88, macd: -.06, vol_sign: "neg", close: 327.05]

327.86 prev_EMA: 329.88 difference: -13.13 rec_price: 316.75 rec_date: #20101111

[macdhisto: -327.99, aroonup: 4, ema: 328.96, open: 325.45, aroondown: 100, high: 326.75, sma: 328.065, turnover: 22661.5, low: 316, date: #20101111, ttq: 7070523, ltp: 317.2, calc2: 326.93, serialno: 218, calc1: 327.86, macd: -1.06, vol_sign: "neg", close: 316.75]

But maybe you do not need to do Query1 at all? You can just have a variable for the previous record, and do prev_rec = rec at the end of the loop. Although this would not supply a prev_rec for the first loop.

I hope that helps.

[ajith]

Hello Andrew,

Thanks for the help. I am doing fine. I was doing my postgraduation and then job and family – no time to play around with computer. Hope you, your friends and family are doing good.

As you said, I stopped using Query1 inside the QueryApplyMulti and instead stored the previousEMA in the variable and the problem is corrected. The prevEMA for the first record was to be calculated in a different way and it could be done outside the loop.
Now the code is

	if TableExists?(stockname)

{

firstrec = QueryFirst(stockname $ ' where date >= ' $ Display(from ) $ ' and date <= ' $ Display(till) $ ' sort date ')

prevEMA = QueryMean(stockname $ ' where  serialno  > '$  Display((firstrec.serialno - period)-1) $ ' and serialno <= ' $ Display(firstrec.serialno-1), price)

QueryApply(stockname $ ' where date >= ' $ Display(from ) $ ' sort date ', update:true)

{|rec|

if (QueryCount(stockname $ ' where serialno <=' $ Display(rec.serialno)) > (period + 1))

{

ema = (((rec[price] - prevEMA) * multiplier) + prevEMA).Round(2)

}

else

ema = ""

Print(ema, rec_date: rec.date,rec_price: rec[price],prev_EMA: prevEMA,difference: (rec[price] - prevEMA))

rec[output] = ema

rec.Update()

prevEMA =ema

}

return QueryLast(stockname $  ' where date >= ' $ Display(from ) $ ' sort date').ema

}

.

Exponential Moving AVerages are calculated by giving different weightage to different member values. In case of the stock prices, that I am dealing with now, the price for the most recent date is given more weightage. The weightage factor calculated from the period that user supplies is multiplied [for a 5 period ema, the weightage would be 5/(1+2+3+4+5)] by the most recent price and 1-weightage is multiplied with the previous EMA. For the first record, there is no previous EMA and hence a simple average is calculated and is used in place of previous EMA. I was not sure how to deal with these calculations and so I had posted another thread on the forum – “Accessing previous record from current record”. Hope you will guide me on that also.

Thanks once again,
ajith

BTW, hello to ferguson and others at Suneido!

[Author]

Posts