Skip to content

Commit

Permalink
Update README.md
Browse files Browse the repository at this point in the history
  • Loading branch information
kittychew authored Jul 12, 2024
1 parent ca3d603 commit c150562
Showing 1 changed file with 45 additions and 1 deletion.
46 changes: 45 additions & 1 deletion README.md
Original file line number Diff line number Diff line change
@@ -1 +1,45 @@
# vba_challenge
Stock Analysis VBA Script

Overview

This VBA script analyzes stock data stored in an Excel workbook. It loops through each worksheet, calculates quarterly changes, percentage changes, and total volumes for each stock ticker, and outputs the results into a summary table. Additionally, it identifies stocks with the greatest percentage increase, decrease, and maximum trading volume.

Features

• Main Subroutine: Main()
• Loops through each worksheet in the workbook, formats data using FormatAllSheets, and processes stocks using LoopThruStocks.
• Formatting Sheets: FormatAllSheets(ws As Worksheet)
• Formats specified columns as numbers with two decimal places across all worksheets.
• Processing Stocks: LoopThruStocks(ws As Worksheet)
• Calculates quarterly changes, percentage changes, and total volumes for each stock ticker.
• Identifies and updates the summary table with the results.
• Highlights positive changes in green and negative changes in red using conditional formatting.
• Output: Summary table includes:
• Ticker symbol
• Quarterly change from opening to closing price
• Percentage change from opening to closing price
• Total stock volume
• Additional Functionality:
• Identifies stocks with the “Greatest % Increase”, “Greatest % Decrease”, and “Greatest Total Volume”.
• Outputs results in accordance with provided example image.

Usage

1. Excel Environment:
• Ensure Excel (Version 16.86) is installed with macros enabled.
2. Running the Script:
• Open your Excel workbook containing stock data.
• Access the VBA editor (Alt + F11), insert a new module, and paste the script.
• Run the Main subroutine to execute the analysis across all worksheets simultaneously.
3. Conditional Formatting:
• The script applies conditional formatting to highlight:
• Positive changes in green.
• Negative changes in red.

Dependencies

• Excel Version: Developed and tested on Microsoft Excel (Version 16.86).
• Resources Used: YouTube tutorial (https://www.youtube.com/watch?v=3OfVIsKy59c&ab_channel=EverydayVBA), Microsoft Community post (https://answers.microsoft.com/en-us/msoffice/forum/all/how-do-i-convert-a-date-formatted-as-text-as/a89d4a1d-cb39-459d-84d6-d473d90f4182), and assistance from ChatGPT.

License
This project is licensed under the MIT License.

0 comments on commit c150562

Please sign in to comment.