Integrating Quik with ATSD: Board Arbitrage

Quik Overview
Quik Features
Market Data
ATSD Quik ODBC Driver
Board Arbitrage
Availability Monitoring
Reference

Quik Overview

Quik Workstation is a brokerage application. It allows professional traders to buy and sell stocks, bonds, derivatives, and currencies on the Moscow Exchange through a broker.

Quik UI

The workstation supports two-factor authentication and connects securely to the Quik server installed at the broker.

The Quik server validates and routes orders to the exchange while also interfacing with the broker's back-end systems.

The orders are executed by the exchange at the best available price determined at continuous auctions.

Quik is a 32-bit Windows program developed by Arqatech and is installed from an msi package. The package is custom built by each broker to hard-code particular features and connection parameters to the broker's Quik servers.

Current version as of May 2019 at Sberbank is v7.19.3.1.

Quik Features

Create and cancel orders
View real-time market data
View historical OHLC history using charts
Read news released by Reuters and Interfax
Export current data into Excel, databases (ODBC), other trading tools
Create reports, submit non-trading requests
Execute transactions from file

Execute Lua and QPILE scripts

Sample script to buy Gazprom stock:

local transaction = {
  ACCOUNT   = 'XXX-XXXXXXXX',
  TRANS_ID  = '123',
  ACTION    = 'NEW_ORDER',
  TYPE      = 'L',            -- limit order
  OPERATION = 'B',            -- buy order
  CLASSCODE = 'TQBR',         -- equities
  SECCODE   = 'GAZP',         -- Gazprom common
  QUANTITY  = '1',
  PRICE     = '200.0',
  EXECUTION_CONDITION = 'FILL_OR_KILL'
}
sendTransaction(transaction)

Market Data

Data Exposed by Quik

The Quik workstation provides access to market data of various types:

Trades
Best bid and offer Quotes (Level 1)
Quote Book (Level 2)
OHLC history, aggregated from trade data
News

Data Available from Exchange

Data available as a subscription from the exchange.

Trades

|    TRADENO | TRADEDATE  | TRADETIME | SECID | BOARDID |  PRICE | QUANTITY |    VALUE | TYPE | BUYSELL |
|------------|------------|-----------|-------|---------|--------|----------|----------|------|---------|
| 2914349072 | 29.12.2018 | 09:59:42  | MTSS  | TQBR    |    237 |       10 |     2370 |      | B       |
| 2914349073 | 29.12.2018 | 09:59:42  | NVTK  | TQBR    | 1126.8 |      150 |   169020 |      | B       |
| 2914349074 | 29.12.2018 | 09:59:42  | NVTK  | TQBR    | 1126.8 |       10 |    11268 |      | B       |
| 2914349075 | 29.12.2018 | 09:59:44  | GAZP  | TQBR    | 153.69 |       10 |   1536.9 |      | S       |
| 2914349076 | 29.12.2018 | 09:59:44  | GAZP  | TQBR    | 153.69 |      140 |  21516.6 |      | S       |
| 2914349077 | 29.12.2018 | 09:59:44  | GAZP  | TQBR    | 153.69 |      750 | 115267.5 |      | S       |

Orders, Type A

|     NO | SECCODE | BUYSELL |         TIME | ORDERNO | ACTION |  PRICE | VOLUME | TRADENO | TRADEPRICE |
|--------|---------|---------|--------------|---------|--------|--------|--------|---------|------------|
| 412833 | ROSN    | B       | 105304715636 |  228871 |      0 | 425.95 |     40 |         |            |
| 412834 | POLY    | B       | 105304718116 |  227693 |      0 |  734.8 |     29 |         |            |
| 412835 | ROSN    | S       | 105304745167 |  227393 |      0 |  427.2 |    170 |         |            |
| 412836 | ROSN    | S       | 105304746264 |  229592 |      1 |  427.2 |     90 |         |            |
| 412837 | RUAL    | B       | 105304800443 |  229593 |      1 | 30.455 |     20 |         |            |
| 412838 | PLZL    | B       | 105304812871 |  229529 |      0 | 5366.5 |     65 |         |            |
| 412839 | PLZL    | S       | 105304813282 |  229531 |      0 | 5409.5 |     65 |         |            |
| 412840 | PLZL    | B       | 105304813336 |  229539 |      0 |   5367 |     52 |         |            |
| 412841 | PLZL    | B       | 105304813772 |  229594 |      1 | 5312.5 |     52 |         |            |
| 412842 | RUAL    | S       | 105304824440 |  229217 |      0 |  30.62 |    930 |         |            |
| 412843 | RUAL    | S       | 105304849448 |  229595 |      1 |  30.57 |     50 |         |            |
| 412844 | CHMF    | S       | 105304852240 |  225438 |      0 |    947 |    210 |         |            |

Orders, Type B

| SECCODE | BUYSELL |         TIME |    TRADENO |  PRICE | VOLUME |
|---------|---------|--------------|------------|--------|--------|
| MOEX    | B       | 133408030288 | 2914434812 |  80.97 |     10 |
| MOEX    | B       | 133408030288 |            |  80.97 |    990 |
| MOEX    | S       | 133408030288 |            |  80.97 |     10 |
| MOEX    | S       | 133408030288 | 2914434812 |  80.97 |     10 |
| MOEX    | S       | 133408030288 |            |     81 |     20 |
| MOEX    | B       | 133408033424 |            |  80.98 |    280 |
| ROSN    | B       | 133408205588 |            | 423.45 |     10 |
| SNGS    | B       | 133408239317 |            |  26.78 |   2000 |
| MOEX    | B       | 133408243551 |            |  80.97 |   1140 |
| POLY    | B       | 133408751686 |            |  732.7 |      1 |

Consolidated Example

| Data Type | Symbol | Operation |         Time |   Trade No |  Price | Quantity |
|-----------|--------|-----------|--------------|------------|--------|----------|
| ORDER B   | SNGS   | S         | 100004425331 |            | 26.855 |     1700 |
| TRADE     | SNGS   | B         | 100004735095 | 2914349804 | 26.855 |      800 |
| TRADE     | SNGS   | S         | 100004735095 | 2914349804 | 26.855 |      800 |
| ORDER A   | SNGS   | S         | 100004735095 |            | 26.855 |      900 |

ATSD Quik ODBC Driver

Driver Setup

Install 32-bit ATSD ODBC driver for Quik from atsd_odbc_x32.msi.

Open Control Panel and create a User DSN.

If the ATSD driver is not listed in the User DSN tab when installing the driver on a 64-bit Windows server, open the ODBC tool as follows:

C:\Windows\SysWOW64\odbcad32.exe

Regional Settings

Open Control Panel > Regional Settings and modify Short Date and Long Time formats as follows:

Export Trades

Create a new table Таблица обезличенных сделок in Quik. This table includes all trades executed on the exchange, not just the client trades which are accessible in a different table called Таблица Сделок.

Right click the table and select Вывод по ODBC.

Connect to the data source. Choose ATSD.quik_tx_all from the list of available database tables.

Include and map only those columns that are present in the target quik_tx_all table.

Ensure that Формальные имена setting is checked.

Click Начать вывод данных.

Export Quotes (Level 1)

Create a new table Текущая таблица параметров in Quik. Include all columns to the table.

Right click the table and select Вывод по ODBC.

Connect to the data source. Choose ATSD.quik_current from the list of available database tables.

Map all available columns.

Ensure that Формальные имена setting is checked.

Click Начать вывод данных.

Verify Data Insertion in ATSD

Enable Quik Logging

Open Settings > Configuration > Configuration Files page. Add Quik file loggers to record SQL commands received from the ATSD ODBC driver to the logback.xml file.

<appender name="quik.1" class="ch.qos.logback.core.rolling.RollingFileAppender">
    <file>../logs/quik_tx.log</file>
    <rollingPolicy class="ch.qos.logback.core.rolling.SizeAndTimeBasedRollingPolicy">
      <fileNamePattern>../logs/quik_tx.%d{yyyy-MM-dd}.%i.log.zip</fileNamePattern>
      <maxFileSize>200MB</maxFileSize>
      <maxHistory>7</maxHistory>
      <totalSizeCap>4GB</totalSizeCap>
    </rollingPolicy>
    <encoder>
      <pattern>%d{"yyyy-MM-dd'T'HH:mm:ss.SSSXXX",UTC};%message%n</pattern>
    </encoder>
</appender>

<logger name="sql.quik.quik_tx_all" level="DEBUG"  additivity="false">
    <appender-ref ref="quik.1"/>
</logger>

<appender name="quik.2" class="ch.qos.logback.core.rolling.RollingFileAppender">
    <file>../logs/quik_current.log</file>
    <rollingPolicy class="ch.qos.logback.core.rolling.SizeAndTimeBasedRollingPolicy">
      <fileNamePattern>../logs/quik_current.%d{yyyy-MM-dd}.%i.log.zip</fileNamePattern>
      <maxFileSize>200MB</maxFileSize>
      <maxHistory>7</maxHistory>
      <totalSizeCap>4GB</totalSizeCap>
    </rollingPolicy>
    <encoder>
      <pattern>%d{"yyyy-MM-dd'T'HH:mm:ss.SSSXXX",UTC};%message%n</pattern>
    </encoder>
</appender>

<logger name="sql.quik.quik_current" level="DEBUG"  additivity="false" >
    <appender-ref ref="quik.2"/>
</logger>

Verify Trade Data

Enter quik_tx_all on the Metrics tab in ATSD and check that at least price and volume metrics are listed.

Trades are received as INSERT statements from the ODBC driver and are logged in the quik_tx.log file.

tail -F /opt/atsd/atsd/logs/quik_tx.log

... INSERT INTO ATSD.quik_tx_all (number, date, time, time_us, instrument, price, volume, operation) VALUES (2964058799.00000000, '2019-05-24', '10:53:11', 223665.00000000, 'MGNT [TQBR]', 3570.50000000, 2.00000000, 'BUY')
... INSERT INTO ATSD.quik_tx_all (number, date, time, time_us, instrument, price, volume, operation) VALUES (2964058800.00000000, '2019-05-24', '10:53:11', 225257.00000000, 'UPRO [TQBR]', 2.49400000, 1.00000000, 'BUY')
... INSERT INTO ATSD.quik_tx_all (number, date, time, time_us, instrument, price, volume, operation) VALUES (2964058801.00000000, '2019-05-24', '10:53:11', 226478.00000000, 'SNGS [TQBR]', 24.32500000, 10.00000000, 'BUY')
... INSERT INTO ATSD.quik_tx_all (number, date, time, time_us, instrument, price, volume, operation) VALUES (2964058802.00000000, '2019-05-24', '10:53:11', 227701.00000000, 'MOEX [TQBR]', 84.09000000, 15.00000000, 'BUY')
... INSERT INTO ATSD.quik_tx_all (number, date, time, time_us, instrument, price, volume, operation) VALUES (2964058803.00000000, '2019-05-24', '10:53:11', 228902.00000000, 'NVTK [TQBR]', 1292.80000000, 6.00000000, 'BUY')

The INSERT statements containing trade details are converted into series commands as part of processing.

tail -F /opt/atsd/atsd/logs/command.log | grep quik_tx_all

... series e:poly_[tqbr] ms:1558685669225 t:operation=BUY m:quik_tx_all_price=674.2 m:quik_tx_all_volume=7
... series e:gmkn_[tqbr] ms:1558685669624 t:operation=SELL m:quik_tx_all_price=13686.0 m:quik_tx_all_volume=14
... series e:gmkn_[tqbr] ms:1558685669625 t:operation=SELL m:quik_tx_all_price=13684.0 m:quik_tx_all_volume=1
... series e:gmkn_[tqbr] ms:1558685669626 t:operation=SELL m:quik_tx_all_price=13684.0 m:quik_tx_all_volume=35
... series e:vtbr_[tqbr] ms:1558685669775 t:operation=SELL m:quik_tx_all_price=0.0382 m:quik_tx_all_volume=11

Verify Quote Data

Enter *[tqbr] on the Entities tab and check that the list contains symbol names with class code as a suffix.

Select one of the entities from the list and check that the quik_current is collected.

Quote are received as INSERT and incremental UPDATE statements from the ODBC driver and are logged in the quik_current.log file.

tail -F /opt/atsd/atsd/logs/quik_current.log

... UPDATE ATSD.quik_current SET "Предл."=0.07170, "Предл.сессии"=0.07170 WHERE "Инструмент"='MRKY [TQBR]'
... UPDATE ATSD.quik_current SET "% измен.закр."=1.51, "Оборот"=7187975, "Спрос"=59.12, "Предл."=59.26, "Цена послед."=59.20, "Время послед."='11:30:47', "Кол-во послед."=14, "Общее кол-во"=120800, "Кол-во сделок"=423, "Предл.сессии"=59.26, "Спрос сессии"=59.12, "Оборот посл."=8288.00, "Кол. предл."=40, "Измен. к закр."=0.88, "Кол. спрос"=68 WHERE "Инструмент"='TRMK [TQBR]'
... UPDATE ATSD.quik_current SET "Кол. спрос"=5 WHERE "Инструмент"='RSTI [TQBR]'
... UPDATE ATSD.quik_current SET "Предл."=0.07160, "Предл.сессии"=0.07160, "Кол. предл."=6 WHERE "Инструмент"='MRKY [TQBR]'
... UPDATE ATSD.quik_current SET "Предл."=1.2537, "Предл.сессии"=1.2537, "Кол. предл."=8 WHERE "Инструмент"='RSTI [TQBR]'

The statements containing quote changes are converted into property commands.

tail -F /opt/atsd/atsd/logs/command.log | grep quik_current

... property t:quik_current e:tatn_[tqbr] ms:1558686958347 v:%_измен.закр.=0.92 v:время_послед.=11:35:58 v:измен._к_закр.=6.5 v:кол-во_послед.=1 v:кол-во_сделок=1425 v:кол._предл.=119 v:оборот=171458842 v:оборот_посл.=7145.0 v:общее_кол-во=239790 v:цена_послед.=714.5
... property t:quik_current e:sberp_[tqbr] ms:1558686958388 v:кол._предл.=56
... property t:quik_current e:aflt_[tqbr] ms:1558686958388 v:кол._предл.=1000 v:предл.=91.4 v:предл.сессии=91.4
... property t:quik_current e:vtbr_[tqbr] ms:1558686958389 v:время_послед.=11:35:58 v:кол-во_послед.=1 v:кол-во_сделок=45674 v:кол._предл.=160 v:оборот=2982679106 v:оборот_посл.=383.15 v:общее_кол-во=76894410000

The incremental updates are automatically assembled into a composite property record that contains the latest values for all fields.

Board Arbitrage

Strategy Description

The strategy generates a paired buy and sell signal when the price of identical securities diverges between different boards by a spread exceeding the brokerage fees, exchange fees, and the cost of capital.

In the example below, the offer on the left is below the bid on the right.

Buy N units at 1.840 (offer).
Sell M units at 1.920 (bid).
Profit is M * (1.920 - 1.840) before fees, or 4.348%.
Net market exposure: 1.840 * (N - M).
The net exposure is subject to risk-adjusted cost of capital.

Boards / Classes

Main boards

Class	Description
TQBR	Т+ Акции и ДР
TQIF	Т+ Паи
TQTF	Т+ ETF
TQOB	Т+ Облигации
TQBD	Т+ Акции и ДР (расч. в USD)
TQTD	Т+ ETF (расч. в USD)
TQOD	Т+ Облигации (расч.в USD)
SMAL	Т+ Неполные лоты
CETS	Валютная секция

White / Black Lists

Dynamic rules are required to protect the strategy from manipulated dislocations, for example "pump and dump" stocks.

The lists can be maintained manually using Named Collections, or using statistical filters implemented as scheduled SQL jobs.

collection('quik_order_blacklist').contains(upper(symbol))

Create Data Rules

To analyze and correlate data in memory, create rules to maintain last trade and quote for each instrument.

Open Rules > Import page in ATSD and upload the following two rules from quote-base-rules.xml.

The quik_trade rule maintains the last trade for each instrument.

The quik_quote rule maintains the consolidated quote for each instrument.

The in-memory windows created by these rules for each instrument are continuously updated as the data from the Quik workstation is received and processed by ATSD.

Create Arbitrage Rules

Create an arbitrage rule that references base rules using the rule_window().

The arbitrage rules can now operate on matching instrument pairs, or baskets, using in-memory trades, quotes, and statistics to trigger algorithmic trades.

To automatically execute trades, send the trade signal command using the sendTcpMessageReply function, execute a script, or trigger an outgoing webhook to an external service.

@if{in_white_list && signal == 'buy'}
${sendTcpMessageReply('quik-01.example.org', 13770, buy_order_command)}
@end{}

The advantage of executing strategy rules in ATSD compared to local Lua scripts executed directly by the Quik workstation are as follows:

Offload heavy calculations to the server.
Apply strategies to all active instruments without sacrificing performance.
Correlate data from different sources - Quik and other sources.

Strategy Example

Redacted for non-public display.
Profitability example, daily result is T2 - T1 for T2 traded securities.

Availability Monitoring

Open Сервисы > Lua Скрипты and load the monitor.lua script.

Modify connection parameters in the monitor.conf file.

ATSD_HOST = atsd.example.org
ATSD_PORT = 8081
SEND_ASSETS = true
SEND_TRADES = true

Start the script which collects key Quik workstation parameters every 5 seconds including:

Connection status.
Quik server response time (ping).
Number of records in tables: orders, trades, depo_limits.

To deploy this portal, create a new portal in ATSD based on quik-monitor-portal.config.

To receive email/chat alerts when the data stops arriving, open Rules > Import page in ATSD and upload the following rules from quik-monitor-rules.xml.

Alert when the workstation disconnects from the broker's Quik server.
Alert when no connection status commands are received from the workstation within 3 minutes.
Alert when no BUY trades are received within 3 minutes for sber_[tqbr].
Alert when no quotes are received within 10 minutes for sber_[tqbr] and sber_[smal].

Reference

Driver Tracing

To enable detailed driver logging for connection and latency diagnostics, open the Windows registry with regedit and add Trace and TraceFile keys as follows:

HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\<DSN name>

Time Management

Configure the Windows operating system to continuously synchronize date and time using NTP.

net stop w32time
w32tm /config /syncfromflags:manual /manualpeerlist:"0.it.pool.ntp.org 1.it.pool.ntp.org 2.it.pool.ntp.org 3.it.pool.ntp.org" /reliable:yes
net start w32time
w32tm /config /update
w32tm /resync /rediscover

You can also sync to the Moscow Exchange NTP servers in the col-location zone.

w32tm /config /syncfromflags:manual /manualpeerlist:"91.203.252.12 91.203.254.12" /reliable:yes

To verify that NTP server is available:

w32tm /stripchart /computer:91.203.252.12 /dataonly /samples:5

w32tm /query /config
w32tm /query /peers

[Configuration]
EventLogFlags: 2 (Local)
AnnounceFlags: 5 (Local)
TimeJumpAuditOffset: 28800 (Local)
MinPollInterval: 6 (Local)
MaxPollInterval: 10 (Local)
MaxNegPhaseCorrection: 172800 (Local)
MaxPosPhaseCorrection: 172800 (Local)
MaxAllowedPhaseOffset: 300 (Local)

FrequencyCorrectRate: 4 (Local)
PollAdjustFactor: 5 (Local)
LargePhaseOffset: 50000000 (Local)
SpikeWatchPeriod: 900 (Local)
LocalClockDispersion: 10 (Local)
HoldPeriod: 5 (Local)
PhaseCorrectRate: 7 (Local)
UpdateInterval: 100 (Local)

[TimeProviders]
NtpClient (Local)
DllName: C:\Windows\system32\w32time.dll (Local)
Enabled: 1 (Local)
InputProvider: 1 (Local)
AllowNonstandardModeCombinations: 1 (Local)
ResolvePeerBackoffMinutes: 15 (Local)
ResolvePeerBackoffMaxTimes: 7 (Local)
CompatibilityFlags: 2147483648 (Local)
EventLogFlags: 1 (Local)
LargeSampleSkew: 3 (Local)
SpecialPollInterval: 3600 (Local)
Type: NTP (Local)
NtpServer: 0.pool.ntp.org, 1.pool.ntp.org, 2.pool.ntp.org (Local)

NtpServer (Local)
DllName: C:\Windows\system32\w32time.dll (Local)
Enabled: 1 (Local)
InputProvider: 0 (Local)
AllowNonstandardModeCombinations: 1 (Local)

VMICTimeProvider (Local)
DllName: C:\Windows\System32\vmictimeprovider.dll (Local)
Enabled: 1 (Local)
InputProvider: 1 (Local)

Modify the built-in scheduled task in Windows Scheduler to run more often.

Alternatively create a batch script and execute it every 5 minutes. If necessary, add sleep commands to trigger NTP synchronization more often.

net start w32time

for /l %%x in (1, 1, 100000) do (
w32tm /resync /rediscover
timeout 60 /nobreak
)

Virtual Box Port Forwarding

If the Windows VM with the Quik workstation is installed as a Virtual Box VM, the VM needs to be configured to accept TCP commands generated from ATSD.

Open the Network tab in the VM settings in Virtual Box.

Click Port Forwarding if the VM runs in NAT mode.

Add a forwarding configuration to pass allow TCP traffic from host port to the VM port.

Configure the Windows firewall to allow incoming traffic on the designated VM port.

Brokers

Top 5 brokers by equity trade volume in April 2019, in billion ₽.

Broker	Volume
БКС	475
Ренессанс Брокер	200
Сбербанк	164
ВТБ	152
Открытие	94

Top 5 brokers by active clients in April 2019.

Broker	Count
Сбербанк	45,696
Тинькофф Банк	36,077
ВТБ	23,330
Открытие	22,338
БКС	22,232

Broker and Exchange Fees

The exchange and brokers charge fees based on the daily trade volume.

Sberbank trading commissions:

Daily Range	Broker Fee, %	1,000 ₽ Stock Trade Fee
< 1M ₽	`0.060%`	`0.60 ₽`
1M - 50M ₽	`0.035%`	`0.35 ₽`
50M - 100M ₽	`0.012%`	`0.12 ₽`
100M+ ₽	`0.006%`	`0.06 ₽ (6 kopeeks)`

Exchange fee: 0.01% (10 kopeeks above) with a minimum of 0.02 ₽ per trade.

# Integrating Quik with ATSD: Board Arbitrage

# Table of Contents

# Quik Overview

# Quik Features

# Market Data

# Data Exposed by Quik

# Data Available from Exchange

# ATSD Quik ODBC Driver

# Driver Setup

# Regional Settings

# Export Trades

# Export Quotes (Level 1)

# Verify Data Insertion in ATSD

# Enable Quik Logging

# Verify Trade Data

# Verify Quote Data

# Board Arbitrage

# Strategy Description

# Boards / Classes

# White / Black Lists

# Create Data Rules

# Create Arbitrage Rules

# Strategy Example

# Availability Monitoring

# Reference

# Driver Tracing

# Time Management

# Virtual Box Port Forwarding

# Brokers

# Broker and Exchange Fees