Utilize VBA scripting to analyze real stock market data for 3 consecutive years (2018 - 2020).
The stock market creates massive data in a fraction of seconds that either increase or decrease one’s investments. Imagine manually analyzing stock data by hand using basic knowledge of excel. It’s likely that you will have some investors, or you, that are indifferent to manually analyzing stock data. Creating a VBA script to arrange the stock data in a fast, proficient process can be the transformation of gaining or losing profit on a ticker. The VBA script can share stock tickers with greatest increase and decrease percentage, stock or greatest total volume, yearly change rate, and percent change. Imagine being able to reveal profitable stocks and/or stock market crashes well in advance. Let’s look at the high-level overview of the VBA script that assisted in summarizing stock data year over year!
Create an excel VBA script that loops through all the stocks year over year with output of the following:
- Ticker Symbol (AAB, AAF, etc.…)
- Yearly Change (Yearly Closing Price – Yearly Opening Price)
- Percent Change (Yearly Change/Yearly Open Price)
- Total Stock Volume (Sum “Volume” if ticker symbols are the same)
Display conditional formatting for “Yearly Change” column to reflect positive change (green) and negative change (red)
Second (Bonus) VBA Challenge
- Add a loop to display a summary table that shows stock ticker with greatest increase, decrease, and total volume
2018 Stock Market Analysis
2019 Stock Market Analysis
2020 Stock Market Analysis
