Learning Kdb+/q

[frang0nve]

EDITED: Date format was DD/MM/YY - Corrected


I'm learning Kdb+/q and I'd like to post here my steps so perhaps some traders/coders find them useful and/or interesting and we can help us each other.

Kdb+ is a column oriented database designed for massive datasets. Kdb+ includes q, a SQL-like vector processing language that can access data directly.

Kdb+ is used by financial institutions to capture, store and analyze massive amounts of time sensitive data.

I´m using the 32 bits trial software available in http://kx.com for MSWindows, Linux, Solaris and OSX.

Limitations of trial software are:

May not be used in production environments.
Times out every two hours.
Expires every two/three months.

Due to the second limitation, data should be saved often, but otherwise I found this software very interesting, compact and usable.

My target is tick backtesting TRO strategy "The Rat Adapts" as described here:

http://kreslik.com/forums/viewtopic.php?t=2904&start=0

I'm using EURUSD tickdata supplied in CSV format:

[s]MM/DD/YY hh:mm:ss,BidPrice,AskPrice[/s]
DD/MM/YY hh:mm:ss,BidPrice,AskPrice

02/01/05 18:29:14,9.99999,9.99999

First step after installing Kdb+ in the PC is starting a Kdb+/q session.

I'm using MSWindows 7 so a MSDOS window has to be opened and the active directory has to be changed to the Kdb+ folder (default C:\q)

Then to start a session type "w32\q" at the MS/DOS prompt.

A welcome message will be shown.



If there is somebody interested in this thread, please post below, and we can share ideas

Cheers

Francisco

[frang0nve]

EDITED: Date format was DD/MM/YY - Corrected

We'll select the DD/MM/YY mode:

[font=Courier New]\z 1[/font]

Then we'll import tickdata (csv format) into a q table:

[font=Courier New].Q.fs[{`EU insert flip `datetime`bidprice`askprice!("ZFF";",")0:x}]`:EU10.csv[/font]





Now with the \p command we open a http port:

[font=Courier New]\p 5001[/font]



So we can see and navigate the table using a web browser:





Now we save the table in disk and close the q session:

[font=Courier New]save `:EU

\\[/font]





Cheers

[frang0nve]

After starting a new q sessión, we load the table from disk into memory and then we open a http port:

[font=Courier New]load `:EU

\p 5001[/font]



As we want to evaluate a strategy based on daily data, we extract daily information (OHLC) from tickdata:

[font=Courier New]EU_D1:select Open:first bidprice,High:max bidprice,Low:min bidprice,Close:last bidprice by 1 xbar datetime from EU[/font]



Now we save this daily table:

[font=Courier New]save `:EU_D1[/font]



We can see the daily table using the web browser:



Cheers

[frang0nve]

As the strategy is based on the colour of the daily bars, we add a column with these values: 1=Green Bar, -1=Red Bar, 0= Flat Bar using this command:



EU_D1:update Colour:signum Close-Open from EU_D1

Resulting this table:



The strategy states that after a daily green bar following a red daily bar we buy at Open+4 pips and after a daily red bar following a green daily bar we sell at Open-4 pips.

So we add the column NewRat where buying days are marked 1, selling days are marked -1 and inactive days are marked 0:

EU_D1: update NewRat:prev(deltas signum Close-Open)div 2 from EU_D1

Resulting this table:



Now we save the table into disk and close the q session.

Cheers

[frang0nve]

UPDATED: Bug calculating SLLevel-> Command corrected


After opening the q session, the http port and loading the daily data table, we'll add a column to the table with the daily entry level (Open+4 pips when buying, Open-4 pips when shorting) and the Stop Loss level (10 pips):

[font=Courier New]Threshold:0.0004
StopLoss:0.001
[s]EU_D1:update EntryLevel:(abs NewRat)*Open+Threshold*NewRat,SLLevel:(abs NewRat)*Open-StopLoss*NewRat from EU_D1[/s]

EU_D1:update EntryLevel:(abs NewRat)*Open+Threshold*NewRat,SLLevel:EntryLevel-StopLoss*NewRat from EU_D1[/font]



Here is the updated table:



We can save the table and close the q session

Cheers

[frang0nve]

Now things become trickier, as the take profit description for this strategy is: "Take whatever profit you can"

That can be achieved using an exits playbook like this:

SL is moved forward:
If profit = 5 pips move SL to entry level+3 pips
If profit = 10 pips move SL to entry level+5 pips
If profit = 15 pips move SL to entry level+10 pips
If profit >= 20 pips SL moves to entry level+0.5*max profit

I'm thinking how to code these rules in q.

Cheers

[frang0nve]

There is another rule in the strategy using weekly data:

The RAT only goes LONG when price is ABOVE the weekly open and only goes SHORT when price is BELOW the weekly open.

I'll select saturday as first day in the week (my broker allows 7 days/week trading) using the \W 0 command:

The week offset command \W (note upper case) specifies the start of week offset. An offset of 0 corresponds to Saturday. The default is 2, which is Monday.


To use weekly open we extract the information from tick data table EU:

EU_W1:select Open:first bidprice by 7 xbar datetime from EU



And now we can see the table in the web browser:



We can save the table to disk using the command:

save `:EU_W1


Cheers

[harryjeesu]

atoms:
q)sym: `ibm
List:
q)price: 10.2 30.0 40.4
q)syms:`ibm`msft`goog
q)volume: 1200 1100 2312
q)(syms;price;volume)
ibm msft goog
10 30 40
1200 1100 2312

dictionary:
q)dict:`syms`price`volume!(syms;price;volume) /dictionary
q)dict
syms | ibm msft goog
price | 10 30 40
volume| 1200 1100 2312

table:
q)table: flip dict
q)table
syms price volume
-----------------
ibm 10 1200
msft 30 1100
goog 40 2312

converting into key table:
q)1!table
syms| price volume
----| ------------
ibm | 10 1200
msft| 30 1100
goog| 40 2312

q)2!table
syms price| volume
----------| ------
ibm 10 | 1200
msft 30 | 1100
goog 40 | 2312

[harryjeesu]

Creating a table:
q)trade : ([]sym: (); price: (); volume: `int$())

show table
q)trade
sym price volume
----------------

Ways to insert data into tables:

q)insert[`trade](`aaa;53.75;1200)
,0 --------> / row added i.e first row
q)trade
sym price volume
----------------
aaa 53.75 1200


q)insert[`trade;(`bbb;10.45;1000)]
,1 ----------> row added i.e. second row
q)show trade
sym price volume
----------------
aaa 53.75 1200
bbb 10.45 1000


q)`trade insert(`ccc;59.50;1000)
,2 ----------> row added i.e 3rd row
q)trade
sym price volume
----------------
aaa 53.75 1200
bbb 10.45 1000
ccc 59.5 1000

bulk insertion:

q)stock: `aaa`bbb`ccc`ddd`eee`fff`ggg`hhh`iii`jjj`kkk`lll`mmm
q)n: 20
q)trade:([]sym: n?stock;price: n?50.0; volume: 100*10+n?10)
q)trade
sym price volume
--------------------
hhh 4.061773 1800
iii 46.83752 1100
fff 13.91061 1900
ggg 11.96171 1500
kkk 7.540665 1400
eee 7.836585 1600
mmm 48.925 1600
bbb 35.21657 1100
mmm 47.20835 1800
ddd 39.16843 1500
ddd 20.49781 1400
hhh 30.54409 1900
iii 24.88246 1200
ccc 20.43772 1700
bbb 22.48655 1000
eee 0.6960381 1100
ccc 35.7439 1900
iii 9.732546 1200
aaa 4.529513 1100
mmm 31.01507 1800

Converting a table into key table:

q)1 !trade
sym| price volume
---| ----------------
hhh| 4.061773 1800
iii| 46.83752 1100
fff| 13.91061 1900
ggg| 11.96171 1500
kkk| 7.540665 1400
eee| 7.836585 1600
mmm| 48.925 1600
bbb| 35.21657 1100
mmm| 47.20835 1800
ddd| 39.16843 1500
ddd| 20.49781 1400
hhh| 30.54409 1900
iii| 24.88246 1200
ccc| 20.43772 1700
bbb| 22.48655 1000
eee| 0.6960381 1100
ccc| 35.7439 1900
iii| 9.732546 1200
aaa| 4.529513 1100
mmm| 31.01507 1800

[frang0nve]

Hello and welcome.

Do you have experience with q?

Cheers