According to Investopedia: "The stock market refers to the collection of markets and exchanges where regular activities of buying, selling, and issuance of shares of publicly-held companies take place. Such financial activities are conducted through institutionalized formal exchanges or over-the-counter (OTC) marketplaces that operate under a defined set of regulations." The stock markets are essential components of a free-market economy because they enable democratized access to trading and capital exchange for investors of all kinds. Therefore, it is important to characterize how do actions of an enterprise or company behave and therefore to profit in the financial markets.
The idea is to design a Visual Basic (VBA) script to perform an analysis of real stock market data by using test data (to improve running time) and once the VBA script is fine then use it over real stock data..
By using a sample of the stock market data within the file alphabetical_testing.xlsx a VBA code is developed. This is done to ensure that the data set is small and therefore it allows a faster test (around 3 to 5 minutes)
The script loops 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 opening price at the beginning of a given year to the closing price at the end of that year.
-
The total stock volume of the stock.
-
It also includes conditional formatting that will highlight positive change in green and negative change in red.
-
The result is similar to the one in the image below.
Since there are other important indicators when studying the strength of stocks in the market and the VBA code must return the following data:
-
The stock with the "Greatest % increase",
-
The stock with "Greatest % decrease",
-
The stock with "Greatest total volume".
Such indicators are presented as shown below.
Note: Once the VBA scripts works on the short version data it is important to guarantee thar the VBA script will run on every worksheet of the complete data just by running the VBA script once. This is important since each worksheet represents a year.
The VBA script is uploaded into the github repository but not the databases. The results are presented in a PDF file named "VBA _challenge_screen_shots_AAV".
Based on VBA homework from Trilogy Education Services © 2019.