Stock Charts - Ingesting NYSE Stock Excange real-time data

Redis modules RedisTimeSeries
1

Hello, thank you for the amazing work!

I recieve New York Stock Exchange trades from a websocket connection in this format:
[Timestamp, Symbol, Price, Size]

``

Timestamp - milisecond unix

Symbol - name of the stock (eg AAPL, MSFT, GOOGL… )

Price - price at which the trade got executed (double)
Size - quantity of shares in the trade (integer)

Example feed from the market:

[1580503106159, “AAPL”, 234.56, 1357]

[1580503106235, “GOOGL”, 899.56, 432]

[1580503106256, “MSFT”, 625.56, 44]
[1580503106304, “AAPL”, 234.78, 67888]

[1580503106304, "AAPL", 232.28, 5378]

What I would like to do is ingest all of the feed into redis time series (2 min of retention) using the timestamp provided from the exchange (its guaranteed to be of incremental value). The goal is to be able to query the time series for particular stock aggregations in OHLCV:
give me Open, High, Low, Close, Volume on 1 minute time bucket

time = minute of the time bucket
Open = first(Price)
High= max(Price)
Low= min(Price)
Close= last(Price)
Volume= sum(Size)

``

If we are to aggregate AAPL trades from te example above, this would be the result:

time=1580485107000
Open = 234.56
High= 234.78
Low= 232.28
Close= 234.78
Volume= ‭74623‬

``

I cant figure out how to model the database, particularly how to use keys, labels and values. Do I insert every trade in a single key called “trades” and use label/value to point to the stock name? Do I need a separate key for each stock? Do I use keys or labels for Price and Size? Any help on this matter is greately appreatiated.

Kind Regards

2

Hi there Damian, good night,
With regards to modeling IMHO I would split each ingested metric (Price, Size) and each stock per key, meaning that for APPL stock you will have two time-series:

  • nyse_APPL_price ( containing the data related to the stock APPL with regards to Price metric )
  • nyse_APPL_size ( containing the data related to the APPL with regards to the Size metric )
    In that manner, and for the given example you would 6 time-series: nyse_APPL_price, nyse_APPL_size, nyse_MSFT_price, nyse_MSFT_size, nyse_GOOGL_price, and nyse_GOOGL_size.
    The following set of commands would create the time-series we’ve discussed already with the retention time specified of 120 000 ms ( 2min that you refer on the question ):
3

Hi Filipe,

I’m kicking myself in the butt for not posting sooner, would’ve saved you some work. I had arrived at the same conclusion, I used “stock:price:AAPL” and “stock:size:AAPL”. I still haven’t figured out what use I might have for the labels since I can query the data by the key pattern, but it feels nice to receive a confirmation from someone with experience. However, I stumbled upon a problem that I don’t know how to approach and the methods I am thinking of don’t look very pretty. At first, I read “Timestamp cannot be older than the latest timestamp in the time series” and that TimeSeries builds on top of Streams which provides a mechanism for ingesting the same millisecond granularity (15123123123.0) and then when I tested in the redis-cli it gave me an error. Unfortunately, it’s not uncommon for very active stocks to have multiple trades per millisecond. The only solution I can think of is aggregating the data before I ingest it into Redis - do a volume-weighted average price and sum(size) of that millisecond and then ingest it into TimeSeries, which is going to be a pain in the trunk. It will take computational power and will slow down the process. It would’ve been nice to be able to have multiple entries per timestamp or expand the granularity to nanoseconds. As for the running aggregations, the same service provides me with a WebSocket feed of 1-minute aggregations which are enough for my needs, so I am just going to ingest that into a separate TimeSeries.

Thank you for the help, kind regards,

Damian

4

Can someone direct me to how to interact with Redis Time Series using C#? Does the ‘StackExchange.Redis’ package provide the necessary interface?

5

Hello Demian,

‘StackExchange.Redis’ package should be a good choice.

You can call RedisTimeSeries command with db.Execute(…). For example -

db.Execute(“TS.CREATE”, “test1”)

db.Execute(“TS.ADD”, “test1”, “*”, “42”)

6

This is very useful, thank you. I can’t figure out how to retrieve ranges from a time series:
TS.RANGE my_key - +
TS.RANGE my_key - + AGGREGATION avg 3600000

``

I am assuming it should return a collection of some sort?

7

I figured it out, I had to cast the result as an array:
var result = await db.ExecuteAsync(“TS.RANGE”, new object[] { $“my_key”, “-”, “+” });

var range = (RedisResult[])result;

foreach (var bar in range)

{

foreach (var item in (string[])bar)

{

Console.WriteLine(item);

}

}

``