Supporting currency risk margin in VBA environment

OData support
Dr. Martinek Péter
Department of Electronics Technology

In my thesis I presented the framework of corporate risk management, how it is integrated into the corporate structure. Furthermore, I wrote about the most important risks that need to be solved such as operating, financial, law and environmental risks. I also presented the steps of the mature risk management.

Within the financial risk I highlighted and wrote in details about the foreign exchange risk, showing the possible ways to deal with these risks. The focus is on the derivatives in addition I also wrote about insurance. Moreover I gave a detailed overview about the potential markets for companies where they are able to purchase derivatives as well as what are the characteristics of these markets.

In the following chapter I wrote about the most important derivatives; futures and forwards, swaps, and options. In terms of derivatives the most important were the options in my work therefore I paid more attention on these products. Thus I depicted the participants of an option, what are the available positions that can be hold, how much profit can be earned (that can be either positive or negative). I emphasized the risk management related to this chapter.

The following part describes the evaluation of the options. I introduced the Black-Scholes and the Binomial Tree Pricing model in details. After the declaration of the former model’s assumptions I wrote about the share price movements’ Markov process. Based on the stochastic behaviour I deduced the equation of the share price movement. Henceforward based on the Wiener processes I examined trend of the share price change of the share’s derivative. Where it is possible I gave a detailed deduction otherwise I marked the source of the deduction. The deduction of the shortened version of Ito-lemma is included in the appendix.

Thereafter I demonstrated the binominal pricing model. I started the introduction with the one period binominal tree afterwards I continued with the more complex trees. I depicted the pricing methods as well as the structuring processes of the trees and the determination principals of the unknown parameters.

The next chapter introduces the option pricing model that was designed and developed by me. I tested the potential branches in the program, what relation can be maintained by the user. I also examined the alignment of the developed program with the company and the market structure. The program is able to price both European and American options and also can distinguish option that pay dividends and that do not. I also determine the parameters that must be provided by the user in order to get the correct results.

After the theory research following the previously mentioned logic I implemented the program using Visual Basic for Applications (VBA). I applied incremental development. I started with the basic (European, call, no dividend paid) option pricing following with the implementation of extra functions. The whole program is a so called State Machine, each of the statuses are depending on the data provided by the user.

After building the model I tested it in an extended sense. Without the opportunity to develop an automated test module I examined the functional units’ operation manually. The binominal trees, pricing, dividend payments, call and put options as well as European and American options were controlled if they work appropriately. I highlighted that because of the fact that big amount of data needed for the program it is not secured against errors. However it can be ensured that the program usually works with a small failure percentage.

In the following chapter by building share price scenarios I made the option pricing’s results useful. Comparing three different and occasional share price scenarios and a reference option with a forward the results show that options are able to prevent the company from the unexpected, significant fluctuation in the first place.

Finally, I wrote about the possible future developments of the program. Automation would filter the remained problems in the program. It is considerable to link the program with the trading systems so it would be able to conclude the transactions instantly.

It would also be a great option to make the program able to use single priced options in order to make a more complex position and therefore it would maximize the profit. These last two recommendations would prove more function than software used by risk management companies but I believe the potential is considerable.


Please sign in to download the files of this thesis.