EOD Files Specifications
Introduction
This document provides a detailed description of each End of Day file supported by GTN MENA, including description and data type for each field.
Reporting Timelines
GTN MENA's end of day back-office process runs twice for all days of the year, regardless of weekends and country specific public holidays.
Therefore, EOD files are available and delivered for all report dates, at 7 PM SGT time and 9 AM SGT time, regardless of the country where a client is based.
For EOD files subscribed on monthly basis, these reports are generated on the last day of the month.
Time Standard
All time stamps in the reports and files are in GMT time standard.
Decimal Data Types
The precision and scale for all decimal fields is specified in this document. This means that for any decimal field, there could be a total of up to n number significant digits (precision), which can include up to n number of decimal places (scale).
Note that a minus sign, “-“, and a decimal point, “.”, are not included in the count of significant digits.
GTN MENA does not pad its decimal fields with spaces or zeroes, so the precision in the data type reflects the maximum number of significant digits that could be reported.
Supported File Formats
GTN MENA supports the delivery of EOD files in below format via email or SFTP subscriptions:
• Excel
EOD File Types
All Cash Transaction Data
Field Name | Description | Data Type | Field Length | Scale | Format | Default Value | List Possible Value |
---|---|---|---|---|---|---|---|
ACCOUNT_CURRENCY | Cash account currency | VARCHAR2 | 10 | - | - | - | - |
AMT_IN_TRANS_CURRENCY | Amount in transaction currency | NUMBER | 22 | 5 | YYYY-MM-DD HH24:MI:SS | - | - |
APPROVED_DATE | L2 approved date | VARCHAR2 | 19 | - | YYYY-MM-DD HH24:MI:SS | - | - |
AS_OF_DATE | Report date | VARCHAR2 | 10 | - | YYYY-MM-DD | - | - |
BRANCH | Institution ID | NUMBER | 22 | - | - | - | - |
BROKER_COMMISSION | Broker commission value | NUMBER | 22 | 5 | - | - | - |
BROKER_ORDER_FEES | Broker order fees value (Sub customer order fees - Master order fees) | NUMBER | 22 | 5 | - | - | - |
BROKER_VAT | - | NUMBER | 18 | 5 | - | - | - |
BANK_ACCOUNT_NO | - | VARCHAR2 | 100 | - | - | - | - |
CASH_ACCOUNT_NO | Customer cash account number | VARCHAR2 | 50 | - | - | - | - |
CASH_ACC_EXTERNAL_REFERENCE | - | VARCHAR2 | 50 | - | - | - | - |
CHANNEL | Channel | VARCHAR2 | 50 | - | - | - | FIX Web TW - OLD OMS Client Manual AT TWS Cond Mobile Web IVR Applet DT Android Tab BB iPhone iPad Web AT Android MTP iPhone MTP iPad MTP Android MTP Android Tab MTP BB MTP Desktop TWS 11 Auto Trade Auto Liquidator Rubix Mobile Rubix I Phone Rubix iPad Rubix Android Rubix Android Tab Rubix Desktop Unsolicited Third Party XT MTP Web New TWS New DT MTP Mobile Web DT 6 RM Client Rubix Investment |
CHEQUE_DATE | Cheque date | DATE | 7 | - | DD-MON-YYYY | - | - |
CHEQUE_NUMBER | Cheque number | VARCHAR2 | 20 | - | - | - | - |
COST_COMMISION | Cost commission (Total commission - Master order fees) | NUMBER | 22 | 5 | - | - | - |
COST_ORDER_FEE | Master Order fees | NUMBER | 22 | 5 | - | - | - |
CUSTOMER_BANK_ACC_NO | Customer bank account number | VARCHAR2 | 200 | - | - | - | - |
CUSTOMER_BANK_BRANCH | Customer bank account branch | VARCHAR2 | 255 | - | - | - | - |
CUSTOMER_BANK_NAME | Customer bank account name | VARCHAR2 | 255 | - | - | - | - |
CUSTOMER_NO | Customer number | VARCHAR2 | 100 | - | - | - | - |
CUSTOMER_BANK_SWIFT_CODE | Customer bank swift code | VARCHAR2 | 20 | - | - | - | - |
CUSTOMER_BANK_ACCOUNT_IBAN | Customer's bank account IBAN | VARCHAR2 | 255 | - | - | - | - |
ELIGIBLE_SHARES | Customer net holdings eligible for the transaction | NUMBER | 22 | - | - | - | - |
EXTERNAL_REF_NO | Customer external ference number | VARCHAR2 | 50 | - | - | - | - |
EXCHANGE | Exchange | VARCHAR2 | 10 | - | - | - | - |
ELIGIBILITY_DATE | Corporate Action Eligibility Date | DATE | 7 | - | - | - | - |
FILLED_QTY | Filled quantity | NUMBER | 22 | 5 | - | - | - |
F_L_DATE | System date (Not being used) | DATE | 7 | - | MM/DD/YYYY | - | - |
INSTITUTION_BANK_ACC_NO | Institution bank account number | VARCHAR2 | 50 | - | - | - | - |
INSTITUTION_BANK_CURRENCY | Institution bank account currency | VARCHAR2 | 3 | - | - | - | - |
INSTITUTION_BANK_NAME | Institution bank name | VARCHAR2 | 255 | - | YYYY-MM-DD HH24:MI:SS | - | - |
KNET_REFERNCE_NUMBER | Online transaction reference number(knet) | VARCHAR2 | 100 | - | - | - | - |
KNET_TRACKING_ID | KNET tracking ID | VARCHAR2 | 100 | - | - | - | - |
KNET_TRANSACTION_ID | KNET Transaction ID | VARCHAR2 | 100 | - | - | - | - |
KNET_REFERNECE_ID | Online transaction reference ID (knet) | VARCHAR2 | 100 | - | - | - | - |
KNET_AUTH_ID | - | VARCHAR2 | 100 | - | - | - | - |
ISIN_CODE | ISIN Code | VARCHAR2 | 30 | - | - | - | - |
L1_APPROVE_BY | L1 approved by user | NVARCHAR2 | 712 | - | - | - | - |
L1_APPROVED_DATE | L1 approved date | DATE | 7 | - | YYYY-MM-DD HH24:MI:SS | - | - |
L2_APPROVED_BY | L2 approved by user | NVARCHAR2 | 712 | - | - | - | - |
L2_APPROVED_DATE | L2 approved date | DATE | 7 | - | YYYY-MM-DD HH24:MI:SS | - | - |
MARKET | Exchange | VARCHAR3 | 10 | - | - | - | - |
MASTER_VAT | - | NUMBER | 18 | 5 | - | - | - |
NARRATION | Narration | VARCHAR2 | 4000 | - | - | - | - |
NET_SETTLE | Customer net settlement amount of the order Eg: order value + commission + order fees for buy | NUMBER | 22 | 5 | - | - | - |
ORDER_ID | Order number | NUMBER | 22 | - | - | - | - |
ORDER_QTY | Order quantity | NUMBER | 22 | 5 | - | - | - |
ORDER_VALUE | Order value | NUMBER | 22 | 5 | - | - | - |
ORDER_CURRENCY | If payment method Trasnfer in or Transfer in Reverse ; from Cash account currency code If payment method Transfer out or Transfer out Reverse; to cash account currency code bulk transaction upload currency other transactions will be denoted as 'N/A' | VARCHAR2 | 10 | - | - | - | - |
PAYMENT_METHOD | Payment method | VARCHAR2 | 20 | - | - | - | Transfer Cheque Cash Bank Bank Transfer KNET Deposit Transfer out Transfer out Reverse Transfer in Transfer in Reverse N/A |
PORTFOLIO_NO | Portfolio number | VARCHAR2 | 20 | - | - | - | - |
PRICE | Price | NUMBER | 22 | 5 | - | - | |
REFERENCE_NUMBER | Online transaction reference number(knet) in knet transactions. Will be null in other transactions | VARCHAR2 | 100 | - | - | - | - |
RELATED_TRANSACTION_ID | Related transaction ID | NUMBER | 22 | - | - | - | - |
SYMBOL | Symbol code | VARCHAR2 | 25 | - | - | - | - |
STAMP_DUTY | Customer's stamp duty | NUMBER | 18 | 5 | - | - | - |
TOTAL_COMMISION | Commision (withour order fee) | NUMBER | 22 | 5 | - | - | - |
TOTAL_COST | Total commission for master account | NUMBER | 22 | 5 | - | - | - |
TOTAL_ORDER_FEES | Sub customer order fees | NUMBER | 22 | 5 | - | - | - |
TOTAL_REVENUE | Total revenue (Sub level commission - master commission) | NUMBER | 22 | 5 | - | - | - |
TRADING_AC_NO | Trading account number | VARCHAR2 | 50 | - | - | - | - |
TRANSACTION_ID | Transaction ID | NUMBER | 22 | - | - | - | - |
TRANS_BASE_CURR_RATE | Transaction base currency rate | NUMBER | 22 | 5 | YYYY-MM-DD HH24:MI:SS | - | - |
TXN_CODE | Transaction code | VARCHAR2 | 10 | - | - | - | - |
TXN_DATE | Transaction date | VARCHAR2 | 19 | - | YYYY-MM-DD HH24:MI:SS | - | - |
VALUE_DATE | Value date | VARCHAR2 | 19 | - | YYYY-MM-DD HH24:MI:SS | - | - |
VAT_AMOUNT | VAT amount | NUMBER | 22 | 5 | - | - | - |
Cash Balance Data
Field Name | Description | Data Type | Field Length | Scale | Format | Default Value | List Possible Value |
---|---|---|---|---|---|---|---|
AS_OF_DATE | Report date | VARCHAR2 | 10 | - | YYYY-MM-DD | - | - |
AVAILABLE_AMOUNT | Available cash balance after deducting all the blocks and pending amounts | NUMBER | 22 | 5 | - | - | - |
BLOCKED_AMOUNT | Bloacked cash amount | NUMBER | 22 | 5 | - | - | - |
| BRANCH | Institution ID | NUMBER | 22 | - | - | - | - | CASH_ACCOUNT_NO | Cash account number | VARCHAR2 | 50 | - | - | - | - | CURRENCY | Cash account currency | VARCHAR2 | 10 | - | - | - | - | CUSTOMER_NO | Customer number | VARCHAR2 | 100 | - | - | - | - | CUSTOMER_NATIONAL_ID | Customer's national ID | VARCHAR2 | 50 | 5 | - | - | - | CUSTOMER_BANK_ACCOUNT_NO | Customer's bank account number | VARCHAR2 | 100 | 5 | - | - | - | EXTERNAL_REF_NO | Customer external reference number | VARCHAR2 | 50 | - | - | - | - | F_L_DATE | System date (Not being used) | DATE | 7 | - | MM/DD/YYYY | - | - | PRIMARY_LIMIT |Primary trading limit | NUMBER | 22 | 5 | - | - | - | PRIMARY_LIMIT_EXPIRY_DATE | Expiry date for secondary trading limit | DATE | 7 | 8 | DD-MON-YYYY HH24:MI:SS | - | - | SECONDARY_LIMIT | Secondary trading limit | NUMBER | 22 | 5 | - | - | - | SECONDARY_LIMIT_EXPIRY_DATE | Expiry date for secondary trading limit | DATE | 7 | - | DD-MON-YYYY HH24:MI:SS | - | - | TOTAL_BALANCE | Trade balance | NUMBER | 22 | 5 | - | - | - | UNSETTLED_CASH | Amount pending settlement | NUMBER | 22 | 5 | - | - | - |
Holding Transaction Data
Field Name | Description | Data Type | Field Length | Scale | Format | Default Value | List Possible Value |
---|---|---|---|---|---|---|---|
APPROVED_DATE | L2 approved date | VARCHAR2 | 19 | - | YYYY-MM-DD HH24:MI:SS | - | - |
AS_OF_DATE | Report date | VARCHAR2 | 10 | - | YYYY-MM-DD | - | - |
AVGCOST | Average cost | NUMBER | 22 | 8 | - | 0 | - |
BRANCH | Institution ID | NUMBER | 22 | - | - | - | - |
CANCELLED_BY | Request cancelled by user | NVARCHAR2 | 712 | - | - | - | - |
CANCELLED_DATE | Request cancelled date | DATE | 7 | - | - | - | - |
CUSTODIAN | Custodian SID | VARCHAR2 | 75 | - | - | - | - |
CUSTOMER_NO | Customer number | VARCHAR2 | 100 | - | - | - | - |
ELIGIBILITY_DATE | Holdings eligibility date | DATE | 7 | - | DD-MON-YYYY | - | - |
ELIGIBLE_SHARES | Customer net holdings eligible for the transaction | NUMBER | 22 | 8 | - | - | - |
EXCHANGE | Exchange code | VARCHAR2 | 160 | - | - | - | - |
EXTERNAL_REF_NO | Customer external reference number | VARCHAR2 | 50 | - | - | - | - |
F_L_DATE | System date (Not being used) | DATE | 7 | - | MM/DD/YYYY | - | - |
ISIN_CODE | Symbol ISIN code | VARCHAR2 | 30 | - | - | - | - |
L1_APPROVED_BY | L1 approved by user | NVARCHAR2 | 712 | - | - | - | - |
L1_APPROVED_DATE | L1 approved date | DATE | 7 | - | YYYY-MM-DD HH24:MI:SS | - | - |
L2_APPROVED_BY | L2 approved by user | NVARCHAR2 | 712 | - | - | - | - |
L2_APPROVED_DATE | L1 approved date | DATE | 7 | - | YYYY-MM-DD HH24:MI:SS | - | A1 |
MARKET | Exchange code | VARCHAR2 | 160 | - | - | - | - |
NARRATION | Narration | VARCHAR2 | 500 | - | - | - | - |
PORTFOLIO_NO | Customer security account number | VARCHAR2 | 20 | - | - | - | - |
QUANTITY | Holding quantity | NUMBER | 22 | 8 | - | - | - |
REFERENCE_NO | Transaction reference number | VARCHAR2 | 3200 | - | - | - | - |
STATUS | Record status | VARCHAR2 | 11 | - | - | 1 | 1-Pending 2-Verified 3-Approved L1 4-Approved L2 5-Cancelled 6-Rejected 7-Approved |
SYMBOL | Symbol code | VARCHAR2 | 3200 | - | - | - | - |
TIME_STAMP | Time stamp | VARCHAR2 | 19 | - | YYYY-MM-DD HH24:MI:SS | - | - |
TXN_TYPE | Transaction type | NUMBER | 22 | - | - | - | 1-Stock Deposit 2-Stock Withdraw 3-Bonus issue 4-Stock Adjustment 5-Stock Split 6-Reverse Split 7-Stock transfer 8-Right Issue 9-Stock dividend 10-Symbol rename 11 - Reverse Bonus Issue 12 - Reverse Right Issue 13 - Reverse of Stock Split 14 - Reverse of Reverse Stock Split 15 - Stock Merger 16 - Spin Off 17 - Reverse Spin Off 18 - Reverse Stock Dividend 19 - Reverse Stock Merger 20 - Charges and Refunds 21 - Reverse Charges and Refunds |
Holding Balance Data
Field Name | Description | Data Type | Field Length | Scale | Format | Default Value | List Possible Value |
---|---|---|---|---|---|---|---|
AS_OF_DATE | Report date | VARCHAR2 | 10 | - | YYYY-MM-DD | - | - |
AVAILABLEQTY | Available holding quantity after deducting blocked and pending amounts | NUMBER | 22 | 5 | - | - | - |
AVG_COST | Average cost of the holding | NUMBER | 22 | 8 | - | - | - |
AVG_PRICE | Average price of the holding | NUMBER | 22 | 8 | - | - | - |
BLOCK_ACTIVE | Manually entered additional field value customer summary -> view holdings -> set additional fields | VARCHAR2 | 1000 | - | - | - | - |
BRANCH | Institution ID | NUMBER | 22 | - | - | - | - |
CERTIFICATE_TYPE | Manually entered additional field value customer summary -> view holdings -> set additional fields | VARCHAR2 | 1000 | - | - | - | - |
CLOSE_PRICE | Closing price of the holding | NUMBER | 22 | 8 | - | - | - |
CUSTODIAN | Custodian SID | VARCHAR2 | 75 | - | - | - | - |
CUSTOMER_NO | Customer number | VARCHAR2 | 100 | 5 | - | - | - |
CUSTOMER_NATIONAL_ID | Customer's national ID | VARCHAR2 | 50 | - | - | - | - |
CUSTOMER_BANK_ACC_NO | Customer's bank account number | VARCHAR2 | 100 | - | - | - | - |
EXCHANGE | Exchange code | VARCHAR2 | 10 | - | - | - | - |
EXTERNAL_REF_NO | Customer external reference number | VARCHAR2 | 50 | - | - | - | - |
F_L_DATE | System date (Not being used) | DATE | 7 | - | MM/DD/YYYY | - | - |
HOLDING_TYPE | Holding type | VARCHAR2 | 7 | - | - | - | Long Short Futures CFD Spot |
ISIN_CODE | Symbol ISIN code | VARCHAR2 | 30 | - | - | - | - |
MARKET_PRICE | Market price | NUMBER | 22 | 5 | - | - | - |
MARKET_VALUE | Market value of holdings | NUMBER | 22 | 5 | - | - | - |
NET_HOLDINGS | Net holding quantity | NUMBER | 22 | 5 | - | - | - |
OPEN_CLOSE | Manually entered additional field value customer summary -> view holdings -> set additional fields | VARCHAR2 | 1000 | 5 | - | - | - |
PENDING_SELL | Pending sell quantity | NUMBER | 22 | 5 | - | - | - |
PORTFOLIO_NO | Customer portfolio number | VARCHAR2 | 20 | - | - | - | - |
SHORT_POSITION | Shorted holding quantity | NUMBER | 22 | 5 | - | - | - |
SYMBOL | Symbol code | VARCHAR2 | 25 | - | - | - | - |
SYMBOL_CURRENCY | Particular's symbol's currency | VARCHAR2 | 10 | - | - | - | - |
UNREALIZE_GAIN_LOSS | Unrealized gain loss (Net holdings * average cost) | NUMBER | 22 | 8 | - | - | - |