VBA is a programming language that interacts directly with Excel worksheets and cells, allowing us to write scripts to automate simple tasks, allowing even more analytical power to Excel. VBA is often used in the financial industry, where Steve just graduated with his Finance degree, and his parents are his first customers curious about investing. Though his clients are primarily interested in DAQO New Energy corporation (DQ), Steve wants to diversify and analyze other green energy stock options against DQ to offer the client variety. Using VBA, in this module, a macro will be created that can trigger pop-ups and inputs, read and change cell values, and format cells to consolidate and compare 12 stock options.
- VBA
- Microsoft Excel
Stock performance:
- DQ, the stock interest for this project, was the highest performing stock in 2017 (199.4%) but had the most vast performance pivot in 2018 (-62%).
- RUN’s rate of return increased 78.55% making it the greatest increase when all the other stock were failing
- ENPH, one of the top performing stocks (129.5%) in 2017 fell in 2018 but was able to maintain a positive ROR performance, indicative of a well founded stock option.
- TERP’s rate of return was down (-7%) in 2018 and fell even lower in 2017 (-5%), denoting a negative return from YTD and low purchase interest
Code Performance:
The original code for AllStockAnalysis() ran in:
- 0.26 seconds: 2017
- 0.37 seconds: 2018
The AllStockAnalysisRefactored() ran in:
- 0.08 seconds: 2017
- 0.07 seconds: 2018
- The refactored code reduce processing time by creating empty arrays to hold data and nested IF:Then statements that looped through the data one time and collected all of the information for simultaneous calculations. verses the original code that ran the calculation for each ticker 1 by 1
In a summary, There are advantages to refactoring code:
- Improves functionality of code by removing redundancies and duplications
- Executes code faster
- Organizes code better
-And disadvantages to refactoring code:
- could introduce new bugs and errors into the code
For this exercise, Refactoring the original code made it’s organization more concise, Improved the processing times, and Improved the functionality for future testability.