CODEWITHSUNDEEP
kdb+
user1158959
user1158959

Reputation: 101

KDB/Q building a stock market index

I'm trying to build a stock index and I'm using Q for the purpose. As you can probably tell, I'm very new at this. Whereas I've built it in SQL, it takes way too long to process, hence my resorting to Q.

The setup so far is the following:

  1. The table; add data to the table, sort it:

    tickers:`ibm`bac`dis`gs`ibm`gs`dis`bac
pxs:100 50 30 250 110 240 45 48
dates:2013.05.01 2013.01.05 2013.02.03 2013.02.11 2013.06.17 2013.06.21 2013.04.24 2013.01.06
sectors:`auto`money`funny`money`auto`money`funny`money
trades:([sectors;tickers;dates];pxs)
`sectors`dates`tickers xasc `trades

  2. The processing so far (thanks to other friendly SO users):

    • I've calculated by how much has each stock price changes from the previous time the price was recorded

      trades: update delta:{0,1_deltas x}pxs by tickers from trades

    • I've calculated the total market value of the components of the sectorial index

      trades: update idxmv:sums[?[delta<>0;delta;pxs]] by sectors from trades

  3. How it looks:

    sectors tickers dates     | pxs delta idxmv
--------------------------| ---------------
auto    ibm     2013.05.01| 100 0     100
auto    ibm     2013.06.17| 110 10    110
funny   dis     2013.02.03| 30  0     30
funny   dis     2013.04.24| 45  15    45
money   bac     2013.01.05| 50  0     50
money   bac     2013.01.06| 48  -2    48
money   gs      2013.02.11| 250 0     298
money   gs      2013.06.21| 240 -10   288

  4. What I want to do:

    I want compute a sector index, that changes when its components change; in order to do this I need to compute a divisor column and the actual index column. The logic I'm trying to implement is as follows:

    • if delta != 0, then divisor = the last value of the divisor, in the same sector
    • if delta != 0, then index = idxmv % the value of the divisor computed above
    • if delta = 0, then index = the last value of the index, in the same sector
    • if delta = 0, then divisor = idxmv % the value of the index computed above, where the initial value of the index is 100; all the sector have a starting value of the index of 100

So essentially, what I want to end up with, is something similar to the below:

    sectors tickers dates     | pxs delta idxmv divisor index
    --------------------------| ------------------------------
    auto    ibm     2013.05.01| 100 0     100   1       100
    auto    ibm     2013.06.17| 110 10    110   1       110
    funny   dis     2013.02.03| 30  0     30    0.30    100
    funny   dis     2013.04.24| 45  15    45    0.30    150
    money   bac     2013.01.05| 50  0     50    0.50    100
    money   bac     2013.01.06| 48  -2    48    0.50    96
    money   gs      2013.02.11| 250 0     298   3.10    96
    money   gs      2013.06.21| 240 -10   288   3.10    92.78

Thanks for your help,

Dan

Upvotes: 1

Views: 652

Answers (1)

Akash
Akash

Reputation: 131

trades1:update index:{100f,1_count[x]#0n}[delta] by sectors from trades; / starting value of index as 100  
trades1:update divisor:?[delta=0;idxmv%index;0n] by sectors from trades1;  / divisor=y%z when delta=0  
trades1:update divisor:?[delta<>0;fills divisor;divisor] by sectors from trades1; / divisor=last divisor when delta <> 0  
trades1:update index:?[delta<>0;idxmv%divisor;index] by sectors from trades1; / index=i%d when delta <>0  
trades1:update index:?[delta=0;fills index;index] by sectors from trades1 / index=last index when delta=0  
trades1:update divisor:?[delta=0;idxmv%index;divisor] by sectors from trades1; /divisor=y%z when delta=0

This should solve your problem.
The last row doesn't seem to be matching.I have taken sectors as a primary key.You should be able to match the last row by changing the primary key to sectors/tickers.

Upvotes: 2

Related Questions