Stock Analysis Excel VBA

Overview

1. To compare the performance of Wall Street Stock between 2017 and 2018.
2. Take user input for the year to show total daily volumn and return for each stock for specific year.
3. Create clear worksheet button to get ready for next analysis.
4. To accelerate the execution time of all stock analysis for each year, the macro code was refactored.
5. Format and conditional format the table.

Result

1. Run Stock Analysis

• Sample code:

• Total daily volumn and return in 2017/2018 for each stock are displayed respectively on worksheet.

2. User friendly year input box and Clear button

   • codes

     Dim yearValue As String
       yearValue = InputBox("What year would you like to run the analysis on?")
     

     ClearWorksheet()
        Cells.Clear
     

   • Worksheet display
   

3. Compare 2017 and 2018 analysis result to select the stock worthy to invest.

   

   

4. The execution of All Stock Analysis for each year(2017 & 2018) was accelarated.

   • The execution of All Stock Analysis for 2017 was sped up from 1.070313 to 0.1875 seconds.

     

     

   • The execution time of All Stock Analysis for 2017 was sped up from 0.9609375 to 0.1875 seconds.

     

     

Summary

1. Steve's parent planned to put their investment on DQ. Unfortunatly, DQ's return has plummeted. Absolutely, DQ is not a smart choice. From the view of return for 2018, the stock of ENPH and RUN got 80~85% return, which seems investable. However, Comparing to 2017, Enph's return dropped from 129.5% to 81.9%, RUN's return increased from 5.5% to 84.0%. Overall, ENPH is more like a promising profitbale stock to make investment.

2. Two big changes were made under refactoring code.

• Variable types of startingPrice and endingPrice were declared as Single instead of Double.

• TickerIndex was introduced. This may avoid nested For Loop.

3. Refactoring code was applied to the VBA script, it helps VBA script run 4~5 times faster.

• Since Refactoring Macro could process starting Prices and endingPrice with single data type, Return can be caculated much faster.

• Refactoring script contains 3 indepent For Loop. Each row would be assigned to a certain tickerIndex. Thus each tickerIndex could loops over independently. The full worksheet could be only scanned for once. Whereas in the orginal script with nested for loop. For Each tickers, all the rows in the worksheet would be scanned. So it would be loop over for 12 times in total. This might be the main reason that execution time were damatically shortened. On the other hand, tickerIndex makes the code more complicate, which is easy to make mistakes for developer.