## Friday, 27 October 2017

### Updating Historical Data Using Oanda's API and R

The main function to do this, HisPricesDates, downloads data between given dates as function inputs and is shown below.
HisPricesDates  = function( Granularity, DayAlign, TimeAlign, AccountToken, Instrument, Start, End ){

% a typical Oanda API call might look like
% which is slowly built up by using the R paste function, commented at end of each line below

auth           = c(Authorization = paste("Bearer",AccountToken,sep=" "))
qstart = paste("start=",Start,sep="")                                % start=2014-03-21
qend   = paste("end=",End,sep="")                                    % end=2014-04-21
qcandleFormat  = "candleFormat=midpoint"                             % candleFormat=midpoint
qgranularity   = paste("granularity=",Granularity,sep="")            % granularity=D
qdailyalignment    = paste("dailyAlignment=",DayAlign,sep="")        % dailyAlignment=0
qincludeFirst = "includeFirst=false"                                 % includeFirst=false
QueryHistPrec2 = paste(QueryHistPrec1,qgranularity,qstart,qend,qcandleFormat,qincludeFirst,qdailyalignment,sep="&")
InstHistPjson = fromJSON(InstHistP, simplifyDataFrame = TRUE)
Prices        = data.frame(InstHistPjson[[3]])
Prices$time = paste(substr(Prices$time,1,10),substr(Prices$time,12,19), sep=" ") colnames(Prices) = c("TimeStamp","Open","High","Low","Close","TickVolume","Complete") Prices$TimeStamp = as.POSIXct(strptime(Prices$TimeStamp, "%Y-%m-%d %H:%M:%OS"),origin="1970-01-01",tz = "UTC") attributes(Prices$TimeStamp)\$tzone = TimeAlign
return(Prices)

}

The daily update script, which is shown next,
% cd to the daily data directory
setwd("~/Documents/octave/oanda_data/daily")

for( ii in 1 : nrow( all_current_historical_data_list ) ) {

instrument = all_current_historical_data_list[ ii , 1 ]
% read second column of dates in all_current_historical_data_list as a date index
date_ix = as.Date( all_current_historical_data_list[ ii , 2 ] )
todays_date = as.Date( Sys.time() )

% download the missing historical data from date_ix to todays_date, if and only if, date_ix != todays_date
if( date_ix + 1 != todays_date ) {

new_historical_data = HisPricesDates( Granularity = "D", DayAlign, TimeAlign, AccountToken, instrument,
date_ix , todays_date )

% the new_historical_data might only try to add incomplete OHLC data, in which case do not actually
% want to update, so only update if we will be adding new, complete OHLC information
if ( nrow( new_historical_data ) >= 2 & new_historical_data[ 2 , 7 ] == TRUE ) {

% now do some data manipulation
% expect date of last line in Instrument_update_file == date of first line in new_historical_data
if ( date_ix == as.Date( new_historical_data[ 1 , 1 ] ) ) { % this is the case if true
new_historical_data = new_historical_data[ -1 , ]       % so delete first row of new_historical_data
}

% similarly, expect last line of new_historical_data to be an incomplete OHLC bar
if ( new_historical_data[ nrow( new_historical_data) , 7 ] == FALSE) {         % if so,
new_historical_data = new_historical_data[ -nrow( new_historical_data) , ] % delete this last line
}

% append new_historical_data to the relevant raw data file
write.table( new_historical_data , file = paste( instrument , "raw_OHLC_daily" , sep = "_" ) , row.names = FALSE , na = "" ,
col.names = FALSE , sep = "," , append = TRUE )

new_last_date = as.Date( new_historical_data[ added_data_length , 1 ] )

% and amend Instrument_update file with lastest update information
all_current_historical_data_list[ ii , 2 ] = new_last_date
all_current_historical_data_list[ ii , 3 ] = all_current_historical_data_list[ ii , 3 ] + added_data_length

} % end of ( date_ix != todays_date ) if statement

} % end of for all_current_historical_data_list loop

% Write updated Instrument_update_file to file
write.table( all_current_historical_data_list , file = "instrument_daily_update_file" , row.names = FALSE , col.names = FALSE , na = "" )
has if statements as control structures to check that there is likely to be new daily data to actually download. It does this by checking a last_update date contained in an "instrument_daily_update_file" and comparing this with the current OS system time. If there is likely to be new data, the script runs and then updates this "instrument_daily_update_file." If not, the script exits with nothing having been done.

The intraday update script doe not have the checks the daily script has because I assume there will always be some new intraday data available for download. In this case, the last_update date is read from the "instrument_update_file" purely to act as an input to the above HisPricesDates function. As a result, this script involves some data manipulation to ensure that duplicate data is not printed to file. This script is shown next and is heavily commented to explain what is happening.
% cd to the hourly data directory
setwd("~/Documents/octave/oanda_data")

for( ii in 1 : nrow( all_current_historical_data_list ) ) {

instrument = all_current_historical_data_list[ ii , 1 ]

% read second column of dates in all_current_historical_data_list as a date index
date_ix = as.Date( all_current_historical_data_list[ ii , 2 ] )

todays_date = as.Date( Sys.time() )

% hourly bars for today only.
new_historical_data = HisPricesDates( Granularity = "H1", DayAlign, TimeAlign, AccountToken, instrument,
date_ix , todays_date + 1 )

% the new_historical_data will almost certainly have incomplete hourly OHLC data in its last line,
% so delete this incomplete OHLC information
if ( new_historical_data[ nrow( new_historical_data ) , 7 ] == FALSE ) {
new_historical_data = new_historical_data[ -nrow( new_historical_data ) , ]
}

% read the last line only of the current OHLC file for this instrument
file = paste( instrument , "raw_OHLC_hourly" , sep = "_" ) % get the filename

system_command = paste( "tail -1" , file , sep = " " )     % create a unix system command to read the last line of this file

% read the file's last line
old_historical_data = read.csv( textConnection( system( system_command , intern = TRUE ) ) , header = FALSE , sep = "," ,
stringsAsFactors = FALSE )

old_historical_data_end_date_time = old_historical_data[ 1 , 1 ]            % get the date value to be matched

new_historical_data_date_times = as.character( new_historical_data[ , 1 ] ) % vector to search for the above date value

ix = charmatch( old_historical_data_end_date_time , new_historical_data_date_times ) % get the matching index value

% delete that part of new_historical_data which is already contained in filename
new_historical_data = new_historical_data[ -( 1 : ix ) , ]

% append new_historical_data to the relevant raw data file
write.table( new_historical_data , file = paste( instrument , "raw_OHLC_hourly" , sep = "_" ) , row.names = FALSE , na = "" ,
col.names = FALSE , sep = "," , append = TRUE )

write.table( all_current_historical_data_list , file = "instrument_hourly_update_file" , row.names = FALSE , col.names = FALSE , na = "" )