Quick candlestick summaries with Elixir's Explorer
Published: 2022-08-22 20:03:09

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](https://www.tradingview.com/), a popular website that provides a lot of charts and tools.

![Candlestick chart](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/9wc8bga3pv1k9i6auoe5.png)

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](https://github.com/elixir-nx/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](https://github.com/pola-rs/polars) for the actual data manipulation.

{% embed https://github.com/elixir-nx/explorer %}

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

| `price` | `quantity` | `executed_at` |
|---------|------------|---------------|
| 100     | 100        | 2022-08-22T12:47:17.123Z |
| 99      | 1000       | 2022-08-22T12:47:17.456Z |
| 101     | 5          | 2022-08-22T12:47:17.789Z |

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 `DateTime`s from your Ecto query into `NaiveDateTime`s, 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`](https://hexdocs.pm/elixir/NaiveDateTime.html#truncate/2) function for this. So you'll prepare your data for an `Explorer.DataFrame` like this:

```elixir
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()`.

```elixir
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`](https://hexdocs.pm/explorer/Explorer.DataFrame.html#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.

```elixir
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.

{% embed https://github.com/Cantido/exchange %}