Foreign Trade¶

Overview¶

The foreign trade processeed trade of investors per security

Available Format: API Restful

Coverage: Securities only

Expected data acquired date:

Expected data frame

Exchange	Coverage period
HOSE	Since inception of stock, last 20Y
HNX	Last 5 month, start at 2024/01

Structure¶

Field descriptions¶

The table of description of dataset

Field	Description
ticker	The abbr of security
date	The trading date
total_room	Room can own by foreign
current_room	Current volume that foreign can buy
current_rate	The current rate (in percentage) that foreign can buy
order_matching_bid_volume	Bid volume in Order Matching option
order_matching_bid_value	Bid value in Order Matching option
order_matching_ask_volume	Ask volume in Order Matching option
order_matching_ask_value	Ask value in Order Matching option
put_through_bid_volume	Bid volume in Put Through option
put_through_bid_value	Bid value in Put Through option
put_through_ask_volume	Ask volume in Put Through option
put_through_ask_value	Ask value in Put Through option
total_volume	Total volume (included both Oder Matching and Put through)
total_value	Total value
volume_unit	The unit of volume in the record
value_unit	The unit of value in the record
value_currency	Currency that represented for value

Included record handler schema: Yes
Follow ACID method: Yes

Field datatypes¶

Field	Data Type	Mandatory/Optional	Key
ticker	string (length >= 3, length <= 50)	M	PK
date	Date	M	PK
total_room	Big Integer (>= 0)	O
current_room	Big Integer	O
current_rate	Numeric(10, 5)	O
order_matching_bid_volume	Big Integer (>= 0)	O
order_matching_bid_value	Numeric(35, 5) (>= 0)	O
order_matching_ask_volume	Big Integer (>= 0)	O
order_matching_ask_value	Numeric(35, 5) (>= 0)	O
put_through_bid_volume	Big Integer (>= 0)	O
put_through_bid_value	Numeric(35, 5) (>= 0)	O
put_through_ask_volume	Big Integer (>= 0)	O
put_through_ask_value	Numeric(35, 5) (>= 0)	O
total_volume	Big Integer (>= 0)	O
total_value	Numeric(35, 5) (>= 0)	O
volume_unit	Integer	O
value_unit	Integer	O
value_currency	String[VND]	O

Note:

Unit for volume: 1

Unit for value: 1 (in VND currency)

Constructor¶

Pagination: Yes
Column Oriented Swap: Yes

Pipeline¶

The trade of foreign investors is executed at different securities firm which processed order of trade. Then at the exchange level will aggreate odered Based on that, each exchange will be the single source of truth that collected that dataset.

It's followed by the following diagram

flowchart LR

    subgraph exchange [Exchange]
        hsx[HOSE - Ho Chi Minh]
        hnx[HNX - Ha Noi]
    end

    ft[Foreign Investors] -- processed trade --> securities[Securities Firm] -- register order metadata --> exchange

Detailed by exchange¶

Exchange HOSE:

The result has been captured by each ticker.

Schedule: Daily fetch 5 20,1 * * * (Asia/Ho_Chi_Minh)

For the example, you can load information at Symbol AAA on listed view

Delcare BUCKET_TICKER that is the ticker listed on HOSE at the current state.

For loop over BUCKET_TICKER then executed

[Step 1] Build the URL that is underlying API endpoint

TICKER: str = "AAA"
URL: str = "https://www.hsx.vn/Modules/Rsde/Report/GetForeignTradingResult?symbol=AAA&_search=false&nd=1705647900661&rows=2147483647&page=2&sidx=id&sord=desc"

[Step 2] Get total pagination information

Example:

{
  "page": 2,
  "total": 1,
  "records": 22,
  "rows": [
    {
      "id": "25/01/2024",
      "cell": [
        "25/01/2024",
        "382.274.496,00",
        "373.557.883,00",
        "1.729,00",
        "1.731,85",
        "1.546,00",
        "1.569,47",
        "0,00",
        "0,00",
        "0,00",
        "0,00",
        "1.729,00",
        "1.546,00",
        "2,28"
      ]
    },
    {
      "id": "24/01/2024",
      "cell": [
        "24/01/2024",
        "382.274.496,00",
        "373.710.383,00",
        "282,00",
        "271,46",
        "127,00",
        "122,08",
        "0,00",
        "0,00",
        "0,00",
        "0,00",
        "282,00",
        "127,00",
        "2,24"
      ]
    }
  ]
}

[Step 3] Navigation:

(a) If records = 0: Continue loop to other

(b) If records > 0: Go loop on total_page and parse into dataframe (with all String sylte) and concat the result

[Step 4] Parse into targeted schema at the last result

(a) Convert datetime (format: DD/MM/YYYY)

(b) Convert number style

[Step 5] Annotate the dataframe

(a) Ticker: The ticker argument has been fetched

[Step 6] Upload to the endpoint at submarine

End loop and continue.

Note: HOSE just contain the language in vietnamse

Exchange HNX:

Schedule: Daily fetch 5 20,1 * * * (Asia/Ho_Chi_Minh)

The result has been captured by each ticker. For HNX, the historical data just can go back last 5 month only.

Example on VCS

The method is using selenium and go to overloop

Note: The language is different, so make sure you have check the language