Search for a File in Windows DOS

Recently I’ve found myself digging through folders and subfolders looking for a file that I only remembered 1-2 words of the file name. Using the below command has proven very useful for doing this in windows command line. The following example assumes we are looking for a file that has the string “Equity” in the name:

    dir *Equity* /s

The “/s” command instructs the search to include all subfolders (of the current directory). It is also useful to know what type of file you are looking for, in which case specifying the file type will help narrow the search. The following example assumes that we are looking for an excel file that includes the string “Equity” in the name:

    dir *Equity*.xlsx /s

I hope you find this as useful as I did!

Advertisements

Excel (VBA) Changing System Date

While using some inherited VBA code from a colleague, I noticed that each time I ran the code the system date on my machine got changed. After digging through the code I discovered that the “Date” variable is apparently linked to the system date, and will in fact change the date on your machine if you assign it a value. I find it a bit scary that Microsoft makes it so easy to change the system date within some VBA code, especially considering that this is sometimes used for authentication purposes (think back to when we used to manipulate the date so that a trial version of some program didn’t expire).

If you want to try this for yourself, run the following macro:


    Sub ChangeDate()
        Date = #1/1/2014#
    End Sub

 

Date to Quarter Conversion – Excel Formula

Here is a formula that I find myself using on (almost) a daily basis, so I figured it is likely useful to others. If you have a date in cell A2 and want to convert the date to its respective year and quarter, e.g. 1/1/2013 to 13Q1, I use the following:


=right(year(a2),2)&"Q"&ceiling(month(a2)/3,1)

Of course, the above formula is meant for excel but it can be easily adapted to any other languages/applications.

What is Brownian Motion?

BM Sample PAth

One of the first concepts that we learn about when learning about modeling stock prices or interest rates is Brownian Motion, which is conceptually similar to a random walk. However, Brownian Motion (BM) is a continuous process, and at every moment in time takes on a random value. The technical definition is as follows:

Let (\Omega, \mathcal{F}, P) be a probability space. For every \omega \in \Omega , if B(t) is a continuous process for t \ge 0 such that

    • B(0) = 0
    • On a given interval t_i \in [0, T]  \forall i = 0:m , the increments B(t_1) - B(t_0), B(t_2) - B(t_1), ... B(t_m) - B(t_{m-1}) are independent.
    • For s,t \ge 0 , B(t) - B(s) \sim N(0, t-s) , where N(0, t-s)  is a normal random variable with mean 0 and variance t-s.

then B(t) is a Brownian Motion.

The last property tells very important information about the path that BM takes – over a given time-step, BM can take on any positive or negative value, and it can technically move a huge amount at any given moment, but this would happen with a very low probability (due to the normal behavior); this movement leads to an interesting fact – BM will almost surely (i.e. with probability 1) hit any given value, but the expected waiting time until it hits a given value is infinite.

Some basic properties of BM include

  • The expected value of B(t) is equal to zero.
  • The variance of B(t) is equal to t .

Many models (e.g. Black-Scholes Model) assume that the stock follows a geometric brownian motion, which in simple terms means that the process is along the lines of V(t) = e^{\sigma B(t) + \mu t} . The key fact is that this process is always positive (because of the exponential behavior).

 

Newton’s Method and Implied Volatility

There are many scenarios where one wants to find the roots of an algebraic expression (e.g. calculating the implied volatility of an option). However, sometimes it is very difficult (if not impossible) to find a closed form solution to a given expression, e.g. solve for x: 100 = 1.5e^{-0.5x} + 1.5e^{-x} + 1.5e^{-1.5x} + 101.5e^{-2x}. This is where iterative methods such as Newton’s method come in.

An iterative method is simply a method that begins with a fixed value, and by plugging that fixed value into the method, we are able to calculate the next value. Doing this over and over again, the method converges to the solution to the equation. Hence the name iterative method.

Newton’s method, is one such method, and works the following way: suppose we have some function f(x) and we are trying to find x^* such that f(x^*) = 0 (i.e. we want to find the root of the function). We can start with some guess x_0 , draw the tangent line to the curve at the point x_0. Now, where the tangent line crosses the x-axis is our next guess x_1 , and we repeat (see the animation for a depiction of how this converges to a root).

The equation of Newton’s method is: x_n = x_{n-1} - \frac{f(x_{n-1})}{f'(x_{n-1})} .

Animation of Newton method

Animation of Newton method (Photo credit: Wikipedia)

It is easy to see that one obvious downfall of Newton’s method is that you must be able to differentiate the function that you are using. One way to get around this would be to use the Secant method which works very similarly to Newton’s method, but does not require the function to be differentiable.

In order to implement Newton’s method, we need an initial guess (which is of course dependent on the function) and a given tolerance that we should stop within (since this is a convergent method, it would need to loop infinitely for an exact solution).

double newtons_method(double x_0, double tol){
    // It is assumed that f() and f_prime() are
    // functions that are elsewhere declared

    // declare and initiate parameters
    double x_new = x_0;
    double x_old = x_0 - 1;

    // now we perform the iterative Newton's method
    do{
        x_old = x_new; // store the old x value
        x_new = x_old - f(x_old)/f_prime(x_old); // calculate the new x value
    }while(fabs(x_old - x_new) > tol); // loop until within the given tolerance

    return x_new;
}

Using the above code with an implemented Black-Scholes model, we would be able to calculate the implied volatility of an option by setting the function f(x) = V_{BS} - V_{Market}, where V_{BS} is the BS value of the option for a given volatility \sigma, V_{Market} is the market price of the option, and f'(x) refers to the Vega of the option.

How to Install iPython on Mac OSX

Python is a very enjoyable language to learn and use for programming. I particularly like it because it can be used as both an object oriented language and a scripting language simultaneously, which makes learning how to code in python that much more fun. To learn the basics of python take a look at LearnToCodeTheHardWay.

English: Python logo Deutsch: Python Logo

English: Python logo Deutsch: Python Logo (Photo credit: Wikipedia)

Mac OSX has Python pre-built, therefore, you can actually try it out without needing to install anything right away. Simply open Terminal (Applications -> Utilities -> Terminal) and type in “python” and you will be able to immediately use python as a scripting language.

iPython allows for a bit more utility and information when scripting (e.g. autocomplete, etc.), which makes life a bit easier and that is why I like to use it. For more information on iPython take a look at their site. Installing it is quick and easy:

  1. Download Anaconda from here. In my case the file was called “Anaconda-1.5.1-MacOSX-x86_64.sh”
  2. Install Anaconda by opening terminal, moving to the directory that your download file is in (e.g. ~/Downloads) and entering the following
 bash Anaconda-1.5.1-MacOSX-x86_64.sh 

And you’re done! Now simply exit terminal and open it up again, type in “ipython” and your iPython environment will open.

 

P.s. If you would like to update iPython to the most recent version, simply enter the following two lines in terminal:


conda update conda

conda update ipython

 

How to Calculate Volatility

As I mentioned in my previous post, “What is Volatility?“, it is not particularly straightforward to calculate implied or historical volatility, but hopefully after reading this post you will understand the basic idea.

Historical Volatility

Here, I will explain how to calculate the historical volatility of a stock. In order to calculate historical volatility, you will need historical stock prices (can get these from places like finance.yahoo.com), and it would be easiest to do this in a spreadsheet environment like Excel. So, suppose we have a year of daily stock prices.

  1. Calculate the daily returns
  2. Find the average of the daily returns, call this value \bar R
  3. Subtract \bar R from each of the daily return values (i.e. calculate R_n - \bar R)
  4. Square each of the values calculated in step 3 (i.e. calculate (R_n - \bar R)^2 )
  5. Sum the squared values from step 4
  6. Divide the sum by (N-1)
  7. Take the square root of the value in step 6. This is the historical (daily) volatility.
  8. If you want to annualize the volatility, simply multiply it by \sqrt{252} , or any other respective value (weekly, monthly, etc.)

Or in mathematical notation, \sigma_{hist}=\sqrt{\frac{1}{N-1} \sum_{n = 0}^{N-1}(R_n - \bar R)^2}, where \bar R = \frac{1}{N-1} \sum_{n=0}^{N-1} R_n .

Implied Volatility

In order to calculate the implied volatility of a derivative, you will first need a few things

  1. Implementation of an option pricing model (e.g. Bl = ack-Scholes-Merton model), which can be done in Excel (here is a sample spreadsheet: Black-Scholes Formula Workbook)
  2. A non-linear solver (e.g. Excel’s built in solver)
  3. An option with all of the information (besides volatility of course). In the worksheet example I used the following: K = 90, S = 90, T = 1, r = 3%, q = 1%, Market Price = 10.

To calculate the implied volatility we simply input all of the information into the model, with an initial guess at the volatility (e.g. 0.3). Then, using Excel’s solver we can find the volatility that matches the market price of the option, which in this case works out to be about 26%.