In this project, I had the opportunity to use VBA scripting to analyze stock market data. The task at hand was to create a VBA script that could loop through three different worksheets containing stock data, read and store key values from the table, and create a summary for each ticker that included yearly changes, percentage changes, and total stock volume for the year. Applying conditional formatting was also necessary to highlight positive and negative changes in the yearly change and percentage change columns. Lastly, the summary table for each ticker was used to find the tickers that had the greatest % increase, the greatest % decrease, and the greatest total volume.
Below is a snippet of my final script. The full version of my script is available on GitHub.
To test the script, I used a smaller version of the stock market data so that the script could run in a timely manner. Once the script was finalized, I successfully ran the script on the larger workbook that contained three years of stock market data. Because of the size of the dataset, the script takes a few minutes to load and the resulting file is also large, too large to upload to GitHub. The dataset file may be viewed on my Google Drive.
Below are screen captures of the outcome:
The main takeaway from this project is that VBA scripting can make repetitive tasks a lot easier by automating data analysis. By creating a script that loops through stock data, retrieves key values, and creates columns for analysis, users can quickly analyze large data sets and identify trends that might be difficult to see otherwise. This project provides an excellent foundation for using VBA scripting to automate data analysis and make it more accessible to non-programmers.
Overall, this project was an exciting and practical application of VBA scripting for data analysis. It was completed as part of the Data Analysis and Visualization Bootcamp at The University of Texas at San Antonio, and it provided a fantastic opportunity for us to learn about VBA scripting and its real-world applications.
Comments