2

I'm pulling data from a source that returns tick data for stocks (timespan + float prices).

I need to build 1 table that has the tick data for each stock, while inserting new timespan index values for each one. Example:

AAPL:
t0    101.20
t3    102.10

GOOG:
t1    850.50
t2    860.10

Table:
    AAPL    GOOG
t0  101.20  NA
t1  NA      850.50
t2  NA      860.10
t3  102.10  NA

There would be many symbols, so I can't just manually type AAPL, GOOG etc.

Thomas Smyth - Treliant
  • 4,993
  • 6
  • 25
  • 36
Fomalhaut -C
  • 320
  • 2
  • 13

1 Answers1

1

While it would be possible to set up a table like you have described it would not be advisable. You would be better to set up a column to record each stock, sym in this case:

t                             sym  price
-------------------------------------------
2018.02.05D14:11:09.241245000 AAPL 101.7808
2018.02.05D14:11:09.241246000 GOOG 103.0177
2018.02.05D14:11:09.241246000 AAPL 107.8503
2018.02.05D14:11:09.241247000 GOOG 105.3471
Thomas Smyth - Treliant
  • 4,993
  • 6
  • 25
  • 36
  • I specifically avoided asking that question because my next step is to construct an index from all the stocks. How can I insert a column to a table like the one you described that only has index values once all the stocks have a given tick value? In the above example, the index would start at T1 (once both AAPL and GOOG have a recorded trade). – Fomalhaut -C Feb 05 '18 at 14:15
  • You might be able to use something like this: `update index:1b from trades where i>=max(first;i)fby sym`. That should identify valid rows. – Thomas Smyth - Treliant Feb 05 '18 at 14:33
  • One more given the different solution: rather than 1b, I want the index value to be based on the latest price for each stock. Basically, sum the latest value of each ticker once the index is complete (1b from above). Any idea how? – Fomalhaut -C Feb 05 '18 at 16:45
  • 1
    Here's one example: `delete ind from update index:sum@'ind from (update ind:{x[y]:z;:x}\[()!();sym;price] from trades) where i>=max(first;i)fby sym`. This uses a scan to create a dictionary of the latest price for each sym and then uses an update to sum this only once all tickers have ticked, and then deletes the intermediate `ind` column – Jonathon McMurray Feb 05 '18 at 16:59
  • Thanks for your help guys. I'm posting another thread shortly as this is aside the topic. – Fomalhaut -C Feb 05 '18 at 18:29