Module 2 VBA-challenge

In the completed file you can run the code manually or I have added a new sheet at the front called 'Menu' which has 2 buttons:

• The first adds the Ticker Data;
• The Second adds the Greatest data;
• You have the option for both buttons to select either All sheets or individual sheets

Link to excel file: https://1drv.ms/x/s!AibIjxY4OH9zijMXZc-Eg2IJXzEM

Background

You are well on your way to becoming a programmer and Excel expert! In this homework assignment, you will use VBA scripting to analyse generated stock market data. Depending on your comfort level with VBA, you may choose to challenge yourself with a few of the bonus Challenge tasks.

Instructions

Create a script that loops through all the stocks for one year and outputs the following information:

• The ticker symbol

• Yearly change from the opening price at the beginning of a given year to the closing price at the end of that year.

• The percentage change from the 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:

Bonus

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 enable it to run on every worksheet (that is, every year) at once.

Other Considerations

• Use the sheet alphabetical_testing.xlsx while developing your code. This dataset is smaller and will allow you to test faster. Your code should run on this file in under 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 the 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, but the bonus is an opportunity to further develop your skills and receive extra points for doing so.

References

Dataset created by Trilogy Education Services, a 2U, Inc. brand.# Module 2 Challenge

Background

You are well on your way to becoming a programmer and Excel expert! In this homework assignment, you will use VBA scripting to analyse generated stock market data. Depending on your comfort level with VBA, you may choose to challenge yourself with a few of the bonus Challenge tasks.

Instructions

Create a script that loops through all the stocks for one year and outputs the following information:

• The ticker symbol

• Yearly change from the opening price at the beginning of a given year to the closing price at the end of that year.

• The percentage change from the 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:

Bonus

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 enable it to run on every worksheet (that is, every year) at once.

Other Considerations

• Use the sheet alphabetical_testing.xlsx while developing your code. This dataset is smaller and will allow you to test faster. Your code should run on this file in under 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 the 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, but the bonus is an opportunity to further develop your skills and receive extra points for doing so.

References

Dataset created by Trilogy Education Services, a 2U, Inc. brand.