AMI Execution Monitor

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 visualisation and analytics solutions for financial institutions. Their flagship platform, AMI, integrates vast amounts of data from multiple sources, allowing users to analyse, visualise, 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 second 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 this post, we will create a tabular visualisation of our data within AMI with streaming price data (the left side of the screenshot below), and in the 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, and the Python service we created last time running in the background.

Visualising the streaming data

Let’s start by first visualising the data that we’re passing into AMI in its raw format. AMI automatically created a real-time data table in its in-memory database when we started streaming in the YA_Trade_Raw objects.

We can view this by going to the Data Modeler screen using the Dashboard menu.

Next, we’ll right click on the YA_Trade_Raw data source and select “Add Realtime Table / Visualisation”. The next popup lets us choose both how we want to format the data, as well as which columns we want to include. In this example, let’s retain the default “Table” and pick “Id (the symbol), time, Price, last Size, Change (day on day change) and day volume”. Note also, how AMI has intelligently detected the appropriate data types for each of these columns. These are inferred from the data that has been populated in the in-memory database.

Upon clicking create, we can see all the records that we have already received. These are best viewed by sorting on time in reverse chronological order, such that new records can be seen streaming in at the top of the grid.

Visualising Changing Data

What we have accomplished thus far is all well and good, but what we’re truly after is a static view of the symbols we’re interested in with the various datapoints changing as new records are received. However, we also don’t want to lose the raw version of the data, as these will be required when we’re building our candlestick chart.

To accomplish our goal, we need to create a secondary table that is populated off the back of the raw table and enforces uniqueness on the symbol itself. To do this, we need to launch the AMIDB Shell Tool. We can also do this as part of an amisql script that is executed on application launch as documented here: https://doc.3forge.com/center/?h=ami.db.schema.config.files#startup-process

CREATE PUBLIC TABLE IF NOT EXISTS YA_Trade(id String,price Float,time Long,change Float,dayVolume Long,lastSize Long);

First, we create a new table that’s composed of the 6 data columns that we are interested in. Note, how AMI’s flavor of SQL is very similar to that used by standard SQL databases such as MySQL and SQL Server, and does not require a steep learning curve.

CREATE INDEX IF NOT EXISTS symbol_uniq ON YA_Trade(id SORT) USE Constraint="PRIMARY";

Next, we create an index on this new table called symbol_uniq which uses the id (the symbol) column and has a constraint that it is primary, which is what enforces the uniqueness. In essence, this has now transformed the inserts we execute on the YA_Trade table akin to upserts. If an entry for that symbol does not exist yet, we will create a new record, otherwise we will update the existing record.

When it comes to populating this table, we have a variety of options:

1. We can create an insert trigger on the YA_Trade_Raw table to execute a second insert on the YA_Trade table
2. We can update our service code to send a YA_Trade_Raw message and a YA_Trade message
3. We can use a projection type trigger

Which route to take is the implementer’s choice, however for this example, we will consider the projection trigger.

CREATE TRIGGER IF NOT EXISTS Symbol_Ticker_Trigger OFTYPE PROJECTION
ON YA_Trade_Raw,YA_Trade
USE selects="id=id, price=price, time=time, change=change, dayVolume=dayVolume, lastSize=lastSize";

This command binds one or more existing source tables (YA_Trade_Raw) and an existing target table (YA_Trade) together such that the target table will be populated using the source table. As the source table has rows inserted, updated or deleted the target table will automatically have its corresponding projected rows inserted, updated, deleted.

All we need to do now is create a visualization using the new YA_Trade table. You can proceed to close the existing table that uses YA_Trade_Raw, navigate back to the Data Modeler screen, and when creating a Realtime Table, use the YA_Trade table instead.

Note how now the number of rows in the table is fixed, however cells will occasionally flash in yellow to indicate that the value has been updated. This view is particularly interesting at the time that an exchange opens for trading, and many transactions are executed in quick succession.

The last couple of changes we’d like to make are related to styling. First, let’s rename the Id column to a more appropriate “Symbol”. To do this, left click on the column header and select “Edit Column”. Here we can update the “Title” field in the “Column Header” section to “Symbol”.

Finally, we want to add some styling to the “Change” column such that we can quickly identify whether that instrument has had a positive or negative day on day change.

As with the “Id” column, left click on the “Change” column header and select “Edit Column”. Let’s configure a ternary expression for the “Background Color”:

change > 0 ? "#008000" : "#ff0000"

As you can see the syntax is straightforward and congruent with that used by modern languages. If we use the plus icon on the right-hand side, AMI also makes available some handy constants such as colors and the variables we have available.

As soon as we click the “Update Column” button, we can see these changes reflected on the grid itself.

Storing Historical Data

One important thing to note is that realtime data tables are not designed to hold data infinitely, and as such, we should offload this information into historical tables on a regular basis.

To do this, we need to go back to the AMIDB Shell and create (a) a historical table, (b) a procedure to back the data up, and (c) a timer that uses a Cron schedule to execute the procedure.

CREATE PUBLIC TABLE IF NOT EXISTS YA_Trade_History(id String PARTITION,price Float,time Long,change Float, dayVolume Long,lastSize Long)
USE PersistEngine="HISTORICAL";

Creating a historical table is nearly identical to the other tables which we’ve created, with the two differences being the “PersistEngine” being set to historical, and the id column being marked as a partitioning column. Historical tables are columnar-based on-disk tables with support for partitioning. They are designed for storing large volumes of data at high speed with fast retrieval, all while using the same SQL syntax as realtime tables. More information on historical tables is available on 3Forge’s documentation here: https://doc.3forge.com/center/historical_tables

CREATE PROCEDURE IF NOT EXISTS ARCHIVE_YA_TRADES OFTYPE AMISCRIPT USE script="
int rowCount=select count(*) from YA_Trade_Raw;
for(int n=0;n<rowCount;n+=batchSize){
    INSERT INTO YA_Trade_History SELECT id, price, time, change, dayVolume, lastSize,

 FROM YA_Trade_Raw LIMIT n, batchSize;
}; Truncate table YA_Trade_Raw;" arguments="int batchSize";

The procedure above inserts all the records in the YA_Trade_Raw table into the YA_Trade_History table in predefined batch sizes. Once all records have been backed up, the raw table is truncated to free up memory. We also define an argument called “batchSize” which we will provide when executing the procedure.

CREATE TIMER IF NOT EXISTS ya_archival_timer oftype amiscript on "0 0 0 * * * UTC" use script="call ARCHIVE_YA_TRADES(1000);"

Finally, we create a timer that executes once a day at midnight UTC, and calls the procedure we have just created using a batch size of 1000.

In the final blog post in this series, we will take a closer look at the charting capabilities that AMI makes available to create a candlestick chart which we are able to filter by symbol and date. In the meantime, 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.