Nov 112014
 

Hi,

In this post, I would like to present you the “Option Pricing n Greeks” in Jedox.

Well continuing on my previous post, I would like to highlight the potential of Jedox in terms of number crunching capacity, where I have designed the macro for option pricing most commonly used in any front desk team of an investment bank.

For all the background about options derivatives used in the financial industry and the measures (the Greeks) incorporated in this model references are as below:

http://www.investopedia.com/university/options/

http://www.investopedia.com/university/option-greeks/

http://en.wikipedia.org/wiki/Option_(finance)

http://en.wikipedia.org/wiki/Greeks_(finance)

This model in Jedox computes the option prices and its greeks based on the user inputs and as the benefits of Jedox come along together the possibilities are enormous.

CropperCapture[22]

Code:


function dOne($S, $X, $T, $r, $v, $d){
  $n1 = application()->Log($S / $X);
  $n2 = ($r - $d + 0.5 * pow($v , 2)) * $T;
  $d1 = $v * pow($T,2);
  $value = ($n1 + $n2) / $d1;
  return $value;
}

function NdOne($S, $X, $T, $r, $v, $d){
  $nd = dOne($S, $X, $T, $r, $v, $d);
  $n1 = -1* (pow($nd , 2) / 2);
  $d1 = application()->sqrt(2 * application()->Pi());
  $value = application()->Exp($n1) / $d1 ;
  return $value;
}

function dTwo($S, $X, $T, $r, $v, $d){
  $value = dOne($S, $X, $T, $r, $v, $d) - $v * application()->sqrt($T);
  return $value;
}

function NdTwo($S, $X, $T, $r, $v, $d){
  $value = application()->NormSDist(dTwo($S, $X, $T, $r, $v, $d));
  return $value;
}

function OptionPrice($OptionType, $S, $X, $T, $r, $v, $d) {
  if($OptionType == 'C'){
   $value = application()->Exp(-$d * $T) * $S * application()->NormSDist(dOne($S, $X, $T, $r, $v, $d)) - $X * application()->Exp(-$r * $T) * application()->NormSDist(dOne($S, $X, $T, $r, $v, $d) - $v * applica   tion()->sqrt($T));
  } elseif ($OptionType == 'P'){
   $m1 = application()->Exp(-1*$r * $T);
   $m2 = application()->NormSDist(-1 * dTwo($S, $X, $T, $r, $v, $d));
   $m3 = application()->Exp(-1 * $d * $T);
   $m4 = application()->NormSDist(-1 * dOne($S, $X, $T, $r, $v, $d));
   $s1 = $X * $m1 * $m2;
   $s2 = $S * $m3 * $m4;
    $value = $s1-$s2;
  }
  return $value;
}

function OptionDelta($OptionType, $S, $X, $T, $r, $v, $d){
  if($OptionType == 'C') {
   $value = application()->NormSDist(dOne($S, $X, $T, $r, $v, $d));
  }elseif ($OptionType == 'P'){
   $value = application()->NormSDist(dOne($S, $X, $T, $r, $v, $d)) - 1;
  }
  return $value;
}

function Gamma($S, $X, $T, $r, $v, $d){
   $value = NdOne($S, $X, $T, $r, $v, $d) / ($S * ($v * application()->sqrt($T)));
   return $value;
}

function Vega($S, $X, $T, $r, $v, $d){
  $value = 0.01 * $S * application()->sqrt($T) * NdOne($S, $X, $T, $r, $v, $d);
  return $value;
}

function OptionRho($OptionType, $S, $X, $T, $r, $v, $d){
  if($OptionType == 'C'){
   $value = 0.01 * $X * $T * Exp(-1 * $r * $T) * application()->NormSDist(dTwo($S, $X, $T, $r, $v, $d));
  }elseif ($OptionType == 'P'){
   $value = -0.01 * $X * $T * Exp(-1*$r * $T) * (1 - application()->NormSDist(dTwo($S, $X, $T, $r, $v, $d)));
  }
  return $value;
}

function OptionTheta($OptionType, $S, $X, $T, $r, $v, $d){
  if($OptionType == 'C'){
   $value = -(($S * $v * NdOne($S, $X, $T, $r, $v, $d)) / (2 * application()->sqrt($T)) - $r * $X * Exp(-1 * $r * ($T)) * NdTwo($S, $X, $T, $r, $v, $d)) / 365;
  }elseif ($OptionType == 'P'){
   $value = -(($S * $v * NdOne($S, $X, $T, $r, $v, $d)) / (2 * application()->sqrt($T)) + $r * $X * Exp(-1 * $r * ($T)) * (1 - NdTwo($S, $X, $T, $r, $v, $d))) / 365;
  }
  return $value;
}

Hope you like it.

Enjoy !!!

Downloads:

http://amolpandey.com/home/downloads/blog34_download.zip

Nov 112014
 

Hi,

This post is especially geared for the investment banking domain, but all are welcome on-board always :).

Embarking on this topic is more mathematically challenging as the crux of this topic is more geared to wards advance maths. But putting that aside for a while in this post I am trying to achieve which is an eye candy for any investment banker/trader requirement for any system they adopt.

In order to simulate the stock prices close to reality on any system (or lets say excel only) a normal random function can not achieve the same.

Why, just plot the “RAND” function for 50~100 points on any chart and see if it by any means represent’s  any stock price movement over the past few years.

CropperCapture[20]

The thing above lacks the “Drift” portion in the randomness, any stock price over the history is surly chaotic, but it has got some directional movement over the time, which the above chart lacks.

Thus to effectively simulate any stock price the mathematical model has to have a random/distortion and drift component to it. And this is where the complex mathematics start which I don’t want to delve  in this post. But here are the links for the interested readers to dig in more.

http://en.wikipedia.org/wiki/Brownian_motion

http://en.wikipedia.org/wiki/Wiener_process

http://en.wikipedia.org/wiki/It%C5%8D’s_lemma

(Ito’s Lemma has got some thing to with rocket science, enough to get you excited until to land up to its wiki page)

So in order to achieve a nice simulated stock price for our future financial models, just by using the native formulas, below is the spreadsheet to achieve the same.

CropperCapture[21]

The above looks more like a stock price movement (see the directional movement, in addition to the randomness), so go ahead and change some parameters and run your own stock simulation models.

So the big question ???

Why, do we need to simulate the stock prices ?? Ain’t the real data of the world is worth enough to run off our models…

Answer is NO, because this stock price generation system will lay the foundation of the future financial models I will introduce, to price complex derivatives like options, futures and its variant’s. To give you an insight using the above model foundation we will run internally thousands and millions of simulations of stocks prices to price the optimal price of the derivative. Remember my simulation strategy post (link). The concept is similar.

Hope it helps !!!

Enjoy !!!

Downloads:
http://amolpandey.com/home/downloads/blog33_download.zip

Nov 112014
 

Hi,

In this post I would like to share,  a technique which has been well rewarded to me in the past, is the extraction of foreign exchange rates from web into my financial models on demand.

Now some information in the world is never static (FX being one of them and the fastest moving information), and some times its not very fruitful to store the entire world data in your models/databases when you can get it on demand any time you need.  (Let experts of their field manage their data, remember my DRY “Dont Repeat Yourself” principle)

This technique has been critical in some of my past projects for the reason of multi currency/country financial reporting. Many businesses now a days are not limited to their continent, they are spread across the world earning’s in multiple currencies thus bring a different set of challenges with itself for conversion complexities and reporting.

Thus to address all the above problems, here you go with the solution:

CropperCapture[19]

The above Jedox spreadsheet downloads the data for the selected currency against USD for the user selected time frames and renders a chart out of it. The above spreadsheet can be amended to download the latest data itself only, for accounting representation of into multiple currencies.

(Note: The data downloaded in the above spreadsheet, is dumped on the “data” sheet)

This is a reference technique only for illustration purposes, which can be amened to incorporate multiple data sources providing data sets like  historical stock prices, macro economic data, custom web extractions, and similar. But ensure that the source is reliable and established in terms of their reporting format so that your model/extraction code continues to work depending upon the source.

Hope it helps !!!

Enjoy !!!

Downloads

http://amolpandey.com/home/downloads/blog32_download.zip

Nov 092014
 

Hi,

Today in this post, I would like to share an simulation strategy using Jedox spreadsheets, where business users can design models via formulas and upon which simulations can run, building upon the case list for statistical insight.

Background:

Many times in real-world scenario, the clients of any business have a profile (meaning they have their choices, preferences, and a business can have multiple types of different profiled client), on which the business has to price products for optimal profit.

Now some times in retailing the bundling of products is also required to attract more customers. So for further taking this post, I have build a case study for an Indian takeaway business where the owner can profile his diners and design bundled menus for optimal profit.

Model Question:

With my existing Indian take away menu, and the following customer profile :

Couple

Type: Vegetarian

Categories Opted: Entrée (Any), Two different main course dish, rice, bread and dessert

With all the possible combinations of my menu has to offer, to the above diner experience.

Which combination of items on the menu will give me the  Maximum, Minimum and Average  diner spending.

Model Answer:

The model in the post runs a 1000 combinations of all the random items with the relevant filter to generate the diner spending and archives the information. Upon which the statistics on the generated list is searched for the required insights.

CropperCapture[17]

Notes: In the workbook, there is a simulation worksheet, which is actually not necessary as for larger list of combination generation it would dramatically slow down the system and its not actually required by the user, so in the long term for such simulated models I would stored the data in an array itself only and dump the evaluated statics on that array, thus saving the workbook size and opening the doors for larger iteration simulations.

Also higher the count of simulations, more accurate your model results.

In this post I have used a very simple simulation model to exhibit the basics for the simulation strategy, but this can open doors to more complex evaluations of Monte Carlo Simulations and similar for option pricing and more, which I will plan to come up soon as well.

http://en.wikipedia.org/wiki/Monte_Carlo_method

http://en.wikipedia.org/wiki/Monte_Carlo_methods_for_option_pricing

Hope it helps !!!

Enjoy !!!

Downloads:

http://amolpandey.com/home/downloads/blog31_download.zip

Nov 092014
 

Hi,

In another series of my post, today, I would like to introduce you to the power of document management system and how it can greatly simplify the office workflow processes and document management in any enterprise.

And to add, how it can be easily integrated in your Jedox portal to add-on the power to your Jedox usage experience.

Q- So what is Document Management System, what are its features and benefits ?

A- One of the big questions revolve around any system (IT) to be incorporated in the main stream business, is it really worth investing the money, time, labour to achieve the benefits. And what are the benefits ?

Well in this case of incorporating any document management system, the benefits are enormous: To name few document version, security, template generation, preview (if your system offers), collaboration,  auditing and more.

So being a developer, I like to follow a principle famous in the community DRY (Don’t repeat yourself). So in order to be efficient in this post for answering the above question, I providing some useful hyper links for viewers to gain the good insight into the feature’s of the product/system has to offer !!!

Links:

http://en.wikipedia.org/wiki/Document_management_system

Q- How would the Document Management System, integrate and add value with my existing Jedox installation ?

A- Now on this question I would like to elaborate in detail in this post and give more insight as the added value and benefits realized when document management system is incorporated along with your organisation BI platform.

Background

Every Project/Task has a Paper Work: In any organisation, when dealt with any Projects which can include financial planning, invoicing, reports,  approvals  and much more.

Some things can be a system in itself, like approvals (or similar) can be built in another system like SharePoint forms, or custom HTML forms to eliminate the custom document/emails and stream  line the process for more clarity.

But not every thing can be built out, no matter how simplified the form creation is, the office documents (in the form of Word, Excel, Outlook emails) makes their way in to fully efficient business system.

Some of the examples, are like organisations using email for approvals, reports stored on shared drives, custom word document with the first page dedicated to the a massive table listing the authors, and anonymous excel file all over the place…

Jedox Dimensions Drive Document Space: In many Jedox implementations, I have realised the of document generated by the business in the regular operation are usually categorised by the combination of Jedox dimension values

Example: For “Scenario” (Forecast_01 , Dimension)  the “Department” (Sales , Dimension) for the “Year” (2012 , Dimension) the “Sales Report” (this is the document)

In the above conversation statement, the user has pronounced the double quoted dimension values identifying the document.

Security: In many organisations, still people use the shared folders to achieve the collaboration of documents across teams and individuals, via the file/folder permissions. Its good while it last unless things get little out of hand and disaster strikes. File is readable by all users of the organisation, because IT revised permissions of all folders, or hierarchy update of permissions are few to blame.

Standardization: I am the approval manager, and users send me documents of whole wide variety of colours, fonts, shapes…, driving me mad to the find the relevant information I am looking for approval and permission.

System

For the above background, I have evaluated multiple systems, with one to name in this post attracting my attention with its great features is “OpenKM”. I found this document management system, very good and I was very impressed by its features provided, much added benefits even with its paid competitors and clarity of usage and clean interface.

http://www.openkm.com/en/

http://wiki.openkm.com/index.php/Main_Page

Jedox Integration:

Picture speaks a thousand word !!!

CropperCapture[16]

There are a great set of API with OpenKM web service calls whose structure is in-line with Jedox, to integrate well with custom PHP scripts based on the needs.

Integration scenario: The OpenKM roles/groups can be matched with the Jedox and security protected directory structure can be generated based on dimensional elements via scripts where the the permissions are driven by Jedox (regular script sync) for providing the user their space for their document storage pertaining to their dimensional area (like forecasting, based on project name)  operations, and can be shared on demand across users for efficient collaboration and notifications.

The integration between a Jedox model and OpenKM is purely business specific requirements based hence generic tool or utility wouldn’t be of much use, but in this post I am trying to highlight the added benefits of a document management system in relation to Jedox for bringing more organisation and features to the business process leveraged by Jedox.

Hope it helps !!!