VIETNAM NATIONAL UNIVERSITY, HANOI INTERNATIONAL SCHOOL GRADUATION PROJECT PROJECT NAME DATA WAREHOUSE AND STOCK ANALYSIS FOR BANK IN VIETNAM Student’s name DAO XUAN HUONG Hanoi – Year 2025 VIETNAM NATIONAL UNIVERSITY, HANOI INTERNATIONAL SCHOOL GRADUATION PROJECT PROJECT NAME DATA WAREHOUSE AND STOCK ANALYSIS FOR BANK IN VIETNAM SUPERVISOR: Doctor. NGUYEN QUANG THUAN STUDENT: DAO XUAN HUONG STUDENT ID: 21070008 COHORT: Management Information Systems SUBJECT CODE: INS401101 MAJOR: Graduation Thesis Hanoi - Year 2025 2 TABLE OF CONTENTS TABLE OF FIGURES. 5 LIST OF TABLES. The Importance of the Research Topic.
Practical Urgency of the Topic. Scientific and Practical Significance. How to build a Data Warehouse (DW) architecture suitable for the banking and securities sectors?. How to integrate data from multiple sources in the financial sector?.
Object and Scope of the Study. Data Collection and Processing. 16 CHAPTER 2: LITERATURE REVIEW. Definition of Data Warehouse.
Key Concepts Related to Data Warehousing. Importance of Data Warehouses in Financial Sectors. Previous Research on Data Warehouse Implementation. Survey of Past and Current Research on Data Warehousing in Vietnam.
Solutions and Best Practices in Financial Data Warehousing. Gaps in Existing Literature. Research Analysis Methods. Data Collection and Integration (Ingestion).
Data Modeling and Cube Building (Build Cube). Data Query and Analysis. System Performance Evaluation. Data Collection Methods.
Analysis and Discussion. Designing Apache Airflow Workflows. Developing OLAP Cubes. Causes and Impacts.
60 CONCLUSION AND RECOMMENDATIONS. Limitations of the Study. Proposals and Recommendations. 67 4 TABLE OF FIGURES Figure 1: Data Warehouse pipeline.
Error! Bookmark not defined. Figure 2: Dimensional Diagram. 36 Figure 3: Star Schema for fact stock performance. 37 Figure 4: Star schema for fact daily market.
38 Figure 5: Star schema for fact investment. 39 Figure 6: Star schema for fact exchange performance. 40 Figure 7: Dag for creating table. 42 Figure 8: Dag for crawling data.
42 Figure 9: Dag for merging data. 43 Figure 10: Query for Daily investment profit by bank cube. 47 Figure 11: Data for Daily investment profit by bank cube. 49 Figure 12: Query for Daily exchange transaction summary cube.
49 Figure 13: Data for Daily exchange transaction summary cube. 52 Figure 14: Query for Financial indicators cube. 53 Figure 15: Data for Financial indicators cube. 58 5 LIST OF TABLES Table 1: Stock data.
32 Table 3: Exchange performance. 33 Table 4: Bank name. 33 Table 5: Exchange name. 34 Table 6: Market summary.
34 6 ACKNOWLEDGEMENT First and foremost, I would like to express my deepest gratitude to my academic advisor, Nguyen Quang Thuan, for their invaluable guidance, encouragement, and insightful feedback throughout the development of this research. Their expertise and support have been crucial in helping me navigate the complexities of this project. Special thanks go to my friends and colleagues, who provided moral support and helpful discussions, and to my family, whose unwavering belief in me was a constant source of motivation during this process. Finally, I would like to acknowledge the stock market professionals and experts who shared their insights and helped validate the practical aspects of this study.
Without their input, this research would not have been as comprehensive or impactful. 7 ABSTRACT The rapid growth of the stock market and banking sector in Vietnam has created an urgent need for a centralized system to manage and analyze stock data efficiently. This study focuses on designing and implementing a data warehouse tailored to Vietnamese banking stock indices. By integrating data from stock exchanges, financial reports, and other indices, the system provides a holistic view of market trends, enabling better decision-making for investors, financial analysts, and banking organizations.
The data warehouse is built with features for real-time data retrieval, visualization, and trend analysis. Advanced tools such as line charts, bar graphs, and data tables offer users a clear picture of stock performance over time. The system is scalable, making it adaptable to the inclusion of new indices and financial metrics. Despite its achievements, the project faced challenges in optimizing data processing for large-scale datasets and in incorporating predictive analytics.
Future enhancements aim to address these limitations by employing advanced technologies such as machine learning and big data analytics. This research contributes significantly to the field of financial data management, providing a foundation for future developments in stock market analytics and decision support systems. 8 CHAPTER 1: INTRODUCTION In this chapter, the background and context of the research project will be introduced, providing an overview of the problem being addressed. The chapter will discuss the significance of data warehousing in the stock market sector, particularly in the context of analyzing stock performance and financial data.
It will outline the purpose, objectives, and scope of the study, explaining why it is relevant to the current industry trends. The chapter will also introduce the research questions and the potential contributions of the study. The Importance of the Research Topic In the era of digital transformation, the banking and securities sectors in Vietnam are witnessing rapid growth, with millions of financial transactions conducted daily. The enormous volume of data generated from these transactions requires not only vast storage capacity but also effective management and utilization.
However, financial institutions and securities companies in Vietnam face significant challenges: • Fragmented data: Data is stored across various systems such as transaction systems, CRM, and accounting, leading to difficulties in integration and information retrieval. • Complex data analysis: Traditional tools are insufficient for analyzing large and diverse datasets, reducing the efficiency of strategic decision-making. • Fierce competition: Financial institutions need to optimize their data utilization to enhance operational efficiency, reduce costs, and improve service quality to stay competitive. Therefore, developing a Data Warehouse that is integrated and modern has become an urgent requirement.
It can address these challenges while leveraging data as a strategic asset. Practical Urgency of the Topic 9 Many banks and securities companies in Vietnam are still utilizing disparate storage and processing systems that lack integration and scalability. This results in: Low efficiency in reporting and analysis: Reporting processes are often time- consuming, inaccurate, and untimely. Difficulties in forecasting and decision-making: The absence of centralized data and effective analysis reduces the ability to forecast market trends and make strategic decisions.
Furthermore, the development of modern technologies such as Big Data, AI, and Machine Learning presents significant opportunities for extracting value from data. A Data Warehouse is not just a storage solution but also a platform for organizations to leverage these technologies, optimizing operations and enhancing competitiveness. Scientific and Practical Significance Scientific Significance: • This study contributes to developing technical solutions for building Data Warehouses, particularly in the financial and banking sectors, where precision, security, and performance are critical. • It provides a reference model and methodology for building Data Warehouses in the financial sector, from architectural design to practical implementation.
Practical Significance: • Helps banks and securities companies in Vietnam integrate data from multiple sources, creating a centralized and reliable data platform. • Supports managers and leaders in making quick and accurate decisions based on comprehensive data analysis and reports. • Enhance operational efficiency by reducing data processing time and improving the accuracy of information, thereby improving customer service quality. Personal Motivation With a strong interest in the field of data and technology, particularly in the financial and banking sectors, I recognize that building a Data Warehouse is not only a fascinating topic but also one that brings immense value to businesses and society.
10 This research is an opportunity for me to apply the knowledge I have learned to practical scenarios while exploring and learning more about modern technologies in data management and utilization. Research Objectives General Objective: The overarching objective of this study is to build a Data Warehouse system that integrates data from various sources, providing a comprehensive platform for data storage, management, and analysis. This system not only supports banks and securities organizations in Vietnam to organize data effectively but also optimizes data analysis and decision-making processes, meeting practical requirements for accuracy, security, and scalability. Specific Objectives: Design a Data Warehouse architecture suitable for the characteristics of financial and banking data in Vietnam: • Analyze the business requirements and data characteristics of banks and securities organizations, including transaction data, customer information, assets, and financial reports.
• Define the architecture of the Data Warehouse system, including: o Star Schema model to optimize data query performance. o Data layers such as staging layer, integration layer, and presentation layer. o Propose appropriate technologies (e., PostgreSQL) • Ensure the system is scalable to handle large volumes of data in the future. • Develop a security mechanism to ensure data safety and integrity, in compliance with financial sector regulations in Vietnam.
Integrate and process data from existing securities and banking systems: • Survey and assess input data sources: o Securities transaction data from stock exchanges (HSX, HNX). 11 o Financial data from banking systems, such as credit management systems, customer account management systems, and interbank transaction systems. o Unstructured data such as emails, customer notes, or reports from customer service departments. Building the ETL (Extract, Transform, Load) Process Extract Objective: Collect data from various sources, including: • Relational Databases (RDBMS): PostgreSQL, etc.
• APIs: Systems for securities and banking transactions that provide data via APIs. • File Data: CSV, Excel files, or log file formats exported from systems. Implementation: • Use data extraction library like Vnstock3, or custom scripts to extract data from sources. • Extract raw data without applying any processing to preserve the original state of the data.
• Store this raw data in a staging area, typically in Relational Database system (PostgreSQL) or internal file systems (excel, csv). Transform Objective: Perform data cleaning, transformation, and integration directly within the Data Warehouse, leveraging the performance of modern data processing tools. Implementation: • Remove Duplicates: o Identify and eliminate duplicate records in the data. o Use SQL queries or tools like dbt to handle this processing.
• Standardize Data Formats: o Convert date and time formats into a unified standard. 12 o Encode data according to industry standards (e., financial sector codes, ISO standards). • Handle Logical Errors or Missing Data: o Apply rules to correct errors or fill in missing values based on the data context. o For instance, assign default values to null fields or eliminate invalid records.
• Integrate Data: o Perform joins between multiple data sources to create analysis tables. o Design and build dimensional analysis tables (e., Star Schema or Snowflake Schema). Loading Objective: Transfer raw data from the staging area to the Data Warehouse without performing any transformations. Implementation: • Use loading tools such as Apache Airflow • Load the data into raw tables (often referred to as staging tables) in the Data Warehouse.
• Ensure data loading is performed sequentially or in batches to reduce the load on source systems. Develop reports and dashboards for data analysis and decision-making support: • Develop a reporting system: Design detailed reports to support business management, such as: o Daily transaction reports: transaction volume, transaction value, and success rate. o Summary reports to help leaders capture an overall view of business activities. • Build interactive dashboards: Use data visualization tool (Power BI) to create interactive dashboards that monitor: 13 o Real-time business performance.
o Securities trading trends and market conditions. o Key performance indicators (KPIs) such as liquidity ratios, risk levels, and investment efficiency. • Support forecasting and decision-making: o Use data from the Data Warehouse to develop forecasting models based on Machine Learning, helping to predict market trends and customer behavior. o Propose business solutions based on data analysis, such as optimizing investment portfolios, adjusting credit strategies, or allocating resources efficiently.
How to build a Data Warehouse (DW) architecture suitable for the banking and securities sectors? • Which architecture models (e., Star Schema, Snowflake Schema) are suitable for integrating diverse financial data?