Market analysis requires structured data to understand current trading activity, global market conditions, and the stocks receiving the highest transaction activity. Raw API responses, however, are often returned in nested JSON structures that are difficult to analyze directly. A systematic data processing workflow is therefore required to transform these responses into usable analytical datasets.
This project develops an IDX Market Intelligence Analysis System using the Indonesia Stock Exchange RapidAPI. The system retrieves three main datasets: today's market calendar, global market overview, and top stock transaction data. The retrieved information is normalized into Pandas DataFrames and processed using automatic column detection and numeric conversion functions.
The analysis focuses on identifying stocks with the highest transaction values and evaluating global market movements based on percentage changes. The system also generates visualizations for the top stocks and global market movers. Finally, the processed results are summarized and exported into an Excel workbook containing separate sheets for the summary, calendar, global market, and top-stock analysis.
The project uses Python, Requests, Pandas, Matplotlib, and Path for API communication, data processing, visualization, and file management. The API configuration uses an environment variable or secure password input for the RapidAPI key.
Cell 1 — Setup Library API Key and Analysis Parameters
The first cell prepares the Python environment and defines the analysis period. The system uses January 5, 2026 as both the start and end date. It also defines the parameters required for the top-stock endpoint, including investor type, market type, and value type.
# CELL 1 — SETUP LIBRARY, API KEY, DAN PARAMETER ANALISIS
import os
import json
import getpass
import requests
import pandas as pd
import matplotlib.pyplot as plt
from pathlib import Path
pd.set_option("display.max_columns", 100)
pd.set_option("display.width", 180)
BASE_URL = "https://indonesia-stock-exchange-idx.p.rapidapi.com"
RAPIDAPI_HOST = "indonesia-stock-exchange-idx.p.rapidapi.com"
RAPIDAPI_KEY = os.getenv("RAPIDAPI_KEY")
if not RAPIDAPI_KEY:
RAPIDAPI_KEY = getpass.getpass("Masukkan RAPIDAPI_KEY: ")
START_DATE = "2026-01-05"
END_DATE = "2026-01-05"
TOP_STOCK_PARAMS = {
"page": 1,
"investorType": "INVESTOR_TYPE_ALL",
"start": START_DATE,
"end": END_DATE,
"marketType": "MARKET_TYPE_ALL",
"valueType": "VALUE_TYPE_TOTAL",
}
print("Setup selesai.")
Cell 2 — Market Data Retrieval from IDX RapidAPI
The second cell establishes a reusable API session and introduces rate-limit handling. The api_get() function handles successful responses, HTTP 429 rate limits, retry delays, invalid JSON responses, and other request errors. Exponential backoff is used when the API temporarily limits requests.
Three datasets are then requested: the current calendar, global market overview, and top-stock transaction data. A three-second delay is inserted between selected endpoint requests to reduce request pressure.
# CELL 2 — PENGAMBILAN DATA API DENGAN RATE-LIMIT HANDLING
import time
session = requests.Session()
session.headers.update({
"Content-Type": "application/json",
"x-rapidapi-host": RAPIDAPI_HOST,
"x-rapidapi-key": RAPIDAPI_KEY,
})
def api_get(
endpoint,
params=None,
max_retries=4,
base_delay=5
):
"""
Mengambil data API dengan penanganan:
- HTTP 429 Too Many Requests
- retry otomatis
- Retry-After header
- exponential backoff
"""
url = f"{BASE_URL}{endpoint}"
for attempt in range(max_retries):
try:
response = session.get(
url,
params=params,
timeout=30
)
print(
f"Request: {endpoint} | "
f"Status: {response.status_code}"
)
# =========================
# SUCCESS
# =========================
if response.status_code == 200:
try:
return response.json()
except ValueError:
print(
f"Response {endpoint} "
"bukan JSON valid."
)
return None
# =========================
# RATE LIMIT
# =========================
elif response.status_code == 429:
retry_after = (
response.headers
.get("Retry-After")
)
if retry_after:
try:
wait_time = float(
retry_after
)
except ValueError:
wait_time = (
base_delay
* (2 ** attempt)
)
else:
wait_time = (
base_delay
* (2 ** attempt)
)
print(
f"Rate limit terkena. "
f"Menunggu {wait_time:.0f} detik..."
)
time.sleep(wait_time)
# =========================
# OTHER HTTP ERROR
# =========================
else:
print(
f"HTTP Error "
f"{response.status_code}"
)
print(
response.text[:500]
)
return None
except requests.exceptions.RequestException as e:
print(
f"Request gagal: {e}"
)
wait_time = (
base_delay
* (2 ** attempt)
)
time.sleep(wait_time)
print(
f"Gagal mengambil {endpoint} "
f"setelah {max_retries} percobaan."
)
return None
# ==========================================
# 1. CALENDAR TODAY
# ==========================================
calendar_raw = api_get(
"/api/calendar/today"
)
# Delay antar endpoint
time.sleep(3)
# ==========================================
# 2. GLOBAL MARKET OVERVIEW
# ==========================================
global_raw = api_get(
"/api/global/market-overview"
)
time.sleep(3)
# ==========================================
# 3. TOP STOCK
# ==========================================
top_stock_raw = api_get(
"/api/market-detector/top-stock",
TOP_STOCK_PARAMS
)
# ==========================================
# STATUS DATA
# ==========================================
print("\n=== API STATUS ===")
print(
"Calendar :",
"OK" if calendar_raw is not None
else "FAILED"
)
print(
"Global Market :",
"OK" if global_raw is not None
else "FAILED"
)
print(
"Top Stock :",
"OK" if top_stock_raw is not None
else "FAILED"
)
Cell 3 — JSON Normalization and DataFrame Transformation
The third cell transforms nested JSON responses into structured DataFrames. The find_tables() function recursively searches through dictionaries and lists to identify table-like structures. The json_to_df() function then selects the most suitable table based on its dimensions.
This approach makes the system more flexible because the exact location of tabular data inside the API response does not have to be manually specified. The resulting DataFrames are generated for the calendar, global market, and top-stock datasets.
# CELL 3 — NORMALISASI DAN TRANSFORMASI JSON KE DATAFRAME
# =========================================================
# FUNCTION 1: MENCARI TABEL DI DALAM STRUKTUR JSON
# =========================================================
def find_tables(data, path="root"):
tables = []
if isinstance(data, list):
if data and all(
isinstance(x, dict)
for x in data
):
try:
df = pd.json_normalize(
data,
sep="."
)
tables.append(
(path, df)
)
except Exception:
pass
for i, item in enumerate(data):
tables.extend(
find_tables(
item,
f"{path}[{i}]"
)
)
elif isinstance(data, dict):
for key, value in data.items():
tables.extend(
find_tables(
value,
f"{path}.{key}"
)
)
return tables
# =========================================================
# FUNCTION 2: KONVERSI JSON MENJADI DATAFRAME
# =========================================================
def json_to_df(data, name):
if data is None:
print(
f"{name}: data tidak tersedia."
)
return pd.DataFrame()
candidates = find_tables(data)
if candidates:
path, df = max(
candidates,
key=lambda x:
max(len(x[1]), 1)
*
max(len(x[1].columns), 1)
)
print(
f"{name}: "
f"{path} | "
f"{len(df)} rows x "
f"{len(df.columns)} columns"
)
return df.copy()
if isinstance(data, dict):
df = pd.json_normalize(
data,
sep="."
)
print(
f"{name}: "
f"{len(df)} rows x "
f"{len(df.columns)} columns"
)
return df
if isinstance(data, list):
return pd.DataFrame(data)
return pd.DataFrame({
"value": [data]
})
# =========================================================
# KONVERSI DATA API
# =========================================================
calendar_df = json_to_df(
calendar_raw,
"Calendar"
)
global_df = json_to_df(
global_raw,
"Global Market"
)
top_stock_df = json_to_df(
top_stock_raw,
"Top Stock"
)
# =========================================================
# CEK HASIL DATAFRAME
# =========================================================
print("\n=== DATAFRAME STATUS ===")
print(
f"Calendar : "
f"{calendar_df.shape}"
)
print(
f"Global Market : "
f"{global_df.shape}"
)
print(
f"Top Stock : "
f"{top_stock_df.shape}"
)
# =========================================================
# CEK NAMA KOLOM
# =========================================================
if not calendar_df.empty:
print(
"\n=== CALENDAR COLUMNS ==="
)
print(
calendar_df.columns.tolist()
)
if not global_df.empty:
print(
"\n=== GLOBAL MARKET COLUMNS ==="
)
print(
global_df.columns.tolist()
)
if not top_stock_df.empty:
print(
"\n=== TOP STOCK COLUMNS ==="
)
print(
top_stock_df.columns.tolist()
)
# =========================================================
# PREVIEW DATA
# =========================================================
if not calendar_df.empty:
print(
"\n=== CALENDAR PREVIEW ==="
)
display(
calendar_df.head(10)
)
if not global_df.empty:
print(
"\n=== GLOBAL MARKET PREVIEW ==="
)
display(
global_df.head(10)
)
if not top_stock_df.empty:
print(
"\n=== TOP STOCK PREVIEW ==="
)
display(
top_stock_df.head(10)
)
Cell 4 — Top Stock Global Market Analysis and Visualization
The fourth cell performs the main analytical transformation. It includes a numeric conversion function capable of handling percentage symbols, commas, decimal formats, and other non-numeric characters.
The find_column() function automatically identifies relevant columns based on keyword matching. This is applied to detect stock symbols, transaction values, global market names, and percentage changes.
The top-stock dataset is sorted by transaction value, while the global market dataset is sorted by percentage change. The system then generates two visualizations: the top 15 stocks based on transaction value and the global market movers.
# CELL 4 — ANALISIS TOP STOCK, GLOBAL MARKET, DAN VISUALISASI
# =========================================================
# FUNCTION: KONVERSI DATA MENJADI NUMERIC
# =========================================================
def to_numeric(series):
if pd.api.types.is_numeric_dtype(series):
return pd.to_numeric(
series,
errors="coerce"
)
s = (
series
.astype(str)
.str.strip()
)
s = (
s.str.replace(
"%",
"",
regex=False
)
.str.replace(
r"[^\d,.\-+]",
"",
regex=True
)
)
both = (
s.str.contains(",", regex=False)
&
s.str.contains(".", regex=False)
)
s.loc[both] = (
s.loc[both]
.str.replace(
",",
"",
regex=False
)
)
comma_only = (
s.str.contains(",", regex=False)
&
~s.str.contains(".", regex=False)
)
decimal_comma = (
comma_only
&
s.str.match(
r"^[+-]?\d+,\d{1,4}$",
na=False
)
)
s.loc[decimal_comma] = (
s.loc[decimal_comma]
.str.replace(
",",
".",
regex=False
)
)
s.loc[
comma_only & ~decimal_comma
] = (
s.loc[
comma_only & ~decimal_comma
]
.str.replace(
",",
"",
regex=False
)
)
return pd.to_numeric(
s,
errors="coerce"
)
# =========================================================
# FUNCTION: MENCARI KOLOM SECARA OTOMATIS
# =========================================================
def find_column(
df,
keywords,
numeric=False
):
if df.empty:
return None
results = []
for column in df.columns:
name = str(column).lower()
score = 0
for keyword in keywords:
keyword = keyword.lower()
if name == keyword:
score += 3
elif name.endswith(keyword):
score += 2
elif keyword in name:
score += 1
if score > 0:
if numeric:
converted = to_numeric(
df[column]
)
score += (
converted
.notna()
.mean()
* 2
)
results.append(
(score, column)
)
if not results:
return None
return max(
results,
key=lambda x: x[0]
)[1]
# =========================================================
# DETEKSI KOLOM TOP STOCK
# =========================================================
symbol_col = find_column(
top_stock_df,
[
"symbol",
"ticker",
"stockcode",
"code",
"stock",
"kode"
]
)
value_col = find_column(
top_stock_df,
[
"totalvalue",
"transactionvalue",
"value",
"amount",
"turnover"
],
numeric=True
)
# =========================================================
# DETEKSI KOLOM GLOBAL MARKET
# =========================================================
global_name_col = find_column(
global_df,
[
"indexname",
"symbol",
"ticker",
"name",
"market",
"index"
]
)
global_change_col = find_column(
global_df,
[
"percentchange",
"changepercent",
"changepct",
"percentage",
"percent",
"change"
],
numeric=True
)
# =========================================================
# MEMBUAT DATA ANALISIS
# =========================================================
top_analysis = top_stock_df.copy()
global_analysis = global_df.copy()
if (
not top_analysis.empty
and value_col is not None
):
top_analysis[
"analysis_value"
] = to_numeric(
top_analysis[value_col]
)
top_analysis = (
top_analysis
.sort_values(
"analysis_value",
ascending=False
)
.reset_index(drop=True)
)
if (
not global_analysis.empty
and global_change_col is not None
):
global_analysis[
"analysis_change"
] = to_numeric(
global_analysis[
global_change_col
]
)
global_analysis = (
global_analysis
.sort_values(
"analysis_change",
ascending=False
)
.reset_index(drop=True)
)
# =========================================================
# TAMPILKAN HASIL ANALISIS
# =========================================================
print("=== DETECTED COLUMNS ===")
print(
"Top Stock Symbol :",
symbol_col
)
print(
"Top Stock Value :",
value_col
)
print(
"Global Name :",
global_name_col
)
print(
"Global Change :",
global_change_col
)
if not top_analysis.empty:
print(
"\n=== TOP STOCK ANALYSIS ==="
)
display(
top_analysis.head(20)
)
if not global_analysis.empty:
print(
"\n=== GLOBAL MARKET ANALYSIS ==="
)
display(
global_analysis.head(20)
)
if not calendar_df.empty:
print(
"\n=== CALENDAR TODAY ==="
)
display(
calendar_df.head(30)
)
# =========================================================
# VISUALISASI TOP STOCK
# =========================================================
if (
not top_analysis.empty
and symbol_col is not None
and value_col is not None
and "analysis_value"
in top_analysis.columns
):
chart_top = (
top_analysis[
[
symbol_col,
"analysis_value"
]
]
.dropna()
.head(15)
)
if not chart_top.empty:
plt.figure(
figsize=(10, 6)
)
plt.barh(
chart_top[
symbol_col
].astype(str)[::-1],
chart_top[
"analysis_value"
][::-1]
)
plt.title(
"Top 15 Stock Berdasarkan Nilai Transaksi"
)
plt.xlabel(
value_col
)
plt.ylabel(
symbol_col
)
plt.tight_layout()
plt.show()
# =========================================================
# VISUALISASI GLOBAL MARKET
# =========================================================
if (
not global_analysis.empty
and global_name_col is not None
and global_change_col is not None
and "analysis_change"
in global_analysis.columns
):
chart_global = (
global_analysis[
[
global_name_col,
"analysis_change"
]
]
.dropna()
.head(15)
)
if not chart_global.empty:
plt.figure(
figsize=(10, 6)
)
plt.barh(
chart_global[
global_name_col
].astype(str)[::-1],
chart_global[
"analysis_change"
][::-1]
)
plt.title(
"Global Market Movers"
)
plt.xlabel(
global_change_col
)
plt.ylabel(
global_name_col
)
plt.tight_layout()
plt.show()
Cell 5 — Market Summary and Excel Export
The fifth cell creates the final analytical summary. The summary records the number of observations in each dataset and calculates total, average, median, and maximum transaction values from the top-stock dataset.
For the global market dataset, the system counts positive, negative, and flat markets and calculates the average and median percentage change.
The final results are exported into IDX_market_analysis.xlsx with four sheets: Summary, Calendar_Today, Global_Market, and Top_Stock.
# CELL 5 — RINGKASAN ANALISIS DAN EXPORT KE EXCEL
# =========================================================
# MEMBUAT SUMMARY
# =========================================================
summary = []
summary.append({
"Metric":
"Calendar Records",
"Value":
len(calendar_df)
})
summary.append({
"Metric":
"Global Market Records",
"Value":
len(global_df)
})
summary.append({
"Metric":
"Top Stock Records",
"Value":
len(top_stock_df)
})
# =========================================================
# SUMMARY TOP STOCK
# =========================================================
if (
not top_analysis.empty
and "analysis_value"
in top_analysis.columns
):
values = (
top_analysis[
"analysis_value"
]
.dropna()
)
if not values.empty:
summary.extend([
{
"Metric":
"Total Transaction Value",
"Value":
values.sum()
},
{
"Metric":
"Average Transaction Value",
"Value":
values.mean()
},
{
"Metric":
"Median Transaction Value",
"Value":
values.median()
},
{
"Metric":
"Maximum Transaction Value",
"Value":
values.max()
}
])
# =========================================================
# SUMMARY GLOBAL MARKET
# =========================================================
if (
not global_analysis.empty
and "analysis_change"
in global_analysis.columns
):
changes = (
global_analysis[
"analysis_change"
]
.dropna()
)
if not changes.empty:
summary.extend([
{
"Metric":
"Positive Global Markets",
"Value":
int(
(changes > 0)
.sum()
)
},
{
"Metric":
"Negative Global Markets",
"Value":
int(
(changes < 0)
.sum()
)
},
{
"Metric":
"Flat Global Markets",
"Value":
int(
(changes == 0)
.sum()
)
},
{
"Metric":
"Average Global Change",
"Value":
changes.mean()
},
{
"Metric":
"Median Global Change",
"Value":
changes.median()
}
])
summary_df = pd.DataFrame(
summary
)
# =========================================================
# TAMPILKAN SUMMARY
# =========================================================
print(
"=== MARKET ANALYSIS SUMMARY ==="
)
display(
summary_df
)
# =========================================================
# TAMPILKAN TOP 10 STOCK
# =========================================================
if (
not top_analysis.empty
):
print(
"\n=== TOP 10 STOCK ==="
)
display(
top_analysis.head(10)
)
# =========================================================
# EXPORT KE EXCEL
# =========================================================
output_file = (
"IDX_market_analysis.xlsx"
)
with pd.ExcelWriter(
output_file,
engine="openpyxl"
) as writer:
summary_df.to_excel(
writer,
sheet_name="Summary",
index=False
)
calendar_df.to_excel(
writer,
sheet_name="Calendar_Today",
index=False
)
global_analysis.to_excel(
writer,
sheet_name="Global_Market",
index=False
)
top_analysis.to_excel(
writer,
sheet_name="Top_Stock",
index=False
)
# =========================================================
# FINAL STATUS
# =========================================================
print(
"\n=== EXPORT SUCCESS ==="
)
print(
f"File berhasil dibuat: "
f"{output_file}"
)
Conclusion
This project develops an IDX Market Intelligence Analysis System that retrieves and processes market information from IDX RapidAPI. The system combines today's market calendar, global market overview, and top-stock transaction data into a structured analytical workflow.
The JSON normalization framework allows nested API responses to be converted into DataFrames without depending entirely on fixed response structures. Automatic column detection further improves flexibility by identifying relevant stock, transaction value, market name, and percentage-change columns based on predefined keywords.
The analysis then ranks stocks according to transaction value and evaluates global market movements according to percentage change. Visualization provides a graphical representation of the top 15 stocks and global market movers, while the final summary calculates transaction statistics and global market distribution.
The resulting Excel workbook provides a practical reporting structure containing the overall summary, current calendar data, global market analysis, and top-stock analysis. This makes the system suitable as a foundation for systematic market monitoring and further financial analytics.
The analysis should be interpreted as a market-data processing and monitoring framework, rather than as a standalone investment recommendation. Further analysis can incorporate valuation, financial performance, technical indicators, broker flow, foreign flow, and other market variables to build a more comprehensive investment intelligence system.
Overall, the project demonstrates how IDX RapidAPI, Python, Pandas, automated data transformation, visualization, and Excel reporting can be integrated into a reproducible workflow for Indonesian stock market intelligence.