Option pricing models and volatility using excel-vba cd files


First make sure that IQfeed is open. You can either download daily or intraday data. Note the period parameter. It can take any of the following values: QuantTools makes the process of managing and storing tick market data easy.

You just setup storage parameters and you are ready to go. The parameters are where, since what date and which symbols you would like to be stored. Any time you can add more symbols and if they are not present in a storage, QuantTools tries to get the data from specified start date.

The code below will save the data in the following directory: There is one sub folder by instrument and the data is aved in. You can also store data between specific dates.

In the example below, I first retrieve the data stored above, then select the first price observations and finally draw the chart. Two things to notice: You can refer to the Examples section on QuantTools website. Overall I find the package extremely useful and well documented. The only missing bit is the live feed between R and IQFeed which will make the package a real end to end solution. A few months ago a reader point me out this new way of connecting R and Excel.

At the time of writing the current version of BERT is 1. Ultimately I have a single Excel file gathering all the necessary tasks to manage my portfolio: In the next sections I present the prerequisite to developed such an approach and a step by step guide that explains how BERT could be used for simply passing data from R to Excel with minimal VBA code.

Once the installation has completed you should have a new Add-Ins menu in Excel with the buttons as shown below. This is what we want to retrieve in Excel. Save this in a file called myRCode. R any other name is fine in a directory of your choice. In this file paste the following code. Then save and close the file functions. Create and save a file called myFile. This is a macro-enabled file that you save in the directory of your choice. Once the file is saved close it.

Once the file is open, paste the below code. You should see something like this. Paste the code below in the newly created module. You should see something like the below appearing.

From my perspective the interest of such an approach is the ability to glue together R and Excel obviously but also to include via XML and batch pieces of code from Python, SQL and more.

This is exactly what I needed. Making the most of the out of sample data August 19, , 9: Then a comparison of the in and out of sample data help to decide whether the model is robust enough. This post aims at going a step further and provides a statistical method to decide whether the out of sample data is in line with what was created in sample. There is a non-parametric statistical test that does exactly this: Using the Kruskal-Wallis Test , we can decide whether the population distributions are identical without assuming them to follow the normal distribution.

It exists other tests of the same nature that could fit into that framework. Then I tested each in sample subset against the out of sample data and I recorded the p-values. This process creates not a single p-value for the Kruskall-Wallis test but a distribution making the analysis more robust.

As usual what is presented in this post is a toy example that only scratches the surface of the problem and should be tailored to individual needs. As usual with those things just a kind reminder: This is a very first version of the project so do not expect perfection but hopefully it will get better over time. Please report any comment, suggestion, bug etc… to: Doing quantitative research implies a lot of data crunching and one needs clean and reliable data to achieve this.

What is really needed is clean data that is easily accessible even without an internet connection. The most efficient way to do this for me has been to maintain a set of csv files. I have one csv file per instrument and each file is named after the instrument it contains. The reason I do so is twofold: Simple yet very efficient so far. The process is summarized in the chart below. In everything that follows, I assume that data is coming from Yahoo.

The code will have to be amended for data from Google, Quandl etc… In addition I present the process of updating daily price data. The code below is placed on a. Note that I added an output file updateLog. The process above is extremely simple because it only describes how to update daily price data.

Callable Range Accrual Notes. The Heston Model of Stochastic Volatility: Fast Option Pricing and Accurate Calibration. Pricing Callable Range Accrual Swaps.

Why Don't My Numbers Match? Next Generation Variance Derivatives. Best practices for VBA coding. Taming the Emerging Market Curve. Pricing floating rate notes FRNs with multiple payments per reset period. Competitive Advantage in Curve Building.