Loading financial data from Internet into R through Excel

When I face a new post, sometimes, I waste the most of the time trying to get the data. The same happens within a company or in academic world. If any of you has ever been doing academic research or developing financial models, knows that get the data, load it on your working platform and have it ready to run is part of the way.

Today, let me show you two easy steps to download financial data from internet into an Excel spreadsheet through VBA and after that, how to import this data set into R through a new package named “XLConnect”. To do so, I have taken the EUR/CHF foreign exchange as example.

I usually use these two steps when I want to get data from internet, look at it in a friendly way or maybe manage some format aspects with Excel , and then, export to R, where I really want to work.

Step 1: There are several websites which provide financial data for free. (Here you can find a list of data sources). For FX data, OANDA provides a wide range of FX with enough historic depth, and besides, it’s really easy to work with.

On the following link you can download the Excel file which includes the macro «GetData()».  However, since I know that sometimes there are incompatibilities among releases or security restrictions, you can also find the macro code here.  Although this macro connects to OANDA database, it’s also  useful to whatever internet source. User should apply only small changes on the data format and change the path.

 FX_import.xls

Step 2: Recently I found out a new R package “XLConnect” to connect R and Excel. Certainly it seems to me very useful and works very well in cases like those. In our example, for instance, the function “readWorksheetFromFile”, allows to load the dataset from our excel file specifying the position and data format only with one simple line.

 

library(rJava) # rJava package is required
library(XLConnect) 

# NEW TOOL: XLConnect
XLSfile <- file.path(".../FX_import.xls") # Your path here
FXdata <- readWorksheetFromFile(XLSfile, sheet="FXdata",header = T
    , startCol = 4, startRow = 14 , endCol = 5, endRow = 4371
    , colTypes = c(XLC$DATA_TYPE.DATETIME, XLC$DATA_TYPE.NUMERIC),
    forceConversion = TRUE,dateTimeFormat = "%Y-%m-%d")  

As we can see, user can define the excel range easily with the startCol, startRow, endCol and endRow. In our example, we show the EUR/CHF FX since 2002, when the EURO banknotes were put into circulation. Once we have got the data into R, we can draw the graph. We point out the maximum and minimum of the sample and add the average as well.

# We define the FX as usual
EUR.CHF <- 1/FXdata$CHF.EUR

# Plot
plot(x=FXdata$End.Date, y=EUR.CHF, ylim=c(0.9*min(EUR.CHF),1.1*max(EUR.CHF)), col='black', type='l'
, main='EUR/CHF FX', xlab='date', ylab='EUR/CHF')
abline(h=min(EUR.CHF), lwd=2, col="red", lty="dotted")  # Mínimum
abline(h=max(EUR.CHF), lwd=2, col="red", lty="dotted")   # Maximum
abline(h=mean(EUR.CHF), lwd=2, col="green", lty="dotted")   # Avg

 

EURCHF FX

Note: On the following post, as an Economic observer, I am going to talk about the underlying factors of this graph.