Quick candlestick summaries with Elixir's Explorer

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.

elixir-nx / explorer

Series (one-dimensional) and dataframes (two-dimensional) for fast data exploration in Elixir

Explorer brings series (one-dimensional) and dataframes (two-dimensional) for fast data exploration to Elixir. Its high-level features are:

• Simply typed series: :float, :integer, :boolean, :string, :date, and :datetime.
• A powerful but constrained and opinionated API, so you spend less time looking for the right function and more time doing data manipulation.
• Pluggable backends, providing a uniform API whether you're working in-memory or (forthcoming) on remote databases or even Spark dataframes.
• The first (and default) backend is based on NIF bindings to the blazing-fast polars library.

The API is heavily influenced by Tidy Data and borrows much of its design from dplyr. The philosophy is heavily influenced by this passage from dplyr's documentation:

• By constraining your options, it helps you think about your data manipulation challenges.
• It provides simple “verbs”, functions that correspond to the most common data manipulation tasks, to help you translate…

View on GitHub

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

Cantido / exchange

A market application with a focus on cryptocurrencies

Exchange

To start your Phoenix server:

• Install dependencies with mix deps.get
• Create and migrate your database with mix ecto.setup
• Install Node.js dependencies with npm install inside the assets directory
• Start Phoenix endpoint with mix phx.server

Now you can visit localhost:4000 from your browser.

Ready to run in production? Please check our deployment guides.

Learn more

• Official website: https://www.phoenixframework.org/
• Guides: https://hexdocs.pm/phoenix/overview.html
• Docs: https://hexdocs.pm/phoenix
• Forum: https://elixirforum.com/c/phoenix-forum
• Source: https://github.com/phoenixframework/phoenix

View on GitHub