Archive

Archive for April, 2012

Intraday Backtest

I came across a free source of Intraday Forex data while reading Forex Trading with R : Part 1 post. You can download either Daily or Hourly historical Forex data from the FXHISTORICALDATA.COM.

The outline of this post:

  • Download and Import Forex data
  • Reference and Plot Intraday data
  • Daily Backtest
  • Intraday Backtest

First,I created a getSymbols.fxhistoricaldata() function in data.r at github to download Forex data from the FXHISTORICALDATA.COM. Following code loads ‘EURUSD’ hourly data and creates a candle chart for 2012:03:06 from 10 to 21 hours using the Systematic Investor Toolbox:

###############################################################################
# Load Systematic Investor Toolbox (SIT)
# http://systematicinvestor.wordpress.com/systematic-investor-toolbox/
###############################################################################
con = gzcon(url('http://www.systematicportfolio.com/sit.gz', 'rb'))
    source(con)
close(con)

	#*****************************************************************
	# Load historical data
	#****************************************************************** 
	load.packages('quantmod')	

	EURUSD = getSymbols.fxhistoricaldata('EURUSD', 'hour', auto.assign = F, download=T)
	SPY = getSymbols('SPY', src = 'yahoo', from = '1980-01-01', auto.assign = F)
		
	#*****************************************************************
	# Reference intraday period
	#****************************************************************** 
	plota(EURUSD['2012:03:06 10::2012:03:06 21'], type='candle', main='EURUSD on 2012:03:06 from 10 to 21')

We can reference Intraday data by specifying hours, minutes, and seconds in addition to year, month, and day. I.e. YYYY:MM:DD HH:MM:SS

Next, let’s combine Daily price for SPY and Intraday quotes for EURUSD in one chart.

	#*****************************************************************
	# Plot hourly and daily prices on the same chart
	#****************************************************************** 	
	# two Y axis plot	
	dates= '2012:01:01::2012:01:11'
	y = SPY[dates]
	plota(y, type = 'candle', LeftMargin=3)
			
	y = EURUSD[dates]
	plota2Y(y, ylim = range(OHLC(y), na.rm=T), las=1, col='red', col.axis = 'red')
		plota.ohlc(y, col=plota.candle.col(y))
	plota.legend('SPY(rhs),EURUSD(lhs)', 'black,red', list(SPY[dates],EURUSD[dates]))

Next, let’s create a Daily Backtest by investing into Currency Majors: EUR/USD, USD/JPY, GBP/USD, AUD/USD, USD/CHF and USD/CAD. I will investigate an Equal Weight portfolio and Timing portfolio as explained in the A Quantitative Approach to Tactical Asset Allocation by M. Faber (2006) paper.

	#*****************************************************************
	# Universe: Currency Majors
	# http://en.wikipedia.org/wiki/Currency_pair	
	#****************************************************************** 
	tickers = spl('EURUSD,USDJPY,GBPUSD,AUDUSD,USDCHF,USDCAD')
	
	#*****************************************************************
	# Daily Backtest
	#****************************************************************** 
	data <- new.env()
	getSymbols.fxhistoricaldata(tickers, 'day', data, download=T)
	bt.prep(data, align='remove.na', dates='1990::')
	
	prices = data$prices   
	n = len(tickers)  
	models = list()
	
	# Equal Weight
	data$weight[] = NA
		data$weight[] = ntop(prices, n)
	models$equal.weight = bt.run.share(data, clean.signal=F)
	
	# Timing by M. Faber
	sma = bt.apply.matrix(prices, SMA, 200)
	data$weight[] = NA
		data$weight[] = ntop(prices, n) * (prices > sma)
	models$timing = bt.run.share(data, clean.signal=F)
	
	# Report
	models = rev(models)
	plotbt.custom.report.part1(models)
	plotbt.custom.report.part2(models)

Next, let’s create an Intraday Backtest using the same strategies.

	#*****************************************************************
	# Intraday Backtest
	#****************************************************************** 
	data <- new.env()	
	getSymbols.fxhistoricaldata(tickers, 'hour', data, download=T)	
	bt.prep(data, align='remove.na', dates='1990::')
	
	prices = data$prices   
	n = len(tickers)  
	models = list()
	
	# Equal Weight
	data$weight[] = NA
		data$weight[] = ntop(prices, n)
	models$equal.weight = bt.run.share(data, clean.signal=F)
	
	# Timing by M. Faber
	sma = bt.apply.matrix(prices, SMA, 200)
	data$weight[] = NA
		data$weight[] = ntop(prices, n) * (prices > sma)
	models$timing = bt.run.share(data, clean.signal=F)
	
	# Report
	models = rev(models)
	plotbt.custom.report.part1(models)
	plotbt.custom.report.part2(models)	

Please note that the code for the Daily Backtest and Intraday Backtest is identical except for the line where we download the data:

  • Daily Backtest:
    getSymbols.fxhistoricaldata(tickers, 'day', data, download=T)
  • Intraday Backtest:
    getSymbols.fxhistoricaldata(tickers, 'hour', data, download=T)

It is easy to work with Intraday data and it is easy to create Intraday Backtest, right?

To view the complete source code for this example, please have a look at the bt.intraday.test() function in bt.test.r at github.

R/Finance Conference

April 21, 2012 1 comment

I will be presenting at the R/Finance conference in Chicago on May 11-12. Details of the conference are available here: www.rinfinance.com

See you there!

Categories: Uncategorized

Borrowing Ideas from Timely Portfolio

April 16, 2012 1 comment

I want to highlight two great Visualization techniques I discovered by reading the fine blog from Timely Portfolio.

First method is based on the lm System on Nikkei with New Chart. Let’s visualize Strategy’s Long/Short/Not Invested periods by highlighting the underlying (i.e. buy & hold) with green/red/gray. Following is a sample code that implements this idea using Systematic Investor Toolbox.

###############################################################################
# Load Systematic Investor Toolbox (SIT)
# http://systematicinvestor.wordpress.com/systematic-investor-toolbox/
###############################################################################
con = gzcon(url('http://www.systematicportfolio.com/sit.gz', 'rb'))
    source(con)
close(con)

	#*****************************************************************
	# Load historical data
	#****************************************************************** 
	load.packages('quantmod')	
	tickers = spl('SPY')

	data <- new.env()
	getSymbols(tickers, src = 'yahoo', from = '1970-01-01', env = data, auto.assign = T)
		for(i in ls(data)) data[[i]] = adjustOHLC(data[[i]], use.Adjusted=T)		
	bt.prep(data, align='keep.all', dates='2000::2011')

	#*****************************************************************
	# Code Strategies
	#****************************************************************** 
	prices = data$prices    
	
	# Buy & Hold	
	data$weight[] = 1
	buy.hold = bt.run(data)	
	
	# Strategy
	ma10 = bt.apply.matrix(prices, EMA, 10)
	ma50 = bt.apply.matrix(prices, EMA, 50)
	ma200 = bt.apply.matrix(prices, EMA, 200)
	data$weight[] = NA;
		data$weight[] = iif(ma10 > ma50 & ma50 > ma200, 1, 
				iif(ma10 < ma50 & ma50 < ma200, -1, 0))
	strategy = bt.run.share(data, clean.signal=F)	
	
	#*****************************************************************
	# Visualization of system Entry and Exit based on
	# http://timelyportfolio.blogspot.ca/2011/08/lm-system-on-nikkei-with-new-chart.html
	#****************************************************************** 	
	layout(1)
	plota(strategy$eq, type='l', ylim=range(buy.hold$eq,strategy$eq))
	
	col = iif(strategy$weight > 0, 'green', iif(strategy$weight < 0, 'red', 'gray'))
	plota.lines(buy.hold$eq, type='l', col=col)			
		plota.legend('strategy,Long,Short,Not Invested','black,green,red,gray')		

We can visually attribute from the plot periods when Strategy is Long/Short/Not Invested.

Another method is based on the Drawdown Visualization . Let’s visualize Drawdowns by highlighting periods when drawdown is large than 10% with yellow and larger than 15% with orange.

	#*****************************************************************	
	# Drawdown Visualization 
	# 10% drawdowns in yellow and 15% drawdowns in orange
	# http://timelyportfolio.blogspot.ca/2011/08/drawdown-visualization.html
	#*****************************************************************	
	layout(1:2)
	drawdowns = compute.drawdown(strategy$eq)
	highlight = drawdowns < -0.1
	
	plota.control$col.x.highlight = iif(drawdowns < -0.15, 'orange', iif(drawdowns < -0.1, 'yellow', 0))	
	
	plota(strategy$eq, type='l', plotX=F, x.highlight = highlight, ylim=range(buy.hold$eq,strategy$eq))
		plota.legend('strategy,10% Drawdown,15%  Drawdown','black,yellow,orange')
		
	plota(100*drawdowns, type='l', x.highlight = highlight)
		plota.legend('drawdown', 'black', x='bottomleft')

The next logical step is to introduce highlighting into the backtest plots. This is extremely easy to do because I use plota library to create backtesting reports.

	#*****************************************************************
	# Create Report
	#****************************************************************** 		
	plota.control$col.x.highlight = iif(drawdowns < -0.15, 'orange', iif(drawdowns < -0.1, 'yellow', 0))				
	highlight = drawdowns < -0.1

	plotbt.custom.report.part1(strategy, buy.hold, x.highlight = highlight)

Please let me know what other Visualization techniques do you use.

To view the complete source code for this example, please have a look at the bt.timelyportfolio.visualization.test() function in bt.test.r at github.

Categories: R

Calling Systematic Investor Toolbox from Excel using RExcel & VBA

RExcel is a great tool to connect R and Microsoft Excel. With a press of a button, I can easily execute my R scripts and present output interactively in Excel. This easy integration allows non-R users to explore the power of the R language. As an example of this approach, I want to show how to create an Efficient Frontier using Systematic Investor Toolbox and display it in Excel.

First, you need to install RExcel from http://rcom.univie.ac.at/download.html I used the following guide to help with my installation http://homepage.univie.ac.at/erich.neuwirth/php/rcomwiki/doku.php?id=wiki:how_to_install

Next, please check that RExcel is working properly by trying some examples from
http://homepage.univie.ac.at/erich.neuwirth/php/rcomwiki/doku.php?id=wiki:excel_worksheet_functions_using_r

Now, we are ready to design the workbook to run mean-variance optimization to create an Efficient Frontier. Following is a screen shot of the complete interface:

You can download the AssetAllocation.xls workbook and experiment with it while you keep reading.

Let’s put our Input Assumptions into Excel: Return, Risk, and Correlation matrix in rows 1:12. Let’s make a button at row 14 to construct an Efficient Frontier and associate it with our “create_efficient_frontier” VBA macro.

The “create_efficient_frontier” VBA macro will collect our Input Assumptions from Excel and send them to the R environment. It will next execute the R script to construct the Efficient Frontier, and finally it will collect R calculations of Risk, Return, and Weights of portfolios lying on the Efficient Frontier and transfer them back to Excel.

Here is the R script to construct the Efficient Frontier. I created a VBA function “create_rcode” to create this file automatically for this example. In practice this can be a static file containing all the logic for your algorithm.

###############################################################################
# Load Systematic Investor Toolbox (SIT)                                       
# http://systematicinvestor.wordpress.com/systematic-investor-toolbox/         
###############################################################################
if(!exists('portopt')) {                                                       
   con = gzcon(url('http://www.systematicportfolio.com/sit.gz', 'rb'))         
       source(con)                                                             
   close(con)                                                                  
}                                                                              

   #--------------------------------------------------------------------------
   # Create Efficient Frontier                                                
   #--------------------------------------------------------------------------
     ia = list()                                                              
       ia$symbols = ia.name                                                   
       ia$n = len(ia$symbols)                                                 
       ia$expected.return = ia.return                                         
       ia$risk = ia.risk                                                      
       ia$correlation = ia.correlation                                        
   n = ia$n                                                                   

   # 0 <= x.i <= 1                                                            
   constraints = new.constraints(n, lb = 0, ub = 1)                           

   # SUM x.i = 1                                                              
   constraints = add.constraints(rep(1, n), 1, type = '=', constraints)       

   # create efficient frontier                                                
   ef.risk = portopt(ia, constraints, 50)                                     

The “create_efficient_frontier” VBA macro will collect Input Assumptions and send them to R environment, it will next execute the R script to construct the Efficient Frontier, and finally it will collect the Risk, Return, and Weights of portfolios lying on the Efficient Frontier and transfer it back to Excel.

Here is the “create_efficient_frontier” VBA macro that automates all the functionality:

' Create Efficient Frontier
Sub create_efficient_frontier()
    ' Start R connection
    RInterface.StartRServer
        
    ' Write R code to file
    create_rcode

    ' Clean Output Area
    Sheets("AssetAllocation").Range("A17:IV10000").ClearContents

    ' Put Input Assumptions into R
    RInterface.PutArray "ia.name", Range("AssetAllocation!A4:A12")
    RInterface.PutArray "ia.return", Range("AssetAllocation!B4:B12")
    RInterface.PutArray "ia.risk", Range("AssetAllocation!C4:C12")
    RInterface.PutArray "ia.correlation", Range("AssetAllocation!F4:N12")
          
    ' Executes the commands in R script
    RInterface.RunRFile r_filename
          
    ' Get Efficient Frontier into Excel
    RInterface.GetArray "ef.risk$return", Range("AssetAllocation!B17")
    RInterface.GetArray "ef.risk$risk", Range("AssetAllocation!C17")
    RInterface.GetArray "ef.risk$weight", Range("AssetAllocation!E17")     
End Sub

Here is a complete AssetAllocation.xls workbook that creates an Efficient Frontier with user specified Input Assumptions. This workbook can be used by non-R users to explore the power of the R language, as long as RExcel is installed.

Please make sure that you add the RExcel library to the references. First, click on the Tool->References (from the VBA IDE menu)

Next select RExcelVBAlib and press Ok

Categories: Portfolio Construction, R

Transaction Cost and Execution Price functionality in the Backtesting library in the Systematic Investor Toolbox

I want to introduce the Transaction Cost and Execution Price functionality in the Backtesting library in the Systematic Investor Toolbox.

The Transaction Cost is implemented by a commission parameter in the bt.run() function. You may specify the commissions in $ per share for “share” type backtest and as a percentage of total trade for “weight” type backtest.

Following is a simple example of 50 / 200 day moving average cross over strategy with 10c per share commission:

###############################################################################
# Load Systematic Investor Toolbox (SIT)
# http://systematicinvestor.wordpress.com/systematic-investor-toolbox/
###############################################################################
con = gzcon(url('http://www.systematicportfolio.com/sit.gz', 'rb'))
    source(con)
close(con)

	#*****************************************************************
	# Load historical data
	#****************************************************************** 
	load.packages('quantmod')	
	tickers = spl('SPY')

	data <- new.env()
	getSymbols(tickers, src = 'yahoo', from = '1970-01-01', env = data, auto.assign = T)
		for(i in ls(data)) data[[i]] = adjustOHLC(data[[i]], use.Adjusted=T)			
	bt.prep(data, align='keep.all', dates='1970::')	
	
	#*****************************************************************
	# Code Strategies
	#****************************************************************** 
	prices = data$prices   
	nperiods = nrow(prices)
	
	models = list()
	
	#*****************************************************************
	# Buy & Hold
	#****************************************************************** 
	data$weight[] = 0
		data$execution.price[] = NA
		data$weight[] = 1
	models$buy.hold = bt.run.share(data, clean.signal=T)

	#*****************************************************************
	# MA cross-over strategy
	#****************************************************************** 
	sma.fast = SMA(prices, 50)
	sma.slow = SMA(prices, 200)
		signal = iif(sma.fast >= sma.slow, 1, -1)
	
	data$weight[] = NA
		data$weight[] = signal
	models$ma.crossover = bt.run.share(data, clean.signal=T, trade.summary = TRUE)

	#*****************************************************************
	# MA cross-over strategy, add 10c per share commission
	#*****************************************************************	
	data$weight[] = NA
		data$weight[] = signal
	models$ma.crossover.com = bt.run.share(data, commission = 0.10, clean.signal=T)

The transaction costs reduced the total strategy performance from 5.13 to 4.83

The Backtesting library in the Systematic Investor Toolbox was designed with an assumption that there is only one price at which securities are traded each day, by default I used Close prices. The Execution.Price field allows to override this default functionality. In the following example, I modified the 50 / 200 day moving average cross over strategy to Enter trades at the open the next day after the signal.

	#*****************************************************************
	# MA cross-over strategy:
	# Exit trades at the close on the day of the signal
	# Enter trades at the open the next day after the signal	
	#****************************************************************** 
	popen = bt.apply(data, Op)		
	signal.new = signal
		trade.start	 = which(signal != mlag(signal) & signal != 0)
		signal.new[trade.start] = 0
		trade.start = trade.start + 1
		
	data$weight[] = NA
		data$execution.price[] = NA
		data$execution.price[trade.start,] = popen[trade.start,]
		data$weight[] = signal.new
	models$ma.crossover.enter.next.open = bt.run.share(data, clean.signal=T, trade.summary = TRUE)			
	
	#*****************************************************************
	# Create Report
	#****************************************************************** 	
	# Plot perfromance
	plotbt(models, plotX = T, log = 'y', LeftMargin = 3)	    	
		mtext('Cumulative Performance', side = 2, line = 1)
		
	# Plot trades
	plotbt.custom.report.part3(models$ma.crossover, trade.summary = TRUE)		
	plotbt.custom.report.part3(models$ma.crossover.enter.next.open, trade.summary = TRUE)		

This modification reduced the total strategy performance from 5.13 to 5.09 and we can double check the logic by comparing trades for each strategy below:

To view the complete source code for this example, please have a look at the bt.execution.price.test() function in bt.test.r at github.

Follow

Get every new post delivered to your Inbox.

Join 242 other followers