This section focuses on the underlying tables, views and functions that support the front-end views.

Table

idx_company_profile

Basic company information:

• company_name - the official full name of the company
• symbol - a 4-letter symbol appended with ".JK" to uniquely identify a company
• address - the address of which the company is headquartered
• email - the corporate/official email
• phone - the official phone number
• fax - the official fax number
• NPWP - the Taxpayer Identification Number or Nomor Pokok Wajib Pajak in Indonesian that must be owned by taxpayers
• website - the official website URL
• listing_date - the official listing date on which the company first listed
• listing_board - the listing board the company is on (New Economy, Acceleration, Development, Main, Watchlist)
• industry - the industry the company is operating on
• sub_industry - the sub-industry the company is operating on
• register
• shareholders - Array of JSON values containing current company shareholders. Structure:

{
    "name": the shareholder name,
    "type": the shareholder type or position (if they are executives or audit committees),
    "share_amount": the share amount they possess,
    "share_percentage": the share percentage (%) they own in the company,
    "share_percentage_change": the share percentage (%) change in the past month
}

• directors - Array of JSON values containing current company directors.
• commissioners - Array of JSON values containing current company commissioners.
• audit_committees - Array of JSON values containing company audit committees.
• delisting_date - the date on which the company is delisted from IDX
• sub_sector_id: serves as a reference to the subsector name stored in the idx_subsector_metadata table

Additional Metadata:

• wsj_format: The format of the financial report on WSJ used for our reference when processing the data in python and presentation on the frontend. Mapping: -1: Null, 1: General, 3: Insurance, 4: Banking
• yf_currency: The currency for the financial report obtained from YF API. If the currency is not IDR, it will be converted into IDR when processing the data in python, to ensure all the financial reports are stored in IDR in the DB. Mapping: 1: IDR, 2: USD
• current_source: Identify the source from which data for each symbol is obtained, which is used for our reference during the scraping process. Mapping: -1: Null, 1: YF, 2: WSJ, 3: Morningstar
• nologo: TRUE indicates the logo is unavailable at the default URL: https://storage.googleapis.com/sectorsapp/logo/XXXX.webp where XXXX is equal to the symbol name (e.g. ADRO). FALSE indicates that the logo is available at the default URL.
• morningstar_code: also known as performanceId, essential for making requests through the RapidAPI platform.
• updated_on

idx_company_forecast

• symbol
• year - the year estimated on
• revenue_estimate - estimated revenue based on Yahoo Finance (YF) analysis tab
• eps_estimate - estimated earnings per share based on Yahoo Finance (YF) analysis tab
• updated_on

idx_daily_data

Data that is stored here are based on daily grain.

• symbol
• date
• close - the last transacted cash/raw price before the market closes
• volume - the number of shares traded
• market_cap - equivalent of multiplying close price and volume, it is the total value of all traded outstanding shares of a company
• mcap_method - 1: directly from API, 2: bfill (share number based on fresh data from API), 3: ffill (share number based on latest data on db)
• updated_on

idx_dividend

Data that is stored here are based on ex-dividend date grain.

• symbol
• date - based on ex-date on regular and negotiation market
• dividend - dividend per share given by the company (adjusted by stock split)
• yield - yield of the dividend (null for current year)
• dividend_original - dividend per share given by the company
• updated_on

idx_esg_score

• symbol
• last_esg_update_date - last ESG score updated in idx website
• esg_score - value of the company's exposure to material ESG risk and how much company manages that risk. ESG values are grouped into 5 categories: Negligible, Low, Medium, High, Severe.
• controversy_risk - identification of the company involved in events that can negatively impact the holders' interests, environment, or company operation
• environment_risk_score - value of how the company businesses affect the environment, and how they can actively play their part as environmental stewards
• social_risk_score - value of the company reputation and relationship with stakeholders, such as local communities, suppliers, customers, employees, and affiliated parties, and how they can create a positive impact on their conditions and welfare
• governance_risk_score - value of the company considering how their build capable leadership operate principles of good governance
• updated_on

idx_financials_annual/_quarterly

• symbol
• date
• net_operating_cash_flow - also called cash from operating activities, it is the amount of cash generated by a company's regular business activities
• total_assets - total resources a company owns that provide economic value with expectations that it will provide future benefit
• total_liabilities - the amount a company owes that includes obligations and debts to outside parties
• total_current_liabilities - the total company's short-term financial obligations that are due within a year
• total_equity - the amount of money that represents the value of the investor's share
• total_revenue - also called sales, it is the amount of money a company generates through selling products/services
• net_income - also called profit/earnings, it is the amount of money a company earns after expenses (operation, dividends, taxes, interest, etc.)
• total_debt - the amount of interest-bearing obligations that a company owes, shows how much a company has to pay back to its lenders
• stockholders_equity - the remaining amount of assets available to the shareholders after all liabilities are paid
• ebit - the earnings before interests and taxes are deducted
• ebitda - the earnings before interests, taxes, depreciation, and amortization are deducted
• cash_and_short_term_investments - also called cash and cash equivalents in Banking and Insurance industries, it represents the value of company's cash assets or can be converted to cash immediately
• cash_only - also called cash and equivalents in Insurance industries, it represents all cash in hand or bank that are readily available
• total_cash_and_due_from_banks - equivalent of cash and equivalents in Banking industry
• diluted_shares_outstanding - total number of commons shares after all convertible securities are exercised
• gross_income - also called gross profit, it represents the profit of a company after deducting costs associated with selling products/services
• pretax_income - the income before deducting income taxes
• income_taxes - represents the tax from the government that is imposed on the company's income
• total_non_current_assets - the total assets that are expected to last, consumed, or converted into cash after a year
• free_cash_flow - the remaining amount of money a company has after paying its operating expenses (OpEx) and capital expenditures (CapEx)
• interest_expense_non_operating - the payable cost from borrowed funds
• operating_income - a company's earnings after deducting operational expenses (wages, COGS)
• source: refer to the current_source of idx_company_profile
• updated_on

idx_historical_mcap

Data that is stored here is based on the end-of-month data.

• symbol
• date
• market_cap

idx_institution_transactions

• symbol
• date - always end-of-month date
• net_transaction - the net sum of the change in the number of shares traded among the top 20 institutional buy transactions and the top 20 institutional sell transactions
• top_buyers - top 20 institutional buy transactions for that month, JSON object with "name" and "changeAmount" (change in the number of shares) as keys
• top_sellers - top 20 institutional sell transactions for that month, JSON object with "name" and "changeAmount" as keys
• updated_on

idx_key_stats

Data that is stored here is based on key statistics obtained in Yahoo Finance

• symbol
• forward_eps - forecasted earnings per share
• recommendation_mean - the average recommendation rating, ranging from 1 (strong buy) to 5 (sell)
• point_summaries - Summary of a stock in JSON format. E.g.

[
  {
    "name": "value",
    "point": 8,
    "maxpoint": 20
  },
  {
    "name": "competitive",
    "point": 1.5,
    "maxpoint": 17
  },
  {
    "name": "future",
    "point": 6,
    "maxpoint": 6
  },
  {
    "name": "ownership",
    "point": null,
    "maxpoint": null
  },
  {
    "name": "management",
    "point": null,
    "maxpoint": null
  },
  {
    "name": "dividend",
    "point": null,
    "maxpoint": null
  },
  {
    "name": "financials",
    "point": null,
    "maxpoint": null
  },
  {
    "updatedAt": "2024-01-10T07:38:09.826Z"
  }
]

• employee_num - the number of employees
• holders_breakdown - the % shares held by Institutions and Insiders, the float % held by Institutions, and the number of Institutions holding shares
• intrinsic_value - intrinsic value obtained through Discounted Cash Flow Analysis
• updated_on

idx_manual_input

• symbol
• financial_year
• sankey_component - the JSON object needed to generate the Sankey diagram
• income_stmt_metrics - the raw data for some income statement metrics
• source_url - the source of the financial report
• updated_on

idx_stock_split

Data that is stored here is based on the stock split date of the symbol.

• symbol
• date
• split_ratio - value of stock split ratio calculated from the result of dividing new ratio by old ratio
• updated_on
• applied_on - the time when the split data is applied on to the other tables in the database (see check_and_execute_split)

idx_subsector_metadata

• subsector_id
• sector: Sector name based on IDX classification
• sub_sector: Subector name based on IDX classification
• slug: Part of the URL that identifies a particular subsector page on sectors.app
• description: General description of the subsector, including companies with the top market cap

idx_ipo_perf

• symbol
• chg_7d: 7-days price change since first trading day
• chg_30d: 30-days price change since first trading day
• chg_90d: 90-days price change since first trading day
• chg_365d: 365-days price change since first trading day
• updated_on

sgx_companies

Columns:

• symbol - Standard symbol from each companies from sgx and yahoo finance
• name - The name of the company
• investing_symbol - Symbol that used to get data from investing.com
• currency - Currency that is used for the data (except close, market_cap, dividend, revenue, and earnings
• close - Last 30 close prices of each stock (already converted to SGD)
• market_cap - Daily market capitalization value for each stock (already converted to SGD)
• volume - Daily transaction volume for each stock
• pe - Trailing P/E
• revenue - Last revenue values from last annual financial statement (From Yahoo Finance and already converted to SGD)
• eps - Last EPS value from annual financial data
• beta - beta value of each stock (5 years Monthly)
• forward_dividend - forward dividend value
• forward_dividend_yield - forward dividend yield value
• dividend_ttm - Trailing twelve months dividend value (if the company didn’t make any dividend payment in the last twelve months, the value will be 0)
• daily_signal - Daily Buy/Sell signal from investing.com
• weekly_signal - Weekly Buy/Sell signal from investing.com
• monthly_signal - Monthly Buy/Sell signal from investing.com
• change_1d - Daily price change rate (in decimal)
• change_7d - Seven days price change rate (in decimal)
• change_1m - One months price change rate (in decimal)
• change_ytd - Year to date price change rate (in decimal)
• change_1y - One year price change rate (in decimal)
• change_3y - Three year price change rate (in decimal)
• pe_ttm - Trailing twelve months Price to Earning Ratio value
• ps_ttm - Trailing twelve months Price to Sales Ratio value
• pcf - Trailing Price to Cash Flow Ratio
• pcf_ttm - PRICE to Cash Flow Ratio ttm
• pb - Current Price to Book Value
• five_year_eps_growth - 5 year EPS value growth Rate
• five_year_sales_growth - 5 years Sales value growth Rate
• five_year_capital_spending_growth - 5 years Sales value growth Rate
• asset_turnover - Trailing twelve month asset turnover
• inventory_turnover_ttm - Trailing twelve month inventory turnover
• receivable_turnover - Trailing Twelve month inventory turnover
• gross_margin - Trailing Twelve Month gross margin
• operating_margin - Trailing Twelve Month operating margin
• net_profit_margin - Trailing Twelve Month net profit margin
• quick_ratio - Most Recent Quarter quick ratio
• current_ratio - Most Recent Quarter current ratio
• debt_to_equity - Most Recent Quarter Total Debt to Equity
• dividend_yield_5y_avg - Dividend yield 5 years average
• dividend_growth_rate - Dividend value annual (financial year) growth rate
• payout_ratio : Trailing Twelve Month payout ratio value
• sector - Sector group for each ticker
• sub_sector - Sub-sector group for each ticker
• employee_num - Number of employee in company
• historical_revenue - 'Total Revenue' value from 5 last annual financial data + ttm 'Total Revenue' value (From Yahoo Finance and already converted to SGD)
• historical_earnings - 'Net Income' value from 5 last annual financial data + ttm 'Net Income' value (From Yahoo Finance and already converted to SGD)
• earnings : Last 'Net Income' value from annual financial data (From Yahoo Finance and already converted to SGD)

klse_companies

Columns:

• symbol - Standard symbol from each companies from klse and yahoo finance
• name - The name of the company
• investing_symbol - Symbol that used to get data from investing.com
• currency - Currency that is used for the data (except close, market_cap, dividend, revenue, and earnings
• close - Last 30 close prices of each stock (already converted to MYR)
• market_cap - Daily market capitalization value for each stock (already converted to MYR)
• volume - Daily transaction volume for each stock
• pe - Trailing P/E
• revenue - Last revenue values from last annual financial statement (From Yahoo Finance and already converted to MYR)
• eps - Last EPS value from annual financial data
• beta - beta value of each stock (5 years Monthly)
• forward_dividend - forward dividend value
• forward_dividend_yield - forward dividend yield value
• dividend_ttm - Trailing twelve months dividend value (if the company didn’t make any dividend payment in the last twelve months, the value will be 0)
• daily_signal - Daily Buy/Sell signal from investing.com
• weekly_signal - Weekly Buy/Sell signal from investing.com
• monthly_signal - Monthly Buy/Sell signal from investing.com
• change_1d - Daily price change rate (in decimal)
• change_7d - Seven days price change rate (in decimal)
• change_1m - One months price change rate (in decimal)
• change_ytd - Year to date price change rate (in decimal)
• change_1y - One year price change rate (in decimal)
• change_3y - Three year price change rate (in decimal)
• pe_ttm - Trailing twelve months Price to Earning Ratio value
• ps_ttm - Trailing twelve months Price to Sales Ratio value
• pcf - Trailing Price to Cash Flow Ratio
• pcf_ttm - PRICE to Cash Flow Ratio ttm
• pb - Current Price to Book Value
• five_year_eps_growth - 5 year EPS value growth Rate
• five_year_sales_growth - 5 years Sales value growth Rate
• five_year_capital_spending_growth - 5 years Sales value growth Rate
• asset_turnover - Trailing twelve month asset turnover
• inventory_turnover_ttm - Trailing twelve month inventory turnover
• receivable_turnover - Trailing Twelve month inventory turnover
• gross_margin - Trailing Twelve Month gross margin
• operating_margin - Trailing Twelve Month operating margin
• net_profit_margin - Trailing Twelve Month net profit margin
• quick_ratio - Most Recent Quarter quick ratio
• current_ratio - Most Recent Quarter current ratio
• debt_to_equity - Most Recent Quarter Total Debt to Equity
• dividend_yield_5y_avg - Dividend yield 5 years average
• dividend_growth_rate - Dividend value annual (financial year) growth rate
• payout_ratio : Trailing Twelve Month payout ratio value
• sector - Sector group for each ticker
• sub_sector - Sub-sector group for each ticker
• employee_num - Number of employee in company
• historical_earnings - 'Total Revenue' value from 5 last annual financial data + ttm 'Total Revenue' value (From Yahoo Finance and already converted to MYR)
• historical_revenue - 'Net Income' value from 5 last annual financial data + ttm 'Net Income' value (From Yahoo Finance and already converted to MYR)
• earnings : Last 'Net Income' value from annual financial data (From Yahoo Finance and already converted to MYR)

index_daily_data

• index_code - Index Code based on IDX code
• date
• price - close price of each index in each date
• index_name - Index Full Name

idx_company_customer

Data that stored here is based on the manual data scraping (excel file) that already processed

• supplier_ticker - The supplier company (the data based on this company financial statement) ticker symbol
• client_name - The name of company where the revenue came from (client)
• financial_year - The financial year where the revenue data is taken
• client_ticker - The ticker (symbol) where the revenue came from
• revenue_amount - The amount of revenue get from each clients

sgx_short_sell

Data that stored here is the short sell daily transaction data based on the sgx website

• name - The company name based on the sgx shor sell data
• date - The transaction date of the short sell
• symbol - The symbol (using yf symbol) of the company (The symbol is gather using the fuzzywuzzy package to search the similarity between the company name from the sgx short sell website and our data)
• volume - The volume of the short sell transaction (in number of shares)
• value - The amount (value) in SGD for each short sell transaction

idx_news

Consists of the news inserted from streamlit app, further development possible.

• id
• created_at - Timestamp of insertion
• title - News title
• body - News body
• source - News URL
• timestamp - Timestamp of news article
• sector - Sector category of the news article
• sub_sector - Subsector category of the news article
• tags - News Classifications
• tickers - List of Tickers relevant to the news article

idx_news_logs

Logs for the table idx_news

• id
• message - Description of request
• request_method - GET/POST/DELETE
• request_url - Request source URL
• remote_addr - Request source IP Address
• timestamp - Timestamp of log insertion
• level - Level of log

idx_filings

Consists of filings/insider trading news inserted via streamlit app

• id
• created_at - Timestamp of insertion
• title - News title
• body - News body
• source - News URL
• timestamp - Timestamp of news article
• sector - Sector category of the news article
• sub_sector - Subsector category of the news article
• tags - News Classifications
• tickers - List of Tickers relevant to the news article
• transaction_type - Category of transaction
• holding_before - Shareholding before transaction
• holding_after - Shareholding after transaction
• amount_transaction - Amount in transaction
• holder_name - Shareholder name

idx_all_time_price

Consist of the price and date of stocks in this condition: All Time High, All Time Low, 52 Weeks High, and 52 Weeks Low

• symbol - Symbol of the company
• date - The date when the price reach each condition
• price - The value of the 4 conditions
• type - The price condition (all_time_high, 52_w_high, all_time_low, 52_w_low)

Materialized Views

idx_calc_metrics_daily

Data that is stored here is based on the latest availability (quarterly or daily)

• pe_ttm - Price to Earnings Ratio (TTM): Latest Market Cap / Earnings TTM
• ps_ttm - Price to Sales (P/S) Ratio (TTM): Latest Market Cap / Revenue TTM
• pb_mrq - Price to Book (P/B) Ratio (MRQ): Latest Market Cap / Book Value MRQ = Latest Market Cap/ (Total Assets MRQ - Total Liabilities MRQ)
• pcf_ttm - Price to Cash Flow (P/CF) Ratio (TTM): Latest Market Cap/ Operating Cash Flow TTM
• enterprise_to_revenue - Enterprise to Revenue Ratio: (Latest Market Cap + EV excluding Market Cap) / Revenue TTM = (Latest Market Cap + Total Debt MRQ - Cash and Equivalents MRQ) / Revenue TTM
• enterprise_to_ebitda - Enterprise to EBITDA Ratio: (Latest Market Cap + EV excluding Market Cap) / EBITDA TTM = (Latest Market Cap + Total Debt MRQ - Cash and Equivalents MRQ) / EBITDA TTM
• latest_close - the latest closing price
• latest_close_date
• latest_m1_close - the second latest price
• daily_close_change - the change in price compared to 1 day ago
• price_change_7_days - the change in price compared to 7 days ago
• price_change_30_days - the change in price compared to 30 days ago
• price_change_365_days - the change in price compared to 365 days ago
• market_cap
• max_drawdown - the maximum drawdown (based on the daily closing price) in the last 365 days
• rsd_close - the relative standard deviation of the daily closing price in the last 365 days
• yearly_mcap_chg - the percentage change in market cap compared to 1 year ago
• mcap_change_7_days - the percentage change in market cap compared to 7 days ago
• mcap_change_14_days - the percentage change in market cap compared to 14 days ago
• mcap_change_30_days - the percentage change in market cap compared to 30 days ago
• abs_mcap_change_7_days - the absolute change in market cap compared to 7 days ago
• abs_mcap_change_14_days - the absolute change in market cap compared to 14 days ago
• abs_mcap_change_30_days - the absolute change in market cap compared to 30 days ago
• forward_pe - measure of p/e ratio using the forecasted earnings
• peg_ratio - p/e ratio divided by the earnings growth of a company

idx_calc_metrics_quarterly

Data that is stored here is based on the latest quarter (TTM or MRQ)

• yoy_quarter_revenue_growth - Year-over-Year Quarter Revenue Growth: (Total Revenue MRQ - Total Revenue in Previous Year’s Quarter) / Total Revenue in Previous Year’s Quarter
• yoy_quarter_earnings_growth - Year-over-Year Quarter Earnings Growth: (Total Earnings MRQ - Total Earnings in Previous Year’s Quarter) / Total Earnings in Previous Year’s Quarter
• interest_coverage_ratio - Interest Coverage Ratio: EBIT TTM / Interest Expense TTM
• cash_flow_to_debt_ttm - Cash Flow to Debt Ratio (TTM): Operating Cashflow TTM / Total Debt MRQ
• bve_mrq - Book Value (MRQ): Total Assets MRQ - Total Liabilities MRQ
• ev_not_with_mcap - Enterprise Value excluding Market Cap: Total Debt MRQ - Cash and Equivalents MRQ
• roa_ttm - Return on Assets (ROA) (TTM): Earnings TTM / Average Assets TTM
• roe_ttm - Return on Equity (ROE) (TTM): Earnings TTM / Average Equity TTM
• dar_mrq - Debt-to-Assets Ratio (MRQ): Total Debt MRQ / Total Assets MRQ
• der_mrq - Debt-to-Equity Ratio (MRQ): Total Debt MRQ / Total Equity MRQ
• net_profit_margin - Earnings TTM/ Revenue TTM
• total_assets_mrq
• total_liabilities_mrq
• total_equity_mrq
• total_debt_mrq
• revenue_ttm
• earnings_ttm
• operating_cashflow_ttm
• ebitda_ttm
• avg_diluted_shares_ttm
• free_cash_flow_ttm
• interest_expense_ttm

idx_calc_metrics_annual

Data that is stored here is based on the annual data (end of FY)

• symbol
• date
• pe
• pb
• ps
• pcf
• diluted_eps

idx_aggregated_calc

This MV is used to provide metrics aggregation of all active companies in IDX.
Columns:

• weighted_avg_drawdown
• median_drawdown
• weighted_avg_rsd
• median_rsd
• weighted_avg_pe
• unweighted_avg_pe
• median_pe
• growth_forecasts - Array of JSON values containing forecasts with a structure of:

{
    "base_year": the year based on,
    "eps_growth": forecasted EPS growth for the estimated year (%),
    "estimate_year": the year forecasted for,
    "revenue_growth": forecasted revenue growth for the estimated year (%)
}

• mcap_change_7d: 7-days IDX market cap change
• mcap_change_30d: 30-days IDX market cap change
• mcap_change_ytd: year-to-date IDX market cap change
• mcap_data_1m: Array of JSON values containing date and total_market_cap in the last 1 month

idx_company_report_calc

This MV is used for producing columns/data needed for the idx_company_report view.
Columns:

• symbol
• market_cap_rank - Company rank sorted by the biggest market cap
• employee_num_rank - Company rank sorted by the largest number of employees
• historical_dividends - Array of JSON values containing the historical dividends per year with the structure of:

{
    "year": year indicator the dividend is given on,
    "total_dividend": total dividend per share the company gave in "year",
    "total_yield": total yield of dividend aggregated by "year",
    "breakdown": {
                     "date": ex-dividend date,
                     "total": the dividend per share,
                     "yield": the dividend yield
                 }
}

• annual_yield - Array of JSON values containing the total dividend per share and yield annually. Structure:

{
    "year": year indicator the dividend is given on,
    "total": total dividend per share the company gave in "year",
    "yield": total yield of dividend aggregated by "year"
}

• yield_ttm - total yield of dividend in the trailing twelve months strict
• dividend_yield_avg - the average of total dividend yield in the last 5 years. A JSON object with the structure of:

{
    "period": the period the yield is averaged on (range from 1-5),
    "avg_yield": the average total dividend yield 
]

• payout_ratio - calculated by dividing dividend in TTM / diluted EPS in TTM
• cash_payout_ratio - calculated by dividing dividend in TTM / free cash flow per share in TTM
• last_ex_dividend_date - the last ex-dividend date the company gave its dividend
• company_growth_forecasts - Array of JSON values containing data about forecasted growth of EPS and revenue. Structure:

{
    "base_year": the year based on,
    "estimate_year": the year estimated on,
    "eps_growth": forecasted EPS growth for the estimated year (%), 
    "revenue_growth": forecasted revenue growth for the estimated year (%)
}

• company_value_forecasts - Array of JSON values containing the data about forecasted value of EPS and revenue. Structure:

{
    "estimate_year": the year estimated on,
    "eps_estimate": forecasted EPS value for the estimated year,
    "revenue_estimate": forecasted revenue value for the estimated year
}

idx_sector_reports_calc

This MV is used for producing columns/data needed and aggregated by sub-sector for the idx_sector_reports view. Columns:

• sub_sector_id
• total_companies: Number of active companies
• total_market_cap: Sum of market cap across all active companies
• avg_market_cap: Mean of market cap across all active companies
• filtered_median_pe: Median of PE across all active companies
• filtered_weighted_avg_pe: Average of PE across all active companies, weighted by their respective market cap
• min_company_pe: Minimum value of PE across all active companies
• max_company_pe: Maximum value of PE across all active companies
• avg_yoy_q_earnings_growth: Average growth of quarterly earnings (compared against the same quarter last year) across all active companies, weighted by their respective market cap
• avg_yoy_q_revenue_growth: Average growth of quarterly revenue (compared against the same quarter last year) across all active companies, weighted by their respective market cap
• weighted_avg_growth_data: JSON object containing:

{
    "year":,
    "avg_annual_earning_growth":,
    "avg_annual_revenue_growth":
}

• top_companies: Array of JSON values containing:

{
    "top_mcap":{
                    "symbol": companies in the same subsector,
                    "name": company name,
                    "market_cap": latest market cap for the company,
               },

    "top_growth":{
                    "symbol": companies in the same subsector,
                    "name": company name,
                    "revenue_growth": revenue growth (%) for the company,
                 },

    "top_profit":{
                    "symbol": companies in the same subsector,
                    "name": company name,
                    "profit_ttm": TTM profit for the company,
                 },

    "top_revenue":{
                    "symbol": companies in the same subsector,
                    "name": company name,
                    "revenue_ttm": TTM revenue for the company,
                 }
}

• weighted_max_drawdown: The average maximum drawdown (based on the daily closing price) in the last 365 days across all active companies, weighted by their respective market cap
• weighted_rsd_close: The average relative standard deviation of the daily closing price in the last 365 days across all active companies, weighted by their respective market cap
• top_change_companies: JSON object containing the top 5 companies with the highest price change in the last 30 days. Include the companies metadata such as PE, 1-year price change, 1-month price change, company name, symbol, and last closing price.
• quarterly_market_cap: JSON object containing the current ttm (the last 4 quarters) market cap of the subsector, previous ttm (the last 5th to 8th quarters) market cap of the subsector, and average of the current ttm (the last 4 quarters) market cap across all subsectors
• mcap_summary: JSON object containing the market cap change (1-week, 1-year, year-to-date), monthly market cap change in the last 12 months, and performance quantile based on the 1-year market cap change of a subsector (calculated from the sum of the market cap of all companies in the subsector)
• historical_valuation: JSON object containing the median PB, PE, PS, and PCF for the last 4 years (mrq and ttm value for the most recent data, annualized value for historical data)
• growth_forecasts - Array of JSON values containing forecasts with a structure of:

{
    "base_year": the year based on,
    "eps_growth": forecasted EPS for the estimated year (%),
    "estimate_year": the year forecasted for,
    "revenue_growth": forecasted revenue for the estimated year (%)
}
* median_yield_ttm: median of dividend yield in the trailing twelve months

idx_daily_mcap_ffill

Contain front-filled market cap data for each symbol

• date
• symbol
• market_cap

idx_mcap_change_heatmap

PLACEHOLDER

Views

idx_active_company_profile

A view for currently listed/active companies containing data from idx_company_profile.

idx_company_report

This view contains all the information grain needed for each listed company in IDX.
Columns:

Taken from idx_company_profile:

• symbol, nologo, company_name, listing_board, industry, sub_industry, address, listing_date, website, phone, email, wsj_format

Taken from idx_subsector_metadata:

• sector, sub_sector

Taken from idx_historical_mcap:

• market_cap

Taken from idx_company_report_calc:

• market_cap_rank, employee_num_rank, historical_dividends, annual_yield, yield_ttm, dividend_yield_avg, payout_ratio, cash_payout_ratio, last_ex_dividend_date, company_growth_forecasts, company_value_forecasts

Taken from idx_key_stats:

• employee_num, point_summaries, intrinsic_value
• analyst_rating - calculated by subtracting 5 from the recommendation mean multiplied by 25 ((5 - n) * 25). See recommendation mean here
• technical_rating_breakdown, analyst_rating_breakdown, technical_rating_breakdown_weekly, technical_rating_breakdown_monthly

Taken from idx_calc_metrics_daily:

• last_close_price, latest_close_date, daily_close_change, enterprise_to_revenue, enterprise_to_ebitda, price_cash_flow, peg_ratio, forward_pe

Taken from idx_calc_metrics_quarterly:

• interest_coverage_ratio, der_mrq, cash_flow_debt_ratio, roa_ttm, roe_ttm, yoy_quarter_earnings_growth, yoy_quarter_revenue_growth, net_profit_margin

Calculated Columns:

• key_executives - Array of JSON values containing the name and position of the company's directors
• executives_shareholdings - Array of JSON values containing executives shareholdings (directors & commissioners) with the structure of:

{
    "name": executive's name,
    "position": position they hold,
    "share_amount": share amount they possess,
    "share_percentage": % of shares they own in the company,
    "share_percentage_change": % of change of share calculated from "share_percentage" monthly,
}

• major_shareholders - Array of JSON values containing the major shareholders in the company with the structure of:

{
    "name": shareholder's name,
    "share_amount": share amount they possess,
    "share_percentage": % of shares they own in the company,
    "share_value": calculated by multiplying "share_amount" with "last_close_price",
    "share_percentage_change": % of change of share calculated from "share_percentage" monthly,
}

• ownership_percentage - Array of JSON values containing how many shares % Institutions, Insiders, and Others owned in the company. Structure:

{
    "Institutions": share % owned,
    "Insiders": share % owned,
    "Others": calculated by 1 - (share % owned by Institutions + share % owned by Insiders)
}

• historical_valuation - Array of JSON values containing the valuation metrics (pe, pb, ps) of a company and its peers based on year. Only data in the last 4 years are shown.
• historical_financials - Array of JSON values containing a company's financial annual statement metrics based on year.
• self_financial_info - Array of JSON values containing the latest financial quarter statement metrics of a company.
• monthly_net_transactions - Array of JSON values containing the monthly net institutional transactions (top 20 buys and top 20 sells)
• top_transactions - JSON object containing the latest top instituional transactions. E.g.
• intrinsic_value - Intrinsic value of a company calculated with DCF method.

{
  "date": "2023-12-31",
  "top_buyers": [
    {
      "name": "Renaissance Capital LLC",
      "changeAmount": 937900
    },
   ...
  ],
  "top_sellers": ...
}

idx_company_growth_forecast

Columns:

• symbol
• estimate_year - the year of eps_esimate and revenue_estimate
• eps_estimate
• revenue_estimate
• base_year - the reference year for which eps_estimate and revenue_estimate is compared to for the growth calculation
• eps_growth
• revenue_growth

idx_delisted_12m

Used to obtain companies' data that has been delisted in the last 12 months.

idx_new_company

Used to obtain companies' data that are newly listed in the last 6 months.

idx_sector_reports

This view contains aggregated companies' information grouped by their respective subsector and directly used for frontend purposes.
Columns:

Taken from idx_subsector_metadata:

• sector, sub_sector, slug, description

Taken from idx_sector_reports_calc:

• total_companies, total_market_cap, avg_market_cap, filtered_median_pe, filtered_weighted_avg_pe, min_company_pe, max_company_pe, avg_yoy_q_earnings_growth, avg_yoy_q_revenue_growth, weighted_avg_growth_data, top_companies, weighted_max_drawdown, weighted_rsd_close, top_change_companies, quarterly_market_cap, mcap_summary, historical_valuation, growth_forecasts

Notes: Negative PE values are possible because we don't filter companies with negative earnings.

idx_sub_industry_market_cap

• sector
• sub_sector
• industry
• sub_industry
• total_market_cap - the market cap of all companies in the subindustry
• num_of_companies - the number of all companies in the subindustry

idx_top25_mcap

Used to obtain the top 25 companies' data (symbol, company_name, market_cap) with the largest market cap in IDX.

Functions used in scripts/in database

• get_last_daily_data(): Retrieve data based on the latest date of each symbol
  Used in: sectors_yf_data_updater - yfdataupdater.py
• get_last_date(table_name): Retrieve the latest date of each symbol
  Args: table_name - The table name of which the data is to be retrieved from.
  Used in: sectors_yf_data_updater - yfdataupdater.py | sectors_wsj_data_updater: scrape_financial_data.py
• get_peers_{metric}: Get the minimum, maximum, q1, median and q3 of {metric} across all active companies in the input subsector. Example arg for p_subsector_slug: 'basic-materials'. Available metric: pe, pb, ps.
  Used in: sectors_dcf_calculation - dcf_v2.py Also used in the frontend.
• calculate_sub_sector_quarterly_market_cap(): Generate data for quarterly_market_cap column in idx_sector_reports_calc
• calculate_sub_sector_mcap_summary(): Generate data for mcap_summary column in idx_sector_reports_calc
• get_subsector_historical_valuation(): Generate data for historical_valuation column in idx_sector_reports_calc
• get_top_mcap_by_subsector(): Retrieve the top 5 companies (symbol, company name and market cap) with the highest market cap in each subsector.
  Used in: sectors_generate_sub_sector_desc - main.py
• get_outdated_symbols(table_name, source): Retrieve outdated symbols from 'table_name' (idx_financials_quarterly/idx_financials_annual) if the latest financial quarter/annual date for that symbol is behind the current quarter/annual date.
  Used in: sectors_yf_data_updater - yfdataupdater.py | sectors_wsj_data_updater: wsj_updater.py
• get_latest_financial_quarter_date(): Returns the latest financial quarter date from idx_financials_quarterly.
  Used in: sectors_wsj_data_updater - source_format_checker.py
• round_half_down(x) - round a real number to the nearest integer, with the middle value (0.5) rounded down.
• round_idx_stock_price(price) - round a price in IDR based on the IDX stock price fraction (last referenced July 2024). Rounding uses round_half_down(x) for its middle value after discussion with IDX employee.
• update_data_after_split(symbol, current_date, split_ratio): update historical data in several tables (idx_daily_data, idx_dividend, idx_key_stats, idx_financials_annual, and idx_financials_quarterly) by adjusting affected columns after a stock split. The adjustments are made for rows where the symbol matches the provided input symbol and the date is earlier than the given stock split date. The stock price adjustment is also calculated with IDX rounding using the round_idx_stock_price(price) function.

Triggers

Structure: Trigger name | function to run | table name

• drop_delisted_historical_mcap_trigger | drop_delisted_historical_mcap() | idx_historical_mcap - Delete data for delisted companies.
• delete_old_daily_data_trigger | delete_old_daily_data() | idx_daily_data - Delete data older than 5 years from today's date.
• delete_old_dividend_data_trigger | delete_old_dividend_data() | idx_dividend - Delete data older than 5 years from table max(date).
• delete_old_quarter_data_trigger | delete_old_quarter_data() | idx_financials_quarterly - Delete data older than 20 months from table max(date).
• delete_old_annual_data_trigger | delete_old_annual_data() | idx_financials_annual - Delete data older than 6 years from table max(date).
• delete_old_historical_mcap_trigger | delete_old_historical_mcap() | idx_historical_mcap - Delete data older than 5 years from table max(date).
• refresh_icma_mv_on_upsert | refresh_icma_mv() | idx_calc_metrics_annual - Refresh the MV upon upsert on idx_financials_annual.
• drop_delisted_historical_mcap_trigger | drop_delisted_historical_mcap() | Delete rows from the idx_historical_mcap table if the date is greater than the delisting date.

Refer to Data Management for retention policies.

Database Cron

Refresh MV Jobs

All below runs at 10:00 PM UTC/ 5:00 AM GMT+7 daily

• refresh_idx_sector_reports_calc - refreshes idx_sector_reports_calc
• refresh_idx_aggregated_calc - refreshes idx_aggregated_calc
• refresh_idx_company_report_calc - refreshes idx_company_report_calc
• refresh_idx_calc_metrics_daily - refreshes idx_calc_metrics_daily
• refresh_idx_calc_metrics_quarterly - refreshes idx_calc_metrics_quarterly
• refresh_idx_calc_metrics_annual - refreshes idx_calc_metrics_annual
• refresh_idx_daily_mcap_ffill - refreshes idx_daily_mcap_ffill
• refresh_idx_mcap_change_heatmap - refreshes idx_mcap_change_heatmap

Update Jobs

• update_yield_job - Execute update_yield_for_prev_year() which updates yield in idx_dividend for the previous year. Scheduled on 2nd January every year.
• upsert_last_month_mcap_job: Execute upsert_last_month_mcap() which performs an upsert operation on the idx_historical_mcap table based on the last market cap data of the previous month obtained from the idx_daily_data table and scheduled on the 2nd day of every month.
• check_and_execute_split_job: Execute check_and_execute_split() which checks if there's any stock split happening on the current day and executes update_data_after_split(symbol, current_date, split_ratio) if necessary. update_data_after_split(symbol, current_date, split_ratio) update historical data in several tables (idx_daily_data, idx_dividend, idx_financials_annual, idx_financials_quarterly, and idx_key_stats) by adjusting affected columns after a stock split. The adjustments are made for rows where the symbol matches the provided input symbol and the date is earlier than the given stock split date. Adjusted rows also have their updated_on set to the split date (at 7:00 GMT/0:00 AM GMT+7). Scheduled at 6:00 PM UTC/ 1:00 AM GMT+7 daily.
• filter_daily_data_job: Execute filter_daily_data() which deletes rows from the idx_daily_data table where the count of records for a specific date is less than 10. Scheduled at 1:00 PM UTC/ 8:00 PM GMT+7 daily.

Definitions:

Peers:

Defined as companies that are in the same subsector unless stated otherwise.

Convertible Securities:

Includes stock options, warrants, preferred stocks/shares, bonds, and other common types of dilutives