Finance and stock trading
12/09/2019
Case Study on globalizations
12/09/2019

TFIN 420 Visual Basic Programming

Dr. Sergio Davalos

Table of Contents

Final Project:
Introduction/Description ………………………………………………………………………………………….. 1
Portfolio Optimization ……………………………………………………………………………………………… 1
Capital Asset Pricing Model ……………………………………………………………………………………… 1
Option Pricing Model ………………………………………………………………………………………………. 2
Simulation of Stock Price …………………………………………………………………………………………. 2
Implementation of the Models:
User Control Panel ………………………………………………………………………………………………….. 3-4
Portfolio Optimization ………………………………………………………………………………………………

4-5
Calculate Beta …………………………………………………………………………………………………………. 6-7
Option Pricing Model ………………………………………………………………………………………………. 7
Simulation of Stock Price …………………………………………………………………………………………. 8-9
Manage Symbol List ……………………………………………………………………………………………….. 9
Conclusion ……………………………………………………………………………………………………………… 9-10
VBA Source Code:
Sub Main() ……………………………………………………………………………………………………………… 11-12
Sub GetStockData …………………………………………………………………………………………………… 12-14
Sub Cleanup() …………………………………………………………………………………………………………. 14-15
Sub RemoveBlankCells() …………………………………………………………………………………………. 15-16
Sub FinalCleanup() ………………………………………………………………………………………………….. 16
Sub PorfolioOptimize() ……………………………………………………………………………………………. 16-20
Sub DeleteTabs() …………………………………………………………………………………………………….. 20-21
Sub BetaCalc()………………………………………………………………………………………………………… 21-25
Sub Simulate() ………………………………………………………………………………………………………… 25
Frm_Control (User Control Panel) …………………………………………………………………………….. 26-27
Frm Portfolio (Portfolio Optimization) ………………………………………………………………………. 27-31
Frm_Beta (Calculate Beta) ……………………………………………………………………………………….. 31-34
Frm_Options (Option Pricing) ………………………………………………………………………………….. 31-39
Frm_Simulate (Simulate Stock Price) ………………………………………………………………………… 39-41
Table of Contents (Cont.)
Worksheet ScreenShots:
Master Worksheet ……………………………………………………………………………………………………. 42
AllReturns Worksheet ……………………………………………………………………………………………… 43
BetaCalc Worksheet ………………………………………………………………………………………………… 44
Simulate Worksheet ………………………………………………………………………………………………… 45

Introduction/Description

The project consisted of implementing theories of finance into Excel using Visual Basic for Applications(VBA). The VBA components involved using Graphical User Interfaces (GUI) to capture the user’s preferences regarding various components of each financial model. The common thread in most of the models involved downloading stock data from Yahoo Finance. There were four financial models utilized and each one involved a different process to achieve the desired outcome. The four models in order were Portfolio Optimization, the Capital Asset Pricing and BETA calculation, the Option Pricing Model, and finally a simulation of a stock’s price over time.

Portfolio Optimization

This model involves optimizing any given portfolio between return and risk.

Capital Asset Pricing Model

The CAPM model involves itself with one part of a portfolio’s risk level.

Option Pricing Model

The Option Pricing Model involves pricing an option using either the Black-Scholes model (for European options) or the Binomial Option Pricing Model (used in American options). Both models involve an option exercise date sometime in the future, a risk-free rate, the current stock price, the expected return of the stock, and finally the type of option either a call or a put option.

Simulation of Stock Price

The Simulation of Stock Price model involves using random numbers that are normalized using the Lognormal distribution of stock prices.

Implementation of the Models: User Control Panel

The project begins with the first worksheet with a command button called Financial Analytics.

After pressing the Financial Analytics command button, the user would be taken to the first form called the User Control Panel.

This form allows the user to choose any of the options presented along with a brief description. The end date was generated using todays date but changing it if the date happened to fall on a Saturday or Sunday.

Portfolio Optimization

The user can select from two to 10 stocks and weights them to their preference. The program automatically weights them evenly and then allows the user to change the weights. If the user does fail to keep the total weights equal to 100%, the program automatically takes the difference between the total weights and 100% and distributes the difference evenly among the selected stocks enabling the total to always be 100%.

This screen shot shows the result displaying all ten stocks mean returns for each period and various statistics for the symbol’s average, variance, standard deviation, and the user selected weighting. .

Calculate Beta for CAPM Model

Here the user picks ten stocks and the program calculates the Beta using the Slope and Average worksheet functions.

Option Pricing Model

For the option pricing model,

Simulation of Stock Price

Once

Manage Symbol List

This

Conclusion

2

2

WhatsApp Chat with us on Whatsapp