Background Steve loves the workbook you prepared for him. At the click of a button, he can analyze an entire dataset. Now, to do a little more research for his parents, he wants to expand the dataset...

Background

Steve loves the workbook you prepared for him. At the click of a button, he can analyze an entire dataset. 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. Although your code works well for a dozen stocks, it might not work as well for thousands of stocks. And if it does, it may take a long time to execute.

In this challenge, you’ll edit, orrefactor, 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. Then, you’ll determine whether refactoring your code successfully made the VBA script run faster. Finally, you’ll present a written analysis that explains your findings.

Refactoring is a key part of the coding process. 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 common on the job because first attempts at code won’t always be the best way to accomplish a task. Sometimes, refactoring someone else’s code will be your entry point to working with the existing code at a job.

What You're Creating

This new assignment consists of one technical deliverable and a written report to deliver your results. You will submit the following:

• Deliverable 1: Refactor VBA code and measure performance
  
  
  • This deliverable will include an updated workbook and a folder with PNGs of the pop-ups with script run time
  
  
  
  


• Deliverable 2: A written analysis of your results (README.md)

Files

Use the following link to download the Challenge starter code.

Download challenge_starter_code.vbs(Links to an external site.)

Deliverable 1: Refactor VBA Code and Measure Performance (80 points)

Deliverable 1 Instructions

Use your knowledge of VBA and the starter code provided in this Challenge to refactor theModule2_VBA_Scriptso you loop through the data one time and collect all of the information. Your refactored code should run faster than it did in this module.

REWIND

For this deliverable, you’ve already done the following as part of Module 2:

• 
  
  Lesson 2.1.4
  : Create a variable with single or long data types.


• 
  
  Lesson 2.2.3
  : Writeforloops.


• 
  
  Lesson 2.2.3
  : Writeif-thenstatements.


• 
  
  Lesson 2.2.3
  : Use design patterns.


• 
  
  Lesson 2.2.4
  : Use logical and comparison operators.


• 
  
  Lesson 2.3.2
  : Use an index to access data in an array.


• 
  
  Lesson 2.3.2
  : Use nested loops.


• 
  
  Lesson 2.3.3
  : Reuse code.


• 
  
  Lesson 2.3.3
  : Debug and comment on code.


• 
  
  Lesson 2.4.1
  : Use visual and numeric formatting.


• 
  
  Lesson 2.4.2
  : Use conditional formatting.


• 
  
  Lesson 2.5.3
  : Measure code performance.

1. Download thechallenge_starter_code.vbsfile and rename itVBA_Challenge.vbs.


2. Create a folder called “Resources” to hold the run-time pop-up messages that you’ll screenshot after running refactored analyses for 2017 and 2018.


3. Rename thegreen_stocks.xlsmfile that you used in this module asVBA_Challenge.xlsm.


4. Add theVBA_Challenge.vbsscript to the Microsoft Visual Basic editor.


5. Use the steps below to add code where indicated by the numbered comments in the starter code file.

Step 1a:

• Create atickerIndexvariable and set it equal to zero before iterating over all the rows. You will use thistickerIndexto access the correct index across the four different arrays you’ll be using: the tickers array and the three output arrays you’ll create in Step 1b.

Step 1b:

• Create three output arrays:tickerVolumes,tickerStartingPrices, andtickerEndingPrices.
  
  
  • ThetickerVolumesarray should be aLongdata type.
  
  
  • ThetickerStartingPricesandtickerEndingPricesarrays should be aSingledata type.
  
  
  
  

Step 2a:

• Create aforloop to initialize thetickerVolumesto zero.

Step 2b:

• Create aforloop that will loop over all the rows in the spreadsheet.

Step 3a:

• Inside theforloop in Step 2b, write a script that increases the currenttickerVolumes(stock ticker volume) variable and adds the ticker volume for the current stock ticker.
  
  
  • Use thetickerIndexvariable as the index.
  
  
  
  

If you’d like a hint on how to increase the currenttickerVolumesby using thetickerIndexvariable as the index, that’s totally okay. If not, that’s great too. You can always revisit this later if you change your mind.

HINT

Step 3b:

• Write anif-thenstatement to check if the current row is the first row with the selectedtickerIndex. If it is, then assign the current starting price to thetickerStartingPricesvariable.

Step 3c:

• Write anif-thenstatement to check if the current row is the last row with the selectedtickerIndex. If it is, then assign the current closing price to thetickerEndingPricesvariable.

Step 3d:

• Write a script that increases thetickerIndexif the next row’s ticker doesn’t match the previous row’s ticker.

Step 4:

• Use aforloop to loop through your arrays (tickers,tickerVolumes,tickerStartingPrices, andtickerEndingPrices) to output the “Ticker,” “Total Daily Volume,” and “Return” columns in your spreadsheet.

Finally, run the stock analysis, then confirm that your stock analysis outputs for 2017 and 2018 are the same as they were in the module (as shown in the images below). In your Resources folder, save the pop-up messages showing elapsed run time for the refactored code asVBA_Challenge_2017.pngandVBA_Challenge_2018.png. Then, save the changes to your workbook.

Deliverable 1 Requirements

You will earn a perfect score for Deliverable 1 by completing all requirements below:

• ThetickerIndexis set equal to zero before looping over the rows.(5 pt).


• Arrays are created fortickers,tickerVolumes,tickerStartingPrices, andtickerEndingPrices
  (15 pt).


• ThetickerIndexis used to access the stock ticker index for thetickers,tickerVolumes,tickerStartingPrices, andtickerEndingPricesarrays(15 pt).


• The script loops through stock data, reading and storing all of the following values from each row:tickers,tickerVolumes,tickerStartingPrices, andtickerEndingPrices
  (25 pt).


• Code for formatting the cells in the spreadsheet is working(5 pt).


• There are comments to explain the purpose of the code(5 pt).


• The outputs for the 2017 and 2018 stock analyses in theVBA_Challenge.xlsmworkbook match the outputs from the AllStockAnalysis in the module(5 pt).


• The pop-up messages showing the elapsed run time for the script are saved asVBA_Challenge_2017.pngandVBA_Challenge_2018.png
  (5 pt).

Deliverable 2: Written Analysis of Results (20 points)

Deliverable 2 Instructions

Initialize your repository with a README, and write your analysis of Deliverable 1.

NOTE

Thisdocumentation(Links to an external site.)provides more information about basic writing and formatting syntax for GitHub.

For your written analysis, be sure to use complete and coherent sentences. Your written analysis should contain three sections, which cover the following:

1. Overview of Project: Explain the purpose of this analysis.


2. Results: Using images and examples of your code, compare the stock performance between 2017 and 2018, as well as the execution times of the original script and the refactored script.


3. Summary: In a summary statement, address the following questions.
   
   
   1. What are the advantages or disadvantages of refactoring code?
   
   
   2. How do these pros and cons apply to refactoring the original VBA script?
   
   
   
   

Deliverable 2 Requirements

Structure, Organization, and Formatting Requirements (8 points)

The written analysis contains the following structure, organization, and formatting:

• There is a title, and there are multiple paragraphs(2 pt).


• Each paragraph has a heading(2 pt).


• There are subheadings to break up text(2 pt).


• Links are working, and images are formatted and displayed where appropriate(2 pt).

Analysis Requirements (12 points)

The written analysis has the following:

• Overview of Project
  
  
  • The purpose and background are well defined(2 pt).
  
  
  
  


• Results
  
  
  • The analysis is well described with screenshots and code(4 pt).
  
  
  
  


• Summary
  
  
  • There is a detailed statement on the advantages and disadvantages of refactoring code in general(3 pt).
  
  
  • There is a detailed statement on the advantages and disadvantages of the original and refactored VBA script(3 pt).
  
  
  
  

Submission

Once you’re ready to submit, make sure to check your work against the rubric to ensure you are meeting the requirements for this Challenge one final time. It’s easy to overlook items when you’re in the zone!

As a reminder, upload the following deliverables to your stock-analysis GitHub repository:

• Deliverable 1: Refactor VBA code and measure performance
  
  
  • TheVBA_Challenge.xlsmworkbook
  
  
  • The Resources folder withVBA_Challenge_2017.pngandVBA_Challenge_2018.png
    
  
  
  
  


• Deliverable 2: A written analysis of your results (README.md)

To submit your challenge assignment, click Submit, then provide the URL of your stock-analysis GitHub repository for grading. Comments are disabled for graded submissions in BootCampSpot. If you have questions about your feedback, please notify your instructional staff or the Student Success Manager. If you would like to resubmit your work for an improved grade, you can use theRe-Submit Assignmentbutton to upload new links. You may resubmit up to 3 times for a total of 4 submissions.

IMPORTANT

Once you receive feedback on your Challenge, make any suggested updates or adjustments to your work. Then, add this week’s Challenge to your professional portfolio.

NOTE

You are allowed to miss up to two Challenge assignments and still earn your certificate. If you complete all Challenge assignments, your lowest two grades will be dropped. If you wish to skip this assignment, click Submit then indicate you are skipping by typing “I choose to skip this assignment” in the text box.

Rubric

Module-2 RubricModule-2 Rubric

Criteria	Ratings	Pts
This criterion is linked to a learning outcomeDeliverable 1: Refactor VBA Code and Measure Performance.	80to >71.0PtsMastery✓The tickerIndex is set to equal to zero before looping over the rows. ✓Arrays are created for tickers and ALL output arrays. ✓The tickerIndex is used to access the stock ticker index for the tickers array and ALL THREE output arrays. ✓The script loops through stock data, reading and storing ALL of the following values from each row: tickers, volumes, starting prices, ending prices. ✓Code for formatting the cells in the spreadsheet is working. ✓The output for the 2017 and 2018 stock analyses match the outputs from the AllStockAnalysis. ✓The pop-up messages are saved as PNGs. 71to >67.0PtsApproaching Mastery✓The tickerIndex is set to equal to zero before looping over the rows. ✓Arrays are created for tickers and TWO of the THREE output arrays. ✓The tickerIndex is used to access the stock ticker index for the tickers array and ALL THREE output arrays. ✓The script loops through stock data, reading and storing THREE of the following values from each row: tickers, volumes, starting prices, ending prices. ✓Code for formatting the cells in the spreadsheet is working. ✓The output for the 2017 and 2018 stock analyses match the outputs from the AllStockAnalysis. ✓The pop-up messages are saved as PNGs. 67to >59.0PtsProgressing✓The tickerIndex is set to equal to zero before looping over the rows. ✓Arrays are created for tickers and TWO of the THREE output arrays. ✓The tickerIndex is used to access the stock ticker index for the tickers array and TWO output arrays. ✓The script loops through stock data, reading and storing TWO of the following values from each row: tickers, volume, starting prices, ending prices. ✓Code for formatting the cells in the spreadsheet is working. ✓The output for the 2017 and 2018 stock analyses match the outputs from the AllStockAnalysis. ✓The pop-up messages are saved as PNGs. 59to >0.0PtsEmerging✓The tickerIndex is set to equal to zero after looping over the rows. ✓Arrays are created for tickers and ONE of the THREE output arrays. ✓The tickerIndex is used to access the stock ticker index for the tickers array and ONE output array. ✓The script loops through stock data, reading and storing ONE of the following values from each row: tickers, volume, starting prices, ending prices. ✓Code for formatting the cells in the spreadsheet is working. ✓The output for the 2017 and 2018 stock analyses match the outputs from the AllStockAnalysis. ✓The pop-up messages are saved as PNGs. 0PtsIncomplete	80pts
This criterion is linked to a learning outcomeDeliverable 2: Structure, Organization, and Formatting.	8to >7.0PtsMastery✓The written analysis has a title and multiple paragraphs. ✓Each paragraph has a heading and subheadings. ✓Links are working, images and code are correct and displayed where appropriate. 7to >6.0PtsApproaching Mastery✓The written analysis has a title and multiple paragraphs. ✓Each paragraph has a heading and subheadings. ✓Some links are not working, AND some code is displayed where appropriate. 6to >4.0PtsProgressing✓The written analysis has a title and multiple paragraphs. ✓Each paragraph has a heading, and there may be subheadings for each paragraph, OR links are working, images and code are displayed where appropriate. 4to >0.0PtsEmerging✓The written analysis has a title and multiple paragraphs. ✓There may be a heading OR subheadings for each paragraph, OR all links are working, images and code are displayed where appropriate. 0PtsIncomplete	8pts
This criterion is linked to a learning outcomeDeliverable 2: Analysis	12to >11.0PtsMastery✓The Deliverable Fulfills "Emerging" Required Criteria AND has the following: ✓There is a detailed summary of the pros and cons of refactoring code. ✓There is a detailed summary of the pros and cons of the original and refactored VBA script. 11to >8.0PtsApproaching Mastery✓The Deliverable Fulfills "Emerging" Required Criteria AND has at least ONE of the following: ✓There is a detailed summary of the pros and cons of refactoring code OR there is a key point missing. ✓There is a detailed summary of the pros and cons of the original and refactored VBA script OR there is a key point missing. 8to >6.0PtsProgressing✓The Deliverable Fulfills "Emerging" Required Criteria AND has at least ONE of the following: ✓The summary of refactoring code is missing key points. ✓The summary of the original and refactored VBA script is missing key points. 6to >0.0PtsEmerging✓The purpose and background are well defined. ✓The analysis is well described with screenshots and code. 0PtsIncomplete	12pts
Total points:100

2.5.3: Measure Code Performance" style="float: left;">Previous
Module 2 Career Connection" style="float: right;">Next© 2020 - 2021 Trilogy Education Services, a 2U, Inc. brand. All Rights Reserved.