Create A Script That Loops Through All Stocks On

Question

Create A Script That Will Loop Through All The Stocks For One Create a script that will loop through all the stocks for one year and output 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 the opening price at the beginning of a given year to the closing price at the end of that year, and the total stock volume of the stock. The script should include conditional formatting to highlight positive changes in green and negative changes in red. The solution should also be able to return the stock with the greatest percent increase, greatest percent decrease, and greatest total volume. Make appropriate adjustments to the VBA script to ensure it runs on every worksheet, allowing for yearly analysis by executing the script once. Use the sheet alphabetical_testing.xlsx for developing your code, as this smaller dataset will facilitate faster testing. Ensure that the code runs on this file in less than 3-5 minutes and behaves consistently across each sheet. The intent of using VBA is to minimize the repetitive nature of tasks and streamline the process with a single click. To submit, upload screenshots for each year of your results on the Multi-Year Stock Data and the VBA scripts as separate files to GitHub.

Dr. Jack HW Helper · Accepted Answer

Creating an efficient VBA script that loops through stock data for a year and provides various insights is essential for data analysis in finance. This script will output critical information such as the ticker symbol, yearly change, percent change, and total stock volume for each stock available in the dataset. Setting Up the VBA Environment To begin, open Microsoft Excel and load the workbook named alphabetical_testing.xlsx. Press ALT + F11 to open the Visual Basic for Applications (VBA) editor. Here, you will create a new module to write your code. Insert a new module by right-clicking on any of the items in the project explorer window, navigating to Insert, and then clicking on Module. Writing the VBA Script The following script is a simplified version of what will loop through each stock's data, calculating the required statistics and applying conditional formatting: Sub AnalyzeStocks() Dim ws As Worksheet Dim lastRow As Long Dim stockData As Variant Dim i As Long Dim openingPrice As Double Dim closingPrice As Double Dim yearlyChange As Double Dim percentChange As Double Dim totalVolume As Double Dim greatestIncrease As Double Dim greatestDecrease As Double Dim greatestVolume As Double Dim ticker As String Dim resultRow As Long ' Loop through each worksheet For Each ws In ThisWorkbook.Worksheets lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row resultRow = 2 ' Start output from the second row ' Initialize values greatestIncrease = 0 greatestDecrease = 0 greatestVolume = 0 ' Loop through each row of stock data For i = 2 To lastRow ' Assuming row 1 has headers ticker = ws.Cells(i, 1).Value ' Ticker symbol openingPrice = ws.Cells(i, 2).Value ' Opening price closingPrice = ws.Cells(i, 3).Value ' Closing price totalVolume = ws.Cells(i, 4).Value ' Stock volume ' Calculate yearly change and percent change yearlyChange = closingPrice - openingPrice If openingPrice 0 Then percentChange = (yearlyChange / openingPrice) * 100 Else percentChange = 0 End If ' Output to shee

Create A Script That Loops Through All Stocks On ✓ Solved

Create A Script That Will Loop Through All The Stocks For One

Paper For Above Instructions

Setting Up the VBA Environment

Writing the VBA Script

Running the Script

Validation of Results

Conclusion

References