Stock Charts - Ingesting NYSE Stock Excange real-time data

[Damian Danev]

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]

[1580503106304, "AAPL", 232.28, 5378]

[1580503106235, "GOOGL", 899.56, 432]

[1580503106256, "MSFT", 625.56, 44]
[1580503106304, "AAPL", 234.78, 67888]

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

[Filipe Oliveira]

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 ):

######################

# time-series creation

######################

# https://oss.redislabs.com/redistimeseries/commands/#tscreate

redis-cli ts.create nyse_AAPL_price RETENTION 120000 LABELS metric price name APPL

redis-cli ts.create nyse_GOOGL_price RETENTION 120000 LABELS metric price name GOOGL

redis-cli ts.create nyse_MSFT_price RETENTION 120000 LABELS metric price name MSFT

redis-cli ts.create nyse_APPL_size RETENTION 120000 LABELS metric size name APPL

redis-cli ts.create nyse_GOOGL_size RETENTION 120000 LABELS metric size name GOOGL

redis-cli ts.create nyse_MSFT_size RETENTION 120000 LABELS metric size name MSFT

If you notice the LABELS portion of the command I've added two labels ( one for the metric type and other for the stock name ). That would enable us in the future to query for a specific metric ( for example give me all stock prices ) or for a specific stock ( for a specific stock give me all metrics -- which in our example are just Price and Size ).

With regards to ingestion you would then simply issue TS.ADD ( or if you want TS.MADD ) in the following manner:

#####################

# data ingestion

#####################

# https://oss.redislabs.com/redistimeseries/commands/#tsadd

# (You can also use TS.MADD to ingest multiple samples: https://oss.redislabs.com/redistimeseries/commands/#tsmadd )

# [1580503106159, "AAPL", 234.56, 1357]

redis-cli ts.add nyse_APPL_price 1580503106159 234.56

redis-cli ts.add nyse_APPL_size 1580503106159 1357

# [1580503106304, "AAPL", 232.28, 5378]

redis-cli ts.add nyse_APPL_price 1580503106304 232.28

redis-cli ts.add nyse_APPL_size 1580503106304 5378

# [1580503106235, "GOOGL", 899.56, 432]

redis-cli ts.add nyse_GOOGL_price 1580503106235 899.56

redis-cli ts.add nyse_GOOGL_size 1580503106235 432

# [1580503106256, "MSFT", 625.56, 44]

redis-cli ts.add nyse_MSFT_price 1580503106256 625.56

redis-cli ts.add nyse_MSFT_size 1580503106256 44

# [1580503106304, "AAPL", 234.78, 67888]

# Assuming this was a typo for 1580503106305, thus: [1580503106305, "AAPL", 234.78, 67888]

redis-cli ts.add nyse_APPL_price 1580503106305 234.78

redis-cli ts.add nyse_APPL_size 1580503106305 67888

To be able to query the time series for particular stock aggregations in OHLCV you could use TS.RANGE for a specific stock, or use TS.MRANGE for a specific set of stocks. Focusing on the example you've provided the following set of commands would produce the expected outcome:

#####################

# querying

#####################

# https://oss.redislabs.com/redistimeseries/commands/#tsrange

# To answer the example:

# time = minute of the time bucket ( timerange is 60 * 1000ms )

# Open = first(Price)

# Open = 234.56

echo ""

echo "Open = first(Price)"

echo "expecting Open = 234.56‬"

redis-cli ts.range nyse_APPL_price - + AGGREGATION first 60000

# time = minute of the time bucket ( timerange is 60 * 1000ms )

# High= max(Price)

# High= 234.78

echo ""

echo "High= max(Price)"

echo "expecting High= 234.7‬8"

redis-cli ts.range nyse_APPL_price - + AGGREGATION max 60000

# time = minute of the time bucket ( timerange is 60 * 1000ms )

# Low= min(Price)

# Low= 232.28

echo ""

echo "Low= min(Price)"

echo "expecting Low= 232.28"

redis-cli ts.range nyse_APPL_price - + AGGREGATION min 60000

# time = minute of the time bucket ( timerange is 60 * 1000ms )

# Close= last(Price)

# Close= 234.78

echo ""

echo "Close= last(Price)"

echo "Close= 234.78"

redis-cli ts.range nyse_APPL_price - + AGGREGATION last 60000

# time = minute of the time bucket ( timerange is 60 * 1000ms )

# Volume= sum(Size)

# Volume= ‭74623‬

echo ""

echo "Volume= sum(Size)"

echo "expecting Volume= ‭74623‬"

redis-cli ts.range nyse_APPL_size - + AGGREGATION sum 60000

One important reminder ( but not mandatory ), is that if your goal is to compute always the same type of aggregates I would suggest making usage of TS.CREATE RULE to have automatic compaction rules. The following rules would fit you're purpose ( added example just for one stock, you need to replicate for each stock ):

###################################################################

# DOWNSAMPLING RULES

###################################################################

######################

# DOWNSAMPLING time-series creation

######################

redis-cli ts.create ds_nyse_AAPL_price_first_60s LABELS metric price name APPL downsample_time 60000 rule first

redis-cli ts.create ds_nyse_AAPL_price_max_60s LABELS metric price name APPL downsample_time 60000 rule max

redis-cli ts.create ds_nyse_AAPL_price_min_60s LABELS metric price name APPL downsample_time 60000 rule min

redis-cli ts.create ds_nyse_AAPL_price_last_60s LABELS metric price name APPL downsample_time 60000 rule last

redis-cli ts.create ds_nyse_AAPL_size_sum_60s LABELS metric size name APPL downsample_time 60000 rule sum

######################

# DOWNSAMPLING rules creation

######################

# https://oss.redislabs.com/redistimeseries/commands/#aggregation-compaction-downsampling

# TS.CREATERULE sourceKey destKey AGGREGATION aggregationType timeBucket

redis-cli ts.createrule nyse_APPL_price ds_nyse_AAPL_price_first_60s AGGREGATION first 60000

redis-cli ts.createrule nyse_APPL_price ds_nyse_AAPL_price_max_60s AGGREGATION max 60000

redis-cli ts.createrule nyse_APPL_price ds_nyse_AAPL_price_min_60s AGGREGATION min 60000

redis-cli ts.createrule nyse_APPL_price ds_nyse_AAPL_price_last_60s AGGREGATION last 60000

redis-cli ts.createrule nyse_APPL_size ds_nyse_AAPL_size_sum_60s AGGREGATION sum 60000

As a quick  note you can add a retention time for your down-sampled time-series to for example a month, 3 months or any value that fits your purpose. In this manner you would have the raw values for 2minutes and have the pre-computed aggregations for as long as you need ( without them being forgotten and trimmed as soon as they get old enough ).
I've added the following gist with all commands to ease your learning process: https://gist.github.com/filipecosta90/67ba82b324a01054ba7faccfbc7be354
Please let me know if this fits your goals. Happy learning :)

Kind regards,
Filipe

[Damian Danev]

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

[Damian Danev]

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

[Ariel Shtul]

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")

[Damian Danev]

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?

[Damian Danev]

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);

    }

}