Utilizing VBA to Optimize Stock Portfolios — Presentation Detail — Celebration of Scholars

Instructions

Student presentations must have a faculty sponsor.

Abstracts must include a title and a description of the research, scholarship, or creative work. The description should be 150-225 words in length and constructed in a format or style appropriate for the presenter’s discipline.

The following points should be addressed within the selected format or style for the abstract:

A clear statement of the problem or question you pursued, or the scholarly goal or creative theme achieved in your work.
A brief comment about the significance or uniqueness of the work.
A clear description of the methods used to achieve the purpose or goals for the work.
A statement of the conclusions, results, outcomes, or recommendations, or if the work is still in progress, the results you expect to report at the event.

Presenter photographs should be head and shoulder shots comparable to passport photos.

Additional Information

More information is available at carthage.edu/celebration-scholars/. The following are members of the Research, Scholarship, and Creativity Committee who are eager to listen to ideas and answer questions:

Jun Wang
Kim Instenes
John Kirk
Nora Nickels
Andrew Pustina
James Ripley

Utilizing VBA to Optimize Stock Portfolios

Name: Bryan Feraric
Major: Finance
Hometown: Kenosha
Faculty Sponsor:
Other Sponsors:
Type of research: Course project

Abstract

Investors and other practitioners within the financial industry utilize risk and return as tools to evaluate the performance of investments. In modern portfolio theory, investors seek to construct portfolios to maximize expected return based on a given level of market risk. In other words, investors use the risk and return of an equity to determine whether the future performance of that stock will be favorable to the portfolio overall. The amount of portfolio risk that should be tolerated is completely at the discretion of the investor and should be based on their level of risk aversion. In order to streamline this process, a program can be written to automate it. One programming language with such capabilities is VBA.

Visual Basic for Applications (VBA) is an object-oriented programming language that was developed by Microsoft. It is an implementation of Microsoft’s Visual Basic programming language that has been integrated into Microsoft Office products such as Excel. Using VBA code, programmers can automate all functions already existent within the Excel application.

Using VBA and applying modern portfolio theory, a portfolio optimizing program was constructed to automate a portfolio based on a selection of stocks. The user selects any publicly traded stocks and the program pulls historic price data from the internet. Then the portfolio optimizer creates six different portfolios, each composed of various weightings of the same selection of stocks. Using risk and return as a measure, these portfolios can be compared to the risk and return of the entire stock market for a given period. In doing so, users of the program can select an optimized portfolio that’s performance exceeds the market while also adhering to their own level of risk tolerance.

Poster file