How to generate Volume Weighted Average Price inside redis?

• I have a bunch of ticker prices and volumes that keep getting stored from websockets via Node.js Currently storing each ticker in a hash like

  "XYZ:Source1": {"price": 100, "volume": 10000}

  "XYZ:Source2": {"price": 120, "volume": 15000}

  "ABC:Source1": {"price": 544, "volume": 18000}

• As soon as price or volume of XYZ changes from any source, I want the VWAP aggregate XYZ price which in the above example would be (100 * 10000 + 120 * 15000) / (10000 + 15000) and ABC computed on redis

• How can I achieve this on redis level without loading everything into my application

I think it depends on how frequently your tickers update and how many sources each ticker has.

If the numbers are small, then you can use a Lua script to read every source every time a ticker updates.

Note: I've realized the use of SCAN in the next example is invalid because it can return duplicate values. I'm leaving this example solution here because it might be able to be fixed.

First, I would name your sources "ABC:source:1" so you can store the VWAP under the key "ABC:VWAP" and not mix that key up with sources. Then the Lua script would look something like this (I have not tested this):

-- Execute as:
--
--   EVALSHA <hash> 2 ABC:source:* ABC:VWAP
--
-- "ABC:source:*" is the prefix of all sources
-- "ABC:VWAP" is the key to store the VWAP

local cursor = 0
-- numerator and denominator of VWAP price
local num = 0
local denom = 0

repeat
  -- scan through all keys matching: ABC:source:*
  local result = redis.call("HSCAN", 0, "MATCH", KEYS[1])
  cursor = result[1]
  local keys = result[2]

  -- read the price and quantity of each ticket
  -- update the numerator and denominator
  for i = 1, #keys do
    local ticker = redis.call("HMGET", keys[i], "price", "quantity")
    num += ticker[1] * ticker[2]
    denom += ticker[2]
  end
until cursor == 0

local vwap = 0
if denom > 0 do
  vwap = num / denom
end

-- store the VWAP in the key: ABC:VWAP
redis.call("SET", KEYS[2], vwap)

Although this executes atomically on the Redis server and doesn't bring the data back to your application, it makes at least N+2 look-ups, where N is the number of sources of the ticker. If the number of sources is high, then you may want to avoid scanning them all every time a ticket updates. In that case, you could store num and denom in Redis and update them each time a ticket updates. The keys would be something like:

• ABC:source:<name> - Sources
• ABC:VWAP:num - Current VWAP numerator
• ABC:VWAP:denom - Current VWAP denominator
• ABC:VWAP - Current VWAP

The algorithm to do a ticker update would be something like this:

1. Read the key ABC:source:1 to get the current price and quantity
2. Update the key ABC:source:1 with the new price and quantity
3. Calculate the numerator change: num_change = price * quantity - prevPrice * prevQuantity
4. Calculate the denominator change: denom_change = quantity - prevQuantity
5. INCRBY ABC:VWAP:num <num_change> (returns the new numerator)
6. INCRBY ABC:VWAP:denom <denom_change> (returns the new denominator)
7. Calculate vwap = num / denom and update the VWAP: SET ABC:VWAP <vwap>

This would have only 5 Redis look-ups each time a ticker updates. But it introduces the possibility of errors if the cached numerator and denominator values don't get updated properly.