Updating Tickdata Table

Community Support
1

Hello,

I’m trying to backtest a trading strategy and I need help to go ahead:

I have a tick data table:

datetime bidprice askprice Date Week
---------------------------------------------------------------
2010.05.01T18:17:37.000 1.32875 1.32975 2010.05.01 2010.05.01
2010.05.02T05:05:36.000 1.3289 1.3299 2010.05.02 2010.05.01
2010.05.02T18:19:49.000 1.32865 1.33015 2010.05.02 2010.05.01
2010.05.02T18:19:58.000 1.32895 1.33045 2010.05.02 2010.05.01
2010.05.02T18:30:50.000 1.3287 1.3307 2010.05.02 2010.05.01
2010.05.02T18:30:59.000 1.3296 1.3316 2010.05.02 2010.05.01
2010.05.02T18:32:05.000 1.3299 1.3319 2010.05.02 2010.05.01
2010.05.02T18:44:00.000 1.3304 1.3324 2010.05.02 2010.05.01
2010.05.02T18:54:05.000 1.3324 1.3344 2010.05.02 2010.05.01
2010.05.02T18:54:15.000 1.33125 1.33325 2010.05.02 2010.05.01

 

And a daily Table:

Date Open Low Close Trade EntryLevel
---------------------------------------------------
2010.05.01 1.32875 1.32875 1.32875
2010.05.02 1.3289 1.32865 1.33121 0 0
2010.05.03 1.33113 1.3154 1.31955 0 0
2010.05.04 1.31953 1.29615 1.29774 -1 1.31913
2010.05.05 1.29769 1.27886 1.2816 0 0
2010.05.06 1.28163 1.25148 1.2654 0 0
2010.05.07 1.2654 1.25852 1.27524 0 0
2010.05.08 1.27574 1.27574 1.27594 1 1.27614
2010.05.09 1.27604 1.275 1.28696 0 0
2010.05.10 1.28664 1.27221 1.2737 0 0
2010.05.11 1.27373 1.2616 1.26396 -1 1.27333
2010.05.12 1.26398 1.26054 1.26452 0 0
2010.05.13 1.26446 1.25163 1.25319 1 1.26486
2010.05.14 1.25319 1.23492 1.23578 -1 1.25279
2010.05.15 1.235 1.235 1.235 0 0
2010.05.16 1.23475 1.23338 1.23585 0 0
2010.05.17 1.23587 1.22339 1.23825 0 0
2010.05.18 1.23827 1.21439 1.21448 0 0
2010.05.19 1.21445 1.21438 1.23842 -1 1.21405
2010.05.20 1.23836 1.22954 1.25398 1 1.23876

When Trade=1 > Send a Buy Order (expiration End of Day) at EntryLevel

I need to add a column FirstEntry to the tick table so FirstEntry=1  the first time askprice>=Entrylevel and Trade=1 in a daily basis   from daily table.

How should be the query?

Thanks a lot

Francisco

2

does this work ?

update FirstEntry:1 from (update f:sums askprice>=EntryLevel by Date from quote lj `Date xkey select Date,Trade,EntryLevel from daily) where Trade=1,f=1?

regards

P

3

Thanks for your help.

I tried your query and it’s almost right!

As you can see in my daily Table, first day where Trade=1 was 2010.05.08 (incidentally a Saturday with very low activity), here is the result of your query that day:

datetime ? ? ? ? ? ? ? ?bidprice askprice Date ? ? ? Week ? ? ? Trade EntryLevel f FirstEntry
---------------------------------------------------------------------------------------------
2010.05.08T23:45:09.000 1.27574 ?1.27674 ?2010.05.08 2010.05.08 1 ? ? 1.27614 ? ?1 1 ? ? ? ??
2010.05.08T23:45:48.000 1.27584 ?1.27684 ?2010.05.08 2010.05.08 1 ? ? 1.27614 ? ?2 ? ? ? ? ??
2010.05.08T23:50:06.000 1.27594 ?1.27694 ?2010.05.08 2010.05.08 1 ? ? 1.27614 ? ?3 ? ? ? ? ??

Next day where Trade=1 was 2010.05.13. Below I extracted the interesting parts of that day:

datetime ? ? ? ? ? ? ? ?bidprice askprice Date ? ? ? Week ? ? ? Trade EntryLevel f ? ? FirstEntry
-------------------------------------------------------------------------------------------------
2010.05.12T23:59:53.000 1.2645 ? 1.26459 ?2010.05.12 2010.05.08 0 ? ? 0 ? ? ? ? ?73018 ? ? ? ? ??
2010.05.12T23:59:57.000 1.26452 ?1.26461 ?2010.05.12 2010.05.08 0 ? ? 0 ? ? ? ? ?73019 ? ? ? ? ??
2010.05.13T00:00:01.000 1.26446 ?1.26455 ?2010.05.13 2010.05.08 1 ? ? 1.26486 ? ?0 ? ? ? ? ? ? ??
2010.05.13T00:00:01.000 1.26448 ?1.26457 ?2010.05.13 2010.05.08 1 ? ? 1.26486 ? ?0 ? ? ? ? ? ? ??
2010.05.13T00:00:01.000 1.2645 ? 1.26459 ?2010.05.13 2010.05.08 1 ? ? 1.26486 ? ?0 ? ? ? ? ? ? ??
2010.05.13T00:00:27.000 1.26449 ?1.26458 ?2010.05.13 2010.05.08 1 ? ? 1.26486 ? ?0 ? ? ? ? ? ? ??
2010.05.13T00:00:27.000 1.26453 ?1.26462 ?2010.05.13 2010.05.08 1 ? ? 1.26486 ? ?0 ??
..
2010.05.13T00:14:14.000 1.26474 ?1.26483 ?2010.05.13 2010.05.08 1 ? ? 1.26486 ? ?0 ? ? ? ? ??
2010.05.13T00:14:14.000 1.26476 ?1.26485 ?2010.05.13 2010.05.08 1 ? ? 1.26486 ? ?0 ? ? ? ? ??
2010.05.13T00:14:14.000 1.26476 ?1.26485 ?2010.05.13 2010.05.08 1 ? ? 1.26486 ? ?0 ? ? ? ? ??
2010.05.13T00:14:14.000 1.26478 ?1.26487 ?2010.05.13 2010.05.08 1 ? ? 1.26486 ? ?1 1 ? ? ? ??
2010.05.13T00:14:38.000 1.26469 ?1.26478 ?2010.05.13 2010.05.08 1 ? ? 1.26486 ? ?1 1 ? ? ? ??
2010.05.13T00:14:38.000 1.26471 ?1.2648 ? 2010.05.13 2010.05.08 1 ? ? 1.26486 ? ?1 1 ? ? ? ??
2010.05.13T00:14:38.000 1.26474 ?1.26483 ?2010.05.13 2010.05.08 1 ? ? 1.26486 ? ?1 1 ? ? ? ??
2010.05.13T00:14:39.000 1.26467 ?1.26476 ?2010.05.13 2010.05.08 1 ? ? 1.26486 ? ?1 1 ? ? ? ??
2010.05.13T00:14:40.000 1.26464 ?1.26473 ?2010.05.13 2010.05.08 1 ? ? 1.26486 ? ?1 1 ? ? ? ??
2010.05.13T00:14:41.000 1.2646 ? 1.26469 ?2010.05.13 2010.05.08 1 ? ? 1.26486 ? ?1 1 ? ? ? ??
..
2010.05.13T00:31:51.000 1.26466 ?1.26475 ?2010.05.13 2010.05.08 1 ? ? 1.26486 ? ?1 1 ? ? ? ??
2010.05.13T00:31:56.000 1.26468 ?1.26477 ?2010.05.13 2010.05.08 1 ? ? 1.26486 ? ?1 1 ? ? ? ??
2010.05.13T00:32:01.000 1.26471 ?1.2648 ? 2010.05.13 2010.05.08 1 ? ? 1.26486 ? ?1 1 ? ? ? ??
2010.05.13T00:32:01.000 1.26474 ?1.26483 ?2010.05.13 2010.05.08 1 ? ? 1.26486 ? ?1 1 ? ? ? ??
2010.05.13T00:32:01.000 1.26477 ?1.26486 ?2010.05.13 2010.05.08 1 ? ? 1.26486 ? ?2 ? ? ? ? ??
2010.05.13T00:32:01.000 1.26479 ?1.26488 ?2010.05.13 2010.05.08 1 ? ? 1.26486 ? ?3 ? ? ? ? ??
2010.05.13T00:32:08.000 1.26476 ?1.26485 ?2010.05.13 2010.05.08 1 ? ? 1.26486 ? ?3 ? ? ? ? ??
2010.05.13T00:32:09.000 1.26471 ?1.2648 ? 2010.05.13 2010.05.08 1 ? ? 1.26486 ? ?3 ? ? ? ? ??
2010.05.13T00:32:09.000 1.26474 ?1.26483 ?2010.05.13 2010.05.08 1 ? ? 1.26486 ? ?3 ? ? ? ? ??

4

update FirstEntry:1 from (update f:(first i where askprice>=EntryLevel) by Date from quote lj `Date xkey select Date,Trade,EntryLevel from daily) where Trade=1,f=i

regards

P

5

Yes!, now it works well! Thanks.

I extracted from tick data table a keyed table with weekly open:

<font face='"courier' new monospace>Date | Open ----------| -------2010.05.01| 1.328752010.05.08| 1.275742010.05.15| 1.235 2010.05.22| 1.257042010.05.29| 1.226382010.06.05| 1.196032010.06.12| 1.210612010.06.19| 1.238 2010.06.26| 1.236942010.07.03| 1.255852010.07.10| 1.263272010.07.17| 1.2922 2010.07.24| 1.291052010.07.31| 1.304512010.08.07| 1.3277 </font>



<font face='"arial,' sans-serif>How can I filter that FirstEntry column in tickdata table so only when EntryLevel &gt; Weekly Open then FirstEntry=1 ?</font>

<font face='"arial,' sans-serif><br></font>

<font face='"arial,' sans-serif>Cheers</font>

<font face='"arial,' sans-serif><br></font>

<font face='"arial,' sans-serif><br></font>

<font face='"arial,' sans-serif>Francisco</font>

On Sunday, June 10, 2012 7:20:03 PM UTC+2, Max_18 wrote:

update FirstEntry:1 from (update f:(first i where askprice>=EntryLevel) by Date from quote lj `Date xkey select Date,Trade,EntryLevel from daily) where Trade=1,f=i

regards

P