You are well on your way to becoming a programmer and Excel master! In this homework assignment, you will use VBA scripting to analyze generated stock market data. Depending on your comfort level with VBA, you may choose to challenge yourself with a few of the challenge tasks.
-
Create a new repository for this project called
VBA-challenge. Do not add this homework to an existing repository. -
Inside the new repository that you just created, add any VBA files that you use for this assignment. These will be the main scripts to run for each analysis.
-
Test Data - Use this while developing your scripts.
-
Stock Data - Run your scripts on this data to generate the final homework report.
Create a script that loops through all the stocks for one year and outputs the following information:
-
The ticker symbol.
-
Yearly change from opening price at the beginning of a given year to the closing price at the end of that year.
-
The percent change from opening price at the beginning of a given year to the closing price at the end of that year.
-
The total stock volume of the stock.
Note: Make sure to use conditional formatting that will highlight positive change in green and negative change in red.
The result should match the following image:
Add functionality to your script to return the stock with the "Greatest % increase", "Greatest % decrease", and "Greatest total volume". The solution should match the following image:
Make the appropriate adjustments to your VBA script to allow it to run on every worksheet (that is, every year) just by running the VBA script once.
-
Use the sheet
alphabetical_testing.xlsxwhile developing your code. This data set is smaller and will allow you to test faster. Your code should run on this file in less than 3 to 5 minutes. -
Make sure that the script acts the same on every sheet. The joy of VBA is that it takes the tediousness out of repetitive tasks with one click of a button.
-
Some assignments, like this one, contain a bonus. It is possible to achieve proficiency for this assignment without completing the bonus. The bonus is an opportunity to further develop your skills and be rewarded extra points for doing so.
To submit, please upload the following to GitHub:
-
A screen shot for each year of your results on the multi-year stock data.
-
VBA scripts as separate files.
Be sure to commit regularly to your repository and that it contains a README.md file.
After saving your work, create a shareable link and submit the link to https://bootcampspot-v2.com/.
Unit 2 Rubric - VBA Homework - The VBA of Wall Street
- Dataset generated by Trilogy Education Services, LLC.
© 2022 Trilogy Education Services, a 2U, Inc. brand. All Rights Reserved.