Charting in R - Part I

The toughest step to doing things like quantitative analysis for trading and backtesting is to understand how to use the tools available in the market. The choices available are as follows:

1. Excel
2. R
3. Python
4.  C/C++

Excel allows you to see and manipulate data easily but you cannot see trend properly. For example, if you wish to chart data in Excel, this is what you will get:

This graph looks acceptable for simple presentation. But if you want to do analysis, you would want to be able to add a complex indicator easily. In Excel, you can only do that by manipulate all the data in excel and plot another graph on it. To do complex analysis of data it will be much better to do it with scripting API available in the market. If you know your stuff, you can do things pretty fast. But it takes a hell lot of effort to even get started. To illustrate the advantage of using API, the following is created using GOOGLE prices and R.

As someone who had tried both Python and R, my opinion is that the later is a better tool to do charting. So I documented some tips to get you started on R.

What you need to install
R(Here you will find the latest version but I am using version 2.15)
Quantmod (To quickly install, follow instruction here to download and install all that you need)

Once you are done, you can quickly load data with the following sample code (modified from code taken from here) and data.

Code:

library(quantmod)
require (zoo)
dataFile_strip2 <- "testinput.csv"
dataDir <- "C:/Users/Ken/Documents/R/CSV Data"
file2 <- paste(dataDir,"/",dataFile_strip2,sep="")
zz <- read.zoo(file2,header = TRUE,sep=",",index.column=1, format = "%d/%m/%Y %H:%M")
xx<- as.xts(zz)
chartSeries(xx)


testinput.csv:
DateTime,Close,High,Low,Open,Volume
12/08/2015 21:30:00,112.53,112.61,112.5,112.5,877727

Other Reference:
https://cran.r-project.org/web/packages/zoo/vignettes/zoo-read.pdf

Using C++ and VBA in Excel - Part II

One way of doing this is as stated in my previous post. But many beginner in C++ would be daunted by the amount of basics in Computing required to understand why it work. To simplify this process, we can use XLW to implement wrapper easily. The only requirement is to know basic C++ and a simple 3 step process.

Step 1
First, you will need Microsoft Visual Studio and install XLW onto your PC. Use Template Extractor:

Choose the Visual Studio version correctly. Note that if you are using Visual Studio 2012 you will need to choose VC10 instead of VC12.

Step 2
The template you just created is a skeleton VS project which allow you to develop XLL. Once you fire up template.sln, you will notice that the project is setup nicely for you to start development.

As an example, I did a test function that return the square of a number:

As usual, for every function you create in C++ you will need to declare it in the header. But this time, you will need to declare it onto cppinterface.h. This is the most important step you need to do in C++ and you are ready to compile the project.

Step 3
The compilation will create the XLL file and you will be able to find it in the output folder. In this case, I am using debug mode and "Template.xll" will therefore appear in the debug folder. Launch "Template.xll" and you should not encounter any error, if so then there must be an error in the compilation process. If there are no error then you can create a new worksheet and start using the "helloworld" function you just created.