These are projects posted by the students of Dr. Gove Allen at Brigham Young University. These students have taken one semester-long course on VBA and generally have had no prior programming experience

Thursday, December 6, 2012

S&P 500 & DOW Jones Stock Information

Executive Summary
Last summer, I was preparing for follow-up interviews with a private investment firm located in Southern California.  Before flying down for the interview I was contacted by one of the previous interns at the company to discuss the company objectives and some of the former projects he worked on.  The company’s overall objectives were two-fold:

1)      Manage, distribute and support mutual funds, investment funds and other investments for millions of individuals.
2) Manage assets for institutions around the world — corporations, governments, retirement plans and nonprofit organizations such as endowments and foundations.

As a result, Managers at the company were very interested in the daily stock market reports especially from the major indexes. One of the everyday roles I would be assigned as an intern was looking up stock price information for their various portfolios and also information for stocks in the DOW Jones Industrial Average.  I talked with the previous intern who said that this task took up between 1-2 hours every morning because of the time it took to manually look up each stock ticker and write down the information he needed to report.
Using this daily task as my inspiration, my project was to develop an excel workbook that automates this process as much as possible as well as decreasing the time it takes to perform the function and making the data easily readable and presentable. My workbook first uses a web query search to find each of the company’s and stock tickers in the DOW Jones Industrial Average and the S&P 500.  The workbook then uses these ticker symbols to find the following quote information for each symbol: a) Current Price, b) Previous Close Price, c) Volume of Shares Traded during the day, and d) Industry Type.  The program cleans up this initial data into a readable and workable way and then automatically sorts the worksheet by Industry, calculates the percentage change in price, and highlights any stock that has a 2% change (up or down) from the previous day’s closing price. 

Additionally, I added an option to examine a specific stock in more detail by adding a user form that allows the user to search for a specific ticker or company name in the worksheet, see the initial data already in the worksheet (this data populates the user form in order to read easier), and then be able to check for other data by checking 4 boxes on the form that will create a new sheet specific to that stock with information about the P/E ratio, the analyst rating, and a hyperlink to a site that displays stock price history and recent market news.

The initial worksheet of 530 stock tickers and information takes about 15 minutes to run, each new stock search takes less than a minute to run, and much more information is captured in this project workbook than compared to the 1-2 hour process the previous intern spent each day looking for fewer than 100 stocks.

1 comment:

  1. eToro is the #1 forex broker for new and professional traders.


Blog Archive