Trading Systems: Real Time Dashboard Using Microsoft Excel and VBA

  • กำธน ถาวรอยู่
  • สมพร ปั่นโภชา
  • บำรุง พ่วงเกิด
Keywords: SET50, Cash Flow, RSI, STO, Bootstrapping, Long Term Investment, Never Rebalancing, VBA


The study report on "Trading Systems: Real Time Dashboard Using Microsoft Excel and VBA" aims to create a framework for predicting the direction of stock prices. Which here uses secondary data, Daily data format of 50 securities companies of Thailand (SET50) using data from 2014 to the present (Because the study report is in the form of Real Time Dashboard) using basic analysis tools such as Statement of Cash Flow, For technical analysis tools such as Stochastic Oscillator (STO) and Relative Strength Index (RSI) and the tools used in the prediction of the direction of the stock price via Bootstrapping Methods.

According to the study, Real Time Dashboard obtained from the Stock Exchange of Thailand 50 companies (SET50) can show different indicators which are Last Price, % Change, Price Sparkline, Cash Flow Symbol, Mu, Sigma, RSI, %K, %D, and Bootstrap in a co-organized report view. In 1 update of Dashboard data, it will take 1-2 minutes and then test the signal of STO + RSI which will have performance testing. 2 Step got the test results as follows: Step I Signal performance test from STO + RSI with historical data from 2014 until 30 Apr 2020, found that 1,034 securities were bought, sold 484 times (1 time equals 100 shares) The asset value as of 30 Apr 2020 equals -31,604 baht from the total investment of 5,807,167 baht with the return on investment at -0.5%, representing the average annual return of -0.1% and Step II result Signal performance testing from STO and RSI with data obtained from Bootstrapping Simulation for a total of 10,000 times, found that there are 291 average securities purchases, 72 securities average sales (1 time equals 100 shares), the average asset value at T = 250 equals to 34,441 baht, from an average investment of 1,288,988 baht with average return per year At 2.97% and finally tested the hypothesis using that strategy The hypothesis will be tested with historical data from 2014 until 30 Apr 2020, with 2 hypotheses tested at a significant level  = 0.05. Test results are as follows: Hypothesis 1: Return from using strategy "RSI & STO Signal + Bootstrapping" can provide a much higher rate of return than the rate of return from the use of signals from RSI & STO significant and the second hypothesis: the rate of return using the strategy "RSI & STO Signal + Bootstrapping" with Long Term Investment cannot give a higher return than the Never Rebalancing (buy-only) investment is insignificant.


Most read articles by the same author(s)

1 2 3 4 > >>