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.
No comments:
Post a Comment