Candlestick Chart

Introduction

At RocketFin, our team of software and data engineers is continually exploring new tools and technologies to enhance the value we deliver to our clients in the financial industry. 3forge is a technology company that provides real-time data visualization and analytics solutions for financial institutions. Their flagship platform, AMI, integrates vast amounts of data from multiple sources, allowing users to analyze, visualize, and interact with complex datasets quickly. 3forge's tools are designed to improve decision-making and operational efficiency, particularly in trading, risk management, and compliance within the financial industry.

This is the third and final post in a three-part blog series. In the first part, we created a backend with which to retrieve data and push it to AMI. In the second, we created a tabular visualization of our data within AMI with streaming price data, and in this final part, we will create a candlestick chart using AMI’s charting capabilities using the ticker data we have processed.

In order to follow along, you will require access to the AMI platform, the Python service we created in part 1 running in the background, and the various tables we created in AMI in part 2 set up.

Creating a data model

To build our candlestick chart we require some data points which we don’t have available yet. These are the open, the close, the min and the max. All of these are scoped to a specific time granularity. For this example, we’re going to set our granularity to 15 minutes. To this, we need to create a new data model that operates on top of the YA_Trade_Raw table and these data points available for our chart.

Open the Data Modeler, find the YA_Trade_Raw table, right click it, and choose add data model. For our use case, we will be using the OnProcess event - which should be selected by default.

In part 2 of this series, we concluded by creating a historical table for us to archive old data. This means that when generating our data model, we need to operate on a union of both the YA_Trade_Raw table and the YA_Trade_Historical table.

CREATE TABLE 
  working_trade_raw
AS
USE
EXECUTE 
  SELECT id, price, time
  FROM YA_Trade_History
    
  UNION
  
  SELECT id, price, time
  FROM YA_Trade_Raw;

The statement creates a table – called working_trade_raw – and populates it using a union of the id, price and time columns from both tables.

Next, let us consider how we want the output data model to be partitioned. Our candlestick chart needs to operate on a symbol-by-symbol basis – which will be enforced by means of a filter - and requires a single entry for each timeslot in our granularity. If we were doing this in MySQL, SQL Server, etc. we would leverage a window function to compute the necessary arithmetic on a timeframe basis. AMI SQL offers the functionality to do just that using the Analyze clause. Analysis is performed on rows contained in a defined window, similar to a self-join. Each window can define an order-by to allow for finer control over the order in which an aggregate is applied. More information on this can be found here: https://doc.3forge.com/reference/ami_sql/#window_expr

CREATE TABLE 
  candle AS 
ANALYZE
  id,
  roundNearestUp(time,900000) as timeWindow,
  COUNT(symbolDay.price) as execCount,
  FIRST(symbolDay.price) as windowOpen,
  LAST(symbolDay.price) as windowClose,
  MAX(symbolDay.price) as windowMax,
  MIN(symbolDay.price) as windowMin
FROM working_trade_raw
WINDOW symbolDay ON
  symbolDay.id == id
  PARTITION BY roundNearestUp(time,900000)
  ORDER BY time ASC;

Let’s break down the statement one chunk at a time:

• CREATE TABLE candle AS ANALYZE
  Create a new table called candle, and inform AMI that we’ll be performing analysis on a different table in partitioned windows.
• roundNearestUp(time,900000) as timeWindow
  Round up the time of the transaction to the nearest 900,000 millisecond (15 minutes) and store this as the timeWindow such that we can use it later.

• COUNT(symbolDay.price) as execCount,
  FIRST(symbolDay.price) as windowOpen,
  LAST(symbolDay.price) as windowClose,
  MAX(symbolDay.price) as windowMax,
  MIN(symbolDay.price) as windowMin
          

  Perform various functions on the data within a window to extract the necessary data points.
• FROM working_trade_raw
  We’re using the working_trade_raw table we created before.

• WINDOW symbolDay ON
    symbolDay.id == id
    PARTITION BY roundNearestUp(time,900000)
          

  Create a window called symbol day, with all rows within the same window sharing the same id (the symbol). Before compiling the window, partition the data into 15-minute chunks.

If you click the test button at the bottom, you should get something like the below:

We have successfully grouped the records, but still have duplicates. This can be solved easily by recreating our candle table, and this time specify that we want to group on the id and timeWindow columns. While we’re at it, we’ll also add a new column that lets us pretty print the time window. For this function, we also specify that we want to use the current user’s timezone while formatting the value. Note how this command re-uses the existing candle table to create a new table with the same name.

CREATE TABLE candle AS
SELECT
  *,
  formatDate(timeWindow, "yyyy/MM/dd HH:mm", "${__TIMEZONE}") as visualWindow
FROM
  candle
GROUP BY id, timeWindow
ORDER BY id, timeWindow;

At the end, we can also drop working_table_raw, since this is no longer required.

DROP TABLE working_trade_raw;

Running a test again should now give us a unique record for each combination of symbol and time window.

Adding filters

Before we move on to creating our chart, we need to create a couple of filters with which to filter our data model. The first is a filter on the symbol, otherwise it will be impossible to visualize the data. The second is the date for which we’d like to see data. This is optional but recommended because of the volume of data that you’d otherwise be rendering.

Using the AMI UI, click on the main panel, and create a new panel to the right.

On the new panel, choose to create a new HTML panel.

Using the same menu, add a new text field and set the label and variables to symbol. Next, repeat the process but this time choose to create a date field, with an appropriate label, and the variable name candleDate. Finally, add a button with the label filter (the variable name is not relevant). In the button’s AMI script tab, in the onChange event, enable the data model variable as shown below, and update the script code to:

datamodel.reprocess();

Feel free to move around the three fields such that they don’t overlap. Be sure to also input a valid symbol in the symbol text box and select a date for which you have data in the date text box.

Let’s now reopen our data model and make a few changes to it.

First, check the checkboxes next to the field values for both symbol and candleDate. This will allow us to access these fields in our data model code.

Next, update the individual select statements in the union table to use the two filters. Note how when we use the variables , we wrap them in ${}, such that they are correctly interpreted at runtime.

long startDate = parseDate(candleDate.date(__TIMEZONE),"yyyy-MM-dd","UTC");
long endDate = roundNearestUp(startDate + 1, 86400000);

CREATE TABLE 
  working_trade_raw
AS
USE
  limit = 100000
EXECUTE 
  SELECT id, price, time
  FROM YA_Trade_History
  WHERE id == "${symbol}"
    AND time >= ${startDate}
    AND time < ${endDate}
  
  UNION
  
  SELECT id, price, time
  FROM YA_Trade_Raw
  WHERE id == "${symbol}"
    AND time >= ${startDate}
    AND time < ${endDate};

The filter on the id to use the symbol variable is straightforward. For our time window, we get the start of the day by parsing the selected date in the user’s current timezone, and then retrieving the long value for midnight of the date in UTC. It is important to do this since the date picker shows the value in the user’s timezone, but since the value is stored as a long, the actual UTC date could be a different date.

For instance, consider a user in Italy, where 00:00 UTC is equivalent to 01:00 (without daylight savings) local time. When the user picks the 19th of August in the picker, since the time defaults to midnight local, the underlying value is 23:00 UTC. Therefore, if we retrieved the start of day without jumping through some minor hoops, we’d get the 18th.

We compute the end of the day by rounding the selected date up to the nearest 24th hour. When setting the upper boundary, we also add 1 tick. This is because the default time when selecting a date in the date picker is midnight. This would mean that rounding up to the nearest 24th hour would give us the same value.

Let us consider some examples.

Running a test now will return results that respect both your symbol filter and date filter.

Creating the chart

Finally, we can create the chart to display our data.

Toggle the menu on the HTML panel and choose to create a new panel to the bottom. Then in the new panel, choose the “Create Table / Visualization / Form” option. From the data modeler screen, pick the data model that we have been working on. When prompted to pick a chart type, choose “2D Chart” and then “Advanced”.

A candlestick chart has two components. The open/close for the time window. And the high/low for the same time window. This means that our visualization will require two layers.

Let’s start with the open/close:

• In the “Options” section, set the “Order by” to timeWindow
• In the “Axis” section, set the “X” to timeWindow
• In the “Markers” section, set the “Shape” to square (use the plus on the side to help you), pick “Color” for “Color” (we’ll add some logic to this later) and enter 5 for “Width”
• In the “Marker Position Override” enter windowOpen for “Top” and windowClose for “Bottom”. This dynamically elongates the plot of the marker
• Click “Finish”

You may need to click filter on your HTML panel again, but once it has loaded, you’ll be greeted with a visualization similar to the below:

Next, let’s add the min/max plot.

Using the menu in the top right corner of the plot, choose the “Edit Plot” option and click “Add 2D”, ensuring that “Advanced” is selected for the new plot. The parameters should be near identical to the other layer with a few differences:

• In “Marker” section, pick black as a color, and make the width 1
• In “Marker Position Override, pick windowMin and windowMax

Upon clicking apply, your plot will update to show the below:

The last items on our to-do list are styling related. We’ll make the x-axis show a human readable date time value, add a tooltip to display additional information when hovering over the open/close bar and make the color of the open/close bar dependent on whether the delta was +ve or -ve.

To change the format of the bottom axis, toggle the menu on the x-axis and chose “Edit Highlighted Axis”. Change the “Axis Format Type” to “Time”. AMI will automatically parse the long value, extract from that the time in the user’s timezone, and display it in the axis.

For the other two configurations, re-open the “Edit Plot” screen, and pick the open/close layer.

• In the “Labels” section, set the “Hover over” value to the following. Note that this is simply HTML, which will allow you to create very rich tooltips.

"" + id + " @ " + visualWindow + "
Open: " + windowOpen + "
Close: " + windowClose+ "
Max: " + windowMax + "
Low: " + windowMin

• In the “Markers” section, change the value of “Color” to “Formula”, and enter the below:

windowClose > windowOpen ? "#008000" : "#ff0000"

Clicking the “Apply” button should immediately update the colors in your chart, but you need to close the window to see the tooltip.

The code for the AMI layout, as well as the data model, are available in their entirety on our GitLab here: https://gitlab.com/rocketfin/3forge-demos/-/snippets/3740318

If you have any questions, comments or concerns about this post, you can reach me at mark.buhagiar@rocketfin.co, where I will be happy to discuss both this topic further, as well as how RocketFin can help your organisation leverage 3Forge and the AMI platform to achieve your objectives more effectively and efficiently.