Module_2_Using_VBA_with_Excel
Purpose of this assignment was to build skill with Visual Basic by performing an analysis of various stocks.
For the data set:
- There were 12 stock symbols
- Data was separated by year into separate worksheets
- Within each sheet, data was organized in table alphabetically by stock symbol
This is the code used during the lesson to perform an initial analysis of the stock data. Skills explored include:
- For loops
- If Then statements
- Variable definition and type including arrays
- Text and cell formatting
- Basic math and logical operators
This is the code used during independent challenge. Primary goal was to introduce a tickerIndex variable which could be used as an address to tie a specific stock symbol with the starting price, ending price, and calculated total volume for that same stock symbol.
This was a great exploration of multiple facets of Visual Basic language and the strength it can provide in performing repetitive analysis at the click of a button. In addition to introducing to basic programming concepts it also taught a lesson in refactoring or editing code to improve performance.
Refactoring code provides a couple of key advantages:
- Improving efficiency when running. This reduces strain on system resources & reduces wait time for end user.
- Introducing & inceasing exception handling. Initial iteration of creating code often overlooks error handling. Example: Return Calculation may run into divide by zero error if tickerStartingPrices is zero
Cells(4 + i, 3).Value = tickerEndingPrices(i) / tickerStartingPrices(i) - 1The primary disadvantage to refactoring code is the ROI. Before refactoring, the issue being addressed needs to be weighed against the needs of the business, the impact the code is having on the business, and the resources available to refactor.
Refactor specific to this Stock Analysis was as a learning exercise--so the ROI is demonstrated.
The primary advantage was to conceptually teach managing a multi-dimensional array through a series of loops.
| Stock Symbol | Starting Price | Ending Price | Total Volume |
|---|---|---|---|
| AY | 19.47 | 21.21 | 136070900 |
Another advantage was the use of a timer function to demonstrate how quickly the code was performing.
This is a huge advantage in programming and data analysis.
The primary disadvantage of this specific refactor? This is difficult to say & is subject to opinion.
- Perhaps the tickerIndex could have been used to step through the stock data and exit the loop when the symbol changed?
- What if the stock data was not arranged alphabetically by stock symbol? The code could be made to search through the data and identify startPrice based on the Date field. There are numerous possibilities.