One of the main visualizations of any kind of financial market is the candlestick chart. In a candlestick chart, trades are summarized into time periods with that period's high price, low price, price at the beginning of the time period, and the price at the end of the time period. Here's an example, taken from TradingView, a popular website that provides a lot of charts and tools.

Each of the boxes represent the asset price at the beginning and ending of the time period, and the lines above and below indicate the maximum and minimum price over that period. It's a very succinct way of summarizing a lot of data.

I have a toy project where I'm making a cryptocurrency exchange website, and I've been playing with the Elixir Nx project's Explorer library to manipulate this data. I found that you can summarize trade data into candlestick data extremely easily. This transformation is also very fast, because Explorer uses a Rust-based backend called Polars for the actual data manipulation.

The raw data for my candlestick chart comes from the trades table in my database, which looks like this (with some extraneous fields omitted):

For simplicity, price and quantity are just integers. You can insert your decimal places as appropriate for a given currency.

The main challenge in summarizing this data into candlesticks is to group it by time period. This is a place where you'll need to iterate through all the query results, which you'd need to do anyway in order to transform the DateTimes from your Ecto query into NaiveDateTimes, which are the only time type that Explorer understands. To group this data by time period, you have to find the beginning of your time period when given the trade's timestamp. If you are creating one-second-long candlesticks, you'll use the NaiveDateTime.truncate/2 function for this. So you'll prepare your data for an Explorer.DataFrame like this:

iex> trades =
...>   MyApp.Repo.all(MyApp.Trade)
...>   |> Enum.map(fn t ->
...>     naive_time = DateTime.to_naive(t)
...>     open_time = NaiveDateTime.truncate(naive_time, :second)
...>
...>     t
...>     |> Map.put(:executed_at, naive_time)
...>     |> Map.put(:open_time, open_time)
...>   end)
[
  %{
    open_time: ~N[2022-08-22 12:47:17.000],
    executed_at: ~N[2022-08-22 12:47:17.123],
    price: 100,
    quantity: 100
  },
  ...
]

Now we can give this to Explorer.DataFrame.new().

iex> df = Explorer.DataFrame.new(trades)
#Explorer.DataFrame<
  Polars[3 x 4]
  executed_at datetime [2022-08-22 12:47:17.123000, ...]
  open_time datetime [2022-08-22 12:47:17.000000, ...]
  price integer [100, ...]
  quantity integer [100, ...]
>

Once we've got our data in a DataFrame, it's trivial to do the actual candlestick summary. That's because Explorer data frames offer the Explorer.DataFrame.summarise/2 function, which performs the exact operations we want. You'll just need to make sure the data is in the right order, and is grouped by the open_time value we calculated earlier.

iex> alias Explorer.DataFrame
...> candles =
...>   df
...>   |> DataFrame.arrange(:executed_at)
...>   |> DataFrame.group_by(:open_time)
...>   |> DataFrame.summarise(price: [:first, :last, :max, :min])
#Explorer.DataFrame<
  Polars[1 x 5]
  open_time datetime [2022-08-22 12:47:17.000]
  price_first integer [100]
  price_last integer [101]
  price_max integer [101]
  price_min integer [99]
>

Now you have data for a candlestick chart! There is plenty of room for optimization here, like storing your trading timestamps as NaiveDateTime structs so we don't need to do the conversion. You might be able to do this all within a database query, in fact, but I will argue that you can't beat the simplicity of Explorer.DataFrame.summarise/2. Turning this data into an actual image is an exercise left for the reader.

If you want to see an example of how an event-sourced cryptocurrency exchange might work, check out my exchange project.