4

I have a function quotes[ticker;startDate;endDate], and a function indexConstituents[index;startDate;endDate] that yield the below:

daterange: 2017.12.05,2017.12.06;

quotes'[AAPL;daterange]

date        time    sym    price
2017.12.05  09:45   AAPL   101.20
2017.12.06  09:45   AAPL   102.30

quotes'[GOOG;daterange]

date        time    sym    price
2017.12.05  10:00   GOOG   800.50

quotes'[BBRY;daterange]

date        time    sym    price
2017.12.06  11:15   BBRY   02.10

and

indexConstituents'[DJIA;daterange]

date        sym    shares   divisor
2017.12.05  AAPL   20       2
2017.12.05  GOOG   5        1
2017.12.06  AAPL   10       1.5
2017.12.06  BBRY   100      1

I need a way to run the indexConstituents function as normal to yield a list of constituents over a set of days (as in the second table above), then fetch the data from table 1 for each constituent. Finally, I need to join the data from both tables to yield the below:

data:
date       time     sym    price    shares    divisor
2017.12.05 09:45    AAPL   101.20   20        2
2017.12.06 09:45    AAPL   101.30   10        1.5
2017.12.05 10:00    GOOG   800.50   5         1
2017.12.06 11:15    BBRY   02.10    200       1

Code for the first two tables:

([] date:2017.12.05,2017.12.06; time:09:45,09:45; sym:`AAPL,`AAPL; price:101.20,102.30)

([] date:2017.12.05,2017.12.05,2017.12.06,2017.12.06; sym:`AAPL,`GOOG,`AAPL,`BBRY; shares:20f,5f,10f,100f; divisor:2f,1f,1.5f,1f)
Fomalhaut -C
  • 320
  • 2
  • 13
  • Both of those tables combined could never create that third table. Also this seems like a rehash of [this question](https://stackoverflow.com/questions/48628006/how-to-match-date-and-string-from-2-lists-kdb). Looks like the `lj` or `aj` join approach should still work in this case. – Thomas Smyth - Treliant Feb 08 '18 at 14:46
  • Possible duplicate of [How to match date and string from 2 lists (KDB)?](https://stackoverflow.com/questions/48628006/how-to-match-date-and-string-from-2-lists-kdb) – Thomas Smyth - Treliant Feb 08 '18 at 14:47
  • To expand on Thomas' first comment - you appear to have a lot more quotes in your final output than are in your initial input – Jonathon McMurray Feb 08 '18 at 14:48
  • The first function generates a table for a given ticker. I have a list of tickers I need to iterate over. Will add that to the question – Fomalhaut -C Feb 08 '18 at 14:49
  • Hey Fomalhaut - I noticed you're asking a few questions on kdb basics. I did a set of training videos from AquaQ, some of which are now free. Full disclosure: I do now work there, but this is still the training I did, and it was helpful, so you might want to check it out! https://training.aquaq.co.uk/kdb-taster – Ryan McCarron Feb 08 '18 at 15:11
  • Will do. I only went through "Q for Mortals" but am working all day, so little time to read when I have to deliver stuff using KDB >.<. Thanks!! – Fomalhaut -C Feb 08 '18 at 15:13

1 Answers1

2

I think the best approach is to assign the resultant table from indexConstituents'[DJIA;daterange] to a variable, so that we can then pull out the sym column and apply distinct to it.

You can then use that list of syms as your first argument to the quotes.

Finally join the two resultant tables together.

idx:indexConstituents'[DJIA;daterange];
q:quotes\:/:[distinct idx`sym;daterange];
q lj 2!idx

Hope this helps!

Jemma Borland
  • 390
  • 1
  • 7