Like many of us today, Steve's parents are passionate about Renewable, or "Green", Energy. They would like to invest in a Green Energy company that make silicon waffles for solar panels. Steve's parents came to him for advice since he recently graduated with a finance degree. Steve knows that diversity is important for a successful portfolio, and would like to provide his parent with options for investing that would compliment the one company they have chosen. Steve has provided me with two years of data relating to several Green Energy corportations and has asked for me to run analysis that would provide him and his family with the information they need to make a wise investment.
I began by creating a sheet to house an analysis of the DQ stock that Steve's parents have decided to invest in. By using the Visual Basic Editor in Excel, I assigned a range for the tite and cells for the header row. I referenced the 2018 sheet to pull the data I would need. After defining variables for starting and ending prices, I used the code 'RowCount = Cells(Rows.Count, "A").End(xlUp).row' to find the number of rows containing data. I used this information to create a 'for' loop to caluculate the Total Daily Volume of the stock. By using an 'If' statement, I was able to reference the rows containing DQ data specifically. Looping through all rows on the sheet I used the code 'totalVolume = totalVolume + Cells(i, 8).Value' to calulate the total daily value of the stock and recorded it on the analysis sheet. I created another 'If' statment to find the price of the stock at the beginning and end of the year using the variables I assigned earlier. I then used the formula 'endingPrice / startingPrice - 1' to calulate the yearly return and record it on the analysis sheet. I determined that the DQ stock actually lost value in the year 2018, and expanded my analysis to include the other stocks Steve provided data for.
I created a new sheet called All Stocks Analysisa and reused code from the DQ sheet to create a title and header row. Then I created an array to house the ticker names of the stocks I was analyzing. I assigned 12 containers with string data using 'Dim tickers(12) As String' and assigned each stock a place in the array. I reused the code from my DQ Analysis to find Total Daily Value and yearly return for each stock. I nested this 'for' loop inside another loop that checked for the ticker name from the array. I applied static 'number.format' for ranges to reflect US currency and percentage as well as an 'if' statment with 'Interior.Color' to create conditional formatting 'to help Steve and his parents easily see which stocks increased and decreased in value. To make the sheet easy to use, I created a button to run the 'AllStocksAnalysis' macro and one to clear the sheet in between analyses. I included an 'InputBox' to allow the user to easily select the year for which the analysis would run.
The analysis of all stocks from 2017 as compared to 2018 showed that most of the stocks were inconsistent from one year to the next, showing positive returns in 2017 and negative returns in 2018. I noted, however, that many of the Totaly Daily Volumes were greater in 2018. One stock in particular, ENPH, increased in Total Daily Volume and maintained a positive return. After seeing this information, Steve wanted to expand the analysis to include the entire stock market. Using the code I wrote to analyze this much data could cause issues down the road and could take much longer than desired. Before moving on, I decided to measure the performance of the code and determine if refactoring could improve the code for use with larger data sets.
Before refactoring the code, I added a 'startTimer' and 'endTimer' line to the code to trigger a pop up with the run time for the 'AllStocksAnalysis' macro. Please reference these images for the run times and outcomes using the original code for [2017](Resources/2017 Original Code Analysis.PNG) and [2018](Resources/2018 Original Code Analysis.PNG). I created a new module and copied starter code into a 'sub' called 'AllStocksAnalysisRefactor'. In order to make the code more effient, I used a 'tickersIndex' to reference the array containing the ticker names elimintating the need to loop through the data twelve (or more) times to find the data for specific stocks. Then created arrays to house the Total Volume, Starting Price, and Ending Price for each stock. This allowed for one 'for' loop to run through all of the data and calculate the total daily value for each stock. Inside the 'for' loop, I used the same 'if' statements from the original code to find starting and ending prices to calulate the return. Within the 'for' loop I used the 'tickersIndex' as a variable so that the code would get all of the information it needed on one pass through the rows of data. After much debugging, I was able to refactor the code to work more efficiently as seen in the screenshots of the results of the refactored code for 2017 and 2018. I believe that the refactor will save a great deal of time and crashes when Steve uses it to run analyses for the entire stock market to help his parents.
Refactoring code can make it run more efficently and easier to update. In the case of the code I wrote for Steve, by cutting out extra steps, the code ran much faster than before. This will help when the code is applied to much larger data sets. If it takes too long for code to run, computers (and their users) may crash before it can run its course. The code is also less complicated to understand. Rather than nesting loops inside of loops inside of loops, using an index for an array makes code easty to adjust. If Steve wanted more information on his stocks, I could simiply add another array to access data for use in othe calculations. Refactoring code can also cause some issues. When a person sets out to improve their code, they may not have an idea of how much time it will take. One may also run in to the problem of being stuck and not knowing where to turn. I experienced this while refactoring the 'AllStocksAnalysis'. I poured over my lines of code attempting to find my error. I googled and debugged. I reached out to classmates as well. It took several hours over a few days to find the issue in my syntax. I realize that I am a rookie coder and this project was rather simple, but I experienced a great deal of productive struggle to make this new code work. Time is a factor that should be taken into consideration when deciding whether or not to refactor your code. If you intend to use the code frequently, if you are not the end user, or if the code will be applied to ever increasing or changing data sets it may be worth it to spend the time and effort to improve your code. It is important to make a local copy, comment often, and save new versions frequently. This will help find where your code is improving and where you need to debug or investigate further.