Example 1 – European Option Pricing with Monte Carlo Simulation

This is the first hands on example. In this finance example we price a European Option using Monte Carlo Simulation(*) The workbook contains two worksheets. The ‘Results’ worksheet contains the input to the simulation method and the second one contains the data generated from the simulation method.

There is only one sheet in the workbook which contains both, input and output.

 

DOWNLOAD FILE HERE: European Option Pricing with Monte Carlo Simulation

 

(*) The example is done based on my own experience and knowledge and should not be taken to be correct 100%.

Lesson 4 – More on Ranges

After the brief introduction on lesson 3 regarding ranges, this lesson is an attempt to take it a bit further and discuss a bit more details on ranges with a hands on example. The example of this workbook is a table where each column corresponds to a specific month and each row corresponds to a client. What we want to do in this worksheet is to select a client from the drop down list (green cell) and find the first month that he made a transaction and the value of it. For example for Client2 the red cells should display (February, £8,508.24), for Client8 should display (December, £6,362.22) etc.

In this table I have defined two named ranges, the one is the client list (“ClientList”) which corresponds to the yellow cells and the date list (“DateList”) which are the blue cells.

In order to calculate the First Deposit Date and Value (red cells) I have used events in vba code which basically identify if a change has been done in the green coloured client cell. Events however are not the purpose of this chapter so they will be discussed in a following lesson.

So let’s see how this task is achieved. To be consistent with practises in previous lessons, I have added a module and gave a meaningful name to the worksheet object (shtTransactions) as per the screenshot below:

To briefly explain how I calculate the result every time the selected client is changing, excel has ‘Events’. An event can be a workbook open or close, a selection change, a value change etc. In my particular case I am interested in the Worksheet_Change event. The idea is that when this event is fired a piece of code is executed. To add code in this event, I doubled click in VBE the ‘shtTransactions’ and selected worksheet from the first dropdown menu and change for the event I am interested

The parameter (‘Target’) in this sub procedure is a range variable which corresponds to the range in the workbook where the change has occurred. i.e. if I change the value of the client drop down menu (green cell) then my ‘Target’ is the selected client (green cell). I then use an if statement and the Intersect(explained further below) method to check if the change has occurred in the range that I am interested. If this is true, then I call the sFindFirstTransactionData sub proc to calculate the first transaction data for the selected client.

In the main sub proc now, what we do is that we firstly parse the client name that we want to find data from. Then we define two ranges, one for the client list and one for the date (yellow and blue cells in screenshot above). In the outer For Each I loop through the clients trying to find the selected one. Once the selected client is found I then loop through the date list. Then I make use of the Intersect() method (one of my favourites in vba – simple and powerful as it can fit many purposes.) which basically give you the rectangular common part of two ranges. In my case is going to provide me with the transaction value for a particular client for a particular month. So in the code for each intersection I am checking if the cells is blank or not. If it is not blank for the date I am checking it means that the month I am looking for is found and the intersection will provide me with the value. I then put these two values in the red cells.

The Intersection method can be visually presented as below with the purple cell being the intersection of the two bright green ranges, the horizontal and vertical lines.

Thanks for reading!

Excel 2010 has been used for this Lesson. The excel file of the lesson can be sent to you on request(contact page for details).

Lesson 3 – Range in VBA

One of the most common objects that you will utilize when writing code in vba is the Range Object. Whatever cells you can select in the worksheet you can do it in vba as well. It could be a single cell, a column, an entire row or blocks of cells. Let’s see below some examples of how we can make use of the Range object (* Note that you can’t have a range spanning across many worksheets, a range object can only refer cells in one worksheet.)

In the sample code below i used the Set keyword to assign my ‘testRange’ variable because the Range variable is an object and needs an object reference.

Option Explicit Public Sub sRangesInVba() Dim testRange As Range  ‘Single cell Set testRange = shtRangeInVba.Range(“A1”) ‘Rectangular area Set testRange = shtRangeInVba.Range(“B1:C10”) Set testRange = shtRangeInVba.Range(Cells(1, 2), Cells(10, 3)) ‘Many blocks of cells Set testRange = shtRangeInVba.Range(“A1,C3,E5:F15”) ‘Entire column Set testRange = shtRangeInVba.Range(“B:B”) ‘Or row Set testRange = shtRangeInVba.Range(“3:3”) ‘Using a Named Range Set testRange = shtRangeInVba.Range(“CustomerList”) ‘Using another Range – not really my favorite way to get a range Set testRange = shtRangeInVba.Range(Range(“A1”), Range(“B2”)) End Sub

Excel 2010 has been used for this Lesson. The excel file of the lesson can be sent to you on request(contact page for details).

 

Lesson 2 – Naming and Coding in VBA, basic guidelines

In this lesson, I am trying to introduce a naming convention as is gathered from my professional experience. It is not necessarily correct but it is a personal style which I find easy to understand and efficient to use. But firstly, here are my general rules.

• Use option explicit in your modules. This should prevent you from making spelling mistakes and making your code crush at runtime.
• Give meaningful names to your variables.
  • If you have a for-loop that counts some entity occurrence you could perhaps name your variable as ‘entityCounter’ instead of plain ‘c’. You could use the i,j,… convention when looping through arrays and as such i,j,… are used for indexing.
  • If you have a variable that holds a client’s name then ‘clientName’ is a better option than just ‘client’.
  • I found that naming your variables like, dPrice, iAge, bRegistered where d, i, b stands for double, integer, boolean respectively helps a bit to understand large chunks of code easier as I know the type of variable without need to scroll up to the start of the proc/func.
  • I name my modules as ‘mMyModuleName’, my sub procedures as ‘sMySubProcedure’ my user defined functions as ‘fMyFunction’, my worksheets as ‘shtMyWorksheet’. This helps me to identify easily what exactly is my object without the need to go and see the code or properties.
• My tab space preference is 2 spaces – that prevents me from squeezing the code on the right hand side of the screen in procs/function that have a lot of nesting (you can set this in VBE in Tools->Options). Use a tab, inside an if, select, with, loop statement.
• When I declare (Dim) variables I try to group variables of the same context together.
• When trying to get data from worksheet in your code, use a named range and avoid referencing the address.. Doing otherwise makes your code difficult to maintainable and prone to mistakes. Ideally no range in your vba should be referenced by A1 type of notation.
• Public VS Private. Make public all your subProcs/functions that you want to call from your workbook. Make private functions that are used only by the code(*). On workbooks with a large number of procedures you want to be able to see (from the workbook) those that make sense to be called and not scrolling through an endless list.

The sample worksheet below demonstrates to some extent the above guidelines. The example doesn’t have a use in real life however, in the code I try to process some data for a particular client and sum the total amount of transactions. Simple.

You can see below how the main worksheet looks like. I then attempt to pass some details into the vba code and do some basic calculations.

(*) See how the worksheet name has been renamed from what it was Sheet1 (Excel default name) to shtClientDetails

Main subProc code:

And two Functions

 

Excel 2010 has been used for this Lesson. The excel file of the lesson can be sent to you on request(contact page for details).

Lesson 1 – Introduction

Objectives:

• Familiarize yourself the VBA Editor
• Write a user defined function

In this lesson we will create an excel spreadsheet and write one user defined function in VBA and then we will use that function in the spreadsheet.

The spreadsheet looks this:

My user defined function is the one that is used to calculate Profit(AfterTax) column. In order to write my user defined function I need to open the VBA editor. I can do this by pressing ALT+F11 and the editor should look like this:

If you cannot see the properties window (bottom left corner) then press F4 or Select View->Properties Window from the menu.

Then in the project window right click on the project and click on Insert->Module. In the properties window then change the name of the module to mFunctions.

Now that we have the module, we can write the code of the user defined function:

The function performs a very simple task, check the band that the profit falls in and calculates the profit after tax with the correct rate. This is just a simplified example trying to demonstrate how to write a function and not how to calculate tax.

The name of the function is fProfitAfterTax and it can be then used in the spreadsheet as follows. I have also used named ranges which I explain below:

My function takes four arguments: profit, rate1, rate2 and band and calculates the profit after tax. You can see than instead of writing 12% or referencing the cell as I3, I have renamed the cell I3 as rate1 and whenever I want to use that tax rate I can type rate1 instead (same for rate2)

To rename a cell (or a range of cells) you select your range and type the desired name in the name box. Simple enough. Then you can manage all the names you declared in the Name Manager (you can find it in the formulas tab in the Excel Ribbon)

This short lesson showed you how to write a simple user defined function in the VBA Editor and how to use it in your Excel spreadsheet. Of course there are many more things to learn, this is barely the beginning and the purpose of this lesson was to demonstrate where the VBA code lies in an Excel application, in the VBA editor and how it can be accessed later in Excel.

Excel 2007 has been used for this Lesson. The excel file of the lesson can be sent to you on request(contact page for details).

Welcome to my Excel VBA blog

I have been using Excel VBA for a while now in my job. Excel with VBA is a powerful application, from the perspective that you can develop complex applications very quickly. It has a downside as well, it’s performance (of course there are always ways to optimize the code) .

Developing Excel application with VBA is very easy but sometimes you need that piece of detail that you don’t know or dont rememeber and you have to search for it. So my target is to make this blog a place that you can find those details through a series of lessons. I will try to publish lessons frequently and as soon as I find something interesting. I will try to provide as much details and explanations through small examples.

I hope that you will find this blog useful. Enjoy the lessons 😉