Hello:
I’m trying to add a column holding the median of the last 3 weekly ranges (High-Low) to a weekly data trading table:
So I first extracted the Open, High, Low and Close data from the tick table:
EU_1112_W_GMT_SAT:select WOpen:first bidprice,WHigh:max bidprice,WLow:min bidprice,WClose:last bidprice by 7 xbar datetime from EU_1112GMT
The resulting table:
<font face='"courier' new monospace>datetime | WOpen WHigh WLow WClose -----------------------| -------------------------------2011.01.01T00:00:00.000| 1.33771 1.34333 1.29006 1.290232011.01.08T00:00:00.000| 1.29043 1.34561 1.28583 1.338152011.01.15T00:00:00.000| 1.33837 1.3624 1.32436 1.361322011.01.22T00:00:00.000| 1.36122 1.37582 1.35404 1.360612011.01.29T00:00:00.000| 1.36061 1.38612 1.35432 1.357592011.02.05T00:00:00.000| 1.35759 1.37437 1.3497 1.354642011.02.12T00:00:00.000| 1.35464 1.37145 1.34286 1.36881...</font>
<font face='"courier' new monospace><br></font>
<font face='"courier' new monospace>Then I've added the range Column:</font>
<font face='"courier' new monospace><br></font>
<font color='"#000000"' face='"courier' new monospace>EU_1112_W_GMT_SAT:update W0Range:WHigh-WLow from EU_1112_W_GMT_SAT<br></font>
<font color='"#000000"' face='"courier' new monospace><br></font>
<font face='"courier' new monospace></font>
<font face='"courier' new monospace>datetime | WOpen WHigh WLow WClose W0Range-----------------------| ---------------------------------------2011.01.01T00:00:00.000| 1.33771 1.34333 1.29006 1.29023 0.053272011.01.08T00:00:00.000| 1.29043 1.34561 1.28583 1.33815 0.059782011.01.15T00:00:00.000| 1.33837 1.3624 1.32436 1.36132 0.038042011.01.22T00:00:00.000| 1.36122 1.37582 1.35404 1.36061 0.021782011.01.29T00:00:00.000| 1.36061 1.38612 1.35432 1.35759 0.0318 2011.02.05T00:00:00.000| 1.35759 1.37437 1.3497 1.35464 0.024672011.02.12T00:00:00.000| 1.35464 1.37145 1.34286 1.36881 0.02859</font>
<font face='"courier' new monospace>...</font>
<font face='"courier' new monospace><br></font>
<font face='"courier' new monospace>Now I calculate the median of the last 3 weeks range:</font>
<font face='"courier' new monospace><br></font>
<font face='"courier' new monospace><font color='"#000000"'>EU_1112_W_GMT_SAT:update Median3Range:med(W1Range;W2Range;W3Range) from update W3Range:prev W2Range from update W2Range:prev W1Range from update W1Range:prev W0Range from EU_1112_W_GMT_SAT</font><span style='"color:' rgb></span></font>
<font color='"#000000"' face='"courier' new monospace><br></font>
<font color='"#000000"' face='"courier' new monospace>But the resulting column has wrong results (Right calculation: Green. Wrong calculation: Red)</font>
<font color='"#000000"' face='"courier' new monospace><br></font>
<font face='"courier' new monospace></font>
<font face='"courier' new monospace><span style='"color:' rgb>datetime | WOpen WHigh WLow WClose W0Range W1Range W2Range W3Range Median3Range-----------------------| ----------------------------------------------------------------------------2011.01.01T00:00:00.000| 1.33771 1.34333 1.29006 1.29023 0.05327 2011.01.08T00:00:00.000| 1.29043 1.34561 1.28583 1.33815 0.05978 0.05327 2011.01.15T00:00:00.000| 1.33837 1.3624 1.32436 1.36132 0.03804 0.05978 0.05327 0.05327 2011.01.22T00:00:00.000| 1.36122 1.37582 1.35404 1.36061 0.02178 0.03804 0.05978 0.05327 </span><font color='"#ff0000"'>0.05978</font><font color='"#000000"'> 2011.01.29T00:00:00.000| 1.36061 1.38612 1.35432 1.35759 0.0318 0.02178 0.03804 0.05978 </font><font color='"#00ff00"'>0.03804 </font><font color='"#000000"'> 2011.02.05T00:00:00.000| 1.35759 1.37437 1.3497 1.35464 0.02467 0.0318 0.02178 0.03804 </font><font color='"#ff0000"'>0.02178</font><font color='"#000000"'> 2011.02.12T00:00:00.000| 1.35464 1.37145 1.34286 1.36881 0.02859 0.02467 0.0318 0.02178 </font><font color='"#ff0000"'>0.0318 </font><font color='"#000000"'> </font></font>
<font face='"courier' new monospace><font color='"#000000"'><br></font></font>
<font face='"courier' new monospace><font color='"#000000"'>Calculating the median in another way i get the same (wrong) results:</font></font>
<font face='"courier' new monospace><font color='"#000000"'><br></font></font>
<font face='"courier' new monospace><font color='"#000000"'>EU_1112_W_GMT_SAT:update Median3RangeBis:med(W1Range;W2Range;W3Range) from EU_1112_W_GMT_SAT</font></font>
<font face='"courier' new monospace><font color='"#000000"'><br></font></font>
datetime | WOpen WHigh WLow WClose W0Range W1Range W2Range W3Range Median3Range Median3RangeBis-----------------------| --------------------------------------------------------------------------------------------2011.01.01T00:00:00.000| 1.33771 1.34333 1.29006 1.29023 0.05327 2011.01.08T00:00:00.000| 1.29043 1.34561 1.28583 1.33815 0.05978 0.05327 2011.01.15T00:00:00.000| 1.33837 1.3624 1.32436 1.36132 0.03804 0.05978 0.05327 0.05327 0.05327 2011.01.22T00:00:00.000| 1.36122 1.37582 1.35404 1.36061 0.02178 0.03804 0.05978 0.05327 0.05978 0.05978 2011.01.29T00:00:00.000| 1.36061 1.38612 1.35432 1.35759 0.0318 0.02178 0.03804 0.05978 0.03804 0.03804 2011.02.05T00:00:00.000| 1.35759 1.37437 1.3497 1.35464 0.02467 0.0318 0.02178 0.03804 0.02178 0.02178 2011.02.12T00:00:00.000| 1.35464 1.37145 1.34286 1.36881 0.02859 0.02467 0.0318 0.02178 0.0318 0.0318
<font face='"courier' new monospace><font color='"#000000"'><br></font></font>
<font face='"courier' new monospace><font color='"#000000"'><br></font></font>
<font face='"courier' new monospace></font>
<font face='"courier' new monospace><br></font>
<font face='"courier' new monospace>But med function calculates the correct value:</font>
<font face='"courier' new monospace><br></font>
<font face='"courier' new monospace>q)med 0.02467 0.0318 0.021780.02467q)<br></font>
<font face='"courier' new monospace><br></font>
Any hint please?
<font face='"courier' new monospace><br></font>
<font face='"courier' new monospace>Cheers</font>
<font face='"courier' new monospace><br></font>
<font face='"courier' new monospace>Francisco</font>