Stock data analysis using VBA to determine market trend during 2017 and 2018 years
The Excel sheets provide data related to green energy stocks during the 2017 and 2018 years. By analyzing the dataset, it can be determined whether the investment in a particular stock is worthy or not.
Each sheet related to 2017 or 2018 consists of about 3000 rows and 8 columns. In total, information about 12 different stocks is available. Columns include some attributes associated with the stock ticker, date, opening and closing price of the stock, lowest and highest daily price of the stock, and daily volume of stock.
In this project, we will help Steve, who wants to analyze and understand a trend of the green energy markets for his parents. His parents are only interested in investing their money in DQ, a new energy corporation that makes silicon wafers for solar panels. However, Steve wants to analyze other energy stocks besides DQ to make sure they get the most advantage from their investment.
The data are analyzed using Visual Basic for Applications (VBA). Several conditionals and nested loop are utilized. Also, a user-friendly interface is created, enabling a user to run the functions by only clicking the buttons. Finally, the code is refactored to improve its logic and efficiency.
The “All Stocks Analysis” sheet with three columns named Ticker, Total Daily Volume, and Return is created to analyze the data. Figure 1 represents the total daily volume and percentage of yearly return for all the green energy stocks in 2017 and 2018.
From Figure 1 (left), it can be observed that in 2017, the percentage of yearly return for all the stocks except one of them (i.e., TERP) is positive and is varied in the range of 5%-200%. However, we can interpret that this trend is different in 2018 (Figure 1, right). In this year the percentage of the return is negative for most of the stocks (10 out of 12). We can conclude that for the majority of stocks, 2017 was the better year for investment compared to 2018.
Steve‘s parents were interested in DQ stock. Results show that for this stock, although the volume of daily trading in 2018 is increased considerably compared to 2017, the rate of yearly return is significantly decreased from 199.4% to -62.6%. So, it can be concluded that the performance of DQ stock is unsatisfactory, and it is an unstable and unpredictable stock for investment.
Two modules with the same outputs (Figure 1) are created in green_stocks.xlsm. In the following, the difference between these modules and the time of execution are discussed.
The main difference between the two modules is that in Module 2, Module 1’s code is refactored in which the nested loop is substituted by the For loops. Figures 3 and 4 (left) represent part of the code before refactoring and after refactoring, respectively. As shown in Figure 3 (left), in the outer For loop, two different worksheets are activated in each iteration, which can increase the execution time. However, Figure 4 (left) shows the refactored code that the inner loop is eliminated, and an array named ticker volume and a variable called ticker index are defined. In addition, to store the outputs in a worksheet, another separated For loop is created. Figures 3 and 4 (right) show that after refactoring code, the execution time is decreased from 0.62 seconds to 0.11 seconds.
Code refactoring is an important process in computer programming to reorganize the code that its behavior remains the same. the advantages and disadvantages of refactoring code, in general, are as follows:
Advantages:
Refactoring code helps to decrease the execution time and makes the code as efficient as possible. Refactoring code improves the readability of code, and therefore, it is easier to follow and understand codes. As code is cleaner and the logic of the code is improved, debugging and adding new functionality to code for future purpose is easier.
Disadvantages: Refactoring code is a time-consuming process. In other words, it does not guarantee how much time is needed to get a desirable outcome. This process may influence the outputs. However, the main aim of refactoring code is to improve the code without manipulating the software’s functionality.
In this project, Module 1 (before refactoring code) took about 0.6 seconds to run; however, in Module 2 the run time is decreased by about 82%. In addition, by eliminating the nested For loop, the code is easier to read, and it is not needed to activate and switch between different worksheets in each iteration.
We made a trade-off between gaining advantages of refactoring code and spending time to clean the original code, which gave us the same result as the original one. However, as the dataset is small, the run time for the original code is reasonable, and for the larger dataset, refactoring can be more beneficial.