There are comments to explain the purpose of the code.Īdding Comments is requiered, as a Best Practices for Writing Super Readable Code such,ħ. Added some formatting based on the values of the returns.Ħ. We make positive returns green and negative returns red, to be a lot easier to determine which stocks did well and which ones didn't. Code for formatting the cells in the spreadsheet is working. If it is, then assign the current closing price to the tickerStartingPrices and tickerEndingPrices variable.ĥ. Stored values from tickerStartingPrices and tickerEndingPricesĬreated an if-then statement to check if the current row is the first row with the selected tickerIndex. Inside the loop, we created a script that increases the current tickerVolumes (stock ticker volume) variable and adds the ticker volume for the current stock ticker. The script loops through stock data, reading and storing all of the following values from each row: tickers, tickerVolumes, tickerStartingPrices, and tickerEndingPrices.Ĭreated a loop that will loop over all the rows in the spreadsheet. The tickerIndex is used to access the stock ticker index for the tickers, tickerVolumes, tickerStartingPrices, and tickerEndingPrices arrays.Ĭreated a for loop to initialize the tickerVolumes to zero.Īnd if the next row’s ticker doesn’t match, increase the tickerIndex.Ĥ. In our VBA code, the tickerVolumes array should be a Long data type.īut in our VBA code the tickerStartingPrices and tickerEndingPrices arrays should be a Single data type.ģ. Arrays are created for tickers, tickerVolumes, tickerStartingPrices, and tickerEndingPrices.Ĭreated three output arrays: tickerVolumes, tickerStartingPrices, and tickerEndingPrices. Will use this tickerIndex to access the correct index across the four different arrays on VBA Code: the tickers array and the three output arrays created on next requierement.Ģ. The tickerIndex is set equal to zero before looping over the rows.Ĭreated a tickerIndex variable and set it equal to zero before iterating over all the rows. RESULTS: Refactor VBA Code and Measure Performance Deliverable Requirements, Code Examples, Compare Stock Performance and Timestamp procedure below:ġ. Sometimes, refactoring someone else’s code will be your entry point to working with the existing code at a job. Refactoring is common on the job because first attempts at code won’t always be the best way to accomplish a task. When refactoring code, you aren’t adding new functionality you just want to make the code more efficient-by taking fewer steps, using less memory, or improving the logic of the code to make it easier for future users to read. Refactoring is a key part of the coding process. Finally, you’ll present a written analysis that explains your findings. Then, you’ll determine whether refactoring your code successfully made the VBA script run faster. In this challenge, you’ll edit, or refactor, the Module 2 solution code to loop through all the data one time in order to collect the same information that you did in this module. And if it does, it may take a long time to execute. Although your code works well for a dozen stocks, it might not work as well for thousands of stocks. Now, to do a little more research for his parents, he wants to expand the dataset to include the entire stock market over the last few years. At the click of a button, he can analyze an entire dataset. Steve loves the workbook you prepared for him. Use your knowledge of VBA and the starter code provided in this Project to refactor the VBA Script dataset so we loop through the data one time and collect all of the information.
Use the steps Refactor VBA code and measure performance to add code where indicated by the numbered comments in the starter code file.Add the VBA_Challenge.vbs script to the Microsoft Visual Basic editor.Create and convert our XLSM file from *.vbs dataset that you used in this module as VBA_Challenge.xlsm.Create our resources folder in GitHub to hold the run-time pop-up messages that we’ll screenshot after running refactored analyses for 20.Prepare our dataser VBA_Challenge.vbs file for the project.Here's a quick look at the Kickstarting Analysis and Challenges of this Project, including the following tasks: Finally, we just want to make the code more efficient-by taking fewer steps, using less memory, or improving the logic of the code to make it easier for future users to read. Then, we’ll determine whether refactoring your code successfully made the VBA script run faster. In this project and analyisis, we’ll edit, or refactor, the Stock Market Dataset with VBA solution code to loop through all the data one time in order to collect an entire dataser. STOCK ANALYSIS WITH VBA + EXCEL OVERVIEW: VBA Stock Analysis Project Purpose