The purpose of this VBA script is to loop through stock data to extract, analyze, and document meaningful data for each stock in each year of data provided. The script outputs condensed stock information for that year and highlights the stocks that had the greatest percent increase, greatest percent decrease, and greatest total volume.
The module was created to analyze stock data with the following assumptions:
- Each worksheet represents a different year
- Tickers in each sheet are alphabetized, not randomly ordered
- Columns for raw data are, in order: ticker, date, open price, high price, low price, close price, volume
This VBA module loops through all sheets in a workbook. In each worksheet, the module loops through all stocks in a year (each worksheet represents one year) and outputs:
- The ticker symbol
- The yearly change from opening price at the beginning of a given year to the closing price at the end of that year. The cells are conditionally formatted such that a cell with a zero/positive yearly change is green and a cell withe a negative yearly change is red
- The percent change from opening price at the beginning of a given year to the closing price at the end of that year. Cells are formatted with percent format
- The total volume or the stock for that year